@zeroxsolutions/db

A shared database library providing base entities, repository patterns, and query utilities built on top of Drizzle ORM. Supports both PostgreSQL (via postgres-js) and Cloudflare D1 (SQLite).

Features

• Base table schema with common audit columns (id, createdAt, updatedAt, deletedAt)
• Abstract BaseRepository with full CRUD, soft-delete, pagination, and relation support
• BaseViewRepository for PostgreSQL materialized views
• Type-safe filter and ordering utilities with Zod validation, plus a QueryableFields whitelist guard (assertQueryableFields) keyed to the row type, so a removed column fails typecheck at the whitelist
• The filter vocabulary the wire toolkit is generic over: filterOperatorSchema / FilterOperator (15 operators), filterValueSchema, and typed refusals (UnknownQueryField, InvalidQueryFilter, InvalidQuerySort)
• Finders read a JSON:API query as drizzle's relational config: fields and include, typed off the schema by TableQuery, resolved by buildRelationalRead
• Transaction support for all operations
• Automatic soft-delete filtering (excludes deletedAt IS NOT NULL by default)
• DrizzleUnitOfWork + transactional outboxTable / DrizzleOutbox - the @zeroxsolutions/cosmic IUnitOfWork port implementation (atomic use case + domain events staged in the same transaction)

Installation

Published to the public npm registry. Pin the version once in the consuming repo's pnpm-workspace.yaml catalog and reference catalog: from every package that needs it - two projects resolving two versions of one toolkit is the failure that avoids.

pnpm add @zeroxsolutions/db drizzle-orm zod

Package Exports

Import from the subpath that matches your driver. There is no root entry: package.json still declares ".", but no src/index.ts exists for the build to emit it from, so from '@zeroxsolutions/db' does not resolve.

| Export path | Description | | ------------------------ | ------------------------------------------ | | @zeroxsolutions/db/postgres | PostgreSQL entity, repository, view repository, unit of work, outbox, and types | | @zeroxsolutions/db/d1 | Cloudflare D1 (SQLite) entity, repository, unit of work, outbox, and types | | @zeroxsolutions/db/utils | Filter and order builders, TableQuery and the relational-read builder, the field-whitelist guard, the filter-value schema + operator enum, omit masks, pagination constants | | @zeroxsolutions/db/testing | A throwaway Postgres container carrying your schema, on the engine you name, plus the container timeout and a fake uuid v7 |

PostgreSQL

Base Entity

Take idColumn for the primary key and spread timestamps beside it:

import { pgTable } from 'drizzle-orm/pg-core';
import { idColumn, timestamps, timestampIndexes } from '@zeroxsolutions/db/postgres';

export const users = pgTable(
  'users',
  {
    id: idColumn.$type<UserId>(),
    name: text('name').notNull(),
    email: text('email').notNull().unique(),
    ...timestamps,
  },
  (table) => timestampIndexes(table, 'users'),
);

• idColumn - uuid, primary key, app-assigned uuid v7 (via $defaultFn, time-ordered for index locality). It is declared per table rather than spread, so $type can tag it with the id that table mints; a table keyed by a natural or composite key simply does not take it.
• timestamps adds createdAt (defaults to now), updatedAt (auto-updated on change) and deletedAt (nullable, used for soft deletes). All three are timestamptz, so what is stored is an instant rather than a wall-clock reading; the TypeScript type is Date either way, which is why the column type is the only thing carrying that guarantee.

timestampIndexes creates indexes on createdAt, updatedAt, and deletedAt.

BaseRepository

Extend BaseRepository to get a fully featured data access layer:

import { eq } from 'drizzle-orm';
import { BaseRepository } from '@zeroxsolutions/db/postgres';
import type { PostgresJsDatabase } from 'drizzle-orm/postgres-js';
import { users } from './schema';
import type * as schema from './schema';

