---

name: agentic-jumpstart-database description: Database patterns with Drizzle ORM and PostgreSQL including schema design, queries, migrations, transactions, and relationships. Use when working with database schemas, queries, migrations, indexes, joins, or when the user mentions database, Drizzle, PostgreSQL, SQL, or data access.

Database Patterns with Drizzle ORM

Schema Design

Table Definition

Copyimport { 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

Copyimport { 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

Copyimport { 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

Copy// 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

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

Filtering

Copyimport { 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

Copyimport { 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

Copy// 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

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

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

Aggregations

Copyimport { 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

Copyexport 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

Copy# 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

Copyexport 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

Copy// 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

Copyexport 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