# KORM-JS
**Knowledge Object-Relational Mapping** - A powerful, modular ORM system for Node.js with dynamic database operations, complex queries, relationships, nested requests, and soft delete support.
[](https://badge.fury.io/js/@dreamtree-org%2Fkorm-js)
[](https://opensource.org/licenses/MIT)
## About the Company
KORM-JS is built and maintained under **[Dreamtree Global](http://dreamtreeglobal.com/)** β a team crafting thoughtful, developer-first software for the modern web. We believe great products start with a great foundation, and that's exactly what this library aims to be: a polished, open, and dependable data layer for teams everywhere.
π **Website:** [dreamtreeglobal.com](http://dreamtreeglobal.com/)
## Author & Maintainer
**Partha Preetham Krishna M L**
π§ [preetham.krishna.dev@gmail.com](mailto:preetham.krishna.dev@gmail.com)
Have an idea, a bug, or just want to say hi? Reach out anytime β feedback from developers like you is what keeps this library growing.
## π Sponsor & Support
KORM-JS is **free and open source**, built in the open and powered entirely by passion and late nights. Every feature you rely on β every multi-engine query, relation-aware join, and line of zero-boilerplate CRUD β represents hours of careful craft so that _your_ backend can ship faster and stay safer.
If this library has saved you time, sparked an idea, or spared you a pile of hand-written SQL, please consider supporting its continued development. **Your sponsorship keeps the updates coming, the bugs squashed, and the docs sharp β for the whole community.** Even the smallest contribution is a huge encouragement. π
### β Make a one-tap donation via UPI
**π· Scan the QR with any UPI app** (Google Pay, PhonePe, Paytm, BHIM, β¦) to pay instantly:
> **UPI ID:** `dhrugantha.llp@kotak` β’ **Payee:** Dreamtree Global
>
> π± On mobile, you can also tap the button below β it opens your UPI app directly. On desktop, scan the QR above or copy the UPI ID into your payment app.
[](upi://pay?pa=dhrugantha.llp@kotak&pn=Dreamtree%20Global&cu=INR&tn=Support%20KORM-JS)
### π€ Become a Sponsor
Want to back the project long-term or as a company? You can sponsor the package directly:
- π¦ **npm:** [`@dreamtree-org/korm-js`](https://www.npmjs.com/package/@dreamtree-org/korm-js) β star, share, and sponsor the package
- βοΈ **Corporate sponsorships & partnerships:** [preetham.krishna.dev@gmail.com](mailto:preetham.krishna.dev@gmail.com)
Every star β, share, and contribution helps more than you know. Thank you for being part of the journey!
## Features
- π **Multi-Database Support**: MySQL, PostgreSQL, SQLite
- π§ **Dynamic Operations**: Create, Read, Update, Delete, Replace, Upsert, Sync
- π **Advanced Querying**: Complex queries with relationships, joins, and filtering
- π‘οΈ **Data Validation**: Built-in request validation with custom rules
- π **Auto-Sync**: Automatic table schema synchronization
- π¦ **Modular Design**: Use only what you need
- π― **Express.js Ready**: Perfect integration with Express applications
- π **Schema Generation**: Auto-generate schemas from existing databases
- ποΈ **Soft Delete**: Built-in soft delete support with automatic filtering
- π£ **Model Hooks**: Before, After, Validate, and Custom action hooks
- π **Custom Relations**: Define custom relation hooks for complex data fetching
- π¦ **Nested Requests**: Process multiple related requests in a single call
- π **Logger Utility**: Configurable logging with multiple log levels
- π **SQL Debugging**: Debug mode with SQL statement output for troubleshooting
- π« **NOT EXISTS Queries**: Support for checking absence of related records with `!` prefix
- π€ **AI-native**: a bundled [MCP server](#running-as-an-mcp-server) serves your tables to coding agents as typed JSON tools, plus an [`init --ai`](#ai-assistant-skills-init---ai) skill installer for Claude, Cursor, Copilot, and more
## Installation
```bash
npm install @dreamtree-org/korm-js
```
## AI assistant skills (`init --ai`)
KORM-JS ships a canonical reference doc designed for AI coding assistants. After installing the package (or via `npx` without installing), drop the skill into the location your assistant picks up:
```bash
npx @dreamtree-org/korm-js init --ai claude
```
Supported providers:
| `--ai` value | Target file |
| ----------------------------------- | ----------------------------------------------------------- |
| `claude` | `CLAUDE.md` (idempotent block insert) |
| `openai` (alias: `codex`) | `AGENTS.md` (idempotent block insert) |
| `gemini` | `GEMINI.md` (idempotent block insert) |
| `copilot` (alias: `github-copilot`) | `.github/copilot-instructions.md` (idempotent block insert) |
| `kiro` | `.kiro/steering/korm-js.md` |
| `windsurf` | `.windsurf/rules/korm-js.md` |
| `cursor` | `.cursor/rules/korm-js.mdc` (with MDC frontmatter) |
| `all` | Installs every provider above |
Flags:
- `--cwd ` β install into a directory other than the current working directory.
- `--force` β overwrite dedicated-file targets (Cursor / Windsurf / Kiro) that already exist.
- `--dry-run` β print what would change without writing.
Block-insert targets (`CLAUDE.md`, `AGENTS.md`, `GEMINI.md`, `.github/copilot-instructions.md`) wrap the skill in ` ... ` markers. Re-running the command after a library upgrade updates the block in place without touching the rest of the file.
## Quick Start with Express.js and MySQL
### 1. Basic Setup
```javascript
require('dotenv').config();
const express = require('express');
const { initializeKORM, validate, helperUtility } = require('@dreamtree-org/korm-js');
const knex = require('knex');
const app = express();
app.use(express.json());
// MySQL database configuration
const db = knex({
client: 'mysql2',
connection: {
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'root',
password: process.env.DB_PASS || 'password',
database: process.env.DB_NAME || 'my_database',
port: process.env.DB_PORT || 3306,
},
});
// Initialize KORM with MySQL
const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: false, // Set to true for SQL debugging
});
app.listen(3000, () => {
console.log('π Server running on http://localhost:3000');
});
```
### 2. Schema Generation and Database Sync
```javascript
async function initApp() {
// Load existing schema if available
let syncSchema = helperUtility.file.readJSON('schema/sync.json');
if (syncSchema) {
korm.setSchema(syncSchema);
}
// Sync database with schema (creates tables if they don't exist)
await korm.syncDatabase().then(() => {
console.log('β
Database synced');
});
// Generate schema from existing database
await korm.generateSchema().then((schema) => {
korm.setSchema(schema); // Set the schema to the korm instance
helperUtility.file.createDirectory('schema');
helperUtility.file.writeJSON('schema/schema.json', schema);
console.log('β
Schema generated and saved');
});
}
// Initialize the application
initApp();
```
### 3. Generic CRUD API Endpoint
```javascript
// Generic CRUD endpoint for any model
app.post('/api/:model/crud', async (req, res) => {
try {
const { model } = req.params;
const result = await korm.processRequest(req.body, model);
res.json(result);
} catch (error) {
res.status(400).json({ error: error.message });
}
});
// Health check endpoint
app.get('/health', (req, res) => {
res.send('OK');
});
```
## Using KORM-JS with Next.js
KORM-JS is HTTP-framework-agnostic β mount `processRequest` inside the route
handlers / Server Actions Next.js already gives you (App Router). The complete
integration guide (setup, route handlers, hooks, deployment, and an
**SEO + GEO** section: `generateMetadata`, JSON-LD, `sitemap`, `robots`,
`llms.txt`) lives in [`docs/NEXTJS.md`](docs/NEXTJS.md).
A runnable App-Router example backed by KORM over SQLite is in
[`examples/nextjs-seo-geo/`](examples/nextjs-seo-geo/).
## Complete CRUD Operations Guide
### 1. Create Operation
```javascript
// Create a new record
POST /api/Users/crud
{
"action": "create",
"data": {
"username": "john_doe",
"email": "john@example.com",
"first_name": "John",
"last_name": "Doe",
"age": 30,
"is_active": true
}
}
// Response
{
"message": "Record created successfully",
"data": [
{
"id": 1,
"username": "john_doe",
"email": "john@example.com",
"first_name": "John",
"last_name": "Doe",
"age": 30,
"is_active": true,
"created_at": "2024-01-01T00:00:00.000Z",
"updated_at": "2024-01-01T00:00:00.000Z"
}
],
"success": true
}
```
### 2. List Operation (Read Multiple Records)
```javascript
// Get all records with basic filtering
POST /api/Users/crud
{
"action": "list",
"where": { "is_active": true },
"select": ["id", "username", "email", "first_name", "last_name"],
"orderBy": { "column": "created_at", "direction": "desc" },
"limit": 10
}
// List with pagination
POST /api/Users/crud
{
"action": "list",
"select": ["id", "username", "email"],
"orderBy": { "column": "created_at", "direction": "desc" },
"limit": 10,
"offset": 20
}
// List with complex conditions
POST /api/Users/crud
{
"action": "list",
"where": {
"first_name": "%John%",
"email": "%@example.com",
"age": "><18,65",
"is_active": true
},
"select": ["id", "username", "email", "first_name", "last_name"],
"orderBy": [
{ "column": "created_at", "direction": "desc" },
{ "column": "last_name", "direction": "asc" }
],
"limit": 10
}
// Response
{
"data": [
{
"id": 1,
"username": "john_doe",
"email": "john@example.com",
"first_name": "John",
"last_name": "Doe"
}
],
"total": 1,
"page": 1,
"limit": 10
}
```
### 3. Show Operation (Read Single Record)
```javascript
// Get single record by ID
POST /api/Users/crud
{
"action": "show",
"where": { "id": 1 }
}
// Get single record with multiple conditions
POST /api/Users/crud
{
"action": "show",
"where": {
"username": "john_doe",
"is_active": true
}
}
// Response
{
"id": 1,
"username": "john_doe",
"email": "john@example.com",
"first_name": "John",
"last_name": "Doe",
"age": 30,
"is_active": true,
"created_at": "2024-01-01T00:00:00.000Z",
"updated_at": "2024-01-01T00:00:00.000Z"
}
```
### 4. Update Operation
```javascript
// Update record by ID
POST /api/Users/crud
{
"action": "update",
"where": { "id": 1 },
"data": {
"first_name": "Jane",
"last_name": "Smith"
}
}
// Update multiple records
POST /api/Users/crud
{
"action": "update",
"where": { "is_active": false },
"data": {
"is_active": true
}
}
// Response
{
"message": "Record updated successfully",
"data": [
{
"id": 1,
"first_name": "Jane",
"last_name": "Smith",
"updated_at": "2024-01-01T00:00:00.000Z"
}
],
"success": true
}
```
### 5. Delete Operation
```javascript
// Hard delete (permanent deletion)
POST /api/Users/crud
{
"action": "delete",
"where": { "id": 1 }
}
// Delete multiple records
POST /api/Users/crud
{
"action": "delete",
"where": { "is_active": false }
}
// Response
{
"message": "Record deleted successfully",
"data": [/* deleted records */],
"success": true
}
```
### 6. Count Operation
```javascript
// Count all records
POST /api/Users/crud
{
"action": "count"
}
// Count with conditions
POST /api/Users/crud
{
"action": "count",
"where": {
"is_active": true,
"created_at": ">=2024-01-01"
}
}
// Response
42 // Number of matching records
```
### 7. Sum Operation
Use either `data.sumColumn` (single column) or `data.sumFormula` (expression with `{columnName}` placeholders). Optional `where` filters the rows before summing.
```javascript
// Sum a single column
POST /api/Employee/crud
{
"action": "sum",
"data": {
"sumColumn": "salary"
},
"where": {
"is_active": true,
"created_at": ">=2024-01-01"
}
}
// Sum an expression (e.g. salary + bonus)
POST /api/Employee/crud
{
"action": "sum",
"data": {
"sumFormula": "{salary}+{bonus}"
},
"where": {
"is_active": true
}
}
// Response
125000 // Sum value (number)
```
**Notes:**
- Use **either** `sumColumn` or `sumFormula`, not both. If both are provided, `sumColumn` is used.
- In `sumFormula`, column names are written as `{columnName}` and are safely quoted; only letters, numbers, and underscore are allowed in names.
- **Expression guards:** `sumFormula` allows only BODMAS-safe content: numbers (including decimals), operators `+` `-` `*` `/`, parentheses `(` `)`, and `{columnName}` placeholders. Any other character (e.g. commas, quotes, SQL) is rejected. Parentheses must be balanced and correctly ordered.
- Examples: `{salary}+{bonus}`, `({salary}+{bonus})*0.9`, `{amount}*1.1`, `{a}/{b}*100`.
- `where` supports the same operators as list/count (e.g. `>=`, `><`, `%...%`, etc.).
### 8. Replace Operation
Replaces an entire row, keyed on the model's primary key (include the PK in
`data`). Supported on **all three engines**, but the semantics differ by what
each engine can express:
| Engine | Statement | Semantics |
| -------- | ------------------------------------- | ------------------------------------------------------------------------------------------------------------------------- |
| MySQL | `REPLACE INTO` | True delete + insert. Columns omitted from `data` reset to their column **DEFAULT** (or `NULL`). |
| SQLite | `INSERT OR REPLACE` | True delete + insert (same as MySQL). |
| Postgres | `INSERT β¦ ON CONFLICT (pk) DO UPDATE` | **Merge**, not a true replace: columns omitted from `data` keep their existing values (Postgres has no native `REPLACE`). |
> β οΈ **Cross-engine caveat:** on Postgres, `replace` is emulated and behaves
> like a merge β fields you omit are **retained**, whereas MySQL/SQLite reset
> them. If you need portable insert-or-update semantics, prefer `upsert`. The
> conflict target defaults to the model's primary key; pass an explicit
> `conflict: ['col', β¦]` to override (required on Postgres if the model has no
> primary key).
```javascript
// Replace record β replaces the entire row by primary key
POST /api/Users/crud
{
"action": "replace",
"data": {
"id": 1,
"username": "john_doe_updated",
"email": "john.updated@example.com",
"first_name": "John",
"last_name": "Doe Updated"
}
}
// Response
{
"message": "Record replaced successfully",
"data": [/* replaced record */],
"success": true
}
```
### 9. Upsert Operation (Insert or Update)
```javascript
// Upsert record (insert if not exists, update if exists)
POST /api/Users/crud
{
"action": "upsert",
"data": {
"username": "john_doe",
"email": "john@example.com",
"first_name": "John",
"last_name": "Doe"
},
"conflict": ["username"] // Conflict columns for MySQL
}
// Response
{
"message": "Record upserted successfully",
"data": [/* upserted record */],
"success": true
}
```
### 10. Sync Operation (Upsert + Delete)
```javascript
// Sync operation: upsert records and delete others matching where clause
POST /api/Users/crud
{
"action": "sync",
"data": {
"username": "john_doe",
"email": "john@example.com",
"first_name": "John",
"last_name": "Doe"
},
"conflict": ["username"],
"where": {
"created_at": "<2024-01-01"
}
}
// Response
{
"message": "Record synced successfully",
"data": {
"insertOrUpdateQuery": [/* upserted records */],
"deleteQuery": [/* deleted records */]
},
"success": true
}
```
## Advanced Querying
### Complex Where Conditions
```javascript
// Multiple operators
POST /api/Users/crud
{
"action": "list",
"where": {
"age": "><18,65",
"email": "%@example.com",
"first_name": "[]John,Jane,Bob",
"is_active": true,
"created_at": "><2024-01-01,2024-12-31"
}
}
// NULL checks
POST /api/Users/crud
{
"action": "list",
"where": {
"deleted_at": null
}
}
```
### OR Conditions with `Or:` Prefix
Use the `Or:` prefix on column names to create OR conditions in your queries:
```javascript
// OR condition on a single column
POST /api/Users/crud
{
"action": "list",
"where": {
"status": "active",
"Or:name": "%john%"
}
}
// Generated SQL: WHERE status = 'active' OR name LIKE '%john%'
// Multiple OR conditions
POST /api/Users/crud
{
"action": "list",
"where": {
"is_active": true,
"Or:first_name": "John",
"Or:last_name": "Doe"
}
}
// Generated SQL: WHERE is_active = 1 OR first_name = 'John' OR last_name = 'Doe'
// Combining AND and OR conditions
POST /api/Users/crud
{
"action": "list",
"where": {
"status": "active",
"age": ">=18",
"Or:role": "admin",
"Or:role": "super_admin"
}
}
// Generated SQL: WHERE status = 'active' AND age >= 18 OR role = 'admin' OR role = 'super_admin'
// OR with operators
POST /api/Users/crud
{
"action": "list",
"where": {
"department": "sales",
"Or:email": "%@company.com",
"Or:created_at": ">=2024-01-01"
}
}
// Generated SQL: WHERE department = 'sales' OR email LIKE '%@company.com' OR created_at >= '2024-01-01'
// OR with NULL checks
POST /api/Users/crud
{
"action": "list",
"where": {
"is_verified": true,
"Or:verified_at": null
}
}
// Generated SQL: WHERE is_verified = 1 OR verified_at IS NULL
// OR with IN operator
POST /api/Users/crud
{
"action": "list",
"where": {
"status": "pending",
"Or:role": ["admin", "moderator", "editor"]
}
}
// Generated SQL: WHERE status = 'pending' OR role IN ('admin', 'moderator', 'editor')
```
### Array-Based Where Conditions
You can also pass `where` as an array of condition objects for more complex queries:
```javascript
// Array of conditions - each object is applied sequentially
POST /api/Users/crud
{
"action": "list",
"where": [
{ "status": "active" },
{ "Or:role": "admin" },
{ "Or:role": "moderator" }
]
}
// Generated SQL: WHERE status = 'active' OR role = 'admin' OR role = 'moderator'
// Combining multiple AND and OR groups
POST /api/Users/crud
{
"action": "list",
"where": [
{ "is_active": true, "age": ">=18" },
{ "Or:status": "verified" },
{ "Or:role": "admin" }
]
}
// Generated SQL: WHERE is_active = 1 AND age >= 18 OR status = 'verified' OR role = 'admin'
// Complex filtering with array conditions
POST /api/Users/crud
{
"action": "list",
"where": [
{ "department": "engineering" },
{ "Or:department": "product" },
{ "created_at": ">=2024-01-01" }
]
}
// Generated SQL: WHERE department = 'engineering' OR department = 'product' AND created_at >= '2024-01-01'
```
### Where Operators Reference
| Operator | Description | Syntax | Example |
| ------------- | ----------------------- | -------------- | ------------------------------- |
| `=` (default) | Equals | `value` | `"status": "active"` |
| `>=` | Greater than or equal | `>=value` | `"age": ">=18"` |
| `<=` | Less than or equal | `<=value` | `"age": "<=65"` |
| `>` | Greater than | `>value` | `"price": ">100"` |
| `<` | Less than | `<18,65"` |
| `notBetween` | Outside range | `<>min,max` | `"score": "<>0,50"` |
| `null` | IS NULL check | `null` | `"deleted_at": null` |
| `Or:column` | OR condition prefix | `Or:column` | `"Or:name": "John"` |
**Examples:**
```javascript
// Complex query with string-based operators
POST /api/Users/crud
{
"action": "list",
"where": {
"age": ">=18",
"status": "!deleted",
"name": "%john%",
"role": "[]admin,moderator,editor",
"score": "><50,100"
}
}
// SQL: WHERE age >= 18 AND status != 'deleted' AND name LIKE '%john%'
// AND role IN ('admin', 'moderator', 'editor') AND score BETWEEN 50 AND 100
```
### Sorting (orderBy)
```javascript
// Single sort with object
POST /api/Users/crud
{
"action": "list",
"orderBy": { "column": "created_at", "direction": "desc" }
}
// Single sort with string (default: ascending)
POST /api/Users/crud
{
"action": "list",
"orderBy": "created_at"
}
// Multiple sorts with array of objects
POST /api/Users/crud
{
"action": "list",
"orderBy": [
{ "column": "is_active", "direction": "desc" },
{ "column": "created_at", "direction": "desc" },
{ "column": "last_name", "direction": "asc" }
]
}
// Multiple sorts with array of strings (all ascending)
POST /api/Users/crud
{
"action": "list",
"orderBy": ["is_active", "created_at", "last_name"]
}
```
> **An `orderBy` object must use the `{ column, direction }` shape.** A
> columnβdirection map that mirrors the `where` shape (e.g.
> `{ "created_at": "desc" }`) is **rejected** with a `KormError`
> (`code: 'VALIDATION_FAILED'`, message naming the offending keys) instead of
> generating broken SQL. Use `{ "column": "created_at", "direction": "desc" }`
> or the string form `"created_at"`.
> **Default ordering.** When a `list`/`show` request omits `orderBy`, KORM
> orders by the model's **primary key** ascending β i.e. the first column
> flagged `primary` (which may be `uuid`, `code`, etc., not just `id`). If the
> model declares no primary key it falls back to an `id` column when one
> exists, and otherwise emits no `ORDER BY` at all. Pass an explicit `orderBy`
> to override.
### Joins
```javascript
// Inner join (using innerJoin parameter)
POST /api/Users/crud
{
"action": "list",
"select": ["users.id", "users.username", "user_profiles.bio"],
"innerJoin": {
"table": "user_profiles",
"on": "users.id = user_profiles.user_id"
},
"where": { "users.is_active": true }
}
// Left join (using leftJoin parameter)
POST /api/Users/crud
{
"action": "list",
"select": ["users.*", "user_profiles.bio"],
"leftJoin": {
"table": "user_profiles",
"on": "users.id = user_profiles.user_id"
}
}
// Multiple joins (array format)
POST /api/Users/crud
{
"action": "list",
"select": ["users.*", "profiles.bio", "roles.name"],
"leftJoin": [
{ "table": "user_profiles", "on": "users.id = user_profiles.user_id" },
{ "table": "roles", "on": "users.role_id = roles.id" }
]
}
// Join with explicit columns
POST /api/Users/crud
{
"action": "list",
"innerJoin": {
"table": "orders",
"first": "users.id",
"operator": "=",
"second": "orders.user_id"
}
}
```
**Available join types:**
- `join` - Regular join
- `innerJoin` - Inner join
- `leftJoin` - Left join
- `rightJoin` - Right join
## Relational Support with hasRelations
### Understanding hasRelations Structure
The `hasRelations` object in your schema defines relationships between models. Relationships in your schema use the following structure:
#### One-to-One or Belongs-To Relationship (`type: "one"`)
```json
{
"UserDetail": {
"hasRelations": {
"User": {
"type": "one",
"table": "users",
"localKey": "user_id",
"foreignKey": "id"
}
}
}
}
```
#### One-to-Many Relationship (`type: "many"`)
```json
{
"User": {
"hasRelations": {
"Post": {
"type": "many",
"table": "posts",
"localKey": "id",
"foreignKey": "user_id"
},
"Comment": {
"type": "many",
"table": "comments",
"localKey": "id",
"foreignKey": "user_id"
}
}
}
}
```
#### Many-to-Many Relationship (using `through`)
For many-to-many relationships, use the `through` key to specify the join/pivot table:
```json
{
"User": {
"hasRelations": {
"Role": {
"type": "many",
"table": "roles",
"localKey": "id",
"foreignKey": "id",
"through": "user_roles",
"throughLocalKey": "user_id",
"throughForeignKey": "role_id"
}
}
}
}
```
**Many-to-Many Structure:**
- `type`: `"many"` (always use "many" for many-to-many)
- `table`: The related table name (e.g., `"roles"`)
- `localKey`: The primary key in the current model (e.g., `"id"`)
- `foreignKey`: The primary key in the related table (e.g., `"id"`)
- `through`: **Required** - The join/pivot table name (e.g., `"user_roles"`)
- `throughLocalKey`: The foreign key in the join table pointing to the current model (e.g., `"user_id"`)
- `throughForeignKey`: The foreign key in the join table pointing to the related model (e.g., `"role_id"`)
### Using Relationships in Queries
#### Eager Loading with `with`
```javascript
// Load User with related Posts and Comments
POST /api/User/crud
{
"action": "list",
"where": { "id": 1 },
"with": ["Post", "Comment"]
}
// Nested relationships
POST /api/User/crud
{
"action": "list",
"where": { "id": 1 },
"with": ["Post.Comment", "Post.Like"]
}
// Multiple relationships
POST /api/User/crud
{
"action": "list",
"where": { "id": 1 },
"with": ["UserDetail", "Post", "Comment", "Like"]
}
```
#### Filtering Related Data with Nested Where
```javascript
// Get users who have posts with specific hashtag
POST /api/User/crud
{
"action": "list",
"where": {
"Post.hashtag": "#OrganicFarming"
},
"with": ["Post"]
}
// Get posts with comments from specific user
POST /api/Post/crud
{
"action": "list",
"where": {
"Comment.user_id": 1
},
"with": ["Comment"]
}
// Deeply nested where conditions
POST /api/Post/crud
{
"action": "list",
"where": {
"User.UserRole.Role.name": "admin"
},
"with": ["User", "User.UserRole", "User.UserRole.Role"]
}
// SQL: WHERE EXISTS (SELECT * FROM users WHERE users.id = posts.user_id
// AND EXISTS (SELECT * FROM user_roles WHERE user_roles.user_id = users.id
// AND EXISTS (SELECT * FROM roles WHERE roles.id = user_roles.role_id AND name = 'admin')))
```
#### NOT EXISTS Queries with `!` Prefix
Use the `!` prefix on relation names to check for the absence of related records:
```javascript
// Get posts where the user has NO UserRole entries
POST /api/Post/crud
{
"action": "list",
"where": {
"User.!UserRole": true
},
"with": ["User"]
}
// SQL: WHERE EXISTS (SELECT * FROM users WHERE users.id = posts.user_id
// AND NOT EXISTS (SELECT * FROM user_roles WHERE user_roles.user_id = users.id))
// Get users with no posts
POST /api/User/crud
{
"action": "list",
"where": {
"!Post": true
}
}
// SQL: WHERE NOT EXISTS (SELECT * FROM posts WHERE posts.user_id = users.id)
// Get posts where user has no admin role
POST /api/Post/crud
{
"action": "list",
"where": {
"User.!UserRole.Role.name": "admin"
}
}
// SQL: WHERE EXISTS (SELECT * FROM users WHERE users.id = posts.user_id
// AND NOT EXISTS (SELECT * FROM user_roles WHERE user_roles.user_id = users.id
// AND EXISTS (SELECT * FROM roles WHERE roles.id = user_roles.role_id AND name = 'admin')))
```
**NOT EXISTS Syntax Summary:**
| Pattern | Meaning | Use Case |
| ------------------------------- | ----------------------------- | ---------------------------------- |
| `"!RelName": true` | NOT EXISTS on direct relation | Users with no posts |
| `"Parent.!Child": true` | NOT EXISTS on nested relation | Posts where user has no roles |
| `"Parent.!Child.column": value` | NOT EXISTS with condition | Posts where user has no admin role |
````
#### Filtering Eager Loaded Relations with `withWhere`
The `withWhere` parameter allows you to apply additional where conditions when fetching related data. This filters the related rows without affecting the parent query.
```javascript
// Get all users with their posts, but only posts from user_id = 15
POST /api/User/crud
{
"action": "list",
"with": ["Post"],
"withWhere": {
"Post.user_id": 15
}
}
// Get users with active posts only
POST /api/User/crud
{
"action": "list",
"with": ["Post"],
"withWhere": {
"Post.status": "active",
"Post.is_published": true
}
}
// Multiple relations with different filters
POST /api/User/crud
{
"action": "list",
"with": ["Post", "Comment"],
"withWhere": {
"Post.status": "published",
"Post.created_at": ">=2024-01-01",
"Comment.is_approved": true
}
}
// Using operators in withWhere (same as regular where)
POST /api/User/crud
{
"action": "list",
"with": ["Post"],
"withWhere": {
"Post.title": "%tutorial%",
"Post.views": ">=100",
"Post.category": "[]tech,programming"
}
}
// Using OR conditions in withWhere
POST /api/User/crud
{
"action": "list",
"with": ["Post"],
"withWhere": {
"Post.status": "published",
"Or:Post.is_featured": true
}
}
````
**Key differences between `where` and `withWhere`:**
| Feature | `where` (Nested) | `withWhere` |
| -------------------- | ------------------------------------------- | ------------------------------------ |
| Affects parent query | Yes (filters parent rows) | No (only filters related rows) |
| Purpose | Filter parents that have matching relations | Filter which related rows are loaded |
| SQL generated | `WHERE EXISTS (subquery)` | Additional `WHERE` on relation query |
**Example showing the difference:**
```javascript
// This filters users who have published posts (affects which users are returned)
{
"action": "list",
"where": { "Post.status": "published" },
"with": ["Post"]
}
// Returns: Only users who have at least one published post
// Each user's Post array contains ALL their posts (not filtered)
// This loads all users but only their published posts
{
"action": "list",
"with": ["Post"],
"withWhere": { "Post.status": "published" }
}
// Returns: All users
// Each user's Post array contains ONLY their published posts
```
**withWhere supports all operators:**
- Comparison: `">=18"`, `"<=100"`, `">50"`, `"<10"`, `"!deleted"`
- LIKE: `"%pattern%"`
- IN: `"[]val1,val2,val3"`
- NOT IN: `"![]val1,val2"`
- BETWEEN: `">min,max"`
- NULL: `null`
- OR prefix: `"Or:column"`
### Example: Complete Relationship Structure
Example complete relationship structure:
```json
{
"User": {
"table": "users",
"hasRelations": {
"UserDetail": {
"type": "one",
"table": "user_details",
"localKey": "id",
"foreignKey": "user_id"
},
"Post": {
"type": "many",
"table": "posts",
"localKey": "id",
"foreignKey": "user_id"
},
"Comment": {
"type": "many",
"table": "comments",
"localKey": "id",
"foreignKey": "user_id"
},
"Like": {
"type": "many",
"table": "likes",
"localKey": "id",
"foreignKey": "user_id"
}
}
},
"Post": {
"table": "posts",
"hasRelations": {
"User": {
"type": "one",
"table": "users",
"localKey": "user_id",
"foreignKey": "id"
},
"Comment": {
"type": "many",
"table": "comments",
"localKey": "id",
"foreignKey": "post_id"
},
"Like": {
"type": "many",
"table": "likes",
"localKey": "id",
"foreignKey": "post_id"
}
}
}
}
```
### Custom Relation Hooks
For complex relationships that can't be defined in the schema, you can create custom relation hooks in your model class. This is useful for:
- Virtual/computed relations
- Cross-database relations
- Complex aggregations
- Custom data transformations
Create a method named `get{RelationName}Relation` in your model file:
```javascript
// models/Users.model.js
class Users {
// Custom relation hook for "Statistics" relation
async getStatisticsRelation({ rows, relName, model, withTree, controller, relation, qb, db }) {
// rows = parent rows to attach relation data to
// db = Knex database instance
// qb = QueryBuilder instance
for (const row of rows) {
// Fetch custom data for each row
const stats = await db('user_statistics').where('user_id', row.id).first();
// Attach to row
row.Statistics = stats || { posts: 0, comments: 0, likes: 0 };
}
}
// Custom relation with aggregation
async getPostCountRelation({ rows, db }) {
const userIds = rows.map((r) => r.id);
const counts = await db('posts')
.select('user_id')
.count('* as count')
.whereIn('user_id', userIds)
.groupBy('user_id');
const countMap = new Map(counts.map((c) => [c.user_id, c.count]));
for (const row of rows) {
row.PostCount = countMap.get(row.id) || 0;
}
}
// Custom relation from external API or different database
async getExternalProfileRelation({ rows }) {
for (const row of rows) {
// Fetch from external source
row.ExternalProfile = await fetchFromExternalAPI(row.external_id);
}
}
}
module.exports = Users;
```
**Using Custom Relations:**
```javascript
// Use custom relation just like schema-defined relations
POST /api/Users/crud
{
"action": "list",
"where": { "is_active": true },
"with": ["Statistics", "PostCount", "ExternalProfile"]
}
```
**Custom Relation Hook Arguments:**
| Argument | Description |
| ------------ | ----------------------------------------------------------------------- |
| `rows` | Parent rows to attach relation data to (modify in place) |
| `relName` | The relation name being fetched |
| `model` | Model definition object |
| `withTree` | Nested relations tree for further loading |
| `controller` | ControllerWrapper instance |
| `relation` | Relation definition from schema (may be undefined for custom relations) |
| `qb` | QueryBuilder instance for building queries |
| `db` | Knex database instance for direct queries |
### Important Notes
- `type: "one"` = One-to-One or Belongs-To relationship
- `type: "many"` = One-to-Many relationship
- `through` = Required for Many-to-Many relationships (specifies the join table)
- `localKey` = The key in the current model
- `foreignKey` = The key in the related table
- `throughLocalKey` = The key in the join table pointing to current model (for many-to-many)
- `throughForeignKey` = The key in the join table pointing to related model (for many-to-many)
- Custom relation hooks take precedence when relation is not defined in schema
## Soft Delete Support
### Enabling Soft Delete
Create a model file at `models/Users.model.js`:
```javascript
class Users {
// Enable soft delete for this model
hasSoftDelete = true;
// Optional: Custom soft delete hook
beforeDelete({ model, action, request, context, db, utils, controller }) {
// Custom logic before soft delete
console.log('Soft deleting user:', request.where);
}
// Optional: Custom hook after soft delete
afterDelete({ model, action, data, request, context, db, utils, controller }) {
// Custom logic after soft delete
console.log('User soft deleted:', data);
}
}
module.exports = Users;
```
### Using Soft Delete
```javascript
// When soft delete is enabled, delete action automatically sets deleted_at
POST /api/Users/crud
{
"action": "delete",
"where": { "id": 1 }
}
// List operation automatically filters out soft-deleted records
POST /api/Users/crud
{
"action": "list",
"where": { "is_active": true }
// Automatically adds: deleted_at IS NULL
}
// To see soft-deleted records, you need to query directly
// (soft delete only affects list and delete operations)
```
## Model Hooks
### Creating Model Hooks
Create a model file at `models/Users.model.js`:
```javascript
class Users {
// Soft delete support (property, not method)
hasSoftDelete = true;
// Validation hook - runs before any action
async validate({ model, action, request, context, db, utils, controller }) {
if (action === 'create' || action === 'update') {
if (!request.data.email) {
throw new Error('Email is required');
}
// Check if email already exists
const existing = await db('users').where('email', request.data.email).first();
if (existing && existing.id !== request.where?.id) {
throw new Error('Email already exists');
}
}
}
// Before hooks - run before the action executes
// Method naming: before{Action} (e.g., beforeCreate, beforeUpdate, beforeList, beforeDelete)
async beforeCreate({ model, action, request, context, db, utils, controller }) {
// Modify request data before insert
request.data.created_at = new Date();
request.data.updated_at = new Date();
return request.data;
}
async beforeUpdate({ model, action, request, context, db, utils, controller }) {
// Modify request data before update
request.data.updated_at = new Date();
return request.data;
}
async beforeDelete({ model, action, request, context, db, utils, controller }) {
// Logic before delete (works with both hard and soft delete)
console.log('Deleting user:', request.where);
}
// After hooks - run after the action executes
// Method naming: after{Action} (e.g., afterCreate, afterUpdate, afterList, afterDelete)
async afterCreate({ model, action, data, request, context, db, utils, controller }) {
// data contains the result of the action
console.log('User created:', data);
// Send welcome email, trigger notifications, etc.
return data;
}
async afterUpdate({ model, action, data, request, context, db, utils, controller }) {
console.log('User updated:', data);
return data;
}
async afterList({ model, action, data, request, context, db, utils, controller }) {
// Modify list results before returning
return data;
}
// Custom action hooks
// Method naming: on{Action}Action (e.g., onActivateAction, onDeactivateAction)
async onActivateAction({ model, action, request, context, db, utils, controller }) {
return await db('users')
.where(request.where)
.update({ is_active: true, updated_at: new Date() });
}
async onDeactivateAction({ model, action, request, context, db, utils, controller }) {
return await db('users')
.where(request.where)
.update({ is_active: false, updated_at: new Date() });
}
}
module.exports = Users;
```
### Hook Arguments Reference
| Argument | Description |
| ------------ | ------------------------------------------------------------- |
| `model` | Model definition object with table, columns, relations |
| `action` | Current action being performed (create, update, delete, etc.) |
| `request` | The request object containing where, data, etc. |
| `context` | Custom context passed from the controller |
| `db` | Knex database instance for direct queries |
| `utils` | Utility functions |
| `controller` | ControllerWrapper instance |
| `data` | (After hooks only) Result of the action |
### Complete Hook Types Reference
| Hook Type | Method Naming | When Called | Use Case |
| --------------- | ---------------------- | ---------------------- | --------------------------------------- |
| Validate | `validate` | Before any action | Input validation, authorization |
| Before | `before{Action}` | Before action executes | Modify request data, add timestamps |
| After | `after{Action}` | After action executes | Transform results, trigger side effects |
| Custom Action | `on{Action}Action` | For custom actions | Implement business logic |
| Soft Delete | `hasSoftDelete = true` | During delete/list | Enable soft delete |
| Custom Relation | `get{RelName}Relation` | During eager loading | Custom data fetching |
**Available Before/After hooks:**
- `beforeCreate` / `afterCreate`
- `beforeUpdate` / `afterUpdate`
- `beforeDelete` / `afterDelete`
- `beforeList` / `afterList`
- `beforeShow` / `afterShow`
- `beforeCount` / `afterCount`
- `beforeReplace` / `afterReplace`
- `beforeUpsert` / `afterUpsert`
- `beforeSync` / `afterSync`
### Using Custom Actions
```javascript
// Call custom action - triggers on{Action}Action hook
POST /api/Users/crud
{
"action": "activate",
"where": { "id": 1 }
}
POST /api/Users/crud
{
"action": "deactivate",
"where": { "id": 1 }
}
// You can create any custom action
POST /api/Users/crud
{
"action": "sendWelcomeEmail",
"where": { "id": 1 }
}
// Triggers: onSendWelcomeEmailAction({ model, action, request, context, db, utils, controller })
```
## Data Validation
### Basic Validation
```javascript
const { validate } = require('@dreamtree-org/korm-js');
// Define validation rules
const userValidationRules = {
username: 'required|type:string|minLen:3|maxLen:50',
email: 'required|type:string|maxLen:255',
first_name: 'required|type:string|maxLen:100',
last_name: 'required|type:string|maxLen:100',
age: 'type:number|min:0|max:150',
is_active: 'type:boolean',
};
// Validate and create user
app.post('/api/users/validate', async (req, res) => {
try {
// Validate request data
const validatedData = await validate(req.body, userValidationRules);
const result = await korm.processRequest(
{
action: 'create',
data: validatedData,
},
'Users'
);
res.status(201).json({
success: true,
message: 'User created successfully',
data: result,
});
} catch (error) {
if (error.name === 'ValidationError') {
return res.status(400).json({
success: false,
message: 'Validation failed',
errors: error.message,
});
}
res.status(500).json({
success: false,
message: 'Error creating user',
error: error.message,
});
}
});
```
### Advanced Validation
```javascript
const { validate } = require('@dreamtree-org/korm-js');
// Advanced validation rules
const advancedRules = {
username: 'required|type:string|regex:username',
email: 'required|type:string|regex:email',
phone: 'regex:phone',
password: 'required|type:string|minLen:8',
status: 'in:active,inactive,pending',
user_id: 'exists:users,id',
};
const customRegex = {
username: /^[a-zA-Z0-9_]+$/,
email: /^[^\s@]+@[^\s@]+\.[^\s@]+$/,
phone: /^\+?[\d\s-()]{10,15}$/,
};
const validatedData = await validate(data, advancedRules, { customRegex });
```
## Nested Requests
```javascript
// Process multiple related requests in one call
POST /api/Users/crud
{
"action": "show",
"where": { "id": 1 },
"other_requests": {
"Posts": {
"action": "list",
"where": { "user_id": 1 },
"limit": 10
},
"Comments": {
"action": "list",
"where": { "user_id": 1 },
"limit": 5
}
}
}
// Response includes nested data
{
"id": 1,
"username": "john_doe",
"email": "john@example.com",
"other_responses": {
"Posts": {
"data": [/* posts */],
"total": 10
},
"Comments": {
"data": [/* comments */],
"total": 5
}
}
}
```
## Schema Structure
### Example Schema (Auto-generated from MySQL)
```javascript
{
"Users": {
"table": "users",
"alias": "Users",
"modelName": "Users",
"columns": {
"id": "bigint|size:8|unsigned|primaryKey|autoIncrement",
"username": "varchar|size:255|notNull|unique",
"email": "varchar|size:255|notNull",
"first_name": "varchar|size:255",
"last_name": "varchar|size:255",
"age": "int|size:4",
"is_active": "tinyint|size:1|default:1",
"created_at": "timestamp|default:CURRENT_TIMESTAMP",
"updated_at": "timestamp|default:CURRENT_TIMESTAMP|onUpdate:CURRENT_TIMESTAMP",
"deleted_at": "timestamp"
},
"seed": [],
"hasRelations": {},
"indexes": [
{
"name": "idx_users_email",
"columns": ["email"],
"unique": true
}
]
}
}
```
### Column Definition String Format
Column definitions use a pipe-separated string format:
```
type|modifier1|modifier2|...
```
**Column Modifiers:**
| Modifier | Description | Example |
| -------------------------- | ------------------ | ---------- | --------------------------- | -------------- |
| `size:n` | Column size | `varchar | size:255` |
| `unsigned`ΒΉ | Unsigned integer | `int | unsigned` |
| `primaryKey` | Primary key column | `bigint | primaryKey` |
| `autoIncrement` | Auto increment | `bigint | primaryKey | autoIncrement` |
| `notNull` | Not nullable | `varchar | size:255 | notNull` |
| `unique` | Unique constraint | `varchar | unique` |
| `default:value` | Default value | `tinyint | default:1` |
| `onUpdate:value`Β² | On update value | `timestamp | onUpdate:CURRENT_TIMESTAMP` |
| `comment:text` | Column comment | `varchar | comment:User email address` |
| `foreignKey:table:column`Β³ | Foreign key | `int | foreignKey:users:id` |
ΒΉ **Engine-specific.** `unsigned` is honored on MySQL and SQLite. PostgreSQL has no unsigned integer type and silently drops the modifier.
Β² **Engine-specific.** `onUpdate` is honored on MySQL (emitted via `ON UPDATE `). PostgreSQL and SQLite log a one-time warning and ignore it β the modifier cannot be expressed inline on those engines. See [`docs/agents/05-multi-db-parity.md`](docs/agents/05-multi-db-parity.md).
Β³ **Auto type-matching.** When the referenced table is part of the same schema, `syncDatabase()` automatically widens the foreign-key column's type to match the referenced primary key, so the FK constraint is always type-compatible. In particular, an `autoIncrement` primary key is emitted as `BIGINT UNSIGNED` (MySQL) / `BIGSERIAL` (PostgreSQL) / `INTEGER` (SQLite) via Knex's `.increments()`, so a child column written as `int|foreignKey:β¦` is created as `BIGINT` β you do **not** need to hand-match the width. Foreign keys to tables outside the schema (pre-existing/third-party) are created exactly as declared.
> **Dependency ordering.** `syncDatabase()` creates tables in foreign-key dependency order (parents before the children that reference them), so you can declare your models in **any order** β a child listed before its parent still syncs cleanly (MySQL/PostgreSQL otherwise reject the forward FK reference). FKs to tables outside the schema impose no ordering; a genuine circular FK dependency falls back to declaration order with a warning.
> **Orphan-row guard.** Adding a `notNull` foreign-key column to a table that **already has rows** makes the database back-fill those rows (with `0` for a numeric column, or the column default). If that value has no matching parent row, MySQL/PostgreSQL reject the new constraint with a cryptic driver error (MySQL `errno 1452`, `ER_NO_REFERENCED_ROW_2`). `syncDatabase()` now detects this **before** altering the table and throws a clear `KormError` (`code: 'SYNC_FK_ORPHAN'`) naming the child column, the parent it points at, the affected row count, and the offending back-fill value β **no data is mutated**. Resolve it by making the column `nullable` (existing rows back-fill `NULL`, which is FK-safe), giving it a `default` that exists in the parent, populating the column for existing rows before syncing, or inserting the missing parent rows.
#### `syncDatabase(options)` β additive-only, resilient, dry-run
`syncDatabase()` accepts an optional `options` object that controls how it reconciles drift between your schema and the live database. The defaults are **safe**: it never drops data and never half-migrates silently.
| Option | Default | Effect |
| ----------------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `dropColumns` | `false` | When `false` (default), a column that exists in the database but is **absent from the schema** is **left in place** (reported as drift, never dropped). Set `true` to drop such columns. |
| `prune` | β | Alias for `dropColumns`. |
| `continueOnError` | `false` | When `false` (default), the first failing statement throws and aborts the run (fail-fast). When `true`, a failing statement is **skipped and collected**, and the rest of the schema still applies. |
| `dryRun` | `false` | When `true`, walks the same diff **without executing anything** and returns the planned operations (destructive drops flagged). The database is not touched. |
**Why the additive-only default matters.** Previously `syncDatabase()` dropped any column not declared in the schema β silently destroying data for drifted/legacy columns, and aborting the entire multi-table sync if the drop was blocked (e.g. the column was referenced by a foreign key). The default is now **additive** (create tables, add columns); destructive drops are opt-in.
**Return value.** A default sync resolves to `undefined` (unchanged). When you pass `dryRun: true` **or** `continueOnError: true`, it resolves to a **report**:
```javascript
// Preview what a sync would do β change nothing
const plan = await korm.syncDatabase({ dryRun: true });
// {
// dryRun: true,
// plan: [{ table: 'users', operation: 'add', column: 'bio', destructive: false }, β¦],
// skippedDrops: [{ table: 'products', column: 'warehouse' }], // drift left in place
// applied: [], errors: [],
// }
// Drop drifted columns too
await korm.syncDatabase({ dropColumns: true });
// Keep going past failures, then inspect what was skipped
const report = await korm.syncDatabase({ continueOnError: true });
// report.errors β [{ table, operation, column, message }]
// report.applied β [{ table, operation, column }]
```
**Special Default Values:**
- `now` or `now()` β `CURRENT_TIMESTAMP`
**Example Column Definitions:**
```javascript
{
"id": "bigint|size:8|unsigned|primaryKey|autoIncrement",
"user_id": "int|unsigned|notNull|foreignKey:users:id",
"status": "enum|in:active,inactive,pending|default:pending",
"created_at": "timestamp|default:now",
"updated_at": "timestamp|default:now|onUpdate:now",
"bio": "text|comment:User biography"
}
```
### Seed Data
You can define seed data in the schema to auto-populate tables:
```javascript
{
"Roles": {
"table": "roles",
"columns": {
"id": "int|primaryKey|autoIncrement",
"name": "varchar|size:50|notNull|unique",
"description": "text"
},
"seed": [
{ "name": "admin", "description": "Administrator role" },
{ "name": "user", "description": "Regular user role" },
{ "name": "moderator", "description": "Moderator role" }
]
}
}
```
Seed data is automatically inserted when `syncDatabase()` is called and the table is empty.
## API Reference
### KORM Instance Methods
```javascript
const { initializeKORM } = require('@dreamtree-org/korm-js');
// Schema as an inline object (existing behaviour):
const korm = await initializeKORM({
db: db, // Knex database instance
dbClient: 'mysql', // 'mysql', 'mysql2', 'pg', 'postgresql', 'sqlite', 'sqlite3'
schema: null, // Optional: schema object, or a string (file path / URL β see below)
resolverPath: null, // Optional: path to models directory (default: process.cwd())
debug: false, // Optional: enable SQL debugging (default: false)
});
// Schema auto-resolved from a string (path or URL):
// "./schema.json" β read & JSON.parse
// "./schema.js" β require() (CJS: module.exports = {β¦})
// "./schema.mjs" β dynamic import() (ESM: export default {β¦})
// "https://api.example.com/schema" β fetch & JSON.parse
const korm2 = await initializeKORM({
db, dbClient: 'sqlite',
schema: './schema.json',
});
```
// Process any CRUD request (automatically handles other_requests if present)
const result = await korm.processRequest(requestBody, modelName, context);
// Process request with nested requests (legacy - now same as processRequest)
const result = await korm.processRequestWithOthers(requestBody, modelName, context);
// Set schema manually
korm.setSchema(schemaObject);
// Sync database with schema (additive-only by default: creates tables, adds
// columns; never drops drifted columns unless you opt in). Options:
// { dropColumns | prune, continueOnError, dryRun } β see "syncDatabase(options)".
await korm.syncDatabase();
// Generate schema from existing database
const schema = await korm.generateSchema();
// Load model class from models/{ModelName}.model.js
const ModelClass = korm.loadModelClass('Users');
// Get model instance
const modelInstance = korm.getModelInstance(modelDef);
```
### ProcessRequest Options
| Parameter | Type | Description |
| ---------------- | -------------------- | ------------------------------------------------------------------------------------ |
| `action` | string | Action to perform (list, show, create, update, delete, count, replace, upsert, sync) |
| `where` | object/array | Filter conditions |
| `data` | object/array | Data for create/update operations |
| `select` | array/string | Columns to select |
| `orderBy` | object/array/string | Sorting configuration |
| `limit` | number | Maximum records to return |
| `offset` | number | Records to skip |
| `page` | number | Page number (alternative to offset) |
| `with` | array | Related models to eager load |
| `withWhere` | object | Filter conditions for eager loaded relations |
| `groupBy` | array/string | Group by columns |
| `having` | object | Having conditions |
| `distinct` | boolean/array/string | Distinct results |
| `join` | object/array | Join configuration |
| `leftJoin` | object/array | Left join configuration |
| `rightJoin` | object/array | Right join configuration |
| `innerJoin` | object/array | Inner join configuration |
| `conflict` | array | Conflict columns for upsert |
| `other_requests` | object | Nested requests for related models |
### ProcessRequest Actions Summary
| Action | Description | Required Fields |
| --------- | -------------------------------------------------------- | ---------------------------------------------------------------- |
| `list` | Get multiple records | None (optional: `where`, `select`, `orderBy`, `limit`, `offset`) |
| `show` | Get single record | `where` |
| `create` | Create new record | `data` |
| `update` | Update record(s) | `where`, `data` |
| `delete` | Delete record(s) | `where` |
| `count` | Count records | None (optional: `where`) |
| `sum` | Sum column or expression | `data.sumColumn` or `data.sumFormula` (optional: `where`) |
| `replace` | Replace full row by PK (all engines; pg = merge, see Β§8) | `data` (optional: `conflict`) |
| `upsert` | Insert or update | `data`, `conflict` |
| `sync` | Upsert + delete | `data`, `conflict`, `where` |
### Validation Rules
| Rule | Description | Example |
| --------------------- | -------------------------------------------- | ------------------------------ |
| `required` | Field is required | `'required'` |
| `type:string` | Field must be string | `'type:string'` |
| `type:number` | Field must be number | `'type:number'` |
| `type:boolean` | Field must be boolean | `'type:boolean'` |
| `type:array` | Field must be array | `'type:array'` |
| `type:object` | Field must be object | `'type:object'` |
| `type:longText` | Field must be string > 255 chars | `'type:longText'` |
| `minLen:n` | Minimum string/array length | `'minLen:3'` |
| `maxLen:n` | Maximum string/array length | `'maxLen:255'` |
| `min:n` | Minimum numeric value | `'min:0'` |
| `max:n` | Maximum numeric value | `'max:150'` |
| `in:val1,val2` | Value must be in list | `'in:active,inactive,pending'` |
| `regex:name` | Custom regex pattern (define in options) | `'regex:email'` |
| `call:name` | Custom callback function (define in options) | `'call:myValidator'` |
| `exists:table,column` | Value must exist in database table | `'exists:users,id'` |
| `default:value` | Default value if not provided | `'default:active'` |
**Rule Chaining:** Combine multiple rules with `|` pipe character:
```javascript
{
username: 'required|type:string|minLen:3|maxLen:50',
email: 'required|type:string|regex:email',
age: 'type:number|min:0|max:150',
status: 'in:active,inactive|default:active'
}
```
### Library Exports
```javascript
const {
initializeKORM, // Initialize KORM with database connection
helperUtility, // Utility functions (file operations, string manipulation)
emitter, // Event emitter instance
validate, // Validation function
logger, // Logger utility instance
lib, // Additional utilities
LibClasses, // Library classes (Emitter)
} = require('@dreamtree-org/korm-js');
// lib contains:
// - createValidationMiddleware(rules, options) - Express middleware
// - validateEmail(email) - Email validation
// - validatePassword(password) - Password strength validation
// - validatePhone(phone) - Phone number validation
// - validatePAN(pan) - PAN validation (India)
// - validateAadhaar(aadhaar) - Aadhaar validation (India)
// helperUtility.file contains:
// - readJSON(path) - Read JSON file
// - writeJSON(path, data) - Write JSON file
// - createDirectory(path) - Create directory
```
## Logger Utility
KORM includes a built-in logger utility with configurable log levels for debugging and monitoring.
### Basic Usage
```javascript
const { logger } = require('@dreamtree-org/korm-js');
// Log methods (from most to least verbose)
logger.debug('Detailed debugging information');
logger.log('General log message');
logger.info('Informational message');
logger.warn('Warning message');
logger.error('Error message');
```
### Log Levels
| Level | Value | Description |
| ------- | ----- | ----------------------------- |
| `NONE` | 0 | Disable all logging |
| `ERROR` | 1 | Only errors |
| `WARN` | 2 | Warnings and errors (default) |
| `INFO` | 3 | Info, warnings, and errors |
| `LOG` | 4 | General logs and above |
| `DEBUG` | 5 | All messages (most verbose) |
### Configuration
```javascript
const { logger } = require('@dreamtree-org/korm-js');
// Set log level programmatically
logger.setLevel('debug'); // Show all logs
logger.setLevel('warn'); // Only warnings and errors (default)
logger.setLevel('error'); // Only errors
logger.setLevel('none'); // Disable all logging
// Enable/disable logging
logger.disable(); // Temporarily disable all logging
logger.enable(); // Re-enable logging
```
### Environment Variable
Set the log level via environment variable:
```bash
# In your .env file or environment
KORM_LOG_LEVEL=debug # Show all logs
KORM_LOG_LEVEL=warn # Only warnings and errors (default)
KORM_LOG_LEVEL=error # Only errors
KORM_LOG_LEVEL=none # Disable logging
```
```javascript
// The logger automatically reads KORM_LOG_LEVEL on initialization
// KORM_LOG_LEVEL=debug node app.js
```
### Creating Child Loggers
Create child loggers with custom prefixes for different modules:
```javascript
const { logger } = require('@dreamtree-org/korm-js');
// Create a child logger for a specific module
const authLogger = logger.child('[Auth]');
authLogger.info('User logged in'); // Output: [KORM][Auth] [INFO] User logged in
const dbLogger = logger.child('[DB]');
dbLogger.debug('Query executed'); // Output: [KORM][DB] [DEBUG] Query executed
```
### Logger Options
```javascript
const { Logger } = require('@dreamtree-org/korm-js').logger;
// Create a custom logger instance
const customLogger = new Logger({
prefix: '[MyApp]', // Custom prefix (default: '[KORM]')
enabled: true, // Enable/disable logging (default: true)
level: 'debug', // Log level (default: 'warn' or KORM_LOG_LEVEL)
timestamps: true, // Include timestamps (default: false)
});
customLogger.info('Application started');
// Output: [MyApp] [2024-01-15T10:30:00.000Z] [INFO] Application started
```
### Usage in Development vs Production
```javascript
// Development: Enable debug logging
// KORM_LOG_LEVEL=debug node app.js
// Production: Only show warnings and errors
// KORM_LOG_LEVEL=warn node app.js
// Or configure programmatically
const { logger } = require('@dreamtree-org/korm-js');
if (process.env.NODE_ENV === 'development') {
logger.setLevel('debug');
} else {
logger.setLevel('warn');
}
```
## Inspecting queries (`dryRun`)
Add `dryRun: true` to any request to get back the SQL it **would** run β
without executing anything. Useful for audit pipelines, previewing
destructive operations, and letting an AI agent review SQL before
committing to it.
```javascript
const result = 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: ['archived'] }],
// }
```
- Validation still runs (you still get a `KormError` for a bad request).
- `before`/`after` model hooks do **not** fire, and the database is
untouched.
- Bindings are returned as a separate array (not interpolated into
`sql`), so you can re-parameterize.
- `sync` returns both statements (upsert + delete) in `statements`.
Full reference: [`docs/agents/06-request-contract.md`](docs/agents/06-request-contract.md) Β§9.
## SQL Debugging
Enable SQL debugging to see the exact SQL statements generated by your queries. This is useful for troubleshooting complex queries and understanding how KORM translates your requests.
### Enabling Debug Mode
```javascript
const { initializeKORM } = require('@dreamtree-org/korm-js');
const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: true, // Enable SQL debugging
});
```
### Debug Output
When `debug: true`, list queries will include a `sqlDebug` array in the response:
```javascript
// Request
POST /api/Post/crud
{
"action": "list",
"where": {
"User.!UserRole": true
},
"limit": 5
}
// Response with debug: true
{
"data": [...],
"totalCount": 10,
"sqlDebug": [
"select * from `posts` where exists (select * from `users` where `users`.`id` = `posts`.`user_id` and not exists (select * from `user_roles` where `user_roles`.`user_id` = `users`.`id`)) order by `id` asc limit ?",
"select count(*) as `cnt` from `posts` where exists (select * from `users` where `users`.`id` = `posts`.`user_id` and not exists (select * from `user_roles` where `user_roles`.`user_id` = `users`.`id`))"
],
"pagination": {
"page": 1,
"limit": 5,
"offset": 0,
"totalPages": 2,
"hasNext": true,
"hasPrev": false,
"nextPage": 2,
"prevPage": null
}
}
```
### What's Included in sqlDebug
| Index | Query Type | Description |
| ----- | ----------- | ---------------------------------------------------------------- |
| 0 | Main Query | The primary SELECT query with all WHERE, ORDER BY, LIMIT clauses |
| 1 | Count Query | The COUNT query used for pagination (only when limit > 0) |
### Debug Mode Best Practices
```javascript
// Development: Enable debug mode
const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: process.env.NODE_ENV === 'development',
});
// Or use environment variable
const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: process.env.KORM_DEBUG === 'true',
});
```
**Note:** The `sqlDebug` field is only included in responses when `debug: true`. In production, set `debug: false` to exclude SQL statements from responses.
## Database Support
### MySQL (Current Guide)
```javascript
const knex = require('knex');
const db = knex({
client: 'mysql2',
connection: {
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'root',
password: process.env.DB_PASS || 'password',
database: process.env.DB_NAME || 'database_name',
port: process.env.DB_PORT || 3306,
},
});
const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: process.env.NODE_ENV === 'development', // Enable SQL debugging in development
});
```
### PostgreSQL
```javascript
const knex = require('knex');
const db = knex({
client: 'pg',
connection: {
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'username',
password: process.env.DB_PASS || 'password',
database: process.env.DB_NAME || 'database_name',
port: process.env.DB_PORT || 5432,
},
});
const korm = initializeKORM({
db: db,
dbClient: 'pg',
debug: process.env.NODE_ENV === 'development',
});
```
### SQLite
```javascript
const knex = require('knex');
const db = knex({
client: 'sqlite3',
connection: {
filename: process.env.DB_FILE || './database.sqlite',
},
});
const korm = initializeKORM({
db: db,
dbClient: 'sqlite',
debug: process.env.NODE_ENV === 'development',
});
```
## Using KORM-JS as an AI tool
The discovery β request flow for an LLM agent is two calls: **describe** what's available, then build a request constrained by its **JSON Schema**.
### 1. Discover the schema β `describeSchema()` / `describeModel(name)`
Pure-data, JSON-safe introspection (no hooks, credentials, or internals leak). Use it to load context before generating a request.
```javascript
korm.describeSchema();
// β { schemaApiVersion: 1, models: [ { model, table, columns, relations, softDelete, actions }, β¦ ] }
korm.describeModel('User');
// β {
// schemaApiVersion: 1,
// model: 'User', table: 'users', alias: 'User',
// columns: [ { name: 'id', type: 'integer', primaryKey: true, autoIncrement: true, nullable: false }, β¦ ],
// relations: [ { name: 'Post', type: 'many', table: 'posts', localKey: 'id', foreignKey: 'user_id' } ],
// softDelete: false,
// actions: ['list','show','count','sum','create','update','delete','replace','upsert','sync'],
// }
```
Unknown models throw a `KormError` with `code: 'UNKNOWN_MODEL'` (the `context.available` list helps the caller recover).
### 2. Constrain the request β `getRequestJsonSchema(modelName)`
`korm.getRequestJsonSchema(modelName)` returns a draft-2020-12 JSON Schema describing every valid `processRequest` body for that model β an `action`-discriminated `oneOf` with typed `data`, a `select`/`orderBy`/`conflict` constrained to the model's columns, and inline descriptions. Attach it to an OpenAI / Anthropic tool definition, or use it for client-side prevalidation, so the model's output is constrained to a request your app can actually run.
```javascript
const schema = korm.getRequestJsonSchema('User');
// OpenAI tool definition
const tool = {
type: 'function',
function: {
name: 'query_users',
description: 'Query or mutate the User model via KORM-JS.',
parameters: schema, // the oneOf-over-actions request schema
},
};
// Anthropic tool definition
const anthropicTool = {
name: 'query_users',
description: 'Query or mutate the User model via KORM-JS.',
input_schema: schema,
};
```
The schema is derived from the model's column definitions and relations, so it stays in sync with your schema. Unknown models throw a `KormError` with `code: 'UNKNOWN_MODEL'`.
## Running as an MCP server
KORM-JS ships with an optional [Model Context Protocol](https://modelcontextprotocol.io) server. It exposes your KORM-registered tables as typed JSON-in/JSON-out tools that any MCP client (Claude Desktop, Claude Code, Cursor, custom agents) can call β no HTTP layer, no hand-written CRUD.
Two equivalent ways to launch it (both take the same `--config`):
```bash
korm-mcp --config ./korm-mcp.config.js # the dedicated bin
npx -y @dreamtree-org/korm-js mcp --config ./korm-mcp.config.js # the `mcp` subcommand (zero install)
```
### Install the SDK
The MCP SDK is an _optional_ dependency. If `npm install @dreamtree-org/korm-js` did not auto-install it (locked-down registry, offline mirror, etc.), pull it in explicitly:
```bash
npm install @modelcontextprotocol/sdk
```
### Write a config
`korm-mcp.config.js`:
```javascript
const knex = require('knex');
const schema = require('./schema'); // your KORM schema map
module.exports = {
// Same shape as initializeKORM
db: knex({ client: 'pg', connection: process.env.DATABASE_URL }),
dbClient: 'pg',
schema,
resolverPath: './models', // optional, for model hooks
debug: false,
mcp: {
mode: 'ro', // 'ro' | 'rw' | 'rw-sync'
allowlist: ['User', 'Post', 'Comment'], // flat list of model names; '*' allowed only in 'ro'
blocklist: [], // applied after allowlist
metaTools: true, // korm.list_tables, korm.describe_schema, korm.health
allowNestedRequests: false, // gate `other_requests` (off by default)
customActions: [], // [{ table, action, schema?, description? }]
},
};
```
### Wire it into your MCP client
```json
{
"mcpServers": {
"my-app-db": {
"command": "korm-mcp",
"args": ["--config", "/abs/path/to/korm-mcp.config.js"]
}
}
}
```
Or, with no global install, use the `mcp` subcommand via `npx`:
```json
{
"mcpServers": {
"my-app-db": {
"command": "npx",
"args": ["-y", "@dreamtree-org/korm-js", "mcp", "--config", "/abs/path/to/korm-mcp.config.js"]
}
}
}
```
### What you get
For each allowlisted table, the server emits one tool per action permitted by `mcp.mode`. Example for a `User` model:
| Tool | Available in mode | Maps to |
| --------------- | --------------------- | -------------------------------------------- |
| `users.list` | `ro`, `rw`, `rw-sync` | `processRequest({ action: 'list', ... })` |
| `users.show` | `ro`, `rw`, `rw-sync` | `processRequest({ action: 'show', ... })` |
| `users.count` | `ro`, `rw`, `rw-sync` | `processRequest({ action: 'count', ... })` |
| `users.sum` | `ro`, `rw`, `rw-sync` | `processRequest({ action: 'sum', ... })` |
| `users.create` | `rw`, `rw-sync` | `processRequest({ action: 'create', ... })` |
| `users.update` | `rw`, `rw-sync` | `processRequest({ action: 'update', ... })` |
| `users.delete` | `rw`, `rw-sync` | `processRequest({ action: 'delete', ... })` |
| `users.upsert` | `rw`, `rw-sync` | `processRequest({ action: 'upsert', ... })` |
| `users.replace` | `rw`, `rw-sync` | `processRequest({ action: 'replace', ... })` |
| `users.sync` | `rw-sync` only | `processRequest({ action: 'sync', ... })` |
Three meta tools (unless disabled via `mcp.metaTools: false`):
| Tool | Purpose |
| ---------------------- | -------------------------------------------------------------- |
| `korm.list_tables` | List the allowlisted tables with column / relation counts. |
| `korm.describe_schema` | Return columns + relations for a single allowlisted table. |
| `korm.health` | Engine name, library version, allowlist size, `SELECT 1` ping. |
### Safety properties
- **No raw SQL surface.** Tools always go through `processRequest`, which routes user-supplied values through Knex bindings.
- **Writes are off by default.** `mcp.mode` defaults to `ro`; opting into `rw` or `rw-sync` is a deliberate config choice that also requires a non-`*` allowlist.
- **Nested requests are off by default.** `other_requests` from the LLM is stripped unless you set `mcp.allowNestedRequests: true`.
- **Custom action hooks are not auto-exposed.** Add an explicit entry to `mcp.customActions` to make an `on{Action}` hook callable.
See `docs/agents/11-mcp-server.md` for the full design rationale and the locked decisions behind these defaults.
## Authorization (multi-tenancy & RBAC)
KORM-JS enforces authorization **at the contract layer**, in front of every engine, via two opt-in registrations. The `ctx` you already pass as the third argument to `processRequest(body, model, ctx)` is what predicates and scopes receive β wire it from your auth middleware.
```js
const korm = initializeKORM({ db, dbClient, schema });
// 1) Per-(model, action) permission predicate. Return falsy β the request
// throws KormError({ code: 'FORBIDDEN' }) before any SQL runs (dryRun too).
korm.authorize('User', 'delete', (req, ctx) => ctx.user.role === 'admin');
korm.authorize('Invoice', '*', (req, ctx) => Boolean(ctx.tenantId)); // '*' = every action
// 2) Row scope. Merged into `where` for reads / update / delete / count / sum,
// and stamped into each inserted `data` row for create / replace / upsert /
// sync β so a tenant can neither read nor create rows outside its scope.
korm.scope('Invoice', (req, ctx) => ({ tenantId: ctx.tenantId }));
```
```js
// In your route handler β ctx carries the authenticated principal:
app.post('/api/:model', (req, res) =>
korm.processRequest(req.body, req.params.model, { user: req.user, tenantId: req.user.tenantId })
);
```
- **Opt-in & non-breaking:** with no `authorize()`/`scope()` registered, requests pass through unchanged.
- **Scope wins:** a client cannot widen its scope via `where`, nor create a row in another tenant via `data` β the scope value overrides any conflicting client value.
- **Capability discovery:** `describeModel(name, ctx)` returns `actions` filtered to those permitted for `ctx`, so an agent sees what it may call before generating a request.
- **Denied requests** throw `KormError({ code: 'FORBIDDEN', context: { model, action } })` (see below). A throwing predicate fails closed (denies).
> Scoping applies to every read/write action β including **`count`** and **`sum`** (they honor `where`) β so a tenant's totals reflect only its own rows.
## Error Handling
`processRequest` and `validate` throw a structured **`KormError`** (which
extends the native `Error`). Branch on `error.code` rather than
string-matching `error.message`. Full reference: [`doc/ERRORS.md`](doc/ERRORS.md).
| `code` | Meaning |
| ----------------------- | ---------------------------------------------------------------- |
| `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 (`error.context.fields`) |
| `UNKNOWN_MODEL` | Model name not in the schema (`error.context.available`) |
| `FORBIDDEN` | An `authorize()` predicate denied the request |
| `SYNC_FK_ORPHAN` | Sync would orphan rows with a new FK (`error.context.offenders`) |
| `INTERNAL` | Internal invariant / misconfiguration |
```javascript
const { KormError } = require('@dreamtree-org/korm-js');
app.post('/api/:model/crud', async (req, res) => {
try {
const result = await korm.processRequest(req.body, req.params.model);
res.json(result);
} catch (error) {
if (error instanceof KormError) {
const status =
error.code === 'UNKNOWN_MODEL' || error.code === 'NO_MATCHING_ROW'
? 404
: error.code === 'VALIDATION_FAILED' ||
error.code === 'UNKNOWN_ACTION' ||
error.code === 'NO_CUSTOM_ACTION_HOOK'
? 400
: 500;
// error.toJSON() β { name, code, message, hint, context, suggestedFixes }
return res.status(status).json({ success: false, error: error.toJSON() });
}
res.status(500).json({ success: false, error: 'Internal server error' });
}
});
```
> **Migration note.** Validation errors previously surfaced with
> `name: 'ValidationError'`. They are now `KormError` with
> `code === 'VALIDATION_FAILED'` (the raw field errors remain on
> `error.errors` for back-compat; per-field detail is also under
> `error.context.fields`). Switch `error.name === 'ValidationError'`
> checks to `error.code === 'VALIDATION_FAILED'`.
## Complete Example Application
```javascript
require('dotenv').config();
const express = require('express');
const { initializeKORM, validate, helperUtility } = require('@dreamtree-org/korm-js');
const knex = require('knex');
const app = express();
app.use(express.json());
// MySQL configuration
const db = knex({
client: 'mysql2',
connection: {
host: process.env.DB_HOST || 'localhost',
user: process.env.DB_USER || 'root',
password: process.env.DB_PASS || 'password',
database: process.env.DB_NAME || 'my_database',
port: process.env.DB_PORT || 3306,
},
});
// Initialize KORM
const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: process.env.NODE_ENV === 'development', // SQL debugging in dev mode
});
// Initialize app
async function initApp() {
try {
// Load or generate schema
let schema = helperUtility.file.readJSON('schema/schema.json');
if (schema) {
korm.setSchema(schema);
} else {
schema = await korm.generateSchema();
helperUtility.file.createDirectory('schema');
helperUtility.file.writeJSON('schema/schema.json', schema);
}
// Sync database
await korm.syncDatabase();
console.log('β
Database synced');
} catch (error) {
console.error('β Initialization error:', error);
}
}
// Generic CRUD endpoint
app.post('/api/:model/crud', async (req, res) => {
try {
const { model } = req.params;
const result = await korm.processRequest(req.body, model);
res.json(result);
} catch (error) {
res.status(400).json({ error: error.message });
}
});
// Health check
app.get('/health', (req, res) => {
res.send('OK');
});
// Start server
const PORT = process.env.PORT || 3000;
app.listen(PORT, async () => {
console.log(`π Server running on http://localhost:${PORT}`);
await initApp();
});
```
## Contributing
1. Fork the repository
2. Create your feature branch (`git checkout -b feature/amazing-feature`)
3. Commit your changes (`git commit -m 'Add some amazing feature'`)
4. Push to the branch (`git push origin feature/amazing-feature`)
5. Open a Pull Request
## License
This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.
## Support
- π [Issue Tracker](https://github.com/DreamtreeTech/korm-js/issues)
- π¬ [Discussions](https://github.com/DreamtreeTech/korm-js/discussions)
- π¦ [npm package](https://www.npmjs.com/package/@dreamtree-org/korm-js)
- π [Dreamtree Global](http://dreamtreeglobal.com/)
- π§ [preetham.krishna.dev@gmail.com](mailto:preetham.krishna.dev@gmail.com)
---
**Made with β€οΈ by [Partha Preetham Krishna](https://www.linkedin.com/in/partha-preetham-krishna-68ba00197/)** under [Dreamtree Global](http://dreamtreeglobal.com/)