export class UserRepository extends BaseRepository<typeof users, typeof schema, 'users'> {
  constructor(db: PostgresJsDatabase<typeof schema>) {
    super(db, users, 'users'); // 'users' is the queryKey for relational queries
  }

  findOneByEmail(email: string) {
    return this.findOne(eq(users.email, email));
  }
}

Available Methods

| Method | Description | | ------ | ----------- | | findAll(where, options?) | Paginated list query | | findAllAndCount(where, options?) | Paginated list + total count in parallel | | findOne(where, options?) | First matching record or null | | findOneById(id, options?) | Find by primary key or null | | create(data, options?) | Insert one record, returns inserted row | | createMany(data[], options?) | Bulk insert, returns inserted rows | | update(where, data, options?) | Update matching records, returns first updated row | | updateById(id, data, options?) | Update by primary key | | delete(where, options?) | Hard delete matching records | | deleteById(id, options?) | Hard delete by primary key | | count(where, options?) | Count matching records | | exists(where, options?) | Returns true if any record matches | | softDelete(where, options?) | Sets deletedAt to now | | softDeleteById(id, options?) | Soft delete by primary key | | nullifyBy(field, value, options?) | Set field to null where field equals value |

FindAll Options

type FindAllOptions = {
  tx?: Transaction;      // Run within a transaction
  limit?: number;        // Default: 20; a larger value is clamped to 100, not rejected
  offset?: number;       // Default: 0
  softDeleted?: boolean; // Include soft-deleted records (default: false)
  sort?: SortField[];    // [{ field: 'createdAt', direction: 'desc' }]
  fields?: { books?: readonly ('title' | 'author')[] }; // Fieldsets keyed by TABLE name: columns and relations
  include?: readonly ('author' | 'author.books')[];      // Relation paths, dot-separated (requires queryKey)
  columns?: { authorId?: boolean }; // Root columns added to the root fieldset; never narrows the row
  with?: { ... };        // Drizzle relational "with" clause, spelled out (requires queryKey)
};

fields and include are TableQuery<typeof schema, '<export name>'>, the shape @zeroxsolutions/query guards, so a guarded query spreads straight into the options:

| The query says | The finder loads | | --- | --- | | no fieldset for a table | every column of that table | | a fieldset | those columns and the table's primary key | | a relation name in a fieldset | that relation's primary keys - the linkage a relationship publishes | | an empty fieldset | the primary key alone | | an include path | each relation on it, under its own table's fieldset |

with and include reach the same relational query; where both name one relation, with wins. A read loading no relation takes a plain select of the columns it narrowed to, so a repository given no queryKey still narrows its own row. An include path is typed three relations deep; spell a deeper one out with with.

Nullify FK

Set a foreign key column to null where it matches a given value. Useful when soft-deleting a parent but keeping child rows:

// Set categoryId = null on all transactions where categoryId = '123'
await transactionRepo.nullifyBy('categoryId', '123', { tx });

Transactions

All methods accept an optional tx in their options:

await db.transaction(async (tx) => {
  const user = await userRepo.create({ name: 'Alice', email: '[email protected]' }, { tx });
  await profileRepo.create({ userId: user.id }, { tx });
});

BaseViewRepository (Materialized Views)

import { BaseViewRepository } from '@zeroxsolutions/db/postgres';
import { userSummaryView } from './views';

export class UserSummaryViewRepository extends BaseViewRepository<typeof userSummaryView> {
  constructor(db: PostgresJsDatabase) {
    super(db, userSummaryView);
  }
}

// Refresh the materialized view
await repo.refresh({ concurrently: true });

findAll takes FindAllViewOptions - tx, limit, offset and fields - and findOne the same without the page window. fields is keyed by the view's name; a fieldset loads those columns and any the view selected from a table's primary key. A view has no deletedAt and no relational config, so it takes no softDeleted, with or include, and a view read is ordered in the view's own definition.

Unit of Work & Transactional Outbox

