How to Automate DB Migrations with Claude Code

Solve DB Migration Headaches With Claude Code

Hand-writing migration files every time you change the schema is tedious and error-prone. With Claude Code, you can handle everything from schema design changes and migration generation to updating seed data in one go.

Migrations With Prisma

From Schema Design to Migration Execution

> Add the following table to the Prisma schema, then generate and apply the migration.
>
> Table: Order
> - id: UUID, primary key
> - userId: foreign key to User
> - items: OrderItem[] (one to many)
> - totalAmount: Decimal
> - status: enum (pending, confirmed, shipped, delivered, cancelled)
> - createdAt, updatedAt

An example of the schema Claude Code generates:

enum OrderStatus {
  pending
  confirmed
  shipped
  delivered
  cancelled
}

model Order {
  id          String      @id @default(uuid())
  userId      String
  user        User        @relation(fields: [userId], references: [id])
  items       OrderItem[]
  totalAmount Decimal     @db.Decimal(10, 2)
  status      OrderStatus @default(pending)
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt

  @@index([userId])
  @@index([status])
}

model OrderItem {
  id        String @id @default(uuid())
  orderId   String
  order     Order  @relation(fields: [orderId], references: [id], onDelete: Cascade)
  productId String
  quantity  Int
  price     Decimal @db.Decimal(10, 2)
}

After that, Claude Code automatically runs the migration command.

npx prisma migrate dev --name add_order_tables

Migrations With Drizzle

You can give similar instructions when using Drizzle ORM.

> Add the Order table to the Drizzle ORM schema.
> Also generate the migration file.

import { pgTable, uuid, decimal, timestamp, pgEnum } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { users } from "./users";

export const orderStatusEnum = pgEnum("order_status", [
  "pending", "confirmed", "shipped", "delivered", "cancelled",
]);

export const orders = pgTable("orders", {
  id: uuid("id").defaultRandom().primaryKey(),
  userId: uuid("user_id").notNull().references(() => users.id),
  totalAmount: decimal("total_amount", { precision: 10, scale: 2 }).notNull(),
  status: orderStatusEnum("status").default("pending").notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const ordersRelations = relations(orders, ({ one, many }) => ({
  user: one(users, { fields: [orders.userId], references: [users.id] }),
  items: many(orderItems),
}));

Changing Existing Schemas

You can also safely handle changes to existing tables, such as adding or renaming columns.

> Make the following changes to the users table:
> - Add a phoneNumber column (nullable)
> - Split name into firstName and lastName
> - Include migration logic for existing data

Claude Code will generate a migration that includes a data migration script.

-- Migration: split_user_name
ALTER TABLE "users" ADD COLUMN "first_name" VARCHAR(100);
ALTER TABLE "users" ADD COLUMN "last_name" VARCHAR(100);
ALTER TABLE "users" ADD COLUMN "phone_number" VARCHAR(20);

-- Migrate existing data
UPDATE "users"
SET
  "first_name" = split_part("name", ' ', 1),
  "last_name" = CASE
    WHEN array_length(string_to_array("name", ' '), 1) > 1
    THEN split_part("name", ' ', 2)
    ELSE ''
  END;

ALTER TABLE "users" ALTER COLUMN "first_name" SET NOT NULL;
ALTER TABLE "users" ALTER COLUMN "last_name" SET NOT NULL;
ALTER TABLE "users" DROP COLUMN "name";

Generating Seed Data

You can also generate test data for development.

> Create 20 seed records for the Order table.
> Distribute the statuses evenly.
> Use realistic dummy data.

Verifying Migration Safety

> Check the current migration history and
> verify there are no issues before applying to production.
> Flag any changes with data loss risk.

For DB integration patterns in API development, see how to speed up API development. You can also improve safety by documenting database-related rules in CLAUDE.md. For how to write one, see the complete CLAUDE.md guide.

Summary

With Claude Code, you can automate the entire flow from DB schema design through changes, migration generation, and seed data creation. It especially shines on complex migrations that include data transformation logic.

For details on using Prisma, see the official Prisma documentation. For Claude Code features, see the official Anthropic documentation.