Database Migrations - Juadah API

Overview

The Juadah API uses Prisma as its ORM, which provides a robust migration system to manage database schema changes. Migrations are version-controlled SQL files that track all changes to your database schema.

Prerequisites

Before running migrations, ensure you have:

PostgreSQL installed and running
Node.js v20 (as specified in the project README)
Environment variables configured

Environment Setup

1. Configure Environment Variables

Copy the .env.example file to .env and configure your database connection:

cp .env.example .env

2. Database Connection URLs

Add the following PostgreSQL connection strings to your .env file:

DATABASE_URL="postgresql://user:password@localhost:5432/juadah?schema=public"
DIRECT_URL="postgresql://user:password@localhost:5432/juadah?schema=public"

DATABASE_URL is used for connection pooling, while DIRECT_URL is used for direct connections during migrations.

Connection String Format

postgresql://[user]:[password]@[host]:[port]/[database]?schema=public

user

string

required

PostgreSQL username

password

string

required

PostgreSQL password

host

string

required

Database host (e.g., localhost or remote host)

port

string

default:"5432"

PostgreSQL port number

database

string

required

Database name (e.g., juadah)

Prisma Commands

The project includes several npm scripts for managing Prisma and migrations:

Generate Prisma Client

Generate the Prisma Client after schema changes:

npm run postinstall

Or manually:

prisma generate

The Prisma Client is automatically generated after running npm install via the postinstall script.

Build the Project

Build includes Prisma Client generation and TypeScript compilation:

npm run build

This runs:

prisma generate - Generates Prisma Client
tsc - Compiles TypeScript to JavaScript

Migration Workflow

Understanding Existing Migrations

The project has existing migrations in prisma/migrations/:

prisma/migrations/
├── 20241016022512_init/
│   └── migration.sql
├── 20241115044312_init/
│   └── migration.sql
└── migration_lock.toml

View Initial Migration SQL

-- CreateEnum
CREATE TYPE "crdb_internal_region" AS ENUM ('gcp-asia-southeast1');

-- CreateTable
CREATE TABLE "products" (
    "id" BIGSERIAL NOT NULL,
    "name" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "price" DOUBLE PRECISION NOT NULL,
    "images" JSONB,

    CONSTRAINT "products_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "ratings" (
    "id_user" BIGINT NOT NULL,
    "id_product" BIGINT NOT NULL,
    "star" INTEGER NOT NULL,
    "message" TEXT NOT NULL,

    CONSTRAINT "ratings_pkey" PRIMARY KEY ("id_user","id_product")
);

