DW225/drizzle-patterns

Drizzle ORM Patterns

When to Use This Skill

Activate this skill when working on:

• Designing or modifying database schema
• Creating database migrations
• Writing database queries
• Optimizing query performance
• Managing Turso database operations
• Implementing transactions
• Adding indexes
• Schema type safety and validation

Core Patterns

Schema Definition with Relationships

Primary Keys: All tables use Nano IDs for primary keys

Schema Pattern:

// db/schema.ts
import { sqliteTable, text, integer, index } from "drizzle-orm/sqlite-core";
import { relations } from "drizzle-orm";
import { nanoid } from "nanoid";

export const boardTable = sqliteTable(
  "board",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => nanoid()),
    name: text("name").notNull(),
    userId: text("user_id").notNull(),
    createdAt: integer("created_at", { mode: "timestamp" }).$defaultFn(
      () => new Date()
    ),
  },
  (table) => ({
    userIdIndex: index("board_user_id_index").on(table.userId),
  })
);

export const postTable = sqliteTable(
  "post",
  {
    id: text("id")
      .primaryKey()
      .$defaultFn(() => nanoid()),
    boardId: text("board_id")
      .notNull()
      .references(() => boardTable.id, { onDelete: "cascade" }),
    userId: text("user_id").notNull(),
    content: text("content").notNull(),
    type: text("type", {
      enum: ["went_well", "to_improve", "action_items"],
    }).notNull(),
    voteCount: integer("vote_count").default(0),
  },
  (table) => ({
    boardIdIndex: index("post_board_id_index").on(table.boardId),
    userIdIndex: index("post_user_id_index").on(table.userId),
  })
);

// Define relationships
export const boardRelations = relations(boardTable, ({ many }) => ({
  posts: many(postTable),
  members: many(memberTable),
}));

export const postRelations = relations(postTable, ({ one, many }) => ({
  board: one(boardTable, {
    fields: [postTable.boardId],
    references: [boardTable.id],
  }),
  votes: many(voteTable),
}));

Migration Workflow

Step-by-Step Process:

1. Modify Schema (db/schema.ts)

2. Generate Migration:

   pnpm generate
   

3. Review Generated SQL (in drizzle/ folder)

4. Apply to Development:

   pnpm push:dev
   

5. Test Thoroughly

6. Apply to Production:

   pnpm push
   

Example Migration:

// After adding a new column to schema.ts:
export const boardTable = sqliteTable("board", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
  description: text("description"), // ✅ New field
  // ...
});

Prepared Statements for Performance

Critical: Use prepared statements for repeated queries

Pattern:

// lib/db/post.ts
import { db } from "@/db";
import { postTable } from "@/db/schema";
import { eq, sql } from "drizzle-orm";

// ✅ Prepared statement for repeated queries
export const prepareFetchPostsByBoardID = db
  .select()
  .from(postTable)
  .where(eq(postTable.boardId, sql.placeholder("boardId")))
  .prepare();

// Usage
export async function getPostsByBoardId(boardId: string) {
  return prepareFetchPostsByBoardID.execute({ boardId });
}

When to Use Prepared Statements:

• Queries executed multiple times with different parameters
• High-frequency operations (fetching posts, votes)
• Performance-critical paths

Transaction Patterns

Use for Multi-Table Operations:

import { db } from "@/db";

export async function createBoardWithMember(name: string, userId: string) {
  return db.transaction(async (tx) => {
    // Create board
    const [board] = await tx
      .insert(boardTable)
      .values({
        id: nanoid(),
        name,
        userId,
      })
      .returning();

    // Add owner as member
    await tx.insert(memberTable).values({
      id: nanoid(),
      boardId: board.id,
      userId,
      role: "owner",
    });

    return board;
  });
}

Transaction Best Practices:

• Keep transactions small and fast
• Handle errors appropriately
• Use for data consistency requirements
• Avoid long-running operations in transactions

Index Strategy

Index All Foreign Keys and Frequently Queried Columns:

export const postTable = sqliteTable(
  "post",
  {
    id: text("id").primaryKey(),
    boardId: text("board_id").notNull(),
    userId: text("user_id").notNull(),
    type: text("type"),
  },
  (table) => ({
    // ✅ Index foreign keys
    boardIdIndex: index("post_board_id_index").on(table.boardId),
    userIdIndex: index("post_user_id_index").on(table.userId),
    // ✅ Index frequently filtered columns
    typeIndex: index("post_type_index").on(table.type),
  })
);

Indexing Guidelines:

• Always index foreign keys
• Index columns used in WHERE clauses
• Index columns used in JOIN conditions
• Monitor query performance to identify missing indexes

Query Optimization

