A VS Code extension that formats .sql files containing template placeholders (common in Python projects) without breaking them. Existing SQL formatters treat ${XXX} as a syntax error; this one does not.
The same formatter also ships as a CLI (npx sql-template-formatter) for CI, pre-commit, and AI-agent hooks — it calls the identical core, so both produce byte-identical output.
Features
- Works with
Shift+Alt+F (Format Document), selection formatting, and editor.formatOnSave
- Placeholders are kept intact:
${var} {var} {{ var }} %s %(name)s
GROUP BY 1, 2 / ORDER BY 1 ordinals are replaced with the referenced column names (disable with sqlTemplateFormatter.replaceOrdinals: false)- Dialect selectable: PostgreSQL (default) / BigQuery / MySQL / SQLite / Snowflake and more (all dialects supported by sql-formatter)
How it works
Existing SQL formatters treat template placeholders (${var}, {{ var }}, %s …) as syntax errors and refuse to format such files. This extension avoids masking entirely: it registers the placeholder patterns with sql-formatter v15+ as parameter tokens (paramTypes.custom), so the SQL is formatted with the placeholders kept intact.
🔍 Open Interactive Architecture Diagram — Explore components, guided views, and pipeline trace.
Install
This extension conflicts with other SQL formatter extensions. When using it, set it as the default formatter in settings.json and disable the others:
{
"[sql]": {
"editor.defaultFormatter": "HidenobuNagai.sql-template-formatter",
"editor.formatOnSave": true
}
}
CLI
Same formatter, no editor required — useful in CI, pre-commit, and AI-agent hooks.
npx sql-template-formatter query.sql # file → stdout
npx sql-template-formatter --write query.sql # rewrite in place
npx sql-template-formatter < query.sql # stdin → stdout
npx sql-template-formatter --check sql/*.sql # exit 1 if not formatted (CI)
# pre-commit / agent hook (GNU xargs): format only the .sql files that changed
git diff --name-only --diff-filter=ACM -- '*.sql' | xargs -r sql-template-formatter --write
| Option |
Description |
-w, --write |
Rewrite files in place |
--check |
Exit 1 if any input is not already formatted |
-l, --dialect <name> |
SQL dialect (default: postgresql) |
-k, --keyword-case <c> |
upper / lower / preserve (default: upper) |
--no-ordinals |
Keep GROUP BY 1 / ORDER BY 1 as-is |
--comma-position <p> |
after (default) keeps a wrapping comma at the end of the previous line; before moves it to the start of the next line |
--keep-functions-inline |
Keep SUM(...) / COUNT(CASE ... END) on one line instead of breaking their arguments |
--named-prefix <p> |
Named parameter prefix (:, @, or $); repeat for several. Same as the extension's namedPrefixes |
--tab-width <n>, --tabs |
Indentation (default: 2 spaces) |
-c, --config <file> |
Config JSON (default: the .sql-formatter.json nearest to each file) |
-h, --help, --version |
|
Each file is formatted with the .sql-formatter.json nearest to it (searching its directory and then each parent, like Prettier); stdin uses the one nearest to the working directory. It accepts the standard sql-formatter keys (language, keywordCase, tabWidth, useTabs, paramTypes.custom, paramTypes.named) plus placeholderPatterns, namedPrefixes, replaceOrdinals, commaPosition, and keepFunctionsInline:
{
"language": "postgresql",
"keywordCase": "upper",
"placeholderPatterns": ["\\$\\{[^}]+\\}", "\\{\\{[\\s\\S]*?\\}\\}", "\\{[^{}]*\\}", "%\\([^)]*\\)s", "%s(?![A-Za-z0-9_])"],
"replaceOrdinals": true,
"commaPosition": "after",
"keepFunctionsInline": false
}
When neither placeholderPatterns nor paramTypes.custom is set, the extension's five default patterns apply, so placeholders survive untouched. The CLI does not read VS Code's settings.json; its defaults match the extension's defaults (postgresql + upper + ordinals replaced), and every extension setting has a CLI flag or config key, so equal settings give byte-identical output. Any unknown key in a configuration file is rejected with exit code 2 before formatting begins, leaving files untouched.
Exit codes: 0 success, 1 --check found an unformatted input, 2 an error (bad option or config, unreadable file, unparsable SQL). When both happen, 2 wins.
The file's final newline is preserved: a newline-terminated file stays newline-terminated (extra trailing blank lines collapse to one) and a file without one is left alone. sql-formatter re-prints the parse tree, so the last newline is restored explicitly — otherwise every run would leave a \ No newline at end of file diff behind and --check could never pass on a normal file. Line endings are normalized to LF, the same default as Prettier (endOfLine: "lf").
Hooks: prefer formatting at the end of an agent turn (Stop) rather than right after every file write. Rewriting a file immediately after the agent wrote it invalidates the old text it may still try to edit.
Settings
Every setting can be set per language ("[sql]": { … }) and, in a multi-root workspace, per folder.
| Setting |
Default |
Description |
sqlTemplateFormatter.dialect |
postgresql |
Dialect (postgresql, bigquery, mysql, sqlite, snowflake, etc. — any dialect sql-formatter supports). It also decides how strings and comments are read: whether \ escapes a quote, whether # starts a comment |
sqlTemplateFormatter.placeholderPatterns |
Regexes for ${...}, {{...}}, {...}, %(name)s, %s (5 entries) |
Array of placeholder regex strings. Earlier patterns take priority |
sqlTemplateFormatter.namedPrefixes |
[] |
Prefixes for named parameters: :, @, or $ (e.g. [":"] for :name as used by SQLAlchemy text(), sqlite3, or python-oracledb; psycopg2's %s / %(name)s are already covered by the default placeholder patterns). PostgreSQL :: casts keep working |
sqlTemplateFormatter.keywordCase |
upper |
Keyword casing (preserve/upper/lower) |
sqlTemplateFormatter.replaceOrdinals |
true |
Replace GROUP BY/ORDER BY ordinals (e.g. 1, 2) with the referenced columns — only where the meaning provably stays the same. GROUP BY gets the column's expression (never its alias), ORDER BY a unique alias or the expression. Ordinals are left untouched when the column is *, a constant, a placeholder expression, an aggregate in GROUP BY, or an expression mentioning an output alias, and in the ORDER BY of a UNION/EXCEPT/INTERSECT |
sqlTemplateFormatter.commaPosition |
after |
after keeps a wrapping comma at the end of the previous line; before moves it to the start of the next line with a space after it (id / , name), keeping a trailing -- comment with its own item |
sqlTemplateFormatter.keepFunctionsInline |
false |
Re-join the formatter's line breaks inside word(...) groups so SUM(...), COUNT(CASE … END), and nested calls stay on one line. Only a paren a name touches (SUM() counts as a call, so CTE bodies (AS (), subqueries, IN (…), EXISTS (…), and OVER (…) keep their layout. Newlines inside string literals, $$…$$ bodies, and comments are never removed |
Customizing placeholders
{
"sqlTemplateFormatter.placeholderPatterns": [
"\\$\\{[^}]+\\}",
"\\{\\{[\\s\\S]*?\\}\\}",
"\\{[^{}]*\\}",
"%\\([^)]*\\)s",
"%s(?![A-Za-z0-9_])",
"@\\w+"
],
"sqlTemplateFormatter.namedPrefixes": [":"]
}
Notes
- The
{...} pattern can falsely match JSON literals (e.g. SELECT '{"a":1}'::jsonb;). Text inside string literals is normally safe because the lexer processes strings first, but remove this pattern if you run into issues.
- Jinja2 control constructs (
{% for %} etc.) are not supported.
- Patterns match in array order. Put Jinja2's
{{ }} before single { } (the defaults already do).
- Patterns are strings, not RegExp objects (settings.json values are always strings anyway).
Development
bun install
bun run compile # type-check (tsc --noEmit) + esbuild bundle into out/
bun run test # compiles, then runs the unit tests (bun:test); the CLI tests need out/
node out/cli.js --help # run the CLI from the build output
bun run package # build .vsix
Press F5 to launch an Extension Development Host for manual testing.
Release (maintainers)
- Bump the version in
package.json and tag it: git tag vX.Y.Z (the publish workflow refuses a tag that does not match the version, and runs the tests before publishing anything)
- Push the tag:
.github/workflows/publish.yml publishes to the VS Code Marketplace (VSCE), Open VSX (OVSX), and npm.
npm uses trusted publishing (OIDC) — no token or repository secret; the trust relationship is registered on the package's npm settings page and must name this workflow file (publish.yml). Provenance attestations are attached automatically.
- Manual alternative:
- VS Marketplace: run
bun run publish:vsce with VSCE_PAT set
(the script is deliberately not named publish: npm runs a publish script as a lifecycle step of npm publish, so it would fire again on every npm release and fail without VSCE_PAT)
- Open VSX: run
bun run publish:ovsx -p $OVSX_PAT with OVSX_PAT set
- npm: run
npm publish --access public with NODE_AUTH_TOKEN set (or, from CI, rely on trusted publishing)
Never commit PATs in plain text. Manage them with dotenvx or similar.
License
MIT