The write-side implementation of the @zeroxsolutions/cosmic IUnitOfWork port. DrizzleUnitOfWork runs a use case inside one db.transaction, handing the work whatever makeRepositories binds to that transaction - the port is generic in that, so it can be a transaction-scoped DI container the handler resolves its one repository from, rather than an object carrying every repository the context owns; DrizzleOutbox stages an aggregate's domain events into the outboxTable in that same transaction, so the aggregate rows and the event rows commit atomically. A relay (*-queue/cron worker) later publishes unsent rows and marks them sent.

import { DrizzleUnitOfWork, DrizzleOutbox } from '@zeroxsolutions/db/postgres';

// built in the transport per invocation (the db client comes from the connection string)
const uow = new DrizzleUnitOfWork(db, (tx) => {
  const scope = container.createChildContainer();          // bind the transaction to a scoped container
  scope.register(TOKENS.TX, { useValue: tx });
  scope.register(TOKENS.OUTBOX, { useValue: new DrizzleOutbox(tx) });
  return scope;
});

// a handler: aggregate write + staged events commit in ONE transaction
await uow.run(async (tx) => {
  await tx.resolve<IClassAggregateRepository>(TOKENS.CLASS_AGGREGATE_REPOSITORY).save(klass);
  await tx.resolve<IOutboxWriter>(TOKENS.OUTBOX).stage(klass.pullEvents()); // rolls back with the write
});

outboxTable columns: id (uuid v7), type (event discriminator), payload (jsonb), occurredAt, and publishedAt (NULL until the relay sends it; indexed). The relay reads unsent rows with outbox.pullUnpublished(limit) and acknowledges them with outbox.markPublished(ids, publishedAt).

Cloudflare D1 (SQLite)

The D1 adapter mirrors the PostgreSQL API but targets Cloudflare D1.

Base Entity

import { sqliteTable, text } from 'drizzle-orm/sqlite-core';
import { idColumn, timestamps, timestampIndexes } from '@zeroxsolutions/db/d1';

export const users = sqliteTable(
  'users',
  {
    id: idColumn.$type<UserId>(),
    name: text('name').notNull(),
    email: text('email').notNull().unique(),
    ...timestamps,
  },
  (table) => timestampIndexes(table, 'users'),
);

Dates are stored as ISO 8601 strings (text columns) and auto-managed.

BaseRepository (D1)

import { BaseRepository } from '@zeroxsolutions/db/d1';
import type { DrizzleD1Database } from 'drizzle-orm/d1';
import { users } from './schema';
import type * as schema from './schema';

export class UserRepository extends BaseRepository<typeof users, typeof schema, 'users'> {
  constructor(db: DrizzleD1Database<typeof schema>) {
    super(db, users, 'users');
  }
}

The D1 BaseRepository exposes the same methods as the PostgreSQL version.

Unit of Work & Transactional Outbox (D1)

The D1 write-side implementation of the @zeroxsolutions/cosmic IUnitOfWork port. Unlike the Postgres variant - which runs work inside one interactive db.transaction closure with a consistent read snapshot - D1 has no interactive transactions, so DrizzleD1UnitOfWork is a batch-collect write unit. During run(work), an enlisted write pushes its prepared statement into a per-run collector; on work resolution the collected statements flush as ONE atomic D1 batch() (all succeed or all roll back). If work throws, nothing flushes.

Enlisting is opt-in. The D1 BaseRepository in this package executes every mutator immediately, so it does NOT join the batch. Only DrizzleOutbox (constructed with the collector) and repositories a consuming context writes against the collector do. A repository that must commit with the batch pushes its statement instead of awaiting it, which is what the { db, collector } context below is for.

import { DrizzleD1UnitOfWork, DrizzleOutbox } from '@zeroxsolutions/db/d1';

// built in the transport per invocation (the db client comes from the Hyperdrive/D1 binding)
const uow = new DrizzleD1UnitOfWork(db, ({ db, collector }) => {
  const scope = container.createChildContainer();   // reads use db; writes push to collector
  scope.register(TOKENS.TX, { useValue: { db, collector } });
  scope.register(TOKENS.OUTBOX, { useValue: new DrizzleOutbox(db, collector) });
  return scope;
});

