SQL-Mongo-Query-Converter

SQL-Mongo Converter - A Lightweight SQL to MongoDB (and Vice Versa) Query Converter 🍃

License: MIT
Python Version
SQL
MongoDB
PyPI

SQL-Mongo Converter is a lightweight Python library for converting SQL queries into MongoDB query dictionaries and converting MongoDB query dictionaries into SQL statements. It is designed for developers who need to quickly migrate or prototype between SQL-based and MongoDB-based data models without the overhead of a full ORM.

Currently live on PyPI: https://pypi.org/project/sql-mongo-converter/

Table of Contents

Features

Installation

Prerequisites

Install via PyPI

pip install sql-mongo-converter

Installing from Source

Clone the repository and install dependencies:

git clone https://github.com/yourusername/sql-mongo-converter.git
cd sql-mongo-converter
pip install -r requirements.txt
python setup.py install

Usage

Converting SQL to MongoDB

The sql_to_mongo function converts various SQL statements into MongoDB query dictionaries. By default, write operations (INSERT, UPDATE, DELETE) are enabled via the allow_mutations parameter.

SELECT Queries

from sql_mongo_converter import sql_to_mongo

# Basic SELECT
sql_query = "SELECT name, age FROM users WHERE age > 30 AND name = 'Alice';"
mongo_query = sql_to_mongo(sql_query)
print(mongo_query)
# Output:
# {
#   "collection": "users",
#   "find": { "age": {"$gt": 30}, "name": "Alice" },
#   "projection": { "name": 1, "age": 1 }
# }

# DISTINCT query
sql_query = "SELECT DISTINCT department FROM employees;"
mongo_query = sql_to_mongo(sql_query)
# Output: {"collection": "employees", "operation": "distinct", "field": "department"}

# Aggregation with GROUP BY and HAVING
sql_query = "SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;"
mongo_query = sql_to_mongo(sql_query)
# Output: Aggregation pipeline with $group and $match stages

Advanced WHERE Clauses

# BETWEEN operator
sql_query = "SELECT * FROM products WHERE price BETWEEN 10 AND 100;"
mongo_query = sql_to_mongo(sql_query)
# Output: {"find": {"price": {"$gte": 10, "$lte": 100}}}

# LIKE with wildcards (% and _)
sql_query = "SELECT * FROM users WHERE name LIKE 'John%';"
mongo_query = sql_to_mongo(sql_query)
# Output: {"find": {"name": {"$regex": "John.*", "$options": "i"}}}

# IN and NOT IN
sql_query = "SELECT * FROM users WHERE role IN ('admin', 'manager');"
mongo_query = sql_to_mongo(sql_query)
# Output: {"find": {"role": {"$in": ["admin", "manager"]}}}

# IS NULL and IS NOT NULL
sql_query = "SELECT * FROM users WHERE email IS NOT NULL;"
mongo_query = sql_to_mongo(sql_query)
# Output: {"find": {"email": {"$ne": None}}}

# OR and NOT operators
sql_query = "SELECT * FROM users WHERE age > 30 OR status = 'active';"
mongo_query = sql_to_mongo(sql_query)
# Output: {"find": {"$or": [{"age": {"$gt": 30}}, {"status": "active"}]}}

INSERT Operations

# Single row insert
sql_query = "INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@example.com');"
mongo_query = sql_to_mongo(sql_query)
# Output: {"collection": "users", "operation": "insertOne", "document": {...}}

# Multiple rows insert
sql_query = "INSERT INTO users (name, age) VALUES ('Bob', 25), ('Charlie', 35);"
mongo_query = sql_to_mongo(sql_query)
# Output: {"operation": "insertMany", "documents": [{...}, {...}]}

UPDATE Operations

# UPDATE with WHERE clause
sql_query = "UPDATE users SET age = 31, status = 'active' WHERE name = 'Alice';"
mongo_query = sql_to_mongo(sql_query)
# Output: {"collection": "users", "operation": "updateMany", "filter": {...}, "update": {"$set": {...}}}

DELETE Operations

# DELETE with WHERE clause
sql_query = "DELETE FROM users WHERE age < 18;"
mongo_query = sql_to_mongo(sql_query)
# Output: {"collection": "users", "operation": "deleteMany", "filter": {"age": {"$lt": 18}}}

JOIN Operations

# INNER JOIN
sql_query = """
SELECT u.name, o.order_id
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;
"""
mongo_query = sql_to_mongo(sql_query)
# Output: Aggregation pipeline with $lookup stage

# LEFT JOIN
sql_query = """
SELECT u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;
"""
mongo_query = sql_to_mongo(sql_query)
# Output: Aggregation pipeline with $lookup preserving unmatched documents

CREATE Operations

# CREATE TABLE with schema
sql_query = """
CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    age INT,
    email VARCHAR(255)
);
"""
mongo_query = sql_to_mongo(sql_query)
# Output: createCollection with schema validation

# CREATE INDEX
sql_query = "CREATE INDEX idx_age ON users (age DESC, name ASC);"
mongo_query = sql_to_mongo(sql_query)
# Output: {"operation": "createIndex", "keys": {"age": -1, "name": 1}}

