drizzle-orm

name	drizzle-orm
category	Backend
description	MUST USE when writing or reviewing Drizzle ORM schemas, migrations, relational queries, or drizzle-kit configuration. Enforces identity columns over serial, proper relation definitions, migration safety, type inference, and query patterns.

Drizzle ORM Best Practices

Schema — Use Identity Columns, Not Serial

// BAD: serial is legacy PostgreSQL
import { pgTable, serial, text } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

// GOOD: identity columns are the modern PostgreSQL standard
import { pgTable, integer, text } from "drizzle-orm/pg-core";
export const users = pgTable("users", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  name: text("name"),
});

Schema — Column Naming

Map camelCase TypeScript to snake_case SQL explicitly.

// BAD: implicit column name matches TS key — inconsistent SQL
export const users = pgTable("users", {
  firstName: varchar({ length: 256 }),
});

// GOOD: explicit snake_case SQL column name
export const users = pgTable("users", {
  firstName: varchar("first_name", { length: 256 }),
});

Schema — Indexes and Constraints

Define indexes in the third argument array:

export const posts = pgTable(
  "posts",
  {
    id: integer().primaryKey().generatedAlwaysAsIdentity(),
    slug: varchar({ length: 256 }),
    title: varchar({ length: 256 }),
    ownerId: integer("owner_id").references(() => users.id),
  },
  (table) => [
    uniqueIndex("posts_slug_idx").on(table.slug),
    index("posts_title_idx").on(table.title),
  ]
);

Schema — Enums, Timestamps, Foreign Keys

// Define enums OUTSIDE the table
export const roleEnum = pgEnum("role", ["guest", "user", "admin"]);

export const posts = pgTable("posts", {
  id: integer().primaryKey().generatedAlwaysAsIdentity(),
  title: text("title").notNull(),
  role: roleEnum().default("guest"),
  userId: integer("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at")
    .notNull()
    .$onUpdate(() => new Date()),
});

Schema — Type Inference

// BAD: manually typing insert/select types
interface User { id: number; name: string; email: string; }

// GOOD: infer from schema — always in sync
export type InsertUser = typeof users.$inferInsert;
export type SelectUser = typeof users.$inferSelect;

Relations — One-to-Many

import { relations } from "drizzle-orm";

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
}));

Relations — Many-to-Many

export const usersToGroups = pgTable("users_to_groups", {
  userId: integer("user_id")
    .notNull()
    .references(() => users.id),
  groupId: integer("group_id")
    .notNull()
    .references(() => groups.id),
}, (t) => [
  primaryKey({ columns: [t.userId, t.groupId] }),
]);

export const usersRelations = relations(users, ({ many }) => ({
  groups: many(usersToGroups),
}));

export const groupsRelations = relations(groups, ({ many }) => ({
  members: many(usersToGroups),
}));

Relational Queries — with and Filters

import * as schema from "./schema";
const db = drizzle(pool, { schema }); // pass schema to enable relational queries

// BAD: manual joins for simple relation fetching
const result = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId));

// GOOD: relational query API — automatic joins, nested types
const result = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

// Filter and limit nested relations
const result = await db.query.users.findMany({
  with: {
    posts: {
      where: (posts, { eq }) => eq(posts.published, true),
      limit: 5,
      orderBy: (posts, { desc }) => [desc(posts.createdAt)],
    },
  },
});

Relations v2 — Landing in Drizzle v1.0

The relations API above is the stable 0.45.x syntax — keep using it until v1.0 ships. Drizzle v1.0 (currently drizzle-orm@rc) replaces per-table relations() with a single defineRelations, and you pass { relations } to drizzle() instead of { schema }. Know the shape so you recognize and can migrate v2 code:

import { defineRelations } from "drizzle-orm";
import * as schema from "./schema";

// v1 keys → v2 keys:  fields → from,  references → to,  relationName → alias
export const relations = defineRelations(schema, (r) => ({
  users: { posts: r.many.posts({ from: r.users.id, to: r.posts.authorId }) },
  posts: { author: r.one.users({ from: r.posts.authorId, to: r.users.id }) },
}));

