Database: migrations, queries and tests

packages/db is the only package that talks to Postgres, and the API is its only caller. This page covers what its helpers are for and when to reach for them. The rules every database change follows (generated, append-only migrations; inIds() for id lists; jsonb from schema/jsonb.ts) are in AGENTS.md, and the conventions for a database test (its name, the variable it waits for, its cleanup) in CONTRIBUTING.md.

Migrations

bun run db:generate writes the next migration of yours into packages/db/drizzle-product/ from what src/schema/product.ts declares, and bun run db:migrate applies the kit’s history, then yours. Read the SQL before you migrate. Three cases need more than that.

An index on a big table

CREATE INDEX blocks writes to its table until the index is built, which on a table of millions of rows is a pause every request that writes there feels. CREATE INDEX CONCURRENTLY builds it while the table takes writes, but it cannot run inside a transaction, and db:migrate runs a migration in one. Declare the index with .concurrently(), and generate it with no other change pending:

export const event = pgTable('event', { /* … */ }, (table) => [
  index('event_time_idx').on(table.userId, table.occurredAt).concurrently(),
]);

bun run db:generate --name=event_time_index

db:generate finds a migration that holds only that statement and makes its first line -- no-transaction:

-- no-transaction
CREATE INDEX CONCURRENTLY "event_time_idx" ON "event" USING btree ("user_id","occurred_at");

db:migrate runs such a migration on its own, outside any transaction: first the migrations before it, then its one statement, then its row in the history. Every other migration runs as it always has. Mind:

One statement. A migration marked -- no-transaction holds that one statement. db:migrate refuses, before it applies anything, one that holds more and one whose CONCURRENTLY statement is not marked, and bun run test refuses both (migration-files.test.ts). When drizzle-kit wrote the index beside other changes, db:generate says so and exits 1: run bun run db:generate --undo, generate the other changes first, then the index on its own.
It waits. The build waits for the transactions already open in the database to end, so a long-running one holds the deploy up. It also reads the table twice, and takes longer than a plain build.
A failed build leaves an invalid index, after duplicate values under a unique index or a cancelled deploy. The next db:migrate drops it and builds it again. An index a stopped run had already built is recorded as applied.
Keep .concurrently() on the declaration. Without it drizzle-kit drops the index and builds it again, with the lock.
On a small table, a plain index() is simpler.

The marker works on any one statement Postgres refuses in a transaction, such as DROP INDEX CONCURRENTLY or REINDEX INDEX CONCURRENTLY in a migration from bun run db:generate --custom --name=<name>: put -- no-transaction on a line before it. Write it so that a second run does no harm (IF EXISTS): a run that stops between the statement and its history row runs it again. Work that must run online and takes more than one statement, such as a backfill in batches, belongs in a deploy hook (infra/scripts/hooks/pre-deploy.d/), not in a migration.

Foreign keys onto more than the id

A row that must stay with its parent’s owner references the parent by both columns, so that a page never sits in another account’s folder: (owner_id, folder_id) onto folder (owner_id, id). A tenant’s row stays in its parent’s workspace the same way, by the workspace’s id. Postgres accepts such a foreign key only once folder has a unique key on exactly those columns, and drizzle-kit writes a migration’s foreign keys before its indexes, and before the unique constraints it adds to a table that already exists. Declare the target with unique(), not uniqueIndex():

export const folder = pgTable('folder', { /* … */ }, (table) => [
  unique('folder_owner_id_key').on(table.ownerId, table.id),
]);

export const page = pgTable('page', { /* … */ }, (table) => [
  foreignKey({
    columns: [table.ownerId, table.folderId],
    foreignColumns: [folder.ownerId, folder.id],
    name: 'page_folder_fk',
  }),
]);

On a table the same migration creates, the constraint is part of its CREATE TABLE, ahead of every foreign key. On a table that already exists, generate the constraint on its own first, then the foreign key in a migration after it. A migration that has it the other way round fails bun run test, and db:generate and db:migrate name the foreign key and the key it waits for.

A migration that came out wrong

A migration you have just generated, and neither committed nor run, can be thrown away and generated again:

bun run db:generate --undo

It removes the newest migration of yours, its SQL, its snapshot in meta/ and its journal entry, and says so. Change src/schema/product.ts and run bun run db:generate again. It refuses, and says why, when:

git has committed the file, so another checkout or a deploy may have run it;
your development database (DATABASE_URL, or db:generate’s default) has run it, or does not answer;
the repository is not a git work tree, or there is no migration of yours to remove.