DROP Operations

# DROP TABLE
sql_query = "DROP TABLE users;"
mongo_query = sql_to_mongo(sql_query, allow_mutations=True)
# Output: {"collection": "users", "operation": "drop"}

# DROP INDEX
sql_query = "DROP INDEX idx_age ON users;"
mongo_query = sql_to_mongo(sql_query, allow_mutations=True)
# Output: {"collection": "users", "operation": "dropIndex", "index_name": "idx_age"}

Converting MongoDB to SQL

The mongo_to_sql function translates MongoDB operations back into SQL statements.

Find Operations

from sql_mongo_converter import mongo_to_sql

# Basic find with operators
mongo_obj = {
    "collection": "users",
    "find": {
        "$or": [
            {"age": {"$gte": 25}},
            {"status": "ACTIVE"}
        ],
        "tags": {"$in": ["dev", "qa"]}
    },
    "projection": {"age": 1, "status": 1, "tags": 1},
    "sort": [("age", 1), ("name", -1)],
    "limit": 10,
    "skip": 5
}
sql_query = mongo_to_sql(mongo_obj)
print(sql_query)
# Output:
# SELECT age, status, tags FROM users WHERE ((age >= 25) OR (status = 'ACTIVE')) AND (tags IN ('dev', 'qa'))
# ORDER BY age ASC, name DESC LIMIT 10 OFFSET 5;

Insert Operations

# insertOne
mongo_obj = {
    "collection": "users",
    "operation": "insertOne",
    "document": {"name": "Alice", "age": 30, "email": "alice@example.com"}
}
sql_query = mongo_to_sql(mongo_obj)
# Output: INSERT INTO users (name, age, email) VALUES ('Alice', 30, 'alice@example.com');

# insertMany
mongo_obj = {
    "collection": "users",
    "operation": "insertMany",
    "documents": [
        {"name": "Bob", "age": 25},
        {"name": "Charlie", "age": 35}
    ]
}
sql_query = mongo_to_sql(mongo_obj)
# Output: INSERT INTO users (name, age) VALUES ('Bob', 25), ('Charlie', 35);

Update Operations

mongo_obj = {
    "collection": "users",
    "operation": "updateMany",
    "filter": {"name": "Alice"},
    "update": {"$set": {"age": 31, "status": "active"}}
}
sql_query = mongo_to_sql(mongo_obj)
# Output: UPDATE users SET age = 31, status = 'active' WHERE name = 'Alice';

Delete Operations

mongo_obj = {
    "collection": "users",
    "operation": "deleteMany",
    "filter": {"age": {"$lt": 18}}
}
sql_query = mongo_to_sql(mongo_obj)
# Output: DELETE FROM users WHERE age < 18;

API Reference

sql_to_mongo(sql_query: str, allow_mutations: bool = True) -> dict

mongo_to_sql(mongo_obj: dict) -> str

Supported SQL Operations

Query Operations

Write Operations

DDL Operations

Operators

Testing

The package includes a comprehensive pytest test suite with 103 passing tests and 59%+ code coverage.

Running Tests

  1. Create a virtual environment (optional but recommended):

    python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

  2. Install test dependencies:

    pip install -r requirements.txt
pip install pytest pytest-cov

  3. Run tests:

    # Run all tests
pytest tests/ -v

# With coverage report
pytest tests/ --cov=sql_mongo_converter --cov-report=html

# Quick run
pytest tests/ -q --tb=line

Test Coverage

The test suite includes:

All tests cover:

Demo Scripts

Example scripts are provided in the examples/ directory:

# Basic usage examples
python examples/basic_usage.py

# Advanced features (validation, logging, benchmarking)
python examples/advanced_usage.py

These scripts demonstrate various conversion scenarios and best practices.

Building & Publishing

Building the Package

  1. Ensure you have setuptools and wheel installed:

    pip install setuptools wheel

  2. Build the package:

    python setup.py sdist bdist_wheel

    This creates a dist/ folder with the distribution files.

Publishing to PyPI

  1. Install Twine:

    pip install twine

  2. Upload your package:

    twine upload dist/*

  3. Follow the prompts for your PyPI credentials.

Contributing

Contributions are welcome! To contribute:

  1. Fork the Repository

  2. Create a Feature Branch:

    git checkout -b feature/my-new-feature

  3. Commit Your Changes:

    git commit -am "Add new feature or fix bug"

  4. Push Your Branch:

    git push origin feature/my-new-feature
  5. Submit a Pull Request on GitHub.

For major changes, please open an issue first to discuss your ideas.

License

This project is licensed under the MIT License.

Final Remarks

SQL-Mongo Converter is a comprehensive, production-ready tool that bridges SQL and MongoDB query languages with full bidirectional conversion support.

Key Highlights

Use Cases

What’s New in v2.1.0

This release massively expands query support beyond the basic SELECT-only functionality:

The converter is ideal for developers migrating between SQL and MongoDB data models, building database abstraction layers, or learning NoSQL query patterns. Extend and customize as needed to support additional SQL constructs or MongoDB operators.

Happy converting! 🍃

This site is open source. Improve this page.