sql-render

Type-safe {{variable}} templating for .sql files with built-in injection protection.

• Zero runtime dependencies
• Built-in SQL injection protection
• Schema-based validation with type inference
• Custom schema types for project-specific rules
• Works with SQL engines that treat backslash as literal in strings (Athena, Trino, PostgreSQL)
• Compatible with sql-formatter

Installation

npm install sql-render

Quick Start

Create a SQL file with {{variable}} placeholders:

-- queries/getEvents.sqlSELECT event_id, event_name
FROM {{tableName}}
WHERE status ='{{status}}'AND created_at >='{{startDate}}'ORDER BY {{orderBy}}
LIMIT {{limit}}

Define and use the query in TypeScript:

import{defineQuery}from'sql-render';constgetEvents=defineQuery<{tableName: string;status: string;startDate: string;orderBy: string;limit: number;}>('./queries/getEvents.sql');const{ sql }=getEvents({tableName: 'prod_events',status: 'active',startDate: '2022-02-22',orderBy: 'created_at',limit: 99,});

Result:

SELECT
event_id,
event_name
FROM
prod_events
WHERE
status ='active'AND created_at >='2022-02-22'ORDER BY
created_at
LIMIT99

Schema Validation

For stricter validation, define a schema instead of a generic type. Types are inferred automatically.

import{defineQuery,schema}from'sql-render';constgetEvents=defineQuery('./queries/getEvents.sql',{tableName: schema.identifier,status: schema.enum('active','pending','done'),startDate: schema.isoDate,orderBy: schema.identifier,limit: schema.positiveInt,});const{ sql }=getEvents({tableName: 'prod_events',status: 'active',startDate: '2022-02-22',orderBy: 'created_at',limit: 99,});

Available Schema Types

Type	Format	Example
schema.string	Any string (with SQL injection check)	'hello'
schema.number	Finite number	33, 3.14
schema.boolean	true / false	true
schema.isoDate	YYYY-MM-DD	'2022-02-22'
schema.isoTimestamp	ISO 8601 with timezone	'2022-02-22T22:02:22.000Z'
schema.identifier	SQL identifier (up to db.schema.table)	'public.users'
schema.uuid	UUID hex format (8-4-4-4-12)	'550e8400-e29b-41d4-a716-446655440000'
schema.positiveInt	Positive integer	100
schema.enum(...)	Whitelist of allowed values	schema.enum('asc', 'desc')
schema.s3Path	S3 URI	's3://athena-results/queries/'
schema.array(inner)	Non-empty array of inner values	schema.array(schema.positiveInt)
schema.nullable(inner)	null / undefined or inner value	schema.nullable(schema.isoTimestamp)

Array Values (IN clauses)

schema.array(inner) validates every element against inner and renders a comma-separated SQL list. Strings are quoted and their single quotes escaped; numbers and booleans are rendered raw. Empty arrays are rejected.

-- queries/getByIds.sqlSELECT*FROM {{table}} WHERE id IN ({{ids}})

constgetByIds=defineQuery('./queries/getByIds.sql',{table: schema.identifier,ids: schema.array(schema.positiveInt),});const{ sql }=getByIds({table: 'users',ids: [1,2,3]});// ... WHERE id IN (1, 2, 3)

Nullable Values

schema.nullable(inner) accepts null / undefined in addition to whatever inner accepts, and emits the bare SQL NULL literal. Do not wrap the placeholder in quotes in your template, since NULL must be unquoted.

-- queries/updateLogin.sqlUPDATE {{table}} SET last_login = {{lastLogin}} WHERE id = {{id}}

constupdateLogin=defineQuery('./queries/updateLogin.sql',{table: schema.identifier,lastLogin: schema.nullable(schema.isoTimestamp),id: schema.positiveInt,});updateLogin({table: 'users',lastLogin: null,id: 1});// ... SET last_login = NULL ...

Custom Schema Types

Define your own type descriptors for project-specific validation:

