Metadata-Version: 2.1
Name: mongo-to-mysql
Version: 0.1.1
Summary: A command line tool to migrate MongoDB collections to MySQL tables dynamically
License: MIT
Requires-Python: >=3.7
Description-Content-Type: text/markdown
Requires-Dist: pymongo>=4.0
Requires-Dist: mysql-connector-python>=8.0.28

# mongo-to-mysql

[![PyPI version](https://img.shields.io/pypi/v/mongo-to-mysql.svg)](https://pypi.org/project/mongo-to-mysql/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

`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.

---

## 📦 Installation

Install the package directly from PyPI:
```bash
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`)
```json
{
  "_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:
```bash
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:
```bash
mongo-to-mysql
```

### 3. Remote Connections & Custom Ports
To connect to remote databases running on custom ports or with MongoDB auth:
```bash
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 (`0.1.0`) | 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](LICENSE) for details.
