Using sql

TypeSQL centers on the sql function returned by your driver adapter's createSql<DB>() (or core's createSql<DB>(dialect), such as createSql<DB>(postgres)). It compiles and type-checks your SQL statements against your schema at compile time, and provides helper functions on sql.* for parameter binding, dynamic SQL, fragments, bulk writes, and transactions.

sql(...) is all you need to write queries. Every other sql.* function is a helper for specific use cases.

Overview

Method	Summary	Expected parameters
sql(query, ...params)	Parses and type-checks a SQL statement literal, inferring row and parameter types.	query: string (literal), ...params: T[] or { [name]: T }
sql.as<Row>(query, ...params)	Escape hatch: bypasses compile-time inference and asserts the row type directly.	<Row>, query: string, ...params: unknown[]
sql.param(value)	Binds a value inline inside a template string as a positional $n parameter.	value: T (sql.optional(undefined) becomes DEFAULT; undefined is rejected)
sql.optional(value)	Turns undefined into an omission: SQL DEFAULT, a skipped item, or nothing. null stays SQL NULL.	value: T
sql.if(value, then)	The fragment then(value) builds when value is truthy, otherwise an omission.	value: V, then: (value) => Fragment | Query
sql.json(value)	Binds a value as inline JSON text, checked against json/jsonb/Json<T> columns.	value: T (null becomes SQL NULL, undefined becomes DEFAULT)
sql.fragment(text, ...params)	Creates a reusable SQL fragment to interpolate as ${fragment}.	text: string (literal), ...params
sql.id(...parts)	Creates a safely quoted identifier fragment ("col" or "schema"."table").	...parts: string[] (column, table, schema names)
sql.combine(items, options?)	Combines an array or tuple of fragments or queries with a separator.	items: (Slottable | Empty)[], options?: { separator?: string } (default ", ")
sql.and(items) / sql.or(items)	Combines conditions with AND or OR, in parentheses. No items gives true or false.	items: (Slottable | Empty)[]
sql.set(table, values)	Generates a type-checked SET "col" = $n fragment for UPDATE, omitting undefined.	table?: TableName<DB>, values: SetInput<DB, TM, table>
sql.values(table, rows)	Generates a type-checked ("col") VALUES (...) fragment for INSERT.	table?: TableName<DB>, rows: InsertInput<DB, TM, table>[]
sql.raw(text)	Creates an untyped, raw SQL text fragment (requires sql.as<Row>()).	text: string
sql.transaction(fn)	Runs an async callback in a database transaction with commit, rollback, and savepoints.	fn: (tx: TransactionalSql) => Promise<T>

---

sql(query, ...params)

sql<Q>(query: Q, ...params: Params): Query<Row, Params, Text>

The core function for writing typed queries. TypeSQL's type engine parses the SQL string literal at compile time, checks syntax and relation/column references against DB, and infers:

• Row: The resulting row shape (column names, types, and nullability).
• Params: The expected parameter types (or named parameter object).
• Text: The normalized SQL text sent to the database.

Invalid statements cause compile errors directly on the SQL string.

Parameters

• query (string): A compile-time string literal containing the SQL statement. Can include:
  • Positional placeholders: $1, $2, …
  • Named placeholders: :name
  • Inline expressions: ${sql.param(val)}, ${sql.json(val)}
  • Fragments and subqueries: ${sql.id("col")}, ${fragment}, ${query}(Tagged templates like sql`…` are not supported because TypeScript erases their string literal types).
• ...params (optional):
  • When using $n: Positional argument values matching the inferred types in $1, $2, … order.
  • When using :name: Exactly one object whose keys match the named placeholders { [name]: value }.

Examples

const byRole = sql(
	"SELECT id, email FROM users WHERE role = $1 ORDER BY id",
	"admin",
);

const byEmail = sql(
	"SELECT id, role FROM users WHERE email = :email AND role = :role",
	{ email: "user@example.com", role: "member" },
);

const activeOnly = sql.fragment("AND role = $1", "active");
const filtered = sql(
	`SELECT id, email FROM users WHERE id > $1 ${activeOnly}`,
	10,
);

See Selecting data and Parameters.

---

sql.as<Row>(query, ...params)

sql.as<Row>(query: string, ...params: unknown[]): Query<Row, readonly unknown[]>

