Migrations

Initial setup

The schema is already defined in lib/db/schema.ts. To create the tables in your database:

1. Generate migration files from the schema:

npm run db:generate

2. Push the schema to your database:

npm run db:push

Database URL

Use a transaction pooler connection string for DATABASE_URL. Most hosted PostgreSQL providers (Supabase, Neon) offer both a direct connection and a pooler URL — use the pooler. The database client is configured with prepare: false for compatibility with transaction pool mode.

Generating migrations

When you modify lib/db/schema.ts, generate a new migration:

npm run db:generate

This runs drizzle-kit generate, which compares your schema against the database and creates a new SQL migration file in drizzle/.

Note: drizzle-kit generate is interactive — it may prompt you about destructive changes. For more control, you can write SQL migration files manually in the drizzle/ directory.

Pushing schema directly

For rapid development, you can push schema changes directly without generating migration files:

npm run db:push

This applies schema changes directly to the database. Useful during development but not recommended for production.

Drizzle Studio

Inspect and edit your data with Drizzle Studio:

npm run db:studio

Opens a web-based GUI at https://local.drizzle.studio where you can browse tables, run queries, and edit rows.

Adding a new table

1. Define the table in lib/db/schema.ts:

export const posts = pgTable("post", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text("title").notNull(),
  userId: text("userId").references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("createdAt", { mode: "date" }).defaultNow().notNull(),
});

2. Generate and push:

npm run db:generate
npm run db:push