Add a database table

Files you will touch, in this order:

packages/shared/src/db/schema/<table>.ts, one file per table, flat
packages/shared/src/db/schema/index.ts, the barrel and the relations
packages/shared/drizzle/<timestamp>_<name>/, written for you by pnpm db:generate
the route that reads it

Write the table

// packages/shared/src/db/schema/projects.ts

import { index, pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
import { users } from './users'

export const projects = pgTable(
  'projects',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (table) => [index('projects_user_id_idx').on(table.userId)],
)

export type Project = typeof projects.$inferSelect
export type NewProject = typeof projects.$inferInsert

Column names are written out in snake_case rather than left to a casing option, which would have to be configured identically in drizzle.config.ts and at every drizzle() call site to agree.

Timestamps carry withTimezone: true. $onUpdate keeps updated_at honest without a trigger.

Export the two inferred types. Nothing in this repo hand-declares a row shape beside the schema that produces it.

Warning

onDelete: 'cascade' on a user reference means a Clerk account deletion takes these rows with it. That suits data that only means something to the account. Records you must keep after the account is gone, such as anything financial, use onDelete: 'set null' instead, as purchases and subscriptions do. Decide which one you want before the first row exists.

Register it

// packages/shared/src/db/schema/index.ts

export * from './projects'

export const schema = { users, purchases, subscriptions, projects }

export const relations = defineRelations(schema, (r) => ({
  users: {
    purchases: r.many.purchases(),
    subscriptions: r.many.subscriptions(),
    projects: r.many.projects(),
  },
  projects: {
    user: r.one.users({ from: r.projects.userId, to: r.users.id }),
  },
  // ...
}))

Relations live only in this file, so ten people adding tables at once never touch each other's table file.

drizzle.config.ts points at this barrel rather than at a glob over the folder, because the barrel re-exports every table and a glob would hand drizzle-kit each one twice.

Generate the migration

pnpm db:generate

It diffs your TypeScript against the last snapshot.json and writes a new folder under packages/shared/drizzle/ containing a migration.sql and the next snapshot. Read the SQL before you commit it: drizzle-kit cannot tell a rename from a drop plus an add, and it will ask you when it is unsure.

Careful

Never hand-edit an existing migration. A migration is frozen history: it describes what actually ran, on databases you no longer control. Change the schema and generate the next one.

Migrations are .sql rather than TypeScript for the same reason. A TypeScript migration would import the schema and re-evaluate against today's definitions, so 0001 would stop describing what it did.

Apply it

pnpm db:migrate

Development by default. Production is NODE_ENV=production pnpm db:migrate, typed on purpose, and it is a deploy step rather than something that happens at boot.

Query it

import { projects } from '@ffd/shared/db'
import { desc, eq } from 'drizzle-orm'

const rows = await db
  .select()
  .from(projects)
  .where(eq(projects.userId, res.locals.user.id))
  .orderBy(desc(projects.createdAt))

Careful

Every query, view, filter and index predicate goes through Drizzle's builders. A sql template is a string: it does not know a column was renamed, so it compiles clean and returns nothing. The builder makes that a type error, which is the entire reason this repo has an ORM.

The escape hatch is one expression and never a query, for what the builder genuinely cannot express: a constant column, or DDL like CREATE DATABASE that no ORM models. When you reach for it, sql<T> is a cast, so attach the real decoder with .mapWith(someColumn) rather than asserting the type, or you get a raw Postgres string typed as a Date.

apps/web cannot import @ffd/shared/db. That specifier is a lint error there, so reaching a new table from the browser means a route, which is Add an api route.

If the table needs its own claim

If concurrent handlers can start the same side effect, use a nullable claim column and claim it in one statement. This prevents concurrent attempts; delivery across a vendor and the database still needs idempotency and crash recovery:

const claimed = await db
  .update(projects)
  .set({ provisionClaimedAt: new Date() })
  .where(isNull(projects.provisionClaimedAt))
  .returning({ id: projects.id })

Rows that come back are yours. Add a partial index over the claim predicate, and write it with isNull() rather than a SQL string, so a rename is a type error rather than an index that silently stops matching:

index('projects_unclaimed_idx')
  .on(table.id)
  .where(isNull(table.provisionClaimedAt))

The decision and its failure modes are in docs/adr/0007-atomic-claim-for-side-effects.md. Mail uses the same idea through email_deliveries, which Add an email shows.

Verify

pnpm db:generate   # should report no changes the second time
pnpm typecheck
pnpm check

pnpm typecheck covers drizzle.config.ts too, which is not hygiene: drizzle-kit silently accepts an unknown key, so a typo'd schemaFilter disables it with no error anywhere, and tsc is the only thing that catches it.

Database schema, the three tables that ship
docs/adr/0009-drizzle-v1-on-neon-postgres.md
docs/adr/0007-atomic-claim-for-side-effects.md