The Landscape of SQL Tools
Why is finding the right SQL tool so important?
SQL is the language of data. Data analysts spend over half their working hours writing SQL (engineers, product managers, and marketers aren't far behind). Here's why:
SQL is popular. It's the 3rd most popular programming language, edging out even Java and Python. 1
SQL is everywhere. There's a SQL database baked into every iPhone, Android, web browser, smart TV, smart car, and countless millions of other applications.2
SQL is not going away. SQL's persistence is legendary when you consider its lifespan (how many other technologies from the early 1970s are still booming?)
The Landscape of SQL Tools
People write SQL in a variety of tools. We'll examine the intended purpose and typical user of each type of tool:
Command Line Interfaces
Command Line Interfaces (CLI) may seem retro or confusing. But before user-friendly interfaces arrived in the mid-1980s, CLIs were the only way to talk to your computer. Since everything had to be done in a CLI, they can do everything, including run SQL.
Who writes SQL in a CLI? People already in a CLI for other tasks: software engineers. They allow for minimal switching between programs and fast data extraction.
CLIs are the least user friendly approach to SQL, though. There's a reason why modern computers have abandoned verbally dictating every command. While CLIs are fine for simple queries, they are arduous for writing complex queries, conducting exploratory analyses, and collaborating.
Database Management Tools
These tools focus on solo tasks like managing your database, as opposed to data exploration or collaboration. With their simple interfaces, DB Admins don't have to write DDL statements from scratch (e.g.
drop). Database management tools help create new tables and columns, import data, or edit individual cells.
These offline desktop apps are often free or charge a low one-time fee. Most operate for only one database type and operating system (e.g. just MySQL, only for Mac).
Legacy SQL Editors
Legacy SQL Editors are more feature-loaded than a simple database management tool. They provide a "Microsoft Word-esque" experience for SQL, stretching beyond database management into data exploration. A few have even made recent leaps into visualization.
Users individually write SQL in offline desktop apps. To collaborate, users must export
.sql files to share with teammates who have set up the same connections. There are various pricing models. Most require an individual license per user per database type.
Data Warehouse Web UIs
Cloud data warehouses have simple web portals for running queries. These lightweight tools are free (as you already pay for the data warehouse). Most allow you to save queries, and some even allow you to schedule runs against your data warehouse.
They're built by and for a particular data warehouse, though. If you have any data outside of your data warehouse (e.g. live production data), you’ll need an additional tool.
Business Intelligence Tools
Business Intelligence (BI) tools focus on charts and dashboards. Some tools won’t even let you write a query not attached to a visualization. Data Analysts and Scientists can produce exceptionally complex (though rigid) reports with advanced graphs.
Many BI tools have drag-and-drop "query builders" that let business users pull data without knowing SQL. To achieve both advanced visualizations and drag-and-drop querying, these tools require extensive, upfront data instrumentation. This effort keeps definitions consistent. Exploration is limited, though, only to what has been defined and instrumented. New feature launches or new data models require ongoing maintenance.
Collaborative SQL Editors
Collaborative SQL editors bring teammates together in one connected tool. Teams construct libraries of foundational queries. Connections to data sources are centrally managed and shared.
Collaborative SQL editors combine the most heavily used features from the tools above in a simple modern package. Like legacy SQL Editors, they allow for robust query editing. They provide core chart type, although typically fewer than BI tools.
These tools emphasize exploration and iteration in pure SQL. Sticking with SQL omits the data instrumentation needed in other tools. While querying is not quite "drag-and-drop", collaborative SQL editors make raw SQL easier with features like query parameterization or reusable SQL snippets.
You can map the tools above along two axes, intended purpose vs. typical user:
While some of these tools have been around for decades, the landscape of SQL tools is not static. Next, we'll look at the trends that are transforming SQL tools.
Rankings come from StackOverflow’s massive 2020 developer survey.
These stats are just for one flavor of SQL, SQLite! Crazy stat: there are 167 SQLite databases alone for every human on Earth 🤯 . Learn more.