Skip to main content
Ship’s data layer is a single, typed service per table. You never hand-write a query builder or a repository — codegen-db.ts reads your schema files and generates a DbService for every pgTable, exported from @/db. One obvious way to read and write, the same on every table.
Standardised patterns. Zero guesswork.

​
Where it comes from

DbService lives in the @ship/db package; the generated wiring lives in apps/api/src/db.ts. Add or change a *.schema.ts file, run codegen, and @/db gains a fully typed service for that table: 
cd apps/api && npx tsx scripts/codegen-db.ts   # or: pnpm --filter api codegen
The generated @/db looks like this — one DbService per table, plus transaction: 
// apps/api/src/db.ts — auto-generated, do not edit
const createDB = (db: typeof rawDb) => ({
  accounts: new DbService<typeof accounts>(accounts, db, 'accounts'),
  sessions: new DbService<typeof sessions>(sessions, db, 'sessions'),
  users: new DbService<typeof users>(users, db, 'users', eventBus.hook('users')),
  // ...one per pgTable
  transaction: getTransactionalDB,
});
You import the default export and call methods off the table name: 
import db from '@/db';

const user = await db.users.findFirst({ where: { id, deletedAt: null } });

​
Methods

Every db.<table> exposes the same surface. Reads take a uniform options object; writes return the affected rows.
MethodReturnsNotes
find(options?)Row[]where, orderBy, limit, offset
findFirst(options?)Row | undefinedwhere, orderBy — limits to one
findPage(options){ results, count, pagesCount }requires page + perPage
count(options?)numberwhere
insertOne(data)Rowemits an insert event
insertMany(data[])Row[]emits an insert event
updateOne(where, data)Row | undefinedemits an update event
updateMany(where, data)Row[]emits an update event
deleteOne(where)Row | undefinedhard delete; emits a delete event
deleteMany(where)Row[]hard delete; emits a delete event
Inputs and outputs are inferred from the Drizzle table, so data, where columns and returned rows are all typed against the real schema.

​
Reads

import db from '@/db';

// One row
const user = await db.users.findFirst({ where: { id, deletedAt: null } });

// Many rows
const admins = await db.users.find({
  where: { isAdmin: true, deletedAt: null },
  orderBy: { createdAt: 'desc' },
});

// Count
const total = await db.users.count({ where: { deletedAt: null } });

​
Pagination

findPage runs the page query and a matching count in parallel and returns { results, count, pagesCount } — the exact shape of listResultSchema(itemSchema) from @/resources/base.schema, so it drops straight into an endpoint’s .output(): 
return db.users.findPage({
  where: { deletedAt: null, email: { ilike: '%@acme.com' } },
  orderBy: { createdAt: 'desc' },
  page: 1,
  perPage: 20,
});

​
Writes

// Insert
const user = await db.users.insertOne({ email, fullName });
await db.users.insertMany([{ email, fullName }, /* ... */]);

// Update — filter, then patch
const updated = await db.users.updateOne({ id }, { lastRequestAt: new Date() });
await db.users.updateMany({ isEmailVerified: false }, { isAdmin: false });
Every mutation returns the affected row(s) via RETURNING, so you rarely need a follow-up read.

​
The filter API

where is a plain object. A bare value means equality; an object of operators expresses everything else. OR and AND take arrays of nested filters. 
where: {
  deletedAt: null,                          // IS NULL
  isAdmin: true,                            // equality
  createdAt: { gte: startDate, lt: endDate }, // range
  email: { ilike: '%@acme.com' },           // case-insensitive LIKE
  OR: [
    { fullName: { ilike: `%${q}%` } },
    { email: { ilike: `%${q}%` } },
  ],
}
Supported operators:
OperatorSQL
eq / bare value=
ne<>
gt gte lt lte> >= < <=
like ilikeLIKE / ILIKE
in notInIN / NOT IN
isNull isNotNullIS NULL / IS NOT NULL
A bare null is shorthand for isNull, which is why soft-delete reads spell it deletedAt: null. OR and AND nest arbitrarily.
Need raw SQL? where also accepts a Drizzle SQL expression directly, so you can drop down for anything the operator set doesn’t cover.

​
Ordering

orderBy maps columns to a direction: 
orderBy: { createdAt: 'desc', fullName: 'asc' }

​
Soft delete

Every table built on baseColumns (from @/resources/base.schema) carries a deletedAt timestamp: 
export const baseColumns = {
  id: uuid('id').defaultRandom().primaryKey(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).$onUpdate(() => new Date()),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
};
There’s no magic auto-exclusion: a soft delete is an updateOne that sets deletedAt, and live reads filter deletedAt: null explicitly. 
// "Delete" — keep the row, stamp deletedAt
await db.users.updateOne({ id }, { deletedAt: new Date() });

// Live reads exclude soft-deleted rows
await db.users.find({ where: { deletedAt: null } });
deleteOne / deleteMany are real DELETE statements. Prefer the soft-delete pattern for user-facing data and keep deletedAt: null in your read filters.

​
Relation loading

Pass with or columns to findFirst / find / findPage and the call transparently delegates to Drizzle’s relational query builder, keeping precise inference of the joined result. 
const user = await db.users.findFirst({
  where: { id, deletedAt: null },
  columns: { id: true, email: true, fullName: true }, // shape the row
  with: { accounts: true },                            // load relations
});
Relation loading requires an apps/api/src/relations.ts. When it exists, codegen-db.ts emits the relations-aware generic — DbService<typeof table, typeof rawDb.query.table> — so with/columns results are fully typed. Without it, the generated service is DbService<typeof table> and plain reads still work.

​
Mutations emit events

Any resource with a handlers/ directory is auto-wired so its DbService publishes a typed MutationEvent after each write commits: 
export interface MutationEvent<T> {
  type: 'insert' | 'update' | 'delete';
  docs: Row[];        // the affected rows
  prevDocs?: Row[];   // previous state, on update/delete
}
Subscribe in <resource>/handlers/*.ts to run side effects without coupling them to the endpoint that caused the change: 
// resources/users/handlers/sync-analytics.ts
import { eventBus } from '@/event-bus';

eventBus.on('users.insert', (data) => {
  for (const user of data.docs) {
    analyticsService.track('New user created', { fullName: user.fullName });
  }
});
Event keys are <table>.{insert,update,delete}. See the event handlers model for how this keeps writes decoupled from their side effects.

​
In an endpoint

The data service is the body of most handlers. Compose gates with .use(), declare the contract with .input()/.output(), then call db: 
import { z } from 'zod';

import db from '@/db';
import endpoint from '@/endpoint';
import isAdmin from '@/middlewares/is-admin';
import { listResultSchema, paginationSchema } from '@/resources/base.schema';
import { publicSchema } from '../users.schema';

export default endpoint
  .use(isAdmin)
  .input(paginationSchema)
  .output(listResultSchema(publicSchema))
  .handler(async ({ input }) => {
    return db.users.findPage({ where: { deletedAt: null }, ...input });
  });
For writes that span more than one table, wrap them in db.transaction.