Drizzle ORM avec Claude Code: PostgreSQL, migrations, Zod et CI

Drizzle ORM est un ORM TypeScript qui reste proche de SQL. On decrit les tables en TypeScript, puis on ecrit des requetes dont la forme reste lisible pour une personne qui connait SQL. Cette proximite en fait un bon compagnon pour Claude Code: l’agent peut lire le schema, proposer des relations, produire une migration et expliquer quel index sert quelle requete.

La partie dangereuse est ailleurs. Un schema peut compiler alors que la migration supprime une colonne. Un seed peut fonctionner une fois et echouer a la deuxieme execution. Une transaction peut garder une connexion ouverte pendant qu’elle attend un email, Stripe ou un webhook. Pour eviter cela, il faut demander a Claude Code un flux complet: schema, migration, query, transaction, seed, validation Zod et CI.

Les references a garder ouvertes sont les docs officielles Drizzle ORM, Drizzle Kit, Transactions, Drizzle Zod, et la documentation Claude Code. Pour comparer, lisez aussi Prisma ORM, database migration et Zod validation.

Methode de travail

Un prompt utile donne le contexte produit et les controles attendus.

Implemente la couche base de donnees avec Drizzle ORM.

Stack:
- PostgreSQL
- drizzle-orm, drizzle-kit, node-postgres
- tables: User, Post, Category, Comment, AuditLog
- Post status: draft, published, archived
- email et slug uniques
- liste des articles par status, publishedAt, author, category et search
- supprimer Post cascade seulement Comment et la table de jointure
- supprimer User ne doit pas supprimer Post

Retourne schema, drizzle.config.ts, queries, transaction, seed, Zod et CI.

flowchart LR
  A["Prompt"] --> B["schema.ts"]
  B --> C["drizzle-kit generate"]
  C --> D["revue SQL"]
  D --> E["queries, transaction, seed"]
  E --> F["validation Zod"]
  F --> G["CI checks"]

Schema et migration

Ce schema montre les decisions a relire: unique, index, relations et regles de suppression.

// db/schema.ts
import { relations } from "drizzle-orm";
import {
  boolean,
  index,
  integer,
  jsonb,
  pgEnum,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from "drizzle-orm/pg-core";

export const postStatus = pgEnum("post_status", ["draft", "published", "archived"]);

export const users = pgTable(
  "users",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    email: varchar("email", { length: 255 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
    role: varchar("role", { length: 40 }).default("editor").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("users_email_unique").on(table.email),
    index("users_role_idx").on(table.role),
  ],
);

export const categories = pgTable(
  "categories",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 120 }).notNull(),
    name: varchar("name", { length: 120 }).notNull(),
  },
  (table) => [uniqueIndex("categories_slug_unique").on(table.slug)],
);

export const posts = pgTable(
  "posts",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    slug: varchar("slug", { length: 160 }).notNull(),
    title: varchar("title", { length: 160 }).notNull(),
    body: text("body").notNull(),
    status: postStatus("status").default("draft").notNull(),
    authorId: uuid("author_id").notNull().references(() => users.id, { onDelete: "restrict" }),
    viewCount: integer("view_count").default(0).notNull(),
    featured: boolean("featured").default(false).notNull(),
    publishedAt: timestamp("published_at", { withTimezone: true }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    uniqueIndex("posts_slug_unique").on(table.slug),
    index("posts_status_published_at_idx").on(table.status, table.publishedAt),
    index("posts_author_id_idx").on(table.authorId),
  ],
);

export const postCategories = pgTable(
  "post_categories",
  {
    postId: uuid("post_id").notNull().references(() => posts.id, { onDelete: "cascade" }),
    categoryId: uuid("category_id").notNull().references(() => categories.id, { onDelete: "cascade" }),
  },
  (table) => [primaryKey({ columns: [table.postId, table.categoryId] })],
);

