SQL Editor Benchmark and Market Analysis

Introduction

As organizations recognize the critical function of data for generating value, most are seeking to put data at the center of their business strategy. We are witnessing a "data race", in which companies heavily invest in tools and talents to make sense of their data. The most critical workflow of data analysis is getting access to the data, or "querying" the data. Structured Query Language (SQL) is the programming language used by the most popular database servers worldwide, and SQL editors are the interfaces that provide access to the data. SQL is thus the most important language worldwide when it comes to data manipulation. Thus, democratizing access to data is synonym with making SQL accessible to a wide range of people. And SQL editors have a great role to play here. This article seeks to explain how SQL query editing tools have evolved with increased data usage and the powerful data democratization trend and help you choose the best tool for your organization.

‍

What's a SQL Editor?

In its most basic form, an SQL Editor is an interface allowing SQL queries to be performed. In short, it's a tool providing a way to manage and manipulate data in a database.

We identify three generations of SQL Editor tools:

1st generation: Standard interface that allows for the querying of a single database.

2nd generation: User-friendly interface improving user experience, and enabling multi-platform querying.

3rd generation: SQL notebooks fostering data democratization and collaboration between data users.

‍

SQL Editor 1.0

SQL editors were born from a very basic need: getting access to data located in a database management system. SQL is the most commonly used language when it comes to querying, updating, and organizing data in a database. It is used to manage databases containing high volumes of data: thousands, millions, and even billions of data points.

Interactions with a database are done through writing and executing SQL queries, statements, and scripts. This is exactly what first-generation SQL editors do: provide an interface to query databases. Standard editors usually propose basic features:

• Cut, copy, paste, undo, redo, and find data in the database.
• Establish database objects
• Define database schema and instance
• Debugging
• Data testing
• Highlighting keywords
• The output of a given query or statement
• A code editor

Some editors propose more advanced features, including:

Object explorer: This allows users to view and manage objects in all server types

New activity monitor: Helps you pinpoint any changes in your server.

Database administration: This tremendously facilitates database auditing, server configuration, and log viewing.

Custom reporting: some solutions, such as Oracle SQL Developer, allow you to generate pre-defined reports as well as to create custom ones.

First-generation tools are inherently optimized for database management. Organizations use these tools to set up and manage tables, mostly for administrative purposes. They are not optimized for data exploration, aiming at generating business value from data.

This category of tools is mostly used by technical profiles who have a deep understanding of how databases and SQL work. First-generation SQL IDEs are built for one-off queries, used by one person, and forgotten afterward. Data people remedy this through the use of note-taking apps, slack, and data discovery tools.

Examples of such tools include Microsoft Microsoft SQL Server Management Studio (SSMS), Oracle SQL Developer, Toad for SQL Server, MySQL workbench, dbForge studio, SQLite studio, and others. You can find a full list here. The good thing is, you can find a lot of first-generation options for free.

‍

SQL Editor 2.0

Standard SQL IDEs allowed for effective querying of databases. However, a fleet of new data challenges showed up in the past few years, forcing SQL editors to step up.

First, organizations still had data scattered around various platforms. This resulted in a high tool fragmentation, with data analysts and scientists constantly jumping between different platforms. There was a growing need to easily access data in a multi-platform environment. First-generation database management IDE's such as Oracle SQL Developer didn't do the trick anymore. That's when second-generation SQL tools came to the rescue, providing a single interface to access data in different locations.

Another issue was the difficulty to communicate and exchange work between data users. This resulted in duplication of work and a waste of productivity. Second-generation tools foster team collaboration by enabling teams to create libraries of common queries, usually organized in folders. They also make it easier to share query results: data, charts, etc, preventing users from sharing screenshots or pasting static charts in shared slides decks.

The major difference with first-generation editors is that more recent tools are optimized for data exploration. Newer SQL editors seek to assist data analysts, data scientists, and other data people in conducting meaningful analyses and reports for business purposes. We shift away from the database management paradigm, which mostly has administrative issues at stake. Second and third-generation tools are thus best at making the data talk and generating business insights.

Finally, second-generation editors focused on improving user experience and data analysts' workflows. They propose more advanced features, making it easier for data users to find data and the context around it.

Second generation SQL editors propose the following set of more advanced features:

