prettier-plugin-sql-cst

Prettier plugin SQL-CST

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

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:

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

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

SELECT id, client.name, client.priority
FROM client
WHERE client.id IN (12, 18, 121)

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

SELECT
  client.id,
  client.name AS client_name,
  organization.name AS org_name,
  count(order.id) AS nr_of_orders
FROM
  client
  LEFT JOIN organization ON client.organization_id = organization.id
  LEFT JOIN order ON order.client_id = client.id
WHERE
  client.status = 'active'
  AND client.id IN (28, 214, 457)
  AND order.status IN ('active', 'pending', 'processing')
GROUP BY client.id
ORDER BY client.name
LIMIT 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.

Non-whitespace formatting

Currently this plugin preserves most of the syntax elements and concentrates mainly on the layout of whitespace. For example it does not add/remove paratheses.

There are some opinionated non-whitespace changes though:

• UPPERCASE all keywords.
• Add AS keyword to all alias definitions.

Getting started

Install it as any other Prettier plugin:

npm install --save-dev prettier prettier-plugin-sql-cst

Then use it on SQL files though 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:

{
  "overrides": [
    {
      "files": ["*.sql"],
      "options": { "parser": "bigquery" }
    }
  ]
}

The plugin provides the following parsers:

• sqlite
• bigquery

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.

Limitations and development status

Currently this plugin supports most of SQLite syntax. (It also supports some BigQuery, but only the syntax that's subset of SQLite.) Formatting of the following SQL statements is fully implemented:

• SELECT
• UPDATE
• INSERT
• DELETE
• CREATE / DROP / ALTER TABLE
• CREATE / DROP VIEW
• CREATE / DROP INDEX