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

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

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

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

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; $$;

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

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

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

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()

// 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

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

Database Agnostic

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

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

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.