milvusql

A PEP 249 DBAPI (sync + async) for Milvus — parses/generates MilvusQL via sqlglot-milvus and executes the resulting AST against pymilvus.

📚 Documentation · PyPI · sqlglot-milvus

Why a DBAPI, not a client wrapper?

Writing MilvusQL instead of chaining pymilvus calls means the same SELECT ... ORDER BY embedding <=> :q LIMIT n string works whether it's typed by hand, generated by an ORM, or built by an LLM tool call — and it works the same way from cursor.execute() or await acursor.execute(), off one shared parser and dispatch table (translate.ast_to_pymilvus).

Installation

pip install milvusql

Quick start

import milvusql

conn = milvusql.connect(uri="./items.db")  # Milvus Lite, or a real server's URI
cur = conn.cursor()

cur.execute(
    """
    CREATE TABLE items (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        embedding VECTOR(8),
        category VARCHAR(64)
    ) WITH (shards=1, consistency_level='Strong')
    """
)
cur.execute(
    "CREATE INDEX idx_embedding ON items (embedding) USING HNSW WITH (metric_type='COSINE')"
)
cur.executemany(
    "INSERT INTO items (embedding, category) VALUES (:embedding, :category)",
    [{"embedding": [0.1] * 8, "category": "book"}],
)

cur.execute("SELECT id FROM items WHERE category = :cat LIMIT 10", {"cat": "book"})
print(cur.fetchall())

cur.execute(
    "SELECT id FROM items ORDER BY embedding <=> :q LIMIT 5",
    {"q": [0.1] * 8},
)
print(cur.fetchall())

Full-text search is one column and one generated field away — Milvus's BM25 pipeline (analyzer, schema Function, sparse index) spelled as SQL:

cur.execute(
    """
    CREATE TABLE docs (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        content TEXT,
        content_sparse SPARSEVEC GENERATED ALWAYS AS (BM25(content)),
        embedding VECTOR(768)
    )
    """
)
cur.execute(
    "CREATE INDEX idx_fts ON docs (content_sparse) "
    "USING SPARSE_INVERTED_INDEX WITH (metric_type='BM25')"
)

# keyword filter
cur.execute(
    "SELECT id, content FROM docs WHERE MATCH(content) AGAINST (:q)",
    {"q": "vector database"},
)
# BM25-ranked retrieval
cur.execute(
    "SELECT id, content FROM docs "
    "ORDER BY BM25_SCORE(content_sparse, :q) DESC LIMIT 10",
    {"q": "how do i tune hnsw"},
)
# dense + full-text hybrid, fused with RRF
cur.execute(
    """
    SELECT id, content FROM docs HYBRID SEARCH (
        embedding <=> :dv WEIGHT 0.6,
        BM25_SCORE(content_sparse, :q) WEIGHT 0.4
    ) RERANK RRF(k=60) LIMIT 10
    """,
    {"dv": query_embedding, "q": "how do i tune hnsw"},
)

And the surface a person (or an LLM agent) orients with:

SHOW TABLES;                 -- list_collections()
SHOW DATABASES;              -- list_databases()
DESCRIBE docs;               -- fields, types, keys, BM25 generators
CREATE DATABASE tenant_a;    USE tenant_a;    DROP DATABASE tenant_a;
DROP INDEX idx_fts ON docs;

The same program, asyncio-native, over milvusql.aio (built on pymilvus.AsyncMilvusClient):

from milvusql import aio

conn = aio.connect(uri="./items.db")
cur = conn.cursor()

await cur.execute("SELECT id FROM items WHERE category = :cat", {"cat": "book"})
async for row in cur:
    print(row)

await conn.close()

JOIN, GROUP BY and subqueries

Milvus reads one collection per RPC, joins nothing and reduces nothing. milvusql closes that gap without pretending it isn't there: a statement that needs more than one collection, a grouped aggregate or a subquery is planned into one Milvus read per collection, and the relational part is evaluated client-side with Polars.

cur.execute(
    """
    SELECT c.title, COUNT(*) AS n, AVG(i.price) AS avg_price
    FROM items AS i
    JOIN categories AS c ON i.cat_id = c.id
    WHERE i.price > :floor
    GROUP BY c.title
    HAVING COUNT(*) > 1
    ORDER BY n DESC
    LIMIT 10
    """,
    {"floor": 20.0},
)

What reaches Milvus, and what does not:

Window functions are the one worth calling out against a vector database: ROW_NUMBER() OVER (PARTITION BY category ORDER BY distance) over a search's hits is top-k per group, which Milvus cannot express and an ANN index cannot answer directly.

