Metadata-Version: 2.1
Name: mongo-to-mysql
Version: 0.1.0
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

`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:
   ```bash
   git clone https://github.com/sagarmemane135/mongo-to-mysql.git
   cd mongo-to-mysql
   ```
2. Build and install the package using `pip`:
   ```bash
   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:
```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 fall back to prompts:
```bash
mongo-to-mysql
```

### 3. Optional MongoDB Authentication
If your MongoDB instance has authentication enabled, provide the optional flags:
```bash
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.
