> ## Documentation Index
> Fetch the complete documentation index at: https://ship.paralect.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Data service

> DbService — one typed, generated data layer per table. A uniform filter API over Drizzle, soft deletes, relation loading and mutation events.

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:

```bash theme={null}
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`:

```ts theme={null}
// 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:

```ts theme={null}
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.

| Method                    | Returns                          | Notes                                 |
| ------------------------- | -------------------------------- | ------------------------------------- |
| `find(options?)`          | `Row[]`                          | `where`, `orderBy`, `limit`, `offset` |
| `findFirst(options?)`     | `Row \| undefined`               | `where`, `orderBy` — limits to one    |
| `findPage(options)`       | `{ results, count, pagesCount }` | requires `page` + `perPage`           |
| `count(options?)`         | `number`                         | `where`                               |
| `insertOne(data)`         | `Row`                            | emits an `insert` event               |
| `insertMany(data[])`      | `Row[]`                          | emits an `insert` event               |
| `updateOne(where, data)`  | `Row \| undefined`               | emits an `update` event               |
| `updateMany(where, data)` | `Row[]`                          | emits an `update` event               |
| `deleteOne(where)`        | `Row \| undefined`               | hard 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

```ts theme={null}
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()`:

```ts theme={null}
return db.users.findPage({
  where: { deletedAt: null, email: { ilike: '%@acme.com' } },
  orderBy: { createdAt: 'desc' },
  page: 1,
  perPage: 20,
});
```

### Writes

```ts theme={null}
// 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.

```ts theme={null}
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:

| Operator              | SQL                       |
| --------------------- | ------------------------- |
| `eq` / bare value     | `=`                       |
| `ne`                  | `<>`                      |
| `gt` `gte` `lt` `lte` | `>` `>=` `<` `<=`         |
| `like` `ilike`        | `LIKE` / `ILIKE`          |
| `in` `notIn`          | `IN` / `NOT IN`           |
| `isNull` `isNotNull`  | `IS 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.

<Tip>
  Need raw SQL? `where` also accepts a Drizzle `SQL` expression directly, so you can drop down for anything the operator set doesn't cover.
</Tip>

### Ordering

`orderBy` maps columns to a direction:

```ts theme={null}
orderBy: { createdAt: 'desc', fullName: 'asc' }
```

## Soft delete

Every table built on `baseColumns` (from `@/resources/base.schema`) carries a `deletedAt` timestamp:

```ts theme={null}
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.

```ts theme={null}
// "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 } });
```

<Note>
  `deleteOne` / `deleteMany` are real `DELETE` statements. Prefer the soft-delete pattern for user-facing data and keep `deletedAt: null` in your read filters.
</Note>

## 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.

```ts theme={null}
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:

```ts theme={null}
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:

```ts theme={null}
// 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](/docs/how-ship-works) 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`:

```ts theme={null}
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`](/docs/api-reference/workflow).