sql-mongo-converter

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

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
• Usage
  • Converting SQL to MongoDB
  • Converting MongoDB to SQL
• API Reference
• Testing
• Building & Publishing
• Contributing
• License
• Final Remarks

Features

• Comprehensive SQL to MongoDB Conversion:

  • SELECT queries: Simple and complex queries with WHERE, ORDER BY, GROUP BY, HAVING, LIMIT, OFFSET
  • INSERT operations: Single and bulk inserts with column specifications
  • UPDATE operations: Targeted updates with WHERE clauses
  • DELETE operations: Conditional and bulk deletions
  • JOIN operations: INNER JOIN and LEFT JOIN converted to MongoDB $lookup aggregation
  • CREATE operations: TABLE and INDEX creation with schema validation
  • DROP operations: TABLE and INDEX dropping
  • DISTINCT queries: Single and multiple field distinct operations
  • Aggregation functions: COUNT, SUM, AVG, MIN, MAX with GROUP BY/HAVING
  • Advanced WHERE operators: BETWEEN, LIKE (with wildcards), IN, NOT IN, IS NULL, IS NOT NULL, OR, NOT

• Bidirectional MongoDB to SQL Conversion: Translate MongoDB operations back to SQL:

  • insertOne/insertMany → INSERT statements
  • updateMany → UPDATE statements with $set, $inc operators
  • deleteMany → DELETE statements
  • Aggregation pipelines → SELECT with GROUP BY/HAVING
  • Complex queries with $match, $group, $lookup, $project, $sort, $limit

• Production-Ready Features:

  • Query validation and SQL injection prevention
  • Configurable mutation operations (allow_mutations flag)
  • Comprehensive error handling with custom exceptions
  • Production-grade logging system
  • Performance benchmarking utilities
  • Command-line interface (CLI)

• Extensible & Robust: Built to handle a wide range of query patterns with 103 passing tests and 59%+ code coverage. Easily extended to support additional SQL functions, advanced operators, and more complex query structures.

Installation

Prerequisites

• Python 3.7 or higher
• pip

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, '[e-mail]');"
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": "[e-mail]"}
}
sql_query = mongo_to_sql(mongo_obj)
# Output: INSERT INTO users (name, age, email) VALUES ('Alice', 30, '[e-mail]');

# 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

• Description: Parses SQL statements (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, JOIN) and converts them into MongoDB query dictionaries.

• Parameters:

  • sql_query: A valid SQL query string
  • allow_mutations: (Optional) Enable/disable write operations (INSERT, UPDATE, DELETE, CREATE, DROP). Default: True

• Returns: A dictionary containing the MongoDB operation specification. Structure varies by operation type:

  SELECT queries:

  {
    "collection": str,           # Table name
    "find": dict,                # Filter from WHERE clause
    "projection": dict,          # Columns to return
    "sort": list,                # Optional: sort specification
    "limit": int,                # Optional: result limit
    "skip": int                  # Optional: offset
  }
  

  Aggregation queries (with GROUP BY/HAVING/DISTINCT/JOIN):

  {
    "collection": str,
    "operation": "aggregate",
    "pipeline": [...]            # Aggregation pipeline stages
  }
  

  INSERT operations:

  {
    "collection": str,
    "operation": "insertOne" | "insertMany",
    "document": dict,            # For insertOne
    "documents": [dict]          # For insertMany
  }
  

  UPDATE operations:

  {
    "collection": str,
    "operation": "updateMany",
    "filter": dict,              # WHERE clause
    "update": {"$set": {...}}    # SET clause
  }
  

  DELETE operations:

  {
    "collection": str,
    "operation": "deleteMany",
    "filter": dict               # WHERE clause
  }
  

  CREATE/DROP operations:

  {
    "collection": str,
    "operation": "createCollection" | "createIndex" | "drop" | "dropIndex",
    # ... additional fields based on operation
  }
  

mongo_to_sql(mongo_obj: dict) -> str

• Description: Converts a MongoDB query dictionary into SQL statements. Supports bidirectional conversion for all operation types.
• Parameters:
  • mongo_obj: A dictionary representing a MongoDB operation with keys such as:
    • collection: Collection/table name (required)
    • operation: Operation type (optional, defaults to "find")
    • find: Filter conditions for SELECT/UPDATE/DELETE
    • projection: Columns to select
    • sort: Sort specification
    • limit/skip: Result pagination
    • document/documents: For INSERT operations
    • filter/update: For UPDATE operations
    • pipeline: For aggregation operations
• Returns: A SQL statement string (SELECT, INSERT, UPDATE, DELETE)
• Supported MongoDB Operators:
  • Comparison: $gt, $gte, $lt, $lte, $eq, $ne
  • Array: $in, $nin
  • Logical: $and, $or, $not
  • Pattern: $regex
  • Update: $set, $inc, $unset
  • Aggregation: $match, $group, $lookup, $project, $sort, $limit, $skip