// many-to-many is native in v2 — no relation defined for the junction table:
//   groups: r.many.groups({
//     from: r.users.id.through(r.usersToGroups.userId),
//     to:   r.groups.id.through(r.usersToGroups.groupId),
//   })

const db = drizzle(client, { relations }); // not { schema }

v2 queries also take object-style where / orderBy and can filter parent rows by a related table's columns (v1 can only filter children):

await db.query.users.findMany({ where: { id: 1 }, with: { posts: true } });

During the @rc migration window, v2 lives on db.query while your old v1-style callback queries keep working on db._query.

Queries — Select Only What You Need

// BAD: fetches all columns
const allUsers = await db.select().from(users);

// GOOD: partial select — less data over the wire
const names = await db
  .select({ id: users.id, name: users.name })
  .from(users);

Queries — Insert, Update, Delete

// Insert with returning
const [newUser] = await db
  .insert(users)
  .values({ name: "Alice", email: "[e-mail]" })
  .returning();

// Upsert (PostgreSQL)
await db
  .insert(users)
  .values({ email: "[e-mail]", name: "Alice" })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: "Alice Updated" },
  });

// Update
await db.update(users).set({ name: "Bob" }).where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));

Transactions

// BAD: separate queries — no atomicity
await db.insert(orders).values(order);
await db.update(inventory).set({ stock: sql`stock - 1` }).where(eq(inventory.id, itemId));

// GOOD: wrap in transaction
await db.transaction(async (tx) => {
  await tx.insert(orders).values(order);
  await tx
    .update(inventory)
    .set({ stock: sql`stock - 1` })
    .where(eq(inventory.id, itemId));
});

Migrations — Config and Workflow

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

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/db/schema.ts",
  out: "./drizzle",
  strict: true, // prompts on ambiguous changes like renames
  dbCredentials: { url: process.env.DATABASE_URL! },
});

# 1. Generate migration from schema diff
drizzle-kit generate --name=add_posts_table

# 2. Review the generated SQL in ./drizzle/ before applying

# 3a. Apply migrations (production — uses migration journal)
drizzle-kit migrate

# 3b. Push directly (dev only — no migration files)
drizzle-kit push

# Pull schema from existing database
drizzle-kit pull

# Custom/seed migration (empty SQL file you write yourself)
drizzle-kit generate --name=seed_users --custom

Migrations — Programmatic Apply

import { migrate } from "drizzle-orm/node-postgres/migrator";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

await migrate(db, { migrationsFolder: "./drizzle" });
await pool.end();

Migrations — Rename Columns Safely

Drizzle Kit may interpret renames as drop + add = data loss. With strict: true it will prompt you. Write a custom migration instead:

-- drizzle/XXXX_rename_name_to_full_name/migration.sql
ALTER TABLE "users" RENAME COLUMN "name" TO "full_name";

Migrations — Add Non-Nullable Column Safely

Generate the column addition, then use --custom for backfill + constraint:

ALTER TABLE "users" ADD COLUMN "role" VARCHAR(20);             -- 1. nullable
UPDATE "users" SET "role" = 'member' WHERE "role" IS NULL;     -- 2. backfill
ALTER TABLE "users" ALTER COLUMN "role" SET NOT NULL;           -- 3. constrain

Rules

1. Use identity columns (generatedAlwaysAsIdentity()) over serial for PostgreSQL
2. Explicit snake_case column names — always pass the SQL name string
3. Infer types from schema — use $inferInsert / $inferSelect, never manual interfaces
4. Define relations separately — relations() calls live alongside table definitions
5. Use relational query API (db.query.X.findMany({ with })) for nested data
6. Select only needed columns — avoid bare select() in production queries
7. Wrap multi-table writes in transactions — db.transaction()
8. Always review generated SQL before running drizzle-kit migrate
9. Enable strict: true in drizzle config — catches ambiguous renames
10. Never use push in production — always use migration files via generate + migrate
11. Three-step non-nullable columns — add nullable, backfill, set NOT NULL
12. Commit migration files to version control — they are your database changelog
13. Run drizzle-kit generate in CI to detect schema drift