Database Schema Migrator

A powerful CLI tool to migrate database schemas between MySQL, PostgreSQL, MongoDB, and Firebase Firestore with support for 9 bidirectional migration paths.

Installation

From npm (Recommended)

npm install -g db-schema-migrator

After installation, the db-migrator command will be available globally:

db-migrator --help

From Source

git clone https://github.com/NirmalNagaraj/mig-cli.git
cd mig-cli
npm install
npm run build
npm link

Features

• MySQL to PostgreSQL: Convert MySQL schemas to PostgreSQL-compatible SQL
• PostgreSQL to MySQL: Convert PostgreSQL schemas to MySQL-compatible SQL
• Firebase to MySQL: Infer MySQL schemas from Firebase Firestore collections and generate migration SQL
• Firebase to PostgreSQL: Infer PostgreSQL schemas from Firebase Firestore collections and generate migration SQL
• MySQL to Firebase: Export MySQL database to Firestore-compatible JSON format
• MySQL to MongoDB: Export MySQL database to MongoDB Extended JSON format
• MongoDB to MySQL: Infer MySQL schemas from MongoDB collections and generate migration SQL
• PostgreSQL to MongoDB: Export PostgreSQL database to MongoDB Extended JSON format
• MongoDB to PostgreSQL: Infer PostgreSQL schemas from MongoDB collections and generate migration SQL
• Automatic data type mapping
• Foreign key and constraint preservation
• Foreign key to Firestore/MongoDB reference conversion
• Index migration
• Interactive prompts for easy configuration
• Comprehensive error handling with actionable suggestions

Quick Start

After installing globally with npm, you can immediately start using the tool:

# View all available commands
db-migrator --help

# Get help for a specific command
db-migrator mysql-to-postgresql --help

# Check version
db-migrator --version

Usage

MySQL to PostgreSQL

db-migrator mysql-to-postgresql -u mysql://user:password@localhost:3306/mydb -o ./output

PostgreSQL to MySQL

db-migrator postgresql-to-mysql -u postgresql://user:password@localhost:5432/mydb -o ./output

Firebase to MySQL

Migrate Firebase Firestore collections to MySQL schema:

db-migrator firebase-to-mysql -c ./path/to/serviceAccount.json -o ./output/migration.sql

MySQL to Firebase

Export MySQL database to Firestore-compatible JSON format:

db-migrator mysql-to-firebase -u mysql://user:password@localhost:3306/mydb -o ./firestore-export.json

MySQL to MongoDB

Export MySQL database to MongoDB Extended JSON format:

db-migrator mysql-to-mongodb -u mysql://user:password@localhost:3306/mydb -o ./mongodb-export.json

MongoDB to MySQL

Migrate MongoDB collections to MySQL schema:

db-migrator mongodb-to-mysql -u mongodb://localhost:27017/mydb -o ./migration.sql

PostgreSQL to MongoDB

Export PostgreSQL database to MongoDB Extended JSON format:

db-migrator postgresql-to-mongodb -u postgresql://user:password@localhost:5432/mydb -o ./mongodb-export.json

MongoDB to PostgreSQL

Migrate MongoDB collections to PostgreSQL schema:

db-migrator mongodb-to-postgresql -u mongodb://localhost:27017/mydb -o ./migration.sql

Firebase Migration Options

• -c, --credentials <path>: Path to Firebase service account JSON file (required)
• -o, --output <path>: Output SQL file path (optional, defaults to current directory)
• --collections <collections>: Comma-separated list of collections to migrate (optional, migrates all by default)
• --include-subcollections: Include subcollections in migration (optional)

Examples

Migrate all collections:

db-migrator firebase-to-mysql -c ./serviceAccount.json -o ./migration.sql

Migrate specific collections:

db-migrator firebase-to-mysql -c ./serviceAccount.json --collections users,posts,comments

Include subcollections:

db-migrator firebase-to-mysql -c ./serviceAccount.json --include-subcollections

Firebase Setup

Getting Firebase Service Account Credentials

1. Go to Firebase Console
2. Select your project
3. Click the gear icon (⚙️) and select Project Settings
4. Navigate to the Service Accounts tab
5. Click Generate New Private Key
6. Save the downloaded JSON file securely
7. Use this file path with the -c option

Required Permissions

The service account needs the following IAM role:

• Cloud Datastore User (or Firebase Admin)

To verify/add permissions:

1. Go to Google Cloud Console
2. Select your Firebase project
3. Navigate to IAM & Admin > IAM
4. Find your service account
5. Ensure it has the required role

Firestore Data Type Mapping

