litedbmodel

litedbmodel

📖 API Documentation

litedbmodel is a lightweight, SQL-friendly TypeScript ORM for PostgreSQL, MySQL, and SQLite. It is designed for production systems where you care about predictable SQL, explicit performance control, and operational safety (replication lag, N+1, accidental full scans).

Philosophy

SQL is not the enemy — opacity is.

Most ORMs hide SQL behind abstractions that are hard to debug and hard to tune. litedbmodel keeps SQL visible and controllable: generated queries are intentionally simple, and complex cases use real SQL via query() / execute().

Make performance the default, not a post-mortem

• Lazy relations are supported, but N+1 is prevented automatically via batch loading.
• Per-parent limiting is done at the SQL level (efficient “top-N per group” patterns).
• Write operations default to no RETURNING for throughput; request PKs via PkeyResult only when needed.

Production safety over convenience magic

• Supports reader/writer routing for read replicas and replication-lag-aware reads.
• Write operations (create/createMany, update/updateMany, delete) require an explicit transaction boundary.
• Configurable hard limits detect accidental over-fetching early.

Refactoring-friendly, without sacrificing SQL control

• Column references are symbol-based (Model.column) so IDE rename/find-references work.
• Conditions are type-safe tuples ([Column, value]), with a sql tagged template for operators ([sql\${Col} > ?`, value]`). An ESLint plugin catches mistakes TS cannot.

See Design Philosophy for detailed comparison with query-centric ORMs.

Key Features

SQL Control & Modeling

• Predictable generated SQL (readable, hand-written style)
• Raw SQL escape hatch: Model.query() / DBModel.execute()
• Query-based models for complex reads (aggregations, JOINs, CTEs)

Performance by Default

• Transparent N+1 prevention (automatic batch loading for lazy relations)
• SQL-level per-parent limit for relations
• Subqueries: IN/EXISTS with correlated conditions

Operational Readiness

• Reader/Writer separation with sticky-writer reads after transactions (replication-lag-aware)
• Transactions with retry options (e.g., deadlock retry)
• Safety guards: findHardLimit / hasManyHardLimit

Developer Experience

• Symbol-based columns + tuple conditions for refactoring safety
• Declarative SKIP pattern for optional fields/conditions
• Middleware for cross-cutting concerns (logging, auth, tenant isolation)
• Multi-database support (portable tuple API; raw SQL is dialect-dependent)

When litedbmodel is a good fit

Choose litedbmodel if you:

• Build large or high-throughput services where SQL tuning and explain plans matter
• Want ORM ergonomics, but refuse to lose the ability to write/own SQL
• Operate with read replicas and care about replication lag and routing rules
• Need safe defaults against “oops, loaded 10M rows” and N+1 regressions
• Prefer a model-centric approach (list/detail + relations) with predictable behavior

When it may NOT be a good fit

litedbmodel may be a poor fit if you:

• Want a “fully abstracted” ORM that hides SQL entirely
• Prefer a query-builder DSL as the primary interface (rather than SQL/tuple conditions)
• Need database-agnostic portability for complex raw SQL (dialect differences are real)

Non-goals

Non-goals are deliberate trade-offs to keep SQL predictable and operations safe. litedbmodel is intentionally not trying to be a “do-everything” ORM.

• 100% database-agnostic SQL: complex queries are expected to use real SQL, and SQL dialect differences are real.
• Migrations as a built-in feature: schema migrations are out of scope (use your preferred migration tool).
• Hiding SQL behind a large abstraction layer: we prioritize predictable SQL over a fully abstracted API.
• Automatic eager-loading everywhere: relations are lazy by default; performance characteristics should stay explicit and controllable.

Installation

npm install litedbmodel reflect-metadata

# Plus your database driver:
npm install pg            # PostgreSQL
npm install mysql2        # MySQL
npm install better-sqlite3  # SQLite

Code Generation (optional)

litedbmodel-gen generates model column definitions from SQL DDL (schema.sql). Column definitions inside markers are auto-updated when the schema changes; hand-written code (relations, methods, exports) is preserved.

npm install -D litedbmodel-gen embedoc
npx embedoc init && npx litedbmodel-gen init
npx embedoc generate --datasource schema   # scaffold model files
npx embedoc build                          # sync column definitions

Quick Start

import 'reflect-metadata';
import { DBModel, model, column } from 'litedbmodel';

// 1. Define model
@model('users')
class UserModel extends DBModel {
  @column() id?: number;
  @column() name?: string;
  @column() email?: string;
  @column() is_active?: boolean;
}
export const User = UserModel.asModel();  // Adds type-safe column references

// 2. Configure database
DBModel.setConfig({
  host: 'localhost',
  database: 'mydb',
  user: 'user',
  password: 'pass',
  // driver: 'mysql',    // for MySQL
  // driver: 'sqlite',   // for SQLite (use database: './data.db')
});

// 3. CRUD operations
await User.create([
  [User.name, 'John'],
  [User.email, '[e-mail]'],
]);
await User.update([[User.id, 1]], [[User.name, 'Jane']]);
await User.delete([[User.is_active, false]]);

// Read operations
const users = await User.find([[User.is_active, true]]);
const john = await User.findOne([[User.email, '[e-mail]']]);

// With returning: true → get PkeyResult for re-fetching
const created = await User.create([...], { returning: true });
const [newUser] = await User.findById(created);

Model Options

The @model decorator accepts optional configuration for default behaviors:

@model('entries', {
  order: () => Entry.created_at.desc(),      // DEFAULT_ORDER
  filter: () => [[Entry.is_deleted, false]], // FIND_FILTER (auto-applied)
  select: 'id, title, created_at',           // SELECT_COLUMN
  updateTable: 'entries_writable',           // UPDATE_TABLE_NAME (for views)
})
class EntryModel extends DBModel {
  @column() id?: number;
  @column() title?: string;
  @column() created_at?: Date;
  @column.boolean() is_deleted?: boolean;
}
export const Entry = EntryModel.asModel();

Option	Type	Description
order	() => OrderSpec	Default ORDER BY for find()
filter	() => Conds	Auto-applied WHERE conditions
select	string	Default SELECT columns
updateTable	string	Table name for INSERT/UPDATE
group	() => Column | string	Default GROUP BY

Note: Options using model columns (order, filter, group) require lazy evaluation () => because the model isn't fully defined when the decorator runs.

Column Decorators

Auto-Inferred Types

Types are inferred from TypeScript property types:

@column() id?: number;           // Number conversion
@column() name?: string;         // No conversion
@column() is_active?: boolean;   // Boolean conversion
@column() created_at?: Date;     // DateTime conversion
@column() large_id?: bigint;     // BigInt conversion

Column Options

@column('db_column_name') prop?: string;         // Custom column name (string shorthand)
@column({ columnName: 'db_col' }) prop?: string; // Custom column name (object form)
@column({ primaryKey: true }) id?: number;       // Mark as primary key
@column({ primaryKey: true, columnName: 'user_id' }) id?: number; // Both options

Option	Type	Description
columnName	string	Database column name (defaults to property name)
primaryKey	boolean	Mark as part of primary key (for getPkey())

Explicit Types (for arrays/JSON/UUID)

Use explicit type decorators when auto-inference isn't sufficient:

@column.date() birth_date?: string;         // Date only (YYYY-MM-DD string)
@column.datetime() updated_at?: Date;       // DateTime with timezone
@column.boolean() is_active?: boolean;      // Explicit boolean
@column.number() amount?: number;           // Explicit number
@column.uuid() id?: string;                 // UUID with auto-casting (PostgreSQL)
@column.stringArray() tags?: string[];      // String array
@column.intArray() scores?: number[];       // Integer array
@column.numericArray() prices?: number[];   // Numeric/decimal array
@column.booleanArray() flags?: boolean[];   // Boolean array
@column.datetimeArray() dates?: Date[];     // DateTime array
@column.json<Settings>() settings?: Settings; // JSON with type

Date vs DateTime: Important Distinction

Databases distinguish between date-only and datetime columns. @column.date() returns a string (YYYY-MM-DD), not a Date object, because a calendar date has no timezone concept:

DB Column Type	Decorator	TypeScript Type	Example
TIMESTAMP / DATETIME	@column() or @column.datetime()	Date	created_at, updated_at
DATE	@column.date()	string	birth_date, settlement_date

⚠️ Important: @column() with Date type is treated as TIMESTAMP/DATETIME. For date-only columns (no time component), you must use @column.date() explicitly.

// ✅ Correct usage
@column() created_at?: Date;              // TIMESTAMP column (datetime → Date)
@column.datetime() updated_at?: Date;     // TIMESTAMP column (datetime → Date)
@column.date() settlement_date?: string;  // DATE column (date only → 'YYYY-MM-DD')

// ❌ Wrong: will cause type mismatch errors
@column() settlement_date?: Date;         // Treated as TIMESTAMP, not DATE!

Serialization behavior (PostgreSQL):

• @column() / @column.datetime() → ISO 8601 UTC: 2024-06-15T10:30:00.000Z
• @column.date() → date string: 2024-06-15

Date Utility Functions

formatLocalDate and formatUTCDate convert a Date object to a YYYY-MM-DD string. Use these when constructing date values for queries or conditions:

import { formatLocalDate, formatUTCDate } from 'litedbmodel';

// Format using local timezone (matches @column.date() behavior)
formatLocalDate(new Date());  // '2024-06-15' (in local timezone)

// Format using UTC
formatUTCDate(new Date());    // '2024-06-15' (in UTC)

// Typical use: query with today's local date
const today = formatLocalDate(new Date());
const meals = await Meal.find([Meal.date, today]);

CRUD Operations

PkeyResult Type

Write operations can optionally return a PkeyResult object:

interface PkeyResult {
  key: Column[];       // Key column(s) used to identify rows
  values: unknown[][]; // 2D array of key values
}

// Single PK example
{ key: [User.id], values: [[1], [2], [3]] }

// Composite PK example
{ key: [TenantUser.tenant_id, TenantUser.id], values: [[1, 100], [1, 101]] }

Default behavior: returning: false — returns null for better performance.
With returning: true: Returns PkeyResult with affected primary keys.

Note: PkeyResult.key always contains primary key column(s), regardless of keyColumns used in updateMany.

create / createMany

// Default: returns null (no RETURNING)
await User.create([
  [User.name, 'John'],
  [User.email, '[e-mail]'],
]);

// With returning: true → returns PkeyResult
const result = await User.create([
  [User.name, 'John'],
  [User.email, '[e-mail]'],
], { returning: true });
// result: { key: [User.id], values: [[1]] }

// Multiple records
const result = await User.createMany([
  [[User.name, 'John'], [User.email, '[e-mail]']],
  [[User.name, 'Jane'], [User.email, '[e-mail]']],
], { returning: true });
// result: { key: [User.id], values: [[1], [2]] }

// Fetch created records if needed
const [user] = await User.findById(result);

update / updateMany

// Default: returns null (no RETURNING)
await User.update(
  [[User.status, 'pending']],        // conditions
  [[User.status, 'active']],         // values
);

// With returning: true → returns PkeyResult
const result = await User.update(
  [[User.status, 'pending']],
  [[User.status, 'active']],
  { returning: true }
);
// result: { key: [User.id], values: [[1], [2], [3]] }

// Bulk update with different values per row
const result = await User.updateMany([
  [[User.id, 1], [User.name, 'John'], [User.email, '[e-mail]']],
  [[User.id, 2], [User.name, 'Jane'], [User.email, '[e-mail]']],
], { keyColumns: [User.id], returning: true });
// result: { key: [User.id], values: [[1], [2]] }

// Fetch updated records if needed
const users = await User.findById(result);

Generated SQL for updateMany:

Database	SQL
PostgreSQL	UPDATE ... FROM UNNEST($1::int[], $2::text[], ...) AS v(...) WHERE t.id = v.id
MySQL 8.0.19+	UPDATE ... JOIN (VALUES ROW(?, ?, ?), ...) AS v(...) ON ... SET ...
SQLite 3.33+	WITH v(...) AS (VALUES (...), ...) UPDATE ... FROM v WHERE ...

delete

// Default: returns null (no RETURNING)
await User.delete([[User.is_active, false]]);

// With returning: true → returns PkeyResult
const result = await User.delete([[User.is_active, false]], { returning: true });
// result: { key: [User.id], values: [[4], [5]] }

findById

Fetch records by primary key. Accepts PkeyResult format for efficient batch loading:

// Single record
const [user] = await User.findById({ values: [[1]] });

// Multiple records
const users = await User.findById({ values: [[1], [2], [3]] });

// Composite PK
const [entry] = await TenantUser.findById({
  values: [[1, 100]]  // [tenant_id, id]
});

// Use with update/delete result
const result = await User.update(...);
const users = await User.findById(result);

Generated SQL:

Database	Single PK	Composite PK
PostgreSQL	WHERE id = ANY($1::int[])	WHERE (col1, col2) IN (SELECT * FROM UNNEST(...))
MySQL	WHERE id IN (?, ?, ?)	JOIN (VALUES ROW(...), ...) AS v ON ...
SQLite	WHERE id IN (?, ?, ?)	WITH v AS (VALUES ...) ... JOIN v ON ...

Upsert (ON CONFLICT)

// Insert or ignore
await User.create(
  [[User.name, 'John'], [User.email, '[e-mail]']],
  { onConflict: User.email, onConflictIgnore: true }
);

// Insert or update
await User.create(
  [[User.name, 'John'], [User.email, '[e-mail]']],
  { onConflict: User.email, onConflictUpdate: [User.name] }
);

// Composite unique key
await UserPref.create(
  [[UserPref.user_id, 1], [UserPref.key, 'theme'], [UserPref.value, 'dark']],
  { onConflict: [UserPref.user_id, UserPref.key], onConflictUpdate: [UserPref.value] }
);

Behavior Notes

PkeyResult Semantics

Aspect	Behavior
Order	Matches database RETURNING order (not guaranteed across DBs; findById(result) order is also unspecified)
update result	Contains PKs of matched rows (rows matching WHERE condition, regardless of whether values actually changed)
delete result	Contains PKs of deleted rows
Duplicates	No duplicates (each row appears once; MySQL pre-SELECT uses DISTINCT)
Empty result	{ key: [...], values: [] } when no rows affected (not null)

Note: For MySQL (no RETURNING), when returning: true:

• update/delete: Executes pre-SELECT (with DISTINCT) to get PKs, then executes the operation (2 queries in same transaction)
• updateMany: Executes update, then SELECT to get PKs of affected rows (2 queries in same transaction)
• When returning: false (default): Single query, returns null

Batch Limits

createMany and updateMany do not auto-split large batches. Users are responsible for chunking:

// Recommended: chunk large batches (DB-dependent limits)
const BATCH_SIZE = 1000;  // Adjust based on your DB and row size
for (let i = 0; i < rows.length; i += BATCH_SIZE) {
  const chunk = rows.slice(i, i + BATCH_SIZE);
  await User.updateMany(chunk, { keyColumns: [User.id] });
}

Database	Practical Limits
PostgreSQL	~32,767 parameters per query
MySQL	max_allowed_packet (default 64MB), ~65,535 placeholders
SQLite	999 variables (compile-time SQLITE_MAX_VARIABLE_NUMBER)

updateMany keyColumns Contract

Requirement	Description
Must be unique	keyColumns must uniquely identify rows (primary key or unique constraint)
Must exist in rows	Every row must include all keyColumns
Non-key columns	Columns not in keyColumns become SET clause values

// ✅ Valid: keyColumns is primary key
await User.updateMany([
  [[User.id, 1], [User.name, 'John']],
], { keyColumns: [User.id] });

// ✅ Valid: keyColumns is unique constraint
await User.updateMany([
  [[User.email, '[e-mail]'], [User.name, 'John']],
], { keyColumns: [User.email] });  // If email has UNIQUE constraint

// ❌ Invalid: keyColumns missing from row
await User.updateMany([
  [[User.name, 'John']],  // Missing User.id!
], { keyColumns: [User.id] });

Type-Safe Conditions

Column Tuples (recommended for equality/simple conditions)

The simplest and most type-safe form. The value type is validated at compile time:

await User.find([
  [User.status, 'active'],           // status = 'active'
  [User.is_active, true],            // is_active = TRUE
]);
await User.find([[User.email, '[e-mail]']]);

sql Tagged Template (for operators, LIKE, BETWEEN, IS NULL, etc.)

Use the sql tagged template for conditions that need operators. The sql tag extracts the Column reference, producing a type-safe fragment. See sql Tagged Template Literal for full reference.

import { sql } from 'litedbmodel';

await User.find([
  [sql`${User.age} > ?`, 18],                          // age > 18
  [sql`${User.age} BETWEEN ? AND ?`, [18, 65]],        // age BETWEEN 18 AND 65
  [sql`${User.name} LIKE ?`, '%test%'],                 // name LIKE '%test%'
  [sql`${User.status} IN (?)`, ['active', 'pending']],  // status IN ('active', 'pending')
  sql`${User.deleted_at} IS NULL`,                       // deleted_at IS NULL (no value needed)
]);

Values can also be embedded directly in the template. The sql tag automatically extracts them as parameterized values:

await User.find([
  sql`${User.age} > ${18}`,
  sql`${User.name} LIKE ${'%test%'}`,
  sql`${User.deleted_at} IS NULL`,
]);

OR Conditions and ORDER BY

await User.find([
  [User.is_active, true],
  User.or(
    [[User.role, 'admin']],
    [[User.role, 'moderator']],
  ),
], { order: User.created_at.desc() });

ESLint Plugin: Use litedbmodel/eslint-plugin to catch mistakes that TypeScript cannot:

• Wrong model columns (e.g., User.find([[Post.id, 1]]))
• Hardcoded column names instead of ${Model.column}
• Missing declare keyword for relation properties

Subquery Conditions

IN/NOT IN and EXISTS/NOT EXISTS subqueries with composite key support. Key pairs use the same format as relation decorators: [[parentCol, targetCol], ...]

import { parentRef } from 'litedbmodel';

// IN subquery - key pairs: [[parentCol, targetCol]]
await User.find([
  User.inSubquery([[User.id, Order.user_id]], [
    [Order.status, 'paid']
  ])
]);
// → WHERE users.id IN (SELECT orders.user_id FROM orders WHERE orders.status = 'paid')

// Composite key IN subquery
await User.find([
  User.inSubquery([
    [User.id, Order.user_id],
    [User.group_id, Order.group_id],
  ], [[Order.status, 'active']])
]);
// → WHERE (users.id, users.group_id) IN (SELECT orders.user_id, orders.group_id FROM orders WHERE orders.status = 'active')

// NOT IN subquery
await User.find([
  User.notInSubquery([[User.id, BannedUser.user_id]])
]);
// → WHERE users.id NOT IN (SELECT banned_users.user_id FROM banned_users)

// Correlated subquery with parentRef
await User.find([
  User.inSubquery([[User.id, Order.user_id]], [
    [Order.tenant_id, parentRef(User.tenant_id)],
    [Order.status, 'completed']
  ])
]);
// → WHERE users.id IN (SELECT orders.user_id FROM orders WHERE orders.tenant_id = users.tenant_id AND orders.status = 'completed')

// EXISTS subquery (conditions determine target table)
await User.find([
  [User.is_active, true],
  User.exists([
    [Order.user_id, parentRef(User.id)]
  ])
]);
// → WHERE is_active = TRUE AND EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)

// NOT EXISTS subquery
await User.find([
  User.notExists([
    [Complaint.user_id, parentRef(User.id)]
  ])
]);
// → WHERE NOT EXISTS (SELECT 1 FROM complaints WHERE complaints.user_id = users.id)

Declarative SKIP Pattern

Conditional fields without if-statements:

import { SKIP } from 'litedbmodel';

// ❌ Imperative
const updates = [];
if (body.name !== undefined) updates.push([User.name, body.name]);
if (body.email !== undefined) updates.push([User.email, body.email]);
await User.update([[User.id, id]], updates);

// ✅ Declarative with SKIP
await User.update([[User.id, id]], [
  [User.name, body.name ?? SKIP],
  [User.email, body.email ?? SKIP],
  [User.updated_at, new Date()],
]);

Works for conditions too:

await User.find([
  [User.deleted, false],
  query.name ? [sql`${User.name} LIKE ?`, `%${query.name}%`] : SKIP,
  [User.status, query.status ?? SKIP],
]);

SKIP Behavior by Operation:

Operation	SKIP Behavior
find / findOne / count	Condition excluded from WHERE
create / update	Column excluded from INSERT/UPDATE
createMany	Column excluded → DB DEFAULT applied
updateMany	Column excluded → existing value retained

// createMany - SKIPped columns get DEFAULT value
await User.createMany([
  [[User.name, 'John'], [User.email, '[e-mail]']],
  [[User.name, 'Jane'], [User.email, SKIP]],  // email = DEFAULT
]);

// updateMany - SKIPped columns unchanged
await User.updateMany([
  [[User.id, 1], [User.email, '[e-mail]'], [User.status, SKIP]],  // status unchanged
  [[User.id, 2], [User.email, SKIP], [User.status, 'active']],       // email unchanged
], { keyColumns: User.id });

Batch SQL Strategy by Database:

Database	createMany	updateMany
PostgreSQL	Grouped UNNEST INSERT per SKIP pattern	UNNEST + CASE WHEN skip_flag
MySQL	Grouped VALUES ROW INSERT per SKIP pattern	JOIN VALUES ROW + IF(skip_flag)
SQLite	Grouped VALUES INSERT per SKIP pattern	CASE WHEN key=? THEN col ELSE ?

Records with the same SKIP pattern are batched together for efficient INSERT. Each database uses native batch syntax while ensuring SKIPped columns receive DEFAULT values (createMany) or retain existing values (updateMany).

Note on createMany with SKIP: createMany groups records by their SKIP pattern and issues a separate INSERT per group, because SQL does not allow mixing DEFAULT with array-based bulk insert (UNNEST). updateMany does not have this limitation — it handles SKIP in a single query using boolean flags. For best performance with createMany, prefer providing explicit values for all columns instead of using SKIP.

Relation Decorators

Define relations declaratively with type-safe decorators:

import { DBModel, model, column, hasMany, belongsTo, hasOne } from 'litedbmodel';

@model('users')
class UserModel extends DBModel {
  @column() id?: number;
  @column() name?: string;

  // Use 'declare' for relation properties (not '!' assertion)
  // This prevents TypeScript from creating instance properties that shadow the getter
  @hasMany(() => [User.id, Post.author_id])
  declare posts: Promise<Post[]>;

  @hasOne(() => [User.id, UserProfile.user_id])
  declare profile: Promise<UserProfile | null>;
}
export const User = UserModel.asModel();

@model('posts')
class PostModel extends DBModel {
  @column() id?: number;
  @column() author_id?: number;
  @column() title?: string;

  @belongsTo(() => [Post.author_id, User.id])
  declare author: Promise<User | null>;

  @hasMany(() => [Post.id, Comment.post_id])
  declare comments: Promise<Comment[]>;
}
export const Post = PostModel.asModel();

// Usage
const post = await Post.findOne([[Post.id, 1]]);
const author = await post.author;       // Lazy loaded
const comments = await post.comments;   // Lazy loaded

Important: Use declare (not !) for relation properties. TypeScript class field declarations with ! create instance properties that shadow the prototype getter. The ESLint plugin detects this mistake.

With Options (order, where, limit)

@hasMany(() => [User.id, Post.author_id], {
  order: () => Post.created_at.desc(),
  where: () => [[Post.is_deleted, false]],
})
declare activePosts: Promise<Post[]>;

// Per-parent limit - fetch only N records per parent key
@hasMany(() => [User.id, Post.author_id], {
  limit: 5,
  order: () => Post.created_at.desc(),
})
declare recentPosts: Promise<Post[]>;  // Each user gets their 5 most recent posts

The limit option applies SQL-level limiting per parent key during batch loading:

• PostgreSQL: Uses LATERAL JOIN for efficient per-group limiting
• MySQL/SQLite: Uses ROW_NUMBER() OVER (PARTITION BY ...) window function

This is more efficient than fetching all records and filtering in application code.

Important: Always use order with limit. Without ordering, the "which N records" is non-deterministic and may vary between queries.

Composite Key Relations

@model('tenant_posts')
class TenantPostModel extends DBModel {
  @column({ primaryKey: true }) tenant_id?: number;
  @column({ primaryKey: true }) id?: number;
  @column() author_id?: number;

  @belongsTo(() => [
    [TenantPost.tenant_id, TenantUser.tenant_id],
    [TenantPost.author_id, TenantUser.id],
  ])
  declare author: Promise<TenantUser | null>;
}

Transparent N+1 Prevention

When find() returns multiple records, batch loading is automatic — no eager loading specification needed:

const users = await User.find([]);  // Auto batch context created

for (const user of users) {
  const posts = await user.posts;   // First access batch loads ALL users' posts
}
// Total: 2 queries instead of N+1!

Write natural code (await user.posts); litedbmodel handles the optimization.

Query Limits (Safety Guards)

Prevent accidental loading of too many records with configurable hardLimits:

// Global configuration
DBModel.setConfig(config, {
  findHardLimit: 1000,       // find() throws if > 1000 records
  hasManyHardLimit: 10000,   // hasMany throws if > 10000 records total (batch)
});

// Or update later
DBModel.setLimitConfig({ findHardLimit: 500, hasManyHardLimit: 5000 });

When limits are exceeded, LimitExceededError is thrown:

import { LimitExceededError } from 'litedbmodel';

try {
  const users = await User.find([]);  // May throw if too many records
} catch (e) {
  if (e instanceof LimitExceededError) {
    console.log(`Limit ${e.limit} exceeded: got ${e.actualCount} records`);
  }
}

Per-relation hardLimit override:

You can override the global hasManyHardLimit for specific relations:

@hasMany(() => [User.id, Post.author_id], {
  hardLimit: 500,   // Override global hasManyHardLimit for this relation
})
declare posts: Promise<Post[]>;

@hasMany(() => [User.id, Log.user_id], {
  hardLimit: null,  // Disable limit check for this relation
})
declare logs: Promise<Log[]>;

Note: findHardLimit and hasManyHardLimit are safety guards implemented as LIMIT N+1 at SQL level. If the result exceeds the limit, it throws immediately — this minimizes data transfer while detecting overflow. For explicit SQL-level limiting (e.g., "N records per parent"), use the limit option described in With Options.

Transactions

// Basic
await DBModel.transaction(async () => {
  const user = await User.findOne([[User.id, 1]]);
  await Account.update([[Account.user_id, user.id]], [[Account.balance, 100]]);
});

// With return value
const user = await DBModel.transaction(async () => {
  return await User.create([[User.name, 'Alice']]);
});

// Auto-retry on deadlock
await DBModel.transaction(
  async () => { /* ... */ },
  { retryOnError: true, retryLimit: 3 }
);

// Preview mode (rollback after execution)
await DBModel.transaction(
  async () => { /* ... */ },
  { rollbackOnly: true }
);

Middleware

Class-based middleware for cross-cutting concerns.

Call Flow

All database operations flow through the middleware system:

flowchart TD
    subgraph "High-Level API"
        find["find()"]
        findOne["findOne()"]
        findById["findById()"]
        count["count()"]
        create["create()"]
        createMany["createMany()"]
        update["update()"]
        updateMany["updateMany()"]
        delete["delete()"]
    end
    
    subgraph RelationAPI["Relation API"]
        belongsTo["@belongsTo"]
        hasMany["@hasMany"]
        hasOne["@hasOne"]
    end
    
    subgraph "Middle-Level API"
        query["query()"]
    end
    
    subgraph "Low-Level API"
        execute["execute()"]
    end
    
    subgraph "Database"
        DB[(DB)]
    end
    
    find --> query
    findOne --> query
    findById --> execute
    
    belongsTo --> query
    hasMany --> query
    hasOne --> query
    
    count --> execute
    create --> execute
    createMany --> execute
    update --> execute
    updateMany --> execute
    delete --> execute
    
    query --> execute
    execute --> DB
    
    style RelationAPI fill:#e0e0e0,stroke:#999

Middleware hooks:

• Method-level: find, findOne, findById, count, create, createMany, update, updateMany, delete
• Instantiation-level: query — returns model instances from raw SQL
• SQL-level: execute — intercepts ALL SQL queries (SELECT, INSERT, UPDATE, DELETE)

Note: Relation API (@belongsTo, @hasMany, @hasOne) bypasses method-level middleware hooks and calls query() directly. To intercept relation queries, use Instantiation-level (query) middleware.

Example

// Simple logger (no state needed)
const LoggerMiddleware = DBModel.createMiddleware({
  execute: async function(next, sql, params) {
    console.log('SQL:', sql);
    return next(sql, params);
  }
});

// With per-request state (fully type-safe)
const TenantMiddleware = DBModel.createMiddleware({
  // Initial state for each request (deep-cloned per request)
  state: { tenantId: 0, queryCount: 0 },
  
  // Hook signature: (model, next, ...args) for method-level hooks
  find: async function(model, next, conditions, options) {
    // `this` is typed as { tenantId: number; queryCount: number }
    this.queryCount++;
    const tenantCol = (model as { tenant_id?: Column }).tenant_id;
    if (tenantCol) {
      conditions = [[tenantCol, this.tenantId], ...conditions];
    }
    return next(conditions, options);
  }
});

// Register
DBModel.use(LoggerMiddleware);
DBModel.use(TenantMiddleware);

// Per-request usage (type-safe)
TenantMiddleware.getCurrentContext().tenantId = req.user.tenantId;
console.log(TenantMiddleware.getCurrentContext().queryCount);

State lifecycle: Each HTTP request gets its own copy of the state object via AsyncLocalStorage. States are isolated between concurrent requests and automatically cleaned up when the request ends.

Advanced Features

Raw SQL Methods

When find() isn't enough, use real SQL directly. No query builder translation needed.

Portability note: Tuple API (find(), create(), update()) and relation loading are DB-portable (config-only switching). Raw SQL via query() is your escape hatch for DB-specific optimizations—you control the dialect (placeholders, functions, type casts).

Model.query() — SQL with Type-Safe Results

Execute any SQL and get typed model instances. The SQL you write is exactly what runs.

// Complex JOIN with subquery - returns User[] with full type safety
const activeUsers = await User.query(`
  SELECT u.* 
  FROM users u
  INNER JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at >= $1
    GROUP BY user_id
    HAVING COUNT(*) >= $2
  ) active ON u.id = active.user_id
  WHERE u.status = 'active'
  ORDER BY active.order_count DESC
`, [lastMonth, minOrders]);

// Window functions, CTEs, recursive queries - anything PostgreSQL supports
@model('user_rankings')
class UserRankingModel extends DBModel {
  @column() user_id?: number;
  @column() score?: number;
  @column() rank?: number;
  @column() percentile?: number;
}
const UserRanking = UserRankingModel.asModel();

const rankings = await UserRanking.query(`
  WITH ranked AS (
    SELECT 
      user_id,
      score,
      RANK() OVER (PARTITION BY category ORDER BY score DESC) as rank,
      PERCENT_RANK() OVER (PARTITION BY category ORDER BY score) as percentile
    FROM user_scores
    WHERE created_at >= $1
  )
  SELECT * FROM ranked WHERE rank <= 100
`, [startDate]);
// rankings: UserRanking[] - full IDE autocomplete, type checking

DBModel.execute() - Non-Model Operations

Use execute() for DDL, maintenance, and operations that don't return model instances. Accepts either raw SQL strings or sql tagged templates:

import { sql } from 'litedbmodel';

// With sql tag — parameters are co-located
await DBModel.execute(sql`SELECT process_daily_aggregates(${targetDate})`);
await DBModel.execute(sql`SELECT pg_notify(${'events'}, ${JSON.stringify(payload)})`);

// Raw SQL strings for DDL and maintenance
await DBModel.execute('REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary');
await DBModel.execute('VACUUM ANALYZE orders');
await DBModel.execute('CREATE INDEX CONCURRENTLY idx_orders_date ON orders(created_at)');

When to Use Each Method

Method	Use Case	Returns
Model.find()	Simple queries with conditions	Model[]
Model.query()	Complex SQL returning model data	Model[]
DBModel.execute()	DDL, maintenance, procedures	{ rows, rowCount }
Query-Based Models	Reusable complex queries	Model[] via find()

Query-Based Models

Define models backed by complex SQL queries instead of simple tables. Use find(), findOne(), count() on JOINs, aggregations, CTEs, and analytics queries.

Basic Concept

import { DBModel, model, column } from 'litedbmodel';

@model('user_stats')  // Alias for the CTE
class UserStatsModel extends DBModel {
  @column() id?: number;
  @column() name?: string;
  @column() post_count?: number;
  @column() comment_count?: number;
  @column() last_activity?: Date;

  // Define the base query
  static QUERY = `
    SELECT 
      u.id,
      u.name,
      COUNT(DISTINCT p.id) AS post_count,
      COUNT(DISTINCT c.id) AS comment_count,
      GREATEST(MAX(p.created_at), MAX(c.created_at)) AS last_activity
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    LEFT JOIN comments c ON u.id = c.user_id
    WHERE u.deleted_at IS NULL
    GROUP BY u.id, u.name
  `;
}
export const UserStats = UserStatsModel.asModel();

// Use find() with additional conditions
const topContributors = await UserStats.find([
  [sql`${UserStats.post_count} >= ?`, 10],
  [sql`${UserStats.last_activity} > ?`, lastWeek],
], { order: UserStats.post_count.desc(), limit: 100 });

Generated SQL (CTE-based)

When find() is called, the QUERY becomes a CTE (WITH clause):

WITH user_stats AS (
  SELECT 
    u.id,
    u.name,
    COUNT(DISTINCT p.id) AS post_count,
    COUNT(DISTINCT c.id) AS comment_count,
    GREATEST(MAX(p.created_at), MAX(c.created_at)) AS last_activity
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  LEFT JOIN comments c ON u.id = c.user_id
  WHERE u.deleted_at IS NULL
  GROUP BY u.id, u.name
)
SELECT * FROM user_stats
WHERE post_count >= $1 AND last_activity > $2
ORDER BY post_count DESC
LIMIT 100

Parameterized Queries with sql Tag

For queries that need runtime parameters, use the sql tagged template with withQuery(). Parameters are co-located with SQL — no manual $1/$2 numbering or DB dialect differences:

import { sql } from 'litedbmodel';

@model('sales_report')
class SalesReportModel extends DBModel {
  @column() product_id?: number;
  @column() product_name?: string;
  @column() total_quantity?: number;
  @column() total_revenue?: number;
  @column() order_count?: number;

  static forPeriod(startDate: string, endDate: string) {
    return this.withQuery(sql`
      SELECT 
        p.id AS product_id,
        p.name AS product_name,
        SUM(oi.quantity) AS total_quantity,
        SUM(oi.quantity * oi.unit_price) AS total_revenue,
        COUNT(DISTINCT o.id) AS order_count
      FROM products p
      INNER JOIN order_items oi ON p.id = oi.product_id
      INNER JOIN orders o ON oi.order_id = o.id
      WHERE o.status = 'completed'
        AND o.created_at >= ${startDate} 
        AND o.created_at < ${endDate}
      GROUP BY p.id, p.name
    `);
  }
}
export const SalesReport = SalesReportModel.asModel();

// Usage
const Q1Report = SalesReport.forPeriod('2024-01-01', '2024-04-01');
const topProducts = await Q1Report.find([
  [sql`${SalesReport.total_revenue} > ?`, 10000],
], { order: SalesReport.total_revenue.desc() });

The sql tag automatically converts interpolated values to ? placeholders and extracts them as parameters. The internal dialect conversion (? → $1/$2 for PostgreSQL) is handled transparently.

Type-Safe Column References in QUERY

Use the sql tag with Column references and Model classes for refactoring safety. Columns and table names are embedded directly in SQL; they are not parameterized:

import { sql } from 'litedbmodel';

@model('user_activity')
class UserActivityModel extends DBModel {
  @column() user_id?: number;
  @column() user_name?: string;
  @column() total_posts?: number;

  static QUERY = sql`
    SELECT 
      ${User.id} AS user_id,
      ${User.name} AS user_name,
      COUNT(${Post.id}) AS total_posts
    FROM ${User}
    LEFT JOIN ${Post} ON ${User.id} = ${Post.user_id}
    GROUP BY ${User.id}, ${User.name}
  `;
}

Passing a Model class (${User}) in the sql tag expands to its TABLE_NAME. This is consistent with column references (${User.id} → column name).

Use Cases

Use Case	Example
Aggregations	User stats, sales reports, leaderboards
Analytics	Cohort analysis, funnel metrics, trend data
Denormalized Views	Pre-joined data for read-heavy operations
Time-Series	Period-based summaries with window functions
Recursive Queries	Organizational hierarchies, category trees

Design Considerations

• Read-Only: Query-based models don't support create(), update(), delete()
• CTE vs Subquery: CTE approach produces cleaner, more readable SQL
• Parameter Ordering: QUERY params come first, then find() condition params
• Caching: Consider materializing frequently-used query models as actual views

Reader/Writer Separation

For production deployments with read replicas, litedbmodel supports automatic connection routing.

Configuration

DBModel.setConfig(
  { host: 'reader.db.example.com', database: 'mydb', ... },  // reader (default)
  {
    writerConfig: { host: 'writer.db.example.com', database: 'mydb', ... },
    
    // Keep using writer after transaction (default: true)
    // Avoids stale reads due to replication lag
    useWriterAfterTransaction: true,
    
    // Duration to keep using writer after transaction (ms, default: 5000)
    writerStickyDuration: 5000,
  }
);

Connection Routing Rules

Context	Connection	Write Allowed
Inside transaction()	Writer	✅ Yes
Inside withWriter()	Writer	❌ No (SELECT only)
After transaction (within sticky duration)	Writer	❌ No
Normal query	Reader	❌ No

Important: Write operations (create(), update(), delete()) require a transaction. Attempting to write outside a transaction throws an error.

Transaction Options

// Override global useWriterAfterTransaction per transaction
await DBModel.transaction(
  async () => {
    await User.create([[User.name, 'John']]);
  },
  { 
    useWriterAfterTransaction: false,  // Don't stick to writer after this transaction
  }
);

Explicit Writer Access (SELECT)

Use withWriter() when you need to read from writer to avoid replication lag:

// Read from writer explicitly
const user = await DBModel.withWriter(async () => {
  return await User.findOne([[User.id, 1]]);
});

// Write inside withWriter() throws error - use transaction() instead
await DBModel.withWriter(async () => {
  await User.create([[User.name, 'Error']]);  // → WriteInReadOnlyContextError
});

Multi-Database Support

For applications connecting to multiple databases, use createDBBase() to create isolated base classes.

Setup

import { DBModel, model, column } from 'litedbmodel';

// Foundation database
const BaseDB = DBModel.createDBBase({
  host: 'base-reader.example.com',
  database: 'base_db',
  // ...
}, {
  writerConfig: { host: 'base-writer.example.com', database: 'base_db', ... },
});

// CMS database
const CmsDB = DBModel.createDBBase({
  host: 'cms-reader.example.com',
  database: 'cms_db',
  // ...
}, {
  writerConfig: { host: 'cms-writer.example.com', database: 'cms_db', ... },
});

Model Definition

// Models inherit from their respective database base class
@model('users')
class UserModel extends BaseDB {
  @column() id?: number;
  @column() name?: string;
}
export const User = UserModel.asModel();

@model('articles')
class ArticleModel extends CmsDB {
  @column() id?: number;
  @column() title?: string;
}
export const Article = ArticleModel.asModel();

Independent Transactions

Each database has its own transaction context:

// BaseDB transaction
await BaseDB.transaction(async () => {
  await User.create([[User.name, 'John']]);
});

// CmsDB transaction (independent)
await CmsDB.transaction(async () => {
  await Article.create([[Article.title, 'Hello World']]);
});

// Each DB also has independent withWriter()
const article = await CmsDB.withWriter(async () => {
  return await Article.findOne([[Article.id, 1]]);
});

Scope Isolation

Resource	Scope	Description
Connection Handler	Per DBBase	Each base class has its own connection pool
Transaction Context	Per DBBase	AsyncLocalStorage isolated per base class
Writer Context	Per DBBase	withWriter() isolated per base class
Sticky Timer	Per DBBase	Writer sticky duration tracked separately
Middlewares	Global	Cross-cutting concerns shared across all DBs
Model Registry	Global	For relation resolution across databases

APPENDIX

Comparison

Feature	litedbmodel	Kysely	Drizzle	TypeORM	Prisma
Relation Loading	On-demand	Manual	Eager/upfront	Eager/upfront	Include
Complex Queries	✅ Real SQL	Builder DSL	Builder DSL	HQL/Builder	Prisma DSL
Query-Based Models	✅	❌	❌	Views only	Views only
Model-Centric Relations	✅ On-demand	❌	❌ Eager	❌ Eager	❌ Include
Transparent N+1 Prevention	✅	❌ Manual	⚠️ { with }	Eager only	Include
IDE Refactoring	✅	❌	⚠️ Partial	❌	❌
SKIP Pattern	✅	❌	❌	❌	❌
Extensibility	Middleware	Plugins	❌ Manual	Subscribers	Extensions
Performance	🏆 Fastest (9/19 wins)	Fast	Fast	Medium	Slow

See COMPARISON.md for detailed analysis and BENCHMARK.md for benchmarks.

License

MIT