Database Migrations

PC Fix uses Prisma as its ORM and Prisma Migrate for managing database schema changes. This guide covers creating, applying, and managing migrations throughout the development lifecycle.

Overview

Prisma Migrate enables version-controlled, type-safe database schema management. All schema changes are defined in packages/api/prisma/schema.prisma and tracked as migration files.

Migrations are stored in packages/api/prisma/migrations/ and should be committed to version control.

Prisma Schema

The schema defines your database structure using Prisma’s DSL:

schema.prisma

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "debian-openssl-3.0.x"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  nombre    String
  apellido  String
  password  String?
  googleId  String?  @unique
  role      Role     @default(USER)
  createdAt DateTime @default(now()) @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @db.Timestamptz(3)
  
  // Relations
  cliente   Cliente?
  favoritos Favorite[]
  cart      Cart?
}

enum Role {
  USER
  ADMIN
}

The full schema is located at packages/api/prisma/schema.prisma and contains 20+ models including User, Producto, Venta, Cart, and more.

Common Migration Workflows

Creating a New Migration

When you modify schema.prisma, create a migration:

Edit Schema

Modify packages/api/prisma/schema.prisma:

model Producto {
  id          Int      @id @default(autoincrement())
  nombre      String
  descripcion String?
  precio      Float
  // Add new field
  descuento   Float?   @default(0)
}

Create Migration

Generate migration files:

cd packages/api
npx prisma migrate dev --name add-product-discount

This will:

Generate SQL migration file
Apply migration to database
Regenerate Prisma Client with new types

Review Migration

Check the generated SQL in prisma/migrations/TIMESTAMP_add-product-discount/migration.sql:

ALTER TABLE "Producto" ADD COLUMN "descuento" DOUBLE PRECISION DEFAULT 0;

Commit Changes

git add prisma/schema.prisma prisma/migrations/
git commit -m "Add discount field to products"

Applying Existing Migrations

When pulling changes from git or deploying:

Development
Production
Docker

cd packages/api
npx prisma migrate dev

Applies pending migrations and regenerates Prisma Client.

cd packages/api
npx prisma migrate deploy

Only applies migrations without prompts. Safe for CI/CD and production.

docker exec -it pcfix-api npx prisma migrate deploy

Apply migrations inside running container.

Resetting the Database

This will delete all data! Only use in development.

cd packages/api
npx prisma migrate reset

This will:

Drop the database
Create a new database
Apply all migrations
Run seed script (if configured)

Prisma Client Generation

After schema changes, regenerate the Prisma Client:

cd packages/api
npx prisma generate

This updates TypeScript types to match your schema. Run after pulling schema changes from git.

Database Seeding

PC Fix includes a seed script for populating test data:

prisma/seed.ts

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function main() {
  // Create admin user
  await prisma.user.create({
    data: {
      email: '[email protected]',
      nombre: 'Admin',
      apellido: 'User',
      password: await bcrypt.hash('admin123', 10),
      role: 'ADMIN',
    },
  });
  
  // Create sample products
  await prisma.producto.createMany({
    data: [
      {
        nombre: 'RTX 4090',
        descripcion: 'NVIDIA GeForce RTX 4090',
        precio: 2499.99,
        stock: 10,
      },
      // ... more products
    ],
  });
}

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

Run the seed:

cd packages/api
npx prisma db seed

Seeding is configured in packages/api/package.json:

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

Prisma Studio

Explore and edit database records with Prisma Studio:

cd packages/api
npx prisma studio

Access at http://localhost:5555

For Docker: docker exec -it pcfix-api npx prisma studio

Common Prisma Commands

# Create migration from schema changes
npx prisma migrate dev --name migration-name

# Apply migrations and regenerate client
npx prisma migrate dev

# Reset database (deletes all data)
npx prisma migrate reset

# Generate Prisma Client
npx prisma generate

# Open Prisma Studio
npx prisma studio

Migration Best Practices

Test Locally First

Always test migrations on local database before deploying:

npx prisma migrate dev
npm run test

Use Descriptive Names

Name migrations clearly:

# Good
npx prisma migrate dev --name add-user-phone-number
npx prisma migrate dev --name create-order-status-enum

