Database Migrations - E-commerce API

Overview

The E-commerce API uses Prisma as its ORM and migration tool. This guide covers the complete workflow for managing database schema changes.

Prisma Schema

The database schema is defined in backend/prisma/schema.prisma:1-109:

generator client {
  provider = "prisma-client-js"
}

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

enum Role {
  customer
  admin
}

enum OrderStatus {
  pending
  shipped
  delivered
  cancelled
}

model User {
  id           Int      @id @default(autoincrement())
  name         String
  email        String   @unique
  passwordHash String
  role         Role     @default(customer)
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  cart         Cart?
  orders       Order[]
}

model Category {
  id          Int       @id @default(autoincrement())
  name        String
  description String?
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  products    Product[]
}

model Product {
  id          Int         @id @default(autoincrement())
  name        String
  description String?
  price       Decimal     @db.Decimal(10, 2)
  imageUrl    String
  stock       Int
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt
  categoryId  Int?
  cartItems   CartItem[]
  orderItems  OrderItem[]
  category    Category?   @relation(fields: [categoryId], references: [id])

  @@index([categoryId])
  @@index([name])
}

model Cart {
  id        Int        @id @default(autoincrement())
  userId    Int        @unique
  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt
  user      User       @relation(fields: [userId], references: [id])
  items     CartItem[]
}

model CartItem {
  id        Int     @id @default(autoincrement())
  cartId    Int
  productId Int
  quantity  Int
  cart      Cart    @relation(fields: [cartId], references: [id], onDelete: Cascade)
  product   Product @relation(fields: [productId], references: [id], onDelete: Cascade)

  @@unique([cartId, productId])
  @@index([cartId])
  @@index([productId])
}

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

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

model OrderItem {
  id              Int     @id @default(autoincrement())
  orderId         Int
  productId       Int
  quantity        Int
  priceAtPurchase Decimal @db.Decimal(10, 2)
  order           Order   @relation(fields: [orderId], references: [id])
  product         Product @relation(fields: [productId], references: [id], onDelete: Restrict)

  @@index([orderId])
  @@index([productId])
}

Key Schema Features

Enums

Role: customer (default) | admin
OrderStatus: pending (default) | shipped | delivered | cancelled

Relationships

User has one Cart and many Orders
Category has many Products
Product belongs to one Category (optional)
Cart has many CartItems
Order has many OrderItems

Indexes

Optimized queries with indexes on:

Product.categoryId and Product.name
Order.userId and Order.status
CartItem.cartId and CartItem.productId
OrderItem.orderId and OrderItem.productId

Development Workflow

Modify the schema

Edit backend/prisma/schema.prisma with your changes:

model Product {
  // Add a new field
  featured Boolean @default(false)
  // ... rest of model
}

Create migration

Generate a migration file:

npm run prisma:migrate
# Or directly:
npx prisma migrate dev --name add_featured_field

This command:

Creates SQL migration file in prisma/migrations/
Applies the migration to your database
Regenerates Prisma Client

Review migration SQL

Check the generated SQL in prisma/migrations/[timestamp]_add_featured_field/migration.sql:

ALTER TABLE `Product` ADD COLUMN `featured` BOOLEAN NOT NULL DEFAULT false;

Test the changes

Verify the migration worked:

npx prisma studio

Or test in code:

const product = await prisma.product.create({
  data: {
    name: "Test Product",
    featured: true,  // New field
    // ... other fields
  }
});

Available Scripts

From backend/package.json:6-13:

{
  "scripts": {
    "prisma:migrate": "prisma migrate dev",
    "prisma:generate": "prisma generate",
    "prisma:seed": "tsx prisma/seed.ts"
  }
}

prisma:migrate

npm run prisma:migrate

Development only: Creates and applies migrations. Use --name flag to describe the change:

npx prisma migrate dev --name add_product_ratings

prisma:generate

npm run prisma:generate

Regenerates Prisma Client without creating migrations. Use after pulling schema changes.

prisma:seed

npm run prisma:seed

Populates the database with initial data using prisma/seed.ts.

Production Deployment

Never use prisma migrate dev in production. It can result in data loss.

Deploy Migrations

Use migrate deploy to apply pending migrations:

npx prisma migrate deploy

This command:

Applies all pending migrations
Does NOT create new migrations
Does NOT prompt for user input
Safe for CI/CD pipelines

Deployment Steps

Commit migrations

Ensure all migration files are committed to version control:

git add prisma/migrations
git commit -m "Add product featured field migration"

Deploy application

Build and deploy your Docker container or code

