Metadata-Version: 2.4
Name: milvusql
Version: 1.0.0
Summary: PEP 249 DBAPI (sync + async) for Milvus, backed by sqlglot-milvus
Keywords: milvus,dbapi,pep249,vector-search,asyncio
Author: Neko1313
Author-email: Neko1313 <nikita.ribalchencko@yandex.ru>
License-Expression: MIT
License-File: LICENSE
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Framework :: AsyncIO
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Dist: pymilvus>=2.6,<3
Requires-Dist: sqlglot-milvus>=0.1.0,<0.2
Requires-Dist: polars>=1.30,<2
Requires-Python: >=3.12
Project-URL: Homepage, https://github.com/Callix-Tools/milvusql
Project-URL: Repository, https://github.com/Callix-Tools/milvusql
Project-URL: Issues, https://github.com/Callix-Tools/milvusql/issues
Description-Content-Type: text/markdown

<div align="center">
  <img src="https://Callix-Tools.github.io/milvusql-docs/img/logo.svg" alt="milvusql logo" width="220"/>

  <h1>milvusql</h1>

  <p>A <a href="https://peps.python.org/pep-0249/">PEP 249</a> DBAPI (sync + async) for <a href="https://milvus.io">Milvus</a> — parses/generates MilvusQL via <a href="https://github.com/Callix-Tools/sqlglot-milvus"><code>sqlglot-milvus</code></a> and executes the resulting AST against <code>pymilvus</code>.</p>

  [![PyPI](https://img.shields.io/pypi/v/milvusql?color=blue)](https://pypi.org/project/milvusql/)
  [![Python](https://img.shields.io/pypi/pyversions/milvusql)](https://pypi.org/project/milvusql/)
  [![PyPI Downloads](https://static.pepy.tech/personalized-badge/milvusql?period=total&units=INTERNATIONAL_SYSTEM&left_color=lightgrey&right_color=blue&left_text=downloads)](https://pepy.tech/projects/milvusql)
  [![License](https://img.shields.io/github/license/Callix-Tools/milvusql)](LICENSE)
  [![CI](https://img.shields.io/github/actions/workflow/status/Callix-Tools/milvusql/ci-core.yml?label=CI)](https://github.com/Callix-Tools/milvusql/actions)

  [📚 Documentation](https://Callix-Tools.github.io/milvusql-docs/) · [PyPI](https://pypi.org/project/milvusql/) · [sqlglot-milvus](https://github.com/Callix-Tools/sqlglot-milvus)
</div>

---

## Why a DBAPI, not a client wrapper?

| Feature | **milvusql** | raw `pymilvus` |
|---|:---:|:---:|
| Query surface | SQL (MilvusQL) | Python method calls |
| Parameterized queries | ✅ `:name` binds | ⚠️ manual dict-building |
| Standard `Connection`/`Cursor` (PEP 249) | ✅ | ❌ |
| Sync + async, same dispatch table | ✅ | ⚠️ separate `MilvusClient`/`AsyncMilvusClient` |
| Drop-in for SQLAlchemy / Django | ✅ [`milvusql-sqlalchemy`](packages/milvusql-sqlalchemy), [`milvusql-django`](packages/milvusql-django) | ❌ |
| Auto-`LOAD` on first use, cached per connection | ✅ | manual `load_collection()` |
| Consistency-level fallback (per-connection default, per-query override) | ✅ | manual per-call |
| `JOIN` / `GROUP BY` / subqueries / correlated `EXISTS` | ✅ planned into one read per collection, combined client-side | ❌ Milvus has none of them |
| Full-text search (BM25, `MATCH ... AGAINST`) | ✅ one `TEXT` column + one generated `SPARSEVEC` | ⚠️ schema `Function` + analyzer flags by hand |
| Reads past the 16384-row per-call ceiling | ✅ transparent primary-key-cursor pages | ⚠️ `query_iterator` (sync client only) |
| Introspection (`SHOW TABLES`, `DESCRIBE`) | ✅ | Python method calls |

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

```bash
pip install milvusql
```

## Quick start

```python
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:

```python
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:

```sql
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`):

```python
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](https://pola.rs).

```python
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:

| Pushed to Milvus | Evaluated client-side |
|---|---|
| Every `WHERE` conjunct naming a single collection (`i.price > 20`) | Predicates spanning two collections (`i.price > c.budget`) |
| The columns the statement actually references (projection pushdown) | Joins (`INNER`/`LEFT`/`RIGHT`/`FULL`/`CROSS`, `ON` or `USING`) |
| `ORDER BY <vector> <=> :q LIMIT k` as a real ANN `search` | `GROUP BY`, `HAVING`, aggregates, window functions |
| Equi-join keys learned from the previous read, as `key in [...]` | `WITH` (CTEs), `UNION`/`INTERSECT`/`EXCEPT`, subqueries |
| | Scalar `ORDER BY`, `DISTINCT`, `LIMIT`/`OFFSET` |

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:

| SQLAlchemy | Django |
|---|---|
| `select(...).join(...)` / `.outerjoin(...)` | `filter(related__field=...)`, `select_related(...)` |
| `.group_by(...)` / `.having(...)` | `.values(...).annotate(Count(...))`, then `.filter(n__gt=...)` |
| `col.in_(select(...))` | `filter(fk__in=Model.objects.values("id"))` |
| `Query.count()` (still a server-side `count(*)`, no rows fetched) | `.count()` |

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()`

```python
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
```

| `Connection` | Description |
|---|---|
| `.cursor()` | Returns a new `Cursor` bound to this connection |
| `.commit()` | No-op — every statement is already applied when it returns |
| `.rollback()` | Raises `NotSupportedError` — Milvus has no multi-statement rollback; catch and compensate instead |
| `.close()` | Closes the underlying `MilvusClient` |
| Context manager | `with milvusql.connect(...) as conn: ...` |

| `Cursor` | Description |
|---|---|
| `.execute(operation, parameters=None)` | Runs one statement; `parameters` binds `:name` placeholders |
| `.executemany(operation, seq_of_parameters)` | Batched `INSERT` in one round trip where the statement allows it; falls back to one call per parameter set otherwise |
| `.fetchone()` / `.fetchmany(size)` / `.fetchall()` | Read back result rows |
| `.description`, `.rowcount`, `.lastrowid`, `.arraysize` | Standard PEP 249 attributes |
| Iteration | `for row in cursor: ...` |

`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

| MilvusQL | Milvus field type | Notes |
|---|---|---|
| `BIGINT` / `INT` / `SMALLINT` / `TINYINT` | `INT64/32/16/8` | `PRIMARY KEY [AUTO_INCREMENT]` on `BIGINT`/`VARCHAR` |
| `FLOAT` / `DOUBLE` / `BOOLEAN` / `JSON` | ditto | JSON paths filter server-side: `WHERE meta['brand'] = :b` |
| `VARCHAR(n)` | `VARCHAR` | |
| `TEXT` | analyzer-enabled `VARCHAR(65535)` | full-text input: `MATCH ... AGAINST` + BM25 |
| `ARRAY<T>(capacity)` | `ARRAY` | `ARRAY_CONTAINS`/`_ALL`/`_ANY`, `ARRAY_LENGTH` filter server-side |
| `VECTOR(dim)` | `FLOAT_VECTOR` | |
| `SPARSEVEC` | `SPARSE_FLOAT_VECTOR` | `GENERATED ALWAYS AS (BM25(text_col))` for full-text |
| `BINARYVEC(dim)` / `FLOAT16VEC(dim)` / `BFLOAT16VEC(dim)` / `INT8VEC(dim)` | `BINARY/FLOAT16/BFLOAT16/INT8_VECTOR` | bind values as `bytes` / numpy arrays, passed through untouched |

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

| Package | Description |
|---|---|
| [`milvusql-sqlalchemy`](packages/milvusql-sqlalchemy) | SQLAlchemy 2.0 dialect — `VECTOR`/`SPARSEVEC` column types, `hybrid_search()`, Alembic support |
| [`milvusql-django`](packages/milvusql-django) | Django database backend — `VectorField`, ORM CRUD/filtering through the normal compiler |

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

## Examples

| Example | Shows |
|---|---|
| [`examples/basic_walkthrough`](examples/basic_walkthrough) | A guided, top-to-bottom tour of the DBAPI: connect, `CREATE TABLE`/`CREATE INDEX`, insert, filter `SELECT`, vector search, `UPDATE`/`DELETE` — sync and async |
| [`examples/temporal_worker`](examples/temporal_worker) | A [Temporal](https://temporal.io) workflow/activity that inserts rows into Milvus as a durable, retry-safe ingestion pipeline |

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

## Development

Requires Python 3.12+, [uv](https://docs.astral.sh/uv/), [task](https://taskfile.dev/).

```bash
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:

```bash
task core:lint         task core:test
task sqlalchemy:lint   task sqlalchemy:test
task django:lint       task django:test
```

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

## License

MIT
