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
});

Generated SQL (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:

// 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 })
);

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. Without $populate, 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';

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
}

---

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

Note

• 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';

async function grantCredit(db: UniversalQuerier, payment: Payment) {
  await db.insertOne(Payment, payment);
}

await grantCredit(pool, payment);                                   // its own unit of work
await pool.transaction((querier) => grantCredit(querier, payment)); // 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 expireCredits(db: UniversalQuerier, workspaceId: string) {
  return db.updateMany(Payment, { $where: { workspaceId, mode: 'trial' } }, { expiresAt: new Date() });
}

// Either both land or neither does, and neither function knows about the other.
await pool.transaction(async (querier) => {
  await expireCredits(querier, workspaceId);
  await grantCredit(querier, payment);
});

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.

Tip

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.