Claude Code × Drizzle ORM: Type-Safe Database Development 2026

# Prompt to Claude Code (via CLAUDE.md or chat)

// drizzle.config.ts
import type { Config } from "drizzle-kit";
import * as dotenv from "dotenv";
dotenv.config();
 
export default {
  schema: "./src/db/schema/index.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  verbose: true,
  strict: true,
} satisfies Config;

// src/db/index.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";
 
// Production-ready connection pool configuration
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // Max concurrent connections
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Fail fast if pool is exhausted
});
 
export const db = drizzle(pool, { schema });
 
export async function testConnection() {
  try {
    await pool.query("SELECT 1");
    console.log("✅ Database connected successfully");
    return true;
  } catch (error) {
    console.error("❌ Database connection failed:", error);
    return false;
  }
}

// src/db/schema/users.ts
import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  boolean,
  pgEnum,
} from "drizzle-orm/pg-core";
 
export const userRoleEnum = pgEnum("user_role", ["admin", "editor", "viewer"]);
 
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  displayName: varchar("display_name", { length: 100 }).notNull(),
  avatarUrl: text("avatar_url"),
  role: userRoleEnum("role").notNull().default("viewer"),
  emailVerified: boolean("email_verified").notNull().default(false),
  createdAt: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
  deletedAt: timestamp("deleted_at", { withTimezone: true }),
});
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

// src/db/schema/posts.ts
import {
  pgTable,
  uuid,
  varchar,
  text,
  timestamp,
  integer,
  pgEnum,
  index,
} from "drizzle-orm/pg-core";
import { users } from "./users";
 
export const postStatusEnum = pgEnum("post_status", [
  "draft",
  "published",
  "archived",
]);
 
export const posts = pgTable(
  "posts",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    title: varchar("title", { length: 500 }).notNull(),
    slug: varchar("slug", { length: 500 }).notNull().unique(),
    content: text("content"),
    excerpt: text("excerpt"),
    authorId: uuid("author_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    status: postStatusEnum("status").notNull().default("draft"),
    viewCount: integer("view_count").notNull().default(0),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
    deletedAt: timestamp("deleted_at", { withTimezone: true }),
  },
  (table) => ({
    authorIdIdx: index("posts_author_id_idx").on(table.authorId),
    statusIdx: index("posts_status_idx").on(table.status),
    slugIdx: index("posts_slug_idx").on(table.slug),
    // Composite index for sorted published posts queries
    statusPublishedAtIdx: index("posts_status_published_at_idx").on(
      table.status,
      table.publishedAt
    ),
  })
);
 
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

// src/db/schema/tags.ts
import { pgTable, uuid, varchar, primaryKey } from "drizzle-orm/pg-core";
import { posts } from "./posts";
 
export const tags = pgTable("tags", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: varchar("name", { length: 100 }).notNull().unique(),
  slug: varchar("slug", { length: 100 }).notNull().unique(),
});
 
// Junction table for many-to-many relationship
export const postTags = pgTable(
  "post_tags",
  {
    postId: uuid("post_id")
      .notNull()
      .references(() => posts.id, { onDelete: "cascade" }),
    tagId: uuid("tag_id")
      .notNull()
      .references(() => tags.id, { onDelete: "cascade" }),
  },
  (table) => ({
    pk: primaryKey({ columns: [table.postId, table.tagId] }),
  })
);
 
export type Tag = typeof tags.$inferSelect;

// package.json
{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio",
    "db:check": "drizzle-kit check",
    "db:drop": "drizzle-kit drop"
  }
}

npm run db:generate
# → src/db/migrations/0001_xxxxx.sql is created

npm run db:migrate
# → Runs all pending migrations in order

// scripts/migrate.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { Pool } from "pg";
 
async function runMigration() {
  console.log("🔄 Starting database migration...");
 
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
    max: 1, // Limit connections during migration
  });
 
  const db = drizzle(pool);
 
  try {
    await migrate(db, {
      migrationsFolder: "./src/db/migrations",
    });
    console.log("✅ Migration completed successfully");
  } catch (error) {
    console.error("❌ Migration failed:", error);
    process.exit(1);
  } finally {
    await pool.end();
  }
}
 
runMigration();

// src/db/queries/posts.ts
import { db } from "../index";
import { posts, users, postTags, tags } from "../schema";
import { and, eq, isNull, desc, sql } from "drizzle-orm";
 
// Fetch published posts with author info — fully typed return value
export async function getPublishedPosts(limit = 10, offset = 0) {
  return await db
    .select({
      id: posts.id,
      title: posts.title,
      slug: posts.slug,
      excerpt: posts.excerpt,
      publishedAt: posts.publishedAt,
      viewCount: posts.viewCount,
      author: {
        id: users.id,
        displayName: users.displayName,
        avatarUrl: users.avatarUrl,
      },
    })
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .where(
      and(
        eq(posts.status, "published"),
        isNull(posts.deletedAt),
        isNull(users.deletedAt)
      )
    )
    .orderBy(desc(posts.publishedAt))
    .limit(limit)
    .offset(offset);
}