// a handler: aggregate write + staged events flush as ONE batch() (atomic)
await uow.run(async (tx) => {
  await tx.resolve<ILinkAggregateRepository>(TOKENS.LINK_AGGREGATE_REPOSITORY).save(link);
  await tx.resolve<IOutboxWriter>(TOKENS.OUTBOX).stage(link.pullEvents());
});                                            // -> flush [save, ...stage] as ONE batch()

A consuming context's D1 aggregate repository takes both the db (for immediate load reads) and the per-run collector (for writes). Reads inside work execute immediately against current data and are NOT isolated (no transaction snapshot). Load-then-write race protection is the consumer's job - optimistic locking with a version column and UPDATE ... WHERE id = ? AND version = ? (zero rows updated = conflict, so retry). Nothing here implements it.

DrizzleD1UnitOfWork.run hands makeRepositories a { db, collector } context. The collector is an array-backed sink ({ push(statement) }) the UoW owns per run; do not capture it across runs. The D1 DrizzleOutbox mirrors the Postgres surface (stage / pullUnpublished(limit) / markPublished(ids, publishedAt)); stage pushes into the caller's collector when one is provided (so it commits with the aggregate write), or executes immediately when constructed standalone (relay use). outboxTable columns: id (uuid v7), type (event discriminator), payload (text - SQLite has no jsonb, so the relay JSON.parses), occurredAt, and publishedAt (NULL until the relay sends it; indexed).

Limitation. D1's batch() is atomic but non-interactive, so the D1 UoW gives all-or-nothing write atomicity without a transaction-scoped read snapshot. A handler that loads, decides, then writes is subject to lost-update unless the consumer adds optimistic locking.

Utilities

Import from @zeroxsolutions/db/utils.

Filter Builder

Converts typed filter descriptors into Drizzle SQL conditions:

Takes one options object, so a parsed wire query spreads straight in. Returns undefined when the tree contributes no condition, and throws UnknownQueryField when a clause names a column the table does not hold:

import { buildFilterConditions } from '@zeroxsolutions/db/utils';

const condition = buildFilterConditions({
  table: users,
  filter: [
    { field: 'name', operator: 'contains', value: 'alice' },
    { field: 'createdAt', operator: 'gte', value: '2024-01-01' },
  ],
});

Supported Operators

All 15, as filterOperatorSchema declares them:

| Operator | Description | | -------- | ----------- | | equals / notEquals | Exact match / not equal | | in / notIn | Array membership; an empty array contributes no condition | | contains / startsWith / endsWith | Case-insensitive text match: ILIKE on Postgres, LIKE on SQLite, which is already case-insensitive for ASCII | | gt / gte / lt / lte | Numeric/date comparisons | | between | Range (from / to, both optional) | | notBetween | Range exclusion (from and to, both required) | | isNull / isNotNull | Null checks; carry no value key at all |

A % or _ in a text value is escaped, so it matches literally rather than as a wildcard.

Order Builder

import { buildOrderBy } from '@zeroxsolutions/db/utils';

const clauses = buildOrderBy(users, [
  { field: 'createdAt', direction: 'desc' },
  { field: 'name', direction: 'asc' },
]);

Field Whitelist Guard

Reject a filter/sort on any field outside a listable entity's whitelist before building SQL, so a client can never filter or order by a hidden column (e.g. an access code). QueryableFields<TSelect> is keyed to the row's $inferSelect type, so a renamed/removed column fails typecheck at the whitelist. The guard is transport-agnostic - it throws a plain Error subclass (InvalidQueryFilter / InvalidQuerySort) carrying only the offending field name; the calling service maps each error type to its HTTP status/code at its own transport.

import {
  assertQueryableFields,
  InvalidQueryFilter,
  type QueryableFields,
} from '@zeroxsolutions/db/utils';