SELECT * works across a join too, but it means what it says: each side is asked for output_fields=["*"], so every field of every collection comes back — vectors included. Naming the columns is the difference between moving a few scalars and moving every embedding.

The key pushdown is what keeps this usable: an ANN search returning 50 hits joined against a million-row collection reads 50 rows from it, not a million.

Three behaviours worth knowing before you rely on them:

• Reads page past Milvus's 16384-row per-call ceiling. A scan that comes back at the ceiling continues with primary-key-cursor pages (the same iterator protocol pymilvus's own QueryIterator speaks), so joins, groupings, aggregates, client-side ORDER BY, UPDATE and a bare SELECT with no LIMIT all cover every matching row — on both the sync and async cursors. The one server that cannot serve ordered pages is Milvus Lite: there a result past the ceiling still raises NotSupportedError (never a silent truncation), same as before. No snapshot spans the pages; rows written mid-read may or may not appear, exactly as with pymilvus's own iterator.
• ORDER BY <vector> ... LIMIT k in a joined query means "Milvus's top-k from that collection, then join" — not "top-k of the joined result". The two differ only when the join or a cross-collection predicate drops rows; ranking after the join would mean reading the whole vector collection, which is the one thing an ANN index exists to avoid.
• Columns must be unambiguous. With more than one collection in scope, a bare id raises ProgrammingError asking you to qualify it: output fields are requested from Milvus before any row comes back, and no collection schema is available at that point to resolve the name against.

Through the ORMs

Both packages inherit this at the DBAPI level — no ORM-side change was needed — so the ordinary constructs compile and run:

Django's compiler groups and orders by ordinal position (GROUP BY 1, ORDER BY 2 DESC) rather than by name, and both ORMs quote every identifier; the planner resolves both spellings.

Correlated [NOT] EXISTS — what SQLAlchemy's .any()/.has() and Django's Exists(... OuterRef(...)) compile to — is decorrelated into a semi/anti join (the rewrite SQL engines perform for the same shape), so those ORM constructs run. The correlation must be an equality; SQL's own NULL semantics are kept (a null key never matches EXISTS, always survives NOT EXISTS).

Not supported, and rejected explicitly rather than mistranslated:

• Correlated subqueries beyond EXISTS equality — Django's Subquery(...) annotations (a correlated value per outer row) and non-equi EXISTS correlations. The error names the construct.
• WITH RECURSIVE (re-reads until a fixpoint), INTERSECT ALL / EXCEPT ALL (duplicate-count semantics a semi/anti join cannot express), window frame clauses (ROWS/RANGE BETWEEN), LAG/ LEAD/NTILE, JOIN ... USING past two sources, and SELECT * inside a subquery that joins — the last two because two collections can own the same column name, and nothing at translate time says which.

Anything that doesn't need this path — a filter SELECT, a vector search, a hybrid search, a bare COUNT(*) — is still exactly one RPC and never builds a DataFrame.

API

milvusql.connect()

milvusql.connect(
    uri="http://localhost:19530",  # or a Milvus Lite file path
    token="",                      # "user:password", or a full token string
    db_name="",
    consistency_level=None,        # per-connection default; a query's own CONSISTENCY LEVEL wins
    **kwargs,                      # passed straight through to pymilvus.MilvusClient
) -> Connection

milvusql.aio.connect()/AsyncConnection/AsyncCursor mirror the same shape, async/await throughout — deliberately not PEP 249 itself (execute() as a coroutine can't be), but built on the same parser, dispatch table, and error hierarchy as the sync path.

Column types

Errors

Standard PEP 249 hierarchy, importable from milvusql:

Warning
Error
├── InterfaceError
└── DatabaseError
    ├── DataError
    ├── OperationalError
    ├── IntegrityError
    ├── InternalError
    ├── ProgrammingError
    └── NotSupportedError

Every pymilvus exception and gRPC error raised while executing a statement is translated into one of these before it reaches your code.

Packages

This is the core of a uv workspace. Two packages build on milvusql's DBAPI:

Each is installed and versioned separately; both depend on this package as their DBAPI layer.

Examples

See also milvusql-sqlalchemy's own examples (a FastAPI image-search service, a pydantic-ai agent).

Development

Requires Python 3.12+, uv, task.

task install           # uv sync --all-groups --all-packages
task lint              # ruff + ty + bandit for core + all packages
task tests             # all tests (core + sqlalchemy + django) -- integration tests need Docker (testcontainers)

Individual package tasks:

task core:lint         task core:test
task sqlalchemy:lint   task sqlalchemy:test
task django:lint       task django:test

See benchmarks/ for what the planner's key/predicate pushdown buys, measured through the public DBAPI, and CONTRIBUTING.md for ground rules.

License

MIT