It asks git only whether HEAD holds the file: a migration committed on another branch, and not merged into the one you are on, counts as never committed. Before you undo one, make sure no branch you pushed holds it. A migration that may have run anywhere is never edited or removed: fix forward, with a new migration that changes what it did. --undo is the one change to meta/ that does not come from db:generate itself.

Queries

Queries that stay fast covers id lists and statistics.

Plans without JIT

Postgres compiles a plan to machine code (JIT, through LLVM) once its estimated cost passes jit_above_cost. The queries an API runs read and write a few rows by key, and gain nothing from it, while a plan priced high by a bad estimate, or by an enable_* setting a query turns off to force another plan, compiles for 0.5 to 1 s, every time it runs. So the kit runs Postgres with jit = off: the Compose stacks start it with postgres -c jit=off, and CI’s database job sets it before its first suite. A Postgres built without LLVM, such as Homebrew’s, never compiles, so a timing taken on your machine holds in production too.

An existing Compose deployment picks the setting up on its next docker compose up. A Postgres you run yourself takes it once, as a superuser, and keeps it across restarts:

alter system set jit = off;
select pg_reload_conf();

show jit answers off in a new session. A managed service sets it in its parameter group or flags instead, and alter database <name> set jit = off limits it to one database where you cannot change the server’s. explain (analyze) names a plan that compiled under JIT:.

Raw SQL

Drizzle’s bun-sql execute<T>() answers Record<string, any>[] whatever T you give it, so every field you read from its rows is any and nothing checks your use of it. Read a raw statement’s rows through rawRows<T>() from @repo/db instead:

import { rawRows, sql } from '@repo/db';

const [row] = await rawRows<{ notes: number; newest: Date | null }>(
  sql`select count(*)::int as notes, max(created_at) as newest
    from note where author_id = ${userId}`,
  tx, // optional: the pool by default, or a transaction
);

It returns a plain array, not the driver’s array subclass. T names the rows; nothing checks it at run time, so cast what the driver would otherwise hand back as text: count(*) and bigint arrive as strings unless the query casts them to int, and numeric always does.

Transactions a caller may already hold

A helper that writes several rows must write them in one transaction, and a caller may want them in a transaction of its own, so that they commit or roll back with what it writes. inTransaction(executor, fn) from @repo/db does both: it runs fn on executor when that is a transaction, and otherwise opens one, on the pool or on the pinned connection it is handed.

import { eq, type Executor, inTransaction } from '@repo/db';

/** Archives a project and withdraws its open invites, together. */
export function archiveProject(projectId: string, executor?: Executor) {
  return inTransaction(executor, async (tx) => {
    await tx.update(project).set({ archivedAt: new Date() }).where(eq(project.id, projectId));
    await tx.delete(projectInvite).where(eq(projectInvite.projectId, projectId));
  });
}

The usual executor ?? db runs each statement on its own when the caller passes nothing, or the pool, so a failure between them leaves the first one written. isTransaction(executor) tells a transaction, or a savepoint in one, from the pool, for a helper that needs to know.

withOrganizationLock(organizationId, fn, tx) takes the organization’s lock on a transaction you already hold, and keeps it until that transaction commits or rolls back, not only until fn returns. Count and write inside fn: a count made earlier in the same transaction, before the lock, may already be stale. Without the third argument, or given the pool, it opens a transaction of its own, as it always has.

Input a column can hold

Postgres refuses a value its column cannot hold, and a refusal inside a handler answers 500. A z.date() takes any instant a JavaScript Date holds, to the year 275,760, and z.int() any safe integer, so input the contract accepted can still fail at the insert. Validate it against the column instead, with the schemas packages/api-contract/src/procedures.ts exports for your contract files:

Column	Refused	Schema
`timestamp`, `timestamptz`	a year before 1 or after 9999, as the driver writes it	`StoredInstantSchema`
`date`	the year 0, which `z.iso.date()` takes	`StoredDateSchema`
`smallint`	outside −32,768 to 32,767	`SmallintSchema`
`integer`	outside −2³¹ to 2³¹ − 1	`IntegerSchema`
`bigint`	nothing a `number` holds, but past ±(2⁵³ − 1) a number arrives rounded	`BigintSchema`

@repo/api-client re-exports these five, and TimeZoneSchema, so a form validates a value the way the API will. StoredDateSchema’s refusal carries params: { reason: 'year_zero' }, which zodForm phrases in the user’s language.

