Claude Code and Prisma ORM: Schema Design, Migrations, Transactions, and Tests

Prisma ORM is a typed database layer for safely working with databases from TypeScript. With Claude Code, it can become more than a convenient CRUD generator: you can ask the agent to design the schema, explain the migration, write Prisma Client calls, add transaction boundaries, and produce a review checklist before anything reaches production.

The risk is that Prisma looks simple enough to automate blindly. A thin article or a vague prompt often produces missing indexes, oversized include trees, unsafe cascade deletes, or migrations that look fine locally but fail against real data. This guide turns the Prisma workflow into something you can review and monetize around: a practical, repeatable database layer for content sites, SaaS dashboards, and internal tools.

For the official baseline, keep the Prisma ORM docs, Prisma Schema docs, transaction docs, and Prisma Migrate docs open while reviewing Claude Code output. For adjacent ClaudeCodeLab reading, see the Claude Code getting started guide, Drizzle ORM guide, Supabase integration guide, and Redis caching guide.

Workflow

flowchart LR
  A["Prompt Claude Code with product rules"] --> B["Design schema.prisma"]
  B --> C["Generate and review migration.sql"]
  C --> D["Implement Prisma Client queries"]
  D --> E["Add seed and tests"]
  E --> F["Run production review checklist"]

Treat Prisma as three reviewable artifacts: schema, SQL migration, and TypeScript query code. Claude Code can draft all three, but humans should still inspect the constraints, indexes, and destructive SQL.

A Strong Claude Code Prompt

Design a Prisma ORM data layer for a blog API.

Context:
- TypeScript + Prisma ORM + SQLite locally, with a later PostgreSQL move
- Models: User, Post, Category, Comment, Notification, AuditLog
- Post has a status string: DRAFT, PUBLISHED, ARCHIVED
- email and slug must be unique
- Public listing filters by status, publishedAt, author, and category
- Deleting a Post should cascade comments and join rows
- Deleting a User should be restricted until we design anonymization

Return:
1. prisma/schema.prisma
2. migration SQL review points
3. Prisma Client create/list/publish functions
4. seed and test commands
5. production review checklist

The important part is not the number of models. It is the operational intent: what must be unique, what must not be deleted, which list screens need indexes, and which operations must be atomic.

Runnable Setup

This local setup uses SQLite so you can test the article quickly. The official Prisma docs now emphasize prisma.config.ts for configuration, so the example keeps the database URL there instead of hard-coding it in the schema.

{
  "type": "module",
  "scripts": {
    "db:generate": "prisma generate",
    "db:migrate": "prisma migrate dev",
    "db:deploy": "prisma migrate deploy",
    "db:seed": "prisma db seed",
    "dev": "tsx src/demo.ts",
    "test": "vitest run"
  },
  "dependencies": {
    "@prisma/client": "latest",
    "dotenv": "latest"
  },
  "devDependencies": {
    "prisma": "latest",
    "tsx": "latest",
    "typescript": "latest",
    "vitest": "latest"
  }
}

npm install
echo 'DATABASE_URL="file:./dev.db"' > .env
mkdir prisma src

// prisma.config.ts
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
    seed: "tsx prisma/seed.ts",
  },
  datasource: {
    url: env("DATABASE_URL"),
  },
});

Schema Design

// prisma/schema.prisma
generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

datasource db {
  provider = "sqlite"
}

model User {
  id            String         @id @default(cuid())
  email         String         @unique
  name          String
  role          String         @default("editor")
  posts         Post[]
  comments      Comment[]
  notifications Notification[]
  createdAt     DateTime       @default(now())
  updatedAt     DateTime       @updatedAt

  @@index([role])
}

model Post {
  id          String              @id @default(cuid())
  slug        String              @unique
  title       String
  body        String
  status      String              @default("DRAFT")
  authorId    String
  author      User                @relation(fields: [authorId], references: [id], onDelete: Restrict)
  categories  CategoriesOnPosts[]
  comments    Comment[]
  publishedAt DateTime?
  createdAt   DateTime            @default(now())
  updatedAt   DateTime            @updatedAt

  @@index([authorId])
  @@index([status, publishedAt])
}

