1 | <a href='https://github.com/sql-formatter-org/sql-formatter'><img src="static/prettier-sql-clean.svg" width="128"/></a>
|
2 |
|
3 | # SQL Formatter [![NPM version](https://img.shields.io/npm/v/sql-formatter.svg)](https://npmjs.com/package/sql-formatter) ![Build status](https://img.shields.io/github/workflow/status/sql-formatter-org/sql-formatter/coveralls/master?label=Build&logo=Github) ![Coverage status](https://img.shields.io/coveralls/github/sql-formatter-org/sql-formatter?branch=master&label=Coverage&logo=coveralls&style=plastic) [![VSCode](https://img.shields.io/visual-studio-marketplace/v/inferrinizzard.prettier-sql-vscode?label=vscode)](https://marketplace.visualstudio.com/items?itemName=inferrinizzard.prettier-sql-vscode)
|
4 |
|
5 | **SQL Formatter** is a JavaScript library for pretty-printing SQL queries.
|
6 |
|
7 | It started as a port of a [PHP Library][], but has since considerably diverged.
|
8 |
|
9 | It supports various SQL dialects:
|
10 | GCP BigQuery, IBM DB2, Apache Hive, MariaDB, MySQL, Couchbase N1QL, Oracle PL/SQL, PostgreSQL, Amazon Redshift, Spark, SQL Server Transact-SQL.
|
11 | See [language option docs](docs/language.md) for more details.
|
12 |
|
13 | It does not support:
|
14 |
|
15 | - Stored procedures.
|
16 | - Changing of the delimiter type to something else than `;`.
|
17 |
|
18 | → [Try the demo.](https://sql-formatter-org.github.io/sql-formatter)
|
19 |
|
20 | ## Install
|
21 |
|
22 | Get the latest version from NPM:
|
23 |
|
24 | ```sh
|
25 | npm install sql-formatter
|
26 | ```
|
27 |
|
28 | Also available with yarn:
|
29 |
|
30 | ```sh
|
31 | yarn add sql-formatter
|
32 | ```
|
33 |
|
34 | ## Usage
|
35 |
|
36 | ### Usage as library
|
37 |
|
38 | ```js
|
39 | import { format } from 'sql-formatter';
|
40 |
|
41 | console.log(format('SELECT * FROM tbl'));
|
42 | ```
|
43 |
|
44 | This will output:
|
45 |
|
46 | ```sql
|
47 | SELECT
|
48 | *
|
49 | FROM
|
50 | tbl
|
51 | ```
|
52 |
|
53 | You can also pass in configuration options:
|
54 |
|
55 | ```js
|
56 | format('SELECT * FROM tbl', {
|
57 | language: 'spark',
|
58 | tabWidth: 2,
|
59 | keywordCase: 'upper',
|
60 | linesBetweenQueries: 2,
|
61 | });
|
62 | ```
|
63 |
|
64 | ### Placeholders replacement
|
65 |
|
66 | In addition to formatting, this library can also perform placeholder replacement in prepared SQL statements:
|
67 |
|
68 | ```js
|
69 | format('SELECT * FROM tbl WHERE foo = ?', {
|
70 | params: ["'bar'"],
|
71 | });
|
72 | ```
|
73 |
|
74 | Results in:
|
75 |
|
76 | ```sql
|
77 | SELECT
|
78 | *
|
79 | FROM
|
80 | tbl
|
81 | WHERE
|
82 | foo = 'bar'
|
83 | ```
|
84 |
|
85 | For more details see [docs of params option.](docs/params.md)
|
86 |
|
87 | ### Usage from command line
|
88 |
|
89 | The CLI tool will be installed under `sql-formatter`
|
90 | and may be invoked via `npx sql-formatter`:
|
91 |
|
92 | ```sh
|
93 | sql-formatter -h
|
94 | ```
|
95 |
|
96 | ```
|
97 | usage: sql-formatter [-h] [-o OUTPUT] \
|
98 | [-l {bigquery,db2,hive,mariadb,mysql,n1ql,plsql,postgresql,redshift,spark,sql,sqlite,tsql}] [-c CONFIG] [--version] [FILE]
|
99 |
|
100 | SQL Formatter
|
101 |
|
102 | positional arguments:
|
103 | FILE Input SQL file (defaults to stdin)
|
104 |
|
105 | optional arguments:
|
106 | -h, --help show this help message and exit
|
107 | -o, --output OUTPUT
|
108 | File to write SQL output (defaults to stdout)
|
109 | -l, --language {bigquery,db2,hive,mariadb,mysql,n1ql,plsql,postgresql,redshift,spark,sql,sqlite,tsql}
|
110 | SQL dialect (defaults to standard sql)
|
111 | -c, --config CONFIG
|
112 | Path to config json file (will use default configs if unspecified)
|
113 | --version show program's version number and exit
|
114 | ```
|
115 |
|
116 | By default, the tool takes queries from stdin and processes them to stdout but
|
117 | one can also name an input file name or use the `--output` option.
|
118 |
|
119 | ```sh
|
120 | echo 'select * from tbl where id = 3' | sql-formatter
|
121 | ```
|
122 |
|
123 | ```sql
|
124 | select
|
125 | *
|
126 | from
|
127 | tbl
|
128 | where
|
129 | id = 3
|
130 | ```
|
131 |
|
132 | The tool also accepts a JSON config file with the `--config` option that takes this form:
|
133 |
|
134 | ```ts
|
135 | {
|
136 | "language": "spark",
|
137 | "tabWidth": 2,
|
138 | "keywordCase": "upper",
|
139 | "linesBetweenQueries": 2,
|
140 | }
|
141 | ```
|
142 |
|
143 | All fields are optional and all fields that are not specified will be filled with their default values.
|
144 |
|
145 | ### Configuration options
|
146 |
|
147 | - [**`language`**](docs/language.md) the SQL dialect to use.
|
148 | - [**`tabWidth`**](docs/tabWidth.md) amount of indentation to use.
|
149 | - [**`useTabs`**](docs/useTabs.md) to use tabs for indentation.
|
150 | - [**`keywordCase`**](docs/keywordCase.md) uppercases or lowercases keywords.
|
151 | - [**`indentStyle`**](docs/indentStyle.md) defines overall indentation style.
|
152 | - [**`multilineLists`**](docs/multilineLists.md) determines when to break lists of items to multiple lines.
|
153 | - [**`logicalOperatorNewline`**](docs/logicalOperatorNewline.md) newline before or after boolean operator (AND, OR, XOR).
|
154 | - [**`aliasAs`**](docs/aliasAs.md) enforces or forbids use of AS keyword for aliases.
|
155 | - [**`tabulateAlias`**](docs/tabulateAlias.md) aligns column aliases vertically.
|
156 | - [**`commaPosition`**](docs/commaPosition.md) where to place the comma in column lists.
|
157 | - [**`newlineBeforeOpenParen`**](docs/newlineBeforeOpenParen.md) placement of opening parenthesis.
|
158 | - [**`newlineBeforeCloseParen`**](docs/newlineBeforeCloseParen.md) placement of closing parenthesis.
|
159 | - [**`expressionWidth`**](docs/expressionWidth.md) maximum number of characters in parenthesized expressions to be kept on single line.
|
160 | - [**`linesBetweenQueries`**](docs/linesBetweenQueries.md) how many newlines to insert between queries.
|
161 | - [**`denseOperators`**](docs/denseOperators.md) packs operators densely without spaces.
|
162 | - [**`newlineBeforeSemicolon`**](docs/newlineBeforeSemicolon.md) places semicolon on separate line.
|
163 | - [**`params`**](docs/params.md) collection of values for placeholder replacement.
|
164 |
|
165 | ### Usage without NPM
|
166 |
|
167 | 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.
|
168 | This makes SQL Formatter available as a global variable `window.sqlFormatter`.
|
169 |
|
170 | ### Usage in editors
|
171 |
|
172 | - [VSCode extension](https://marketplace.visualstudio.com/items?itemName=inferrinizzard.prettier-sql-vscode)
|
173 | - [Vim extension](https://github.com/fannheyward/coc-sql/)
|
174 |
|
175 | ## Contributing
|
176 |
|
177 | Please see [CONTRIBUTING.md](CONTRIBUTING.md)
|
178 |
|
179 | You can read more about how the library works in [DOC.md](DOC.md)
|
180 |
|
181 | ## License
|
182 |
|
183 | [MIT](LICENSE)
|
184 |
|
185 | [php library]: https://github.com/jdorn/sql-formatter
|