Methods Reference

Every data method exists on the querier and on the pool, with the same arguments (which to use).

Available methods

Method	Does
findMany(Entity, query, opts?)	Returns the matching records.
findOne(Entity, query, opts?)	Returns the first match, or undefined.
findOneById(Entity, id, query?, opts?)	Returns the record with that key, or undefined.
findManyAndCount(Entity, query, opts?)	Returns [rows, total].
findManyPage(Entity, query, opts?)	Returns a page past a cursor.
findManyStream(Entity, query, opts?)	Streams the records as an AsyncIterable.
count(Entity, query?, opts?)	Counts the matches, or a page of them.
exists(Entity, query?, opts?)	Whether anything matches.
estimatedCount(Entity)	The engine’s row estimate, without a scan.
aggregate(Entity, query, opts?)	Runs an aggregate query.
insertOne / insertMany(Entity, data, opts?)	Inserts records; returns their IDs.
updateOneById(Entity, id, data, opts?)	Updates by key; returns the count changed.
updateMany(Entity, query, data, opts?)	Updates the matches; returns the count changed.
saveOne / saveMany(Entity, data, opts?)	Inserts, or upserts rows naming their key (details); returns IDs.
upsertOne / upsertMany(Entity, { $conflict, $where?, $update? }, data, opts?)	Inserts, or updates on a conflict (details); returns IDs.
deleteOneById(Entity, id, opts?)	Deletes by key, softly when the entity has a soft-delete field; returns the count.
deleteMany(Entity, query, opts?)	Deletes the matches, softly by default; returns the count.
restoreOneById(Entity, id) / restoreMany(Entity, query)	Restores soft-deleted records. Throws if the entity has no soft-delete field.
all<T>(sql`...`) / run(sql`...`)	Runs raw SQL: all returns rows, run executes writes and DDL. SQL only.
transaction(callback, opts?)	Runs callback in a transaction.

Only a querier has:

• beginTransaction, commitTransaction and rollbackTransaction, which does nothing when no transaction is open.
• onCommit(callback), which runs after the outermost commit and never on a rollback.
• release(), which rolls back any open transaction and returns the connection. A released querier throws if used again.

Only the pool has:

• withQuerier(callback, opts?), and a transaction(callback, opts?) that hands the callback a querier. Both take { context } to run under a context.
• getQuerier(), whose querier you must release.
• end(), which closes every connection.

A SQL pool and its queriers also have all and run.

On a querier, every method taking a query also takes the entity as $entity inside it, so the whole call is one JSON value: querier.findMany({ $entity: User, $where: { status: 'active' } }), querier.upsertOne({ $entity: Job, $conflict: { key: true } }, row).

Options

The last argument, QueryOptions, takes:

• filters: turns query filters off or on for the call: { filters: false }, { filters: { softDelete: false } }, or withDeleted().
• hardDelete: on a delete, removes the rows instead of soft-deleting them.
• unfiltered: lets updateMany or deleteMany touch the whole table. Without it, they throw when the query has neither a $where value nor a $limit. The entity’s own filters do not count.
• returning: on a write, the fields to read back, or true for every field a read returns.

returning

With returning, a write resolves to the rows it wrote, typed by the fields you named, instead of IDs or a count:

const [order] = await pool.updateMany(
  Order,
  { $where: { status: 'pending' }, $sort: { createdAt: 'asc' }, $limit: 1 },
  { status: 'shipped' },
  { returning: { id: true, amount: true } },
);

returning: true reads back every field a read with no $select returns, typed as such, so a write returning whole rows needs no field list.

The rows are read in the same transaction, on MongoDB the one you open: after an insert or update, so they hold defaults, trigger output and stored computed columns, and before a delete.

A write is a single statement reporting its rows, RETURNING or SQL Server’s OUTPUT: an insert, an upsert, and a plain update or delete, on PostgreSQL, CockroachDB, the SQLite family and SQL Server, and all but the update on MariaDB. An upsert reports a row its $where or an empty $update kept by reading it after (MariaDB reports it in the statement), so a counter or a lease taken in one call is one round trip when it writes. Where a stamp restates the row after the write, as on SQLite and SQL Server, the rows are read back instead.

An update or delete writes exactly the rows it reports. On MySQL and MongoDB, for an update on MariaDB, and for a paged write, one filtered through a relation, a versioned row, or a delete with hooks or cascades, UQL reads the rows first and holds them, so another writer waits for the commit instead of changing them in between. SQLite cannot hold a row, so it fails that writer with a retryable error instead. MongoDB does the same inside a transaction you open; outside one, the read and the write are two steps.

Atomic arithmetic

$inc adds to a numeric field and $mul multiplies it, inside the statement, so no concurrent write is lost. Guard the change in $where and check the changed-row count:

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

NULL counts as 0, and a field takes $inc or $mul, not both. A bigint field takes a bigint operand and stays exact. An f.number() field without .scale() is an integer column, so a fractional step needs .precision(p).scale(s). The operators are plain JSON, so they work from the browser. On SQL the operand can also be a ref or sql of the field’s type: { total: { $inc: refs(Invoice).amount } }. JSON fields have their own update operators.

Insert IDs

Writes report each ID as the column’s value, or as a key map for a composite key, in payload order:

const ids = await pool.insertMany(User, [
  { name: 'Ada', email: 'ada@uql-orm.dev' },
  { id: 5000, name: 'Alan' }, // Alan's email gets its column default
]);
// [1, 5000] on every database

