SQL Style And Formatter

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

Command-line Tools¶

sleek¶

Sleek is a CLI tool for formatting SQL. It helps you maintain a consistent style across your SQL code, enhancing readability and productivity.

sqlfluff¶

The command sqlfluff fix can be used to fix issues (including formatting issues) in SQL code. It can be used as a tool to format SQL code. However, sqlfluff fix runs extremely slow.

Python Libraries¶

andialbrecht/sqlparse¶

You can also use the Python library andialbrecht/sqlparse to help you format SQL queries. It is actually the backend of SQLFormat.

!pip3 install sqlparse

Collecting sqlparse
  Downloading sqlparse-0.4.1-py3-none-any.whl (42 kB)
     |████████████████████████████████| 42 kB 1.0 MB/s 
Installing collected packages: sqlparse
Successfully installed sqlparse-0.4.1

import sqlparse as sp

sql = """
    select c1, c2 /* this is a comment */
    from t1 join t2 on t1.id = t2.id and t1.city = 'Shanghai' where c1 > 1
    """
sql_fmt = sp.format(
    sql,
    keyword_case="upper",
    identifier_case="lower",
    strip_comments=False,
    reindent=True,
    indent_width=2,
)
print(sql_fmt)


SELECT c1,
       c2 /* this is a comment */
FROM t1
JOIN t2 ON t1.id = t2.id
AND t1.city = 'Shanghai'
WHERE c1 > 1

sql = """
    create table db.table as
    select c1, c2 /* this is a comment */
    from t where c1 > 1
    """
sql_fmt = sp.format(
    sql,
    keyword_case="upper",
    identifier_case="lower",
    strip_comments=False,
    reindent=True,
    indent_width=2,
)
print(sql_fmt)


CREATE TABLE db.table AS
SELECT c1,
       c2 /* this is a comment */
FROM t
WHERE c1 > 1

sql = """
    select c1, c2 /* this is a comment */
    from student S join (select c1, c2, c3, c4, case when c5 >0 then 1 else 0 end as c5 from class where id > 0) C on S.id = C.id 
    join table3 on s.id = table3.id where c1 > 1
    """
sql_fmt = sp.format(
    sql,
    keyword_case="upper",
    identifier_case="lower",
    strip_comments=False,
    reindent=True,
    indent_width=2,
)
print(sql_fmt)


SELECT c1,
       c2 /* this is a comment */
FROM student s
JOIN
  (SELECT c1,
          c2,
          c3,
          c4,
          CASE
              WHEN c5 >0 THEN 1
              ELSE 0
          END AS c5
   FROM CLASS
   WHERE id > 0) c ON s.id = c.id
JOIN table3 ON s.id = table3.id
WHERE c1 > 1

JavaScipt/TypeScript Libraries¶

https://github.com/zeroturnaround/sql-formatter

Java Libraries¶

vertical-blank/sql-formatter is Java port of the great JS-based SQL formatter zeroturnaround/sql-formatter (which is itself a JS port of the PHP-based SQL formatter.)

!wget https://repo1.maven.org/maven2/com/github/vertical-blank/sql-formatter/1.0.3/sql-formatter-1.0.3.jar

--2020-12-04 19:58:11--  https://repo1.maven.org/maven2/com/github/vertical-blank/sql-formatter/1.0.3/sql-formatter-1.0.3.jar
Resolving repo1.maven.org (repo1.maven.org)... 151.101.52.209
Connecting to repo1.maven.org (repo1.maven.org)|151.101.52.209|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 53175 (52K) [application/java-archive]
Saving to: ‘sql-formatter-1.0.3.jar.1’

sql-formatter-1.0.3 100%[===================>]  51.93K   288KB/s    in 0.2s    

2020-12-04 19:58:12 (288 KB/s) - ‘sql-formatter-1.0.3.jar.1’ saved [53175/53175]

!pip3 install JPype1

Requirement already satisfied: JPype1 in /usr/local/lib/python3.8/site-packages (1.1.2)

from pathlib import Path
import jpype
import jpype.imports

jpype.addClassPath(Path("sql-formatter-1.0.3.jar").resolve())
jpype.startJVM()
print(jpype.java.lang.System.getProperty("java.class.path"))
import com.github.vertical_blank.sqlformatter.SqlFormatter as SqlFormatter

/Users/dclong/archives/blog/misc/content/sql-formatter-1.0.3.jar

sql = """
    select c1, c2 /* this is a comment */
    from t where c1 > 1
    """
print(SqlFormatter.format(sql))

select
  c1,
  c2
  /* this is a comment */
from
  t
where
  c1 > 1

help(SqlFormatter.format)

Help on function format in module _jpype:

format(*args) -> jpype._jstring.java.lang.String
    Java method dispatch 'format' for 'com.github.vertical_blank.sqlformatter.SqlFormatter'
    
    Static Methods:
      * java.lang.String format(java.lang.String, java.lang.String, java.util.Map)
      * java.lang.String format(java.lang.String, java.util.List)
      * java.lang.String format(java.lang.String, java.lang.String, java.util.List)
      * java.lang.String format(java.lang.String, com.github.vertical_blank.sqlformatter.core.FormatConfig)
      * java.lang.String format(java.lang.String)
      * java.lang.String format(java.lang.String, java.util.Map)
      * java.lang.String format(java.lang.String, java.lang.String)
    
    Returns:
      java.lang.String

sql = """
    select c1, c2 /* this is a comment */
    from student S join (select c1, c2, c3, c4, case when c5 >0 then 1 else 0 end as c5 from class where id > 0) C on S.id = C.id where c1 > 1
    """
print(SqlFormatter.format(sql, "    "))

select
    c1,
    c2
    /* this is a comment */
from
    student S
    join (
        select
            c1,
            c2,
            c3,
            c4,
            case
                when c5 > 0 then 1
                else 0
            end as c5
        from
            class
        where
            id > 0
    ) C on S.id = C.id
where
    c1 > 1

Online SQL Formatting Tools¶

You can use online SQL formatters to help you format SQL queries.

SQL Style and Formatting¶

https://www.sqlstyle.guide/

|| works differently on different date types. This is because different data types have different (default) padding/formatting styles. You can manually cast date types to produce the format you want.
some people like to put , before column names in select, I don’t think this is a good practices, as what if we want to remove the first column? it similarly inconvenient to removing the last column when put comma after column names

References¶

https://stackoverflow.com/questions/3310188/what-free-sql-formatting-tools-exist

http://stackoverflow.com/questions/1394998/parsing-sql-with-python