Querier

Querier

A querier is UQL’s abstraction over database drivers to dynamically generate queries for any given entity. It allows interaction with different databases in a consistent way.

Using a Querier

The query methods live on the pool. For a single read, call one straight on the pool and the connection is acquired and released for you. For a unit of work (multiple statements, or any write), use pool.withQuerier() and call the methods on the querier it hands you. Same methods, two entry points: which to use, and why.

You write

import { pool } from './uql.config.js';
import { User } from './shared/models/index.js';

const users = await pool.findMany(User, {
  $select: { id: true, name: true },  // Whitelist scalar fields
  $exclude: { password: true },       // Blacklist scalar fields
  $populate: { profile: true },       // Load relations
  $where: {
    $or: [
      { name: 'roger' },
      { creatorId: 1 }
    ]
  },
  $sort: { createdAt: 'desc' },
  $limit: 10
});

Generated SQL (PostgreSQL)

-- Scalar projection combining $select and $exclude
SELECT "User"."id", "User"."name",
       -- $populate fields from joined relations
       "profile"."id" "profile.id", "profile"."picture" "profile.picture"
FROM "User"
LEFT JOIN "Profile" "profile" ON "profile"."userId" = "User"."id"
WHERE "User"."name" = $1 OR "User"."creatorId" = $2
ORDER BY "User"."createdAt" DESC
LIMIT 10

This is especially useful when you want to release the connection before doing slow non-DB work (e.g. calling an external API or LLM), preventing connection pool starvation:

// Phase 1: read from DB (single read - the pool one-liner acquires and releases for you)
const data = await pool.findOne(Resource, { $where: { id: resourceId } });

// Phase 2: slow external call (no connection held)
const result = await callExternalApi(data);

// Phase 3: write result back (writes belong in a unit of work)
await pool.withQuerier((querier) =>
  querier.updateOneById(Resource, resourceId, { result })
);

Raw projections in $select

For plain column selection use the object form ({ id: true }). When you need a computed column, $select also accepts an array of raw() expressions (SQL dialects only), each with an optional alias that becomes the result key:

You write

const stories = await pool.findMany(Story, {
  $select: [raw('*'), raw('LOG10("points" + 1) * 287014.58 + "createdAt"', 'hotness')],
  $sort: { createdAt: 'desc' },
});

Generated SQL (PostgreSQL)

SELECT *, LOG10("points" + 1) * 287014.58 + "createdAt" AS "hotness"
FROM "Story"
ORDER BY "Story"."createdAt" DESC

The object and array forms are mutually exclusive, and the array form is SQL-only - MongoDB rejects it.

The same query, every transport

A UQL query is a plain object, so the same value works unchanged across every layer. There is no per-transport rewriting, no DTO, no second schema to keep in sync, and the result stays fully typed everywhere, including populated relations:

Define it once

import type { Query } from 'uql-orm/type';
import { User } from './shared/models/index.js';

// filters, sorting, and nested relation loading - all type-checked against User
const query: Query<User> = {
  $select: { id: true, name: true },
  $where: { status: 'active' },
  $populate: { posts: { $select: { title: true }, $where: { published: true }, $limit: 5 } },
  $sort: { createdAt: 'desc' },
  $limit: 10,
};

Use it everywhere

// 1. On the server: straight on the pool (or a querier)
const onServer = await pool.findMany(User, query);

// 2. From the browser: against your REST API, same object and same types
const { data: inBrowser } = await httpQuerier.findMany(User, query);

// 3. Across an RPC boundary (tRPC / oRPC): it travels as JSON, untouched
const overRpc = await trpc.user.findMany.query(query);

The object you type-check on the server is the object the browser sends and the object RPC carries. See the HTTP core, browser client, and the tRPC / oRPC recipes.

Manual Querier Management

For advanced scenarios where you need full control over the querier lifecycle, use pool.getQuerier(). Always release it in a finally block:

import { User } from './entities/index.js';
import { pool } from './uql.config.js';

const querier = await pool.getQuerier();

try {
  const users = await querier.findMany(User, {
    $select: { id: true, name: true },
    $limit: 10
  });
} finally {
  await querier.release(); // Essential for pool health
}

---

Available Methods

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 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 querier.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 the other reads	Run a single read on its own connection - see parallel reads 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).

Choosing: pool.x vs. querier.x

pool.findMany(User, q) is exactly pool.withQuerier((querier) => querier.findMany(User, q)): the pool runs a single statement as its own unit of work (acquire a connection, run, release). A querier is the handle you get inside a withQuerier / transaction callback, where several statements share one connection.

You’re running…	Use	Why
A single read	pool.findMany / findOne / findOneById / findManyAndCount / count / aggregate (or pool.all for raw SQL)	Connection acquired and released per call, so Promise.all runs them on separate connections in parallel
Multiple statements, or any write	pool.withQuerier((querier) => …)	Writes touch relations across several statements, so they need one pinned connection
Work that must be all-or-nothing	pool.transaction((querier) => …)	Same pinned connection, plus begin / commit / rollback

Independent reads on the pool run in parallel; the same calls inside one withQuerier share a pinned connection and queue:

Two connections, in parallel

const [invoices, total] = await Promise.all([
  pool.findMany(Invoice, { $where: { paid: false } }),
  pool.count(Invoice, {}),
]);

One pinned connection - queries serialize

await pool.withQuerier((querier) =>
  Promise.all([querier.findMany(Invoice, {}), querier.count(Invoice, {})]),
);

An enclosing withContext scopes pool reads like any other query, so one wrapper covers a whole parallel fan-out:

await withContext({ tenantId }, () =>
  Promise.all([pool.findMany(Invoice, {}), pool.count(Invoice, {})]),
);

Note

• Pool reads take the entity-as-argument form only; for the { $entity } form or streaming, use withQuerier.
• On single-connection backends (better-sqlite3, Bun sqlite, D1) pool reads stay correct but share the one connection, so they serialize rather than parallelize.

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 querier.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 querier.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.