• Multi-platform querying: The tool provides a single interface to query multiple data sources: databases, data warehouses, data lakes.
• Query library: you can build a library with the most widely used queries
• Autocomplete: When you start a query, the tool proposes a list of the most pertinent queries. This way, you don't have to write the query entirely.
• Visualization: The tool has a built-in visualization layer. You can get visual outputs after running your query without having a mastery of plotting libraries.
• Version history: The editor provides access to all the past versions of a query. This ensures previous versions of your/your team's work are never lost.
• Schedule recurring queries: Automate recurring queries, and get updated results just when you need them. You can also schedule refreshes of your dashboards.

Second-generation SQL editors are more intelligent, and allow data analysts/scientists to be more efficient with data manipulation and share their work in a flawless manner.

‍

SQL Editor 3.0: SQL notebooks

Third-generation SQL editors go one step further: they embrace the data democratization trend which shapes companies today. As more people start working with data in organizations, it is crucial to help non-technical users get comfortable with SQL and find answers to their questions without having to seek support from the data team. This help is provided by third-generation SQL editors. They are used by data analysts, product managers, customer success teams, operations, and anyone who needs to manipulate data.

SQL notebooks are the latest generation of SQL editors. A SQL notebook is a collaborative document combined with a SQL editor. It's basically a Jupyter notebook, but for SQL. Jupyter notebooks provide an interface for defining small cells of code that can be annotated using text blocks. Data people can thus conduct a step-by-step analysis that can easily be shared and understood by their peers. SQL notebooks rely on the same underlying principles as Jupyter notebooks. SQL queries can get extremely long and complex. Notebooks allow for a more structured approach, where the analysis is decomposed into small steps that can be explained thanks to text blocks. Each snippet of code is a query supporting rich outputs, such as charts, tables, formatted text, and any other output of your liking. And these queries can be referenced in any other cell.

These tools also have a built-in visualization layer, sparing users the pain of learning a plotting library or having to use another tool to perform visualizations. SQL notebooks thus sit between the worlds of coding interfaces and data visualization.

These tools are collaborative by design thanks to easy sharing features. Data users can see what others are doing in real-time, and discuss queries in each cell thanks to the possibility to write comments. More importantly, these tools can easily be used by non-technical profiles. The step-by-step structured approach allows for a clear understanding of queries. SQL notebooks are also optimized for query re-use, with the possibility to use query parameters. Instead of re-writing the code, some IDE's propose interactive cells that offer the possibility of just changing the value of interest. For example, if a SQL query selects all the books published by a given author after 1960, and you want this authors' books published after 1952, you can just change the date in the cell. Updates will be directly cascaded through the notebook. Some IDE's even offer the possibility to query your database without writing SQL. This way, people who are not fluent in SQL can still manipulate the data as they please.

‍

‍

SQL notebooks generally often offer the following features:

• Queries are written in annotated cells
• Easy cell referencing to build-up analysis
• Visualization in the notebook as you run the query
• Query parameter: Parameterize your SQL queries and let users set values of interest via simple UI elements - no need to manipulate code.
• Drag and drop query: drag-and-drop cells to build SQL queries without coding, with the possibility to get a SQL translation.
• Query link to share a query: Share your query, project, or notebook with a link instead of copying and pasting queries.
• Frequently used queries: The tool provides access to the most popular queries

‍

SQL IDE's landscape

If you're a database developer or user, you'll surely want to find out which is the best database management or the best data exploration tool. Below, you will find a SQL IDE landscape, which can hopefully help you choose a tool suited to your organization's needs. Whether you're looking for a tool like Oracle or PopSQL, we help you choose the best software for you.

‍

Choosing a SQL editor tool

When choosing a SQL editor, you need to pinpoint the exact needs of your organization. Do you need a database management tool, or are you looking for an editor allowing you to conduct data exploration and visualizations? Are you willing to compromise on key features to get a free tool? Don't get me wrong, a lot of free options out there are great, and totally do the job. Defining your needs will allow you to look for the best features for you in a SQL editor. For example, if you're using MySQL as your main database, you want to ensure you choose a tool that works the best with MySQL and your other databases. Similarly, if you're working on a Mac system, don't mistakingly purchase a software that only works on Windows.

More modern data stack benchmarks?

Find more benchmarks and analysis on the modern data stack here. We write about all the processes involved when leveraging data assets: from the modern data stack to data teams composition, to data governance. Our blog covers the technical and the less technical aspects of creating tangible value from data.

At Castor, we are building a data documentation tool for the Notion, Figma, Slack generation. Or data-wise for the Fivetran, Looker, Snowflake, DBT aficionados. We designed our catalog to be easy to use, delightful and friendly.

Want to check it out? Experience CastorDoc with a demo.

‍

Subscribe to the Castor Blog

‍