Format whitespace in a SQL query to make it more readable
npm install prettier-sqlIt started as a port of a [PHP Library][], but has since considerably diverged.
Prettier SQL supports the following dialects:
- sql - [Standard SQL][]
- bigquery - [GCP BigQuery][]
- db2 - [IBM DB2][]
- hive - [Apache Hive][]
- mariadb - [MariaDB][]
- mysql - [MySQL][]
- n1ql - [Couchbase N1QL][]
- plsql - [Oracle PL/SQL][]
- postgresql - [PostgreSQL][]
- redshift - [Amazon Redshift][]
- spark - [Spark][]
- tsql - [SQL Server Transact-SQL][tsql]
It does not support:
- Stored procedures.
- Changing of the delimiter type to something else than ;.
ā Try the demo.
- Install
- Documentation
- Usage
- Usage as library
- Usage from command line
- Usage without NPM
- Usage with VSCode
- Contributing
Get the latest version from NPM:
``sh`
npm install prettier-sql
Also available with yarn:
`sh`
yarn add prettier-sql
You can read more about how the library works in DOC.md
`js
import { format } from 'prettier-sql';
console.log(format('SELECT * FROM tbl'));
`
This will output:
`sql`
SELECT
*
FROM
tbl
You can also pass in configuration options:
`js`
format('SELECT * FROM tbl', {
language: 'spark', // Defaults to "sql" (see the above list of supported dialects)
indent: ' ', // Defaults to two spaces
uppercase: false, // Defaults to true
linesBetweenQueries: 2, // Defaults to 1
});
`js
// Named placeholders
format("SELECT * FROM tbl WHERE foo = @foo", {
params: {foo: "'bar'"}
}));
// Indexed placeholders
format("SELECT * FROM tbl WHERE foo = ?", {
params: ["'bar'"]
}));
`
Both result in:
`sql`
SELECT
*
FROM
tbl
WHERE
foo = 'bar'
The CLI tool will be installed under prettier-sqlnpx prettier-sql
and may be invoked via :
`sh`
prettier-sql -h
`
usage: sqlfmt.js [-h] [-o OUTPUT] \
[-l {bigquery,db2,hive,mariadb,mysql,n1ql,plsql,postgresql,redshift,spark,sql,tsql}] [-c CONFIG] [--version] [FILE]
Prettier SQL
positional arguments:
FILE Input SQL file (defaults to stdin)
optional arguments:
-h, --help show this help message and exit
-o, --output OUTPUT
File to write SQL output (defaults to stdout)
-l, --language {bigquery,db2,hive,mariadb,mysql,n1ql,plsql,postgresql,redshift,spark,sql,tsql}
SQL dialect (defaults to standard sql)
-c, --config CONFIG
Path to config json file (will use default configs if unspecified)
--version show program's version number and exit
`
By default, the tool takes queries from stdin and processes them to stdout but
one can also name an input file name or use the --output option.
`sh`
echo 'select * from tbl where id = 3' | prettier-sql
`sql`
SELECT
*
FROM
tbl
WHERE
id = 3
The tool also accepts a JSON config file with the --config option that takes this form: \
All fields are optional and all fields that are not specified will be filled with their default values
`ts`
{
"indent": string,
"uppercase": boolean,
"keywordPosition": "standard" | "tenSpaceLeft" | "tenSpaceRight",
"newline": "always" | "lineWidth" | "never" | number,
"breakBeforeBooleanOperator": boolean,
"aliasAs": "always" | "select" | "never",
"tabulateAlias": boolean,
"commaPosition": "before" | "after" | "tabular",
"parenOptions": {
"openParenNewline": boolean,
"closeParenNewline": boolean
},
"lineWidth": number,
"linesBetweenQueries": number,
"denseOperators": boolean,
"semicolonNewline": boolean,
}
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