Querier

A querier holds one database connection and runs UQL’s query methods on it. The pool has the same methods and takes a connection for you on each call, so most code calls the pool:

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 },
  $populate: { profile: true },
  $where: { $or: [{ name: 'roger' }, { creatorId: 1 }] },
  $sort: { createdAt: 'desc' },
  $limit: 10,
});

PostgreSQL

SELECT "User"."id", "User"."name",
       "profile"."id" "profile.id", "profile"."picture" "profile.picture",
       "profile"."bio" "profile.bio", "profile"."userId" "profile.userId"
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

Every method and its arguments are in the methods reference.

Choosing: pool.x vs. querier.x

pool.findMany(User, q) is exactly pool.withQuerier((querier) => querier.findMany(User, q)): one connection, taken and released for that call.

You’re running	Use
A single operation	pool.findMany, pool.insertOne, … (or pool.all for raw SQL)
Several operations on one connection	pool.withQuerier((querier) => ...)
Work that must be all-or-nothing	pool.transaction((querier) => ...)

Two pool calls are two units of work: if the second fails, the first stays committed. They run on separate connections, so Promise.all over pool calls runs in parallel, while the same calls inside one withQuerier wait for each other. A pool call holds no connection once it returns, so you can read, call a slow external API and write back without starving the pool. An enclosing withContext scopes pool calls like any other query, so one wrapper covers a whole parallel fan-out.

Where pool calls differ

• They take the entity as the first argument only. The { $entity } form needs a querier.
• pool.findManyStream holds its connection until the loop ends, so always finish the for await.
• Inside a transaction callback, a pool call runs on the transaction’s querier, so it is part of the transaction.
• On backends with one shared connection (the SQLite drivers, embedded Turso, PGlite and libSQL), pool calls wait for any open transaction.

The result is the row you asked for

The projection shapes the result type, so reading a field the query did not fetch is a compile error:

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

user.profile; // Profile | undefined: a join can find no row
user.company; // Company: `$required` drops the users with none
user.posts; // { title: string }[], empty when there are none
user.posts[0].body; // error: not selected
user.password; // error: not selected

Each populated relation is shaped by its own $select, $exclude and $populate, at every depth. A query with no projection returns the whole entity, and so does one whose projection is unknown at compile time: a raw projection, or a query typed as Query<User>. An entry typed boolean rather than true or false is decided at run time, so the field it names may be absent. A typo’d key in any clause, at any depth of $populate, fails to compile.

Naming a projected row

A narrowed row is not a User, so name its shape with QueryFindResult:

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

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

const card = {
  $select: { id: true, name: true },
  $populate: { posts: { $select: { title: true } } },
} as const satisfies Query<User>;
type UserCard = QueryFindResult<User, typeof card>;

It takes the field names, or a whole query. A function that forwards a query keeps its shape by capturing it the way the querier does:

import type { Captured, Query, QueryFindResult, Type } from 'uql-orm';

function findAll<E extends object, const Q extends Query<E>>(
  entity: Type<E>,
  q: Captured<Query<E>, Q>,
): Promise<QueryFindResult<E, Q>[]> {
  return pool.findMany(entity, q);
}

Excluding fields with $exclude

$exclude: { password: true } returns every field except password, as does $select: { password: false }. Combining $exclude with a positive $select throws a UqlUsageError. A joined to-one row always keeps its primary key, since that key tells whether the join matched.

Raw projections in $select

For computed columns, pass $select an array of sql expressions and name each with .as(). SQL databases only:

You write

import { sql } from 'uql-orm';

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

PostgreSQL

SELECT *, LOG10("points" + 1) * 287014.58 + "createdAt" "hotness" FROM "Post"

The array form cannot be mixed with the object form, and MongoDB rejects it. A populated relation’s $select takes it too.

The same query, every transport

A query is a plain object, so one value works on the server, in the browser and over RPC, typed the same everywhere. Type it as WireQuery<E>, a Query<E> without sql fragments, since JSON cannot carry them:

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

const query: WireQuery<User> = {
  $where: { status: 'active' },
  $populate: { posts: { $where: { published: true }, $limit: 5 } },
};

await pool.findMany(User, query); // server
await httpQuerier.findMany(User, query); // browser
await trpc.user.findMany.query(query); // tRPC

See the HTTP core, the browser client, and the tRPC and oRPC recipes.

Managing a querier yourself

When a connection must outlive one callback, take it with pool.getQuerier() and bind it with await using, which releases it on any exit, early return and throw included:

async function report(companyId: number) {
  await using querier = await pool.getQuerier();
  const users = await querier.findMany(User, { $where: { companyId } });
  if (!users.length) return null;
  return { users, total: await querier.count(User, { $where: { companyId } }) };
}

A connection you never release is a leak the pool cannot recover. Without await using, call await querier.release() in a finally.

Accept a UniversalQuerier

Querier and QuerierPool share one interface, UniversalQuerier. Type a helper’s database parameter with it and the caller decides where its queries run:

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

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

const invoice: Invoice = { companyId: 42, total: 1200 };

await raiseInvoice(pool, invoice); // its own unit of work
await pool.transaction(async (querier) => {
  await querier.updateMany(
    Invoice,
    { $where: { companyId: 42, paid: false } },
    { paid: true },
  );
  await raiseInvoice(querier, invoice); // commits with the update, or not at all
});

A helper that calls pool directly joins a caller’s transaction() callback too, but not a querier the caller holds outside one, such as a withQuerier connection or a beginTransaction. Taking UniversalQuerier works in every case. Take Querier instead only when the helper needs a pinned connection: to open a transaction itself, to use the { $entity } form, or to run all/run and a query on the same connection.

Next steps

• Comparison operators: everything you can put in $where.
• Relations: $populate, relation filters and relation sorting.
• Transactions: units of work, isolation levels and nesting.