SQL

SQL Formatter: Definition and Best Tools

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

Copy
        
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:

Copy
        
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:

  1. Parses the input SQL code into its constituent parts, such as keywords, identifiers, literals, whitespace characters, and punctuation.
  2. Applies a set of rules, standards, conventions, and best practices to reformat each SQL building block in the code according to a specified style guide.
  3. Systematically reassembles the parsed elements into a structured and formatted SQL statement.

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:

  1. Increased readability: Produce well-formatted SQL code that is easier to read and understand, reducing the time needed to comprehend complex queries and facilitating collaboration among team members.
  2. Better consistency: Ensure that all SQL code within a project or organization follows the same rules, promoting consistency across queries.
  3. Improved maintainability: Make identifying and addressing issues quicker and more efficient, as developers will deal with queries with consistent formatting and style conventions.
  4. Standardization: Enforce coding standards and best practices, ensuring that all SQL code conforms to industry or organizational guidelines for style, naming conventions, and formatting.
  5. Enhanced productivity: Save users the time and effort of manually formatting queries and/or making them more readable. This means that developers can focus on writing and optimizing SQL queries instead of worrying about less important details, such as formatting.

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:

Copy
        
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 toolTypeTarget userSupported databases
DbVisualizerDatabase clientDevelopers, database admins, non-technical usersOracle, MySQL, PostgreSQL, Microsoft SQL Server, ClickHouse, SQLite, MongoDB, https://www.dbvis.com/supported-databases/
SQL Pretty PrinterOnline siteDevelopers, database admins, non-technical usersMySQL, MS ACCESS, DB2, Oracle/PLSQL, MDX, Generic SQL
sql-formatterJavaScript libraryJavaScript/TypeScript developersGCP 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:

Formatting SQL code in DbVisualizer
Formatting SQL code in DbVisualizer

DbVisualizer will format your SQL query for you. The end result will be:

Copy
        
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:

The SQL formatting options offered by DbVisualizer
The SQL formatting options offered by DbVisualizer

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

https://paper-attachments.dropboxusercontent.com/s_79821CCC6CE36A4EAC7658B0224FA803882350691A3ED2EEA2E56FBD4F3D77D3_1713255576501_image.png
SQL Pretty Printer

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:

Copy
        
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;
Formatting a query on SQL Pretty Printer
Formatting a query on SQL Pretty Printer

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:

Copy
        
1 npm install sql-formatter

Then, you can use it in a Node.js script to format the input query as follows:

Copy
        
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:

Copy
        
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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Definitive Guide to Postgres Foreign Key

author Leslie S. Gyamfi tags POSTGRESQL SQL 12 min 2024-06-13
title

SQL Server DATEADD: The Complete Guide

author Ochuko Onojakpor tags SQL SQL SERVER 7 min 2024-06-10
title

mysqldump: How to Backup and Restore MySQL Databases

author Antonello Zanini tags Backup MySQL SQL 11 min 2024-06-06
title

The SQL DELETE Statement Explained

author Leslie S. Gyamfi tags DELETE SQL 4 min 2024-06-03
title

A Guide to the SQL Date Data Types

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 10 min 2024-05-30
title

SQL HAVING Clause: The Ultimate Guide

author Antonello Zanini tags SQL 7 min 2024-05-27
title

SQL INTERSECT: Everything You Need to Know

author Leslie S. Gyamfi tags SQL 2 min 2024-05-23
title

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16
title

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09
title

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