DEV Community

Mohamed Ammar
Mohamed Ammar

Posted on

Database Migration Guide: MySQL to PostgreSQL using pgloader and Prisma

Published on: 07/12/2025 | Tags: #database #migration #mysql #postgresql #prisma #devops

Pgloader to migrate from Mysql to postgres

Introduction

Migrating databases can be daunting, but with the right tools and approach, it becomes manageable. In this guide, I'll walk you through migrating from MySQL to PostgreSQL using pgloader for data transfer and Prisma ORM for schema management and evolution.

Why This Combination?

  • pgloader: Excellent for bulk data migration with type casting and data transformation
  • Prisma ORM: Perfect for schema management, evolution, and providing type-safe database access

Prerequisites

Environment Setup

  • Linux environment (WSL on Windows, Ubuntu, or similar)
  • MySQL database with existing schema and data
  • PostgreSQL instance ready for migration
  • Both instances could be local or remote (just update connection strings)

Installation Requirements 

# Install pgloader
sudo apt-get install pgloader

# Install Node.js and npm (if not already installed)
sudo apt-get install nodejs npm

# Install Prisma CLI version 6.x (not version 7)
npm install -g prisma@6.x

# Install necessary database connectors
npm install @prisma/client@6.x prisma@6.x
npm install @prisma/client-mysql@6.x @prisma/client-pg@6.x
Enter fullscreen mode Exit fullscreen mode

Step 1: Initialize Prisma and Extract MySQL Schema

First, let's set up Prisma and extract our existing MySQL schema:

bash
# Initialize a new Prisma project
mkdir db-migration-project
cd db-migration-project
npx prisma init

# Configure your Prisma schema to connect to MySQL
# Update prisma/schema.prisma with MySQL connection
Your prisma/schema.prisma should look like this initially:

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

datasource db {
  provider = "mysql"
  url      = "mysql://root:password@localhost:3306/railway"
}
Now, pull the schema from your MySQL database:

bash
# Extract schema from MySQL
npx prisma db pull

# This creates a Prisma schema file representing your MySQL structure
# Review the generated schema in prisma/schema.prisma
Enter fullscreen mode Exit fullscreen mode

Step 2: Create Migration File
Create your migration file for pgloader. Let's create a file called migration.load:


bash
# Create migration configuration
nano migration.load
Enter fullscreen mode Exit fullscreen mode

Add the following configuration to migration.load:

LOAD DATABASE
    FROM mysql://root:password@localhost:3306/railway
    INTO postgresql://postgres:postgres@localhost:5432/railway

    WITH include drop, 
         quote identifiers, 
         create tables,
         foreign keys, 
         create indexes,
         reset sequences,
         workers = 8,
         concurrency = 1

    CAST
        -- Map MySQL datetime to PostgreSQL timestamp without time zone
        type datetime to timestamp,

        -- Map large text types to PostgreSQL text
        type longtext to text,

        -- Map MySQL integer types appropriately
        type int to integer,
        type tinyint to boolean using tinyint-to-boolean,

        -- Handle MySQL specific types
        type year to integer

    BEFORE LOAD DO
        $$ CREATE SCHEMA IF NOT EXISTS railway; $$,
        $$ SET search_path TO railway; $$;
Enter fullscreen mode Exit fullscreen mode

Key Configuration Notes:
include drop: Drops tables in PostgreSQL if they exist

quote identifiers: Ensures special characters in table/column names are handled

create tables: Creates tables in PostgreSQL

reset sequences: Resets PostgreSQL sequences to match MySQL auto-increment values

workers: Parallel workers for faster migration (adjust based on your system)

Step 3: Execute Data Migration
Now, run the migration using pgloader:

bash
# Execute the migration
pgloader migration.load
Enter fullscreen mode Exit fullscreen mode

Monitor the progress - pgloader provides real-time statistics
What Happens During Migration:
pgloader connects to both databases

It reads the MySQL schema and creates equivalent PostgreSQL tables

Data is transferred with appropriate type casting

Indexes and foreign keys are recreated

Sequences are reset to maintain auto-increment values

Step 4: Handle Migration Report
After migration completes, pgloader provides a detailed report:

text

Summary report:

Total transfer time : 1m 30s
Total bytes transferred : 2.5 GB
Average transfer rate : 28.3 MB/s
Errors : 0
Warnings : 2
Review any warnings or errors and address them accordingly.

Step 5: Update Prisma to PostgreSQL
Now that your data is in PostgreSQL, update your Prisma configuration:

bash
# Update Prisma schema to use PostgreSQL
nano prisma/schema.prisma
Enter fullscreen mode Exit fullscreen mode

Change the datasource provider to PostgreSQL:

prisma
datasource db {
provider = "postgresql"
url = "postgresql://postgres:postgres@localhost:5432/railway"
}
Pull the schema from PostgreSQL to ensure Prisma understands the new database:

bash
# Extract schema from PostgreSQL
npx prisma db pull

# Generate Prisma Client for PostgreSQL
npx prisma generate

# Optional: Push schema to ensure consistency
npx prisma db push
Enter fullscreen mode Exit fullscreen mode

Step 6: Update Your Application
Update your application's database connection:

javascript
// In your application code
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()
Enter fullscreen mode Exit fullscreen mode

// Your Prisma client now connects to PostgreSQL

Benefits of Using Prisma ORM for Schema Evolution

  1. Type Safety 
typescript
// Full TypeScript/JavaScript type safety
const user = await prisma.user.findUnique({
  where: { email: 'user@example.com' }
})
// `user` is fully typed
Enter fullscreen mode Exit fullscreen mode

  1. Easy Schema Migrations
    bash
    Make schema changes in Prisma schema file
    Then create and apply migrations
    npx prisma migrate dev --name add_new_feature

  2. Database Agnostic

  • Same Prisma schema can work with different databases
  • Easy to switch or support multiple database backends

  1. Built-in Migration History
    Prisma maintains a migration history, making rollbacks and audits straightforward.

  2. Developer Experience

  • Intuitive data modeling
  • Auto-completion in IDEs
  • Built-in best practices

Application Testing
Thoroughly test your application with the new PostgreSQL database.

Conclusion

Migrating from MySQL to PostgreSQL using pgloader and Prisma provides a robust, reliable approach. pgloader handles the heavy lifting of data transfer with proper type casting, while Prisma offers excellent schema management and evolution capabilities.

The combination gives you:

  • Smooth data migration
  • Type-safe database access
  • Easy future schema changes
  • Database abstraction

Remember to always backup both databases before migration and test thoroughly in a staging environment before production deployment.

Happy migrating!

About the Author:
Mohamed Ammar, senior data architect with expertise in database systems and data architectures. Follow for more technical guides and tutorials!

Disclaimer: This guide assumes you have appropriate backups and have tested the migration process in a non-production environment first. Always verify data integrity after migration.

Top comments (0)