// src/db/queries/transactions.ts
export async function createPostWithTags(
  postData: { title: string; slug: string; content: string; authorId: string },
  tagNames: string[]
) {
  return await db.transaction(async (tx) => {
    // 1. Create the post
    const [newPost] = await tx
      .insert(posts)
      .values(postData)
      .returning();
 
    // 2. Upsert tags (create if they don't exist)
    const upsertedTags = await Promise.all(
      tagNames.map((name) =>
        tx
          .insert(tags)
          .values({ name, slug: name.toLowerCase().replace(/\s+/g, "-") })
          .onConflictDoUpdate({ target: tags.slug, set: { name } })
          .returning()
      )
    );
 
    // 3. Link post to tags
    const tagIds = upsertedTags.map(([tag]) => tag.id);
    if (tagIds.length > 0) {
      await tx.insert(postTags).values(
        tagIds.map((tagId) => ({ postId: newPost.id, tagId }))
      );
    }
 
    return { post: newPost, tagCount: tagIds.length };
    // Any error rolls back the entire transaction automatically
  });
}

export async function getPostsCursor(cursor?: string, limit = 10) {
  const results = await db
    .select({
      id: posts.id,
      title: posts.title,
      slug: posts.slug,
      publishedAt: posts.publishedAt,
    })
    .from(posts)
    .where(
      and(
        eq(posts.status, "published"),
        isNull(posts.deletedAt),
        cursor
          ? sql`${posts.publishedAt} < ${new Date(cursor).toISOString()}`
          : undefined
      )
    )
    .orderBy(desc(posts.publishedAt))
    .limit(limit + 1); // Fetch one extra to detect next page
 
  const hasNextPage = results.length > limit;
  const items = hasNextPage ? results.slice(0, -1) : results;
  const nextCursor = hasNextPage
    ? items[items.length - 1].publishedAt?.toISOString()
    : null;
 
  return { items, nextCursor, hasNextPage };
}

<!-- CLAUDE.md -->
# Database Development Rules
 
## Drizzle ORM Conventions
- Always run `npm run db:generate` after any schema change
- Prefer explicit `db.select().from()` over `db.query.*` relational API for clarity
- All query functions must include error handling
- Avoid N+1 queries — always use JOINs or batch queries
 
## Naming Conventions
- Tables: snake_case plural (users, posts, post_tags)
- Columns: snake_case (created_at, author_id)
- TypeScript variables: camelCase (createdAt, authorId)
- Indexes: {tableName}_{columnName}_idx pattern
 
## Security
- Never interpolate user input directly into sql`` template literals
- Always use Drizzle's parameterized query API
- Use `sql.raw()` only for trusted, static SQL fragments

// .claude/settings.json
{
  "hooks": {
    "PreToolUse": [
      {
        "matcher": "Write|Edit",
        "hooks": [
          {
            "type": "command",
            "command": "bash -c \"file='$TOOL_ARGS'; if echo \"$file\" | grep -q 'schema.*\\.ts'; then echo '⚠️ Schema change detected. Remember to run: npm run db:generate'; fi\""
          }
        ]
      }
    ]
  }
}

// src/db/utils/explain.ts
import { db } from "../index";
import { sql } from "drizzle-orm";
 
export async function explainQuery(queryStr: string) {
  const result = await db.execute(
    sql`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql.raw(queryStr)}`
  );
  return result.rows[0];
}

// Advanced index design in schema
export const posts = pgTable(
  "posts",
  {
    // ... fields
  },
  (table) => ({
    // Only index published posts — dramatically smaller index
    publishedPostsIdx: index("posts_published_idx")
      .on(table.publishedAt)
      .where(sql`status = 'published'`),
 
    // Composite partial index for active posts by author
    activePostsIdx: index("posts_active_idx")
      .on(table.authorId, table.createdAt)
      .where(sql`deleted_at IS NULL`),
  })
);

// src/db/edge.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import * as schema from "./schema";
 
export function createDb(env: { DATABASE_URL: string }) {
  const sql = neon(env.DATABASE_URL);
  return drizzle(sql, { schema });
}
 
export default {
  async fetch(request: Request, env: { DATABASE_URL: string }) {
    const db = createDb(env);
 
    const latestPosts = await db
      .select({ id: schema.posts.id, title: schema.posts.title })
      .from(schema.posts)
      .where(eq(schema.posts.status, "published"))
      .limit(10);
 
    return Response.json({ posts: latestPosts });
  },
};

// When using Cloudflare Hyperdrive (managed connection pooling)
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
 
export function createDbWithHyperdrive(env: { HYPERDRIVE: Hyperdrive }) {
  const pool = new Pool({
    connectionString: env.HYPERDRIVE.connectionString,
    // Hyperdrive handles pooling — keep local pool minimal
    max: 5,
  });
  return drizzle(pool, { schema });
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,           // Lower the ceiling
  idleTimeoutMillis: 10000,
  connectionTimeoutMillis: 3000,
});

const status = "published" as const;
type PostStatus = typeof postStatusEnum.enumValues[number];