export const comments = pgTable(
  "comments",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    postId: uuid("post_id").notNull().references(() => posts.id, { onDelete: "cascade" }),
    authorId: uuid("author_id").notNull().references(() => users.id, { onDelete: "restrict" }),
    body: text("body").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [
    index("comments_post_created_at_idx").on(table.postId, table.createdAt),
    index("comments_author_id_idx").on(table.authorId),
  ],
);

export const auditLogs = pgTable(
  "audit_logs",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    action: varchar("action", { length: 80 }).notNull(),
    targetId: uuid("target_id").notNull(),
    metadata: jsonb("metadata").$type<Record<string, unknown>>(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  },
  (table) => [index("audit_logs_action_created_at_idx").on(table.action, table.createdAt)],
);

export const usersRelations = relations(users, ({ many }) => ({ posts: many(posts), comments: many(comments) }));
export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, { fields: [posts.authorId], references: [users.id] }),
  comments: many(comments),
  categories: many(postCategories),
}));
export const categoriesRelations = relations(categories, ({ many }) => ({ posts: many(postCategories) }));
export const postCategoriesRelations = relations(postCategories, ({ one }) => ({
  post: one(posts, { fields: [postCategories.postId], references: [posts.id] }),
  category: one(categories, { fields: [postCategories.categoryId], references: [categories.id] }),
}));
export const commentsRelations = relations(comments, ({ one }) => ({
  post: one(posts, { fields: [comments.postId], references: [posts.id] }),
  author: one(users, { fields: [comments.authorId], references: [users.id] }),
}));

// drizzle.config.ts
import "dotenv/config";
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./db/schema.ts",
  out: "./drizzle",
  dialect: "postgresql",
  dbCredentials: { url: process.env.DATABASE_URL! },
  verbose: true,
  strict: true,
});

npm run db:generate
npm run db:check
npm run db:migrate

Relisez le SQL genere. Cherchez les DROP non voulus, les colonnes NOT NULL ajoutees sans backfill, les cascades trop larges et les index qui ne correspondent a aucune requete.

Requetes, transaction et seed

// db/client.ts
import "dotenv/config";
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import * as schema from "./schema";

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

// db/posts.ts
import { and, desc, eq, ilike, sql } from "drizzle-orm";
import { z } from "zod";
import { db } from "./client";
import { auditLogs, categories, comments, postCategories, posts, users } from "./schema";
import { createPostInputSchema } from "./validation";

type CreatePostInput = z.infer<typeof createPostInputSchema>;

export async function createPost(input: CreatePostInput) {
  const data = createPostInputSchema.parse(input);

  return db.transaction(async (tx) => {
    const [post] = await tx.insert(posts).values({
      slug: data.slug,
      title: data.title,
      body: data.body,
      authorId: data.authorId,
    }).returning();

    for (const slug of data.categorySlugs) {
      const [category] = await tx.insert(categories)
        .values({ slug, name: slug })
        .onConflictDoUpdate({ target: categories.slug, set: { name: slug } })
        .returning();
      await tx.insert(postCategories)
        .values({ postId: post.id, categoryId: category.id })
        .onConflictDoNothing();
    }

    await tx.insert(auditLogs).values({
      action: "post.create",
      targetId: post.id,
      metadata: { slug: post.slug },
    });

    return post;
  });
}

export async function listPublishedPosts(params: { page?: number; perPage?: number; search?: string } = {}) {
  const page = Math.max(params.page ?? 1, 1);
  const perPage = Math.min(Math.max(params.perPage ?? 20, 1), 50);
  const where = params.search
    ? and(eq(posts.status, "published"), ilike(posts.title, `%${params.search}%`))
    : eq(posts.status, "published");

  const [items, [{ total }]] = await Promise.all([
    db.select({
      id: posts.id,
      slug: posts.slug,
      title: posts.title,
      publishedAt: posts.publishedAt,
      authorName: users.name,
      commentCount: sql<number>`count(${comments.id})::int`,
    })
      .from(posts)
      .innerJoin(users, eq(posts.authorId, users.id))
      .leftJoin(comments, eq(comments.postId, posts.id))
      .where(where)
      .groupBy(posts.id, posts.slug, posts.title, posts.publishedAt, users.name)
      .orderBy(desc(posts.publishedAt), desc(posts.createdAt))
      .limit(perPage)
      .offset((page - 1) * perPage),
    db.select({ total: sql<number>`count(*)::int` }).from(posts).where(where),
  ]);

  return { items, pagination: { page, perPage, total, totalPages: Math.ceil(total / perPage) } };
}

