node package manager
Loosely couple your services. Use Orgs to version and reuse your code. Create a free org ยป

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...