korm 2.0.0a6

KORM Protocol v2 — JSON request/response protocol for dynamic database operations (reference implementation)

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.

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:

{
  "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:

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:

{
  "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" }
      }
    }
  }
}

FastAPI

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

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, and other_requests sub-requests). meta.prevCursor is currently always null (forward-only keyset).