StoredInstantSchema starts a day into the year 1 (EARLIEST_STORED_INSTANT): west of UTC, Postgres prints an earlier instant as the year 1 BC, which the driver cannot read back, so every query that read such a row would fail. Narrow each schema to your domain. Zod applies every bound you chain, so a narrower one wins and a wider one changes nothing:

import { z } from 'zod';
import { IntegerSchema, StoredInstantSchema } from './procedures';

export const EventInputSchema = z.object({
  startsAt: StoredInstantSchema.min(new Date('1970-01-01T00:00:00Z')),
  seats: IntegerSchema.min(1).max(10_000),
});

One list for a CHECK and its enum

A status, a kind or a role is a list of values twice over: in the CHECK constraint on its column, and in the z.enum() its procedures take. Declare it once, in packages/api-contract/src/product-values.ts, a file of yours that imports no package, so that packages/db can load it without loading the contract’s dependencies:

export const NOTE_STATUSES = ['draft', 'published'] as const;

import { NOTE_STATUSES } from '@repo/api-contract/values';
import { check, pgTable, text } from 'drizzle-orm/pg-core';
import { oneOf } from './constraints';

export const notes = pgTable(
  'note',
  {
    // …
    status: text({ enum: NOTE_STATUSES }).notNull().default('draft'),
  },
  (t) => [check('note_status_check', oneOf('status', NOTE_STATUSES))],
);

import { NOTE_STATUSES } from './product-values';

export const NoteSchema = z.object({ /* … */ status: z.enum(NOTE_STATUSES) });

A new value is then one edit, and one migration for the CHECK (bun run db:generate writes it); the enum follows. @repo/api-contract/values is the only contract path packages/db imports, and values.test.ts in each package holds both sides to it: packages/db fails on any other contract import, and the contract fails when a file the values reach imports a package. A feature’s lists can live in a file of their own that product-values.ts re-exports (export * from './notes-values';). To hand a list to clients, re-export it from product.ts. The kit’s document, stored-file and job statuses live in values.ts the same way, and @repo/db still exports them under the names it always has.

Time zones you store

A zone a user picks, stored so their times can be read back in it, must be one the runtime and Postgres both read, and read the same way. Their tz databases differ: the runtime’s ICU still takes US/Pacific-New, which Postgres 17 refuses in every at time zone, and Postgres takes EST5EDT, which Bun refuses on macOS and takes on Linux, whose ICU differs. An offset is worse: Postgres reads +05:00 with the POSIX sign, five hours west of UTC.

In the contract, validate it with TimeZoneSchema from ./procedures (or .refine(isTimeZoneName, timeZoneRule) on a string of your own): an IANA name the runtime knows, in any case, never an offset. Its refusal carries params: { reason: 'time_zone' }, which zodForm phrases in the user’s language.
In the handler, store what postgresTimeZone(zone) from @repo/db answers: the server’s spelling (europe/berlin becomes Europe/Berlin), or null for a name the server does not know.

const timeZone = await postgresTimeZone(input.timeZone);
if (!timeZone) throw new ORPCError('BAD_REQUEST', { message: 'Unknown time zone.' });

It reads the server’s names (pg_timezone_names) once per process. Keep instants in timestamptz columns and the zone beside them, and convert when you read (at time zone). A query that compares a timestamp without time zone column with the current time is tested as Comparing with the current time shows.

The database’s collation

Every id in the schema is text, so the database’s collation prices every join: a btree probe on a text key costs about 7 µs under a language collation (en_US.UTF-8, which a Postgres installed with Homebrew or a distribution package gives a new database) against 0.7 µs under C. Create a database with createdb -T template0 --locale=C <name>; the Compose stack creates its volume in the builtin C.UTF-8 locale, which compares the same way. bun run doctor prints the collation of the database DATABASE_URL names and warns about any other.

Postgres cannot change an existing database’s collation. To move one that already holds data, stop everything that writes to it (bun run dev:app, bun run serve), dump it, create a new database in the C locale, restore the dump into it, and point DATABASE_URL at the new one:

pg_dump -Fc -f before-c.dump "postgres://<you>@127.0.0.1:5432/<old>"
createdb -h 127.0.0.1 -T template0 --locale=C <new>
pg_restore --no-owner -d "postgres://<you>@127.0.0.1:5432/<new>" before-c.dump

