Methods Reference

The methods below are on the querier and, with the noted exceptions, on the pool too.

Available Methods

Every data method below is on the pool as well: same name, same arguments. The querier runs it on the connection you are holding; the pool acquires one for that call and releases it (which to use). Only the last five 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 as an AsyncIterable for memory-efficient row-by-row iteration. Relation loading rules differ from findMany; see streaming & relations.
findManyAndCount(Entity, query, opts?)	Find records and return [rows, totalCount].
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 matching the query (or all records when omitted).
aggregate(Entity, query, opts?)	Run an aggregate query (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.
saveOne(Entity, data)	Insert or update based on ID presence.
saveMany(Entity, data[])	Bulk insert or update based on ID presence.
upsertOne(Entity, conflictPaths, data)	Insert or update based on conflict paths.
upsertMany(Entity, conflictPaths, data[])	Bulk insert or update based on conflict paths.
deleteOneById(Entity, id, opts?)	Delete by primary key. Soft-deletes 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).
restoreOneById(Entity, id)	Restore a soft-deleted record by its primary key.
restoreMany(Entity, query)	Restore soft-deleted records matching the query.
run(sql, values?)	Execute raw SQL (INSERT, UPDATE, DELETE).
all<T>(sql, values?)	Execute raw SQL SELECT with generics.
transaction(callback, opts?)	Run a transaction within a callback.
beginTransaction(opts?)	Start a transaction manually.
commitTransaction()	Commit the active transaction.
rollbackTransaction()	Roll back the active transaction.
release()	Return the connection to the pool.

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

Note

The query-based methods also support an RPC-friendly call pattern:

const users = await pool.withQuerier((querier) =>
  querier.findMany({ $entity: User, $where: { status: 'active' } }),
);

This lets you serialize queries as plain JSON and pass them across RPC/REST boundaries. The $entity field is stripped before query execution.

Insert IDs

insertOne/insertMany return the record IDs in payload order. IDs you provide, and IDs generated client-side via @Id({ onInsert }) (e.g. randomUUID), are always returned as-is on every database. Database-generated IDs are exact per row on dialects where the statement itself reports them: PostgreSQL, CockroachDB, MariaDB, and SQLite (including LibSQL/Turso, Cloudflare D1, and Bun’s native SQL) via INSERT ... RETURNING, 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 is applied only when the primary key is auto-increment and no record in the batch supplies an explicit ID (MySQL detects a clustered auto_increment_increment stride automatically); otherwise those entries are undefined instead of potentially wrong values.

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: PostgreSQL/CockroachDB/MariaDB/SQLite → [1, 5000] · MySQL → [undefined, 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. There’s no code-level fix for this - MySQL has no RETURNING - so avoid relying on inferred multi-row IDs for a table under heavy concurrent insert load, 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 if all-or-nothing behavior matters across such splits.

---

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. You must call querier.release() yourself.
pool.findMany(...) and every other operation	Run a single operation on its own connection - see pool vs. querier below.
pool.all(sql, values?) / pool.run(sql, values?)	Run one raw SQL statement on its own connection (SQL pools only).
pool.end()	Gracefully shut down the pool (close all connections).

Tip

Always prefer pool.withQuerier() or pool.transaction() for multi-statement work. They guarantee the connection is released. Use pool.getQuerier() only when you need manual lifecycle control (e.g., long-lived operations).

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

You write

await pool.upsertOne(User, { email: true }, {
  email: 'roger@uql-orm.dev',
  name: 'Roger',
});

Generated SQL (PostgreSQL / CockroachDB)

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:

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' },
]);

Generated SQL (PostgreSQL)

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

Generated SQL (MySQL / MariaDB)

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

Tip

upsertMany is ideal for data synchronization, bulk imports, or seeding. It reduces round-trips to a single statement regardless of the number of records.

Insert-vs-update detection

The result’s created field (see Raw SQL) reports true/false on Postgres and MySQL only. CockroachDB has no equivalent to Postgres’s xmax system column, so created is always undefined there - upsertOne/upsertMany themselves work the same as everywhere else.

upsertMany IDs

On RETURNING-based dialects (Postgres, CockroachDB, MariaDB, SQLite and its variants), ids/firstId are exact for every row regardless of insert or update. MySQL has no RETURNING, so its ON DUPLICATE KEY UPDATE affectedRows convention (1=insert, 2=update) only unambiguously identifies a single row - upsertOne still reports ids/firstId/created, but a multi-row upsertMany batch reports only changes, since the moment more than one row is touched affectedRows becomes a weighted sum that can’t be split back into per-row values without fabricating them. On MongoDB, ids only covers documents bulkWrite actually inserted - a matched-and-updated document’s _id isn’t in that response, so it’s simply absent from ids rather than guessed.

---