poor-mans-t-sql-formatter

Poor Man's T-SQL Formatter - NPM Package

An NPM package for the Poor Man's T-SQL Formatter JS Library.

This library is transpiled from the C# original (it's not a port in that the JS code is never maintained independently) - so for history of changes, issues with the library itself, etc please see https://github.com/TaoK/PoorMansTSqlFormatter.

This library exists to make it easy to include the formatter library in websites, in random node projects or in command-line usage in node-friendly environments (which may not be as C#-friendly / mono-friendly).

This library also has a much better (in my opionion) API than the original C# library, hopefully I'll get around to changing the source at some point.

There is one serious consideration that might drive you to the C#/mono library or command-line utility, however: This JS library is about 8x slower than the C# original. I haven't investigated exactly why yet, but I don't hold high hopes of this changing anytime soon. If you need to deal with large sets of files and performance matters, you will likely want to look at the C#/mono library/project.

Please note, there is a command-line utility for node/npm also, but it is in a separate npm package, poor-mans-t-sql-formatter-cli (github: https://github.com/TaoK/poor-mans-t-sql-formatter-npm-cli)

Installation

npm install poor-mans-t-sql-formatter

Usage

This JS library exposes a very simple API to the formatter: one method, with options, returning one datastructure (with optional bits).

Example:

var formatterLib = require('poor-mans-t-sql-formatter');
var queryToFormat = "select 1 from somewhere WHERE someoneforgot = punctuation and [we want better] = 1";
var result = formatterLib.formatSql(queryToFormat);
console.log(result.text);

Options are provided in a second argument.

Example:

var formatterLib = require('poor-mans-t-sql-formatter');
var result = formatterLib.formatSql("select 1, 2, 3, 4 from counter", { trailingCommas: false, spaceAfterExpandedComma: true });
console.log(result.text);

General options:

Option	Description	Type	Default
formattingType	standard, identity or obfuscation - what you'd like to do	enum	standard
errorOutputPrefix	Text to be included (in a comment at the top) if parsing failed and result is therefore suspect	string	(something like "--WARNING: errors during parsing")
includeText	Specify that the formatted SQL should be included in the result as a Text value/property	bool	true
includeHtml	Specify that the formatted SQL should be included in the result as HTML	bool	false
includeHtmlPage	Specify that the formatted SQL should be included in the result as a full HTML document	bool	false
includeTokenList	Specify that the Token List produced from the Tokenization phase should be included	bool	false
includeParseTree	Specify that the Parse Tree produced from the Parsing phase should be included	bool	false

Standard formatter options:

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
expandCommaLists	Should comma-delimited lists (columns, group by args, etc) be broken out onto new lines?	bool	true
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	true
spaceAfterExpandedComma	Should a space be added after the comma? (typically not if they are "trailing")	bool	false
expandBooleanExpressions	Should boolean operators (AND, OR) cause a linebreak?	bool	true
expandCaseStatements	Should CASE expressions have their WHEN and THEN expressions be broken out on new lines?	bool	true
expandBetweenConditions	Should BETWEEN expressions have the max argument broken out on a new line?	bool	true
expandInLists	Should IN() lists have each argument on a new line?	bool	false
breakJoinOnSections	Should the ON section of a JOIN clause be broken out onto its own line?	bool	false
uppercaseKeywords	Should T-SQL keywords (like SELECT, FROM) be automatically uppercased?	bool	true
coloring	(In HTML output, if enabled) should keywords, comments etc have distinct color classes?	bool	true
keywordStandardization	Should less-common T-SQL keywords be replaced with their standard counterparts? (NOTE: only safe for T-SQL!)	bool	false

Obfuscating formatter options:

Option	Description	Type	Default
randomizeKeywordCase	Should the case of keywords be randomized, to minimize legibility?	bool	false
randomizeColor	(In HTML output, if enabled) should the color of the SQL text be randomly varied?	bool	false
randomizeLineLengths	Should the SQL be wrapped at arbitrary intervals, to minimize legibility?	bool	false
preserveComments	Should comments in the code be retained (vs being stripped out)?	bool	true
enableKeywordSubstitution	Should keywords with synonyms use less common forms? (NOTE: only safe for T-SQL!)	bool	false

Tests

npm test

(please note, coverage is not yet up to scratch; the general goal is to address the same test cases as the C# library)

License

This library, like the original at https://github.com/TaoK/PoorMansTSqlFormatter, is made freely available under the AGPL 3.0 license.

Contributing

Given that this module is mostly wrapping over the original code at https://github.com/TaoK/PoorMansTSqlFormatter, there is little opportunity for contributing here - but any input is of course welcome!

To produce/refresh the npm library file (index.js) just run the build.sh script. When executed from the root of the project, it will simply collect the files from the parent project in github and add the wrapper code defined in prefix and suffix files.

STATUS

When it is published, I'll see if I can take care of all those trendy things like:

• CI & test tracking
• coverage tracking
• a suitable "try it out" link
• Think about restructuring the library into a "legacylib" file (using prefix/suffix build process) and a new stuff file.
• (anything else?)

Eventually, probably once the library has been published and shows some sign of interest/activity, I'll go for the bigger stuff

• look for perf opportunities. 8s is a LONG time for some simple tests!! (C# equivalent is 1s) - otherwise DOCUMENT the terrible performance...