MongoDB to PostgreSQL Migration Guide

Overview

This guide covers the migration from MongoDB to PostgreSQL using Prisma ORM.

Key Changes

1. Database Schema

• MongoDB: Document-based with embedded objects
• PostgreSQL: Relational with foreign keys and separate tables

2. ORM Change

• Before: Mongoose ODM
• After: Prisma ORM

3. Data Structure Changes

Categories

-- PostgreSQL Table
CREATE TABLE categories (
  id TEXT PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  name_hebrew TEXT NOT NULL,
  description TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Cards

-- PostgreSQL Table
CREATE TABLE cards (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  title_hebrew TEXT NOT NULL,
  description TEXT,
  image_url TEXT NOT NULL,
  image_public_id TEXT NOT NULL,
  is_active BOOLEAN DEFAULT true,
  category_id TEXT REFERENCES categories(id),
  created_by_id TEXT REFERENCES admins(id),
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Editable Fields

-- PostgreSQL Table (Normalized)
CREATE TABLE editable_fields (
  id TEXT PRIMARY KEY,
  field_id TEXT NOT NULL,
  type TEXT NOT NULL,
  default_text TEXT NOT NULL,
  position_x FLOAT NOT NULL,
  position_y FLOAT NOT NULL,
  font_size INTEGER DEFAULT 16,
  font_family TEXT DEFAULT 'Noto Sans Hebrew',
  color TEXT DEFAULT '#000000',
  font_weight TEXT DEFAULT 'normal',
  text_align TEXT DEFAULT 'right',
  card_id TEXT REFERENCES cards(id) ON DELETE CASCADE
);

Migration Steps

1. Setup PostgreSQL Database

Local Development

# Install PostgreSQL
brew install postgresql  # macOS
sudo apt-get install postgresql  # Ubuntu

# Create database
createdb simcha_cards

Using Docker

docker run --name postgres-simcha \
  -e POSTGRES_DB=simcha_cards \
  -e POSTGRES_USER=simcha_user \
  -e POSTGRES_PASSWORD=simcha_password \
  -p 5432:5432 \
  -d postgres:15

2. Install Dependencies

cd backend
npm install @prisma/client prisma
npm uninstall mongoose

3. Setup Prisma

# Initialize Prisma
npx prisma init

# Generate Prisma client
npx prisma generate

# Push schema to database
npx prisma db push

# Run migrations (production)
npx prisma migrate deploy

4. Environment Variables

Update your .env file:

# Replace MongoDB URI with PostgreSQL
DATABASE_URL="postgresql://username:password@localhost:5432/simcha_cards?schema=public"

5. Data Migration Script

# Seed new database
npm run db:seed

API Changes

Response Format

The API maintains the same response format for frontend compatibility:

// Frontend still receives MongoDB-style IDs
{
  "_id": "clp123...",  // Prisma CUID
  "title": "Card Title",
  "category": {
    "_id": "clp456...",
    "name": "category-name"
  }
}

Query Changes

// Before (Mongoose)
const cards = await Card.find({ isActive: true })
  .populate('category')
  .sort({ createdAt: -1 })

// After (Prisma)
const cards = await prisma.card.findMany({
  where: { isActive: true },
  include: { category: true },
  orderBy: { createdAt: 'desc' }
})

Benefits of PostgreSQL

1. ACID Compliance

• Guaranteed data consistency
• Better transaction support
• Referential integrity

2. Performance

• Better query optimization
• Efficient indexing
• Faster complex queries

3. Scalability

• Better horizontal scaling options
• Read replicas support
• Connection pooling

4. Data Integrity

• Foreign key constraints
• Check constraints
• Data validation at database level

Deployment Considerations

1. Database Hosting

• Vercel: Use Vercel Postgres
• Railway: Built-in PostgreSQL
• Supabase: Managed PostgreSQL
• AWS RDS: Production-grade PostgreSQL

2. Connection Pooling

// Prisma connection pooling
const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL + "?connection_limit=5&pool_timeout=20"
    }
  }
})

3. Migrations

# Production deployment
npx prisma migrate deploy
npx prisma generate

Troubleshooting

Common Issues

1. Connection Errors

   # Check PostgreSQL is running
   pg_isready -h localhost -p 5432
   

2. Schema Sync Issues

   # Reset database (development only)
   npx prisma migrate reset
   

3. Type Errors

   # Regenerate Prisma client
   npx prisma generate
   

Performance Optimization

1. Indexing

   CREATE INDEX idx_cards_category ON cards(category_id);
   CREATE INDEX idx_cards_active ON cards(is_active);
   

2. Query Optimization

   // Use select to limit fields
   const cards = await prisma.card.findMany({
     select: {
       id: true,
       title: true,
       titleHebrew: true,
       category: {
         select: {
           id: true,
           nameHebrew: true
         }
       }
     }
   })
   

Testing

Database Tests

# Test database connection
npx prisma db pull

# Validate schema
npx prisma validate

# View data in Prisma Studio
npx prisma studio

The migration maintains full compatibility with the existing frontend while providing better performance, data integrity, and scalability.