Metadata-Version: 2.4
Name: korm
Version: 2.0.0a7
Summary: KORM Protocol v2 — JSON request/response protocol for dynamic database operations (reference implementation)
Author: Partha Preetham Krishna M L
License-Expression: MIT
License-File: LICENSE
Keywords: database,json,mcp,orm,protocol,sql
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Requires-Python: >=3.10
Requires-Dist: sqlalchemy>=2.0
Provides-Extra: cli
Provides-Extra: dev
Requires-Dist: aiosqlite>=0.19; extra == 'dev'
Requires-Dist: greenlet>=3; extra == 'dev'
Requires-Dist: pytest-asyncio>=0.23; extra == 'dev'
Requires-Dist: pytest>=8; extra == 'dev'
Provides-Extra: fastapi
Requires-Dist: fastapi>=0.110; extra == 'fastapi'
Provides-Extra: mcp
Requires-Dist: mcp>=1.0; extra == 'mcp'
Provides-Extra: mysql
Requires-Dist: aiomysql>=0.2; extra == 'mysql'
Requires-Dist: pymysql>=1.1; extra == 'mysql'
Provides-Extra: postgres
Requires-Dist: asyncpg>=0.29; extra == 'postgres'
Requires-Dist: psycopg[binary]>=3.1; extra == 'postgres'
Provides-Extra: sqlite
Requires-Dist: aiosqlite>=0.19; extra == 'sqlite'
Description-Content-Type: text/markdown

# korm

Reference Python implementation of the **KORM Protocol v2** — a JSON request/response
protocol for dynamic database operations against PostgreSQL, MySQL/MariaDB, and SQLite.

A KORM server receives a JSON request naming a model and an action, executes it via
SQLAlchemy Core 2.x, and returns a uniform response envelope. The protocol is fully
describable by JSON Schema, so MCP tools, OpenAPI docs, and typed clients can be
generated from one source.

```python
import korm

db = korm.initialize_korm(url="postgresql+asyncpg://...", schema="korm.schema.json")

result = await db.process({          # async, v2-native
    "korm": 2,
    "action": "list",
    "model": "users",
    "where": {
        "is_active": True,
        "age": {"gte": 18, "lt": 65},
        "email": {"endsWith": "@example.com"},
    },
    "include": {"profile": True, "posts": {"limit": 5, "orderBy": "-created_at"}},
    "orderBy": "-created_at",
    "limit": 20,
})

result = db.process_sync(request_dict)   # sync engines
```

Every action returns the same envelope:

```json
{
  "ok": true, "action": "list", "model": "users",
  "data": [ ... ],
  "meta": { "protocol": 2, "count": 20, "limit": 20, "offset": 0, "durationMs": 4 },
  "error": null
}
```

**New in v2.0.0a4:** attach read-only sub-requests via `other_requests`:

> **New in v2.0.0a7:** Result caching, streaming responses, and slow query logging.

```python
result = await db.process({
    "korm": 2, "action": "show", "model": "users",
    "where": {"id": 1},
    "other_requests": {
        "posts": {
            "action": "list",
            "where": {"author_id": 1},
            "limit": 10,
        },
        "stats": {
            "action": "aggregate",
            "aggregates": [{"fn": "count", "as": "post_count"}],
            "model": "posts",
        },
    },
})
# result["other_responses"]["posts"]["data"]  — list of posts
# result["other_responses"]["stats"]["data"]  — aggregate result
```

## Features (spec v2.0)

- **Actions**: `create`, `list`, `show`, `update`, `delete`, `restore`, `upsert`,
  `replace`, `sync`, `aggregate`, `batch` (optionally atomic, with `$ref` chaining).
- **Structured where grammar** — no stringly-typed operator DSL: `eq ne gt gte lt lte
  in notIn between notBetween like ilike contains startsWith endsWith is not`, plus
  nestable `and` / `or` / `not` boolean trees with fully parenthesized SQL.
- **Safe by construction** — no raw SQL fragments anywhere; all identifiers validated
  against the schema; `contains`/`startsWith`/`endsWith` escape `%` and `_`.
