node package manager
Painless code sharing. npm Orgs help your team discover, share, and reuse code. Create a free org »

poor-mans-t-sql-formatter-cli

Poor Man's T-SQL Formatter - node command-line formatter

A command-line SQL formatter that runs in pretty much any environment (as long as node.js is installed).

It is based on the Poor Man's T-SQL Formatter NPM package (poor-mans-t-sql-formatter), which in turn is based on the C# library of the same name ( https://github.com/TaoK/PoorMansTSqlFormatter ).

This formatter should be equivalent in functionality to the C# command-line formatter that's existed for a few years (downloadable at http://architectshack.com/PoorMansTSqlFormatter.ashx), with two major differences:

  • It's super easy to install, especially in unixey environments - as long as Node.js is available
  • It's quite a bit slower than the .Net-based formatter.

Installation

(assuming node.js is installed)

npm install --global poor-mans-t-sql-formatter-cli

Usage

piped/stdin input & stdout output:

echo "select a from b join c on b.id = c.id where abc = 123 and def = N'whatêver' " | sqlformat

file input & output:

echo "with a as (select 1 as b) select * from a cros join c" > testfile.sql
sqlformat -f testfile.sql -g testfile.sql
cat testfile.sql

Options

This command-line formatter will exit with a non-0 exit code if the SQL parsing "gets in trouble" - for example if it encounters an unfinished "IF" statement, suggesting that something about the SQL was not correctly understood/parsed, and may therefore "come out wrong". There's an option to disable this behavior if the SQL is known to be shady, or the parsing confusion is known to be innocuous.

If the parsing is aborted, any specified "output file" will be left untouched.

Command-line-utility-specific options:

Option Description Type Default
--inputFile Read input to be formatted from a file rather than stdin (typed or piped input) string
--outputFile Write formatted output to a file - like shell redirection of stdout, except on error it leaves the file untouched string
--ignoreErrors Return 0 (success) exit code even if parsing failed (and so the formatted output is suspect) bool
--inputEncoding Use a specific character encoding supported by node for input - basically utf-16le or utf-8 string utf-8
--outputEncoding Use a specific character encoding supported by node for output - basically utf-16le or utf-8 string utf-8
--forceOutputBOM Add a byte order mark (BOM) to the start of the output bool

Standard formatter options:

(please note, boolean options that normally default to "true" in the library have been flipped with a "no" prefix, following unixey command-line parameter conventions)

Option Description Type Default
--indent The unit of indentation - typically a tab (\t) or a number of spaces string \t
--maxLineWidth Request that the formatter wrap long lines to avoid exceeding this line length int 999
--spacesPerTab This is used to measure line length, and only applies if you use tabs int 4
--statementBreaks How many linebreaks should be added when starting a new statement? int 2
--clauseBreaks How many linebreaks should be added when starting a new clause within a statement? int 1
--no-expandCommaLists Should comma-delimited lists (columns, group by args, etc) be broken out onto new lines? bool
--no-trailingCommas When starting a new line because of a comma, should the comma be at the end of line (VS the start of the next)? bool
--spaceAfterExpandedComma Should a space be added after the comma? (typically not if they are "trailing") bool
--no-expandBooleanExpressions Should boolean operators (AND, OR) cause a linebreak? bool
--no-expandCaseStatements Should CASE expressions have their WHEN and THEN expressions be broken out on new lines? bool
--no-expandBetweenConditions Should BETWEEN expressions have the max argument broken out on a new line? bool
--expandInLists Should IN() lists have each argument on a new line? bool
--breakJoinOnSections Should the ON section of a JOIN clause be broken out onto its own line? bool
--no-uppercaseKeywords Should T-SQL keywords (like SELECT, FROM) be automatically uppercased? bool
--keywordStandardization Should less-common T-SQL keywords be replaced with their standard counterparts? (NOTE: only safe for T-SQL!) bool

Obfuscating formatter ("min" command) options:

Option Description Type Default
--randomizeKeywordCase Should the case of keywords be randomized, to minimize legibility? bool
--randomizeLineLengths Should the SQL be wrapped at arbitrary intervals, to minimize legibility? bool
--no-preserveComments Should comments in the code be retained (vs being stripped out)? bool
--enableKeywordSubstitution Should keywords with synonyms use less common forms? (NOTE: only safe for T-SQL!) bool

Features

Please note, this command-line tool does NOT currently produce HTML (syntax-highlighted) output. This would be a reasonably trivial feature to add, it is definitely supported by the underlying library, but I haven't seen any realistic use-case. If you have one, please let me know (and/or fork, add the option(s), let me know).

This formatter effectively "inherits" all the functionality of the Poor Man's T-SQL Formatter library:

  • Full support for MS SQL Server T-SQL, with (as far as I know) no parsing failures ** Including full procedural/batch code support, DDL, DML, etc.
  • Reasonable support for other SQL dialects (regularly used to format PL/SQL, PostgreSql, MySQL, etc queries) ** Specific constructs may not work or may not be correctly/faithfully preserved for those other dialects - please file issues when such concerns are identified
  • A reasonable number of formatting configuration options, with more on the way.

Status

As noted in the JS library package doc (https://github.com/TaoK/poor-mans-t-sql-formatter-npm-package), this formatter is rather slow at the moment. On my laptop, formatting just a single query takes about half a second. That suggests that for bulk formatting workloads, it it might make sense to offer a wildcard/recursive file input/output option.

The encoding support is also very limited - as we use node's built-in encoding support, it comes down to utf-8 or utf-16le. It might be worth addressing this with the iconv-lite library, but adding even more code to load would also have its downsides. Something to think about.

Besides these things I'm not aware of any major outstanding concerns (vs for example the C# / .Net version of this same command-line formatter downloadable at http://architectshack.com/PoorMansTSqlFormatter.ashx), so any input / feature requests are welcome!