Raw SQL

When the query API does not cover what you need, write the SQL yourself. all() returns typed rows and run() executes a write. Both are tags: write the statement right after the method, and each interpolated value is bound as a parameter:

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

const stats = await pool.all<{ status: string; total: number }>`
  SELECT status, COUNT(*) AS total FROM "User"
  WHERE "deletedAt" IS NULL AND "createdAt" > ${since}
  GROUP BY status
`;

const { changes } =
  await pool.run`UPDATE "User" SET "status" = ${'active'} WHERE "id" = ${123}`;

run() returns a QueryUpdateResult: changes is the number of rows affected, and ids holds the inserted ids where the engine reports them. Use it for INSERT, UPDATE, DELETE and DDL.

Each call runs one statement. Engines differ on several statements in one string, from running them all to refusing, so run a script as one call per statement. A column reads back as the query API reads it, and on Postgres each element of an array does too.

Filters and tenant context do not apply

Query filters, including security filters and soft-delete, and the multi-tenancy context apply only to UQL’s query methods. Scope raw SQL yourself, as the "deletedAt" IS NULL above does, or back it with row-level security in the database.

What sql interpolates

A value is bound ($1, ? or @p1, depending on the engine): a string, number, boolean, bigint, Date, bytes, null, or a list of them for = ANY(${ids}). undefined is refused, as in $where. Bind a JSON document as text: ${JSON.stringify(doc)}. A field read off refs(Entity) becomes its column, and another sql is spliced in place. That makes sql useful inside typed queries too:

import { sql, refs } from 'uql-orm';

const item = refs(Item);

await pool.updateMany(
  Item,
  { $where: { id: 1 } },
  { price: sql`ROUND(${item.price} * ${1.1}, 2)` },
);

sql`LOG10(${100})`.as('score'); // .as() names a $select projection

A ref renders its column the way a query would: through the naming strategy and @Field({ name }), escaped, and qualified by the table alias in scope. It follows a rename in your editor and carries the field’s type, so { price: item.price } compiles where { price: item.name } does not. The SQL around it is not type-checked.

Building a statement from parts

Compose a statement from smaller sql fragments, each binding its own values. A fragment you leave out can be an empty one:

const byStatus = status ? sql`AND "status" = ${status}` : sql``;

await pool.all`SELECT * FROM "User" WHERE "deletedAt" IS NULL ${byStatus}`;

sql.join(fragments, separator) puts a list of them together, a comma and a space apart unless you say otherwise:

const conditions = [sql`"createdAt" > ${since}`];
if (status) conditions.push(sql`"status" = ${status}`);

await pool.all`SELECT * FROM "User" WHERE ${sql.join(conditions, ' AND ')}`;

A piece of SQL held in a string, such as a shared ORDER BY clause, goes in the template as sql.text(clause). A plain string is bound as a value.

Trusted text and dialect-driven SQL

all() and run() also take a statement built apart as a sql value. For SQL you hold in a string, such as a statement read from a file, that value is sql.text(sql), which binds nothing:

import { sql } from 'uql-orm';

await pool.run(sql.text(sqlFromFile));

sql(callback) builds SQL that depends on the dialect. Whatever the callback writes is emitted as is, so bind input with ctx.addValue():

const byEmail = sql(({ dialect, ctx }) => {
  ctx.append(`LOWER(${dialect.escapeId('email')}) = `);
  ctx.addValue(email);
});

sql(...) called with a plain template literal does not compile, because it would splice the values into the text.

Never concatenate input

Only ${value} inside a sql template is bound. SQL built by string concatenation, or passed to sql.text, is open to injection, and nothing will catch it.

Raw SQL on the pool

pool.all() and pool.run() take a connection per call, like the pool’s other methods, so two of them in a Promise.all run in parallel. Inside a pool.transaction() callback they join the transaction. For statements that must share a connection, use the querier from pool.withQuerier() or pool.transaction().

all() and run() exist only on SQL queriers and pools (SqlQuerier, SqlQuerierPool), not on MongoDB; isSqlQuerier(querier) tells the two apart.

Next steps

• Sub-Queries: sql fragments inside a typed query.
• Transactions: raw statements inside a unit of work.
• Logging & Monitoring: the SQL and timings your queries produce.