SQL string formatter

Does anyone know of 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.

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: https://en.wikipedia.org/wiki/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

Try fsqlf

fsqlf (http://sourceforge.net/projects/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