MongoDB → PostgreSQL Migration Assistant & Validator (VS Code Extension)

An all-in-one VS Code extension designed to accelerate and de-risk your migration from MongoDB to PostgreSQL.

It provides tools for every phase of the migration:

1. Schema & DDL Inferrer: Turn MongoDB documents and Mongoose schemas into typed PostgreSQL relational tables, Prisma, or Drizzle schemas.
2. Query & Pipeline Translator: Convert MongoDB queries and aggregation pipelines ($match, $group, $lookup, $unwind) into PostgreSQL SQL and JSONB queries with index suggestions.
3. Codebase Migration Scanner: Audit your project for MongoDB driver queries, Mongoose models, and unsupported patterns (capped collections, $where, GridFS).
4. ETL Script Generator: Generate production-ready streaming data migration scripts in Node.js or Python and local Docker Compose environments.
5. Dual-Run API & Data Validator: Run the same requests against both MongoDB and PostgreSQL backends to produce field-level diffs and guarantee zero regressions before cutover.
6. Interactive Migration Workbench: Multi-tab VS Code webview and dedicated Activity Bar sidebar.

🚀 Features

1. 🏗️ Schema & DDL Generator

• Infers schema types from sample JSON documents or Mongoose schemas (ObjectId -> UUID, ISODate -> TIMESTAMPTZ, arrays -> child tables or native arrays).
• Supports 3 architectural strategies:
  • Normalized Relational: Relational tables with Foreign Keys and cascaded deletes.
  • Hybrid (Relational + JSONB): Relational columns for primary query fields, metadata JSONB with GIN indexes for flexible attributes.
  • Document Store (JSONB): Fast lift-and-shift with id PRIMARY KEY and data JSONB.
• Generates:
  • PostgreSQL DDL (.sql) with tables, indexes, and updated_at triggers.
  • Prisma schema (schema.prisma) models.
  • Drizzle ORM (schema.ts) definitions.

2. 🔄 Query & Aggregation Translator

• Translates find(), findOne(), count(), deleteMany() queries into SQL.
• Operators supported: $eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $or, $and, $regex (~*), $exists, $all.
• Aggregation stages supported:
  • $match -> WHERE / HAVING
  • $project -> SELECT
  • $group + accumulators ($sum, $avg, $min, $max, $push -> jsonb_agg)
  • $sort, $limit, $skip -> ORDER BY, LIMIT, OFFSET
  • $lookup -> LEFT JOIN ... ON ...
  • $unwind -> jsonb_array_elements(...)
• Produces both Relational SQL and JSONB SQL side-by-side with recommended PostgreSQL indexes.

3. 🔍 Codebase Scanner & Migration Audit

• Scans .js, .ts, .py files across your workspace.
• Detects:
  • Mongoose schemas and models
  • MongoDB native driver CRUD and aggregation calls
  • BSON types (ObjectId)
• Flags high-risk patterns:
  • Capped collections (recommends: range partitioning or cron cleanup)
  • $where arbitrary JavaScript execution
  • Tailable cursors (recommends: PostgreSQL LISTEN/NOTIFY)
  • GridFS (recommends: S3/MinIO cloud storage)
  • MapReduce
• Calculates a Migration Readiness Score and produces an interactive audit report.

4. 📦 Data Migration (ETL) Generator

• Generates reproducible, idempotent streaming migration scripts:
  • Node.js: Cursor streaming with @prisma/client, pg, or pg-promise with transactions and ON CONFLICT DO UPDATE.
  • Python: Bulk streaming with pymongo and psycopg2.extras.execute_values.
  • Docker Compose: Preconfigured MongoDB 7, PostgreSQL 16, pgAdmin, and Mongo Express.

5. ⚡ Dual-Run API & Response Validator

• Runs requests against both MongoDB and PostgreSQL APIs with identical payloads.
• Deep, path-aware comparison (e.g. customers[mongoId=...].status).
• Configurable normalization: field aliases (_id -> id), ignored fields, null/empty-string unification, date tolerance, numeric coercion.
• Pass/Fail results webview with record-level summary and diff explorer.
• Batch runner (Run All Comparisons in Workspace) and report exporter (Markdown / HTML).

🛠️ Usage

Quick Access

1. Click the Database icon in the VS Code Activity Bar to open the Migration Assistant sidebar.
2. Click Open Migration Workbench to open the multi-tab interactive workbench.
3. Or click the $(database) Mongo → Postgres status bar item at the bottom left.

Available Commands (Command Palette: Cmd/Ctrl + Shift + P)

• Mongo → PostgreSQL: Open Migration Workbench
• Mongo → PostgreSQL: Run Active Comparison
• Mongo → PostgreSQL: Run All Comparisons in Workspace
• Mongo → PostgreSQL: Generate PostgreSQL DDL / Prisma / Drizzle
• Mongo → PostgreSQL: Translate Mongo Query to SQL
• Mongo → PostgreSQL: Scan Workspace for MongoDB Code
• Mongo → PostgreSQL: Generate ETL Migration Script (Node.js / Python)
• Mongo → PostgreSQL: Create New migration-test.json

Context Menu Actions

• Highlight any MongoDB query or JSON in the editor -> Right-click -> Translate Mongo Query to SQL or Generate PostgreSQL DDL.

🧪 Testing

npm install
npm test            # Runs all 48 vitest tests across 8 test suites
npm run compile     # Typescript strict compilation
npm run lint        # ESLint verification