@spfn/core/db
Standalone CRUD helpers, a BaseRepository base class, transaction-aware read/write
routing, schema helpers, and PostgreSQL error mapping — built on PostgreSQL Drizzle drivers,
with postgres.js as the default and external providers such as PGlite supported. Connection
management, schema column helpers, and transactions each live in a
sub-module with its own README (linked below); this file covers the main module
(helpers, repository, query-utils, postgres-errors) and the connection/transaction entry
points re-exported from it.
Import paths
Everything ships from a single entry point:
import {
// CRUD helpers
findOne, findMany, create, createMany, upsert,
updateOne, updateMany, deleteOne, deleteMany, count,
// Repository
BaseRepository, TenantRepository, RepositoryError,
// Connection (sub-module: manager/)
initDatabase, getDatabase, closeDatabase, getDatabaseInfo,
// Transactions (sub-module: transaction/)
Transactional, runWithTransaction, getTransaction,
onBeforeCommit, onAfterCommit, onAfterRollback,
// Schema helpers (sub-module: schema/)
id, uuid, timestamps, foreignKey, tenantKey, enumText, typedJsonb, createSchema,
// Error mapping
fromPostgresError,
} from '@spfn/core/db';
There is no deeper public import path (no @spfn/core/db/helpers); import from
@spfn/core/db.
Public API (complete)
Everything re-exported from @spfn/core/db (src/db/index.ts):
CRUD helpers (helpers.ts) — standalone, no class needed:
findOne, findMany, create, createMany, upsert, updateOne, updateMany,
deleteOne, deleteMany, count
Repository (repository.ts, tenant/):
BaseRepository (abstract class), TenantRepository (abstract class, tables shared by many
tenants), RepositoryError (class), RepositoryDatabase (type), CountOptions (type)
Tenancy (tenant/tenancy.ts — see Tenancy):
defineTenancy, tenantList, TenantMismatchError (class), listTenancyTables (internal: the boot check)
Types: Tenancy, TenancyOptions, TenancyGuardOptions, TenancyOnDelete, TenancyReferenceOptions,
TenancyTableOptions, TenantListOptions, TenantListTable, TenantListWhereValue, TenantBindingKey,
TenantSource
PostgreSQL error mapping (postgres-errors.ts):
fromPostgresError
Connection / manager (sub-module — see manager/README):
createDatabaseFromEnv, initDatabase, getDatabase, setDatabase,
setDatabaseProvider, closeDatabase,
getDatabaseInfo, forceReconnectDatabase, createDatabaseConnection, checkConnection,
reportDatabaseError, isConnectionLevelError, resetConnectionErrorCounter,
getDrizzleConfig, detectDialect, generateDrizzleConfigFile, renderDrizzleConfig (0.3.0-beta.9: getDrizzleConfig returns schemaSource and schemaRegistry; the autoDetectSchemas option is gone — the CLI derives PostgreSQL schemas from the loaded modules, and a hand-written config passes schemaFilter itself)
Types: DatabaseClients, DatabaseInitOptions, DatabaseOptions, DatabaseProvider,
DatabaseTransaction, DefaultDatabase, DrizzleDatabase, PoolConfig, RetryConfig,
DrizzleConfigOptions
Transactions (sub-module — see transaction/README;
runInTransaction(fn, { tenant, user }) binds a transaction to a tenant and a user, see Tenancy):
Transactional, getTransaction, runWithTransaction, runInTransaction,
onBeforeCommit, onAfterCommit, onAfterRollback
Types: TransactionContext, TransactionDB, TransactionalOptions,
RunInTransactionOptions, BeforeCommitCallback, AfterCommitCallback,
AfterRollbackCallback
Schema helpers (sub-module — see schema/README, re-exported via
export * from './schema'):
id, uuid, timestamps, foreignKey, optionalForeignKey, tenantKey, auditFields,
publishingFields, softDelete, verificationTimestamp, utcTimestamp, enumText,
typedJsonb, createSchema, packageNameToSchema, getSchemaInfo
No such API. There is no
update,deleteById,findById,save, orinserttop-level helper — the helpers are exactly the ten listed above.BaseRepositoryexposes CRUD as protected_-prefixed methods (_findOne,_create, …) — they are not callable from outside the class. There is no publicgetDatabaseOrThrow(usegetDatabase, which already throws when uninitialized) and nodb.update()chain helper beyond what Drizzle itself provides.
Quick Start
import { BaseRepository, initDatabase, Transactional } from '@spfn/core/db';
import { id, timestamps, enumText } from '@spfn/core/db';
import { pgTable, text } from 'drizzle-orm/pg-core';
import { desc, isNull } from 'drizzle-orm';
// 1. Define a table with schema helpers
const USER_STATUS = ['active', 'inactive'] as const;
export const users = pgTable('users', {
id: id(), // bigserial PK
email: text('email').notNull().unique(),
name: text('name'),
status: enumText('status', USER_STATUS).default('active').notNull(),
...timestamps(), // createdAt, updatedAt (timestamptz)
});
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
// 2. Repository — extend BaseRepository, export a singleton
export class UserRepository extends BaseRepository
{
async findById(id: number): Promise<User | null>
{
return this._findOne(users, { id });
}
async findActive(limit = 10): Promise<User[]>
{
return this._findMany(users, {
where: { status: 'active' },
orderBy: desc(users.createdAt),
limit,
});
}
async create(data: NewUser): Promise<User>
{
return this._create(users, data);
}
}
export const userRepo = new UserRepository();
// 3. Initialize once (startServer() does this automatically; manual only in scripts)
await initDatabase();
// 4. Use inside a transactional route — auto commit / rollback
export const middlewares = [Transactional()];
export async function POST(c: RouteContext)
{
const user = await userRepo.create(await c.req.json());
return c.json(user, 201);
}
CRUD helpers (helpers.ts)
Standalone functions for operations that don't need a repository class. Each one resolves
the global DB instance internally (getDatabase('read'|'write')) and is transaction-aware
only through that instance — they do not read the AsyncLocalStorage transaction
context. Inside a transaction, use BaseRepository methods or getTransaction()
instead (see Pitfalls).
| Function | Signature | Returns |
|---|---|---|
findOne |
findOne(table, where) |
T | null |
findMany |
findMany(table, options?) |
T[] |
create |
create(table, data) |
T |
createMany |
createMany(table, data[]) |
T[] |
upsert |
upsert(table, data, { target, set? }) |
T |
updateOne |
updateOne(table, where, data) |
T | null |
updateMany |
updateMany(table, where, data) |
T[] |
deleteOne |
deleteOne(table, where) |
T | null |
deleteMany |
deleteMany(table, where) |
T[] |
count |
count(table, where?) |
number |
Note the argument order: where comes before data for updates
(updateOne(table, where, data)), and delete takes only where
(deleteOne(table, where)). T is inferred from the table (table.$inferSelect).
import {
findOne, findMany, create, upsert, updateOne, deleteOne, count,
} from '@spfn/core/db';
import { eq, and, gt, desc } from 'drizzle-orm';
// Find — object where (equality, ANDed) OR a Drizzle SQL condition
const user = await findOne(users, { id: 1 });
const adult = await findOne(users, and(eq(users.id, 1), gt(users.age, 18)));
const active = await findMany(users, {
where: { status: 'active' },
orderBy: desc(users.createdAt),
limit: 10,
offset: 0,
});
// Create
const created = await create(users, { email: 'a@b.com', name: 'A' });
// Upsert (INSERT … ON CONFLICT DO UPDATE) — target is required, set defaults to data
const cache = await upsert(cmsCache, data, {
target: [cmsCache.section, cmsCache.locale],
set: { content: data.content, updatedAt: new Date() },
});
// Update — where then data; returns null if nothing matched
const updated = await updateOne(users, { id: 1 }, { name: 'New' });
// Delete — returns the deleted row(s)
const deleted = await deleteOne(users, { id: 1 });
// Count — where is optional
const total = await count(users);
const activeOnly = await count(users, { status: 'active' });
findOne / updateOne / updateMany / deleteOne / deleteMany throw if the
resolved where clause is empty ('<op> requires at least one where condition') — you
cannot accidentally update/delete the whole table. findMany and count allow no where.
BaseRepository (repository.ts)
Abstract base class. Extend it to get transaction-aware connections plus the same CRUD set as protected methods.
import { BaseRepository } from '@spfn/core/db';
import { eq, isNull, desc } from 'drizzle-orm';
export class UserRepository extends BaseRepository
{
// Protected getters provided by BaseRepository:
// this.db → write instance (tx-aware: uses the active transaction if any)
// this.readDb → read instance (replica if configured; tx-aware)
async findById(id: number)
{
return this._findOne(users, { id });
}
async findActive()
{
// Drop to raw Drizzle for anything the helpers can't express
return this.readDb.select().from(users).where(isNull(users.deletedAt));
}
}
export const userRepo = new UserRepository();
Protected CRUD methods
Same semantics and argument order as the standalone helpers, prefixed with _. Use these
inside repository methods.
| Method | Returns |
|---|---|
_findOne(table, where) |
T | null |
_findMany(table, options?) |
T[] |
_findManyKeyset(table, { cursorColumn, limit, after?, order?, where? }) |
T[] |
_create(table, data) |
T |
_createMany(table, data[]) |
T[] |
_upsert(table, data, { target, set? }) |
T |
_updateOne(table, where, data) |
T | null |
_updateMany(table, where, data) |
T[] |
_deleteOne(table, where) |
T | null |
_deleteMany(table, where) |
T[] |
_count(table, where?, { cap? }) |
number — with cap, at most cap + 1 |
_count with cap stops counting past cap rows: it reads at most cap + 1 rows through a
limited subquery, and a result of cap + 1 means "more than cap". A list label such as
"1,000+" needs nothing more, and the count no longer scans every matching row on each
request.
These are protected — calling userRepo._findOne(...) from outside is a TypeScript
error. Expose a domain method (findById) instead.
this.db vs this.readDb
Both getters first check for an active transaction (getTransaction()); if one exists,
both return that transaction's DB so all work runs in the same transaction. Outside a
transaction, this.db returns the write/primary instance and this.readDb the read/replica
instance. Use readDb for SELECT, db for INSERT/UPDATE/DELETE. The _-helpers already do
this (_findOne/_findMany/_count use readDb; writes use db).
Their type is the injected database or its matching transaction type. Common Drizzle
query methods remain available in both contexts, while driver-only members such as a raw
$client are intentionally not exposed through these transaction-aware getters. Use the
provider outside repository operations when direct driver access is required.
withContext — error tracking
Wrap a raw query to attach repository/method/table context to failures and feed the
reconnect fast-path (see manager/README). On error it throws a RepositoryError carrying
{ repository, method, table, originalError }.
async findById(id: number)
{
return this.withContext(
() => this.readDb.select().from(users).where(eq(users.id, id)),
{ method: 'findById', table: 'users' },
);
}
The built-in _-helpers do not auto-wrap with withContext; wrap raw this.db /
this.readDb queries yourself when you want the enriched error + reconnect reporting.
Typed relations (optional)
import { defineRelations } from 'drizzle-orm';
import * as schema from './schema';
export const relations = defineRelations(schema);
export type AppRelations = typeof relations;
export class UserRepository extends BaseRepository<AppRelations>
{
// this.db and this.readDb preserve AppRelations
}
For an injected driver, pass its database type as the second generic:
import type { PgliteDatabase } from 'drizzle-orm/pglite';
type AppDatabase = PgliteDatabase<AppRelations>;
export class UserRepository extends BaseRepository<AppRelations, AppDatabase>
{
// this.db and this.readDb are AppDatabase
}
TenantRepository (tenant/) — tables shared by many tenants
For an app that keeps many tenants (workspaces, organizations, customers) in the same
tables. Mark a table's tenant column with tenantKey(); extend TenantRepository instead of
BaseRepository. Every method takes the tenant id first and writes the tenant condition
into the query itself, so a query this class builds cannot reach another tenant's rows.
Packages that never call tenantKey() are unaffected.
import { createSchema, foreignKey, id, tenantKey, timestamps, TenantRepository } from '@spfn/core/db';
import { index, text } from 'drizzle-orm/pg-core';
import { eq } from 'drizzle-orm';
const schema = createSchema('@acme/notes');
export const authors = schema.table('authors', {
id: id(),
tenantId: tenantKey(), // tenant_id bigint not null
name: text('name').notNull(),
}, (t) => [index('authors_tenant_id_idx').on(t.tenantId, t.id)]);
export const notes = schema.table('notes', {
id: id(),
tenantId: tenantKey(),
authorId: foreignKey('author', () => authors.id),
title: text('title').notNull(),
...timestamps(),
}, (t) => [index('notes_tenant_id_idx').on(t.tenantId, t.id)]);
export class NoteRepository extends TenantRepository
{
async list(tenantId: number, after?: number)
{
return this._findManyKeyset(tenantId, notes, { cursorColumn: notes.id, after, limit: 50 });
}
async listWithAuthor(tenantId: number)
{
return this._select(tenantId, { title: notes.title, author: authors.name })
.from(notes)
.innerJoin(authors, eq(notes.authorId, authors.id));
}
}
listWithAuthor(7) runs:
select "notes"."title", "authors"."name"
from "acme_notes"."notes"
inner join "acme_notes"."authors"
on ("notes"."author_id" = "authors"."id" and "authors"."tenant_id" = $1)
where "notes"."tenant_id" = $2
| Method | Tenant handling |
|---|---|
_findOne(tenantId, table, where) |
added to WHERE |
_findMany(tenantId, table, options?) |
added to WHERE |
_findManyKeyset(tenantId, table, options) |
added to WHERE |
_count(tenantId, table, where?, { cap? }) |
added to WHERE |
_create / _createMany(tenantId, table, data) |
written to the tenant column; a different tenant value in data throws, and _createMany checks every row before writing any |
_upsert(tenantId, table, data, { target, set? }) |
written to the tenant column; target must include the tenant column, set must not change it |
_updateOne / _updateMany(tenantId, table, where, data) |
added to WHERE; data naming the tenant column throws |
_deleteOne / _deleteMany(tenantId, table, where) |
added to WHERE |
_select(tenantId, fields?) |
the from table in WHERE, each joined tenant-aware table in its ON clause |
_selectOfUser(userId, fields?) |
by person, not by tenant: the from table must be a visibleTo tenancy table and gets user = userId in WHERE, each joined visibleTo table in its ON clause; see A second key |
- The tenant id must be a positive safe integer. Anything else —
0,NaN,undefined, a string — throws before a query is built. - Every method takes tenant-aware tables only. An ordinary table throws; it can still be
joined in
_select, where it gets no tenant condition. - Joined tables get the condition in ON, not WHERE. In WHERE, a left join's unmatched
rows would be filtered out and it would behave as an inner join.
_selectscopesinnerJoinandleftJoin(aliases included);rightJoin,fullJoin,crossJoin, the lateral joins and set operators (union, … — the methods and the standalone functions) throw — write those onthis.readDb. - A view cannot be joined. A view's columns are its own, so
_selectcannot tell which holds the tenant; joining one throws, as.from(view)does. Join the underlying table. - The upsert target must include the tenant column. With
target: [table.id], an insert could collide with another tenant's row and update it. - Update and delete still need a where of their own, as in
BaseRepository: the tenant condition alone does not count, so a bare call cannot rewrite or delete a whole tenant. - Index on the tenant column first.
tenantKey()cannot create an index (a Drizzle column cannot); declare each index in the table's third argument starting with the tenant column, e.g.(tenant_id, id)for keyset pages. this.db/this.readDbremain available and are not scoped, nor is a subquery you pass as a condition or join source. In local and development runs (NODE_ENVlocalordevelopment; an unsetNODE_ENVislocal), the database connection logs a warning — once per SQL text, with the SQL — when a query reads, updates or deletes a tenant-aware table without a condition on its tenant column. It reads SQL text, so it is a development aid, not isolation: an alias it cannot resolve, a rawsqltemplate or an unusual shape can make it miss a query or warn about a scoped one. A failure inside the check never fails the query. Other environments attach no logger.- Tenancy tables add rules R1–R6. For a table made with
tenancy.table(next section) every method also decides where the query runs and refuses a mismatched tenant; atenantKey()-only table keeps the behaviour above, plus the two mismatch checks.
Tenancy (defineTenancy) — tenant tables enforced by the database
TenantRepository keeps the queries it builds inside one tenant. A tenancy makes the
database enforce it as well, for every query: declare the tenant list once, build the tenant
tables from it, and each table gets composite foreign keys and a row-level security policy.
// server/entities/tenancy.ts
import { createSchema, defineTenancy, id, timestamps } from '@spfn/core/db';
import { getAuth } from '@spfn/auth/server';
import { text } from 'drizzle-orm/pg-core';
const schema = createSchema('@acme/notes');
export const workspaces = schema.table('workspaces', {
id: id(),
name: text('name').notNull(),
});
export const tenancy = defineTenancy({
tenants: workspaces, // or `users` from @spfn/auth/server: tenant = user
authorize: async (c, tenantId) => memberRepository.isMember(tenantId, Number(getAuth(c).userId)),
// column: 'workspace_id', // default 'tenant_id'
// property: 'workspaceId', // default 'tenantId': the key rows carry it under
});
export const authors = tenancy.table(schema, 'authors', {
id: id(), // required
name: text('name').notNull(),
});
export const notes = tenancy.table(schema, 'notes', {
id: id(),
authorId: tenancy.reference('author', () => authors), // required
reviewerId: tenancy.reference('reviewer', () => authors, { optional: true }),
title: text('title').notNull(),
...timestamps(),
});
// server/routes/notes.ts
export const listNotes = route.get('/workspaces/:workspaceId/notes')
.use([tenancy.guard({ param: 'workspaceId' })])
.handler(async (c) => noteRepository.list(tenancy.of(c)));
export const createNote = route.post('/workspaces/:workspaceId/notes')
.input({ body: Type.Object({ authorId: Type.Number(), title: Type.String() }) })
.use([tenancy.guard({ param: 'workspaceId' }), Transactional()])
.handler(async (c) =>
{
const { body } = await c.data();
return noteRepository.create(tenancy.of(c), body);
});
// Personal data: the tenant is the signed-in user
export const listDiaries = route.get('/me/diaries')
.use([tenancy.guard({ resolve: (c) => Number(getAuth(c).userId) })])
.handler(async (c) => diaryRepository.list(tenancy.of(c)));
// Outside a request (a script, a job)
await runInTransaction(() => noteRepository.archiveOld(42), { tenant: 42 });
spfn db generate writes, for notes:
CREATE TABLE "acme_notes"."notes" (
"id" bigserial PRIMARY KEY,
"tenant_id" bigint NOT NULL,
"author_id" bigint NOT NULL,
"reviewer_id" bigint,
"title" text NOT NULL,
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
CONSTRAINT "notes_tenant_id_id_key" UNIQUE("tenant_id","id")
);
ALTER TABLE "acme_notes"."notes" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "acme_notes"."notes" ADD CONSTRAINT "notes_tenant_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "acme_notes"."workspaces"("id");
ALTER TABLE "acme_notes"."notes" ADD CONSTRAINT "notes_author_fk" FOREIGN KEY ("tenant_id","author_id") REFERENCES "acme_notes"."authors"("tenant_id","id") ON DELETE CASCADE;
ALTER TABLE "acme_notes"."notes" ADD CONSTRAINT "notes_reviewer_fk" FOREIGN KEY ("tenant_id","reviewer_id") REFERENCES "acme_notes"."authors"("tenant_id","id");
CREATE POLICY "notes_tenant_isolation" ON "acme_notes"."notes" AS PERMISSIVE FOR ALL TO public
USING ("tenant_id" = current_setting('app.tenant_id')::bigint)
WITH CHECK ("tenant_id" = current_setting('app.tenant_id')::bigint);
unique (tenant_id, id)is what the composite foreign keys point at, and doubles as the(tenant_id, id)index keyset pages need.- A reference cannot cross tenants.
(tenant_id, author_id) → authors (tenant_id, id): a tenant 7 note pointing at a tenant 9 author is refused by the database. A required reference isON DELETE CASCADE; an optional one isNO ACTION— the author cannot be deleted while a note names it. A null optional reference is not checked (MATCH SIMPLE). { onDelete }chooses another delete action:'cascade','restrict'or'no action', on a required or an optional reference —tenancy.reference('session', () => sessions, { onDelete: 'restrict' })refuses to delete a session while a record still points at it.SET NULLandSET DEFAULTare not offered: the tenant column is part of the same foreign key, so they would changetenant_idtoo (the column-list form that avoids it needs PostgreSQL 15). Any other value throws when the schema module loads. A cascade that reaches arestrictreference refuses the whole delete.- A tenant with rows cannot be deleted: the tenant foreign key is
NO ACTION. - A connection that set no tenant gets an error, not rows: the policy reads
current_setting('app.tenant_id')withoutmissing_ok. - The tenant column is added for you (property
tenantIdordefineTenancy({ property }), columntenant_idordefineTenancy({ column })); declaring it incolumns, under its property or its column name, throws, and so does a table withoutid. The third argument adds your own indexes and constraints after these.propertylets an app whose code already readsrow.workspaceIdadopt a tenancy without renaming it; pass a string literal, because the row types are keyed by it, and notid. With another property,tenantIdis an ordinary key a table may use. tenancy.referencepoints at atenancy.tableof the same tenancy only — anything else throws when drizzle reads the table (getTableConfig,spfn db generate). The target is a function, so it may name a table defined later; a reference to its own table spells out the return type, as drizzle's.references()does:(): PgTable => notes.- Constraint names are fixed:
<table>_tenant_id_id_key,<table>_tenant_id_fk,<table>_<reference>_fk,<table>_tenant_isolation. A name over PostgreSQL's 63-byte limit becomes its first 54 bytes,_, and 8 hex digits of a hash of the whole name.
A second key: visibleTo
A membership table belongs to a tenant like any other, but a person also reads it across tenants — after sign-in, at the workspace picker, when deleting an account — before any tenant is known. Declare it as a tenancy table with a second key, the column naming the user:
export const members = tenancy.table(schema, 'members', {
id: id(),
userId: foreignKey('user', () => users.id),
role: enumText('role', ['owner', 'member']).notNull(),
status: enumText('status', ['active', 'invited']).notNull(),
}, (t) => [unique('members_workspace_user_key').on(t.workspaceId, t.userId)], {
visibleTo: 'userId', // last argument; fourth when there is no extra config
});
It gets the tenancy table's constraints, an index on the user column, and four policies in
place of _tenant_isolation — reads admit the tenant's rows or the rows of the user in
app.user_id; inserts, updates and deletes still need the tenant:
CREATE INDEX "members_user_id_idx" ON "acme_notes"."members" ("user_id");
CREATE POLICY "members_tenant_isolation" ON "acme_notes"."members" AS PERMISSIVE FOR SELECT TO public
USING ("workspace_id" = (case when nullif(current_setting('app.user_id', true), '') is null
then current_setting('app.tenant_id')
else nullif(current_setting('app.tenant_id', true), '') end)::bigint
OR "user_id" = nullif(current_setting('app.user_id', true), '')::bigint);
CREATE POLICY "members_tenant_insert" ON "acme_notes"."members" AS PERMISSIVE FOR INSERT TO public
WITH CHECK ("workspace_id" = current_setting('app.tenant_id')::bigint);
CREATE POLICY "members_tenant_update" ON "acme_notes"."members" AS PERMISSIVE FOR UPDATE TO public
USING ("workspace_id" = current_setting('app.tenant_id')::bigint)
WITH CHECK ("workspace_id" = current_setting('app.tenant_id')::bigint);
CREATE POLICY "members_tenant_delete" ON "acme_notes"."members" AS PERMISSIVE FOR DELETE TO public
USING ("workspace_id" = current_setting('app.tenant_id')::bigint);
visibleTonames a declared integer column (bigint or integer), notidand not the tenant column; anything else throws when the schema module loads, naming the table and the key.- A connection bound to neither still gets an error. The tenant read is lenient only
while a user is set; a
CASEevaluates one branch, which anORof permissive policies does not promise.nullif(..., '')because a pooled connection keeps''once a transaction that set the value has ended. - One policy per command, because a
FOR ALLpolicy applies to reads too and permissive policies are OR-ed. A write in a transaction bound to the user only fails instead of touching nothing. - The user column index
<table>_<user column>_idxis created for you; an app that already has the same index drops its own. - Adding
visibleToto an existing tenancy table generates a migration that drops and recreates_tenant_isolationand adds three policies and the index. A table withoutvisibleTogenerates the same SQL as before.
TenantRepository._selectOfUser(userId, fields?) is _select by person: rules U1–U5 bind
app.user_id the way R1–R5 bind app.tenant_id.
| # | Situation | What happens |
|---|---|---|
| U1 | no open transaction | BEGIN + SET TRANSACTION READ ONLY on the read connection → set_config('app.user_id', u, true) → query → COMMIT |
| U3 | in a transaction with no user yet | set_config('app.user_id', u) in it first, and the transaction is bound to u — beside its tenant, if it has one |
| U4 | in a transaction bound to u |
the query |
| U5 | in a transaction bound to another user | TenantMismatchError (key: 'user'), no SQL |
| — | tenancy.guard() authorized a tenant for this request |
no check: a read by person is allowed whatever the tenant |
| Table | from |
innerJoin / leftJoin |
|---|---|---|
visibleTo tenancy table |
WHERE user = u |
user = u in ON |
tenancy table without visibleTo, tenantKey()-only table |
throws | throws — read it with _select(tenantId) |
ordinary table (the tenant list, users) |
throws | no condition; a policy on it still applies |
class MemberRepository extends TenantRepository
{
async isMember(workspaceId: number, userId: number) // the guard's authorize
{
return await this._findOne(workspaceId, members, { userId, status: 'active' }) !== null;
}
async listWorkspacesOf(userId: number)
{
return this._selectOfUser(userId, { id: workspaces.id, name: workspaces.name, role: members.role })
.from(members)
.innerJoin(workspaces, eq(members.workspaceId, workspaces.id));
}
}
// Outside a request: read by person, then write by tenant
const memberships = await runInTransaction(() => memberRepository.listWorkspacesOf(userId), { user: userId });
runInTransaction(fn, { user }) binds a transaction to a user, as { tenant } binds it to a
tenant; with both, both are set. The same right joins, set operators and .prepare() are
refused as on _select. There are no writes by person: rows are written by tenant.
The tenant list: tenantList
With members visible through visibleTo, the tenant list itself can carry policies — a
transaction bound to a tenant sees that tenant's row, one bound to a user the tenants the
user's memberships point at:
// server/entities/workspaces.ts — declared before the tenancy that names it
import { createSchema, id, tenantList } from '@spfn/core/db';
import { text, type PgTable } from 'drizzle-orm/pg-core';
export const workspaces = tenantList(schema, 'workspaces', {
id: id(),
name: text('name').notNull(),
}, {
visibleThrough: { table: (): PgTable => members, where: { status: 'active' } },
});
CREATE POLICY "workspaces_tenant_visibility" ON "acme_notes"."workspaces" AS PERMISSIVE FOR SELECT TO public
USING ("id" = (case when nullif(current_setting('app.user_id', true), '') is null
then current_setting('app.tenant_id')
else nullif(current_setting('app.tenant_id', true), '') end)::bigint
OR EXISTS (
SELECT 1 FROM "acme_notes"."members" "m"
WHERE "m"."workspace_id" = "workspaces"."id"
AND "m"."user_id" = nullif(current_setting('app.user_id', true), '')::bigint
AND "m"."status" = 'active'));
CREATE POLICY "workspaces_tenant_insert" ON "acme_notes"."workspaces" AS PERMISSIVE FOR INSERT TO public
WITH CHECK (true);
CREATE POLICY "workspaces_tenant_update" ON "acme_notes"."workspaces" AS PERMISSIVE FOR UPDATE TO public
USING ("id" = current_setting('app.tenant_id')::bigint) WITH CHECK ("id" = current_setting('app.tenant_id')::bigint);
CREATE POLICY "workspaces_tenant_delete" ON "acme_notes"."workspaces" AS PERMISSIVE FOR DELETE TO public
USING ("id" = current_setting('app.tenant_id')::bigint);
- The membership table is a
visibleTotable of a tenancy over this list. Its tenant and user columns come from its markers when drizzle reads the table (spfn db generate); a table that is no tenancy table, of a tenancy over another list, or withoutvisibleTothrows then, naming both tables. Spell out the thunk's return type,(): PgTable => members: the membership's type depends on the list through the tenancy, a circle TypeScript cannot infer. wherekeys are membership properties, written as their column names; values are strings (quotes doubled), finite numbers, booleans ornull(is null), rendered as literals — the policy lands in a migration file, where a bound parameter cannot.- Creating a tenant needs no binding (its id comes from the insert); renaming or
deleting one needs that tenant's — a guarded route with
Transactional(), orrunInTransaction(fn, { tenant: id })outside a request. - A connection bound to neither gets an error. A path that walks every tenant (a reconciler, a cleanup job) reads the list through the owner connection or an explicit all-tenant read of the app's own; it is not offered here.
- The
existsreadsmembersunder its own read policy, so noSECURITY DEFINERfunction is needed, andmembers' policy never readsworkspaces, so the two never recurse.
Where a TenantRepository query runs (R1–R6)
| # | Situation | What happens |
|---|---|---|
| R1 | no open transaction, read method | BEGIN + SET TRANSACTION READ ONLY on the read connection → set_config('app.tenant_id', t, true) → query → COMMIT |
| R2 | no open transaction, write method | BEGIN on the write connection → set_config → query → COMMIT |
| R3 | in a transaction with no tenant yet | set_config(t) in it first, and the transaction is bound to t |
| R4 | in a transaction bound to t |
the query |
| R5 | in a transaction bound to another tenant | TenantMismatchError, no SQL |
| R6 | tenancy.guard() authorized another tenant for this request |
TenantMismatchError, no SQL |
Inside a request tenancy.guard() authorized, the guard's tenant binds the transaction as
early as it can (when the app has tenancy tables): a root transaction opened under the guard
without { tenant } runs set_config('app.tenant_id', t, true) right after its BEGIN, and a
guard that runs inside an open, still unbound transaction binds it. So
[tenancy.guard(...), Transactional()] and [Transactional(), tenancy.guard(...)] are both
bound before the handler's first query, and a write a plain BaseRepository sends — renaming
the tenant in a tenantList — runs under the tenant. runInTransaction(fn, { tenant: 9 })
under a guard for 7 binds 9 as asked; its TenantRepository calls are refused by R6, as before.
- The tenant id is a bound parameter; no SQL text is built from it.
set_config(..., true)ends with the transaction, so a pooled connection never carries it further. - The short transactions of R1/R2 are invisible to the transaction context: no hooks, no nesting, and code after the call sees no transaction.
- The binding lives on the root transaction; a savepoint shares it. A savepoint that set it and then rolled back undoes it, and the next call sets it again.
runInTransaction(fn, { tenant })sets it right afterBEGIN. A nested call naming another tenant throws before itsSAVEPOINT;requiresNew: trueis a root on its own connection and may name its own tenant._selectapplies R1–R6 when the builder runs (await,.then,.execute()), not when it is built;toSQL()is unchanged..prepare()on a query that reads a tenancy table throws — the statement would run later, outside these rules.tenantKey()-only tables (no policy) keep part 1's behaviour — no short transaction, noset_config— with R5 and R6 added.- Hand-written queries (
this.db,this.readDb) get no rule: inside a transaction bound to a tenant the policy shows that tenant's rows only; anywhere else they fail.
tenancy.guard and tenancy.of
tenancy.guard({ param: 'workspaceId' }) reads the route parameter, tenancy.guard({ resolve })
calls your function — exactly one of the two, checked when guard() is called.
- The id must be a positive safe integer: anything else answers 400 (
ValidationError) andauthorizedoes not run. An error thrown byresolvepropagates unchanged. authorize(c, tenantId): anything buttrueanswers 403 (ForbiddenError); a throw propagates unchanged.- The rest of the request runs bound to the tenant (an
AsyncLocalStorage, so concurrent requests never see each other's);tenancy.of(c)returns it, and throws on a route without the guard.
The guard opens no transaction, so reads keep using the read replica. Add Transactional()
to a write route — in either order: the transaction is bound to the guard's tenant before the
handler runs (see R1–R6 above).
Database roles
| Role | URL | Used by | Owns the tables |
|---|---|---|---|
| owner | DATABASE_OWNER_URL |
spfn db migrate, backup, restore, drop, tenant import |
yes — row-level security does not apply to it |
| app | DATABASE_URL / DATABASE_WRITE_URL / DATABASE_READ_URL |
the running app, spfn db tenant export |
no; not superuser, not BYPASSRLS |
PostgreSQL skips row-level security for a table's owner (and roles inheriting its
privileges), superusers and BYPASSRLS roles. Without DATABASE_OWNER_URL the four commands
use DATABASE_URL, as before. pg_dump stops with an error on a table whose policy applies
to its role, and PostgreSQL refuses COPY FROM into one — so backup and restore need the
owner too. FORCE ROW LEVEL SECURITY is not used: it would subject the owner, and break
both, and any data migration across tenants.
Grant the app role its row privileges once, as the owner, on every schema the app uses —
its own (acme_notes below) and each function package's (spfn_auth, and any other
package that ships migrations):
GRANT USAGE ON SCHEMA acme_notes TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA acme_notes TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA acme_notes TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA acme_notes
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA acme_notes
GRANT USAGE ON SEQUENCES TO app_user;
GRANT USAGE ON SCHEMA spfn_auth TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA spfn_auth TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA spfn_auth TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA spfn_auth
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA spfn_auth
GRANT USAGE ON SEQUENCES TO app_user;
The server's boot migration check reads the bookkeeping tables in schema drizzle
(__drizzle_migrations and one table per function package) through the app role, so grant
it read access there too:
GRANT USAGE ON SCHEMA drizzle TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA drizzle TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA drizzle
GRANT SELECT ON TABLES TO app_user;
Without it the check cannot read them: it logs one warning (permission denied for schema drizzle) and the server starts unchecked.
An app already deployed with the app role as owner moves the tables once (nothing does it automatically), as a superuser or the current owner:
ALTER TABLE acme_notes.notes OWNER TO app_owner; -- per table, sequences follow their table
-- or everything app_user owns in this database:
REASSIGN OWNED BY app_user TO app_owner;
One tenant at a time. spfn db tenant export <id> writes one tenant's rows of every
tenant table to a folder, in one snapshot, as the app role — so the policy admits that
tenant's rows only, behind the command's own WHERE. spfn db tenant import <dir> [--replace] puts them back in one transaction as the owner, which the policy does not
apply to: it checks every row's tenant column itself, and moves the sequences forward past
the imported ids. Restoring one tenant to a point in time is: restore a copy of the
database to that point, export the tenant from the copy, import --replace into
production. The CLI finds tenant tables through the Symbol.for('spfn.tenant.column') and
Symbol.for('spfn.tenancy.table') markers, so it reads them through any copy of
@spfn/core the app's schema loads. A visibleTo membership table is exported and imported
like any tenant table; a tenantList is not, as the tenant list never was.
Boot check. When the app declares tenancy tables, the server checks the role of its
write and read connections at startup: a role that owns a tenancy table, or is a superuser
or BYPASSRLS, refuses to boot in production (NODE_ENV=production) and logs one warning
line elsewhere — local development usually runs as the postgres superuser. A
tenantList is checked with them: its policy does not apply to such a role either. A check
that cannot run logs one warning and the server starts. An app without tenancy tables sends no
query.
Where clauses & query options (query-utils.ts)
Both the helpers and BaseRepository._* methods accept the same two where forms,
resolved by the internal buildWhereFromObject / isSQLWrapper utilities (not exported):
import { eq, and, or, gt, like, isNull, inArray, desc, asc } from 'drizzle-orm';
// 1. Object form — equality only, ANDed together. undefined values are dropped.
await this._findOne(users, { email: 'a@b.com', status: 'active' });
// → WHERE email = 'a@b.com' AND status = 'active'
// 2. SQL form — any Drizzle condition, for non-equality / OR / IN / NULL / etc.
await this._findMany(users, { where: and(eq(users.role, 'admin'), gt(users.age, 18)) });
await this._findMany(users, { where: or(eq(users.role, 'admin'), eq(users.role, 'mod')) });
await this._findMany(users, { where: inArray(users.id, [1, 2, 3]) });
await this._findMany(users, { where: isNull(users.deletedAt) });
await this._findMany(users, { where: like(users.email, '%@example.com') });
findMany / _findMany options: { where?, orderBy?, limit?, offset? }.
orderBy accepts a single SQL or an array:
await this._findMany(users, {
where: { status: 'active' },
orderBy: [desc(users.createdAt), asc(users.name)],
limit: 20,
offset: 40,
});
An empty object
{}(or one whose values are allundefined) resolves to no condition. ForfindOne/updateOne/deleteOnethat triggers the "requires at least one where condition" throw — build the SQLwhereconditionally (conditions.length ? and(...conditions) : undefined) only forfindMany/count, which permit it.
PostgreSQL error mapping (postgres-errors.ts)
fromPostgresError(error) maps a postgres.js / Drizzle error (by SQLSTATE code) to a
typed @spfn/core/errors class with the right HTTP status:
import { fromPostgresError } from '@spfn/core/db';
try {
await create(users, data);
} catch (err) {
throw fromPostgresError(err);
}
| SQLSTATE | Mapped error |
|---|---|
08xxx, 53xxx, 57xxx (connection / resources / operator) |
ConnectionError |
23505 unique_violation |
DuplicateEntryError (parses Key (field)=(value)) |
23502/23503/23514/23000/23001 constraints |
ConstraintViolationError |
40001 etc. transaction rollback |
TransactionError |
40P01 deadlock_detected |
DeadlockError |
42xxx syntax / undefined object |
QueryError (status 400) |
| anything else | QueryError (status 500) |
Inside BaseRepository.withContext and Transactional middleware, query errors are already
reported to the reconnect fast-path; fromPostgresError is for explicit conversion to a
client-facing typed error.
Sub-modules
These have their own READMEs — do not duplicate their APIs here, link to them:
- manager/README.md — connection lifecycle (
initDatabase,getDatabase,closeDatabase,getDatabaseInfo), pooling, env vars (DATABASE_URL/DATABASE_WRITE_URL/DATABASE_READ_URL), health checks, pool rebuild / reconnect (forceReconnectDatabase,reportDatabaseError), and the Drizzle config generator. - transaction/README.md —
Transactional()middleware,runWithTransaction/runInTransaction,getTransaction, theonBeforeCommit/onAfterCommit/onAfterRollbackhooks, and theAsyncLocalStorage-based context. - schema/README.md — column helpers (
id,uuid,timestamps,foreignKey,enumText,typedJsonb, …) and PostgreSQL schema isolation (createSchema,packageNameToSchema,getSchemaInfo). - migrations/index.ts
(linked to the source on GitHub: this sub-module has no README, and the package ships
only READMEs) — which migrations each installed function
package ships and which the database has applied:
discoverFunctionMigrations,collectMigrationStatus,pendingMigrationTargets,countPendingMigrations. Read-only — applying migrations isspfn db migrate. One implementation behindspfn db status, the server's migration boot gate and the detailed health payload, so they cannot disagree about what "pending" means.
Pitfalls & anti-patterns
getDatabase()throws when uninitialized — it does not returnnull. CallinitDatabase()first (the server does this for you; only scripts/tests need it). Helpers and repositories surface this as a thrown "Database not initialized" error.- Standalone helpers are not transaction-context aware.
findOne/create/… resolve the global read/write instance, not theAsyncLocalStoragetransaction. Inside aTransactional()route orrunInTransaction, callBaseRepository._*methods (which checkgetTransaction()) orgetTransaction()directly — otherwise the write escapes the transaction and won't roll back. _findOne/_updateOne/_deleteOne(and their plural update/delete forms) throw on an empty where. This is a safety rail against full-table writes — pass a real condition. OnlyfindMany/_findMany/count/_countaccept "no where".- Argument order is
(table, where, data)for updates and(table, where)for deletes. Don't passdatabeforewhere. - Object where is equality + AND only. For
>,<,LIKE,IN,IS NULL, orOR, use a Drizzle SQL condition (and(eq(...), gt(...))) — passing an object can't express those. - Protected
_methods aren't callable externally.userRepo._findOne(...)is a compile error by design; wrap them in a domain method on the repository. - Use
this.readDbfor reads,this.dbfor writes in raw queries. Reaching forthis.dbon a SELECT skips the read replica; reaching forthis.readDbon a write hits the replica (read-only / stale). The_-helpers already route correctly. - Export repositories as singletons.
export const userRepo = new UserRepository(). Transaction propagation works throughAsyncLocalStorage, so a shared instance is correct — you do not pass adb/txhandle around. (Fresh instances in tests are fine.) - Don't start your own transaction inside repository write methods. Let route
Transactional()middleware (or an explicitrunInTransaction) own the boundary. upsertrequirestarget.setis optional and defaults to the inserteddata; passsetexplicitly (e.g.updatedAt: new Date(), or asql\…`` expression) when the conflict update should differ from the insert.
Complete example
// src/server/entities/users.ts
import { pgTable, text } from 'drizzle-orm/pg-core';
import { id, timestamps, softDelete, enumText } from '@spfn/core/db';
const ROLE = ['user', 'admin'] as const;
export const users = pgTable('users', {
id: id(),
email: text('email').notNull().unique(),
name: text('name'),
role: enumText('role', ROLE).default('user').notNull(),
...softDelete(), // deletedAt, deletedBy
...timestamps(), // createdAt, updatedAt
});
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
// src/server/repositories/user.repository.ts
import { BaseRepository } from '@spfn/core/db';
import { and, eq, desc, isNull } from 'drizzle-orm';
import { users, type User, type NewUser } from '../entities/users';
export class UserRepository extends BaseRepository
{
async findById(id: number): Promise<User | null>
{
return this._findOne(users, { id });
}
async createWithDedup(data: NewUser): Promise<User>
{
const existing = await this._findOne(users, { email: data.email });
if (existing)
{
throw new Error('Email already exists');
}
return this._create(users, data);
}
async softDelete(id: number, deletedBy: string): Promise<User | null>
{
return this._updateOne(users, { id }, { deletedAt: new Date(), deletedBy });
}
async findActiveAdmins(): Promise<User[]>
{
return this._findMany(users, {
where: and(eq(users.role, 'admin'), isNull(users.deletedAt)),
orderBy: desc(users.createdAt),
});
}
async paginate(page: number, limit: number)
{
const offset = (page - 1) * limit;
const [items, total] = await Promise.all([
this._findMany(users, { orderBy: desc(users.createdAt), limit, offset }),
this._count(users),
]);
return { items, total, page, limit, totalPages: Math.ceil(total / limit) };
}
}
export const userRepo = new UserRepository();
// src/server/routes/users.ts — Transactional boundary owned by the route
import { Transactional, runInTransaction } from '@spfn/core/db';
import { userRepo } from '../repositories/user.repository';
import { profileRepo } from '../repositories/profile.repository';
export const middlewares = [Transactional()];
export async function POST(c: RouteContext)
{
// Single transaction (middleware) — both writes commit/rollback together
const user = await userRepo.createWithDedup(await c.req.json());
return c.json(user, 201);
}
// Or an explicit boundary outside a route:
export async function signup(data: NewUser, profile: NewProfile)
{
return runInTransaction(async () =>
{
const user = await userRepo.createWithDedup(data);
await profileRepo.create({ ...profile, userId: user.id });
return user;
});
}
Types reference
// Inferred per-table (Drizzle):
type User = typeof users.$inferSelect; // SELECT row shape
type NewUser = typeof users.$inferInsert; // INSERT shape
// BaseRepository generic:
abstract class BaseRepository<
TRelations extends AnyRelations = EmptyRelations,
TDatabase extends DrizzleDatabase = PostgresJsDatabase<TRelations>,
>
// RepositoryError fields:
class RepositoryError extends Error {
repository: string;
method?: string;
table?: string;
originalError?: Error;
}
Connection, transaction, and schema types are documented in their sub-module READMEs.
Related
- manager/README.md — connection lifecycle, pooling, reconnect
- transaction/README.md — transaction context & middleware
- schema/README.md — column helpers & schema isolation
- @spfn/core/errors — error classes returned by
fromPostgresError - Drizzle ORM —
eq/and/sql/pgTableand the query builder