PopSQL

The landscape of SQL tools

October 28th, 2022
Post Cover Image

Why finding the right SQL tool is so important

SQL is the language of data. Data teams spend most of their workdays writing SQL (engineering, product, finance, and operations teams aren’t far behind). Here's why:

SQL is popular: It's the third most popular programming language in the world, edging out Python, Java, and many others.

SQL is everywhere: There's a SQL database baked into every iPhone, Android, web browser, smart TV, smart car, and countless other applications.

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?) 💪

How different SQL tools stack up

People write SQL in a variety of tools. When choosing which tool to use, it helps to understand its intended purpose and the typical user. landscape-of-sql-tools

Command-line interfaces

command-line-interface 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 today? People who are using them for other tasks, such as software engineers. CLIs allow for minimal switching between programs and fast data extraction.

But CLIs are the least user-friendly approach to SQL. There's a reason why modern computers have abandoned verbally dictating every command.

While CLIs are fine for simple queries, they’re arduous for writing complex queries, conducting exploratory analyses, and collaborating.

Database management tools

database-management-tool As the name implies, these tools are designed for the solo task of managing a database, not exploring or collaborating on data.

Database management tools can help simplify administrative tasks, such as creating new tables and columns, importing data, or editing individual cells.

These offline desktop applications are often free or charge a low, one-time fee. However, most operate for only one database type and operating system — such as only MySQL and macOS — so you may end up needing to purchase more than one to cover all your databases, warehouses, and users on different operating systems.

Legacy SQL editors

legacy-sql-editor Legacy SQL editors typically have more features than a simple database management tool (some users even call it feature creep), stretching beyond database management into data exploration.

They provide a Microsoft Word-esque experience for writing SQL, also through an offline desktop app.

Users individually write SQL in legacy SQL editors. In order to collaborate, users must export SQL files to share with teammates who have set up the same connections.

There are various pricing models, with most requiring an individual license per user per database type.

Data warehouse web UIs

data-warehouse-sql-web-user-interface Cloud data warehouses have native web portals for running queries. These graphical user interfaces (GUI) are free (since you already pay for the data warehouse). Some allow you to save queries, and others even allow you to schedule runs against your data warehouse.

But data warehouse SQL GUIs are built by and for specific data warehouses. If you want to analyze any data outside of your data warehouse (e.g., a live production database), you’ll need an additional SQL editor.

Also, similar to legacy SQL editors, they’re often bloated with too many features, creating a UI that’s difficult for users to navigate.

Business intelligence tools

business-intelligence-tool Business intelligence (BI) tools focus on charts and dashboards. Some tools won’t even let you write a query that isn’t attached to a visualization.

Data analysts and scientists can produce exceptionally complex (though rigid) reports with advanced graphs. Many BI tools also 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 to keep definitions consistent.

But data exploration is limited to only what’s already been defined and instrumented. New feature launches or data models require ongoing updates and maintenance, which means teams may have to wait days or weeks for the data plumbing to get done before they can gather the insights they need today.

Collaborative SQL editors

collaborative-sql-editor Collaborative SQL editors combine the most heavily used features from the tools listed above in a modern, user-friendly workspace.

Like legacy SQL editors, they allow for robust query editing. And similar to BI tools, they support data visualization through customizable dashboards and charts (although with faster, easier options than pure play BI tools).

Collaborative SQL editors bring teammates together in one unified workspace where connections to data sources are centrally managed and shared. Teams create libraries of ad hoc foundational queries that can easily be organized, annotated, version-controlled, commented on, searched, updated, shared, and scheduled.

These tools emphasize fast and flexible data exploration. Since they’re focused on pure SQL, they don’t require the data instrumentation necessary with other tools 🫶

While they don’t have drag-and-drop query building, their collaboration features — such as query variables that allow users to rerun existing queries with different parameters — make it easy for even business teams to transform raw SQL into easily understood insights.

SQL tool overview

You can map the tools above along two axes: what they’re built to do and who they’re designed to support 🔍 sql-tool-matrix

The trends transforming SQL tools

Some SQL tools have been around for decades, but the technology landscape is always evolving. Learn the trends shaping the future of SQL tools 🔮

Ready for a modern SQL editor?