Efficient Queries:

import { db } from "@/db";
import { postTable, voteTable } from "@/db/schema";
import { eq, and, count, inArray, sql } from "drizzle-orm";

// ✅ Select only needed columns
export async function getBoardPostSummary(boardId: string) {
  return db
    .select({
      id: postTable.id,
      content: postTable.content,
      voteCount: postTable.voteCount,
    })
    .from(postTable)
    .where(eq(postTable.boardId, boardId));
}

// ✅ Use aggregations efficiently
export async function getVoteCountForPost(postId: string) {
  const [result] = await db
    .select({ count: count() })
    .from(voteTable)
    .where(eq(voteTable.postId, postId));

  return result.count;
}

// ✅ Batch operations
export async function updatePostVotes(postIds: string[]) {
  return db
    .update(postTable)
    .set({ voteCount: sql`vote_count + 1` })
    .where(inArray(postTable.id, postIds));
}

Anti-Patterns

❌ Not Using Prepared Statements for Repeated Queries

Bad:

export async function getPostsByBoardId(boardId: string) {
  // ❌ Query parsed every time
  return db.select().from(postTable).where(eq(postTable.boardId, boardId));
}

Good:

const preparedQuery = db
  .select()
  .from(postTable)
  .where(eq(postTable.boardId, sql.placeholder("boardId")))
  .prepare();

export async function getPostsByBoardId(boardId: string) {
  // ✅ Prepared statement reused
  return preparedQuery.execute({ boardId });
}

❌ Missing Indexes on Foreign Keys

Bad:

export const postTable = sqliteTable("post", {
  id: text("id").primaryKey(),
  boardId: text("board_id").notNull(),
  // ❌ No index on foreign key
});

Good:

export const postTable = sqliteTable(
  "post",
  {
    id: text("id").primaryKey(),
    boardId: text("board_id").notNull(),
  },
  (table) => ({
    // ✅ Index on foreign key
    boardIdIndex: index("post_board_id_index").on(table.boardId),
  })
);

❌ Not Using Transactions for Multi-Table Operations

Bad:

// ❌ Two separate operations - potential inconsistency
await db.insert(boardTable).values(board);
await db.insert(memberTable).values(member); // Could fail leaving orphaned board

Good:

// ✅ Transaction ensures atomicity
await db.transaction(async (tx) => {
  await tx.insert(boardTable).values(board);
  await tx.insert(memberTable).values(member);
});

❌ Selecting All Columns When Only Few Needed

Bad:

// ❌ Fetches all columns
const posts = await db
  .select()
  .from(postTable)
  .where(eq(postTable.boardId, boardId));

Good:

// ✅ Select only needed columns
const posts = await db
  .select({
    id: postTable.id,
    content: postTable.content,
  })
  .from(postTable)
  .where(eq(postTable.boardId, boardId));

❌ Modifying Schema Without Migration

Bad:

// ❌ Directly modifying schema and pushing without generating migration
// Changes schema.ts
// Runs: pnpm push

Good:

// ✅ Proper migration workflow
// 1. Modify schema.ts
// 2. Run: pnpm generate
// 3. Review generated SQL
// 4. Run: pnpm push:dev (test)
// 5. Run: pnpm push (production)

Integration with Other Skills

• nextjs-app-router: Data fetching in server components and actions
• rbac-security: Database operations require authentication
• testing-patterns: Test database operations and queries
• signal-state-management: Sync database state with signals

Project-Specific Context

Key Files

• db/schema.ts - Complete database schema with all tables and relationships
• db/index.ts - Database connection configuration
• lib/db/ - Prepared statements and query utilities by entity
• drizzle.config.ts - Drizzle configuration for migrations
• drizzle/ - Generated migration files

Database Configuration

Development:

// Uses local SQLite file
DATABASE_URL=file:test.db

Production:

# Uses Turso Cloud
TURSO_DATABASE_URL=<TURSO_DATABASE_URL>
TURSO_AUTH_TOKEN=<TURSO_AUTH_TOKEN>

Project Conventions

1. Nano IDs for all primary keys
2. Foreign key indexes on all relationships
3. Prepared statements for repeated queries
4. Transactions for multi-table operations
5. Cascade deletes where appropriate (posts delete with board)
6. Vote count optimization denormalized in post table

Common Queries

Fetch Board with Posts:

const board = await db.query.boardTable.findFirst({
  where: eq(boardTable.id, boardId),
  with: {
    posts: true,
    members: true,
  },
});

Update Vote Count:

await db
  .update(postTable)
  .set({ voteCount: sql`vote_count + 1` })
  .where(eq(postTable.id, postId));

---

Last Updated: 2026-01-10