--no-owner gives every restored object to the role that restores it, the one DATABASE_URL will name. bun run db:migrate then finds nothing to apply, and bun run doctor no longer warns. Keep the old database until the new one has served a while; dropdb <old> removes it. A Compose volume moves through the backup service instead (infra/README.md, “Backups and the restore drill”). The dump creates the pgvector extension, which the server must have available.

Names people read

order by name sorts by the database’s collation. Under the C locale, which the quickstart and bun run doctor recommend for a new database, that is byte order: Zebra before apple, Éclair after zulu. Never leave a name people read to the default:

A list the API returns whole: sort it with compareNames from @repo/db, Unicode’s root order, the same on every database: rows.sort((a, b) => compareNames(a.name, b.name) || (a.id < b.id ? -1 : 1)).
A list paged by name: order in SQL by name collate "und-x-icu", id, the order compareNames gives, and index that same expression, (name collate "und-x-icu", id). It needs a server built with ICU: select 1 from pg_collation where collname = 'und-x-icu' answers a row on one.

Both sort digits as text, Project 10 before Project 2. workspaces.list sorts with compareNames.

Testing against Postgres

The helpers below come from @repo/db (packages/db/src/testing.ts), for the kit’s database suites and yours; nothing in production calls them. A database suite is named *.db.test.ts and waits for RUN_DB_TESTS=1 beside DATABASE_URL, as CONTRIBUTING.md shows.

Claiming a prefix

Bun runs no afterAll when a run is stopped with Ctrl-C, nor after a hook that timed out, so a suite that only cleans up after itself leaves its whole fixture behind the first time it is interrupted. A suite therefore claims an id prefix before it writes anything, and gives every id it writes that prefix. The claim removes what an earlier run left under it:

import { afterAll, beforeAll, describe, it } from 'bun:test';
import type { TestPrefixClaim } from '@repo/db';

const enabled = Boolean(process.env.DATABASE_URL && process.env.RUN_DB_TESTS === '1');
const PREFIX = 'notes-test-';

(enabled ? describe : describe.skip)('notes in Postgres', () => {
  let database: typeof import('@repo/db');
  let claim: TestPrefixClaim | undefined;
  const userId = `${PREFIX}${crypto.randomUUID()}`;

  beforeAll(async () => {
    database = await import('@repo/db');
    claim = await database.claimTestPrefix(PREFIX);
    // … a user whose id is `userId`, and what the tests need
  }, 120_000);

  afterAll(async () => {
    try {
      // … delete what this run wrote, as before
    } finally {
      await claim?.release();
    }
  }, 120_000);

  it('keeps a note', async () => {
    /* … */
  });
});

In one transaction, the claim deletes:

organizations whose id or slug starts with the prefix, and their cascades;
users whose id starts with it, or whose email does at example.invalid or example.com, with their sessions, accounts and memberships;
their storage_deletion and revoked_storage_key rows, which no cascade reaches;
system jobs whose id or kind starts with it, and schedule claims whose name does;
verification rows whose identifier or value starts with it, and feature flags whose key does.

A row of yours that none of those cascades to, and whose key does not start with the prefix, goes through also, which runs first in the same transaction:

claim = await database.claimTestPrefix(PREFIX, {
  // A product table with no foreign key to the user who asked for its rows.
  async also(tx, prefix) {
    await tx.delete(importRun).where(sql`starts_with(${importRun.requestedBy}, ${prefix})`);
  },
  // Scratch tables and databases the suite creates: their names start with `test_`.
  tables: 'test_notes_',
  databases: 'test_notes_',
});

Three rules keep the sweep to the suite’s own rows:

A prefix is the suite’s own, and never the beginning of another suite’s. The shorter one’s sweep would reach the longer one’s live rows. packages/db/src/test-prefixes.test.ts reads every test file of the repository and fails when one claimed prefix begins another. The kit’s suites claim kit-test-<suite>-, so start yours with anything else.
A prefix is four or more lowercase characters ending in -, _ or .. Scratch table and database names must look like test_<suite>_. claimTestPrefix throws a RangeError for anything else, before it connects.
A user is matched by its email only at example.invalid or example.com, the domains fixtures use, so an account of yours that happens to share the prefix stays.

Every run of a prefix holds a shared advisory lock on it until it calls release() or its process ends, and the sweep needs the lock to itself. A run that starts while another holds the prefix sweeps nothing (claim.owned is false), and its own rows are safe from the run after it in turn. The lock lives on a connection of the claim’s own, named claimTestPrefix <prefix> in pg_stat_activity, so a suite that sizes the pool for what it proves, or closes it in afterAll, is not affected.