An escape hatch that skips TypeSQL's compile-time type inference and explicitly asserts the row type Row. Use it for dynamic SQL where the statement text is not known at compile time, unsupported SQL features, or statements containing sql.raw().

Parameters

• <Row> (type parameter): The expected TypeScript type of the returned rows. When using CasePlugin, write Row with camelCase property names.
• query (string): The SQL query string. Does not need to be a compile-time string literal.
• ...params (optional): Values passed as positional parameters or as a single named parameter object.

Example

type Summary = { count: number; role: string };
const summary = sql.as<Summary>(
	"SELECT count(*)::int AS count, role FROM users GROUP BY role",
);

See Errors and troubleshooting.

---

sql.param(value)

sql.param<T>(value: T): InlineMarker<T>

Binds an expression as an inline parameter inside an interpolated template string (e.g. sql(`… WHERE id = ${sql.param(userId)}`)).

At runtime, the value is rewritten to a positional $n parameter—it is never concatenated as raw text, preventing SQL injection. At compile time, TypeScript checks the value's type against the expected column type.

Parameters

• value (T): The value to bind to the parameter. undefined is rejected. Use sql.optional(value) to send SQL DEFAULT when the value is undefined, or null to bind SQL NULL. When a value can be omitted that way, the statement must allow DEFAULT in that position (such as in an INSERT … VALUES or UPDATE … SET clause).

Example

const userId = 42;
const byId = sql(`SELECT email FROM users WHERE id = ${sql.param(userId)}`);

See Inline parameters.

---

sql.optional(value)

sql.optional<T>(value: T): Optional<T>

Turns undefined into an omission and returns other values unchanged, including null, 0, false and "". The argument is required; sql.optional(undefined) is an explicit omission. As in sql.set, sql.values and sql.json, undefined means "leave it out" and null means SQL NULL, so a missing key and an explicit null in a request body stay different:

declare const body: { display_name?: string | null };
const insert = sql(
	`INSERT INTO users (email, display_name) VALUES ($1, ${sql.param(sql.optional(body.display_name))})`,
	"a@b.c",
);

A missing display_name sends DEFAULT, and null sends NULL. To send DEFAULT for null too, write sql.optional(value ?? undefined).

Inside an interpolated sql.param(...), an omission emits SQL DEFAULT without consuming a parameter number. The statement must allow DEFAULT in that position. In sql.combine(...), sql.and(...) and sql.or(...), it omits an item without adding a separator or parameters. Interpolated as ${…}, an omitted fragment or query splices nothing:

declare const minId: number | undefined;
const optionalFilter =
	minId === undefined ? undefined : sql.fragment("id > $1", minId);
const filters = sql.and([requiredFilter, sql.optional(optionalFilter)]);
const more = sql(
	`SELECT id FROM users WHERE role = $1 ${sql.optional(minId === undefined ? undefined : sql.fragment("AND id > $1", minId))}`,
	"admin",
);

A fragment or query that can be null is an error: interpolated, null would be the SQL text null. Turn it into undefined first, as in sql.optional(maybeFragment ?? undefined).

See also sql.if, which builds the fragment only when a value is present.

Migration: sql.empty is replaced by sql.optional: write sql.optional(value ?? undefined) for sql.empty(value) and value ?? sql.empty, which also omitted null; sql.optional(value) when null should stay SQL NULL; and sql.optional(undefined) for a bare sql.empty. An omission must be interpolated through sql.param; it cannot be sent as a bound $n or :name argument.

---

sql.if(value, then)

sql.if<V, R>(value: V, then: (value: Truthy<V>) => R): R | EmptyFragment

Calls then with value when value is truthy and returns the fragment or query it builds. Otherwise it returns the same omission as sql.optional(undefined), so the item is skipped in sql.combine, sql.and and sql.or, and splices nothing as ${…}. then receives value without null, undefined, false, 0, 0n and "" in its type.

declare const search: string | undefined;
declare const minAge: number | null;
const filters = sql.and([
	sql.fragment("id > $1", 0),
	sql.if(search, (s) => sql.fragment(`email ILIKE ${sql.param(s)}`)),
]);
const adults = sql(
	`SELECT id FROM users WHERE true ${sql.if(minAge, (n) => sql.fragment("AND age >= $1", n))}`,
);

