MongoDB to PostgreSQL Migration Guide

This guide provides step-by-step instructions for migrating your InterChat application from MongoDB to PostgreSQL.

Prerequisites

• Docker and Docker Compose installed
• Node.js installed
• Access to your MongoDB database
• Sufficient disk space for database dumps and migration logs

Migration Steps

1. Set Up PostgreSQL Environment

First, set up the necessary environment variables and PostgreSQL database:

DATABASE_URL=YOUR_POSTGRES_CONNECTION_STRING
MONGODB_URL=YOUR_OLD_MONGODB_URL

# Start PostgreSQL using Docker
docker-compose -f dev/docker/docker-compose.postgres.yml up -d

This will:

• Replace PostgreSQL-related environment variables to your .env file
• Start a PostgreSQL database in Docker
• Start pgAdmin (accessible at http://localhost:5050) for database management

2. Prepare PostgreSQL Schema

Generate the Prisma client for PostgreSQL and create the database schema:

# Generate Prisma client for PostgreSQL
npx prisma generate
 
# Create PostgreSQL database schema
npx prisma db push
 
# Generate the Prisma client for MongoDb
npx prisma generate --schema=./prisma/schema-mongo.prisma

3. Run the Migration Script

The migration script will transfer data from MongoDB to PostgreSQL:

node scripts/migrate-to-postgres.js

This script:

• Connects to both MongoDB and PostgreSQL
• Extracts data from MongoDB
• Transforms it to fit PostgreSQL's schema
• Loads it into PostgreSQL
• Logs the migration process to logs/migration-[timestamp].log

The migration may take some time depending on the size of your database.

4. Verify the Migration

After the migration completes, verify that all data has been transferred correctly:

1. Check the migration logs for any errors
2. Connect to pgAdmin (http://localhost:5050) to inspect the PostgreSQL database
3. Run some test queries to ensure data integrity

5. Switch the Application to PostgreSQL

Once you're satisfied with the migration, update your application to use PostgreSQL:

1. Remove your MONGODB_URL from the .env

2. Run the following commands:

   # Regenerate Prisma clients
   npx prisma generate
    
   # Rebuild the application
   npm run build
    
   # Restart the application
   npm start

Technical Details

Schema Differences

The main differences between MongoDB and PostgreSQL schemas:

1. ID Fields: MongoDB uses ObjectId types, while PostgreSQL uses CUID or string IDs
2. Embedded Documents: MongoDB's embedded documents are normalized in PostgreSQL

Performance Considerations

• PostgreSQL may perform differently than MongoDB for certain operations
• You may need to optimize queries and indexes after migration
• Consider monitoring database performance after switching

Data Integrity

The migration script includes validation to ensure data integrity:

• Checks for required fields
• Validates relationships between entities
• Logs any issues encountered during migration
• Some data that are automatically deemed insufficient, broken or outdated will be skipped

Troubleshooting

Common Issues

1. Connection Errors:

   • Verify database connection strings in .env
   • Check that PostgreSQL container is running

2. Schema Errors:

   • Ensure Prisma schema is correctly defined
   • Check for any custom types that may need special handling

3. Data Type Errors:

   • Some fields may need type conversion during migration
   • Check the migration logs for type conversion errors

Getting Help

If you encounter issues not covered in this guide:

1. Check the migration logs for specific error messages
2. Review the Prisma documentation for PostgreSQL-specific features
3. Reach out to the development team for assistance