intro
Let’s discover the best SQL formatter tools and understand their key role in making it easier to write high-quality and readable SQL code.
An SQL formatter is a tool that parses SQL statements to format them according to some specific style guidelines. Thanks to such tools, you can automatically transform messy SQL code into readable and easier-to-manage SQL code. This is why it is very important to use a SQL formatting tool or plugin in your projects.
In this article, you will take an in-depth look at SQL formatting, understanding how it works and why to use it. You will also discover the best SQL formatter tools.
Let's dive in!
What Is a SQL Formatter?
A SQL formatter, also known as a SQL beautifier, is a tool designed to format SQL code according to specified rules. These can follow standardized guidelines or custom user preferences.
A SQL formatting tool can be a CLI, online, or desktop application, or even a plugin for your favorite IDE or text editor. It takes unformatted SQL code as input and applies consistent indentation, line breaks, spacing, and capitalization. The result will be equivalent SQL code that is easier to read and maintain.
Let’s better understand what SQL formatting is all about with an example. Assume you have a messy, hard-to-read, long query as below:
1
SELECT e.LAST_NAME AS "Last Name", e.FIRST_NAME AS "First Name", d.DEPARTMENT_NAME AS "Department", e.SALARY AS "Salary", e.SALARY + e.SALARY * e.COMMISSION_PCT, e.COMMISSION_PCT * 100 || '%', ROUND(e.SALARY / ( SELECT MAX(SALARY) FROM HR.EMPLOYEES), 2) * 100 AS "Percentage of Max" FROM HR.EMPLOYEES e INNER JOIN HR.DEPARTMENTS d ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID) WHERE d.DEPARTMENT_ID IN (10, 20, 90, 210) AND e.SALARY > 3000;
A SQL query formatter would rewrite that query for you to make it readable, tidy, and consistent as follows:
1
SELECT
2
e.LAST_NAME AS "Last Name",
3
e.FIRST_NAME AS "First Name",
4
d.DEPARTMENT_NAME AS "Department",
5
e.SALARY AS "Salary",
6
e.SALARY + e.SALARY * e.COMMISSION_PCT,
7
e.COMMISSION_PCT * 100 || '%',
8
ROUND(e.SALARY /
9
( SELECT
10
MAX(SALARY)
11
FROM
12
HR.EMPLOYEES), 2) * 100 AS "Percentage of Max"
13
FROM
14
HR.EMPLOYEES e
15
INNER JOIN
16
HR.DEPARTMENTS d
17
ON
18
(
19
e.DEPARTMENT_ID = d.DEPARTMENT_ID)
20
WHERE
21
d.DEPARTMENT_ID IN (10,
22
20,
23
90,
24
210)
25
AND e.SALARY > 3000;
Much better, isn’t it? That is the power of SQL formatting!
How Does a SQL Query Formatter Work?
A SQL query formatter works by following this three-step procedure:
To achieve this, a SQL code formatter usually relies on algorithms and heuristics to determine the appropriate formatting for each part of a query. It might also utilize libraries or other tools for syntax analysis and pattern recognition.
Keep in mind that the way a SQL query formatter operates may not be fixed. That is because some formatters allow users to select predefined output styles or deeply customize formatting options to tailor the output to their specific requirements.
Top 5 Reasons to Use a SQL Code Formatter
Integrating a SQL code formatter into database management processes offers several benefits to developers and database administrators. These include:
Best SQL Formatter Tools
Now that you know what a SQL query formatter is, how it works, and why you need one, it is time to take a look at the list of the best SQL formatter tools.
The query these tools will be tested against will be the following one:
1
SELECT FIRST_NAME, LAST_NAME, SALARY, CASE WHEN SALARY > 10000 THEN 'High' WHEN SALARY BETWEEN 5000 AND 999 THEN 'Midlevel' ELSE 'Low' END AS "Income Level", CASE DEPARTMENT_ID WHEN 40 THEN 'Administration' WHEN 20 THEN 'Sales Related' ELSE 'Other' END AS "Special Departments" FROM EMPLOYEES;
Due to poor formatting, it is hard to tell, but this query retrieves employee data. In particular, it categorizes employees by their salary into 'High', 'Mid-level' or 'Low', and by their department ID into 'Administration', 'Related Sales' or 'Other'.
If you are eager to find out what the best SQL formatter tools have to offer, take a look at the summary table below:
| SQL formatting tool | Type | Target user | Supported databases |
|---|---|---|---|
| DbVisualizer | Database client | Developers, database admins, non-technical users | Oracle, MySQL, PostgreSQL, Microsoft SQL Server, ClickHouse, SQLite, MongoDB, https://www.dbvis.com/supported-databases/ |
| SQL Pretty Printer | Online site | Developers, database admins, non-technical users | MySQL, MS ACCESS, DB2, Oracle/PLSQL, MDX, Generic SQL |
| sql-formatter | JavaScript library | JavaScript/TypeScript developers | GCP BigQuery, IBM DB2, Apache Hive, MariaDB, MySQL, TiDB, Couchbase N1QL, Oracle PL/SQL, PostgreSQL, Amazon Redshift, SingleStoreDB, Snowflake, Spark, SQL Server Transact-SQL, Trino |
Time to analyze the top SQL formatting tools!
DbVisualizer: Database Client
DbVisualizer is a powerful database client that supports dozens of databases technologies. As a full-featured tool, it comes with built-in SQL formatting and linting capabilities.
Download DbVisualizer for free, follow the wizard to set up a database connection, and open the SQL Commander tab.
Write the input query, right-click on the code editing canvas, and select “Format SQL > Format Buffer” option to format it:
DbVisualizer will format your SQL query for you. The end result will be:
1
SELECT
2
FIRST_NAME,
3
LAST_NAME,
4
SALARY,
5
CASE
6
WHEN SALARY > 10000
7
THEN 'High'
8
WHEN SALARY BETWEEN 5000 AND 999
9
THEN 'Midlevel'
10
ELSE 'Low'
11
END AS "Income Level",
12
CASE DEPARTMENT_ID
13
WHEN 40
14
THEN 'Administration'
15
WHEN 20
16
THEN 'Sales Related'
17
ELSE 'Other'
18
END AS "Special Departments"
19
FROM
20
EMPLOYEES;
If this result does not meet your needs or preferences, you can configure it thanks to the several options available:
Et voilà! You just formatted your query with a couple of clicks. Note that you can also beautify only the current selection with the “Format Current” option. Find out more in our guide on formatting SQL in DbVisualizer.
SQL Pretty Printer: Online SQL Formatter
SQL Pretty Printer is one of the most popular online SQL formatter sites available. Even though the UI of the webpage is quite outdated, the tool works as you would expect. Compared to similar online formatters, SQL Pretty Printer also offers in-depth configuration options in the right-hand column.
The main disadvantage of using an online SQL code formatter is that it may break your workflow. The reason is that developers typically write queries directly into the IDE or database client. When using SQL Pretty Printer to get formatted output, you have to open the site, manually copy the query, paste it, and press the “Format SQL” button. This is a cumbersome procedure. Thus, the tool works best for occasional uses.
With no custom configuration, the result for the input query presented earlier would be:
1
SELECT first_name,
2
last_name,
3
salary,
4
CASE
5
WHEN salary > 10000 THEN 'High'
6
WHEN salary BETWEEN 5000 AND 999 THEN 'Midlevel'
7
ELSE 'Low'
8
END AS "Income Level",
9
CASE department_id
10
WHEN 40 THEN 'Administration'
11
WHEN 20 THEN 'Sales Related'
12
ELSE 'Other'
13
END AS "Special Departments"
14
FROM employees;
sql-formatter: npm Package
With over 2k stars on GitHub, sql-formatter is the most popular library for SQL formatting. This tool requires coding skills and can only be used by JavaScript or TypeScript developers.
As a JavaScript npm library, you can install it with the command below:
1
npm install sql-formatter
Then, you can use it in a Node.js script to format the input query as follows:
1
import { format } from "sql-formatter";
2
3
const inputQuery = `SELECT FIRST_NAME, LAST_NAME, SALARY, CASE WHEN SALARY > 10000 THEN 'High' WHEN SALARY BETWEEN 5000 AND 999 THEN 'Midlevel' ELSE 'Low' END AS "Income Level", CASE DEPARTMENT_ID WHEN 40 THEN 'Administration' WHEN 20 THEN 'Sales Related' ELSE 'Other' END AS "Special Departments" FROM EMPLOYEES;`;
4
5
const outputQuery = format(inputQuery , { language: "sql" });
6
7
console.log(outputQuery);
The result of this snippet would be logged into the console and would look like this:
1
SELECT
2
FIRST_NAME,
3
LAST_NAME,
4
SALARY,
5
CASE
6
WHEN SALARY > 10000 THEN 'High'
7
WHEN SALARY BETWEEN 5000 AND 999 THEN 'Midlevel'
8
ELSE 'Low'
9
END AS "Income Level",
10
CASE DEPARTMENT_ID
11
WHEN 40 THEN 'Administration'
12
WHEN 20 THEN 'Sales Related'
13
ELSE 'Other'
14
END AS "Special Departments"
15
FROM
16
EMPLOYEES;
Conclusion
SQL formatters play a key role in making SQL statements more readable. They can enforce standards and correct inconsistencies in stylistic conventions, improving developer productivity.
In this blog, you learned how SQL query formatting tools work and saw a list of the best ones. The ideal solution would be to have SQL formatting capabilities directly in your database client. This is exactly what DbVisualizer offers. As a full-featured database client, it also offers many other features, including query optimization and more. Try it for free today!
FAQ
Is there a SQL formatter online site?
Yes, there are several online SQL formatter tools available. Some of the most popular ones are:
What is the difference between a SQL query formatter and a SQL linter?
A SQL query formatter primarily focuses on formatting SQL code. Instead, a SQL linter analyzes SQL code for syntax errors, security vulnerabilities, or adherence to coding standards and best practices. The confusion arises from the fact that most SQL linters also offer a SQL beautifier plugin. However, this does not mean that the two tools are the same thing.
Is it possible to set up a SQL formatter notepad?
Yes, it is possible to have a SQL formatter notepad. In detail, Notepad++ supports SQLinForm, a plugin for SQL formatting. Similarly, Sublime Text supports the sql-formatter plugin.
Is there a VS SQL formatter extension?
Yes, Visual Studio Code supports SQL formatting via the SQL Formatter extension. Behind the scenes, this tool relies on the sql-formatter-plus npm package.
What is the difference between a SQL beautifier and a SQL code formatter?
There is not much difference between the two tools. “SQL beautifier” and “SQL code formatter” are two expressions that refer to the same tools. They have the objective of formatting SQL code. In the first case, the focus is on making the code “beautiful,” that is, easier to read. In the second case, the focus is on the word “formatting,” which is the operation performed by the tool. Regardless of the terminology, their goal is the same.
