Prettier plugin SQL-CST

A Prettier plugin for SQL that uses sql-parser-cst and the actual Prettier formatting algorithm.

Try it live!

Like Prettier for JavaScript, this plugin formats SQL expressions differently depending on their length. A short SQL query will be formatted on a single line:

1SELECT a, b, c FROM tbl WHERE x > 10;

A longer query, will get each clause printed on a separate line:

1SELECT id, client.name, client.priority
2FROM client
3WHERE client.id IN (12, 18, 121);

An even longer one gets the contents of each clause indented:

1SELECT
2  client.id,
3  client.name AS client_name,
4  organization.name AS org_name,
5  count(orders.id) AS nr_of_orders
6FROM
7  client
8  LEFT JOIN organization ON client.organization_id = organization.id
9  LEFT JOIN orders ON orders.client_id = client.id
10WHERE
11  client.status = 'active'
12  AND client.id IN (28, 214, 457)
13  AND orders.status IN ('active', 'pending', 'processing')
14GROUP BY client.id
15ORDER BY client.name
16LIMIT 100;

Formatting philosophy

• Adapt formatting based on expression length.
• Stick to one style and avoid configuration options.
• Format embedded languages (like JSON data and JavaScript programs).
• When unsure, preserve existing syntax.

Currently this plugin preserves most of the syntax elements and concentrates mainly on the layout of whitespace.

See STYLE_GUIDE for overview of the SQL formatting style used.

Getting started

Install it as any other Prettier plugin:

npm install --save-dev prettier prettier-plugin-sql-cst
# or
pnpm add --save-dev prettier prettier-plugin-sql-cst
# or
yarn add --dev prettier prettier-plugin-sql-cst

Then use it on SQL files through Prettier command line tool or Prettier extension for your editor of choice.

Choosing an SQL dialect

By default the plugin will determine SQL dialect based on file extension:

• .sql or .sqlite - SQLite
• .bigquery - BigQuery

You can override this behavior with a prettier configuration in .prettierrc.json file:

1{
2  "plugins": ["prettier-plugin-sql-cst"],
3  "overrides": [
4    {
5      "files": ["*.sql"],
6      "options": { "parser": "bigquery" }
7    }
8  ]
9}

Or you could also store it inside your package.json:

1{
2  "prettier": {
3    "plugins": ["prettier-plugin-sql-cst"],
4    "overrides": [
5      {
6        "files": ["*.sql"],
7        "options": { "parser": "bigquery" }
8      }
9    ]
10  }
11}

The plugin provides the following parsers:

• sqlite
• bigquery
• postgresql (experimental! expect crashes)
• mysql (experimental! expect crashes)
• mariadb (experimental! expect crashes)

Configuration

The standard Prettier options printWidth, tabWidth, useTabs apply. There are also some SQL-specific options:

API Option	Default	Description
sqlKeywordCase	upper	Converts SQL keywords to upper or lower case, or preserve existing. Note that for now preserve is somewhat incompatible with sqlCanonicalSyntax: true (e.g. the added AS keywords will always be in uppercase).
sqlParamTypes	[]	Array of bound parameter types: ?, ?nr, $nr, :name, @name, $name.
sqlCanonicalSyntax	true	When enabled, performs some opinionated changes of keywords and operators, like enforcing the use of AS in aliases and replacing <> comparisons with !=. See STYLE_GUIDE for more details. (Since 0.11.0)
sqlAcceptUnsupportedGrammar	false	Normally when the plugin encounters SQL syntax it doesn't support it will throw an error and won't format anything at all. With this option enabled, it will skip over SQL statements it doesn't recognize, leaving them as-is.

Usage inside VSCode

To use this plugin inside VSCode, install the Prettier VSCode extension.

Follow Prettier VSCode docs to configure it as the default formatter.

You might also need to configure prettier.documentSelectors to enable Prettier for *.sql files.

To see what Prettier is, or is not doing - open the VSCode Output window and select the Prettier dropdown. On format, the window should show your inferredParser. It should reconfirm that by showing "parser": "sqlite" (or whichever perser you have configured inside your prettier config overrides section) and plugins with the path to this package. If you don't see that part, Prettier not using this package.

FAQ

The SQL dialect I'm using is not supported. Can you add support for it?

Support for new SQL dialects depends on these dialects being supported by sql-parser-cst. If you really want to, you can open a new issue for that in the parser repo. But be aware that implementing parser support for new dialects takes a lot of work. As long as the ongoing implementation of PostgreSQL, MySQL and MariaDB is not finished, it's unlikely that work on any other dialect will start.

How can I format SQL strings inside JavaScript files?

Use prettier-plugin-embed together with prettier-plugin-sql-cst.

Limitations and development status

Currently this plugin supports two SQL dialects:

• SQLite - full support.
• BigQuery - full support.

It also has experimental support for the following dialects:

• PostgreSQL
• MySQL
• MariaDB

The main limitation is that the parser does not support full syntax of these dialects. One should expect the parser to crash for syntax that's more specific to these dialects. But as long as the parsing succeeds, the formatting should also succeed. Mainly one can expect the formatting of SELECT, INSERT, UPDATE, DELETE statements to work.

To overcome this limitation you can enable the sqlAcceptUnsupportedGrammar option, which will make the plugin skip the SQL statements it doesn't recognize.

The specifics of the SQL formatting style are still very much subject to change. Though the general principles should be mostly in place by now.