model Category {
  id    String              @id @default(cuid())
  slug  String              @unique
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  postId     String
  categoryId String
  assignedAt DateTime @default(now())
  post       Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  category   Category @relation(fields: [categoryId], references: [id], onDelete: Cascade)

  @@id([postId, categoryId])
}

model Comment {
  id        String   @id @default(cuid())
  body      String
  postId    String
  authorId  String
  post      Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  author    User     @relation(fields: [authorId], references: [id], onDelete: Restrict)
  createdAt DateTime @default(now())

  @@index([postId, createdAt])
  @@index([authorId])
}

model Notification {
  id        String    @id @default(cuid())
  userId    String
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  type      String
  message   String
  readAt    DateTime?
  createdAt DateTime  @default(now())

  @@index([userId, readAt])
}

model AuditLog {
  id        String   @id @default(cuid())
  action    String
  targetId  String
  metadata  String?
  createdAt DateTime @default(now())

  @@index([action, createdAt])
}

Ask Claude Code to justify each index against a real screen. @@index([status, publishedAt]) supports the public article list; @@index([postId, createdAt]) supports comment display. Indexes without a query behind them are maintenance cost.

Migration Review

npx prisma format
npx prisma migrate dev --create-only --name init_blog
# Review prisma/migrations/*/migration.sql in the pull request.
npx prisma migrate dev
npx prisma generate

Use migrate deploy in production:

npx prisma migrate deploy

Do not treat db push as a production migration workflow. It is useful for prototypes, but it does not create a reviewed SQL history. On a real site, review DROP, NOT NULL, unique constraints on existing columns, and cascade rules before deploying.

Rollback guidance belongs in the PR before deployment. If a migration already succeeded, prefer a new forward migration that reverses the schema change. For a failed migration, follow Prisma’s down migration workflow: verify a backup, run migrate status, apply a reviewed down SQL if needed, then mark only the failed migration as rolled back with migrate resolve.

npx prisma migrate status
npx prisma migrate resolve --rolled-back "20260603090000_failed_change"

Prisma Client and Transactions

// src/db.ts
import { PrismaClient } from "./generated/prisma/client";

const globalForPrisma = globalThis as unknown as { prisma?: PrismaClient };

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: ["warn", "error"],
  });

if (process.env.NODE_ENV !== "production") {
  globalForPrisma.prisma = prisma;
}

// src/posts.ts
import { Prisma } from "./generated/prisma/client";
import { prisma } from "./db";

export async function listPublishedPosts(params: {
  page?: number;
  perPage?: number;
  categorySlug?: string;
  search?: string;
}) {
  const page = Math.max(params.page ?? 1, 1);
  const perPage = Math.min(Math.max(params.perPage ?? 20, 1), 50);

  const where: Prisma.PostWhereInput = {
    status: "PUBLISHED",
    ...(params.categorySlug
      ? { categories: { some: { category: { slug: params.categorySlug } } } }
      : {}),
    ...(params.search
      ? { OR: [{ title: { contains: params.search } }, { body: { contains: params.search } }] }
      : {}),
  };

  const [items, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      skip: (page - 1) * perPage,
      take: perPage,
      orderBy: [{ publishedAt: "desc" }, { createdAt: "desc" }],
      select: {
        id: true,
        slug: true,
        title: true,
        publishedAt: true,
        author: { select: { name: true } },
        _count: { select: { comments: true } },
      },
    }),
    prisma.post.count({ where }),
  ]);

  return { items, pagination: { page, perPage, total } };
}

