Database Patterns with Drizzle ORM

Schema Design

Table Definition

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

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }),
  bio: text("bio"),
  isAdmin: boolean("is_admin").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// Type exports
export type User = typeof users.$inferSelect;
export type UserCreate = typeof users.$inferInsert;

Relationships

import { relations } from "drizzle-orm";

// One-to-many
export const modules = pgTable("modules", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  order: integer("order").default(0).notNull(),
});

export const segments = pgTable("segments", {
  id: serial("id").primaryKey(),
  moduleId: integer("module_id")
    .references(() => modules.id, { onDelete: "cascade" })
    .notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  order: integer("order").default(0).notNull(),
});

// Define relations for query builder
export const modulesRelations = relations(modules, ({ many }) => ({
  segments: many(segments),
}));

export const segmentsRelations = relations(segments, ({ one }) => ({
  module: one(modules, {
    fields: [segments.moduleId],
    references: [modules.id],
  }),
}));

Indexes

import { pgTable, index, uniqueIndex } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
}, (table) => ({
  emailIdx: uniqueIndex("email_idx").on(table.email),
  createdAtIdx: index("created_at_idx").on(table.createdAt),
}));

Data Access Layer

Data access functions go in /src/data-access/. They contain pure database operations with no business logic.

Naming Convention

• Function name: verbNoun (e.g., createUser, getSegmentById)

Basic CRUD Operations

// src/data-access/users.ts
import { database } from "~/db";
import { users } from "~/db/schema";
import { eq } from "drizzle-orm";
import type { User, UserCreate } from "~/db/schema";

export async function getUsers() {
  return database.query.users.findMany();
}

export async function getUserById(id: number) {
  const result = await database
    .select()
    .from(users)
    .where(eq(users.id, id))
    .limit(1);
  return result[0];
}

export async function getUserByEmail(email: string) {
  const result = await database
    .select()
    .from(users)
    .where(eq(users.email, email))
    .limit(1);
  return result[0];
}

export async function createUser(user: UserCreate) {
  const result = await database.insert(users).values(user).returning();
  return result[0];
}

export async function updateUser(id: number, user: Partial<UserCreate>) {
  const result = await database
    .update(users)
    .set({ ...user, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

export async function deleteUser(id: number) {
  const result = await database
    .delete(users)
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

Query Patterns

Select Specific Columns

// Only select what you need
const users = await database
  .select({
    id: users.id,
    name: users.name,
    email: users.email,
  })
  .from(users);

Filtering

import { eq, ne, gt, lt, gte, lte, like, and, or, isNull, isNotNull, inArray } from "drizzle-orm";

// Equality
const user = await database
  .select()
  .from(users)
  .where(eq(users.email, email));

// Multiple conditions
const activeAdmins = await database
  .select()
  .from(users)
  .where(and(eq(users.isAdmin, true), isNotNull(users.lastLoginAt)));

// OR conditions
const results = await database
  .select()
  .from(users)
  .where(or(eq(users.role, "admin"), eq(users.role, "moderator")));

// IN clause
const selectedUsers = await database
  .select()
  .from(users)
  .where(inArray(users.id, [1, 2, 3]));

// LIKE pattern matching
const matchingUsers = await database
  .select()
  .from(users)
  .where(like(users.name, `%${searchTerm}%`));

Joins

import { eq } from "drizzle-orm";

// Inner join
const segmentsWithModules = await database
  .select({
    segment: segments,
    moduleTitle: modules.title,
  })
  .from(segments)
  .innerJoin(modules, eq(segments.moduleId, modules.id));

// Left join (optional relationship)
const usersWithProgress = await database
  .select()
  .from(users)
  .leftJoin(progress, eq(users.id, progress.userId));

Using Query Builder with Relations

// Get segments with their modules (using relations)
const result = await database.query.segments.findMany({
  with: {
    module: true,
  },
  orderBy: [segments.order],
});

// Nested relations
const modulesWithSegments = await database.query.modules.findMany({
  with: {
    segments: {
      with: {
        attachments: true,
      },
    },
  },
});

Ordering and Pagination

import { desc, asc } from "drizzle-orm";

const paginatedUsers = await database
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(20)
  .offset(40);

Aggregations

import { sql, count } from "drizzle-orm";

// Count
const [{ total }] = await database
  .select({ total: count() })
  .from(users);

// Sum, avg, etc.
const [{ avgPrice }] = await database
  .select({ avgPrice: sql`avg(${products.price})` })
  .from(products);

Transactions

export async function reorderSegmentsUseCase(
  updates: { id: number; order: number }[]
) {
  return database.transaction(async (tx) => {
    const results = [];
    for (const update of updates) {
      const [result] = await tx
        .update(segments)
        .set({ order: update.order, updatedAt: new Date() })
        .where(eq(segments.id, update.id))
        .returning();
      results.push(result);
    }
    return results;
  });
}

Migration Commands

# Generate migration from schema changes
npm run db:generate

# Run migrations
npm run db:migrate

# Push schema directly (development only)
npm run db:push

# Open Drizzle Studio
npm run db:studio

# Reset database (clear, migrate, seed)
npm run db:reset

Common Patterns

Soft Delete

export const users = pgTable("users", {
  // ...other fields
  deletedAt: timestamp("deleted_at"),
});

// Query only non-deleted
const activeUsers = await database
  .select()
  .from(users)
  .where(isNull(users.deletedAt));

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

Timestamp Management

// Always update updatedAt on modifications
export async function updateUser(id: number, data: Partial<UserCreate>) {
  const result = await database
    .update(users)
    .set({ ...data, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

Check if Exists

export async function isEmailInUse(email: string): Promise<boolean> {
  const existing = await database
    .select({ id: users.id })
    .from(users)
    .where(eq(users.email, email))
    .limit(1);
  return existing.length > 0;
}

Database Checklist

• Tables have appropriate indexes for queried columns
• Foreign keys use onDelete cascade where appropriate
• Data access functions use verbNoun naming
• Select only needed columns, not select()
• Use transactions for multi-step operations
• Always update updatedAt on modifications
• Use parameterized queries (automatic with Drizzle)
• Run db:generate after schema changes
• Type exports for $inferSelect and $inferInsert
• Relations defined for query builder usage