Raw SQL at runtime (`session.raw`)¶

The escape hatch for the query the builder cannot yet express — without keeping a second database stack around.

The problem¶

A query builder will never cover 100% of SQL. That is fine — as long as there is a way out. Without one, a single unsupported query forces the whole project to carry a second database client (raw pg alongside the ORM), with two pools, two timeout settings and two row-mapping conventions. The cost is not proportional to the size of the gap.

Migrations already had that escape hatch (Op.execute). At runtime it is session.raw().

Usage¶

import { createEngine } from "tempest-db-js";

const engine = createEngine("postgresql://app@localhost/app");
const session = engine.session();

const rows = await session
  .raw<{ waiting: number; oldestSeconds: number }>(
    `SELECT count(*)::int                                      AS "waiting",
            EXTRACT(EPOCH FROM (now() - MIN(created_at)))::int AS "oldestSeconds"
       FROM outbound_messages
      WHERE status = $1`,
    ["queued"],
  )
  .all();

It returns the same AsyncResult as execute, so .all(), .first(), .one(), .oneOrNull(), .scalar(), .scalars() and .rowsAffected() are all available. On a SyncSession (SQLite) the method is identical, only synchronous.

Always parameterized¶

The signature is (sql, params) — never interpolation:

// ✅ Correct — the value becomes a bound parameter
session.raw("DELETE FROM sessions WHERE user_id = $1", [userId]);

// ❌ Wrong — an open door to SQL injection
session.raw(`DELETE FROM sessions WHERE user_id = '${userId}'`);

The SQL string is interpolated verbatim

raw does not validate the statement text — it cannot. Anything coming from outside your code goes into params, always. Passing a params that is not an array raises TypeError immediately, precisely to catch the call that "forgot" the parameters.

Integrated with everything else¶

raw is not a bypass of the runtime — it goes down exactly the same path as a compiled statement:

Logging. It shows up in onQuery like any other query.
Errors. A failure becomes QueryExecutionError, with the SQL and params attached.
Transactions. Inside transaction() it runs on the reserved connection and rolls back with it.

await engine.transaction(async (tx) => {
  await tx.raw("SET LOCAL statement_timeout = '5s'");
  await tx.execute(insert(Event).values(payload));
});

Optional coercion through the model¶

Without as, rows come back as the driver delivered them. Passing the model runs them through the same type coercion as execute — Date, bigint, Uint8Array, JSON and the column-name mapping:

const claimed = await session
  .raw<OutboundRow>(
    `UPDATE outbound_messages SET status = 'sending'
      WHERE id = ANY($1) RETURNING *`,
    [ids],
    { as: Outbound },
  )
  .all();

claimed[0].createdAt instanceof Date;  // true

When not to use it¶

raw is the exception, not the default. Before reaching for it, check whether the builder already solves it:

You need	Use
`FOR UPDATE SKIP LOCKED`	`.forUpdate({ skipLocked: true })`
`attempts = attempts + 1`	`sql.raw()` in `set`
`ON CONFLICT ... WHERE`	`onConflictDoNothing(target, { where })`
`lower(col) = lower($1)`	`{ ieq: value }`
`text[]`, `@>`, `&&`	`column.array()`

Why the schema is different

Reversible, autogenerated migrations are the core of the project, and a hand-written .sql blob is expensive there: the schema is finite and you control it. The space of queries is the opposite — infinite and driven by the product. That is why the schema stays typed and the query gets an escape hatch.

Recap¶

session.raw(sql, params, options?) runs a raw statement, always parameterized.
It returns the same result view as execute.
It participates in onQuery, QueryExecutionError and transaction().
{ as: Model } coerces rows through the model's types.
Prefer the builder whenever it already expresses the query.

Raw SQL at runtime (session.raw)¶