• Stars
    star
    1,114
  • Rank 41,670 (Top 0.9 %)
  • Language
    TypeScript
  • Created about 8 years ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

A whitespace formatter for different query languages

SQL Formatter NPM version Build status Coverage Status

SQL Formatter is a JavaScript library for pretty-printing SQL queries.

It started as a port of a PHP Library, but has since considerably diverged.

It supports various SQL dialects: GCP BigQuery, IBM DB2, Apache Hive, MariaDB, MySQL, Couchbase N1QL, Oracle PL/SQL, PostgreSQL, Amazon Redshift, SingleStoreDB, Snowflake, Spark, SQL Server Transact-SQL, Trino/Presto. See language option docs for more details.

It does not support:

  • Stored procedures.
  • Changing of the delimiter type to something else than ;.

β†’ Try the demo.

Install

Get the latest version from NPM:

npm install sql-formatter

Also available with yarn:

yarn add sql-formatter

Usage

Usage as library

import { format } from 'sql-formatter';

console.log(format('SELECT * FROM tbl', { language: 'mysql' }));

This will output:

SELECT
  *
FROM
  tbl

You can also pass in configuration options:

format('SELECT * FROM tbl', {
  language: 'spark',
  tabWidth: 2,
  keywordCase: 'upper',
  linesBetweenQueries: 2,
});

Placeholders replacement

In addition to formatting, this library can also perform placeholder replacement in prepared SQL statements:

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

Results in:

SELECT
  *
FROM
  tbl
WHERE
  foo = 'bar'

For more details see docs of params option.

Usage from command line

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

sql-formatter -h
usage: sql-formatter [-h] [-o OUTPUT] \
[-l {bigquery,db2,hive,mariadb,mysql,n1ql,plsql,postgresql,redshift,singlestoredb,snowflake,spark,sql,sqlite,transactsql,trino,tsql}] [-c CONFIG] [--version] [FILE]

SQL Formatter

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)
  --fix           Update the file in-place
  -l, --language  {bigquery,db2,hive,mariadb,mysql,n1ql,plsql,postgresql,redshift,singlestoredb,snowflake,spark,sql,sqlite,trino,tsql}
                    SQL dialect (defaults to basic 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.

echo 'select * from tbl where id = 3' | sql-formatter
select
  *
from
  tbl
where
  id = 3

The tool also accepts a JSON config file with the --config option that takes this form:

{
  "language": "spark",
  "tabWidth": 2,
  "keywordCase": "upper",
  "linesBetweenQueries": 2
}

All fields are optional and all fields that are not specified will be filled with their default values.

Configuration options

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.

Usage in editors

Frequently Asked Questions

Parse error: Unexpected ... at line ...

The most common cause is that you haven't specified an SQL dialect. Instead of calling the library simply:

format('select [col] from tbl');
// Throws: Parse error: Unexpected "[col] from" at line 1 column 8

pick the proper dialect, like:

format('select [col] from tbl', { language: 'transactsql' });

Or when using the VSCode extension: Settings -> Prettier-SQL: SQLFlavourOverride.

Module parse failed: Unexpected token

This typically happens when bundling an appication with Webpack. The cause is that Babel (through babel-loader) is not configured to support class properties syntax:

    | export default class ExpressionFormatter {
    >   inline = false;

This syntax is widely supported in all major browsers (except old IE) and support for it is included to the default @babel/preset-env.

Possible fixes:

  • Update to newer Babel / Webpack
  • Switch to @babel/preset-env
  • Include plugin @babel/plugin-proposal-class-properties

The future

The development of this formatter is currently in maintenance mode. Bugs will get fixed if feasible, but new features will likely not be added.

I have started a new SQL formatting tool: prettier-plugin-sql-cst.

  • It solves several problems which can't be fixed in SQL Formatter because of fundamental problems in its arhictecture.
  • It makes use of the Prettier layout algorithm, doing a better job of splitting long expressions to multiple lines.
  • It takes much more opinionated approach to SQL formatting, giving only a very limited set of options to adjust the code style.
  • It already has full support for SQLite and BigQuery syntax. It should work for the most common SQL code in various other dialects.

Give it a try if you'd like to take your SQL auto-formatting to the next level.

Contributing

Please see CONTRIBUTING.md

License

MIT

More Repositories

1

zt-zip

ZeroTurnaround ZIP Library
Java
1,373
star
2

zt-exec

ZeroTurnaround Process Executor
Java
876
star
3

zt-process-killer

ZeroTurnaround Process Killer
Java
126
star
4

jvm-languages-report

Combined repository for the JVM languages report by RebelLabs
Java
63
star
5

callspy

A simple tracing agent
Java
63
star
6

gradle-jrebel-plugin

The plugin generates rebel.xml configuration file for the Gradle-based project
Java
51
star
7

maven-jrebel-plugin

Generates rebel.xml configuration file for the maven project
Java
51
star
8

stardate-converter

Converts common date to Star Trek stardate
JavaScript
20
star
9

xrebel-docker-demo

Demo project for showcasing XRebel features with distributed apps
Shell
9
star
10

jenkins-reporter

Generate nice reports of your Jenkins views.
Java
7
star
11

giantrobots

7
star
12

isJRebel

Check if a String is equal to JRebel
Java
7
star
13

bad-classes

Utility to debug UnsupportedClassVersionError in Java
Java
6
star
14

zt-hock

JavaScript
4
star
15

strict-spies

A strict alternative for Jasmine spies
JavaScript
4
star
16

atg-hello-demo

Simple demo application for Oracle ATG Web Commerce
Java
4
star
17

build-flow-test-aggregator

Java
4
star
18

jenkins-liverebel-plugin

Jenkins LiveRebel Plugin
Java
3
star
19

cluster-stats

Jenkins Cluster Statistics Plugin
Java
2
star
20

jrebel-tdd

Java
2
star
21

xrebel-demo-supplements

Application included into https://github.com/zeroturnaround/xrebel-docker-demo
Java
2
star
22

eslint-config-zt

ESLint shareable config for ZeroTurnaround JavaScript projects
JavaScript
1
star
23

xr-demo-answers

JavaScript
1
star
24

lunch-bot

JavaScript
1
star
25

rebellabs-report-2017-analysis-app

An analysis app for the data received for the RebelLabs Developer Productivity Report 2017
Java
1
star
26

zt-react-components

Commonly used React components for ZeroTurnaround products
JavaScript
1
star
27

aws-ecs-deploy-jenkins-plugin

Java
1
star
28

netbeans-jrebel-open-plugin

JRebel installer plugin for NetBeans
Java
1
star