Apply migrations

Run migrations before starting the application:

# In Docker:
docker-compose exec backend npx prisma migrate deploy

# Or in Dockerfile CMD:
CMD ["sh", "-c", "npx prisma migrate deploy && node dist/server.js"]

Migration Commands Reference

Create migration (development)

npx prisma migrate dev --name description_of_change

Apply migrations (production)

npx prisma migrate deploy

Check migration status

npx prisma migrate status

Reset database (development only)

npx prisma migrate reset

migrate reset drops the database, recreates it, and applies all migrations. All data will be lost.

Resolve migration issues

npx prisma migrate resolve --applied "migration_name"
npx prisma migrate resolve --rolled-back "migration_name"

Database Seeding

The seed script is configured in package.json:15-17:

{
  "prisma": {
    "seed": "tsx prisma/seed.ts"
  }
}

Run seeding:

# Standalone
npm run prisma:seed

# After reset (automatic)
npx prisma migrate reset

Prisma Client Generation

The Prisma Client is generated from the schema and must be regenerated when:

Schema changes
After pulling changes from Git
During Docker builds

Development

npm run prisma:generate

Docker Build

Included in the Dockerfile (backend/dockerfile:13):

RUN npx prisma generate

The generated client is copied to the final image (backend/dockerfile:26):

COPY --from=builder /app/node_modules/.prisma ./node_modules/.prisma

Common Workflows

Adding a new field

Update schema

model Product {
  slug String @unique
  // ... rest of model
}

Create migration

npx prisma migrate dev --name add_product_slug

Update application code

const product = await prisma.product.create({
  data: {
    slug: "product-slug",
    // ... other fields
  }
});

Adding a new model

Define model in schema

model Review {
  id        Int      @id @default(autoincrement())
  productId Int
  userId    Int
  rating    Int
  comment   String?
  createdAt DateTime @default(now())
  product   Product  @relation(fields: [productId], references: [id])
  user      User     @relation(fields: [userId], references: [id])
  
  @@index([productId])
  @@index([userId])
}

Add relations to existing models

model Product {
  reviews Review[]
  // ... rest of model
}

model User {
  reviews Review[]
  // ... rest of model
}

Create migration

npx prisma migrate dev --name add_reviews

Renaming a field (with data preservation)

Simply renaming in schema drops and recreates the column, losing data.

Create empty migration

npx prisma migrate dev --create-only --name rename_field

Edit migration SQL

Replace the generated SQL with:

ALTER TABLE `Product` RENAME COLUMN `oldName` TO `newName`;

Apply migration

npx prisma migrate dev

Troubleshooting

Migration out of sync

Symptom: “Your database schema is not in sync with your migration history” Solution:

# Development: Reset database
npx prisma migrate reset

# Production: Mark migration as applied
npx prisma migrate resolve --applied "migration_name"

Prisma Client not found

Symptom: Cannot find module '@prisma/client' Solution:

npm run prisma:generate

Database connection errors

Symptom: P1001: Can't reach database server Solution: Verify DATABASE_URL in .env:

DATABASE_URL="mysql://user:password@host:3306/database"

Getting Started

Core Concepts

Guides

Security

​Overview

​Prisma Schema

​Key Schema Features

​Enums

​Relationships

​Indexes

​Development Workflow

​Available Scripts

​prisma:migrate

​prisma:generate

​prisma:seed

​Production Deployment

​Deploy Migrations

​Deployment Steps

​Migration Commands Reference

​Create migration (development)

​Apply migrations (production)

​Check migration status

​Reset database (development only)

​Resolve migration issues

​Database Seeding

​Prisma Client Generation

​Development

​Docker Build

​Common Workflows

​Adding a new field

​Adding a new model

​Renaming a field (with data preservation)

​Troubleshooting

​Migration out of sync

​Prisma Client not found

​Database connection errors

​Next Steps

Environment Setup

Docker Deployment

Build docs developers (and LLMs) love

Overview

Prisma Schema

Key Schema Features

Enums

Relationships

Indexes

Development Workflow

Available Scripts

prisma:migrate

prisma:generate

prisma:seed

Production Deployment

Deploy Migrations

Deployment Steps

Migration Commands Reference

Create migration (development)

Apply migrations (production)

Check migration status

Reset database (development only)

Resolve migration issues

Database Seeding

Prisma Client Generation

Development

Docker Build

Common Workflows

Adding a new field

Adding a new model

Renaming a field (with data preservation)

Troubleshooting

Migration out of sync

Prisma Client not found

Database connection errors

Next Steps