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.

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

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`)}`
);

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.

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.

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 LibSQL reads integer columns with intMode: "bigint", so an INTEGER arrives as a BigInt where other drivers give a number. Convert at the call site when it matters.

Deprecated: the string form of $queryRaw/$executeRaw

Before tagged templates, $queryRaw took (sql: string, params?: unknown[]). That shape still runs for one release, and announces itself once per method on the warning log channel.

// Deprecated — removed next release
await client.$queryRaw('SELECT id FROM "user" WHERE age >= $1', [18]);

// Replace with either:
await client.$queryRaw`SELECT id FROM "user" WHERE age >= ${18}`;
await client.$queryRawUnsafe('SELECT id FROM "user" WHERE age >= $1', 18);

The return type changed with it: these methods used to answer a QueryResult<T> envelope ({ rows, rowCount }). They now answer T[] and number.