# Database and Services

> Add a Postgres table, derive a validator, write scoped Drizzle queries, and apply generated migrations.

*Canonical: https://makerkit.dev/docs/void-cloudflare/development-guide/database-and-services*

---

The app uses Drizzle through `void/db`, with PGlite locally and Postgres through Hyperdrive in production. Your tables belong in `apps/web/db/schema.ts`. Auth tables are generated into `auth-schema.ts`; kit tables live in `kit-schema.ts`.

## Add a Small User-owned Table

This example stores one preferences row per user. Add it to `apps/web/db/schema.ts`, keeping the existing exports:

```ts
import { boolean, pgTable, timestamp, uuid } from 'void/schema-pg';
import { user } from './auth-schema';

export const userPreferences = pgTable('user_preferences', {
  userId: uuid('user_id')
    .primaryKey()
    .references(() => user.id, { onDelete: 'cascade' }),
  compactView: boolean('compact_view').notNull().default(false),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .notNull()
    .defaultNow(),
});
```

The primary key limits this to one row per user and indexes the foreign key. Cascade deletion is appropriate for that bounded relationship. A table that can grow without bound, such as activity events, uses an indexed owner id and a batched purge target instead; see [Data Retention](../cleanup/overview).

## Generate and Apply a Migration

From the repository root:

```bash
pnpm --filter web exec void db generate
```

Review the generated SQL under `apps/web/db/migrations/`. Then apply migrations to the configured **local development database**:

```bash
pnpm --filter web exec void db migrate
```

Commit the schema, reviewed migration files, and any generated `void.lock.json` changes together. Production schema changes travel through migrations and the [deployment workflow](../deployment/production). Use expand-then-contract changes for existing data; do not use `db push` against a live database.

To change Better Auth tables, change its configuration and use its schema-generation task rather than editing the generated auth schema by hand.

## Derive a Writable Validator

Create `apps/web/lib/preferences.schema.ts`:

```ts
import { createInsertSchema } from 'void/drizzle-zod';
import { userPreferences } from '#/db/schema.ts';

export const preferencesBody = createInsertSchema(userPreferences)
  .pick({ compactView: true })
  .required();
```

The request does not accept `userId` or `updatedAt`. Those are server-owned fields. The validator is for server use; do not import the table or this database-dependent module into a browser component. `useForm` can infer input types through the page action.

## Write Named Database Functions

Create `apps/web/lib/preferences.service.ts`:

```ts
import { db, eq } from 'void/db';
import { userPreferences } from '#/db/schema.ts';

export async function findPreferences({ userId }: { userId: string }) {
  const [preferences] = await db
    .select({ compactView: userPreferences.compactView })
    .from(userPreferences)
    .where(eq(userPreferences.userId, userId))
    .limit(1);

  return preferences ?? { compactView: false };
}

export async function savePreferences({
  userId,
  compactView,
}: {
  userId: string;
  compactView: boolean;
}) {
  await db
    .insert(userPreferences)
    .values({ userId, compactView })
    .onConflictDoUpdate({
      target: userPreferences.userId,
      set: { compactView, updatedAt: new Date() },
    });
}
```

An authorized handler calls these functions with `user.id` from `requireSession({ c })`. Keep validation and authorization at the handler boundary and business rules in services. For an id-addressed record, updates and deletes need both the record id and its owner predicate in the database query.

For multiple writes that must succeed together, use a transaction. Work that must happen after commit, such as background processing, belongs in the transactional [outbox](../outbox/jobs); an unrelated queue send after a database write can lose work.

## Reusable Packages

Generate a package with `pnpm kit:generate package preferences` when the feature needs a reusable module. Package code receives `db`, schema tables, auth, and other dependencies through parameters; it does not import `void/*`. Keep browser-safe exports in `src/index.ts` and server work behind `src/server.ts`. The app binds dependencies under `apps/web/config/`.

Test ownership and database constraints against the kit's PGlite test database. Avoid tests that only assert that a query builder method was called; a different user's read or write must actually be refused.