A lock belongs to its database. A suite that points its whole environment at a database it creates for itself claims in the one it creates it next to, with databaseUrl, as apps/api/src/seed.db.test.ts does. Databases are the server’s, though: a run of the same suite against another database of the server holds no claim in yours, and may be using a scratch database the sweep would find. So the sweep drops only a scratch database nobody is connected to, which still does not prove nobody is using it: such a run whose scratch database has no connection open at that moment, between two of its steps, loses it. Run a suite that makes scratch databases against one database of a server at a time.

Run database suites against a development database you can throw away: they write, and some create and drop databases next to it, so its role needs CREATEDB.

People, organizations and sessions

createTestUsers(claim, labels) from @repo/db makes a verified user per label, under the prefix the suite claimed: ids <prefix><label>-<8 hex>, emails <id>@example.invalid, so the claim’s sweep finds them after an interrupted run. Its cleanup() deletes them.

createTestTenant(claim, spec) makes an organization the same way, in one transaction:

const tenant = await database.createTestTenant(claim, {
  members: { alice: 'owner', bob: 'member', carol: 'member' },
  strangers: ['mallory'], // in no organization
  teams: { legal: ['bob'] },
  workspaces: { open: {}, legal: { team: 'legal' } },
});
tenant.users.bob.id; // typed by the labels the spec gives
tenant.workspaces.legal; // named and slugged `legal`

A team holds only members, each once, and a workspace’s team must be one of the spec’s; a spec that breaks either is refused before anything is written. members: {} makes an organization of nobody, for rows a test only needs to be another tenant’s. cleanup() deletes the organization, the storage_deletion and revoked_storage_key rows its cascade queued, then its people.

To call the handlers as one of them, sessionFor(person) from apps/api/test/sessions.ts builds the session a mocked getSession answers ({ role: 'admin' } for a platform administrator, { impersonatedBy } for an impersonated session). To go through Better Auth instead, httpTestClient({ origin: env.API_URL, webOrigin: env.WEB_URL, fetch: app.fetch }) from apps/api/test/http-client.ts signs people up (signUp), follows a captured verification link (verifyEmail), signs them in (signIn), and gives a fetch that sends a person’s cookie (fetchAs(person)), for createApiClient or a raw route. It sends nothing to any origin but the API’s, and follows no redirect: one comes back as the answer, as it does from app.fetch. Give the addresses it signs up the claimed prefix: their ids are Better Auth’s, so the claim finds them by address.

Running a suite

bun run test:db:all runs every database suite, each with the variable it waits for, as CI’s database job does. One suite in apps/api, packages/db or packages/auth runs from that directory with DATABASE_URL=… bun test src/<file>, plus its variable: each of them preloads a test environment (bunfig.toml), so it needs neither the root .env nor SKIP_ENV_VALIDATION. The preloads of packages/db and packages/auth set no DATABASE_URL, so it still decides whether their database tests run. apps/api’s sets one on port 1, where nothing listens, so that a unit test’s stray query fails at once: there the suite’s own variable decides, and a database suite run without DATABASE_URL fails at its first query.

Some of the kit’s suites reach past their prefix. The queue’s suites take jobs back through requeueStaleJobs, which takes back every job of the database whose lease has passed, as the minute sweep does, another suite’s included: two suites that sweep jobs must not run against one database at once. A suite of yours keeps to its own jobs: it claims one with claimTestJob and sweeps it with requeueStaleJobs(cutoff, { ids }) (Long jobs). apps/api/src/demo/sandbox.db.test.ts starts by sweeping every demo sandbox, through a prefix of its own, so two runs of it at once remove each other’s sandboxes. test:db:all runs one suite at a time.

Running every suite on a database of its own

bun run test:db:all runs the suites one after another against the database DATABASE_URL names, so each inherits the rows, dead tuples and statistics of the ones before it, and a plan or a timing a test measures can change with what ran first. -- --scratch gives each suite a database of its own:

bun run test:db:all -- --scratch

It creates a template next to DATABASE_URL’s database, with that database’s locale, migrates it once, and runs each suite on a copy of it, made in about a tenth of a second and dropped when the suite ends. Your development database is not written. The role needs CREATEDB, as the suites that create databases of their own already do. Ctrl-C drops what the run created; a run killed outright leaves databases named test_dball_<pid>_…, which the next one on the same machine drops. It is not the default yet: a suite that reads rows of yours from the development database, rather than writing its own, finds an empty copy.