Interpolated, the statement is checked both with and without the fragment, as with sql.optional. Like any condition on a value, an empty string or 0 counts as absent: use sql.optional with a fragment built from value !== undefined when they should count.

sql.json(value)

sql.json<T>(value: T): JsonMarker<T>

Binds an inline JSON or JSONB value inside an interpolated template string.

At runtime, the value is serialized to JSON text and sent with driver-safe formatting (preventing node-postgres from mistaking JavaScript arrays for PostgreSQL arrays). At compile time, the value is checked against json/jsonb columns or custom Json<T> schema types.

Parameters

• value (T): The object, array, or primitive to serialize as JSON. Passing null sends SQL NULL. Passing undefined sends DEFAULT, so a value that can be undefined is only accepted where DEFAULT is (a VALUES item or a SET value). In sql.set and sql.values it leaves the column out.

Example

const settings = { theme: "dark" as const };
const updateSettings = sql(`
  UPDATE users SET metadata = ${sql.json(settings)} WHERE id = ${sql.param(1)}
`);

See JSON values.

---

sql.fragment(text, ...params)

sql.fragment<Text>(text: Text, ...params: Params): Fragment<Text, Params>

Creates a reusable piece of SQL to interpolate as ${fragment} into queries or other fragments.

Fragments preserve their parameter types and carried values. When spliced into a parent query, their parameters are automatically renumbered after the parent statement's parameters and type-checked against the outer query context.

Parameters

• text (string): A compile-time string literal containing SQL. May include $n, :name, inline ${sql.param()} calls, and interpolated ${fragment}s.
• ...params (optional): Positional argument values or one named parameter object matching parameters defined inside text.

Example

const whereRole = sql.fragment("WHERE role = $1", "admin");
const countAdmins = sql(`SELECT count(*) FROM users ${whereRole}`);

See Dynamic SQL.

---

sql.id(...parts)

sql.id(...parts: [string, ...string[]]): Fragment

Creates a safely quoted identifier fragment. Each argument is quoted in double quotes and joined by periods ("part1"."part2").

Identifier allowlisting

sql.id() prevents SQL syntax injection by double-quoting identifiers and escaping embedded quotes. However, it does not restrict which columns or tables can be accessed.

Passing untrusted user input directly to sql.id() allows callers to specify any column or table name. In an ORDER BY or SELECT clause, an attacker could supply sensitive column names (such as password_hash, ssn, or private tokens) to exfiltrate values or infer them through sorting order. Supplying non-existent column names will also make the database throw an error (undefined_column in PostgreSQL), aborting transactions.

Always validate dynamic identifier names against an explicit allowlist or a strict schema enum before calling sql.id().

Parameters

• ...parts (string[]): One or more identifier segments.
  • sql.id("name") produces "name".
  • sql.id("schema", "table") produces "schema"."table".
  • Also accepts unions of literal strings (checked for each union member) or generic relation names (RelationName<DB>).

Example

const col = sql.id("email");
const fqTable = sql.id("public", "users");
const customSelect = sql(`SELECT ${col} FROM ${fqTable}`);

See Identifiers and lists.

---

sql.combine(items, options?)

sql.combine(items: readonly (Slottable | Empty)[], options?: { separator?: string }): Fragment

Combines an array or tuple of fragments or subqueries with a delimiter into a single fragment.

Omitted items (sql.optional(…) of undefined) are ignored, without adding delimiters or parameters. Plain null and undefined entries are rejected. If no items remain, it throws.

• When given a tuple ([a, b]), literal SQL text is preserved after removing known omitted entries. Conditional fragments are checked as present, like elements of a runtime array.
• When given a dynamic runtime array (Slottable[]), TypeSQL validates a representative statement where each distinct element type is checked once. This keeps dynamic clauses (like dynamic ORDER BY or IN (...) lists) fully type-safe.

To combine conditions, use sql.and or sql.or: they add the parentheses that operator precedence needs.

Caution: SQL Injection

The options.separator value is concatenated directly into the statement as raw SQL text. Never pass untrusted user input as the separator. Use constant SQL delimiters such as ", ", " AND ", " OR ", or "\n".

Parameters

• items (readonly (Slottable | Empty)[]): An array or tuple of fragments (Fragment), subqueries (Query), or omissions from sql.optional. Throws at runtime if empty after ignoring omissions.
• options.separator (string, optional): The delimiter string placed between elements. Defaults to ", ". Other strings, such as " " or " UNION ALL ", are accepted. Must be a trusted SQL delimiter.

