57

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.

3

2 Answers 2

86

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
12
  • 16
    This totally botched the formatting (of indentation) i wanted, no matter the arguments supplied. Aug 22, 2017 at 21:48
  • 4
    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.
    – Pysis
    Apr 12, 2018 at 19:13
  • 2
    This tool is automatically installed by Apache Superset. And I consider it as some form of endorsement from Superset.
    – Lei Zhao
    May 28, 2018 at 6:21
  • 3
    It doesn't seem to support pipe | for reading data from STDIN.
    – cprn
    Oct 11, 2018 at 10:40
  • 2
    I hope you don't have more than a few records as I quit waiting for output after minutes.
    – wilsotc
    Mar 5, 2021 at 14:00
16

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
2
  • 4
    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!
    – Alex Bitek
    Jul 24, 2014 at 7:52
  • Any PPA for easy install on Ubuntu-like?
    – cprn
    Oct 11, 2018 at 10:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.