Firebase Firestore types are automatically mapped to MySQL types:

| Firestore Type | MySQL Type | Notes | |---------------|------------|-------| | String | VARCHAR(n) or TEXT | Length inferred from data | | Number (integer) | INT or BIGINT | Based on value range | | Number (decimal) | DECIMAL(p,s) | Precision inferred from data | | Boolean | TINYINT(1) | 0 for false, 1 for true | | Timestamp | DATETIME | Converted to MySQL format | | Array | JSON | Serialized as JSON | | Map/Object | JSON | Serialized as JSON | | GeoPoint | VARCHAR(100) | Stored as "latitude,longitude" | | Reference | VARCHAR(255) | Stored as document path |

Schema Inference

The tool analyzes all documents in each collection to infer the schema:

• Field Types: Determined by analyzing all values across documents
• Nullable Fields: Fields missing in some documents are marked as nullable
• Primary Key: Document ID is automatically added as primary key
• Timestamps: created_at and updated_at columns are added from Firestore metadata
• Mixed Types: When a field has multiple types, the most appropriate fallback type is chosen

Output Structure

The generated SQL file includes:

1. Header Comments: Migration metadata and document counts
2. CREATE TABLE Statements: One per collection with inferred schema
3. Collection Comments: Document counts and collection names
4. Proper Escaping: All identifiers and values are properly escaped

Example output:

-- MySQL Schema Migration
-- Generated from: Firebase Firestore
-- Generated at: 2024-01-15T10:30:00.000Z
-- Schema Type: Inferred from documents
-- Inference Strategy: document-analysis
-- Total Documents Analyzed: 1500

-- Collection: users

