sql

@filtron/sql

Convert Filtron AST to parameterized SQL WHERE clauses.

Installation

npm install @filtron/sql

Usage

import { parse } from "@filtron/core";
import { toSQL } from "@filtron/sql";

const result = parse('age > 18 AND status = "active"');

if (result.success) {
	const { sql, params } = toSQL(result.ast);
	// sql: "(age > $1 AND status = $2)"
	// params: [18, "active"]

	await db.query(`SELECT * FROM users WHERE ${sql}`, params);
}

API

toSQL(ast, options?): SQLResult

Converts a Filtron AST to a parameterized SQL WHERE clause.

Returns:

interface SQLResult {
	sql: string; // The WHERE clause (without "WHERE" keyword)
	params: unknown[]; // Parameter values in order
}

Options

Option	Type	Default	Description
dialect	"postgres" | "mysql" | "sqlite"	undefined	Preset for dialect-specific defaults
parameterStyle	"numbered" | "question"	"numbered"	Placeholder format
fieldMapper	(field: string) => string	undefined	Transform field names to column names
valueMapper	(value: unknown) => unknown	undefined	Custom transform for ~ values (see below)
likeMode	"contains" | "raw"	"contains"	How ~ builds its LIKE parameter (see below)
startIndex	number	1	Starting index for numbered placeholders
allowedFields	string[]	undefined	Allowlist of field names (throws otherwise)

A dialect sets the parameter style (postgres uses numbered; mysql and sqlite use question); explicitly supplied options override the preset.

Parameter styles

Numbered ($1, $2, ...) — PostgreSQL, CockroachDB:

const { sql, params } = toSQL(ast);
// sql: "(age > $1 AND status = $2)"

Question marks (?, ?, ...) — MariaDB, SQLite, DuckDB:

const { sql, params } = toSQL(ast, {
	parameterStyle: "question",
});
// sql: "(age > ? AND status = ?)"

Examples

Custom field mapping:

const { sql, params } = toSQL(ast, {
	fieldMapper: (field) => `users.${field}`,
});
// "age > 18" becomes "users.age > $1"

Table-qualified columns:

const { sql, params } = toSQL(ast, {
	fieldMapper: (field) => `"${field}"`, // Quote column names
});

Start index (for combining queries):

const { sql, params } = toSQL(ast, {
	startIndex: 3,
});
// Placeholders start at $3

The contains operator (~)

By default ~ means substring-contains, matching @filtron/js. The value is wrapped in % wildcards and LIKE metacharacters (%, _, \) are escaped before parameterization:

const result = parse('name ~ "john"');
const { sql, params } = toSQL(result.ast);
// sql: "name LIKE $1"
// params: ["%john%"]

// Metacharacters in the value match literally
toSQL(parse('name ~ "100%"').ast).params; // ["%100\\%%"]

Set likeMode: "raw" to pass the value through untouched. Wildcards and escaping become the caller's responsibility:

const { sql, params } = toSQL(result.ast, { likeMode: "raw" });
// Query 'name ~ "john%"' produces params: ["john%"]

For prefix or suffix matching, use valueMapper with the LIKE helpers. valueMapper takes precedence over likeMode:

import { toSQL, contains, prefix, suffix, escapeLike } from "@filtron/sql";

const { sql, params } = toSQL(result.ast, { valueMapper: prefix });
// Query 'name ~ "john"' produces params: ["john%"]

Function	Input	Output	Use case
contains	"foo"	"%foo%"	Substring match
prefix	"foo"	"foo%"	Starts with
suffix	"foo"	"%foo"	Ends with
escapeLike	"a%b"	"a\\%b"	Escape special chars

Relative time values

Queries with @now (such as created > @now-7d) parse into relative values that toSQL rejects with "Unresolved relative time value". Resolve them to absolute dates first with @filtron/time-resolver:

import { resolveTemporal } from "@filtron/time-resolver";

const { sql, params } = toSQL(resolveTemporal(ast));

Resolve per request, not at cache time: the resolved AST pins now to a single instant.

Performance

For APIs with repeated filter queries, cache parsed results to avoid redundant parsing:

const cache = new Map<string, SQLResult>();

function getFilterSQL(filter: string): SQLResult | null {
	const cached = cache.get(filter);
	if (cached) return cached;

	const result = parse(filter);
	if (!result.success) return null;

	const sql = toSQL(result.ast, { parameterStyle: "question" });
	cache.set(filter, sql);
	return sql;
}

Consider using an LRU cache with a size limit for production:

// https://github.com/isaacs/node-lru-cache
import { LRUCache } from "lru-cache";

const cache = new LRUCache<string, SQLResult>({ max: 1000 });

Caching is effective when:

• Users frequently repeat the same filter queries
• Filter expressions are complex (nested AND/OR conditions)

For simple queries or unique filters, caching overhead is not worthwhile.

Caching at the HTTP level (database query results) is often more effective than caching a parsed Filtron SQLResult. Database queries are typically orders of magnitude slower than processing the Filtron query.

Security

All queries are parameterized to prevent SQL injection:

// User input with SQL injection attempt
const result = parse("name = \"admin' OR '1'='1\"");
const { sql, params } = toSQL(result.ast);

// sql: "(name = $1)"
// params: ["admin' OR '1'='1"]  — treated as literal string value

Never interpolate user input directly into SQL. Always use the params array with your database driver's parameterized query support.

Field names are interpolated into the generated SQL. Queries that go through the parser restrict the field charset, but hand-built ASTs are unvalidated. Use allowedFields to guard those and add defense in depth:

// Safe: only listed fields can reach the SQL string
toSQL(ast, { allowedFields: ["name", "age", "status"] });

// Unsafe: a hand-built AST can put anything in a field name
toSQL({
	type: "comparison",
	field: 'password" = "" OR "1', // interpolated as-is
	operator: "=",
	value: { type: "string", value: "x" },
});

LIKE patterns deserve the same care. The default likeMode: "contains" escapes %, _ and \ so user input cannot smuggle wildcards into the pattern:

// Safe: default likeMode escapes metacharacters
toSQL(ast); // 'name ~ "100%"' produces params: ["%100\\%%"]

// Unsafe: raw mode passes user input straight into the LIKE pattern
toSQL(ast, { likeMode: "raw" }); // params: ["100%"] — % acts as a wildcard

// Safe raw usage: escape user input yourself
toSQL(ast, { valueMapper: (v) => `${escapeLike(String(v))}%` });

License

MIT