- **Pagination** — offset mode and opaque keyset cursors (send `"cursor": null` for the
  first page, follow `meta.nextCursor`); optional HMAC-signed cursors via `cursor_secret`.
- **Relations** — `hasOne` / `hasMany` / `belongsTo` includes with per-relation
  `select`/`where`/`orderBy`/`limit`, nested dotted paths, and validated explicit joins.
  `manyToMany` is accepted in schemas (syntax v2.0) and executes from v2.1.
- **Aggregates** — `count sum avg min max`, `groupBy`, `having`, and JSON expression
  trees (`{"add": ["salary", "bonus"]}`) replacing v1's `sumFormula` strings.
- **Soft delete** — automatic `deleted_at IS NULL` filtering, `withDeleted` /
  `onlyDeleted` / `hardDelete` options, first-class `restore`.
- **Hooks** — `beforeValidate`, `afterValidate`, `before<Action>`, `after<Action>`,
  `onError`; sync or async; may mutate the request, short-circuit, or raise.
- **Policy layer** — per-role model/action allowlists, `default_deny` production mode,
  mandatory where-scopes for tenant isolation (`{"org_id": "$ctx.orgId"}`).
- **v1 compatibility** — a deterministic up-converter (on by default, `compat_v1`)
  accepts v1 operator strings (`">=18"`, `"><18,65"`, `"[]a,b"`, `"!x"`, `"%like%"`,
  `Or:` keys, `count`/`sum` actions, string join conditions) and down-converts
  responses (bare count numbers, bare `show` objects) for v1 clients. `strict_v2: true`
  turns it off.
- **New in v2.0.0a4: `other_requests`** — attach read-only sub-requests (`list`,
  `show`, `aggregate`) to any primary action. Sub-requests execute after the
  primary action within the same transaction (for write actions) and return
  results under `response.other_responses.<label>`. Errors in a sub-request
  are captured per-label — a failing sub-request never fails the primary.
- **Errors are codes, not prose**: `VALIDATION_FAILED`, `UNKNOWN_MODEL`,
  `UNKNOWN_COLUMN`, `UNKNOWN_RELATION`, `UNKNOWN_ACTION`, `NOT_FOUND`, `CONFLICT`,
  `FK_VIOLATION`, `POLICY_DENIED`, `LIMIT_EXCEEDED`, `TRANSACTION_FAILED`,
  `BATCH_ROLLED_BACK`, `UNSUPPORTED_ON_ENGINE`, `INTERNAL` — with structured
  per-field `details`.

## Install

```
pip install korm                 # SQLite via stdlib driver
pip install korm[postgres]       # psycopg + asyncpg
pip install korm[mysql]          # PyMySQL + aiomysql
pip install korm[fastapi]        # HTTP router
pip install korm[mcp]            # MCP server
```

## Schema (`korm.schema.json`)

One file drives DDL sync, request validation, and generated types:

```json
{
  "kormSchema": 2,
  "models": {
    "users": {
      "table": "users",
      "primaryKey": ["id"],
      "softDelete": { "column": "deleted_at" },
      "timestamps": { "createdAt": "created_at", "updatedAt": "updated_at" },
      "columns": {
        "id":       { "type": "increments" },
        "username": { "type": "string", "length": 80, "unique": true, "required": true },
        "email":    { "type": "string", "format": "email", "required": true }
      },
      "relations": {
        "profile": { "type": "hasOne", "model": "user_profiles", "foreignKey": "user_id" },
        "posts":   { "type": "hasMany", "model": "posts", "foreignKey": "author_id" }
      }
    }
  }
}
```

## Fluent Query Builder (v2.0.0a6+)

Chainable Python API — no raw JSON dicts required:

```python
# Sync
result = db.query("users").where(age__gte=18).limit(20).order_by("-created_at").list()

# Async
result = await db.query("users").where(age__gte=18).a.list()

# Build raw request dict for manual processing
req = db.query("users").where(age__gte=18).build("list")
```