Supported SQL Operations

Query Operations

• SELECT: Basic and complex queries with projections, filters, sorting
• DISTINCT: Single and multiple field distinct queries
• WHERE: Complex conditions with AND, OR, NOT, BETWEEN, LIKE, IN, IS NULL
• JOIN: INNER JOIN and LEFT JOIN (converted to $lookup)
• GROUP BY: With aggregation functions (COUNT, SUM, AVG, MIN, MAX)
• HAVING: Post-aggregation filtering
• ORDER BY: Single and multiple column sorting (ASC/DESC)
• LIMIT/OFFSET: Result pagination

Write Operations

• INSERT: Single and bulk inserts with column specifications
• UPDATE: Conditional updates with SET and WHERE clauses
• DELETE: Conditional and bulk deletions

DDL Operations

• CREATE TABLE: With column definitions and type mapping to BSON
• CREATE INDEX: With single/multiple columns and sort order
• DROP TABLE: Collection removal
• DROP INDEX: Index removal

Operators

• Comparison: =, >, >=, <, <=, !=, <>
• Range: BETWEEN ... AND ...
• Pattern: LIKE with wildcards (%, _)
• List: IN (...), NOT IN (...)
• Null: IS NULL, IS NOT NULL
• Logical: AND, OR, NOT
• Aggregation: COUNT, SUM, AVG, MIN, MAX

Testing

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

Running Tests

• Create a virtual environment (optional but recommended):

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

• Install test dependencies:

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

• 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:

• test_sql_to_mongo.py: 40+ tests for SQL to MongoDB conversion
• test_mongo_to_sql.py: 35+ tests for MongoDB to SQL conversion
• test_new_operations.py: 33+ tests for INSERT, UPDATE, DELETE, JOIN, CREATE, DROP operations
• test_validator.py: 30+ tests for query validation and security
• test_integration.py: End-to-end integration tests
• test_benchmark.py: Performance benchmarking tests

All tests cover:

• Basic operations (SELECT, INSERT, UPDATE, DELETE)
• Advanced features (JOIN, GROUP BY, HAVING, DISTINCT)
• Complex WHERE clauses (BETWEEN, LIKE, IN, OR, NOT, IS NULL)
• Aggregation functions (COUNT, SUM, AVG, MIN, MAX)
• DDL operations (CREATE, DROP)
• Edge cases and error handling
• Bidirectional conversions (SQL↔MongoDB)

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

• Ensure you have setuptools and wheel installed:

  pip install setuptools wheel
  

• Build the package:

  python setup.py sdist bdist_wheel
  

  This creates a dist/ folder with the distribution files.

Publishing to PyPI

• Install Twine:

  pip install twine
  

• Upload your package:

  twine upload dist/*
  

• Follow the prompts for your PyPI credentials.

Contributing

Contributions are welcome! To contribute:

• Fork the Repository

• Create a Feature Branch:

  git checkout -b feature/my-new-feature
  

• Commit Your Changes:

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

• Push Your Branch:

  git push origin feature/my-new-feature
  

• 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

• Complete CRUD Operations: Full support for SELECT, INSERT, UPDATE, DELETE with complex conditions
• Advanced SQL Features: JOIN, GROUP BY, HAVING, DISTINCT, aggregation functions, and more
• Comprehensive Operator Support: BETWEEN, LIKE, IN, IS NULL, OR, NOT - all major SQL operators
• DDL Operations: CREATE and DROP for tables and indexes
• Production-Ready: 103 passing tests, 59%+ code coverage, comprehensive error handling
• Bidirectional: Convert SQL→MongoDB and MongoDB→SQL seamlessly
• Secure: Built-in query validation and SQL injection prevention

Use Cases

• Database Migration: Easily migrate between SQL and MongoDB databases
• Query Translation: Convert existing SQL queries to MongoDB for NoSQL adoption
• Prototyping: Quickly test query equivalents between SQL and MongoDB
• Learning Tool: Understand how SQL concepts map to MongoDB operations
• API Development: Dynamically convert between query formats in your application

What's New in v2.1.0

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

• ✅ Full CRUD operations (INSERT, UPDATE, DELETE)
• ✅ JOIN support with MongoDB $lookup aggregation
• ✅ CREATE/DROP TABLE and INDEX operations
• ✅ DISTINCT queries with single/multiple fields
• ✅ GROUP BY with HAVING clause
• ✅ Aggregation functions: COUNT, SUM, AVG, MIN, MAX
• ✅ Advanced WHERE operators: BETWEEN, LIKE, IN, NOT IN, IS NULL, OR, NOT
• ✅ Complex nested conditions with proper precedence
• ✅ Bidirectional conversion for all operations

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! 🍃