ANALYZE in a test is fine: a test of a query plan needs fresh statistics, and under --scratch they reach no other suite. To prove that a code path ran ANALYZE, compare the table’s last_analyze in pg_stat_user_tables before and after it; under --scratch its analyze_count is the suite’s own as well.

Waiting for what happens elsewhere

A notification, a write on another connection, a worker or a timer finishes on its own schedule. A fixed Bun.sleep() is too short on a loaded machine and wasted time on a fast one; wait for the condition instead:

import { waitFor } from '@repo/db';

await waitFor(() => heard.includes(kind));
const finished = await waitFor(async () => (await findJob(id))?.finishedAt, 3_000);

waitFor(check, timeoutMs = 2_000, intervalMs = 10) resolves with the first truthy answer of check, and asks again when it throws, so an expect works inside it. Past the timeout it rejects, with the last error as its cause. Four more habits keep such tests steady:

Prime a watcher until it reports. A LISTEN takes effect once Postgres acknowledges it, and a stream a client opened on it a moment later. Wait for proof that it is listening (its backend in pg_stat_activity, or a probe notification it heard) before you make the change it should see.
Restore spies in finally or afterEach. A spy left on a shared module (spyOn(email, 'send')) answers every later test in the process.
Keep fixtures out of it. A test has bun’s 5-second default, and the inserts it makes before its first expect count against it. Build fixtures in beforeAll, with a timeout of its own, or give the test one.
Keep waitFor’s timeout under the test’s. When the test’s timeout ends first, bun reports a timeout, not what the test waited for.

Proving nothing was written

To prove that a procedure left some rows alone, read their versions before and after it:

import { changedRows, eq, rowVersions } from '@repo/db';

const before = await rowVersions(note, eq(note.authorId, userId));
await call(router.notes.rename, { id, name: 'Same name' }, options);
const after = await rowVersions(note, eq(note.authorId, userId));
expect(changedRows(before, after)).toEqual({ inserted: [], updated: [], deleted: [] });

rowVersions(table, where?, executor?) reads each row’s xmin, the transaction that last wrote it, keyed by its primary key as JSON text (["note-1"]). An UPDATE gives a row a new xmin even when it writes the values the row already had, so equal readings prove that no statement wrote those rows in between. changedRows names the keys inserted, updated and deleted, each list sorted. The pg_stat_* counters cannot prove the same: they count per connection, and Postgres publishes them late.

Proving a write is all or nothing

A procedure that writes several rows in one transaction should leave none of them when a later statement fails. failTransactions makes that failure happen, so the test goes through the procedure itself rather than calling its helpers with input the API would refuse:

import { failTransactions } from '@repo/db';

using failing = failTransactions({ matching: /insert into "note_revision"/ });
await expect(call(router.notes.update, input, options)).rejects.toThrow();
expect(failing.refused).toBe(1);
expect(changedRows(before, await rowVersions(note, eq(note.id, id)))).toEqual({
  inserted: [],
  updated: [],
  deleted: [],
});

Until restore() (or the end of the using block), every transaction on the pool refuses its statementth statement, or the first one whose SQL matches matching, by throwing an InjectedFailure in its place; Drizzle reports a failed query whose cause is that error. A savepoint’s statements count as its transaction’s. Statements outside a transaction, and transactions on a pinned connection, run as before. A second failTransactions before restore() throws.

Reading statistics

What belongs to one connection (a session setting, a temporary table, the statistics counters it has not published yet) needs the same connection for every query:

import { withPinnedConnection } from '@repo/db';

await withPinnedConnection(async (pinned) => {
  await pinned.db.insert(note).values(rows); // `pinned.db` goes wherever an `Executor` does
  await pinned.flushStatistics(); // `pg_stat_user_tables` read elsewhere includes them now
});

withPinnedConnection(run) reserves a connection from the pool and hands it back once run returns or throws. pinned.db has the type db has, so it needs no cast, and pinned.sql is the reserved Bun connection itself.

Comparing with the current time

A timestamp without time zone column, such as Better Auth’s expiry columns, compared with now() is read in the server’s zone. Run such a query under inServerTimeZone(zone, run) from @repo/db, in a zone on each side of UTC, as HTTP routes of your own shows.