mongo-to-mysql 0.1.5

A command line tool to migrate MongoDB collections to MySQL tables dynamically

mongo-to-mysql

mongo-to-mysql is a high-performance Python command-line utility (CLI) designed to automate the migration of unstructured MongoDB databases to structured MySQL relational databases.

It dynamically infers table schemas from NoSQL document structures, normalizes complex hierarchies (arrays, nested dictionaries) into relational schemas without redundant junction tables, supports schema evolution across batch boundaries, and operates with full transactional reliability.

---

🚀 Key Features

• Automated Schema Inference & Evolution: Automatically creates tables and columns based on document attributes. If new fields are introduced dynamically during migration, the tool alters the MySQL tables on the fly without interrupting the transaction.
• Recursive Normalization (No Redundant Mappings): Converts embedded hierarchies directly into relational layouts:
  • Lists of Scalars (e.g., ["friend", "colleague"]) are mapped to a dedicated child table.
  • Nested Dictionaries (One-to-One) (e.g., {"city": "Pune"}) are normalized into a sub-table linked directly via a foreign key (e.g., subtable_parent_id).
  • Lists of Dictionaries (One-to-Many) are normalized into a sub-table with direct parent foreign key references, completely avoiding redundant *_mapping junction tables.
• BSON Sanitization: Recursively flattens MongoDB-specific data types (ObjectId, Decimal128, datetime) to Python-native scalars to avoid unwanted schema nesting.
• Session-backed Batch Streaming: Streams documents in chunks using PyMongo cursors inside an explicit client session, preventing cursor timeout errors on large datasets.
• Transactional Batch Control: Performs all insertions within batch-controlled database transactions. If a single document parsing or insertion fails in a batch, the entire batch transaction is rolled back.

📋 Prerequisites

• Python: Version >= 3.7
• Databases: Running instances of MongoDB and MySQL.
• Target Schema: The destination MySQL database (e.g., sample_mysql_db) must be created on your MySQL server before running the tool. The tool automatically creates the tables dynamically, but the database container schema must already exist.
• MySQL Privileges: The user credentials provided must have CREATE, ALTER, INSERT, and SELECT permissions on the destination database to create tables, update column shapes during evolution, and insert data.

---

📦 Installation

Install the package directly from PyPI:

pip install mongo-to-mysql

(Note: Ensure your Python script installation directory is added to your system's PATH to run the command globally).

---

📖 End-to-End Migration Example

Here is a visual example of how a MongoDB document is transformed into structured MySQL tables.

1. MongoDB Source Document (Collection: companies)

{
  "_id": {"$oid": "64b0f92b7c4e2b001c8a1234"},
  "name": "TechNova Solutions",
  "headquarters": {
    "city": "Pune",
    "country": "India"
  },
  "industries": ["Software", "AI"],
  "projects": [
    { "title": "Astra", "budget": 12000 },
    { "title": "Horizon", "budget": 45000 }
  ]
}

2. Resulting MySQL Relational Tables

Table: companies

companies_id	companies_name
1	TechNova Solutions

Table: headquarters (Nested Object -> One-to-One FK)

headquarters_id	headquarters_city	headquarters_country	headquarters_companies_id (FK)
1	Pune	India	1

Table: industries (List of Scalars -> Lookup FK)

industries_id	industries_companies_id (FK)	industries_industries
1	1	Software
2	1	AI

Table: projects (List of Objects -> One-to-Many FK)

projects_id	projects_title	projects_budget	projects_companies_id (FK)
1	Astra	12000	1
2	Horizon	45000	1

---

🛠️ Usage Guide

The tool supports both direct CLI arguments and an interactive prompt mode.

1. Non-Interactive CLI Command

Provide arguments directly to run the migration:

mongo-to-mysql \
  --mongo-db sample_mongo_db \
  --mysql-db sample_mysql_db \
  --username root \
  --password rootpwd \
  --host localhost \
  --batch-size 50

2. Interactive Mode

If arguments are omitted, the tool will guide you with prompts:

mongo-to-mysql

3. Remote Connections & Custom Ports

To connect to remote databases running on custom ports or with MongoDB auth:

mongo-to-mysql \
  --mongo-db secure_db \
  --mongo-host 192.168.1.50 \
  --mongo-port 27017 \
  --mongo-user admin \
  --mongo-pass adminpwd \
  --mysql-db target_db \
  --host mysql-server.internal \
  --mysql-port 3306 \
  --username migrate_user \
  --password migratepwd

---

⚙️ CLI Reference Options

Argument	Short Flag	Description	Required / Optional
--mongo-db	-	Name of the source MongoDB database	Required (or prompted)
--mysql-db	-	Name of the target MySQL database	Required (or prompted)
--username	-	MySQL database connection username	Required (or prompted)
--password	-	MySQL database connection password	Required (or prompted)
--host	-	MySQL database connection host/IP	Required (or prompted)
--mysql-port	-	MySQL database connection port (default 3306)	Optional
--mongo-host	-	MongoDB host IP/hostname (default localhost)	Optional
--mongo-port	-	MongoDB port (default 27017)	Optional
--mongo-user	-	MongoDB authentication username	Optional
--mongo-pass	-	MongoDB authentication password	Optional
--batch-size	-	Batch size for chunked streaming (default 1000)	Optional
--help	-h	Show help guidelines and options	Optional
--version	-v	Show version number and exit	Optional

---

🔍 Relational Mapping Mechanics

1. Table Creation: Every collection in MongoDB is represented by a table in MySQL named after the collection.
2. Column Naming: All columns in MySQL are prepended with {tablename}_ (e.g., users_name in the users table) and declared as VARCHAR(1000) NULL to handle polymorphic schemas cleanly.
3. Primary Keys: Tables utilize auto-incremented integer keys named {tablename}_id.
4. Sub-document Normalization: Nested JSON dicts and list items are normalized into dedicated sub-tables, utilizing direct parent foreign key references (e.g., address_users_id inside address table) rather than redundant junction mapping tables.

---

📄 License

This project is licensed under the MIT License. See LICENSE for details.