# KORM-JS — AI assistant reference > Skill installed by `npx @dreamtree-org/korm-js init --ai `. > Source of truth: [`@dreamtree-org/korm-js`](https://www.npmjs.com/package/@dreamtree-org/korm-js). > Re-run the installer to refresh this block when the library updates. ## What KORM-JS is `@dreamtree-org/korm-js` is a **JSON-contract ORM** built on top of Knex. The consumer sends a single JSON request describing the operation; KORM translates it into safe, parameterized SQL across **MySQL, PostgreSQL, and SQLite**. Models and their relations are declared once; CRUD is never hand-written. When helping the user, **always express data access as a KORM request object**, not as raw Knex calls or string SQL. ## Wiring (do not invent alternatives) ```js const { initializeKORM } = require('@dreamtree-org/korm-js'); const knex = require('knex'); const db = knex({ client: 'mysql2', // 'mysql2' | 'pg' | 'sqlite3' connection: { /* ... */ }, }); const korm = await initializeKORM({ db, dbClient: 'mysql', // 'mysql' | 'pg' | 'sqlite' debug: false, schema: null, // optional: schema object, file path, or URL resolverPath: null, // optional: path to models directory }); // `schema` accepts four forms — auto-detected: // object → used as-is // ".json" → readFileSync + JSON.parse // ".js" → require() (CJS: module.exports = {…}) // ".mjs" → dynamic import() (ESM: export default {…}) // "http(s)"→ fetch + JSON.parse // Invalid sources throw KormError. const result = await korm.processRequest(requestObject, 'ModelName'); ``` In Express/Next/Fastify the consumer just forwards `req.body` and the model name. KORM does **not** own routing — never suggest an HTTP framework as part of KORM itself. ## Request contract `processRequest(request, modelName)` accepts a JSON object with these top-level fields: | Field | Type | Purpose | | ----------------------------------------------- | -------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `action` | string (required) | The operation: `list`, `show`, `create`, `update`, `delete`, `count`, `sum`, `replace`, `upsert`, `sync` | | `where` | object \| array | Filter conditions for `list`/`show`/`update`/`delete`/`count`/`sum` | | `data` | object \| array | Payload for `create`/`update`/`upsert`/`replace`/`sync` | | `select` | array \| string | Columns to return (default: all) | | `with` | array of strings | Relations to eager-load (dot-nested allowed: `"Post.Comment"`) | | `withWhere` | object | Filters scoped to related rows only — does NOT filter parents | | `orderBy` | object \| array \| string | `{column, direction}` / `"column"` / array of either. Object MUST use the `column` key — `{created_at:"desc"}` is rejected (`VALIDATION_FAILED`). Omitted → defaults to the model's primary key ascending (not always `id`) | | `limit` | number | Max rows | | `offset` / `page` | number | Pagination | | `groupBy` | array \| string | GROUP BY columns | | `having` | object | Post-group filter | | `distinct` | boolean \| array \| string | DISTINCT / DISTINCT ON | | `join` / `innerJoin` / `leftJoin` / `rightJoin` | object \| array | Explicit joins (rarely needed — prefer `with`) | | `conflict` | array | Conflict columns for `upsert` / `sync` | | `other_requests` | object | Nested requests on related models; results returned under `other_responses` | | `dryRun` | boolean | If `true`, return the SQL that would run without executing it (see "Inspecting queries" below) | ### Actions | Action | Behavior | | --------- | --------------------------------------------------------------------------------------------------------------------------------------- | | `list` | Multi-row read with where/order/limit/offset | | `show` | Single-row read | | `create` | Insert from `data` (object = 1 row, array = bulk) | | `update` | Update rows matching `where` with `data` | | `delete` | Delete (soft if the model declares soft-delete; otherwise hard) | | `count` | COUNT(\*) of matching rows | | `sum` | Sum a column or formula; needs `data.sumColumn` or `data.sumFormula` | | `replace` | Full-row replace by PK, all engines (MySQL/SQLite = delete+insert; pg = ON CONFLICT merge — omitted cols retained). Optional `conflict` | | `upsert` | Insert-or-update keyed by `conflict` columns | | `sync` | Upsert matching `data` + delete non-matching within `where` scope | ### `where` operator cheat-sheet Operators are **encoded as string prefixes on the value** (not separate keys): | Operator | Value form | Example | SQL | | ------------------- | --------------------------- | --------------------------- | --------------------- | | Equals (default) | bare value | `{status: "active"}` | `= ?` | | `>=` | `">=N"` | `{age: ">=18"}` | `>= ?` | | `<=` | `"<=N"` | `{age: "<=65"}` | `<= ?` | | `>` | `">N"` | `{price: ">100"}` | `> ?` | | `<` | `"<18,65"}` | `BETWEEN ? AND ?` | | NOT BETWEEN | `"<>min,max"` | `{score: "<>0,50"}` | `NOT BETWEEN ? AND ?` | | IS NULL | `null` | `{deleted_at: null}` | `IS NULL` | | OR group | key prefix `"Or:"` | `{"Or:first_name": "John"}` | `OR (...)` | | NOT EXISTS relation | `"!RelName": true` | `{"User.!UserRole": true}` | `NOT EXISTS (...)` | Rules: - Non-`Or:`-prefixed keys are ANDed together. - Array form `where: [ {a: 1}, {b: 2} ]` is equivalent to object form for ANDs but lets you repeat the same column. - `sumFormula` uses `{columnName}` placeholders and accepts only `+ - * / ( )` and decimal literals — **never interpolate user input**. - All values flow through Knex bindings. **Do not hand-build SQL strings.** ### Relations (`with`) Relation metadata lives on the model definition (`hasRelations`). The consumer just names them: ```js { action: "list", where: { id: 1 }, with: ["UserDetail", "Post", "Post.Comment"], withWhere: { "Post.status": "published" } } ``` `withWhere` filters child rows but does **not** drop parent rows that have no matching children. To drop parents, filter on the relation in the top-level `where` (e.g. `{"Post.status": "published"}`). Supported relation `type` values when defining a model: `"one"` (belongs-to / one-to-one) and `"many"` (one-to-many or many-to-many via `through`). ### Inspecting queries (`dryRun`) Add `dryRun: true` to any request to get back the SQL it **would** run, without executing it. Validation still runs; the database is untouched. ```js await korm.processRequest( { action: 'delete', where: { status: 'archived' }, dryRun: true }, 'Post' ); // → { success: true, dryRun: true, action: 'delete', model: 'Post', // sql: 'delete from `posts` where `status` = ?', bindings: ['archived'], // statements: [{ sql, bindings }] } // `sync` returns 2 statements ``` Bindings come back as a separate array (never interpolated into `sql`). ### Errors (`KormError`) `processRequest` throws a `KormError` (extends `Error`, so `e.message` still works) with a machine-readable `code` you can branch on: | `code` | When | | ----------------------- | --------------------------------------------------------------------------------------------------------------- | | `NO_MATCHING_ROW` | A mutating action matched no row | | `UNKNOWN_ACTION` | Action isn't built-in and has no custom hook | | `NO_CUSTOM_ACTION_HOOK` | Custom action requested, no hook on the model | | `VALIDATION_FAILED` | Input failed validation (`e.context.fields`) | | `UNKNOWN_MODEL` | Model name not in the schema (`e.context.available`) | | `FORBIDDEN` | A registered `authorize()` predicate denied the request (`e.context.model`/`action`) | | `SYNC_FK_ORPHAN` | `syncDatabase()` would add a foreign key that orphans existing rows (`e.context.offenders`); no data is mutated | | `INTERNAL` | Internal invariant / misconfiguration | ```js const { KormError } = require('@dreamtree-org/korm-js'); try { await korm.processRequest({ action: 'updaet' }, 'User'); } catch (e) { if (e instanceof KormError && e.code === 'UNKNOWN_ACTION') { // e.context.closest → "update" (typo suggestion); e.toJSON() for HTTP } } ``` ### Discovery + tool schema (`describeSchema` / `getRequestJsonSchema`) Two read-only helpers for agent integration: - `korm.describeSchema()` / `korm.describeModel('User')` — pure-data description of tables, typed columns, relations, soft-delete flag, and available actions. Use it to discover what's queryable before building a request. Throws `KormError` (`code: 'UNKNOWN_MODEL'`) for a bad name. Pass a context — `korm.describeModel('User', ctx)` — and `actions` is filtered to those the current context may call (see authorization). - `korm.getRequestJsonSchema('User')` — draft-2020-12 JSON Schema for every valid request body for that model (an `action`-discriminated `oneOf`). Attach it to an OpenAI/Anthropic tool definition or use it for client-side prevalidation: ```js const ctx = korm.describeModel('User'); // discovery const schema = korm.getRequestJsonSchema('User'); // request contract // OpenAI: { type: 'function', function: { name, description, parameters: schema } } // Anthropic:{ name, description, input_schema: schema } ``` ## Canonical examples ### Read with filter + pagination ```js await korm.processRequest( { action: 'list', where: { is_active: true, age: '>=18' }, select: ['id', 'username', 'email'], orderBy: { column: 'created_at', direction: 'desc' }, limit: 20, offset: 0, }, 'User' ); ``` ### Create ```js await korm.processRequest( { action: 'create', data: { username: 'john_doe', email: 'john@example.com', age: 30 }, }, 'User' ); ``` ### Update by relation ```js await korm.processRequest( { action: 'update', where: { 'User.id': 1 }, data: { status: 'active' }, with: ['User'], }, 'Profile' ); ``` ### Nested eager-load ```js await korm.processRequest( { action: 'list', where: { 'User.is_active': true }, select: ['id', 'title', 'User.username'], with: ['User', 'User.UserDetail', 'Comment'], withWhere: { 'Comment.is_approved': true }, limit: 5, }, 'Post' ); ``` ### Upsert ```js await korm.processRequest( { action: 'upsert', data: { email: 'a@b.com', name: 'Alice' }, conflict: ['email'], }, 'User' ); ``` ## Rules for AI assistants helping consumers 1. **Use the JSON contract.** When the user asks for a query, return a KORM request object plus the `processRequest` call — not raw Knex chains. 2. **Never concatenate user input into SQL.** All filtering goes through `where` operators above. 3. **Multi-DB.** Assume the same request runs on MySQL, Postgres, and SQLite. If a feature's _semantics_ differ by engine, call it out — e.g. `replace` is a true delete+insert on MySQL/SQLite but a merge on Postgres (omitted columns are retained); prefer `upsert` for portable insert-or-update. 4. **Don't invent operators.** If the user needs something not in the operator table, use `where` with relation traversal, `having`, or `groupBy` — or tell the user the contract doesn't support it. 5. **Don't invent fields.** The top-level keys above are the entire contract surface. No `filter`, no `query`, no `params`. 6. **Soft delete is per-model.** `delete` becomes a soft-delete only if the model declares it; don't assume. 7. **Preview before mutating.** For a risky write, add `dryRun: true` first to inspect the SQL, then re-issue without it. 8. **Handle errors by `code`.** Catch `KormError` and branch on `e.code` (table above) rather than string-matching `e.message`. 9. **Refresh this doc** by re-running `npx @dreamtree-org/korm-js init --ai ` when the library is upgraded.