export async function publishPost(postId: string) {
  return prisma.$transaction(async (tx) => {
    const post = await tx.post.findUnique({
      where: { id: postId },
      select: { id: true, title: true, status: true, authorId: true },
    });

    if (!post) throw new Error("Post not found");
    if (post.status === "PUBLISHED") return post;

    const published = await tx.post.update({
      where: { id: post.id },
      data: { status: "PUBLISHED", publishedAt: new Date() },
      select: { id: true, title: true, status: true, publishedAt: true },
    });

    await tx.notification.create({
      data: {
        userId: post.authorId,
        type: "POST_PUBLISHED",
        message: `${post.title} was published`,
      },
    });

    await tx.auditLog.create({
      data: {
        action: "POST_PUBLISHED",
        targetId: post.id,
        metadata: JSON.stringify({ title: post.title }),
      },
    });

    return published;
  });
}

Use $transaction([]) for independent reads such as list plus count. Use an interactive transaction when one business operation must update several tables together. Keep the transaction short: no email sending, no webhook calls, and no slow network work inside the callback.

Safe SQL

Use Prisma Client queries first. When a reporting query really needs SQL, the pitfall is string concatenation. Prisma’s raw query docs recommend tagged templates or Prisma.sql so values are parameterized.

import { Prisma } from "./generated/prisma/client";
import { prisma } from "./db";

export async function topAuthors(limit = 10) {
  return prisma.$queryRaw<
    { authorId: string; postCount: bigint }[]
  >(Prisma.sql`
    SELECT "authorId", COUNT(*) AS "postCount"
    FROM "Post"
    WHERE "status" = ${"PUBLISHED"}
    GROUP BY "authorId"
    ORDER BY "postCount" DESC
    LIMIT ${Math.min(limit, 50)}
  `);
}

Ask Claude Code to justify every $queryRawUnsafe use. If the reason is only dynamic table or column names, replace that with a human-reviewed allowlist.

Seed, Test, and Review

// prisma/seed.ts
import { PrismaClient } from "../src/generated/prisma/client";

const prisma = new PrismaClient();

async function main() {
  const user = await prisma.user.upsert({
    where: { email: "editor@example.com" },
    update: { name: "Editor" },
    create: { email: "editor@example.com", name: "Editor", role: "admin" },
  });

  const post = await prisma.post.upsert({
    where: { slug: "claude-code-prisma-demo" },
    update: { status: "PUBLISHED", publishedAt: new Date() },
    create: {
      slug: "claude-code-prisma-demo",
      title: "Claude Code Prisma demo",
      body: "Seeded article for local verification.",
      status: "PUBLISHED",
      publishedAt: new Date(),
      authorId: user.id,
    },
  });

  console.log({ user: user.email, post: post.slug });
}

main().finally(async () => prisma.$disconnect());

npm run db:migrate -- --name init_blog
npm run db:generate
npm run db:seed
npm run test

Review checklist:

• Unique constraints, indexes, relations, and delete rules match the product behavior.
• Generated SQL has no accidental destructive change.
• List queries cap take, use stable orderBy, and return only needed fields.
• Transactions do not include external API calls.
• Seed scripts are idempotent.
• Tests cover rollback, duplicate webhook handling, or repeated publish attempts.
• Production deploys run prisma migrate deploy, not db push.

Use Cases and Pitfalls

Prisma ORM is a strong fit for content platforms, SaaS admin panels, and workflow tools where TypeScript models and database constraints should move together. For a content site, focus on slug uniqueness, publication status, category filters, and count queries. For a multi-tenant SaaS app, make Claude Code prove every query scopes by tenant. For payment or entitlement flows, require transaction tests and audit logs.

The common pitfalls are predictable: accepting generated migrations without reading SQL, returning full relation trees with include: true, putting slow side effects inside transactions, and letting users request unlimited pages. Masa’s own small blog API test caught two issues early by making Claude Code explain migration risk before writing application code: cascade delete was too broad, and the public list query returned more author data than the page needed.

For self-study, start with the ClaudeCodeLab /products/ materials. For teams that need a shared database review workflow, see /training/. Bring an existing Prisma schema, migration history, or AI-generated PR, and the review can start from concrete code rather than abstract advice.