Drizzle ORM Patterns

Purpose

Provide patterns for database operations with Drizzle ORM, including schema definition, relations, type inference, CRUD queries, pagination, transactions, migrations, and testing strategies for PostgreSQL databases.

Schema Definition

Tables with Indexes and Constraints

import {
  pgTable, uuid, varchar, text, timestamp, integer,
  index, check, primaryKey,
} from "drizzle-orm/pg-core";
import { relations, sql } from "drizzle-orm";

export const users = pgTable("users", {
  id: uuid("id").defaultRandom().primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 255 }),
  role: varchar("role", { length: 20 }).notNull().default("user"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
  deletedAt: timestamp("deleted_at"),
}, (table) => ({
  emailIdx: index("idx_users_email").on(table.email),
}));

export const items = pgTable("items", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description"),
  status: varchar("status", { length: 20 }).notNull().default("active"),
  price: integer("price").notNull(),
  authorId: uuid("author_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
  deletedAt: timestamp("deleted_at"),
}, (table) => ({
  authorIdx: index("idx_items_author").on(table.authorId),
  statusIdx: index("idx_items_status").on(table.status),
  priceCheck: check("check_price_positive", sql`${table.price} > 0`),
}));

// Many-to-many junction table
export const tags = pgTable("tags", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name", { length: 50 }).notNull().unique(),
});

export const itemTags = pgTable("item_tags", {
  itemId: uuid("item_id").notNull().references(() => items.id, { onDelete: "cascade" }),
  tagId: uuid("tag_id").notNull().references(() => tags.id, { onDelete: "cascade" }),
}, (table) => ({
  pk: primaryKey({ columns: [table.itemId, table.tagId] }),
}));

Relations

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

export const itemsRelations = relations(items, ({ one, many }) => ({
  author: one(users, {
    fields: [items.authorId],
    references: [users.id],
  }),
  tags: many(itemTags),
}));

export const itemTagsRelations = relations(itemTags, ({ one }) => ({
  item: one(items, { fields: [itemTags.itemId], references: [items.id] }),
  tag: one(tags, { fields: [itemTags.tagId], references: [tags.id] }),
}));

Type Inference

export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Item = typeof items.$inferSelect;
export type NewItem = typeof items.$inferInsert;

Query Patterns

Basic CRUD

import { eq, and, isNull, desc, sql } from "drizzle-orm";

// Create
const [newItem] = await db
  .insert(items)
  .values({ title: "New Item", price: 100, authorId: userId })
  .returning();

// Read one with relations
const item = await db.query.items.findFirst({
  where: eq(items.id, itemId),
  with: { author: true, tags: { with: { tag: true } } },
});

// Read many with filters
const activeItems = await db.query.items.findMany({
  where: and(eq(items.status, "active"), isNull(items.deletedAt)),
  orderBy: [desc(items.createdAt)],
  limit: 10,
});

// Update
const [updated] = await db
  .update(items)
  .set({ title: "Updated Title", updatedAt: new Date() })
  .where(eq(items.id, itemId))
  .returning();

// Soft delete
await db
  .update(items)
  .set({ deletedAt: new Date() })
  .where(eq(items.id, itemId));

Pagination

async function findPaginated(input: {
  page: number;
  pageSize: number;
  status?: string;
}) {
  const { page, pageSize, status } = input;
  const offset = (page - 1) * pageSize;

  const conditions = [isNull(items.deletedAt)];
  if (status) conditions.push(eq(items.status, status));

  const [results, [{ count }]] = await Promise.all([
    db.query.items.findMany({
      where: and(...conditions),
      limit: pageSize,
      offset,
      orderBy: [desc(items.createdAt)],
      with: { author: true },
    }),
    db
      .select({ count: sql<number>`count(*)` })
      .from(items)
      .where(and(...conditions)),
  ]);

  return {
    data: results,
    pagination: {
      total: Number(count),
      page,
      pageSize,
      totalPages: Math.ceil(Number(count) / pageSize),
    },
  };
}

Transactions

const result = await db.transaction(async (tx) => {
  const [item] = await tx
    .insert(items)
    .values({ title: "New", price: 100, authorId: userId })
    .returning();

  await tx.insert(itemTags).values(
    tagIds.map((tagId) => ({ itemId: item.id, tagId }))
  );

  return item;
});

Database Client Setup

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });
export type Database = typeof db;

Migrations

# Generate migration from schema changes
pnpm drizzle-kit generate

# Apply migrations
pnpm drizzle-kit migrate

# Push schema directly (development only)
pnpm drizzle-kit push

Model Class Pattern

export class ItemModel {
  constructor(private db: Database) {}

  async findById(id: string) {
    return this.db.query.items.findFirst({
      where: and(eq(items.id, id), isNull(items.deletedAt)),
      with: { author: true },
    });
  }

  async create(data: NewItem) {
    const [item] = await this.db.insert(items).values(data).returning();
    return item;
  }

  async update(id: string, data: Partial<NewItem>) {
    const [item] = await this.db
      .update(items)
      .set({ ...data, updatedAt: new Date() })
      .where(eq(items.id, id))
      .returning();
    return item;
  }

  async softDelete(id: string) {
    const [item] = await this.db
      .update(items)
      .set({ deletedAt: new Date() })
      .where(eq(items.id, id))
      .returning();
    return item;
  }
}

Testing

import { describe, it, expect, beforeEach, afterAll } from "vitest";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "../schema";

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

describe("ItemModel", () => {
  beforeEach(async () => {
    await db.delete(schema.items);
  });
  afterAll(async () => {
    await pool.end();
  });

  it("should create and retrieve an item", async () => {
    const [item] = await db
      .insert(schema.items)
      .values({ title: "Test", price: 100, authorId: testUserId })
      .returning();
    expect(item.id).toBeDefined();
    expect(item.title).toBe("Test");
  });
});

Best Practices

Use $inferSelect and $inferInsert for type inference instead of manually defining types
Define relations() for type-safe joins with the query API
Use returning() to get inserted or updated rows without a separate query
Add indexes on frequently queried and filtered columns
Use transactions for multi-step operations that must be atomic
Implement soft delete with a deletedAt column and filter with isNull()
Separate schema definitions, relations, and query logic into distinct files
Use the Model class pattern for encapsulating domain-specific queries
Run drizzle-kit generate for production migrations, push only in development
Always run count queries in parallel with data queries for pagination

drizzle-patterns