Skip to content
UQL
NewComposite primary keys5 min read

Raw SQL

Sometimes you need full control over your queries. UQL provides all() and run() for executing vanilla SQL while maintaining type safety through generics.

The raw tag

Section titled “The raw tag”

raw is a tagged template: the literal text is emitted as written, every interpolation is bound.

Atomic update, value bound
import { col, raw } from 'uql-orm';


await pool.updateMany(
  Item,
  { $where: { id: 1 } },
  { stock: raw`"stock" - ${quantity}` },
);


raw`${col('price')} > ${minimum}`; // col() is the alias-qualified, escaped column
raw`LOG10(${100})`.as('score'); // .as() names a $select projection

col() takes the column name as it is in the database, so a naming strategy is not applied.

raw('SQL') is @deprecated: it emits verbatim and cannot bind. npx uql-codemod rewrites it, moving an alias argument onto .as(). The callback form stays for dialect-driven SQL and emits whatever it writes, so bind user input with ctx.addValue().

Available Methods

Section titled “Available Methods”
Method Returns Use Case
all<T>(sql, values?) Promise<T[]> SELECT queries, reports.
run(sql, values?) Promise<QueryUpdateResult> Data manipulation (DML).

all()

Section titled “all()”

Use all() when you expect a result set. It accepts a generic type to ensure the returned array is fully typed.

Select with Generics
import { pool } from './uql.config.js';


interface UserCount {
  status: string;
  total: number;
}


const stats = await pool.all<UserCount>(`
  SELECT status, COUNT(*) as total
  FROM "User"
  WHERE "deletedAt" IS NULL
  GROUP BY status
`);


// stats: UserCount[]

run()

Section titled “run()”

Use run() for INSERT, UPDATE, or DELETE statements (DML) where you only care about the operation’s metadata (e.g., affected rows).

Update with Parameters
const result = await pool.run(
  'UPDATE "User" SET "status" = $1 WHERE "id" = $2',
  ['active', 123],
);


console.log(result.changes); // Number of affected rows

Response Metadata

Section titled “Response Metadata”

run() returns a QueryUpdateResult object containing:

  • changes: Number of rows modified, deleted, or inserted.
  • ids: Array of inserted IDs (for bulk inserts).
  • firstId: The first inserted ID.
  • created: Boolean indicating if a row was created (for upsert). Only Postgres and MySQL can reliably tell insert from update (Postgres via its xmax system column, MySQL via its affectedRows convention); CockroachDB (no xmax equivalent), MariaDB, and SQLite always return undefined here - check changes/firstId instead if you need to confirm the row exists.

Raw SQL on the Pool

Section titled “Raw SQL on the Pool”

SQL pools expose all() and run() directly, with the same connection-per-call semantics as the read helpers - so two pool.all() calls in a Promise.all run on separate connections in parallel:

Two aggregates, two connections, in parallel
import { pool } from './uql.config.js';


const [payments, usages] = await Promise.all([
  pool.all<{ sum: number }>(
    'SELECT COALESCE(SUM(value), 0) sum FROM "Payment" WHERE "workspaceId" = $1',
    [id],
  ),
  pool.all<{ sum: number }>(
    'SELECT COALESCE(SUM(cost), 0) sum FROM "Usage" WHERE "workspaceId" = $1',
    [id],
  ),
]);

For multiple statements that must share a connection or run atomically, use pool.withQuerier() / pool.transaction() and the querier’s all()/run() instead.

While run() is available on all queriers, all<T>() is specific to SQL-based dialects through the SqlQuerier interface - as are pool.all() / pool.run(), which exist only on SQL pools (SqlQuerierPool).

Next Steps

Section titled “Next Steps”