---
name: prisma-migrations
description: Best practices for Prisma schema changes, migrations, seeding, and rollback in a NestJS + TypeScript backend
---

# Prisma Migrations Skill

## Scope

Apply this skill for any task involving database schema changes, migration creation, seed data management, or data transformations. This skill assumes **Prisma ORM** with **PostgreSQL** in a **NestJS + TypeScript** project.

---

## Schema as Source of Truth

The file `prisma/schema.prisma` is the single source of truth for all database models, relations, enums, and indexes. Never modify the database directly — all changes flow through the schema file and Prisma's migration tooling.

---

## Migration Workflow

### When to Use `prisma migrate dev`

Use `prisma migrate dev` for all schema changes that will reach production. This generates a SQL migration file, applies it to the dev database, and regenerates the Prisma Client.

```bash
# 1. Edit prisma/schema.prisma
# 2. Generate and apply migration
npx prisma migrate dev --name <descriptive-name>
```

### When to Use `prisma db push`

Use `prisma db push` **only** during early prototyping or when iterating on a schema that has not been committed yet. It applies schema changes without creating a migration file — meaning the change is not reproducible.

**Never use `db push` on a schema that has existing migrations in version control.** It will drift the database from the migration history.

### Migration Naming

Migration names must be descriptive, lowercase, and use hyphens:

| Change | Migration Name |
|---|---|
| Add a new model | `add-audit-log-table` |
| Add a field to existing model | `add-last-login-to-user` |
| Add an index | `add-index-on-user-email` |
| Remove a field | `remove-deprecated-role-field` |
| Add an enum | `add-permission-action-enum` |
| Rename a field | `rename-name-to-full-name-on-user` |

### Migration File Review

After generating a migration, **always read the generated SQL file** in `prisma/migrations/<timestamp>_<name>/migration.sql` before committing. Verify:

- [ ] The SQL matches your intent — no accidental drops or renames
- [ ] Indexes are created where expected
- [ ] Default values are applied correctly
- [ ] Foreign key constraints and cascades are correct
- [ ] No data loss — columns are not dropped that contain data

---

## Destructive Changes

Destructive changes require extra care because they are irreversible once applied to production.

### Dropping a Column

1. First deploy: remove all code references to the column, but keep the column in the schema
2. Second deploy: remove the column from the schema and generate the migration
3. This two-phase approach prevents runtime errors from code that still references the column

### Renaming a Column

Prisma treats a rename as a drop + create. To preserve data:

1. Add the new column with a migration
2. Write a data migration to copy values from old to new column
3. Update all code references to use the new column
4. Drop the old column in a subsequent migration

### Dropping a Table

Verify that no code references the table before generating the migration. Check:
- Repositories, services, resolvers that query the table
- Seed files that insert into the table
- Test factories that reference the model

---

## Data Migrations

Schema migrations (DDL) and data migrations (DML) should be separate:

- **Schema migration**: generated by Prisma, modifies table structure
- **Data migration**: a TypeScript script in `prisma/data-migrations/` that transforms existing data

```typescript
// prisma/data-migrations/001-backfill-user-display-name.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  const users = await prisma.user.findMany({
    where: { displayName: null },
  });

  for (const user of users) {
    await prisma.user.update({
      where: { id: user.id },
      data: { displayName: `${user.firstName} ${user.lastName}` },
    });
  }

  console.log(`Backfilled displayName for ${users.length} users`);
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());
```

Run data migrations manually after the schema migration is applied. Never embed data transformations inside Prisma's generated SQL migration files.

---

## Seeding

### Seed File Location

The seed script lives at `prisma/seed.ts` and is configured in `package.json`:

```json
{
  "prisma": {
    "seed": "ts-node prisma/seed.ts"
  }
}
```

### Seed Design Rules

- Seeds must be **idempotent** — running them multiple times produces the same result
- Use `upsert` instead of `create` to avoid duplicate key errors on re-runs
- Seeds for different concerns (roles, permissions, test data) should be organized into separate functions
- Never seed production-only data in development — use environment checks
- Seed data must match the current schema — update seeds when the schema changes

```typescript
async function seedRoles(prisma: PrismaClient) {
  const roles = [
    { name: 'admin', description: 'Full system access' },
    { name: 'user', description: 'Standard user access' },
  ];

  for (const role of roles) {
    await prisma.role.upsert({
      where: { name: role.name },
      update: { description: role.description },
      create: role,
    });
  }
}
```

### Running Seeds

```bash
# Run seed script
npx prisma db seed

# Reset database and re-seed (development only)
npx prisma migrate reset
```

---

## Testing Database

### Test Database Reset

For integration tests, reset the database to a known state before each test suite:

```typescript
// test/setup-e2e.ts
beforeAll(async () => {
  // Apply all migrations to test database
  execSync('npx prisma migrate deploy', {
    env: { ...process.env, DATABASE_URL: process.env.TEST_DATABASE_URL },
  });
});

afterEach(async () => {
  // Clean up test data — use transactions for speed
  await prisma.$transaction([
    prisma.userRole.deleteMany(),
    prisma.user.deleteMany(),
    prisma.role.deleteMany(),
  ]);
});
```

### Test Database Isolation

- Each test suite uses a dedicated test database or a transaction that is rolled back
- Test database URL is configured via `DATABASE_URL` in `.env.test`
- Never run tests against the development database — data will be destroyed

---

## Rollback Procedures

### Development Rollback

```bash
# Roll back the last migration (marks it as rolled back, does not delete the file)
npx prisma migrate resolve --rolled-back <migration-name>

# Then apply a corrective migration
npx prisma migrate dev --name fix-<description>
```

### Production Rollback

Prisma does not support automatic rollback. For production:

1. Write a new migration that reverses the changes
2. Test the reverse migration against a staging database
3. Apply the reverse migration to production

**Never delete migration files from the `prisma/migrations/` directory** — this breaks the migration history and makes `prisma migrate deploy` fail.

---

## Hard Rules

- Never use `prisma db push` on a branch that has committed migrations
- Never edit a generated migration SQL file after it has been applied to any environment
- Never delete migration files — add new migrations to fix mistakes
- Always read the generated SQL before committing a migration
- Always update seed files when the schema changes
- Always use two-phase deploys for destructive column/table changes in production
- Never run `prisma migrate reset` in production — it drops and recreates the entire database