> 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

# Methods Reference

> Every querier and pool method, the IDs an insert reports back per database, and the upsert operations.

Source: https://uql-orm.dev/querying/methods

## Available Methods

Every data method is on the [querier](https://uql-orm.dev/querying/querier.md) and on the pool, same name and arguments. The querier runs it on the connection you are holding; the pool acquires one for that call and releases it ([which to use](https://uql-orm.dev/querying/querier.md#choosing-poolx-vs-querierx)). Only the last four are the querier’s alone, because they are what owning a connection means.

| Method | Description |
| - | - |
| `findMany(Entity, query, opts?)` | Find multiple records matching the query. |
| `findManyStream(Entity, query, opts?)` | [Stream records](https://uql-orm.dev/querying/streaming.md) as an `AsyncIterable`, row by row, each with the relations `findMany` would load. |
| `findManyAndCount(Entity, query, opts?)` | Find records and return `[rows, totalCount]` - the page, and how many matched beyond it. |
| `findOne(Entity, query, opts?)` | Find a single record matching the query. |
| `findOneById(Entity, id, query?, opts?)` | Find a record by its primary key. |
| `count(Entity, query?, opts?)` | [Count records](https://uql-orm.dev/querying/counting.md) matching the query. A `$skip`/`$limit` counts that page instead of every match. |
| `exists(Entity, query?, opts?)` | [Whether anything matches](https://uql-orm.dev/querying/counting.md#exists), stopping at the first row. |
| `estimatedCount(Entity)` | [The engine’s own row estimate](https://uql-orm.dev/querying/counting.md#estimatedcount), read from its statistics without scanning. Approximate, whole-table, server-side only. |
| `aggregate(Entity, query, opts?)` | Run an [aggregate query](https://uql-orm.dev/querying/aggregate.md) (`GROUP BY`, `HAVING`, etc.). |
| `insertOne(Entity, data)` | Insert a single record and return its ID. |
| `insertMany(Entity, data[])` | Insert multiple records and return their IDs. |
| `updateOneById(Entity, id, data, opts?)` | Update a record by its primary key. |
| `updateMany(Entity, query, data, opts?)` | Update multiple records matching the query. One naming no rows (no `$where`, no `$limit`) throws; pass `{ unfiltered: true }` to mean the whole table. |
| `saveOne(Entity, data)` | Insert, or upsert on the primary key when the payload names it. Returns the ID. |
| `saveMany(Entity, data[])` | Bulk insert/upsert, per row, on the same rule. Returns the IDs in payload order. |
| `upsertOne(Entity, conflictPaths, data)` | Insert or update on the conflict paths. Returns `{ id, changes, created }`. |
| `upsertMany(Entity, conflictPaths, data[])` | Bulk insert or update on the conflict paths. Returns `{ ids, changes }`, IDs in payload order. |
| `deleteOneById(Entity, id, opts?)` | Delete by primary key. [Soft-deletes](https://uql-orm.dev/entities/soft-delete.md) when the entity has a soft-delete field; pass `{ hardDelete: true }` to remove permanently. |
| `deleteMany(Entity, query, opts?)` | Delete multiple records matching the query (soft by default; `{ hardDelete: true }` removes permanently). Naming no rows throws, as for `updateMany`. |
| `restoreOneById(Entity, id)` | Restore a [soft-deleted](https://uql-orm.dev/entities/soft-delete.md) record by its primary key. |
| `restoreMany(Entity, query)` | Restore soft-deleted records matching the query. |
| [`run(sql, values?)`](https://uql-orm.dev/querying/raw-sql.md) | Execute [raw SQL](https://uql-orm.dev/querying/raw-sql.md) (INSERT, UPDATE, DELETE). |
| [`all<T>(sql, values?)`](https://uql-orm.dev/querying/raw-sql.md) | Execute [raw SQL SELECT](https://uql-orm.dev/querying/raw-sql.md) with generics. |
| `transaction(callback, opts?)` | Run a [transaction](https://uql-orm.dev/querying/transactions.md) within a callback. |
| `beginTransaction(opts?)` | Start a [transaction](https://uql-orm.dev/querying/transactions.md) manually. |
| `commitTransaction()` | Commit the active transaction. |
| `rollbackTransaction()` | Roll back the active transaction. |
| `release()` | Roll back any unfinished transaction and return the connection to the pool. The querier is finished afterwards: using it again throws. |

The trailing `opts?` on reads, updates, and deletes is a [`QueryOptions`](https://uql-orm.dev/querying/filters.md): bypass [query filters](https://uql-orm.dev/querying/filters.md) for the call (e.g. `withDeleted()` to include soft-deleted rows, or `{ filters: false }`), or force `{ hardDelete: true }` on a delete.

> **RPC-friendly form**
>
> A query-based method also takes the entity inside the query, so the whole call serializes as one JSON value across an RPC or REST boundary (`$entity` is stripped before execution).
>
> ```ts
> const users = await pool.withQuerier((querier) =>
>   querier.findMany({ $entity: User, $where: { status: 'active' } }),
> );
> ```

### Atomic arithmetic

`$inc` adds to a numeric field and `$mul` multiplies it, inside the statement, so no read comes between and no concurrent write is lost. A NULL counts as 0, on every database, and a field takes one of them per update. Put the guard in `$where` and the count of changed rows tells you whether it held:

```ts
const taken = await pool.updateMany(
  Item,
  { $where: { id, stock: { $gte: quantity } } },
  { stock: { $inc: -quantity } },
);
if (taken === 0) throw new Error('sold out');
```

A `bigint` field takes a `bigint` operand, which keeps it exact. A fraction needs a column that holds one (`precision`/`scale`), as any written value does. Both are plain JSON, so they also work from the [browser](https://uql-orm.dev/browser.md), where `raw` does not. JSON fields have their own [update operators](https://uql-orm.dev/querying/json.md).

### Insert IDs

Every write reports its ID in one shape: the column’s value on a single key, the [key map](#composite-keys) on a composite. `insertOne`/`insertMany` return them in payload order.

IDs you provide, and IDs generated client-side via `@Id({ onInsert })` (e.g. `randomUUID`), come back as-is on every database. Database-generated ones are exact per row wherever the statement itself reports them: PostgreSQL, CockroachDB, MariaDB, and SQLite (including LibSQL/Turso, Cloudflare D1, and Bun’s native SQL) via `INSERT ... RETURNING`, MSSQL via `OUTPUT INSERTED`, and MongoDB via `insertedIds`.

Only MySQL (and Bun SQL’s MySQL mode) has no `RETURNING`: its driver reports one generated ID per statement, and UQL infers the rest arithmetically. That inference needs every row *in a statement* to have left the key to the database, so a mixed batch is split into one statement per kind and both halves report (MySQL detects a clustered `auto_increment_increment` stride automatically). An entry is `undefined` only where nothing could name the row: a non-auto-increment key the caller did not supply.

```ts
const ids = await pool.insertMany(User, [
  { name: 'Ada', email: 'ada@uql-orm.dev' },
  { id: 5000, name: 'Alan' }, // explicit id, and omits email
]);
// Alan's missing email falls back to its column default.
// ids on every database, MySQL included: [1, 5000]
```

> **MySQL under concurrent writes**
>
> MySQL’s inferred IDs assume the statement got a contiguous block of auto-increment values, which only holds under `innodb_autoinc_lock_mode` 0 (`traditional`) or 1 (`consecutive`). Under mode 2 (`interleaved`, MySQL 8.0’s default), other connections inserting into the same table concurrently with your batch can interleave with its allocation, so the inferred IDs may not be contiguous. With no `RETURNING` there is no code-level fix: avoid relying on inferred multi-row IDs for a table under heavy concurrent inserts, or set `innodb_autoinc_lock_mode` to 0 or 1.

Records in one `insertMany` batch may provide different subsets of columns: the statement uses the union of columns, and missing cells fall back to the database default (`DEFAULT` keyword; `NULL` on SQLite, which also triggers its auto-generated keys). Batches larger than the dialect’s bind-parameter limit are split into multiple statements automatically; wrap the call in a [transaction](https://uql-orm.dev/querying/transactions.md) if all-or-nothing behavior matters across such splits.

### saveOne / saveMany

`save` picks a statement per row from whether the payload **names its primary key**, not from whether the row exists:

| The row | What runs |
| - | - |
| Names no key | `INSERT` |
| Names its key, and carries other columns | `INSERT ... ON CONFLICT DO UPDATE` on that key |
| Names its key, and nothing else | nothing: a reference, not a write |

That last row is how a relation links something it did not author: `{ tags: [{ id: 22 }] }` writes the junction row and leaves tag 22 untouched.

A stale ID therefore writes the row instead of updating nothing. It fires `@BeforeUpsert`/`@AfterUpsert`, never the update pair ([lifecycle hooks](https://uql-orm.dev/entities/lifecycle-hooks.md)). IDs come back in payload order.

### Composite keys

On an entity with a [composite primary key](https://uql-orm.dev/entities/basic.md#composite-primary-keys), every write reports that key as the map the by-id methods take. No column holds it, so no statement reports one; the row is named from the payload that wrote it.

```ts
await pool.insertMany(Enrolment, [
  { studentId: 1, courseId: 'maths', grade: 'A' },
]);
// [{ studentId: 1, courseId: 'maths' }]
```

`idOf(getMeta(Enrolment), row)` names a row you already hold, the same way.

MongoDB refuses composite keys outright, on reads as well as writes; see [what is not supported yet](https://uql-orm.dev/entities/basic.md#what-is-not-supported-yet).

---

## Pool API

The pool manages the connection lifecycle. These are the main `pool` methods:

| Method | Description |
| - | - |
| `pool.withQuerier(callback)` | Acquire a querier, run `callback`, and auto-release, even on errors. |
| `pool.transaction(callback)` | Like `withQuerier`, but wraps the callback in a transaction. |
| `pool.getQuerier()` | Manually acquire a querier. **Releasing it is yours**: bind it with `await using`, or call `querier.release()` in a `finally`. Either way, an unfinished transaction is rolled back on release. |
| `pool.findMany(...)` and every other operation | Run a single operation on its own connection; see [pool vs. querier](https://uql-orm.dev/querying/querier.md#choosing-poolx-vs-querierx). |
| [`pool.all(sql, values?)` / `pool.run(sql, values?)`](https://uql-orm.dev/querying/raw-sql.md#raw-sql-on-the-pool) | Run one [raw SQL](https://uql-orm.dev/querying/raw-sql.md) statement on its own connection (SQL pools only). |
| `pool.end()` | Gracefully shut down the pool (close all connections). |

## Upsert Operations

Upsert (insert-or-update) resolves conflicts using **conflict paths**: the fields that define uniqueness. If a row with matching conflict path values already exists, it is updated; otherwise, a new row is inserted.

### `upsertOne`

```ts title="You write"
await pool.upsertOne(
  User,
  { email: true },
  {
    email: 'roger@uql-orm.dev',
    name: 'Roger',
  },
);
```

PostgreSQL / CockroachDB:

```sql
INSERT INTO "User" ("email", "name") VALUES ($1, $2)
ON CONFLICT ("email") DO UPDATE SET "name" = EXCLUDED."name"
```

### `upsertMany`

Efficiently upsert multiple records in a single statement:

```ts title="You write"
await pool.upsertMany(User, { email: true }, [
  { email: 'roger@uql-orm.dev', name: 'Roger' },
  { email: 'ana@uql-orm.dev', name: 'Ana' },
  { email: 'freddy@uql-orm.dev', name: 'Freddy' },
]);
```

PostgreSQL:

```sql
INSERT INTO "User" ("email", "name") VALUES ($1, $2), ($3, $4), ($5, $6)
ON CONFLICT ("email") DO UPDATE SET "name" = EXCLUDED."name"
```

MySQL:

```sql
INSERT INTO `User` (`email`, `name`) VALUES (?, ?), (?, ?), (?, ?) AS `_uql_new`
ON DUPLICATE KEY UPDATE `name` = `_uql_new`.`name`
```

MariaDB:

```sql
INSERT INTO `User` (`email`, `name`) VALUES (?, ?), (?, ?), (?, ?)
ON DUPLICATE KEY UPDATE `name` = VALUE(`name`) RETURNING `id` `id`
```

### What the upsert result reports

`id` (`upsertOne`) and `ids` (`upsertMany`, in payload order) name every row, inserted or updated, on every database. Where the statement cannot report a row’s id (on MySQL, CockroachDB, MSSQL and MongoDB, and for mixed-shape batches everywhere), UQL reads it back by the conflict columns.

`created`, on `upsertOne` only, is `true`/`false` on Postgres and MySQL (see [Raw SQL](https://uql-orm.dev/querying/raw-sql.md#run)), and `undefined` elsewhere: CockroachDB, for one, has no equivalent of Postgres’s `xmax` system column.

---