psqlformat

psqlformat

A PostgreSQL SQL syntax formatter that supports a CLI and library / module interface.

This package is a wrapper for darold/pgFormatter and requires Perl to be available.

Command Line Interface

To run psqlformat on Node.js, install it first with the following command:

npm i -g psqlformat

To run it, use this command format:

psqlformat [options] [file|dir|glob]*

Examples:

psqlformat --write --spaces=2 query.sql
psqlformat --commaEnd --spaces=4 "db/*.sql"
psqlformat --commaEnd --spaces=4 "db/**/*.sql"

When passing a glob as a parameter, it will be expanded by your shell. If you want to use node glob syntax, you have to quote your parameter (using double quotes if you need it to run in Windows), as follows:

psqlformat --noComment "db/**"

npx

Bundled with npm starting in version 5.2, the npx tool can be used to run psqlformat without installing it globally. If you have installed psqlformat locally (npm install psqlformat), npx will use the installed version and if you have not installed it, the latest version will be pulled down and used automatically. To use this tool, simply prefix psqlformat with npx like this:

npx psqlformat --write --spaces=2 query.sql

Options

The command line utility has several options. You can view the options by running psqlformat -h.

Usage: pgformatter [options] <file/glob ...>

By default, output is written to stdout. (use --write option to edit files
in-place)


Options:
  --help          Show help                                            [boolean]
  --version       Show version number                                  [boolean]
  --write         Edit files in-place. (Beware!)                       [boolean]
  --spaces        Number of spaces to indent the code      [number] [default: 4]
  --maxLength     Maximum length of a query                             [number]
  --commaStart    Use preceding comma in parameter list                [boolean]
  --commaBreak    In insert statement, add a newline after each comma  [boolean]
  --commaEnd      Use trailing comma in parameter list [boolean] [default: true]
  --noComment     Remove any comments                                  [boolean]
  --functionCase  Case of the function names
         [string] [choices: "unchanged", "lowercase", "uppercase", "capitalize"]
                                                          [default: "unchanged"]
  --noGrouping    Add a newline between statements in transaction regroupement
                                                      [boolean] [default: false]
  --keywordCase   Case of the reserved keywords
         [string] [choices: "unchanged", "lowercase", "uppercase", "capitalize"]
                                                          [default: "uppercase"]
  --formatType    Use another formatting type for some statements      [boolean]
  --wrapLimit     Wrap queries at a certain length                      [number]
  --placeholder   Regex to find code that must not be changed           [string]
  --perlBinPath   The path to the perl executable     [string] [default: "perl"]

Module usage

psqlformat can also be used as a module so that it can be integrated into an existing project. Simply install it locally like this:

npm install psqlformat

Then, require it with require("psqlformat") and use either the formatSql or formatFiles method. Here are the method signatures in TypeScript declaration file format:

/**
 *
 * @param fileOrGlob The file path or glob to use (i.e. /tmp/query.sql or *.sql)
 * @param options
 */
export declare function formatFiles(filesOrGlobs: string | string[], editInPlace: boolean, options?: IOptions, log?: (text: string) => void): string;

/**
 * Format SQL
 * @param sqlText The SQL to be formatted
 * @param options
 */
export declare function formatSql(sqlText: string, options?: IOptions): string;

Example usage:

const psqlformat = require("psqlformat");

// Format SQL text
let formatted = psqlformat.formatSql("select id from people", {
  spaces: 2
 
  /* Other available options:
  maxLength
  commaStart
  commaBreak
  commaEnd
  noComment
  functionCase
  keywordCase
  formatType
  placeholder
  perlBinPath
  */
});

console.log(formatted);

/* Expected Output:
SELECT
  id
FROM
  people
*/

// Format a file
const path = require("path");
psqlformat.formatFiles(path.resolve(__dirname, "query.sql"), true, {    
  spaces: 3 
});

// query.sql file should have been edited in-place.

TypeScript

psqlformat is written using TypeScript and has declaration files (.d.ts) available so that if you are using TypeScript in your own project, you can import psqlformat. The entry declaration file, dist/index.d.ts, is specified in package.json file under the "types" field and should automatically be recognized by the TypeScript compiler.