Drizzle + Postgres + Docker

One connection string rules everything: the app reads DATABASE_URL and nothing else. Locally it points at a Dockerized Postgres; in production the platform env var overrides it with the managed database. No if (dev) branching in db code, ever.

Local database — Docker Compose

# docker-compose.yml
services:
  db:
    image: postgres:17-alpine
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: app
      POSTGRES_PASSWORD: app
      POSTGRES_DB: app
    volumes:
      - db-data:/var/lib/postgresql/data
volumes:
  db-data:

# .env.local (gitignored)
DATABASE_URL=postgres://app:app@localhost:5432/app

docker compose up -d starts it; the named volume keeps data across restarts. In production, do not ship this compose file — set DATABASE_URL in the host's environment (Vercel, Railway, Fly, …) and the same code connects to the managed Postgres.

Drizzle setup

npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./lib/db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
});

// lib/db/index.ts — the only place the driver is imported
import { drizzle } from "drizzle-orm/node-postgres";
import * as schema from "./schema";

export const db = drizzle(process.env.DATABASE_URL!, { schema });

Schema & migrations

Define tables in lib/db/schema.ts; scope user-owned rows by the auth provider's user id (see clerk-nextjs-auth):

import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

export const projects = pgTable("projects", {
  id: serial("id").primaryKey(),
  userId: text("user_id").notNull(),   // Clerk userId — always filter by it
  name: text("name").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Workflow — generate SQL migrations and commit them:

npx drizzle-kit generate   # diff schema → SQL file in ./drizzle
npx drizzle-kit migrate    # apply pending migrations to DATABASE_URL
npx drizzle-kit studio     # browse the db while developing

Use generate + migrate (not push) once anything real depends on the database — migrations are code-reviewed history, and the same migrate command runs against production by virtue of DATABASE_URL.

Query conventions

• Query from Server Components and server actions only — never from client code.
• Every query on a user-owned table filters by userId from the session; ownership checks live in the query, not the UI.
• Keep queries near their feature (lib/db/queries/projects.ts) and return plain serializable objects to components.