mongo-to-mysql 0.1.0

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.

The tool dynamically infers table schemas from NoSQL document structures, maps complex hierarchies (arrays, nested dictionaries) into relational schemas, supports schema evolution across batch boundaries, and operates transactionally.

---

Key Features

• Automated Schema Inference & Evolution: Automatically creates tables and columns based on document attributes. If new fields are introduced during migration, the tool alters the MySQL tables dynamically without interrupting the migration process.
• Recursive Batch Normalization: Normalizes nested document relationships:
  • Lists of Scalars (e.g., ["friend", "colleague"]) are mapped to a dedicated lookup table.
  • Lists of Dictionaries (e.g., nested sub-documents) are normalized into a distinct table linked to the parent record via a many-to-many junction mapping table.
  • Nested Dictionaries are normalized into distinct sub-tables.
• BSON Sanitization: Recursively flattens MongoDB-specific data types (e.g., ObjectId, Decimal128, datetime) into compatible Python scalars (e.g., standard hex strings, floats, formatted timestamps) to prevent redundant nesting.
• Batch Streaming: Uses MongoDB cursors to stream documents in chunks (default 1000, configurable) keeping local memory footprint constant.
• Transactional Reliability: Performs insertions in batches using SQL transactions. If any record fails in a batch, the entire batch transaction is rolled back.
• CLI & Interactive Interface: Can be run entirely non-interactively via CLI flags, or interactively with fallback user prompts. Supports version printing and command help.

---

Prerequisites

• Python >= 3.7
• Running instances of MongoDB and MySQL databases.

---

Installation

To build and install the tool locally from source:

1. Clone the repository:

   git clone https://github.com/sagarmemane135/mongo-to-mysql.git
   cd mongo-to-mysql
   

2. Build and install the package using pip:

   pip install .
   

   (This registers the global mongo-to-mysql command in your environment).

---

Usage Guide

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

1. Direct CLI Arguments

Provide arguments directly to run the migration non-interactively:

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 fall back to prompts:

mongo-to-mysql

3. Optional MongoDB Authentication

If your MongoDB instance has authentication enabled, provide the optional flags:

mongo-to-mysql \
  --mongo-db secure_mongo_db \
  --mysql-db target_mysql_db \
  --username db_user \
  --password db_pass \
  --host localhost \
  --mongo-user admin \
  --mongo-pass securepwd

CLI Command Options

Argument	Description	Required / Optional
-h, --help	Show the help message and exit	Optional
-v, --version	Show the tool version and exit	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

---

Relational Schema Mapping Rules

1. Main 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.