Drizzle ORM in Practice: Schema, Migrations, and the Patterns That Stick

Drizzle ORM hits a sweet spot between raw SQL control and the type safety of an ORM. After using it in production for NodWatch, here are the patterns that stuck.

Schema Definition

Drizzle schemas are TypeScript. The table definition is the source of truth for both the database and your types.

// db/schema.ts
import { pgTable, text, uuid, integer, boolean, timestamp, jsonb, unique } from "drizzle-orm/pg-core";
 
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  role: text("role").notNull().default("user"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});
 
export const watchProgress = pgTable("watch_progress", {
  id: uuid("id").primaryKey().defaultRandom(),
  userId: text("user_id").notNull().references(() => users.id, { onDelete: "cascade" }),
  tmdbId: integer("tmdb_id").notNull(),
  mediaType: text("media_type").notNull(),
  season: integer("season").default(0),
  episode: integer("episode").default(0),
  progressSeconds: integer("progress_seconds").notNull().default(0),
  durationSeconds: integer("duration_seconds").notNull().default(0),
  completed: boolean("completed").notNull().default(false),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => ({
  uniqueProgress: unique().on(t.userId, t.tmdbId, t.mediaType, t.season, t.episode),
}));

The (t) => ({}) callback gives you access to columns to define composite indexes and unique constraints.

Type Inference

Drizzle infers TypeScript types from your schema. You rarely write interfaces manually:

import { type InferSelectModel, type InferInsertModel } from "drizzle-orm";
 
type User = InferSelectModel<typeof users>;
type NewUser = InferInsertModel<typeof users>;
// NewUser omits fields with defaults (id, createdAt, role)

drizzle-kit Migrations

Configure drizzle.config.ts:

import type { Config } from "drizzle-kit";
 
export default {
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
} satisfies Config;

Commands:

bash

npx drizzle-kit generate   # generate SQL migration files from schema diff
npx drizzle-kit migrate    # apply pending migrations
npx drizzle-kit push       # directly push schema (skip migration files — dev only)
npx drizzle-kit studio     # local GUI for your DB

Use push in development for fast iteration. Use generate + migrate for production — keep a history of migrations in version control.

Queries: Select and Relations

Basic select with filtering:

const posts = await db
  .select()
  .from(watchProgress)
  .where(and(eq(watchProgress.userId, userId), eq(watchProgress.completed, false)))
  .orderBy(desc(watchProgress.updatedAt))
  .limit(20);

With relations (define them separately):

// db/relations.ts
import { relations } from "drizzle-orm";
 
export const usersRelations = relations(users, ({ many }) => ({
  watchProgress: many(watchProgress),
}));
 
// Query with join
const result = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: { watchProgress: { limit: 10, orderBy: desc(watchProgress.updatedAt) } },
});

Upserts

The onConflictDoUpdate handles upserts cleanly:

await db
  .insert(watchProgress)
  .values({ userId, tmdbId, mediaType, season, episode, progressSeconds, durationSeconds })
  .onConflictDoUpdate({
    target: [watchProgress.userId, watchProgress.tmdbId, watchProgress.mediaType, watchProgress.season, watchProgress.episode],
    set: {
      progressSeconds: sql`excluded.progress_seconds`,
      durationSeconds: sql`excluded.duration_seconds`,
      completed: sql`excluded.completed`,
      updatedAt: new Date(),
    },
  });

Transactions

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values(newUser).returning();
  await tx.insert(userPreferences).values({ userId: user.id });
  // If either throws, both roll back
});

Neon Serverless Adapter

Neon's serverless driver uses HTTP instead of TCP — essential for edge environments where TCP connections don't persist:

import { neon } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-http";
import * as schema from "./schema";
 
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

For local development with drizzle-kit studio or migrations, use drizzle-orm/node-postgres with the standard pg package instead — Neon's HTTP driver doesn't support drizzle-kit commands directly.

Drizzle's killer feature is that the type system and the schema are the same object. You refactor the schema, TypeScript errors show you every query that needs updating. No code generation step, no ORM magic — just typed SQL.