Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

Raw SQL

Tagged-template $queryRaw/$executeRaw, the Unsafe variants, and the sql composition helpers

When the query builder cannot express what you need, drop to SQL. There are four methods, in two families.

Method Argument Returns
$queryRaw`...` tagged template (values are bound) T[] — the rows
$queryRawUnsafe(sql, ...params) statement string + positional params T[] — the rows
$executeRaw`...` tagged template (values are bound) number — affected rows
$executeRawUnsafe(sql, ...params) statement string + positional params number — affected rows

The examples on this page use a user model whose id is a text key, s.string().id(). If your key has a format such as .ulid() or .uuid(), read Identifiers stored as bytes first.

Tagged templates bind, they never splice

Every ${...} in a tagged call becomes a bound parameter, rendered in the driver’s own placeholder style ($1 on PostgreSQL, ? on MySQL and SQLite). The value can never reach the statement as text, so this is safe by construction:

const search = req.query.name; // untrusted

const users = await client.$queryRaw<{ id: string; name: string }>`
  SELECT id, name FROM "user" WHERE name = ${search}
`;
// → SELECT id, name FROM "user" WHERE name = $1   params: [search]

$executeRaw is the same, for statements that change rows:

const updated = await client.$executeRaw`
  UPDATE "user" SET active = ${false} WHERE last_seen < ${cutoff}
`;
// updated: number

On SQLite3, libSQL, Bun SQLite and D1, a valid Date parameter is bound as date.toISOString() text in both safe and unsafe raw methods. The cutoff example therefore compares canonical timestamp TEXT. Raw SQL receives no field descriptor: for epoch-millisecond INTEGER or Julian-day REAL columns, bind that physical value yourself. Invalid Dates are refused before provider dispatch.

The generic parameter describes one row: write $queryRaw<User>, not $queryRaw<User[]>. It is a caller-supplied type assertion and performs no result validation or model decoding. sql.join([]) is an empty SQL fragment; it cannot infer whether you intended an empty IN predicate. Handle an empty list before composing IN (...) (for example, return an empty result or compose FALSE). Raw SQL also owns its table qualification; the driver’s namespace is not inserted into caller-written statements.

Identifiers stored as bytes

VibORM has no public helper that converts an identifier to its stored bytes. To address these rows, use the typed client (for example client.user.update({ where: { id }, data })), filter the raw statement on another column, or keep the key as text with a text-family native type, as described under String storage. Keys declared with .id() alone, .nanoid() or .cuid() are text and bind as written.

The Unsafe variants

Use these when the statement text itself is dynamic — a column name, an ORDER BY direction, a generated statement. The string is used verbatim; you own the escaping.

const rows = await client.$queryRawUnsafe<{ id: string }>(
  'SELECT id FROM "user" WHERE age >= $1',
  minimumAge
);

const deleted = await client.$executeRawUnsafe(
  'DELETE FROM "session" WHERE expires_at < $1',
  cutoff
);

Composing fragments

sql, join, empty and raw are exported from the package root (also available as viborm/sql). They build Sql fragments that nest inside each other and inside a $queryRaw/$executeRaw call.

import { empty, join, raw, sql } from "viborm";

// join binds plain values — one placeholder per value
const ids = ["u1", "u2", "u3"];
const rows = await client.$queryRaw<{ id: string }>(
  sql`SELECT id FROM "user" WHERE id IN (${join(ids)})`
);

// join also splices nested fragments, keeping their bound values
const filters = [sql`age >= ${18}`, sql`active = ${true}`];
const adults = await client.$queryRaw<{ id: string }>(
  sql`SELECT id FROM "user" WHERE ${join(filters, " AND ")}`
);

// empty contributes nothing — useful for an optional clause
const clause = onlyActive ? sql`WHERE active = ${true}` : empty;
await client.$queryRaw(sql`SELECT id FROM "user" ${clause}`);

// raw(text) splices text with no binding — identifiers, keywords, directions
await client.$queryRaw(
  sql`SELECT id FROM "user" ${raw(`ORDER BY ${column} DESC`)}`
);

raw(trustedText) deliberately inserts SQL text. Its constant template form (raw`CURRENT_TIMESTAMP`) is also accepted, but interpolations in a raw template are rejected. Use sql to bind values; never pass request data to raw, including dynamically chosen identifiers without an allowlist.

A prebuilt fragment can be passed straight to either safe method:

const fragment = sql`SELECT id FROM "user" WHERE age >= ${18}`;
const rows = await client.$queryRaw<{ id: string }>(fragment);

Passing a fragment and extra values is refused — the fragment already carries its parameters, so there is nowhere for the extras to go.

Safe methods accept only the tagged-template call or one Sql fragment. A plain string is deliberately an Unsafe-method call; no string compatibility overload remains.

Raw rows keep provider physical values. In particular, a model GeoPoint is a PostGIS geography on PostgreSQL, a geometry on MySQL, and canonical JSON text on SQLite. Use a typed model operation when you need the public { longitude, latitude } value.

