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 operation, call one straight on the pool and the connection is acquired and released for you. For a unit of work (several statements that share a connection, or must commit together), use pool.withQuerier() / pool.transaction() 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
  $populate: { profile: true }, // Load relations
  $where: {
    $or: [{ name: 'roger' }, { creatorId: 1 }],
  },
  $sort: { createdAt: 'desc' },
  $limit: 10,
});

PostgreSQL

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:

import { Item } from './shared/models/index.js';

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

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

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

The result is the row you asked for

A projection shapes the result type, so reading something the query never fetched is a compile error rather than a silent undefined:

const [user] = await pool.findMany(User, {
  $select: { id: true, name: true },
  $populate: { profile: true },
});

user.name; // string
user.profile; // Profile, because the query populated it

The columns it did not fetch are not on the row at all:

user.password; // not selected
user.posts; // not populated

$select: { password: false } and $exclude subtract instead, and a populated relation keeps the id its rows are assembled by, so the type follows what the statement actually returns. A query that projects nothing gives you the whole entity, as does one whose projection is not known statically - a raw projection, or a query built elsewhere and annotated as Query<User>. Every clause is still checked key by key: a typo in $select, $where, $sort, $populate, or inside a populated relation’s own query, fails to compile.

Naming a projected row

A narrowed row is not the entity, so a helper typed (user: User) => ... will not take one. Name the shape with QueryFindResult instead of widening the query:

import type { QueryFindResult } from 'uql-orm';

type UserCard = QueryFindResult<User, 'id' | 'name'>;

function render(user: UserCard) {
  return `${user.id} ${user.name}`;
}

The field names come second. A third parameter takes the map’s value - pass false for the subtractive form, QueryFindResult<User, 'password', false> - and a fourth and fifth take $exclude’s field names and $populate’s relation names.

Subtractive projection with $exclude

When you want every scalar column except a few, use $exclude instead of listing the rest by hand:

You write

const users = await pool.findMany(User, {
  $exclude: { password: true },
  $populate: { profile: true },
});

$exclude is mutually exclusive with a positive $select: combining $select: { name: true } with $exclude throws a TypeError, because a whitelist and a blacklist of the same scalars have no meaningful intersection. Turning a field off through $select: { password: false } is the equivalent shorthand. The rule is checked recursively, so it applies to nested $populate queries too.

Keys that a relation is assembled from survive any subtraction, exactly as they do under $select: the primary key of a joined row, and the foreign key a to-many relation is grouped by. $exclude: { id: true } alongside $populate still returns the id, because dropping it would leave the relation unfilled - as does ordering by a relation, which joins one. In a statement with no relation in it at all, nothing needs the key and it is subtracted like any other column.

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

import { raw } from 'uql-orm';
import { Post } from './shared/models/index.js';

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

PostgreSQL

SELECT *, LOG10("points" + 1) * 287014.58 + "createdAt" AS "hotness"
FROM "Post"
ORDER BY "Post"."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

When the connection has to outlive a single callback, take it yourself with pool.getQuerier() and bind it with await using, which releases it when the block exits however it exits:

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

async function report(companyId: number) {
  await using querier = await pool.getQuerier();

  const users = await querier.findMany(User, {
    $where: { companyId },
    $limit: 10,
  });
  if (!users.length) {
    return null; // released here too, with no finally to remember
  }

  return { users, total: await querier.count(User, { $where: { companyId } }) };
}

An unreleased connection is the one leak a pool cannot recover from, and await using is what makes an early return or a throw unable to cause it. try / finally with await querier.release() is the same thing written out, for a target that cannot have the syntax.

---

Every method, its arguments and what each database reports back is on the methods reference.

Choosing: pool.x vs. querier.x

pool.findMany(User, q) is exactly pool.withQuerier((querier) => querier.findMany(User, q)), and the same holds for every other operation: the pool runs a single operation 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 operations share one connection.

You’re running…	Use	Why
A single operation	pool.findMany / insertOne / updateMany / … (or pool.all for raw SQL)	Connection acquired and released per call, so Promise.all runs them on separate connections in parallel
Several operations that belong together	pool.withQuerier((querier) => …)	One pinned connection for all of them
Work that must be all-or-nothing	pool.transaction((querier) => …)	Same pinned connection, plus begin / commit / rollback

Two pool calls are two units of work, so nothing rolls the first one back if the second fails. When they have to commit together, that is a transaction.

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

Two connections, in parallel

import { Invoice } from './shared/models/index.js';

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 calls like any other query, so one wrapper covers a whole parallel fan-out:

import { withContext } from 'uql-orm';

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

Where pool calls differ

• Pool calls take the entity-as-argument form only; for the { $entity } form, use withQuerier.
• pool.findManyStream is the exception to acquire/run/release: it holds its connection until the loop ends, so consume it in a for await rather than abandoning the iterator.
• On single-connection backends (better-sqlite3, Bun sqlite, D1) pool calls stay correct but share the one connection, so they serialize rather than parallelize.

Accept a UniversalQuerier

Querier and QuerierPool implement the same interface, UniversalQuerier. A function that needs somewhere to run its queries takes that type, and the caller decides what it runs on:

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

async function raiseInvoice(db: UniversalQuerier, invoice: Invoice) {
  await db.insertOne(Invoice, invoice);
}

await raiseInvoice(pool, invoice); // its own unit of work
await pool.transaction((querier) => raiseInvoice(querier, invoice)); // joins the caller's

That one parameter is what makes helpers composable. Each of these is useful on its own, and the caller can still make any group of them atomic without touching them:

async function settleOutstanding(db: UniversalQuerier, companyId: number) {
  return db.updateMany(
    Invoice,
    { $where: { companyId, paid: false } },
    { paid: true },
  );
}

// Either both land or neither does, and neither function knows about the other.
await pool.transaction(async (querier) => {
  await settleOutstanding(querier, companyId);
  await raiseInvoice(querier, invoice);
});

Hardcode pool inside those functions instead and that last guarantee is gone: each call becomes its own unit of work, so a failure halfway through leaves the first write committed. Hardcode Querier and every caller has to open a unit of work even when it only wants one statement.

Which of the two types to take

The type is the whole contract. Take UniversalQuerier when the function runs queries, and Querier only when it genuinely needs a pinned connection: to open a transaction itself, to use the { $entity } form, or to hold all/run and a query on one connection.

Next Steps

• Comparison Operators: Everything you can put in $where.
• Deep Relations: $populate, relation filters, and relation sorting.
• Transactions: Units of work, isolation levels, and nesting.
• Streaming: Row-by-row iteration for large result sets.