SQL string formatter [closed]

Question

Is there a program, a utility, or some programmatic library, preferably for Linux, that takes an unformatted SQL string and pretty prints it?

For example, I would like the following

select * from users where name = 'Paul'

be changed to something like this

select *
from users
where
   name = 'Paul'

The exact formatting is not important. I just need something to take a large SQL string and break it up into something more readable.

Goldiegoldilocks

This SQL Formatter plugin is working great in vscode. You can use the sql-formatter cli version of the node package as well. – Jeaniejeanine

Related: Command line SQL formatter or beautifier - Software Recommendations Stack Exchange – Verein

Related: What free SQL formatting tools exist? – Presbytery

Answer 1

Try sqlparse module's sqlformat

Check out sqlparse. It is a Python module that installs the command sqlformat. Usage is simple, e.g.:

sqlformat --reindent --keywords upper --identifiers lower my_file.sql

Example:

$ echo "sElECt f1,f2,fname,lName FrOm tblName WhErE f1=true aNd fname iS nOt null oRdEr bY lName aSc" | \
sqlformat - --reindent --keywords upper --use_space_around_operators
SELECT f1,
       f2,
       fname,
       lName
FROM tblName
WHERE f1 = TRUE
  AND fname IS NOT NULL
ORDER BY lName ASC

I tried the aforementioned CLI alternatives, but:

• sqlinform is out, because I want an open source CLI application.
• fsqlf has only few features (create view is missing for example).

Note on SQL keywords

There are many SQL keywords. And they differ by SQL dialect. Wikipedia has a list: List of SQL reserved words

These keywords are illegal for use as an identifier. And if you still try, then there might be unpleasant surprises.

Thanks to sqlformat I learned that "REF" is a reserved keyword in SQL:2011 and SQL:2008.

So this explains why when you say want uppercase keywords but lowercase identifiers "rEf" here becomes "REF", but "mYrEf" becomes "myref":

$ echo 'sElEcT rEf fRoM mYtAbLe' | sqlformat - --reindent --keywords upper --identifiers lower
SELECT REF
FROM mytable


$ echo 'sElEcT mYrEf fRoM mYtAbLe' | sqlformat - --reindent --keywords upper --identifiers lower
SELECT myref
FROM mytable

Captor

This totally botched the formatting (of indentation) i wanted, no matter the arguments supplied. – Kedah

Same. Even with just -r, incidentally only after the ORDER BY clause, it started incrementally indenting by almost 40 characters for each new line of field provided. – Ssr

This tool is automatically installed by Apache Superset. And I consider it as some form of endorsement from Superset. – Typewritten

It doesn't seem to support pipe | for reading data from STDIN. – Larock

Just use > to redirect output to a file. – Nexus

@Larock You can in version 0.1.2 --- "Format FILE according to OPTIONS. Use "-" as FILE to read from stdin." – Magbie

[email protected] ist only getting Killed somehow when try to run it over my sql file. – Hennie

I hope you don't have more than a few records as I quit waiting for output after minutes. – Missus

If you use the programs sqlformat and the npm installed sql-formatter-cli in sequence in that order, the SQL formatted output is exquisite. – Emad

I probably won't write my grandparents to tell them about how great this formatter is, but it was good enough for my task... – Headlock

Doesn't work for me. The indent is in a mess. I try this: CREATE TABLE student ( sid INT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT, gpa FLOAT ); sqlformat tmp.sql --reindent --indent_width 4 --keywords upper --use_space_around_operators The result is: CREATE TABLE student (sid INT PRIMARY KEY, name VARCHAR(16), login VARCHAR(32) UNIQUE, age SMALLINT, gpa FLOAT); – Gains

The easiest way to install this is to run: pipx install sqlparse – Helfant

Answer 2

Try fsqlf

fsqlf is a command line or GUI program, open source, to format SQL. It supports having a formatting.conf file which allows you a lot of flexibility in how the final product looks.

Example 1:

☺  [wwalker@speedy:~]
$ echo "select f1, f2, fname, lName from tblName where f1 = true and fname is not null order by lName asc" | fsqlf

SELECT
  f1
, f2
, fname
, lName
FROM tblName
WHERE f1=true
AND fname is not null
ORDER BY lName asc

Example 2:

☺  [wwalker@speedy:~]
$ vim formatting.conf # 2 character change

☺  [wwalker@speedy:~]
$ echo "select f1, f2, fname, lName from tblName where f1 = true and fname is not null order by lName asc" | fsqlf

SELECT
 f1 ,
 f2 ,
 fname ,
 lName
FROM tblName
WHERE f1=true
AND fname is not null
ORDER BY lName asc

Example 3:

☺  [wwalker@speedy:~]
$ vim formatting.conf # 1 character change

☺  [wwalker@speedy:~]
$ echo "select f1, f2, fname, lName from tblName where f1 = true and fname is not null order by lName asc" | fsqlf

SELECT
 f1 , f2 , fname , lName
FROM tblName
WHERE f1=true
AND fname is not null
ORDER BY lName asc

Shillyshally

This is the best answer! Because you can format SQL queries on your own machine with an open source tool without having to use a third party website or a proprietary tool! With shady data retention and privacy policies! – Harelda

Any PPA for easy install on Ubuntu-like? – Larock