> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Raw SQL

> Run your own SQL with all() and run(), values bound and rows typed.

Source: https://uql-orm.dev/querying/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:

```ts
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](https://uql-orm.dev/querying/filters.md), including `security` filters and [soft-delete](https://uql-orm.dev/entities/soft-delete.md), and the [multi-tenancy](https://uql-orm.dev/multi-tenancy.md) 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:

```ts
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](https://uql-orm.dev/naming-strategy.md) 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:

```ts
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:

```ts
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:

```ts
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()`:

```ts
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](https://uql-orm.dev/querying/querier.md#choosing-poolx-vs-querierx), 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](https://uql-orm.dev/querying/sub-queries.md): `sql` fragments inside a typed query.
- [Transactions](https://uql-orm.dev/querying/transactions.md): raw statements inside a unit of work.
- [Logging & Monitoring](https://uql-orm.dev/logging.md): the SQL and timings your queries produce.