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.


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

Installation

``bash
npm install @dreamtree-org/korm-js
`

Quick Start with Express.js and MySQL

$3

`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');
});
`

$3

`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

`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');
});
`

Complete CRUD Operations Guide

$3

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

$3

`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

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

$3

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

$3

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

$3

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

$3

`javascript
// Replace record (MySQL specific - replaces entire row)
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
}
`

$3

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

$3

`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

$3

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

$3

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

$3

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

$3

| 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 | | "price": "<500" |
| != | Not equal | !value | "status": "!deleted" |
| like | Pattern matching (auto) | %value% | "name": "%john%" |
| in | Value in list | []val1,val2 | "status": "[]active,pending" |
| notIn | Value not in list | ![]val1,val2 | "role": "![]banned,suspended" |
| between | Range (inclusive) | > | "age": "><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
`

$3

`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"]
}
`

$3

`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

$3

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")

$3

#### 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: ">
- NOT BETWEEN: "<>min,max"
- NULL: null
- OR prefix: "Or:column"

$3

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

$3

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 |

$3

- 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

$3

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

$3

`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

$3

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

$3

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

$3

| 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

$3

`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

$3

`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
});
}
});
`

$3

`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

$3

`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
}
]
}
}
`

$3

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 |

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

$3

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

$3

`javascript
const { initializeKORM } = require('@dreamtree-org/korm-js');

const korm = initializeKORM({
db: db, // Knex database instance
dbClient: 'mysql', // 'mysql', 'mysql2', 'pg', 'postgresql', 'sqlite', 'sqlite3'
schema: null, // Optional: initial schema object
resolverPath: null, // Optional: path to models directory (default: process.cwd())
debug: false // Optional: enable SQL debugging (default: false)
});

// 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 (creates/updates tables)
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);
`

$3

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

$3

| 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) |
| replace | Replace record (MySQL) | data |
| upsert | Insert or update | data, conflict |
| sync | Upsert + delete | data, conflict, where |

$3

| 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'
}
`

$3

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

$3

`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');
`

$3

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

$3

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

$3

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
`

$3

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
`

$3

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

$3

`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');
}
`

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.

$3

`javascript
const { initializeKORM } = require('@dreamtree-org/korm-js');

const korm = initializeKORM({
db: db,
dbClient: 'mysql',
debug: true // Enable SQL debugging
});
`

$3

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

$3

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

$3

`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

$3

`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
});
`

$3

`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'
});
`

$3

`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'
});
`

Error Handling

`javascript
// Global error handler
app.use((error, req, res, next) => {
console.error('KORM Error:', error);

res.status(error.status || 500).json({
success: false,
message: 'Internal server error',
error: process.env.NODE_ENV === 'development' ? error.message : 'Something went wrong',
stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
});
});

// Route-specific error handling
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) {
// Handle validation errors
if (error.name === 'ValidationError') {
return res.status(400).json({
success: false,
message: 'Validation failed',
errors: error.message
});
}

// Handle not found errors
if (error.message.includes('not found')) {
return res.status(404).json({
success: false,
message: error.message
});
}

// Handle other errors
res.status(400).json({
success: false,
message: error.message
});
}
});
`

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 file for details.

Support

If you have any questions or need help, please open an issue on GitHub or contact us at partha.preetham.krishna@gmail.com.

---

Made with ❤️ by Partha Preetham Krishna