Supported methods: `.where()`, `.select()`, `.include()`, `.order_by()`, `.limit()`,
`.offset()`, `.cursor()`, `.options()`, `.other()` (sub-requests), `.build(action)`,
`.run(action)`, plus terminal shorthands `.list()`, `.show()`, `.create(data)`,
`.update(data)`, `.delete()`, `.restore()`, `.upsert(data, conflict)`,
`.replace(data)`, `.sync(data, conflict)`, and `.aggregates(...)`.

## WebSocket Subscriptions (v2.0.0a6+)

Subscribe to real-time mutation events:

```python
async for event in db.subscribe("users", where={"org_id": 1}):
    print(event["event"], event["data"])  # "create" / "update" / "delete"
```

FastAPI WebSocket endpoint: `ws://host/api/{model}/subscribe?where={"org_id":1}`

## Result Caching (v2.0.0a7+)

Opt-in per-request caching with auto-invalidation on mutations:

```python
# Configure with default TTL
db = initialize_korm(url="sqlite:///app.db", schema=schema,
                     default_cache_ttl=30)  # 30s default

# Per-request override
result = db.process_sync({
    "korm": 2, "action": "list", "model": "users",
    "options": {"cache_ttl": 60},
})

# Bypass cache for this call
result = db.process_sync({
    "korm": 2, "action": "list", "model": "users",
    "options": {"cache_ttl": 0},
})
```

Built-in `MemoryCache` backend; bring your own via `CacheBackend` protocol.

## Streaming Responses (v2.0.0a7+)

Stream rows one at a time using server-side cursors — no full-result buffering:

```python
# Sync
for row in db.stream_sync("users", where={"is_active": True}):
    process(row)

# Async
async for row in db.stream("users"):
    process(row)

# QueryBuilder
for row in db.query("users").where(age__gte=18).stream():
    process(row)

# FastAPI endpoint: POST /{model}/stream returns NDJSON
```

Supports `select` column trimming, `where`, `orderBy`, and `limit`.

## Slow Query Log (v2.0.0a7+)

Automatically log queries exceeding a duration threshold:

```python
db = initialize_korm(url="postgresql://...", schema=schema,
                     slow_query_threshold_ms=500)  # log queries >500ms
```

On any request slower than the threshold, a structured dict is logged to
`korm.slow_query` (Python `logging` module) containing: action, model,
duration, compiled SQL, row count, and response metadata. Cache hits are
skipped (no SQL executed).

## OpenAPI 3.1 Spec (v2.0.0a6+)

Auto-generate an OpenAPI spec from your schema:

```python
from korm import generate_openapi
spec = generate_openapi(schema)
```

CLI: `korm serve-openapi --url sqlite:///app.db --schema korm.schema.json`

## FastAPI

```python
from fastapi import FastAPI
from korm.fastapi import crud_router

app = FastAPI()
app.include_router(crud_router(db), prefix="/api")
# POST /api/{model}/crud
```

## CLI

```
korm init                    # scaffold korm.schema.json
korm generate-schema --url sqlite:///app.db      # introspect a live database
korm sync  --url ... --schema korm.schema.json   # dev-time DDL sync
korm diff  --url ... --schema korm.schema.json   # migration skeleton
korm serve-mcp --url ... --schema ...            # per-model MCP tools over stdio
korm serve-openapi --url ... --schema ...        # OpenAPI 3.1 spec + Swagger UI
korm serve-openapi --url ... --schema ... --no-serve  # print spec as JSON
```

`sync` is a dev/prototyping tool; for production use migration tools (Alembic/Knex)
generated from `korm diff` output.

## Development

```
pip install -e ".[dev]"
pytest
```

## Status

Implements KORM Protocol v2 draft 0.2 (including v2.1 features: manyToMany include
execution, column-level policy allowlists, `other_requests` sub-requests, fluent
query builder, OpenAPI spec generation, WebSocket subscriptions, result caching,
streaming responses, and slow query logging).
`meta.prevCursor` is currently always `null` (forward-only keyset).