-- CreateTable
CREATE TABLE "users" (
    "id" BIGSERIAL NOT NULL,
    "fullname" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "refresh_token" TEXT,
    "email_verified" BOOLEAN DEFAULT false,
    "verification_token" CHAR(6),

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");

-- AddForeignKey
ALTER TABLE "ratings" ADD CONSTRAINT "ratings_id_product_fkey" 
  FOREIGN KEY ("id_product") REFERENCES "products"("id") 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE "ratings" ADD CONSTRAINT "ratings_id_user_fkey" 
  FOREIGN KEY ("id_user") REFERENCES "users"("id") 
  ON DELETE NO ACTION ON UPDATE NO ACTION;

Running Migrations

Apply Existing Migrations

To apply all pending migrations to your database:

npx prisma migrate deploy

This command:

Applies all migrations in the prisma/migrations/ folder
Does not generate new migrations
Ideal for production deployments

Development Migrations

During development, use:

npx prisma migrate dev

This command:

Creates a new migration from schema changes
Applies the migration to the database
Generates Prisma Client
Prompts for a migration name

Only use migrate dev in development environments. For production, use migrate deploy.

Reset Database

To reset your database and reapply all migrations:

npx prisma migrate reset

This command will:

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

All data will be lost! Only use in development.

Creating New Migrations

1. Modify the Schema

Edit prisma/schema.prisma to add or modify models:

model products {
  id          BigInt    @id @default(autoincrement())
  name        String
  description String
  price       Float
  images      Json?
  stock       Int       // New field
  ratings     ratings[]
  orders      orders[]
}

2. Create Migration

Generate a new migration:

npx prisma migrate dev --name add_product_stock

This creates a new migration file:

prisma/migrations/[timestamp]_add_product_stock/migration.sql

3. Review Generated SQL

Always review the generated SQL before applying:

-- AlterTable
ALTER TABLE "products" ADD COLUMN "stock" INTEGER NOT NULL;

4. Apply Migration

The migration is automatically applied when using migrate dev. For production:

npx prisma migrate deploy

Migration Best Practices

1. Version Control

Always commit migration files to version control (Git). This ensures:

Team members have the same database schema
Migrations can be rolled back if needed
Production deployments are consistent

git add prisma/migrations/
git commit -m "feat: add product stock field"

2. Never Edit Applied Migrations

Once a migration is applied and committed:

Never modify the SQL file
Create a new migration instead
Editing applied migrations can cause inconsistencies

3. Test Migrations Locally

Before deploying to production:

Test migrations on a local database
Verify data integrity
Check for breaking changes
Test rollback procedures if needed

4. Use Descriptive Names

Name migrations clearly:

# Good
npx prisma migrate dev --name add_user_role_field
npx prisma migrate dev --name create_orders_table

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

5. Backup Before Major Changes

Before running migrations that modify or delete data:

# Create a database backup
pg_dump juadah > backup_$(date +%Y%m%d_%H%M%S).sql

# Run migration
npx prisma migrate deploy

Troubleshooting

Migration Failed

If a migration fails:

Check the error message - It usually indicates the issue
Resolve the issue in your schema or database

Mark migration as rolled back:

npx prisma migrate resolve --rolled-back [migration_name]

Try again:
```
npx prisma migrate deploy
```

Schema Out of Sync

If your schema doesn’t match the database:

npx prisma db pull

This introspects the database and updates your Prisma schema.

This will overwrite your schema.prisma file. Use with caution.

Reset Development Database

If your development database is in a bad state:

npx prisma migrate reset

Prisma Studio

Prisma Studio is a visual database browser:

npx prisma studio

This opens a web interface at http://localhost:5555 where you can:

View all tables and data
Edit records
Run queries
Test relationships

Prisma Studio is helpful for debugging and exploring your database during development.

Production Deployment

CI/CD Pipeline

In your deployment pipeline:

# 1. Install dependencies
npm install

# 2. Generate Prisma Client
npx prisma generate

# 3. Apply migrations
npx prisma migrate deploy

# 4. Build application
npm run build

# 5. Start server
npm start

Environment Variables

Ensure your production environment has:

DATABASE_URL="postgresql://user:password@prod-host:5432/juadah"
DIRECT_URL="postgresql://user:password@prod-host:5432/juadah"

Never commit production credentials to version control. Use environment variable management services.

Additional Resources

Database Schema

View complete database schema documentation

Prisma Documentation

Official Prisma documentation

PostgreSQL Docs

PostgreSQL official documentation

Migration Reference

Prisma Migrate reference guide

​Overview

​Prerequisites

​Environment Setup

​1. Configure Environment Variables

​2. Database Connection URLs

​Connection String Format

​Prisma Commands

​Generate Prisma Client

​Build the Project

​Migration Workflow

​Understanding Existing Migrations

​Running Migrations

​Apply Existing Migrations

​Development Migrations

​Reset Database

​Creating New Migrations

​1. Modify the Schema

​2. Create Migration

​3. Review Generated SQL

​4. Apply Migration

​Migration Best Practices

​Troubleshooting

​Migration Failed

​Schema Out of Sync

​Reset Development Database

​Prisma Studio

​Production Deployment

​CI/CD Pipeline

​Environment Variables

​Additional Resources

Database Schema

Prisma Documentation

PostgreSQL Docs

Migration Reference

Overview

Prerequisites

Environment Setup

1. Configure Environment Variables

2. Database Connection URLs

Connection String Format

Prisma Commands

Generate Prisma Client

Build the Project

Migration Workflow

Understanding Existing Migrations

Running Migrations

Apply Existing Migrations

Development Migrations

Reset Database

Creating New Migrations

1. Modify the Schema

2. Create Migration

3. Review Generated SQL

4. Apply Migration

Migration Best Practices

Troubleshooting

Migration Failed

Schema Out of Sync

Reset Development Database

Prisma Studio

Production Deployment

CI/CD Pipeline

Environment Variables

Additional Resources