Search docs

Type at least 3 characters to search

All pages

Add a database table

Files you will touch, in this order:

  1. packages/shared/src/db/schema/<table>.ts, one file per table, flat
  2. packages/shared/src/db/schema/index.ts, the barrel and the relations
  3. packages/shared/drizzle/<timestamp>_<name>/, written for you by pnpm db:generate
  4. 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 is already true of purchases and subscriptions, and it is the reason "delete user" is a deferred capability on the admin surface: the delete that looks like it removes an account also destroys the payment records a refund would have to be reasoned from. 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 full pattern, including what to do when the side effect fails, is Fulfillment.

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

Updated