import{defineQuery,schema}from'sql-render';constprodTable={validate: (val: unknown)=>typeofval==='string'&&val.startsWith('prod_'),};constquery=defineQuery('./query.sql',{table: prodTable,startDate: schema.isoDate,limit: schema.positiveInt,});

A type descriptor is any object with a validate(val: unknown) => boolean method.

Exporting Rendered SQL

Pass an exportTo path to write the rendered SQL to disk for debugging or audit purposes. Missing parent directories are created automatically.

const{ sql }=getEvents({tableName: 'prod_events',status: 'active',startDate: '2022-02-22',orderBy: 'created_at',limit: 99},{exportTo: './debug/getEvents.sql'},);

SQL Injection Protection

schema.string and the generic string type check values against built-in patterns:

Pattern	Examples
Comments	--, /*, */
Statement separator	;
DDL commands	DROP, ALTER, TRUNCATE, CREATE
UNION injection	UNION SELECT, UNION ALL SELECT
DML commands	INSERT INTO, DELETE FROM, UPDATE ... SET
Execution	EXEC, EXECUTE
Time-based	SLEEP(), BENCHMARK(), WAITFOR DELAY
System procedures	xp_*, sp_*
Privilege commands	GRANT, REVOKE
File operations	LOAD_FILE(), INTO OUTFILE, INTO DUMPFILE
Data loading	LOAD DATA

Patterns use word boundaries to avoid false positives (e.g., "backdrop" won't trigger DROP).

Other schema types like schema.identifier, schema.isoDate, schema.uuid etc. are inherently safe due to their strict format validation.

The pattern list is exported for reference:

import{SQL_INJECTION_PATTERNS}from'sql-render';

Error Messages

Scenario	Error
File not found	File not found: ./query.sql
Schema mismatch	Schema missing definitions for template variables: [id]
Missing params	Missing variables in params: [tableName, limit]
Extra params	Extra variables not in template: [foo]
Schema validation	Schema validation failed for 'status': received string ("invalid")
Type validation	SQL injection pattern detected in 'status': ...
Null/undefined	Validation failed for 'key': value cannot be null or undefined
Invalid descriptor	Invalid schema descriptor for 'key': must have a validate(val) method

Security Model

sql-render protects against SQL injection using a denylist + escape strategy, not parameterized queries (prepared statements). Values are validated and escaped before being interpolated directly into the SQL string.

This is effective for engines that don't support parameterized queries (e.g., Athena, Trino DDL, ad-hoc SQL scripts). If your database driver supports parameterized queries, prefer using them as the primary defense and treat sql-render's protection as an additional layer.

The built-in denylist does not guarantee 100% protection against all SQL injection vectors. For stricter control, define custom schema types tailored to your project's specific validation needs.

To report a vulnerability, see SECURITY.md.

sql-formatter Compatibility

The {{variable}} syntax is fully compatible with sql-formatter. A paramTypes custom regex is required so that {{variables}} containing SQL keywords (e.g. {{limit}}) are treated as parameters instead of being parsed as SQL.

VS Code

Install the SQL Formatter VSCode extension, then copy .vscode/settings.json into your project to enable format-on-save with the recommended settings:

{
"[sql]": {
"editor.defaultFormatter": "ReneSaarsoo.sql-formatter-vsc",
"editor.formatOnSave": true
},
"SQL-Formatter-VSCode.dialect": "trino",
"SQL-Formatter-VSCode.keywordCase": "upper",
"SQL-Formatter-VSCode.functionCase": "upper",
"SQL-Formatter-VSCode.dataTypeCase": "upper",
"SQL-Formatter-VSCode.paramTypes": {
"custom": [{ "regex": "\\{\\{[a-zA-Z_][a-zA-Z0-9_]*\\}\\}" }]
}
}

For LLMs

Two machine-readable summaries are maintained for AI consumption, following the llms.txt convention:

• llms.txt - curated API reference and usage guide
• llms-full.txt - full packed source, auto-generated on each push

License

MIT