Raw SQL in a transaction

Raw methods are lazy. Calling one captures the statement and parameters; no validation, warning, or database work happens until the returned operation is awaited or submitted to $transaction([...]).

The interactive transaction client carries the same four methods, bound to the open transaction. They travel the transaction’s connection, see its uncommitted writes, and roll back with it:

await client.$transaction(async (tx) => {
  await tx.user.create({ data: { id: "u1", name: "Alice" } });

  // Sees the row the line above wrote, even though nothing is committed yet
  const [row] = await tx.$queryRaw<{
    name: string;
  }>`SELECT name FROM "user" WHERE id = ${"u1"}`;

  await tx.$executeRaw`UPDATE "user" SET active = ${true} WHERE id = ${"u1"}`;
});

The array form accepts raw and model operations together. They execute in declared order inside the same atomic database unit, and later SQL can observe earlier database effects. Array members remain independent at the JavaScript level: one member cannot interpolate another member’s result.

const disable = client.$executeRaw`
  UPDATE "account" SET active = ${false} WHERE tenant_id = ${tenantId}
`;

const [affected, accounts] = await client.$transaction([
  disable,
  client.$queryRaw<{ id: string }>`
    SELECT id FROM "account" WHERE tenant_id = ${tenantId}
  `,
]);

Raw operations implement the complete Promise surface, so await, Promise.resolve, Promise.all, and assignment to Promise<T> keep working. They are deliberately not native Promise instances.

Governed raw reads

Raw SQL runs as the base client: on a view that filters rows (a permissions actor, soft delete, a bound argument), $queryRaw still sees every row. To keep a hand-written read inside the view, take the model’s rows from it with $rows(alias) and use them as a FROM item:

const view = db.$as(actor);

const hits = await view.$queryRaw<{ id: string; title: string }>`
  SELECT p.id, p.title
  FROM ${view.page.$rows("p")}
  JOIN page_fts ON page_fts.rowid = p.rowid
  WHERE page_fts MATCH ${query}
  ORDER BY rank
`;

view.page.$rows("p") renders a derived table holding exactly the rows a view.page.findMany() would filter to, controls bound to the view included:

(SELECT * FROM "main"."page" AS "q0" WHERE <the view's row domain>) AS "p"

Every table it reads is named with its database or schema ("main" on SQLite, the namespace on PostgreSQL and MySQL), so a CTE your text defines under a table’s name cannot stand for that table inside the filter. A MySQL client without a bound database (a supplied pool without namespace) refuses $rows for that reason.

On SQLite it also carries the table’s implicit rowid (SELECT "q0"."rowid" AS rowid, * ...), the key an FTS5 table joins on, unless the model is declared .withoutRowid().

  • It is a FROM (or JOIN) item, so the text around it cannot put the filter under an OR or an outer join’s ON. Several may appear in one statement, each from its own model, sharing one instant.
  • Take it on the handle you run the read on: the same view (the same $extends chain and $as or $bind values) or a transaction of it. A fragment from another client or view, the base client included, is refused.
  • The alias is lowercase letters, digits and _ (at most 63, not starting with a digit), so an unquoted p.id reads it on every dialect.
  • A model with read-gated fields (permissions fields: { x: { read: ... } }) is refused: SELECT * would disclose them.
  • Under permissions, a model the policy does not list reads no row (where its findMany throws ForbiddenError).
  • Only the tagged $queryRaw accepts it, interpolated directly into its template, on the client or a transaction client. $executeRaw, the Unsafe forms, a prebuilt sql`...` fragment holding it and any ORM query refuse it. Compose around it instead: $queryRaw`${select} FROM ${view.page.$rows("p")} ${rest}`.
  • The statement binds the domain’s values: past the driver’s bound-value limit (D1: 100) it is refused before it runs.

Raw SQL is still trusted code: nothing stops it from reading the table directly. $rows is how a read you write takes the view’s rows, not a sandbox.

Rows are driver-native

Raw results are not passed through the ORM’s typed read path. A column comes back exactly as the driver hands it over — for example an owned LibSQL client reads integer columns with intMode: "bigint", so an INTEGER arrives as a BigInt where other drivers give a number; a supplied LibSQL client keeps its own intMode. Convert at the call site when it matters.

Migrating string calls

V1 does not accept the earlier (sql: string, params?: unknown[]) shape on $queryRaw or $executeRaw. Replace it with a tagged template or pass trusted statement text and individual parameters to the matching Unsafe method.

// Tagged template: values are bound by the driver.
await client.$queryRaw`SELECT id FROM "user" WHERE age >= ${18}`;

// Trusted PostgreSQL statement text: parameters are positional arguments.
await client.$queryRawUnsafe('SELECT id FROM "user" WHERE age >= $1', 18);

If an earlier integration reads a QueryResult<T> envelope ({ rows, rowCount }), update that call site too: query methods return T[] directly, and execute methods return an affected-row number. See the V1 upgrade guide.

Was this page helpful?