Drizzle

Note

You may need to initialize the env.ts first.

1. Install dependencies

pnpm add drizzle-orm pg server-only
pnpm add -D drizzle-kit @types/pg dotenv-cli

2. `.env.local`

Create a .env.local file in the root of your project and add the following:

DATABASE_URL="postgres://postgres:postgres@localhost:5432/<project-name>"

3. Add `DATABASE_URL` to `env.ts` if needed

src/env.ts

import { createEnv } from '@t3-oss/env-nextjs'
import { z } from 'zod'

export const env = createEnv({
  server: {
    DATABASE_URL: z.string().url()
  }
  // other configs
})

4. Folder Structure

src

schema

index.ts

db.ts

index.ts

docker-compose.yml

drizzle.config.ts

src/db/db.ts

import 'server-only'

import { drizzle } from 'drizzle-orm/node-postgres'

import { env } from '@/env'

import * as schema from './schema'

export const db = drizzle({ connection: env.DATABASE_URL, schema })

src/db/index.ts

export * from './db'

drizzle.config.ts

import type { Config } from 'drizzle-kit'

import { env } from './src/env'

export default {
  dialect: 'postgresql',
  schema: './src/db/schema/index.ts',
  dbCredentials: {
    url: env.DATABASE_URL
  },
  out: './src/db/migrations',
  strict: true,
  verbose: true
} satisfies Config

docker-compose.yml

version: '3'

services:
  db:
    image: postgres:16-alpine
    container_name: <project-name>_postgres
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=<project-name>
    volumes:
      - db:/var/lib/postgresql/data
    networks:
      - <project-name>-network

volumes:
  db:

networks:
  <project-name>-network:
    driver: bridge

Note

Remember to replace <project-name> with the name of your project.

5. Start Database

docker compose up -d

6. Add your schema (Example)

src/db/schema/users.ts

import { integer, pgTable, varchar } from 'drizzle-orm/pg-core'

export const usersTable = pgTable('users', {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: varchar({ length: 255 }).notNull(),
  age: integer().notNull(),
  email: varchar({ length: 255 }).notNull().unique()
})

src/db/schema/index.ts

export * from './users'

7. Add scripts to `package.json`

package.json

{
  "scripts": {
    "db:check": "pnpm with-env drizzle-kit check",
    "db:generate": "pnpm with-env drizzle-kit generate",
    "db:migrate": "pnpm with-env drizzle-kit migrate",
    "db:push": "pnpm with-env drizzle-kit push",
    "db:studio": "pnpm with-env drizzle-kit studio",
    "with-env": "dotenv -e .env.local --"
  }
}

8. Apply Migrations

pnpm db:generate
pnpm db:migrate

Preflight for an existing production database

drizzle-kit migrate applies every journal entry with a timestamp newer than the last applied migration. Before running it against a database that already holds schema, check:

Journal order: timestamps must ascend with idx. A stale when on an old entry makes the migrator skip it forever and start at a later migration whose statements reference objects that do not exist.
Push-synced schema: if the database was ever updated with drizzle-kit push, later migrations may fail with column already exists or reference constraints that were created under different names.
Missing tables the code references: grep the schema exports and compare against information_schema.tables.
Duplicate rows: any new UNIQUE constraint fails to add when duplicates exist for its columns. Check with GROUP BY ... HAVING count(*) > 1 and dedupe first.
Orphan rows: re-created foreign keys with ON DELETE CASCADE fail when child rows reference missing parents.

Inspect the divergence with catalog queries (pg_constraint, pg_indexes, information_schema.columns) rather than assuming the journal reflects the database. Apply repairs in a transaction with pre-checks, then register applied migrations in the migrations table with the same sha256 hash the migrator uses so future runs stay no-ops.

After any schema change, pnpm db:check must pass and a no-op pnpm db:migrate must succeed.

Drizzle

Tree View