const USERS_QUERYABLE = {
  filterable: ['name', 'createdAt'],
  sortable: ['name', 'createdAt'],
} as const satisfies QueryableFields<typeof users.$inferSelect>;

// In a repository finder, before building SQL:
assertQueryableFields(query, USERS_QUERYABLE); // throws InvalidQueryFilter / InvalidQuerySort on a hidden field

Reading the query off a request

This package ships no query schema. Parse the request with @zeroxsolutions/query and hand its result straight to a finder - the member names line up (offset / limit / sort / filter / fields / include), so nothing renames anything:

import { collectionQuerySchema } from '@zeroxsolutions/query';

createRoute({ request: { query: collectionQuerySchema(usersQueryPermits) } });
const query = c.req.valid('query');
const [rows, total] = await usersRepository.findAllAndCount(buildFilterConditions({ table: users, filter: query.filter }), query);

Pass filterItem: filterValueSchema in the spec so the parsed clauses are the ones this package's builders accept. A test in utils/filter.spec.ts pins the absence of a second query shape.

Omit Constants

Three column masks, each a plain { column: true } object to hand to a schema builder's omit or to Omit<...>. This package exports the masks only - it ships no omit helper of its own.

| Mask | Drops | | ---- | ----- | | defaultSelectOmit | deletedAt | | defaultResponseOmit | the above plus createdAt, updatedAt | | defaultInsertOmit | the above plus id |

import { defaultInsertOmit } from '@zeroxsolutions/db/utils';

type InsertUser = Omit<typeof users.$inferInsert, keyof typeof defaultInsertOmit>;

Pagination Constants

import { DEFAULT_PAGE_SIZE, DEFAULT_OFFSET, MAX_PAGE_SIZE } from '@zeroxsolutions/db/utils';
// DEFAULT_PAGE_SIZE = 20
// DEFAULT_OFFSET    = 0
// MAX_PAGE_SIZE     = 100

Testing Seam

@zeroxsolutions/db/testing starts a throwaway Postgres in Docker with your own schema already created in it, so a repository test runs against the engine it deploys to rather than against a mock.

Everything it imports is an optional peer dependency, so a service that never imports this subpath installs none of it. A package whose tests do want it declares all three:

pnpm add -D @testcontainers/postgresql drizzle-kit postgres

import {
  CONTAINER_TIMEOUT_MS,
  type DisposablePostgres,
  fakeUuidV7,
  startDisposablePostgres,
} from '@zeroxsolutions/db/testing';
import * as schema from './lib/db/index.js';

// The engine THIS package deploys, written down once here. It is asked for rather than defaulted:
// only you know which major your deployment runs, and a container started on another one passes.
const POSTGRES_IMAGE = 'postgres:17-alpine';

let database: DisposablePostgres<typeof schema>;

beforeAll(async () => {
  database = await startDisposablePostgres(schema, POSTGRES_IMAGE);
}, CONTAINER_TIMEOUT_MS);

afterAll(async () => {
  await database?.stop();
});

A suite that cannot use the client - a worker e2e reaching its database through a Hyperdrive binding - takes database.connectionString and hands that to its test pool instead.

This package names no engine for you, and exports no constant standing in as that argument. Which major you run is your deployment's own fact, so image takes no default: a forgotten argument is a typecheck failure rather than a suite that quietly passed on a major you do not deploy.

| Export | What it is | | ------ | ---------- | | startDisposablePostgres(schema, image) | Starts the container, creates schema in it, and returns a Drizzle client typed against it, the connectionString it dialled, and the stop() that tears it down | | CONTAINER_TIMEOUT_MS | 180s, for the hook that starts a container: an image pull on a cold machine outruns vitest's 5s default | | fakeUuidV7 | Random bits in a v7 layout: it passes a v7 check, and is not time-ordered like a minted id |

Building

pnpm nx build @zeroxsolutions/db

Testing

pnpm nx test @zeroxsolutions/db