Raw SQLSupported

A compiled query contains text, parameters and required execution effects, plus optional telemetry. Raw SQL uses the same driver boundary. Declare whether the statement requires the primary and returns rows; use an UNKNOWN operation requiring the primary when you cannot establish its effects.

Running a statement#

const rows = await driver.execute({
  effects: { operation: 'SELECT', requiresPrimary: false, returnsRows: true },
  text: `SELECT id, email FROM "users" WHERE "created_at" > $1 ORDER BY "created_at" DESC LIMIT 50`,
  parameters: [since],
});

rows is readonly Record<string, unknown>[]. Not typed, on purpose: nothing derived the column list, so nothing can credibly claim to know it.

Give the result a type you checked#

This is the part worth doing properly. Validate rather than cast, and the hand-written query gets the same guarantees as a compiled one:

import { assert } from '@zmdb/validator';

interface Row {
  id: number;
  email: string;
}

const rows = await driver.execute({ effects: { operation: 'UNKNOWN', requiresPrimary: true, returnsRows: true }, text: '...', parameters: [since] });
const typed = rows.map(r => assert<Row>(r));

If the query and the interface drift — a renamed column, a SUM that comes back as a string — the failure names the field instead of surfacing as undefined three layers up. as Row[] would have hidden it.

For a query returning whole rows of a known table, use the schema's own type:

const users = rows.map(r => assert<Entity<User>>(r));

Parameters, always#

// yes
{ text: 'SELECT * FROM "users" WHERE "email" = $1', parameters: [email], effects: { operation: 'SELECT', requiresPrimary: false, returnsRows: true } }

// no
{ text: `SELECT * FROM "users" WHERE "email" = '${email}'`, parameters: [], effects: { operation: 'SELECT', requiresPrimary: false, returnsRows: true } }

The placeholder syntax is the dialect's, because the text goes straight to the driver:

If a query has to run on more than one dialect, generate the placeholders:

import { formatPlaceholder, quoteIdentifier } from '@zmdb/sql';

const ph = (i: number) => formatPlaceholder(dialect, i + 1);
const list = ids.map((_, i) => ph(i)).join(', ');
const text = `SELECT * FROM ${quoteIdentifier(dialect, 'users')} ` + `WHERE ${quoteIdentifier(dialect, 'id')} IN (${list})`;

The interpolated identifiers and placeholders are generated by closed helpers; values remain in parameters.

Mixing raw fragments with the builder#

The builder's Operator type is ... | (string & {}), so an operator it does not know still compiles:

import { trustedTable } from '@zmdb/sql';

import { postgres } from '@zmdb/postgres';

createQueryCompiler(postgres).selectFrom(trustedTable('documents')).where('embedding', '<->', vec);

Handy for extension operators — see Database Extensions. Column and table names are still quoted by the compiler, so this widens the operator only, not the identifiers.

Inside a transaction#

MigrationConnection and the transactional db both take statements, so raw SQL participates normally:

await db.transaction(async () => {
  await driver.execute({ effects: { operation: 'UNKNOWN', requiresPrimary: true, returnsRows: false }, text: 'SET LOCAL statement_timeout = 5000', parameters: [] });
  await repo.create(dto);
});

When to reach for it#

Legitimately: ON CONFLICT (upsert), window functions, recursive CTEs, LATERAL, expression updates (increment), extension operators, and EXPLAIN ANALYZE.

Not legitimately: as a workaround for not knowing the DTO. If you are hand-writing SELECT * FROM users WHERE id = $1, findById is shorter and stays correct when the table changes.

Keeping raw SQL contained#

Two habits make hand-written SQL maintainable:

1. One module. Put raw queries in a queries/ file per table rather than inline in handlers. When a column is renamed, that is where you grep.
2. A test per query, against a real database. A compiled query is checked by the type system; a raw one is only checked by running it. See Testing.

---

See also: Query Compiler · Query Utilities · Writing a Driver