dbapi-mongodb
DBAPI-style adapter that lets you execute a limited subset of SQL against MongoDB by translating SQL to Mongo queries. Built on pymongo (3.13.x for MongoDB 3.6 compatibility) and SQLGlot.
Purpose: let existing DB-API / SQLAlchemy Core / FastAPI code treat MongoDB as “just another dialect.”
- PyPI package name:
dbapi-mongodb - Module import name:
mongo_dbapi
Release Policy (Join-first / MongoDB 4.4)
- This release locks on practical JOIN compatibility first, targeting MongoDB 4.4.
- Guaranteed JOIN scope: INNER/LEFT (up to 3 hops, composite ON, non-equi ON), RIGHT OUTER (single JOIN, equality ON), FULL OUTER (single JOIN, equality ON), JOIN with
ORDER BY/LIMIT/OFFSET, and JOIN +GROUP BY/HAVING(A27 pattern:COALESCE(...) + COUNT(*)). - Other advanced SQL features remain available where implemented, but further hardening is treated as future work. Unsupported cases return
[mdb][E2].
Features
-
DBAPI-like
Connection/Cursor -
SQL → Mongo:
SELECT/INSERT/UPDATE/DELETE,CREATE/DROP TABLE/INDEX(ASC/DESC, UNIQUE, composite),WHERE(comparisons/AND/OR/IN/BETWEEN/LIKE/ILIKE/regex literal),ORDER BY,LIMIT/OFFSET, JOIN (INNER/LEFT up to 3 hops with composite/non-equi ON, RIGHT/FULL OUTER for single JOIN with equality ON),GROUP BY+ aggregates +HAVING(including FULL OUTER A27 pattern), simple CASE aggregates (SUM(CASE WHEN ... THEN ... ELSE ... END)),UNION ALL/UNION(multi-branch), subqueries (WHERE IN/EXISTS/NOT EXISTS, scalar subquery in comparisons,FROM (SELECT ...)), non-recursiveWITHCTE -
%spositional and%(name)snamed parameters; unsupported constructs raise Error IDs (e.g.[mdb][E2]) -
Error IDs for common failures: invalid URI, unsupported SQL, unsafe DML without WHERE, parse errors, connection/auth failures
-
DBAPI fields:
rowcount,lastrowid,description(column order: explicit order, or alpha forSELECT *; JOIN uses left→right) -
Transactions:
begin/commit/rollbackwrap Mongo sessions; MongoDB 3.6 and other unsupported envs are treated as no-op success -
Async dialect (thread-pool backed) for Core CRUD/DDL/Index with FastAPI-friendly usage; minimal ORM CRUD for single-table entities (relationships out of scope)
-
Use cases
- Swap in Mongo as “another dialect” for existing SQLAlchemy Core–based infra (Engine/Connection + Table/Column)
- Point existing Core-based batch/report jobs at Mongo data with minimal changes
- Minimal ORM CRUD for single-table entities (PK →
_id) - Async dialect for FastAPI/async stacks (thread-pool implementation; native async later)
Requirements
- Python 3.10+
- MongoDB 3.6 (bundled
mongodb-3.6binary) or later (note: bundled binary is 3.6, so transactions are unsupported) - Virtualenv at
.venv(already present); dependencies are managed viapyproject.toml
Installation
pip install dbapi-mongodb
# (optional) with a virtualenv: python -m venv .venv && . .venv/bin/activate && pip install dbapi-mongodb
Start local MongoDB (bundled 3.6)
# Default port 27017; override with PORT
PORT=27018 ./startdb.sh
Start local MongoDB 4.4 (replica set, bundled)
# Default port 27019; uses bundled libssl1.1. LD_LIBRARY_PATH is set inside the script for mongod.
PORT=27019 ./start4xdb.sh
# Run tests against 4.x
MONGODB_URI=mongodb://127.0.0.1:27019 MONGODB_DB=mongo_dbapi_test .venv/bin/pytest -q
Usage example
from mongo_dbapi import connect
conn = connect("mongodb://127.0.0.1:27018", "mongo_dbapi_test")
cur = conn.cursor()
cur.execute("INSERT INTO users (id, name) VALUES (%s, %s)", (1, "Alice"))
cur.execute("SELECT id, name FROM users WHERE id = %s", (1,))
print(cur.fetchall()) # [(1, 'Alice')]
print(cur.rowcount) # 1
Supported SQL
- Statements:
SELECT,INSERT,UPDATE,DELETE,CREATE/DROP TABLE,CREATE/DROP INDEX - WHERE: comparisons (
=,<>,>,<,<=,>=),AND,OR,IN,BETWEEN,LIKE(%/_→$regex),ILIKE, regex literal/.../ - JOIN: INNER/LEFT (composite ON, non-equi ON, up to 3 joins), RIGHT OUTER (single JOIN, equality ON), FULL OUTER (single JOIN, equality ON)
- Aggregation:
GROUP BYwith COUNT/SUM/AVG/MIN/MAX andHAVING - Subqueries:
WHERE IN/EXISTS/NOT EXISTS(correlated EXISTS in limited simple form), scalar non-correlated subqueries in comparisons, andFROM (SELECT ...) - Set ops:
UNION ALL/UNION(supports 3+ branches; mixedUNION+UNION ALLis rejected) - CTE: non-recursive
WITH - Window:
ROW_NUMBER/RANK/DENSE_RANKon MongoDB 5.x+ ([mdb][E2]on 4.4) - ORDER/LIMIT/OFFSET
- Unsupported (4.4 target):
WITH RECURSIVE, RIGHT/FULL OUTER with non-equality ON, RIGHT/FULL join chains, complex FULL OUTER aggregate shapes (outsideCOALESCE(...) + COUNT(*)), complex correlated subqueries, mixedUNION+UNION ALL, ORM relationships
SQLAlchemy
- DBAPI module attributes:
apilevel="2.0",threadsafety=1,paramstyle="pyformat". - Scheme:
mongodb+dbapi://...dialect provided (sync + async/thread-pool). - Scope: Core text()/Table/Column CRUD/DDL/Index、ORM 最小 CRUD(単一テーブル)、JOIN/UNION ALL/HAVING/subquery/ROW_NUMBER を実通信で確認済み。async dialect は Core CRUD/DDL/Index のラップで、ネイティブ async は今後検討。
Async (FastAPI/Core) - beta
- Current implementation wraps the sync driver in a thread pool (native async driver is planned). Provided via
mongo_dbapi.async_dbapi.connect_async. API mirrors sync Core: awaitable CRUD/DDL/Index, JOIN/UNION ALL/HAVING/IN/EXISTS/FROM subquery. - Transactions: effective on MongoDB 4.x+ only; 3.6 is no-op. Be mindful that MongoDB transactions differ from RDBMS in locking/perf; avoid heavy transactional workloads.
- Window:
ROW_NUMBER/RANK/DENSE_RANKare available on MongoDB 5.x+; earlier versions return[mdb][E2] Unsupported SQL construct: WINDOW_FUNCTION. - FastAPI example:
from fastapi import FastAPI, Depends
from sqlalchemy.ext.asyncio import create_async_engine, AsyncConnection
from sqlalchemy import text
engine = create_async_engine("mongodb+dbapi://127.0.0.1:27019/mongo_dbapi_test")
app = FastAPI()
async def get_conn() -> AsyncConnection:
async with engine.connect() as conn:
yield conn
@app.get("/users/{user_id}")
async def get_user(user_id: str, conn: AsyncConnection = Depends(get_conn)):
rows = await conn.execute(text("SELECT id, name FROM users WHERE id = :id"), {"id": user_id})
row = rows.fetchone()
return dict(row) if row else {}
- Limitations: async ORM/relationship and statement cache are out of scope; heavy concurrency uses a thread pool under the hood.
Support levels
- Stable guarantee (Join-first): JOIN-heavy paths (INNER/LEFT up to 3 hops, RIGHT/FULL single JOIN constraints, JOIN + ORDER/LIMIT/OFFSET, JOIN + GROUP BY/HAVING in supported shapes) and single-collection CRUD.
- Available as best effort:
UNION/UNION ALL, non-recursive CTE, scalar subqueries, limited correlated EXISTS, MongoDB 5.x+ window functions. - Not supported / constraints:
WITH RECURSIVE, RIGHT/FULL OUTER with non-equality ON, RIGHT/FULL join chains, complex FULL OUTER aggregate shapes, complex correlated subqueries, mixedUNION+UNION ALL, ORM relationships; async remains thread-pool based.
Running tests
PORT=27018 ./startdb.sh # if 27017 is taken
MONGODB_URI=mongodb://127.0.0.1:27018 MONGODB_DB=mongo_dbapi_test .venv/bin/pytest -q
Tutorials
- English:
docs/tutorial.md - 日本語:
docs/tutorial_ja.md
Notes
- Transactions on MongoDB 3.6 are treated as no-op; 4.x+ (replica set) uses real sessions and the bundled 4.4 binary passes all tests.
- Error messages are fixed strings per
docs/spec.md. Keep logs at DEBUG only (default INFO is silent).
Roadmap (SQL support prioritization)
- Complex correlated subqueries and FULL OUTER aggregate generalization (beyond A27 shape)
WITH RECURSIVEand mixedUNION+UNION ALLchains- Additional window functions beyond ROW_NUMBER/RANK/DENSE_RANK (
LAG/LEAD/NTILE, etc.) - Performance guidance for large JOIN workloads (recommended indexes and slow-query thresholds, especially FULL OUTER)
If you need one of these sooner, please open an issue and share your use case.
License
MIT License (see LICENSE). Provided as-is without warranty; commercial use permitted.
GitHub Sponsors
Maintained in personal time. If this helps you run MongoDB from DB-API/SQLAlchemy stacks, consider supporting via GitHub Sponsors to keep fixes and version updates coming.
Metadata
Release files for dbapi-mongodb 0.2.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| dbapi_mongodb-0.2.0.tar.gz | 42.4 kB | Without provenance | Details for dbapi_mongodb-0.2.0.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| dbapi_mongodb-0.2.0-py3-none-any.whl | Python 3 | none | any | Details for dbapi_mongodb-0.2.0-py3-none-any.whl |
Total release size: 75.9 kB
Release files / dbapi_mongodb-0.2.0.tar.gz
| Download URL | dbapi_mongodb-0.2.0.tar.gz |
|---|---|
| Size | 42.4 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
d69bcbdb36ca438133070bff3d17ef56e9aac27d77cc2fffde8f694b160067e0
|
|
BLAKE2b-256 checksum How to use checksums |
6e607a7606be3ca9dd17a350a19402cb7f471a9ec17a31939bac8bdf9125864b
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/6.2.0 CPython/3.12.10
|
Release files / dbapi_mongodb-0.2.0-py3-none-any.whl
| Download URL | dbapi_mongodb-0.2.0-py3-none-any.whl |
|---|---|
| Size | 33.5 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
d709fff79730da98a4ff017c5a56a076f38cec85b4cde4600acf88349bfa0f54
|
|
BLAKE2b-256 checksum How to use checksums |
dc7e29475abda3b7130cc522d5d556e571768ac17fffea20c19352ec0ae73670
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
twine/6.2.0 CPython/3.12.10
|
Security for dbapi-mongodb 0.2.0
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.