sql-formatter

If your SQL contains templating syntax (e.g., {col1}) that causes parsing errors, you can use the paramTypes option to treat these constructs as prepared-statement parameter-placeholders using a regex.

format('SELECT {col1}, {col2} FROM {tablename};', {
  paramTypes: { custom: [{ regex: String.raw`\{\w+\}` }] },
});

You can define custom placeholder syntax using the custom array within paramTypes. Each entry requires a regex pattern.

To avoid double-escaping issues with backslashes in your regex, it is recommended to use String.raw.

By default, the params option uses the entire string matched by the regex as the key. To use cleaner keys (e.g., using lname instead of {lname}), provide a key function to extract the name from the matched text.

Install the latest version of sql-formatter using your preferred package manager.

npm install sql-formatter # or yarn, pnpm, bun, etc

You can prevent the formatter from processing specific blocks of SQL by wrapping them in /* sql-formatter-disable */ and /* sql-formatter-enable */ comments. The formatter will not parse the code between these comments.

/* sql-formatter-disable */
SELECT * FROM tbl1;
/* sql-formatter-enable */
SELECT * FROM tbl2;

The identifierCase option converts unquoted identifiers to a specific case.

Warning: This feature is experimental and generally not recommended. The formatter aims to detect as few keywords as possible to avoid accidental conversion when using keywordCase. Consequently, many elements may be incorrectly labeled as identifiers and converted. If you only want keywords to be uppercase, use keywordCase: "upper" instead.

Limitations:

• Prefixed variables (e.g., @my_var) are not converted.
• Parameter placeholders (e.g., :param) are not converted.

The dataTypeCase option controls how SQL data types are cased in the formatted output. You can choose to preserve the original casing, force all data types to uppercase, or force them to lowercase.

Available options:

• "preserve" (default): Keeps the casing exactly as it was in the input SQL.
• "upper": Converts all data type names to uppercase.
• "lower": Converts all data type names to lowercase.

The expressionWidth configuration option determines the maximum length of parenthesized expressions before they are split across multiple lines.

• Value: A number representing the character threshold.
• Default: 50.

If the length of a parenthesized expression is less than or equal to the expressionWidth, it remains on a single line. If it exceeds the value, the formatter will split the expression into multiple lines.

The linesBetweenQueries option controls the number of empty lines inserted between separate SQL statements.

• Value: A positive number representing the count of empty lines.
• Default: 1 (adds one empty line between statements).
• Note: While the documentation mentions false for no newline, the numeric examples suggest using 0 to achieve no newline.

The indentStyle option allows you to switch between different indentation styles.

Note: This option is DEPRECATED.

Available options:

• "standard" (default): Indents code by the amount specified by the tabWidth option.
• "tabularLeft": Indents in a tabular style using 10 spaces, aligning keywords to the left.
• "tabularRight": Indents in a tabular style using 10 spaces, aligning keywords to the right.

Caveats for tabular styles:

• The tabWidth option is ignored; indentation will always be 10 spaces.
• These styles are considered a bolted-on feature and may not work as reliably as the "standard" style.

The newlineBeforeSemicolon option controls whether the query separator (;) is placed on a new line or kept on the same line as the preceding statement.

• Set newlineBeforeSemicolon: false (default) to keep the semicolon on the same line as the last part of the query.
• Set newlineBeforeSemicolon: true to force the semicolon onto its own separate line.