agrawroh / sql-formatter

A whitespace formatter for different query languages

SQL Formatter

SQL Formatter is a JavaScript library and command line tool for pretty-printing SQL queries. It started as a Javascript port of a PHP Library, but has diverged considerably, and been forked/joined multiple times in the past. The current formatter (@gwax/sql-formatter) forked from zeroturnaround/sql-formatter with code consolidated from kufii/sql-formatter-plus and a number of other forks scattered around GitHub.

SQL Formatter supports Standard SQL, Couchbase N1QL, IBM DB2, Oracle PL/SQL, Amazon Redshift, and Spark dialects.

→ Try the demo.

Install

Get the latest version from NPM:

npm install @gwax/sql-formatter

Command Line Interface

The CLI tool will be installed under @gwax/sql-formatter and under sql-formatter and may be invoked via npx @gwax/sql-formatter:

npx @gwax/sql-formatter -h

usage: sql-formatter [-h] [-v] [-f FILE] [-o OUTPUT]
                     [-l {db2,n1ql,pl/sql,plsql,redshift,spark,sql}]
                     [-i N | -t] [-u] [--lines-between-queries N]


SQL Formatter

Optional arguments:
  -h, --help            Show this help message and exit.
  -v, --version         Show program's version number and exit.
  -f FILE, --file FILE  Input SQL file (defaults to stdin)
  -o OUTPUT, --output OUTPUT
                        File to write SQL output (defaults to stdout)
  -l {db2,n1ql,pl/sql,plsql,redshift,spark,sql}, --langauge {db2,n1ql,pl/sql,plsql,redshift,spark,sql}
                        SQL Formatter dialect (defaults to basic sql)
  -i N, --indent N      Number of spaces to indent query blocks (defaults to
                        2)
  -t, --tab-indent      Indent query blocks with tabs instead of spaces
  -u, --uppercase       Capitalize language keywords
  --lines-between-queries N
                        How many newlines to insert between queries
                        (separated by ";")

By default, the tool takes queries from stdin and processes them to stdout but the -f/--file and -o/--output flags can be used to alter this behavior.

echo 'select * from tbl where id = 3' | npx @gwax/sql-formatter -u

SELECT
  *
FROM
  tbl
WHERE
  id = 3

Usage

import sqlFormatter from '@gwax/sql-formatter';

console.log(sqlFormatter.format('SELECT * FROM tbl'));

This will output:

SELECT
  *
FROM
  tbl

You can also pass in configuration options:

sqlFormatter.format('SELECT * FROM tbl', {
  language: 'spark', // Defaults to "sql"
  indent: '    ', // Defaults to two spaces
  uppercase: bool, // Defaults to false
  linesBetweenQueries: 2, // Defaults to 1
});

Currently just six SQL dialects are supported:

• sql - Standard SQL
• n1ql - Couchbase N1QL
• db2 - IBM DB2
• pl/sql - Oracle PL/SQL
• redshift - Amazon Redshift
• spark - Spark

Placeholders replacement

// Named placeholders
sqlFormatter.format('SELECT * FROM tbl WHERE foo = @foo', {
  params: { foo: "'bar'" },
});

// Indexed placeholders
sqlFormatter.format('SELECT * FROM tbl WHERE foo = ?', {
  params: ["'bar'"],
});

Both result in:

SELECT
  *
FROM
  tbl
WHERE
  foo = 'bar'

Usage without NPM

If you don't use a module bundler, clone the repository, run npm install and grab a file from /dist directory to use inside a <script> tag. This makes SQL Formatter available as a global variable window.sqlFormatter.

Contributing

Make sure to run all checks:

npm run check

...and you're ready to poke us with a pull request.

License

MIT