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 every 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. What an engine does with several in one string differs, from running them all to refusing, so run a script as a call per statement. A column reads as the query API reads it, and on Postgres an array reads each element the same way.

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, and a JSON document is bound as the text you write, ${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, has to be sql.text(clause) inside the template. As a plain string it is bound as a value. The same goes for SQL you used to pass with its values, run(sql, [a, b]): write those values into the statement instead.

Trusted text and dialect-driven SQL

all() and run() also take a statement built apart, such as a sql fragment. sql.text(sql) is one for SQL you hold in a string, a statement read from a file say, and 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 read helpers, 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.