IDs you supply, or generate with a key’s .onInsert(), come back as they are. Generated IDs are exact per row on every engine but MySQL (Bun SQL’s MySQL mode included), which reports one ID per statement, so UQL infers the rest, mixed batches included. Each resolves to a defined key, so findOneById takes it as it is. On SQL engines, an insert refuses a key it leaves out unless the entity declares what fills it: .autoIncrement(), .default() or .onInsert().

MySQL under concurrent writes

Inferred IDs assume consecutive auto-increment values, which innodb_autoinc_lock_mode 0 or 1 guarantees. Under mode 2, the MySQL 8.0 default, concurrent inserts into the same table can break that. Set the mode to 0 or 1 on such tables, or do not rely on multi-row insert IDs.

Records in one insertMany can set different columns, and a column a record leaves out gets its default (NULL on SQLite, which still generates keys). A batch past the engine’s bind-parameter limit becomes several statements, which still land whole: one request on D1, libSQL, Turso and Neon, and a transaction elsewhere.

saveOne / saveMany

save decides per row by whether the payload names its primary key, not by 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: it links an existing row

So { tags: [{ id: 22 }] } writes the junction row and leaves tag 22 untouched, and a stale ID inserts a new row. A row naming its key and other columns fires @BeforeUpsert and @AfterUpsert, never the update hooks.

Composite keys

With a composite primary key, writes report each key as the map the by-id methods take, such as [{ studentId: 1, courseId: 'maths' }]. idOf(getMeta(Enrolment), row) builds the same map from a row. MongoDB refuses composite keys; see what is not supported.

Upsert operations

An upsert finds rows by its conflict keys, $conflict, the fields of a unique key. A found row is updated with the payload minus those keys, and its .onUpdate() fills, but keeps its own key: the payload’s key and its .onInsert() fills apply only to a row the upsert inserts. Otherwise the payload is inserted:

You write

await pool.upsertMany(User, { $conflict: { email: true } }, [
  { email: 'roger@uql-orm.dev', name: 'Roger' },
  { email: 'ana@uql-orm.dev', name: 'Ana' },
]);

PostgreSQL

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

MySQL

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

MariaDB

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

An upsert returns the IDs in payload order, inserted or updated, on every database, or the rows with returning. A versioned row it finds must carry its version, as an update does.

Updating a conflicting row its own way

Give the query an $update to give a conflicting row something else, operators included. A new row still gets the payload, which makes a counter one call:

import { f, Entity, Field } from 'uql-orm';

@Entity()
export class SearchQuery {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().unique()) term!: string;
  @Field(f.number().nullable()) uses?: number | null;
}

await pool.upsertOne(
  SearchQuery,
  { $conflict: { term: true }, $update: { uses: { $inc: 1 } } },
  { term: 'shoes', uses: 1 },
);

PostgreSQL

INSERT INTO "SearchQuery" ("term", "uses") VALUES ($1, $2)
ON CONFLICT ("term") DO UPDATE SET "uses" = COALESCE("SearchQuery"."uses", 0) + $3 RETURNING "id", "term"

An $update value can be SQL reading the found row through refs(Entity), with no alias: { uses: sql`GREATEST(${query.uses}, ${n})` } with const query = refs(SearchQuery) reads the row as it stands, under the naming strategy, on every SQL engine.

An empty $update ({}) leaves a conflicting row untouched, .onUpdate() fields included, so the call only inserts.

Updating only some conflicting rows

A $where beside $conflict names which found rows take the update, the payload or its $update; the others stay as they are. Here a job is queued again only once its last run finished:

import { f, Entity, Field } from 'uql-orm';

@Entity()
export class Job {
  @Field(f.number().autoIncrement()) id?: number;
  @Field(f.string().unique()) key!: string;
  @Field(f.string()) status!: string;
}

await pool.upsertOne(
  Job,
  { $conflict: { key: true }, $where: { status: ['done', 'failed'] } },
  { key: 'nightly', status: 'queued' },
);

PostgreSQL

INSERT INTO "Job" ("key", "status") VALUES ($1, $2)
ON CONFLICT ("key") DO UPDATE SET "status" = EXCLUDED."status" WHERE "Job"."status" = ANY($3) RETURNING "id", "key"

MySQL

INSERT INTO `Job` (`key`, `status`) VALUES (?, ?) AS `_uql_new`
ON DUPLICATE KEY UPDATE `Job`.`id` = LAST_INSERT_ID(`Job`.`id`),
  `status` = CASE WHEN `Job`.`status` IN (?, ?) THEN `_uql_new`.`status` ELSE `Job`.`status` END

It is one statement on every SQL database: SQL Server matches with WHEN MATCHED AND, and MySQL and MariaDB, whose ON DUPLICATE KEY UPDATE takes no WHERE, guard each column, so there a $where may read only one column the update sets.

On MongoDB, an upsert with an $update or a $where inserts the absent documents, then updates the found ones, as two batches; it needs no replica set. Run it in a transaction where a failure between the batches, or a document another writer inserts meanwhile, must not leave the result half applied. Any other upsertMany, like an insertMany, is one command, which MongoDB applies a document at a time; run it in a transaction for all or none.

Relations in an upsert

Cascaded relations are written on both branches, so an updated row’s relations are replaced; with $update, only the relations it names. Such an upsert, like any upsert on an entity with a security filter, reads the matching rows before writing, so a row another writer inserts in between makes it fail on its key.