Example

declare const byEmail: boolean;
const order = sql.combine([
	sql.optional(byEmail ? sql.fragment("email") : undefined),
	sql.fragment("id DESC"),
]);
const sorted = sql(`SELECT id FROM users ORDER BY ${order}`);

See Lists from runtime arrays.

---

sql.and(items) / sql.or(items)

sql.and(items: readonly (Slottable | Empty)[]): Fragment
sql.or(items: readonly (Slottable | Empty)[]): Fragment

Combines conditions with AND or OR. Each condition is wrapped in parentheses, and so is the result when it has more than one condition, so a OR b stays one condition and NOT ${filters} negates all of them:

Items	sql.and	sql.or
[a, b]	((a) AND (b))	((a) OR (b))
[a]	(a)	(a)
[]	true	false

Omitted items are ignored, as in sql.combine. Plain null and undefined entries are rejected. Tuples and runtime arrays are checked the same way as in sql.combine, with every condition present: a conditional display_name IS NOT NULL still makes display_name non-null in the row type.

An empty sql.and matches every row

With no conditions left, sql.and gives true, so DELETE FROM posts WHERE ${filters} deletes every row. Keep required authorization filters (such as tenant or ownership checks) in the statement or as a fixed first item, and make only optional search filters conditional. To throw on an empty list instead, use sql.combine(items, { separator: " AND " }).

Parameters

• items (readonly (Slottable | Empty)[]): An array or tuple of conditions as fragments (Fragment), subqueries (Query), or omissions from sql.optional.

Example

declare const minId: number | undefined;
declare const search: string | undefined;
const filters = sql.and([
	sql.fragment("role = $1", "admin"),
	sql.optional(minId === undefined ? undefined : sql.fragment("id > $1", minId)),
	sql.optional(
		search === undefined
			? undefined
			: sql.or([
					sql.fragment("email ILIKE $1", search),
					sql.fragment("display_name ILIKE $1", search),
				]),
	),
]);
const filtered = sql(`SELECT id FROM users WHERE ${filters}`);

See Identifiers and lists.

---

sql.set(table, values)

sql.set<T>(table: T, values: SetInput<DB, TM, T>): Fragment
sql.set(values: object): Fragment

Generates a type-checked SET "col1" = $1, "col2" = $2 fragment for UPDATE statements from a JavaScript object.

Keys must match column names of table, and values must match their corresponding column types. Properties with undefined values are automatically omitted, allowing partial updates without manually constructing dynamic SQL.

Without table, the fragment is checked against the table of the UPDATE it is interpolated into, with every key present. Errors are then reported on the statement instead of on the object's keys.

Mass assignment protection

The table argument is used for compile-time type checking. At runtime, TypeSQL does not retain schema metadata; sql.set iterates every enumerable property on values and emits a column assignment for it.

If values comes from an untrusted source (such as req.body) and is typed as any or contains unstripped properties, unexpected fields (such as is_admin or role) will be written to the database.

Best practice: Validate request payloads using a schema validation library with strict parsing enabled (e.g. Zod's z.object(...).strict() or Valibot's v.strictObject(...)) to actively reject unexpected properties at the API boundary, or explicitly pick permitted fields via destructuring.

Parameters

• table (TableName<DB>): The table name as a string literal (e.g. "users" or "schema.users").
• values (SetInput<DB, TM, table>): An object with column updates. Throws at runtime if no properties are defined.

Example

const changes = { display_name: "Ada", role: undefined };
const setClause = sql.set("users", changes);
const updateQuery = sql(
	`UPDATE users SET ${setClause} WHERE id = $1 RETURNING id`,
	1,
);

See Updating with sql.set.

---

sql.values(table, rows)

sql.values<T>(table: T, rows: readonly InsertInput<DB, TM, T>[]): Fragment
sql.values(rows: readonly object[]): Fragment

Generates a type-checked column list and multi-row ("col1", "col2") VALUES ($1, $2), ($3, DEFAULT) fragment for bulk INSERT statements from an array of objects.

NOT NULL columns without defaults are required on every row. Optional or defaultable columns that are missing or set to undefined emit DEFAULT.