# Bad
npx prisma migrate dev --name update
npx prisma migrate dev --name fix

Review Generated SQL

Check migration files before committing:

cat prisma/migrations/TIMESTAMP_migration-name/migration.sql

Backup Production Data

Before running migrations in production:

pg_dump pcfix_production > backup.sql

Handling Complex Migrations

Data Migrations

For migrations that require data transformation, edit the generated SQL:

migration.sql

-- Step 1: Add new column (nullable)
ALTER TABLE "User" ADD COLUMN "fullName" TEXT;

-- Step 2: Populate new column from existing data
UPDATE "User" SET "fullName" = CONCAT("nombre", ' ', "apellido");

-- Step 3: Make column required
ALTER TABLE "User" ALTER COLUMN "fullName" SET NOT NULL;

-- Step 4: Drop old columns (optional)
ALTER TABLE "User" DROP COLUMN "nombre";
ALTER TABLE "User" DROP COLUMN "apellido";

Breaking Changes

For breaking schema changes:

Deploy in stages:
- Add new fields (optional)
- Update application code to use new fields
- Make fields required
- Remove old fields
Use database transactions:
```
BEGIN;
-- Your migration SQL
COMMIT;
```

Plan rollback strategy:

-- rollback.sql
ALTER TABLE "Producto" DROP COLUMN "descuento";

Troubleshooting

Migration failed to apply

If a migration fails:

# Check migration status
npx prisma migrate status

# Mark migration as applied (if manually fixed)
npx prisma migrate resolve --applied 20240305_migration_name

# Or mark as rolled back
npx prisma migrate resolve --rolled-back 20240305_migration_name

Schema and database out of sync

# Pull current database schema
npx prisma db pull

# Or push schema to database (development only)
npx prisma db push

Prisma Client outdated

# Regenerate client
npx prisma generate

# Clear node_modules and reinstall
rm -rf node_modules
npm install

Migration conflicts after git merge

# Reset to a clean state
npx prisma migrate reset

# Reapply all migrations
npx prisma migrate dev

CI/CD Integration

Add migration checks to your CI pipeline:

.github/workflows/ci.yml

name: CI

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v3
      - name: Setup Node
        uses: actions/setup-node@v3
        with:
          node-version: 20
      - name: Install dependencies
        run: npm install
        working-directory: packages/api
      - name: Run migrations
        run: npx prisma migrate deploy
        working-directory: packages/api
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test_db
      - name: Run tests
        run: npm test
        working-directory: packages/api

Next Steps

Database Schema

Explore the complete database schema

Backend Architecture

Learn about the API structure

Environment Setup

Configure database connection

Testing

Test database operations

Get Started

Architecture

Features

Guides

Database Migrations

Overview

Prisma Schema

Common Migration Workflows

Creating a New Migration

Applying Existing Migrations

Resetting the Database

Prisma Client Generation

Database Seeding

Prisma Studio

Common Prisma Commands

Migration Best Practices

Handling Complex Migrations

Data Migrations

Breaking Changes

Troubleshooting

CI/CD Integration

Next Steps

Database Schema

Backend Architecture

Environment Setup

Testing

Build docs developers (and LLMs) love

Get Started

Architecture

Features

Guides

​Overview

​Prisma Schema

​Common Migration Workflows

​Creating a New Migration

​Applying Existing Migrations

​Resetting the Database

​Prisma Client Generation

​Database Seeding

​Prisma Studio

​Common Prisma Commands

​Migration Best Practices

​Handling Complex Migrations

​Data Migrations

​Breaking Changes

​Troubleshooting

​CI/CD Integration

​Next Steps

Database Schema

Backend Architecture

Environment Setup

Testing

Build docs developers (and LLMs) love

Overview

Prisma Schema

Common Migration Workflows

Creating a New Migration

Applying Existing Migrations

Resetting the Database

Prisma Client Generation

Database Seeding

Prisma Studio

Common Prisma Commands

Migration Best Practices

Handling Complex Migrations

Data Migrations

Breaking Changes

Troubleshooting

CI/CD Integration

Next Steps