Le seed doit pouvoir etre relance sans casser l’environnement.

// db/seed.ts
import { db, pool } from "./client";
import { categories, postCategories, posts, users } from "./schema";

async function main() {
  const [user] = await db.insert(users)
    .values({ email: "masa@example.com", name: "Masa", role: "admin" })
    .onConflictDoUpdate({ target: users.email, set: { name: "Masa", role: "admin", updatedAt: new Date() } })
    .returning();

  const [category] = await db.insert(categories)
    .values({ slug: "drizzle", name: "Drizzle ORM" })
    .onConflictDoUpdate({ target: categories.slug, set: { name: "Drizzle ORM" } })
    .returning();

  const [post] = await db.insert(posts)
    .values({
      slug: "claude-code-drizzle-demo",
      title: "Claude Code Drizzle demo",
      body: "A seeded post for local verification.",
      status: "published",
      authorId: user.id,
      publishedAt: new Date(),
    })
    .onConflictDoUpdate({ target: posts.slug, set: { title: "Claude Code Drizzle demo", updatedAt: new Date() } })
    .returning();

  await db.insert(postCategories).values({ postId: post.id, categoryId: category.id }).onConflictDoNothing();
}

main().finally(async () => pool.end());

Zod et CI

TypeScript ne valide pas les donnees entrantes a l’execution. Validez l’entree API avant l’insertion.

// db/validation.ts
import { createInsertSchema } from "drizzle-orm/zod";
import { z } from "zod";
import { posts } from "./schema";

export const createPostInputSchema = createInsertSchema(posts, {
  slug: (schema) => schema.min(3).max(160).regex(/^[a-z0-9-]+$/),
  title: (schema) => schema.min(1).max(160),
  body: (schema) => schema.min(50),
})
  .pick({ slug: true, title: true, body: true, authorId: true })
  .extend({
    categorySlugs: z.array(z.string().min(1).max(120)).min(1).max(5),
  });

name: drizzle
on:
  pull_request:
jobs:
  db:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: app
          POSTGRES_PASSWORD: app
          POSTGRES_DB: app_test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    env:
      DATABASE_URL: postgresql://app:app@localhost:5432/app_test
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
          cache: npm
      - run: npm ci
      - run: npm run db:generate
      - run: npm run db:check
      - run: npm run db:migrate
      - run: npm run db:seed
      - run: npm run typecheck
      - run: npm test

Cas d’usage, erreurs et CTA

Cas	Point sensible	Demande a Claude Code
CMS editorial	slug, statut, date de publication	Explique l’index de chaque liste
Admin SaaS	tenant, droits, audit	Ne supprime pas les donnees metier avec l’utilisateur
Plateforme de cours	progression, acces, transaction	Garde progression et audit log coherents
Contenu monetise	CTA, produit, lecture complete	Ajoute analytics sans affaiblir les contraintes

Les erreurs frequentes: ne pas lire SQL, creer un seed non idempotent, appeler des services externes dans une transaction, croire que Zod remplace les contraintes DB, oublier une limite de pagination, laisser Claude Code modifier des migrations sans rapport. Pour industrialiser la methode dans une vraie equipe, utilisez la page anglaise Claude Code training and consultation. Pour approfondir, reliez ce guide a Supabase integration et CI/CD setup.

Dans un petit projet de blog API, le meilleur gain a ete de demander a Claude Code d’expliquer le SQL genere. Le premier jet etait trop agressif sur une regle de suppression. Apres une revue separee de onDelete, des index et du seed idempotent, la modification etait beaucoup plus facile a approuver. Drizzle ORM donne de la vitesse, mais la securite vient de la revue SQL et des preuves CI.