Without table, the rows are checked against the table of the INSERT they are interpolated into, as strictly: the statement is checked once with every key, and once with only the keys every row always sends, so a required column that a row can leave out is an error. Errors are reported on the statement instead of on the rows.

declare const form: { email?: string; display_name?: string | null };
sql(`INSERT INTO users ${sql.values([form])}`);

Mass assignment in bulk inserts

The table argument is checked at compile time. At runtime, sql.values collects every key present across all row objects into the INSERT column list. If row objects contain unvalidated user input, unexpected columns will be inserted. Validate input rows using a strict schema parser (e.g. z.array(RowSchema.strict())) or explicitly pick allowed properties to prevent inserting unauthorized columns.

Parameters

• table (TableName<DB>): The table name as a string literal (e.g. "users").
• rows (readonly InsertInput<DB, TM, table>[]): A non-empty array of row objects. Throws at runtime if the array is empty.

Example

const newUsers = [
	{ email: "ada@example.com", role: "admin" },
	{ email: "grace@example.com", role: "member", display_name: "Grace" },
];
const insertValues = sql.values("users", newUsers);
const insertQuery = sql(`INSERT INTO users ${insertValues} RETURNING id, email`);

See Bulk insert with sql.values.

---

sql.raw(text)

sql.raw(text: string): Fragment<string, [], []>

Creates an untyped, unsanitized SQL text fragment.

Because raw fragments bypass compile-time syntax and schema analysis, queries containing them cannot be inferred and must use sql.as<Row>().

Caution: SQL Injection

Never pass untrusted user input to sql.raw(). Use $n, :name, or ${sql.param()} for values. For identifiers like column or table names, validate user input against an explicit allowlist before passing to sql.id().

Parameters

• text (string): Raw SQL string sent directly to the database.

Example

const rawSnippet = sql.raw("ORDER BY id DESC");
const sorted = sql.as<{ id: number }>(`SELECT id FROM users ${rawSnippet}`);

See Fully dynamic SQL.

---

sql.transaction(fn)

sql.transaction<T>(fn: (tx: TransactionalSql) => Promise<T>): Promise<T>

Runs an async callback inside a database transaction. Available on sql instances created with an adapter package (@typesql/pg, @typesql/postgres, @typesql/kysely, @typesql/drizzle, @typesql/prisma).

• Automatically commits when fn resolves, and rolls back if fn throws.
• The callback receives tx, a transactional sql instance bound to the active transaction.
• Nested calls tx.transaction(fn) automatically manage savepoints.

Parameters

• fn ((tx: TransactionalSql) => Promise<T>): An async callback receiving the transactional tx handle.

Example

const pgSql = createPgSql<DB>(new Pool());
const createdId = await pgSql.transaction(async (tx) => {
	const user = await tx(
		"INSERT INTO users (email, role) VALUES ($1, $2) RETURNING id",
		"dev@example.com",
		"engineer",
	).executeTakeFirstOrThrow();
	await tx(
		"INSERT INTO posts (author_id, title) VALUES ($1, $2)",
		user.id,
		"Getting Started",
	).execute();
	return user.id;
});

See Transactions.

---

Running queries: Query methods

Calling sql(...) or sql.as(...) returns a Query<Row, Params, Text> object:

const q = sql("SELECT id, email FROM users WHERE id = $1", 1);

const text = q.text;
const values = q.values;

declare const runner: typeof q;
const allRows = await runner.execute();
const first = await runner.executeTakeFirst();
const single = await runner.executeTakeFirstOrThrow();
const count = await runner.executeCount();

Properties

Property	Type	Description
q.text	string	The finalized SQL statement with positional $1, $2, … parameters and spliced fragments.
q.values	unknown[]	The array of parameter values sent to the database in $n order.

Execution methods

When createSql is initialized with a client or executor (or when one is passed to the execution method), q provides:

Method	Returns	Description
q.execute(executor?)	Promise<Row[]>	Executes the query and returns all matching rows.
q.executeTakeFirst(executor?)	Promise<Row | undefined>	Returns the first matching row, or undefined if none matched.
q.executeTakeFirstOrThrow(executor?)	Promise<Row>	Returns the first row, or rejects with NoResultError if no rows matched.
q.executeCount(executor?)	Promise<number>	Returns the count of affected rows (INSERT, UPDATE, DELETE, MERGE) or returned rows (SELECT).