CREATE TABLE `users` (
  `id` VARCHAR(255) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `age` INT,
  `created_at` DATETIME,
  `updated_at` DATETIME,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

MySQL to Firebase Migration

Overview

The MySQL to Firebase migration exports your MySQL database to a Firestore-compatible JSON format. This JSON can then be imported into Firestore using the Firebase Admin SDK or custom import scripts.

Command Options

db-migrator mysql-to-firebase [options]

• -u, --uri <uri>: MySQL connection URI (required)
• -o, --output <path>: Output JSON file path (optional)
• --tables <tables>: Comma-separated list of tables to migrate (optional)
• --convert-references: Convert foreign keys to Firestore references (optional)
• --batch-size <size>: Number of rows to process at once (default: 1000)

Usage Examples

Export all tables:

db-migrator mysql-to-firebase -u mysql://user:pass@localhost:3306/mydb -o firestore.json

Export specific tables:

db-migrator mysql-to-firebase -u mysql://user:pass@localhost:3306/mydb --tables users,posts,comments

With foreign key to reference conversion:

db-migrator mysql-to-firebase -u mysql://user:pass@localhost:3306/mydb --convert-references

Custom batch size for large tables:

db-migrator mysql-to-firebase -u mysql://user:pass@localhost:3306/mydb --batch-size 500

Data Type Mapping

MySQL types are automatically converted to Firestore-compatible types:

| MySQL Type | Firestore Type | Notes | |-----------|---------------|-------| | VARCHAR, CHAR, TEXT | string | Direct conversion | | INT, BIGINT, SMALLINT | number | Direct conversion | | DECIMAL, FLOAT, DOUBLE | number | Converted to JavaScript number | | TINYINT(1), BOOLEAN | boolean | 0/1 to false/true | | DATE, DATETIME, TIMESTAMP | timestamp | Firestore timestamp object with _seconds and _nanoseconds | | JSON | map/array | Parsed to native object/array | | BLOB, BINARY | string | Base64-encoded with metadata | | ENUM | string | String value | | SET | array | Array of strings | | NULL | omitted | Field not included in document |

Foreign Key Conversion

When --convert-references is enabled, foreign keys are converted to Firestore reference paths:

// MySQL Foreign Key
users.id = 123
posts.user_id = 123 (FK to users.id)

// Firestore Reference
{
  "_type": "reference",
  "_path": "users/123"
}

Output JSON Structure

The generated JSON follows this structure:

{
  "metadata": {
    "exportedAt": "2024-01-15T10:30:00.000Z",
    "sourceDatabase": "MySQL",
    "version": "8.0.32",
    "tableCount": 3,
    "documentCount": 150
  },
  "collections": {
    "users": {
      "1": {
        "name": "John Doe",
        "email": "[email protected]",
        "age": 30,
        "created_at": {
          "_seconds": 1705315200,
          "_nanoseconds": 0
        }
      }
    },
    "posts": {
      "101": {
        "title": "First Post",
        "content": "Hello World",
        "user": {
          "_type": "reference",
          "_path": "users/1"
        }
      }
    }
  }
}

Importing to Firestore

After generating the JSON, import it to Firestore using the Firebase Admin SDK:

const admin = require('firebase-admin');
const fs = require('fs');

// Initialize Firebase Admin
admin.initializeApp({
  credential: admin.credential.cert('./serviceAccount.json')
});

const db = admin.firestore();
const data = JSON.parse(fs.readFileSync('./firestore.json', 'utf8'));

async function importToFirestore() {
  for (const [collectionName, documents] of Object.entries(data.collections)) {
    for (const [docId, docData] of Object.entries(documents)) {
      // Convert timestamp objects
      const processedData = processTimestamps(docData);
      
      await db.collection(collectionName).doc(docId).set(processedData);
      console.log(`Imported ${collectionName}/${docId}`);
    }
  }
}

function processTimestamps(obj) {
  const processed = {};
  for (const [key, value] of Object.entries(obj)) {
    if (value && typeof value === 'object' && '_seconds' in value) {
      // Convert to Firestore Timestamp
      processed[key] = admin.firestore.Timestamp.fromMillis(value._seconds * 1000);
    } else if (value && typeof value === 'object' && '_type' in value && value._type === 'reference') {
      // Convert to Firestore DocumentReference
      processed[key] = db.doc(value._path);
    } else {
      processed[key] = value;
    }
  }
  return processed;
}

importToFirestore().then(() => {
  console.log('Import complete!');
  process.exit(0);
}).catch(error => {
  console.error('Import failed:', error);
  process.exit(1);
});

Field Name Sanitization

Firestore has specific rules for field names and document IDs. The tool automatically:

• Removes invalid characters from field names
• Sanitizes document IDs to meet Firestore requirements
• Handles reserved field names
• Truncates long document IDs (>1500 bytes)
• Logs warnings when sanitization occurs

Best Practices

1. Test First: Export a small subset of tables first to verify the output
2. Review JSON: Check the generated JSON before importing to Firestore
3. Batch Size: Adjust --batch-size for large tables to manage memory usage
4. References: Only use --convert-references if you need relational data preserved
5. Backup: Always backup your MySQL database before migration
6. Validate: Test the import on a development Firestore instance first

PostgreSQL to MongoDB Migration

Overview

The PostgreSQL to MongoDB migration exports your PostgreSQL database to MongoDB Extended JSON format with proper BSON type annotations.

Command Options

db-migrator postgresql-to-mongodb [options]

• -u, --uri <uri>: PostgreSQL connection URI (required)
• -o, --output <path>: Output JSON file path (optional)
• --tables <tables>: Comma-separated list of tables to migrate (optional)
• --convert-references: Convert foreign keys to MongoDB references (optional)
• --use-objectid: Generate ObjectId format for _id fields (optional)
• --batch-size <size>: Number of rows to process at once (default: 1000)

Usage Examples

Export all tables:

db-migrator postgresql-to-mongodb -u postgresql://user:pass@localhost:5432/mydb -o mongodb.json

Export specific tables with ObjectId:

db-migrator postgresql-to-mongodb -u postgresql://user:pass@localhost:5432/mydb --tables users,orders --use-objectid

Data Type Mapping

PostgreSQL types are automatically converted to MongoDB BSON types:

| PostgreSQL Type | MongoDB Type | Notes | |----------------|--------------|-------| | INTEGER, SMALLINT | $numberInt | 32-bit integer | | BIGINT | $numberLong | 64-bit integer | | NUMERIC, DECIMAL, FLOAT, DOUBLE PRECISION | $numberDouble | Double precision | | BOOLEAN | boolean | Native boolean | | TIMESTAMP, DATE, TIME | $date | ISO 8601 format | | JSON, JSONB | object/array | Parsed to native | | BYTEA | $binary | Base64 encoded | | VARCHAR, TEXT, CHAR | string | Direct conversion | | Arrays | array | Native arrays |

Output JSON Structure

{
  "users": [
    {
      "_id": { "$numberInt": "1" },
      "name": "John Doe",
      "email": "[email protected]",
      "age": { "$numberInt": "30" },
      "balance": { "$numberDouble": "1234.56" },
      "is_active": true,
      "created_at": { "$date": "2025-11-25T10:30:00.000Z" }
    }
  ]
}

Importing to MongoDB

mongoimport --db mydb --collection users --file mongodb.json --jsonArray

MongoDB to PostgreSQL Migration

Overview

The MongoDB to PostgreSQL migration analyzes MongoDB collections and generates PostgreSQL-compatible SQL DDL statements.

Command Options

db-migrator mongodb-to-postgresql [options]

• -u, --uri <uri>: MongoDB connection URI (required)
• -o, --output <path>: Output SQL file path (optional)
• --collections <collections>: Comma-separated list of collections to migrate (optional)

Usage Examples

Export all collections:

db-migrator mongodb-to-postgresql -u mongodb://localhost:27017/mydb -o schema.sql

Export specific collections:

db-migrator mongodb-to-postgresql -u mongodb://localhost:27017/mydb --collections users,orders

Data Type Mapping

MongoDB types are automatically converted to PostgreSQL types:

| MongoDB Type | PostgreSQL Type | Notes | |-------------|----------------|-------| | ObjectId | VARCHAR(24) | Hex string | | String | TEXT | Unlimited length | | Int, Int32 | INTEGER | 32-bit integer | | Long, Int64 | BIGINT | 64-bit integer | | Double, Float | DOUBLE PRECISION | Double precision | | Boolean | BOOLEAN | Native boolean | | Date, Timestamp | TIMESTAMP WITH TIME ZONE | With timezone | | Array | JSONB | Preserves structure | | Object, Document | JSONB | Preserves structure | | Binary | BYTEA | Binary data |

Output SQL Structure

-- PostgreSQL Schema Migration
-- Generated from: MongoDB
-- Generated at: 2025-11-25T10:30:00.000Z
-- Schema Type: Inferred from documents
-- Total Documents Analyzed: 1500

-- Collection: users

CREATE TABLE "users" (
  "_id" VARCHAR(24) NOT NULL,
  "name" TEXT NOT NULL,
  "email" TEXT NOT NULL,
  "age" INTEGER,
  "metadata" JSONB,
  "created_at" TIMESTAMP WITH TIME ZONE,
  PRIMARY KEY ("_id")
);

Executing the SQL

psql -U username -d database -f schema.sql

Troubleshooting

MySQL to Firebase Errors

"Failed to extract data"

• Verify user has SELECT privileges on tables
• Check if tables exist and are accessible
• Try extracting specific tables with --tables option

"Invalid data type conversion"

• Review unsupported MySQL data types
• Check the data type mapping table above
• Consider manual conversion for complex types

"Foreign key reference not found"

• Ensure referenced tables are included in migration
• Verify foreign key constraints are valid
• Disable reference conversion if not needed

"Document ID too long"

• Primary key values must be <1500 bytes
• Tool will automatically truncate and hash long IDs
• Consider using shorter primary keys

"Memory errors"

• Reduce batch size: --batch-size 500
• Migrate tables one at a time
• Increase Node.js memory: node --max-old-space-size=4096

Firebase Errors

"Credentials file not found"

• Verify the file path is correct
• Use an absolute path or path relative to current directory
• Check file permissions

"Invalid JSON format" or "Missing required fields"

• Download a fresh service account key from Firebase Console
• Ensure the file hasn't been modified
• Verify it contains: project_id, private_key, client_email

"Permission denied"

• Check Firestore security rules
• Verify service account has "Cloud Datastore User" role
• Ensure Firestore API is enabled in Google Cloud Console

"Collection not found"

• Verify collection name spelling
• Check if collection exists in Firestore Console
• Try migrating all collections first

General Errors

Connection Errors

• Verify database server is running
• Check firewall settings
• Confirm host and port are correct

Authentication Errors

• Verify credentials are correct
• Check user permissions
• Ensure user account is not locked

For more detailed error information, run with:

DEBUG=1 db-migrator firebase-to-mysql -c ./serviceAccount.json

Development

Build

npm run build

Run Tests

npm test

Run in Development Mode

npm run dev

Architecture

The tool follows a modular architecture:

• Connectors: Database-specific connection and schema extraction
  • MySQLConnector: Connects to MySQL and extracts schema
  • PostgreSQLConnector: Connects to PostgreSQL and extracts schema
  • FirebaseConnector: Connects to Firestore and infers schema
• Transformers: Convert schemas between formats
  • MySQLTransformer: Generates MySQL-compatible SQL
  • PostgreSQLTransformer: Generates PostgreSQL-compatible SQL
• Utilities:
  • SchemaInferencer: Analyzes Firestore documents to infer table schemas
  • DataTypeMapper: Maps Firestore types to MySQL types
  • FileWriter: Handles output file generation
• CLI: Command-line interface with interactive prompts

License

ISC

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

mig-cli