1. Introduction
The official meaning for the verb format is to convert a code from an initially defined style that you wrote into a standard generic format.
SQL format tool will allow you to convert a “handwritten” SQL format into a pettier visual format to be more easily readable. The readability decreases direct proportional with the length of the query this will cost the time you understand the entire query (this is basically when you have nested queries).
2. About SQL
SQL is the Standard Query Language used to query information from a table in a relational database. The language became a standard for ANSI (American National Standards Institute) in 1986, and one year later became ISO (International Organization for Standardization) as Wikipedia related.
This is used by Data Analysis, Data Engineer or Data Science.
The most popular databases so far are Oracle, MySQL, SQL-Server, Postgress, and IBM, and others as DB-Engines related.
When you write an SQL query you shouldn’t worry about formatting will be different, from one dialect o another. Formatting a query should not depend on which dialect you wrote.
3. SQL Style best practice
These best practices were inspired by a book about SQL design written by Joe Celko. Most of these best practice was summarised on this website.
As a general overview about storing information in SQL:
- having descriptive and consistent identifiers
- storing information about date and time in the ISO 8601 format (
YYYY-MM-DD HH:MM:SS.SSSSS) - using standard SQL functions instead of other vendors
- adding comments in the code when necessary
Best practice about indentation, white spaces/line spaces in a query syntax:
- write SQL queries with uppercase (e.g: SELECT, WHERE, AND)
- for a more readable SQL query it is important that the correct number of spaces is used. Do not crowd code or remove natural language spaces.
- space should be used to line up the code, so the root keywords all will end up on the same line
- after a line ended and there are not any new columns that you want to print in the result the new line should be indented with a tab making it easy for the readers to separate the keywords from the implementation details. In typography is a bad practice to add a tab between sentences.
- notice that
SELECT,FROM, etc. are all right-aligned while the actual column names and implementation-specific details are left-aligned. - spaces before and after ‘=’, ‘,’
- adding newline and vertical spaces before and, or. After semicolon to separate queries, after each keyword definition, after a comma after separating multiple columns, after separate code into related sections
- align to the right all the keywords and to the left all values (applicable for INSERT, UPDATE and SELECT)
- joins should be indented to the other side of the river and grouped with a new line where necessary.
- subqueries should also follow the same rule as before, meaning to align to the right of the previous root query. For a complex subquery, it will help wrap into parenthesis on a new line at the same character position.
4. 5 Best free online SQL formatter tools
Below are the best 5 online websites that format SQL queries.
I ranked the below websites from the accessibility point of view and how much you can customize in the formatting process, giving also the possibility to select the SQL dialect.
4.1 Dbriver https://www.dpriver.com/pp/sqlformat.htm
After you open the website you will be able firstly to select your SQL dialect through selecting the database. You can specify the output of the formatting SQL in many formats: HTML format with different blocks, a simple output text or even to output the formatting SQL in different languages like C#, Java, PHP, and others. Besides that, you will be able to specify how to formatting SQL queries should behave. You can specify to upper case all the SQL keywords to the table name, column name, the variable case even specify max length per line in compact mode. Below is a screenshot of the webpage interface.
4.2 PoorSQL https://poorsql.com/
The second website have pretty much the same custom fields to format SQL query. When you want to format a SQL query will have to insert your SQL query in the first tab: ‘Input SQL’ and after that to switch to the second tab: ‘Formatted SQL’ to see the result. This website will do a syntax check for the given query and will highlight where is the problem.
4.3 SQL Format https://sqlformat.org/
This website offers some limited options to format the SQL query. Besides the rest of the website, offers an API or a python module to use in your project to automatically format your queries.
4.4 Extendsclass https://extendsclass.com/sql-formatter.html
ExtendsClass website offer pretty much the same functionality from the customizing the output format for an SQL query the possibility to download the result file.
4.5 Codebeautify https://codebeautify.org/sqlformatter
The last one is a basic SQL Formatter online tool where you can type SQL quest to format. This tool allows you also to upload an SQL file or a link with some SQL queries to format. This website will provide different dialect beside SQL like N1QL and DB2.
If you use InteliJ Idea a basic useful formatter plugin is: SQL Formatter that you can install from your InteliJ. Go to File -> Settings -> Plugins -> Select ‘Marketplace’ and then type: SQL Formatter and install. After you installed on the right side of the InteliJ you will see a tab: ‘SQL Formatter’. Copy and Paste your query in the test area and press the ‘Format’ button.
5. Conclusion
In this article, you got familiar with what SQL format means, and how it will help you daily basis when working with SQL queries. We follow some of the best practices when you want to indent your query manually. Besides the theoretical part, you will find some online free tools for formatting SQL queries.
