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!