🚀 Migrating from MongoDB to PostgreSQL — A Developer's Complete Guide

As developers evolve applications from flexible NoSQL setups to structured, scalable relational systems, migrating from MongoDB to PostgreSQL becomes a powerful move. But with great power comes great complexity.

This guide walks through the complete journey of migrating a Node.js app from MongoDB to PostgreSQL — from schema introspection to data normalization, tool selection, and best practices.

---

📌 Why Migrate from MongoDB to PostgreSQL?

• ✅ Better support for relational data and constraints

• ✅ Complex querying with JOINs, views, indexes

• ✅ Strict schema enforcement and data integrity

• ✅ Easier reporting and analytics

• ✅ Lower cost for some workloads at scale

---

🔍 Challenges in Migrating from MongoDB to PostgreSQL

• ❌ MongoDB is schemaless — Postgres is schema-strict

• ❌ Embedded documents and arrays are not 1:1 mappable

• ❌ No built-in foreign keys in MongoDB means no relation inference

• ❌ Inconsistent data types across documents

---

✅ High-Level Migration Plan

1. Inventory collections and their structure

2. Introspect MongoDB schema using Prisma

3. Design PostgreSQL schema with normalization and relations

4. Migrate data (via scripts or ETL tools)

5. Update application code

6. Test and deploy

---

⚙️ Tools for Migration

---

🔧 Using Prisma for MongoDB Introspection

Step 1: Setup Project

mkdir mongo-to-postgres && cd mongo-to-postgres
npm init -y
npm install prisma --save-dev
npx prisma init

Step 2: Configure MongoDB as Data Source

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

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

Update .env:

DATABASE_URL="mongodb+srv://user:pass@cluster.mongodb.net/dbname"

Step 3: Introspect MongoDB

npx prisma db pull

Prisma will generate models from MongoDB collections.

Example generated model:

prismaCopyEditmodel User {
  id    String @id @map("_id") @default(auto()) @test.ObjectId
  name  String?
  email String?
  @@map("users")
}

Step 4: Switch to PostgreSQL

Update schema.prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Convert ObjectId fields and composite types to relational-friendly formats.

1. Remove Mongo-specific fields:

   • Replace @test.ObjectId with @default(uuid()) or @id with @default(autoincrement())

   • Remove @map("_id") unless you're preserving naming

   • Adjust types like Json, String?, Int?, etc.

Example after conversion:

prismaCopyEditmodel User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

---

🏗️ Designing the Relational Schema

❌ What Prisma Won't Do:

• Won’t create @relation mappings

• Won’t normalize embedded objects into new tables

• Won’t enforce foreign key constraints

✅ What You Should Do:

• Manually split composite types into separate models

• Use @relation to link models via foreign keys

• Convert MongoDB array fields into child tables

---

💾 6. Create PostgreSQL DB and Update .env

In .env, update your Postgres DB URL:

dotenvCopyEditDATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Test the connection:

bashCopyEditnpx prisma generate

---

📐 7. Push Schema to PostgreSQL

Create the tables in PostgreSQL using:

bashCopyEditnpx prisma migrate dev --name init

💡 This creates a migration file and syncs the schema.

---

🛠️ Migrating the Data

Option A: Custom Node.js Script with Prisma Client

const { PrismaClient } = require('@prisma/client');
const mongoose = require('mongoose');
const prisma = new PrismaClient();

(async () => {
  await mongoose.connect('mongodb://localhost/mydb');
  const UserMongo = mongoose.model('User', new mongoose.Schema({}, { strict: false }));
  const users = await UserMongo.find();

  for (let user of users) {
    await prisma.user.create({
      data: {
        name: user.name,
        email: user.email,
      }
    });
  }
})();

Option B: Use Airbyte or ETL tool

• Setup source (MongoDB) and destination (Postgres)

• Map collections to tables (basic flattening)

• Normalize after import using SQL

---

🧠 Lessons Learned

• MongoDB → PostgreSQL is not a direct mapping

• Relations must be designed manually

• Prisma helps introspect and define schema, but not normalize

• Tools like Hackolade and Airbyte make parts easier, but not perfect

---

✨ Final Thoughts

MongoDB gives freedom, PostgreSQL gives structure. Migrating between them requires discipline, tools, and domain knowledge. Use Prisma to bootstrap your schema, then layer in normalization and relational structure.

With the right plan, you can scale your backend from flexible MVP to production-grade system — the relational way.

---

Automated MongoDB-to-PostgreSQL migration falls short:

🔥 No tool today can perfectly normalize and convert MongoDB collections into a relational PostgreSQL schema with accurate relationships.

Why? Because MongoDB is schemaless and lacks foreign key constraints — meaning no tool can safely guess your business logic and data relationships without human help.

That said, here are your best options to get as close as possible to what you're asking:

---

🛠️ 1. MongoDB Compass + Hackolade (Semi-Auto Normalization Tool)

🔹 Hackolade is a visual data modeling tool made exactly for:

• Reverse-engineering NoSQL databases (like MongoDB)

• Designing normalized relational schemas for SQL databases (like PostgreSQL)

✅ Pros:

• Can connect to your MongoDB

• Automatically infers data models

• Provides suggested relational mapping

• Allows drag-and-drop to design foreign keys and normalization

• Can export SQL schema for PostgreSQL

• Good for large schemas (30–50+ collections)

🚫 Cons:

• It’s not free (but free trial available)

• You still need to understand your domain to finalize schema

📌 This is the closest to what you’re asking for.

---

🛠️ 2. Airbyte + Manual Schema Conversion

Airbyte (https://airbyte.io/)

• Open-source ETL tool

• Can migrate data from MongoDB to PostgreSQL

• You can set up JSON flattening rules (basic normalization)

But:

• It won’t infer relations

• Just copies documents as-is into flat tables or JSON columns

Use case: If you just want to move raw data into PostgreSQL and normalize it later using SQL or scripts.

---

🛠️ 3. Prisma + AI-Assisted Refactoring (Best DIY path)

Use Prisma to introspect MongoDB, then:

• Use a tool like ChatGPT (me 😊) or scripts to:

  • Auto-suggest foreign key relationships

  • Split embedded objects into separate tables

  • Generate relational Prisma schemas

• Then generate and migrate your schema

This gives total control + AI assistance but is not fully auto.

---

🛠️ 4. Mongify (legacy, no longer maintained but works)

https://github.com/anlek/mongify

• Older tool to migrate MongoDB to SQL databases

• Allows you to define a mapping file where you describe relationships manually

• Can normalize some data if configured

🟡 Not actively maintained
🟢 Still usable for simple use cases

---

💡 Realistic Strategy (Recommended for Production Teams)

---

🧠 Want Full Automation? Here's the Truth:

There is no tool that will both:

• Automatically extract and normalize MongoDB

• Create relational schemas with perfect foreign keys

• Migrate and preserve business logic

Because that requires human domain understanding.