masterrecord

v1.3.0

Published

2 days ago

An Object-relational mapping for the Master framework. Master Record connects classes to relational database tables to establish a database with almost zero-configuration

0High
0Medium
0Low

tailor

orm database migrations mysql sqlite postgres esm masterrecord

MasterRecord

ESM only. As of v1.0, MasterRecord is a pure ESM package. Requires Node.js 20+ and a host project with "type": "module" in package.json. There is no CommonJS build.

MasterRecord is a lightweight, code-first ORM for Node.js with a fluent query API, comprehensive migrations, and multi-database support. Build type-safe queries with lambda expressions, manage schema changes with CLI-driven migrations, and work seamlessly across MySQL, PostgreSQL, and SQLite.

Key Features

🔹 Multi-Database Support - MySQL, PostgreSQL, SQLite with consistent API 🔹 Code-First Design - Define entities in JavaScript, generate schema automatically 🔹 Fluent Query API - Lambda-based queries with parameterized placeholders 🔹 Active Record Pattern - Entities with .save(), .delete(), .reload() methods 🔹 Entity Serialization - .toObject() and .toJSON() with circular reference protection 🔹 Lifecycle Hooks - beforeSave, afterSave, beforeDelete, afterDelete hooks 🔹 Business Validation - Built-in validators (required, email, length, pattern, custom) 🔹 Bulk Operations - Efficient bulkCreate, bulkUpdate, bulkDelete APIs 🔹 Full-Text Search - Portable .search() API on top of SQLite FTS5, Postgres tsvector, and MySQL FULLTEXT 🔹 Query Result Caching - Production-grade in-memory and Redis caching with automatic invalidation 🔹 Migration System - CLI-driven migrations with rollback support 🔹 SQL Injection Protection - Automatic parameterized queries throughout 🔹 Field Transformers - Custom serialization/deserialization for complex types 🔹 Type Validation - Runtime type checking and coercion 🔹 Relationship Mapping - One-to-many, many-to-one, many-to-many support 🔹 Seed Data - Built-in seeding with idempotent operations

Database Support

| Database | Version | Features | |------------|--------------|---------------------------------------------------| | PostgreSQL | 9.6+ (12+) | JSONB, UUID, async/await, connection pooling | | MySQL | 5.7+ (8.0+) | JSON, async/await, connection pooling, AUTO_INCREMENT | | SQLite | 3.x | Embedded, zero-config, file-based, async API wrapper |

⚠️ Best Practices (CRITICAL)

1. Creating Entity Instances

ALWAYS use context.Entity.new() to create new entity instances:

// ✅ CORRECT - Creates proper data instance with getters/setters
const task = this._qaContext.QaTask.new();
const annotation = this._qaContext.QaAnnotation.new();
const project = this._qaContext.QaProject.new();

task.name = "My Task";
task.status = "active";
await db.saveChanges();  // ✅ Saves correctly

// ❌ WRONG - Creates schema definition object with function properties
const task = new QaTask();  // task.name is a FUNCTION, not a property!

task.name = "My Task";  // ❌ Doesn't work - name is a function
await db.saveChanges();  // ❌ Error: "Type mismatch: Expected string, got function"

Why?

new Entity() creates a schema definition object where properties are methods that define the schema
context.Entity.new() creates a data instance with proper getters/setters for storing values
Using new Entity() causes runtime errors: "Type mismatch for Entity.field: Expected integer, got function with value undefined"

Error Example:

Error: INSERT failed: Type mismatch for QaTask.name: Expected string, got function with value undefined
    at SQLLiteEngine._buildSQLInsertObjectParameterized

This error means: You used new Entity() instead of context.Entity.new()

2. Saving Changes - ALWAYS use `await`

ALWAYS use await when calling saveChanges():

// ✅ CORRECT - Waits for database write to complete
await this._qaContext.saveChanges();

// ❌ WRONG - Returns immediately without waiting for database write
this._qaContext.saveChanges();  // Promise never completes!

Why?

saveChanges() is async and returns a Promise
Without await, code continues before database write completes
Causes data loss - appears successful but nothing saves to database
Results in "phantom saves" - data in memory but not persisted

Symptoms of missing await:

API returns success but data not in database
Queries after save return old/missing data
Intermittent save failures
Race conditions

Repository Pattern - Make Methods Async:

// ✅ CORRECT - Async method with await
async create(entity) {
    this._qaContext.Entity.add(entity);
    await this._qaContext.saveChanges();
    return entity;
}

// ❌ WRONG - Synchronous method calling async saveChanges
create(entity) {
    this._qaContext.Entity.add(entity);
    this._qaContext.saveChanges();  // No await - returns before save completes!
    return entity;  // Returns entity with undefined ID
}

3. Quick Reference Card

// Entity Creation
✅ const user = db.User.new();           // CORRECT
❌ const user = new User();              // WRONG - creates schema object

// Saving Data
✅ await db.saveChanges();               // CORRECT - waits for completion
❌ db.saveChanges();                     // WRONG - fire and forget

// Repository Methods
✅ async create(entity) {                // CORRECT - async method
      await db.saveChanges();
   }
❌ create(entity) {                      // WRONG - sync method
      db.saveChanges();                  // No await!
   }

// Querying (all require await)
✅ const users = await db.User.toList();  // CORRECT
✅ const user = await db.User.findById(1); // CORRECT
❌ const users = db.User.toList();         // WRONG - returns Promise

Installation

# Global installation (recommended for CLI)
npm install -g masterrecord

# Local installation
npm install masterrecord

# With specific database drivers
npm install masterrecord pg           # PostgreSQL
npm install masterrecord mysql2       # MySQL
npm install masterrecord better-sqlite3  # SQLite

Dependencies

MasterRecord includes the following database drivers by default:

pg@^8.17.2 - PostgreSQL (async)
mysql2@^3.11.5 - MySQL (async with connection pooling)
better-sqlite3@^12.6.2 - SQLite (async API wrapper for consistency)

Two Patterns: Entity Framework & Active Record

MasterRecord supports both ORM patterns - choose what feels natural:

Active Record Style (Recommended for beginners)

// Entity saves itself
const user = db.User.findById(1);
user.name = 'Updated';
await user.save();  // ✅ Entity knows how to save

Entity Framework Style (Efficient for batch operations)

// Context saves all tracked entities
const user = db.User.findById(1);
user.name = 'Updated';
await db.saveChanges();  // ✅ Batch save

Quick Start

1. Create a Context

// app/models/AppContext.js
// Name the file after the context class so the `masterrecord` migration CLI can
// resolve it: `masterrecord enable-migrations AppContext` looks for *AppContext.js.
import context from 'masterrecord/context';
import User from './User.js';
import Post from './Post.js';

class AppContext extends context {
    constructor() {
        super();

        // Configure database connection
        this.env({
            type: 'postgres',  // or 'mysql', 'sqlite'
            host: 'localhost',
            port: 5432,
            database: 'myapp',
            user: 'postgres',
            password: 'password'
        });

        // Register entities
        this.dbset(User);
        this.dbset(Post);
    }
}

export default AppContext;

2. Define Entities

// app/models/User.js
// Each field is a METHOD that receives the schema builder (db).
class User {
    id(db) { db.integer().primary().auto(); }
    name(db) { db.string().notNullable(); }
    email(db) { db.string().notNullable().unique(); }
    age(db) { db.integer(); }              // nullable by default
    created_at(db) { db.datetime(); }
}

export default User;

3. Run Migrations

# Enable migrations (one-time setup)
masterrecord enable-migrations AppContext

# Create initial migration
masterrecord add-migration InitialCreate AppContext

# Apply migrations
masterrecord update-database AppContext

4. Query Your Data

import AppContext from './app/models/AppContext.js';
const db = new AppContext();

// Create (Active Record style)
const user = db.User.new();
user.name = 'Alice';
user.email = '[email protected]';
user.age = 28;
await user.save();  // Entity saves itself!

// Read with parameterized query
const alice = db.User
    .where(u => u.email == $$, '[email protected]')
    .single();

// Update (Active Record style)
alice.age = 29;
await alice.save();  // Entity saves itself!

// Delete
db.remove(alice);
await db.saveChanges();

Database Configuration

PostgreSQL (Async)

class AppContext extends context {
    constructor() {
        super();

        this.env({
            type: 'postgres',
            host: 'localhost',
            port: 5432,
            database: 'myapp',
            user: 'postgres',
            password: 'password',
            max: 20,  // Connection pool size
            idleTimeoutMillis: 30000,
            connectionTimeoutMillis: 2000
        });

        this.dbset(User);
    }
}

// Usage requires await
const db = new AppContext();
await db.saveChanges();  // PostgreSQL is async

MySQL (Async with Connection Pooling)

class AppContext extends context {
    constructor() {
        super();

        this.env({
            type: 'mysql',
            host: 'localhost',
            port: 3306,
            database: 'myapp',
            user: 'root',
            password: 'password',
            connectionLimit: 10  // Connection pool size (optional)
        });

        this.dbset(User);
    }
}

// Usage requires await (async like PostgreSQL)
const db = new AppContext();
await db.saveChanges();  // MySQL now uses async/await

SQLite (Async API)

class AppContext extends context {
    constructor() {
        super();

        this.env({
            type: 'sqlite',
            connection: './data/myapp.db'  // File path
        });

        this.dbset(User);
    }
}

// Usage requires await for consistency across databases
const db = new AppContext();
await db.saveChanges();  // SQLite now has async API wrapper

Environment Files

Store configurations in JSON files keyed by the context class name (so one file can hold settings for multiple contexts):

// config/environments/env.development.json
{
    "AppContext": {
        "type": "postgres",
        "host": "localhost",
        "port": 5432,
        "database": "myapp_dev",
        "user": "postgres",
        "password": "dev_password"
    }
}

// Load environment file
class AppContext extends context {
    constructor() {
        super();
        this.env('config/environments');  // Loads env.<NODE_ENV>.json
        this.dbset(User);
    }
}

# Set environment
export NODE_ENV=development
node app.js

Entity Definitions

Basic Entity

Each field is a method on the class that receives the schema builder (db) and chains type + modifiers:

class User {
    // Primary key with auto-increment
    id(db) { db.integer().primary().auto(); }

    // Required string field
    name(db) { db.string().notNullable(); }

    // Optional field with a default value
    status(db) { db.string().default('active'); }

    // Unique constraint
    email(db) { db.string().unique(); }

    // Timestamp (stored as TEXT on SQLite; set it in app code or a beforeSave hook)
    created_at(db) { db.datetime(); }
}

Note: fields are methods, not assigned object literals. MasterRecord reads the class's prototype methods to build the schema; plain this.x = {...} properties in the constructor are ignored.

Field Types

| MasterRecord Type | PostgreSQL | MySQL | SQLite | |-------------------|---------------|---------------|-----------| | integer | INTEGER | INT | INTEGER | | bigint | BIGINT | BIGINT | INTEGER | | string | VARCHAR(255) | VARCHAR(255) | TEXT | | text | TEXT | TEXT | TEXT | | float | REAL | FLOAT | REAL | | decimal | DECIMAL | DECIMAL | REAL | | boolean | BOOLEAN | TINYINT | INTEGER | | date | DATE | DATE | TEXT | | time | TIME | TIME | TEXT | | datetime | TIMESTAMP | DATETIME | TEXT | | timestamp | TIMESTAMP | TIMESTAMP | TEXT | | json | JSON | JSON | TEXT | | jsonb | JSONB | JSON | TEXT | | uuid | UUID | VARCHAR(36) | TEXT | | binary | BYTEA | BLOB | BLOB |

Relationships

Relationships are declared with the hasMany / hasOne / belongsTo builder methods. belongsTo('User') automatically creates the user_id foreign-key column.

class User {
    id(db) { db.integer().primary().auto(); }
    name(db) { db.string(); }

    // One-to-many: User has many Posts (foreign key inferred as user_id on Post)
    Posts(db) { db.hasMany('Post'); }
}

class Post {
    id(db) { db.integer().primary().auto(); }
    title(db) { db.string(); }

    // Many-to-one: Post belongs to a User (creates the user_id column)
    User(db) { db.belongsTo('User'); }
}

Field Transformers

Store complex JavaScript types in simple database columns:

Use .set(fn) to transform a value on its way into the database and .get(fn) to transform it on the way out:

class User {
    id(db) { db.integer().primary().auto(); }

    // Store arrays as JSON strings transparently
    tags(db) {
        db.string()
          .set((value) => (Array.isArray(value) ? JSON.stringify(value) : value))
          .get((value) => (value ? JSON.parse(value) : []));
    }
}

// Usage is natural
const user = db.User.new();
user.tags = ['admin', 'moderator'];  // Assign array
await db.saveChanges();  // Stored as '["admin","moderator"]'

const loaded = await db.User.findById(user.id);
console.log(loaded.tags);  // ['admin', 'moderator'] - JavaScript array!

Querying

Basic Queries

// Find all (requires await)
const users = await db.User.toList();

// Find by primary key (requires await)
const user = await db.User.findById(123);

// Find single with where clause (requires await)
const alice = await db.User
    .where(u => u.email == $$, '[email protected]')
    .single();

// Find multiple with conditions (requires await)
const adults = await db.User
    .where(u => u.age >= $$, 18)
    .toList();

Parameterized Queries

Always use $$ placeholders for SQL injection protection:

// Single parameter (requires await)
const user = await db.User.where(u => u.id == $$, 123).single();

// Multiple parameters (requires await)
const results = await db.User
    .where(u => u.age > $$ && u.status == $$, 25, 'active')
    .toList();

// Single $ for OR conditions (requires await)
const results = await db.User
    .where(u => u.status == $ || u.status == null, 'active')
    .toList();

IN Clauses

// Array parameter with .includes() (requires await)
const ids = [1, 2, 3, 4, 5];
const users = await db.User
    .where(u => $$.includes(u.id), ids)
    .toList();

// Generated SQL: WHERE id IN ($1, $2, $3, $4, $5)
// PostgreSQL parameters: [1, 2, 3, 4, 5]

// Alternative .any() syntax (requires await)
const users = await db.User
    .where(u => u.id.any($$), [1, 2, 3])
    .toList();

// Comma-separated strings (auto-splits) (requires await)
const users = await db.User
    .where(u => u.id.any($$), "1,2,3,4,5")
    .toList();

Query Chaining

let query = db.User;

// Build query dynamically
if (searchTerm) {
    query = query.where(u => u.name.like($$), `%${searchTerm}%`);
}

if (minAge) {
    query = query.where(u => u.age >= $$, minAge);
}

// Add sorting and pagination (requires await)
const users = await query
    .orderBy(u => u.created_at)
    .skip(offset)
    .take(limit)
    .toList();

Ordering

// Ascending (requires await)
const users = await db.User
    .orderBy(u => u.name)
    .toList();

// Descending (requires await)
const users = await db.User
    .orderByDescending(u => u.created_at)
    .toList();

Pagination

// Skip 20, take 10 (requires await)
const users = await db.User
    .orderBy(u => u.id)
    .skip(20)
    .take(10)
    .toList();

// Page-based pagination (requires await)
const page = 2;
const pageSize = 10;
const users = await db.User
    .skip(page * pageSize)
    .take(pageSize)
    .toList();

Counting

// Count all (requires await)
const total = await db.User.count();

// Count with conditions (requires await)
const activeCount = await db.User
    .where(u => u.status == $$, 'active')
    .count();

Complex Queries

// Multiple conditions with OR (requires await)
const results = await db.User
    .where(u => (u.status == 'active' || u.status == 'pending') && u.age >= $$, 18)
    .orderBy(u => u.name)
    .toList();

// Nullable checks (requires await)
const usersWithoutEmail = await db.User
    .where(u => u.email == null)
    .toList();

// LIKE queries (requires await)
const matching = await db.User
    .where(u => u.name.like($$), '%john%')
    .toList();

Full-Text Search

Portable full-text search that runs on each engine's native FTS implementation: FTS5 on SQLite, tsvector + GIN on PostgreSQL, FULLTEXT INDEX on MySQL. Use it when LIKE isn't enough — when you want stemming, tokenization, multi-word queries, and ranking.

Set up the index in a migration

import masterrecord from 'masterrecord';

class AddMemoryDocFts extends masterrecord.schema {
    async up(table) {
        await this.init(table);
        await this.createTable(table.MemoryDoc);
        await this.createFullTextIndex({
            tableName: 'MemoryDoc',
            columns: ['title', 'body'],
        });
    }

    async down(table) {
        await this.init(table);
        await this.dropFullTextIndex({ tableName: 'MemoryDoc' });
        await this.dropTable(table.MemoryDoc);
    }
}

export default AddMemoryDocFts;

Query

const docs = await db.MemoryDoc
    .search({ in: ['title', 'body'], query: 'auth login' })
    .where(d => d.workspace_id == $$, workspaceId)
    .take(10)
    .toList();

// Each row has a __rank field; default ordering is rank descending.
for (const d of docs) console.log(d.__rank, d.title);

.search() composes with .where(), .take(), .skip(), and .orderBy(). Engine-specific query syntax (FTS5's NEAR / prefix, Postgres to_tsquery operators, MySQL boolean mode), ranking semantics, and engine caveats are documented in docs/FULL_TEXT_SEARCH.md.

Migrations

CLI Commands

# Enable migrations (one-time per context)
masterrecord enable-migrations AppContext

# Create a migration
masterrecord add-migration MigrationName AppContext

# Apply migrations
masterrecord update-database AppContext

# List migrations
masterrecord get-migrations AppContext

# Multi-context commands
masterrecord enable-migrations-all          # Enable for all contexts
masterrecord add-migration-all Init         # Create migration for all
masterrecord update-database-all            # Apply all pending migrations

Migration File Structure

// db/migrations/20250111_143052_CreateUser.js
import masterrecord from 'masterrecord';

class CreateUser extends masterrecord.schema {
    constructor(context) {
        super(context);
    }

    // IMPORTANT: Migrations must be async
    async up(table) {
        await this.init(table);

        // Create table
        await this.createTable(table.User);

        // Seed initial data
        this.seed('User', {
            name: 'Admin',
            email: '[email protected]',
            role: 'admin'
        });
    }

    async down(table) {
        await this.init(table);

        // Rollback
        await this.dropTable(table.User);
    }
}

export default CreateUser;

Migration Operations

class MyMigration extends masterrecord.schema {
    async up(table) {
        this.init(table);

        // Create table (requires await)
        await this.createTable(table.User);

        // Add column
        schema.addColumn({
            tableName: 'User',
            name: 'phone',
            type: 'string'
        });

        // Alter column
        schema.alterColumn({
            tableName: 'User',
            table: {
                name: 'age',
                type: 'integer',
                nullable: false,
                default: 0
            }
        });

        // Rename column
        schema.renameColumn({
            tableName: 'User',
            name: 'old_name',
            newName: 'new_name'
        });

        // Drop column
        schema.dropColumn({
            tableName: 'User',
            name: 'deprecated_field'
        });

        // Drop table
        schema.dropTable(table.OldTable);
    },

    down: function(table, schema) {
        // Reverse operations
    }
};

Seed Data

import masterrecord from 'masterrecord';

class SeedUsers extends masterrecord.schema {
    async up(table) {
        await this.init(table);
        await this.createTable(table.User);

        // Single record
        this.seed('User', {
            name: 'Admin',
            email: '[email protected]'
        });

        // Multiple records (efficient bulk insert)
        this.bulkSeed('User', [
            { name: 'Alice', email: '[email protected]', age: 25 },
            { name: 'Bob', email: '[email protected]', age: 30 },
            { name: 'Charlie', email: '[email protected]', age: 35 }
        ]);
    }

    async down(table) {
        await this.init(table);
        await this.dropTable(table.User);
    }
}

export default SeedUsers;

Seed data is idempotent - re-running migrations won't create duplicates:

SQLite: INSERT OR IGNORE
MySQL: INSERT IGNORE
PostgreSQL: INSERT ... ON CONFLICT DO NOTHING

Advanced Features

Type Validation

MasterRecord validates and coerces field types at runtime:

const user = db.User.new();
user.age = "25";  // String assigned to integer field
await db.saveChanges();
// ⚠️  Console: Auto-converting string "25" to integer 25

user.age = "invalid";
await db.saveChanges();
// ❌ Error: Field User.age must be an integer, got string "invalid"

Field Transformers (Advanced)

class Post {
    id(db) { db.integer().primary().auto(); }

    // Store array as JSON in a TEXT column
    tags(db) {
        db.string()
          .set((v) => (Array.isArray(v) ? JSON.stringify(v) : v))
          .get((v) => (v ? JSON.parse(v) : []));
    }

    // Native JSON column (JSON/JSONB on Postgres & MySQL, TEXT on SQLite)
    metadata(db) {
        db.json()
          .set((v) => JSON.stringify(v || {}))
          .get((v) => (typeof v === 'string' ? JSON.parse(v) : v));
    }
}

Table Prefixes

Useful for multi-tenant applications or plugin systems:

class AppContext extends context {
    constructor() {
        super();

        this.tablePrefix = 'myapp_';  // Set before dbset()
        this.env('config/environments');

        this.dbset(User);  // Creates table: myapp_User
        this.dbset(Post);  // Creates table: myapp_Post
    }
}

Transactions (PostgreSQL)

import PostgresSyncConnect from 'masterrecord/postgresSyncConnect';

const connection = new PostgresSyncConnect();
await connection.connect(config);

const result = await connection.transaction(async (client) => {
    // Insert user
    const userResult = await client.query(
        'INSERT INTO User (name, email) VALUES ($1, $2) RETURNING id',
        ['Alice', '[email protected]']
    );

    // Insert related record
    await client.query(
        'INSERT INTO Profile (user_id, bio) VALUES ($1, $2)',
        [userResult.rows[0].id, 'Software Engineer']
    );

    return userResult.rows[0].id;
});

// Automatically commits on success, rolls back on error

Query Result Caching

MasterRecord includes a production-grade two-level caching system similar to Entity Framework and Hibernate. The cache dramatically improves performance by storing query results and automatically invalidating them when data changes.

How It Works

┌─────────────────────────────────────────────────────┐
│              First-Level Cache (Identity Map)       │
│  - Request-scoped entity tracking                   │
│  - O(1) entity lookup                               │
│  - Already in MasterRecord                          │
└─────────────────────────────────────────────────────┘
                       ▼
┌─────────────────────────────────────────────────────┐
│       Second-Level Cache (Query Result Cache)       │
│  - Application-wide query result storage            │
│  - Automatic invalidation on data changes           │
│  - In-memory (development) or Redis (production)    │
└─────────────────────────────────────────────────────┘

Basic Usage (Opt-In, Request-Scoped)

Caching is opt-in and request-scoped like Active Record. Use .cache() to enable caching, and call endRequest() to clear:

const db = new AppContext();

// DEFAULT: No caching (always hits database)
const user = db.User.findById(1);  // DB query
const user2 = db.User.findById(1);  // DB query again (no cache)

// OPT-IN: Enable caching with .cache()
const categories = await db.Categories.cache().toList();  // DB query, cached
const categories2 = await db.Categories.cache().toList();  // Cache hit! (instant)

// Update invalidates cache automatically
const cat = await db.Categories.findById(1);
cat.name = "Updated";
await db.saveChanges();  // Cache for Categories table cleared

// End request (clears cache - like Active Record)
db.endRequest();  // Cache cleared for next request

Web Application Pattern (Recommended):

// Express middleware - automatic request-scoped caching
app.use((req, res, next) => {
    req.db = new AppContext();

    // Clear cache when response finishes (like Active Record)
    res.on('finish', () => {
        req.db.endRequest();  // Clears query cache
    });

    next();
});

// In your routes
app.get('/categories', async (req, res) => {
    // Cache is fresh for this request
    const categories = await req.db.Categories.cache().toList();
    res.json(categories);
    // Cache auto-cleared after response
});

Configuration

Configure caching via environment variables:

# Development (.env)
QUERY_CACHE_TTL=5000               # TTL in milliseconds (5000ms = 5 seconds - request-scoped)
QUERY_CACHE_SIZE=1000              # Max cache entries (default: 1000)
QUERY_CACHE_ENABLED=true           # Enable/disable globally (default: true)

# Production (.env)
QUERY_CACHE_TTL=5                  # Redis uses seconds (5 seconds default)
REDIS_URL=redis://localhost:6379  # Use Redis for distributed caching

Note:

Cache is opt-in per query using .cache()
Default TTL is 5 seconds (request-scoped like Active Record)
Call db.endRequest() to clear cache manually (recommended in middleware)
Environment variables control the cache system globally

Enable Caching for Specific Queries

Use .cache() for frequently accessed, rarely changed data:

// DEFAULT: Always hits database (safe)
const liveData = await db.Analytics
    .where(a => a.date == $$, today)
    .toList();  // No caching (default)

// OPT-IN: Cache reference data
const categories = await db.Categories.cache().toList();  // Cached for 5 seconds (default TTL)
const settings = await db.Settings.cache().toList();  // Cached
const countries = await db.Countries.cache().toList();  // Cached

// When to use .cache():
// ✅ Reference data (categories, settings, countries)
// ✅ Rarely changing data (roles, permissions)
// ✅ Expensive aggregations with stable results
// ❌ User-specific data
// ❌ Real-time data
// ❌ Financial/critical data

Manual Cache Control

const db = new AppContext();

// Check cache performance
const stats = db.getCacheStats();
console.log(stats);
// {
//   size: 45,
//   maxSize: 1000,
//   hits: 234,
//   misses: 67,
//   hitRate: '77.74%',
//   enabled: true
// }

// Clear cache manually
db.clearQueryCache();

// Disable caching temporarily
db.setQueryCacheEnabled(false);
const freshData = await db.User.toList();
db.setQueryCacheEnabled(true);

Redis-Based Distributed Caching (Production)

For multi-process or clustered deployments, use Redis:

import { createClient } from 'redis';
import RedisQueryCache from 'masterrecord/Cache/RedisQueryCache';

class AppContext extends context {
    constructor() {
        super();

        // Use Redis cache in production
        if (process.env.NODE_ENV === 'production' && process.env.REDIS_URL) {
            const redisClient = redis.createClient(process.env.REDIS_URL);
            this._queryCache = new RedisQueryCache(redisClient, {
                ttl: 300,  // 5 minutes (seconds for Redis)
                prefix: 'myapp:'
            });
        }
        // In-memory cache used automatically in development

        this.dbset(User);
    }
}

Benefits of Redis cache:

Shared across processes (horizontally scalable)
Pub/sub invalidation (cache stays consistent)
Two-level cache (L1 in-memory + L2 Redis)
Automatic failover to database on Redis errors

Cache Invalidation Strategy

MasterRecord automatically invalidates cache entries when data changes:

// Query with caching enabled
const categories = await db.Categories.cache().toList();  // DB query, cached

// Any modification to Categories table invalidates ALL cached Category queries
const cat = await db.Categories.findById(1);
cat.name = "Updated";
await db.saveChanges();  // Invalidates all cached Categories queries

// Next cached query hits database (fresh data)
const categoriesAgain = await db.Categories.cache().toList();  // DB query (cache cleared)

// Non-cached queries are unaffected (always fresh)
const users = await db.User.toList();  // No .cache() = always DB query

// Queries for OTHER tables' caches are unaffected
const settings = await db.Settings.cache().toList();  // Still cached (different table)

Invalidation rules:

INSERT invalidates all queries for that table
UPDATE invalidates all queries for that table
DELETE invalidates all queries for that table
Queries for other tables are not affected

Performance Impact

Expected performance improvements:

| Scenario | Without Cache | With Cache | Improvement | |----------|---------------|------------|-------------| | Single query (100 calls) | 100 DB queries | 1 DB + 99 cache | 99% faster | | List query (50 calls) | 50 DB queries | 1 DB + 49 cache | 98% faster | | Reference data (1000 calls) | 1000 DB queries | 1 DB + 999 cache | 99.9% faster | | Mixed operations | Baseline | 70-90% hit rate | 3-10x faster |

Memory usage: ~1KB per cached query (1000 entries ≈ 1MB)

Best Practices

DO use .cache():

// Reference data (rarely changes)
const categories = await db.Categories.cache().toList();
const settings = await db.Settings.cache().toList();
const countries = await db.Countries.cache().toList();

// Expensive aggregations (stable results)
const totalRevenue = await db.Orders
    .where(o => o.year == $$, 2024)
    .cache()
    .count();

DON'T use .cache():

// User-specific data (default is safe - no caching)
const user = await db.User.findById(userId);  // Always fresh

// Real-time data (default is safe)
const liveOrders = await db.Orders
    .where(o => o.status == $$, 'pending')
    .toList();  // Always fresh

// Financial transactions (default is safe)
const balance = await db.Transactions
    .where(t => t.user_id == $$, userId)
    .toList();  // Always fresh

// User-specific sensitive data (default is safe)
const permissions = await db.UserPermissions
    .where(p => p.user_id == $$, userId)
    .toList();  // Always fresh

Monitoring Cache Performance

// Log cache stats periodically
setInterval(() => {
    const stats = db.getCacheStats();
    console.log(`Cache: ${stats.hitRate} hit rate, ${stats.size}/${stats.maxSize} entries`);
}, 60000);

// Watch for low hit rates (< 50% might indicate poor cache strategy)
if (parseFloat(stats.hitRate) < 50) {
    console.warn('Cache hit rate is low, consider tuning cache TTL or size');
}

Request-Scoped Caching (Like Active Record)

MasterRecord's caching is designed to work like Active Record - cache within a request, clear after:

// Express middleware pattern (recommended)
app.use((req, res, next) => {
    req.db = new AppContext();

    // Automatically clear cache when request ends
    res.on('finish', () => {
        req.db.endRequest();  // Like Active Record's cache clearing
    });

    next();
});

// In routes - cache is fresh per request
app.get('/api/categories', async (req, res) => {
    // First call in this request - DB query
    const categories = await req.db.Categories.cache().toList();

    // Second call in same request - cache hit
    const categoriesAgain = await req.db.Categories.cache().toList();

    res.json(categories);
    // After response, cache is automatically cleared
});

// Next request starts with empty cache (fresh)

Why request-scoped?

✅ Like Active Record - familiar pattern
✅ No stale data across requests
✅ Cache only lives during request processing
✅ Automatic cleanup

Important: Shared Cache Behavior

The cache is shared across all context instances of the same class. This ensures consistency within a request:

const db1 = new AppContext();
const db2 = new AppContext();

// Context 1: Cache data with .cache()
const categories1 = await db1.Categories.cache().toList();  // DB query, cached

// Context 2: Sees cached data
const categories2 = await db2.Categories.cache().toList();  // Cache hit!

// Context 2: Updates invalidate cache for BOTH contexts
const cat = await db2.Categories.findById(1);
cat.name = "Updated";
await db2.saveChanges();  // Invalidates shared cache

// Context 1: Sees fresh data
const categories3 = await db1.Categories.cache().toList();  // Cache miss, fresh data
console.log(categories3[0].name);  // "Updated"

Why shared cache?

✅ Prevents stale data across multiple context instances
✅ Ensures all parts of your application see consistent data
✅ Reduces memory usage (one cache instead of many)
✅ Correct behavior for single-database applications (most use cases)

Multi-Context Applications

Manage multiple databases in one application:

// contexts/userContext.js
class UserContext extends context {
    constructor() {
        super();
        this.env({ type: 'postgres', database: 'users_db', ... });
        this.dbset(User);
        this.dbset(Profile);
    }
}

// contexts/analyticsContext.js
class AnalyticsContext extends context {
    constructor() {
        super();
        this.env({ type: 'postgres', database: 'analytics_db', ... });
        this.dbset(Event);
        this.dbset(Metric);
    }
}

// Usage
const userDb = new UserContext();
const analyticsDb = new AnalyticsContext();

const user = await userDb.User.findById(123);
const event = analyticsDb.Event.new();
event.log('user_login', user.id);
await analyticsDb.saveChanges();

# Migrate all contexts at once
masterrecord update-database-all

Raw SQL Queries

For SQL the query builder can't express, use the engine-agnostic escape hatch db.query(sql, params) (alias db.execute(sql, params)). It works the same on SQLite, MySQL, and PostgreSQL and returns an array of row objects for row-returning statements:

// SELECT → array of rows (all engines)
const users = await db.query(
    'SELECT * FROM "User" WHERE age > $1 AND status = $2',   // Postgres: $1,$2
    [25, 'active']
);
// SQLite / MySQL use ? placeholders:
const users = await db.query('SELECT * FROM User WHERE age > ? AND status = ?', [25, 'active']);

// Writes — reads naturally as execute() (works on every engine)
await db.execute('UPDATE Step SET run_id = ? WHERE id = ?', ['run_x', 1]);

Do not reach into db.db. That is the raw, engine-specific driver — db.db.prepare() is better-sqlite3's synchronous API and does not exist on mysql2/pg, so code written against it works on SQLite and breaks on MySQL/Postgres. (On a non-SQLite engine, db.db.prepare() now throws a descriptive error pointing you here.) Prefer the ORM (db.User.where(...).toList()); use db.query() only when you must.

Placeholders are engine-native (? for SQLite/MySQL, $1,$2,… for Postgres) — query() passes them straight through, so a raw statement is as portable as the SQL you write. For fully portable code, prefer the query builder.

API Reference

Context Methods

// Entity registration
context.dbset(EntityClass)
context.dbset(EntityClass, 'custom_table_name')

// Save changes (all databases now async)
await context.saveChanges()  // PostgreSQL, MySQL, SQLite (all async)

// Add/Remove entities
context.EntityName.add(entity)
context.remove(entity)

// Attach detached entities (like Entity Framework's Update())
context.attach(entity)                        // Attach and mark as modified
context.attach(entity, { field: value })      // Attach with specific changes
context.attachAll([entity1, entity2])         // Attach multiple entities
await context.update('Entity', id, changes)   // Update by primary key

// Engine-agnostic raw SQL (escape hatch — prefer the query builder)
await context.query(sql, params)              // raw SQL → array of rows (all engines)
await context.execute(sql, params)            // alias; reads naturally for writes

// Cache management
context.getCacheStats()              // Get cache statistics
context.clearQueryCache()            // Clear all cached queries
context.endRequest()                 // End request and clear cache (like Active Record)
context.setQueryCacheEnabled(bool)   // Enable/disable caching

Query Methods

// Chainable query builders (do not execute query)
.where(query, ...params)         // Add WHERE condition
.and(query, ...params)           // Add AND condition
.orderBy(field)                  // Sort ascending
.orderByDescending(field)        // Sort descending
.skip(number)                    // Skip N records
.take(number)                    // Limit to N records
.include(relationship)           // Eager load
.cache()                         // Enable caching for this query (opt-in)

// Terminal methods (execute query - ALL REQUIRE AWAIT)
await .toList()                  // Return array of all records
await .single()                  // Return one or null
await .first()                   // Return first or null
await .count()                   // Return count
await .any()                     // Return boolean

// Convenience methods (REQUIRE AWAIT)
await .findById(id)              // Find by primary key
.new()                           // Create new entity instance (synchronous)

// Entity methods (Active Record style - REQUIRE AWAIT)
await entity.save()              // Save this entity (and all tracked changes)
await entity.delete()            // Delete this entity
await entity.reload()            // Reload from database, discarding changes
entity.clone()                   // Create a copy for duplication (synchronous)
entity.toObject(options)         // Convert to plain JavaScript object (synchronous)
entity.toJSON()                  // JSON.stringify compatibility (synchronous)

Entity Serialization

.toObject(options)

Convert a MasterRecord entity to a plain JavaScript object, removing all internal properties and handling circular references automatically.

Parameters:

options.includeRelationships (boolean, default: true) - Include related entities
options.depth (number, default: 1) - Maximum depth for relationship traversal

Examples:

// Basic usage - get plain object
const user = await db.User.findById(1);
const plain = user.toObject();
console.log(plain);
// { id: 1, name: 'Alice', email: '[email protected]', age: 28 }

// Include relationships
const userWithPosts = user.toObject({ includeRelationships: true });
console.log(userWithPosts);
// {
//   id: 1,
//   name: 'Alice',
//   Posts: [
//     { id: 10, title: 'First Post', content: '...' },
//     { id: 11, title: 'Second Post', content: '...' }
//   ]
// }

// Control relationship depth
const deep = user.toObject({ includeRelationships: true, depth: 3 });

// Exclude relationships
const shallow = user.toObject({ includeRelationships: false });

Circular Reference Protection:

.toObject() automatically prevents infinite loops from circular references:

// Scenario: User → Posts → User creates a cycle
const user = await db.User.findById(1);
await user.Posts;  // Load posts relationship

const plain = user.toObject({ includeRelationships: true, depth: 2 });
// Circular references marked as:
// { __circular: true, __entityName: 'User', id: 1 }

Why It's Needed:

MasterRecord entities have internal properties that cause JSON.stringify() to fail:

const user = await db.User.findById(1);

// ❌ FAILS: TypeError: Converting circular structure to JSON
JSON.stringify(user);

// ✅ WORKS: Use toObject() or toJSON()
const plain = user.toObject();
JSON.stringify(plain);  // Success!

.toJSON()

Used automatically by JSON.stringify() and Express res.json(). Returns the same as .toObject({ includeRelationships: false }).

Examples:

// JSON.stringify automatically calls toJSON()
const user = await db.User.findById(1);
const json = JSON.stringify(user);
console.log(json);
// '{"id":1,"name":"Alice","email":"[email protected]"}'

// Express automatically uses toJSON()
app.get('/api/users/:id', async (req, res) => {
    const user = await db.User.findById(req.params.id);
    res.json(user);  // ✅ Works automatically!
});

// Array of entities
app.get('/api/users', async (req, res) => {
    const users = await db.User.toList();
    res.json(users);  // ✅ Each entity's toJSON() called automatically
});

Entity Instance Methods

.delete()

Delete an entity without manually calling context.remove() and context.saveChanges().

Example:

// Before
const user = await db.User.findById(1);
db.remove(user);
await db.saveChanges();

// After (Active Record style)
const user = await db.User.findById(1);
await user.delete();  // ✅ Entity deletes itself

Cascade Deletion:

If your entity has cascade delete rules, they will be applied automatically:

class User {
    id(db) { db.integer().primary().auto(); }

    // hasMany cascades by default — call .stopCascadeOnDelete() to opt out
    Posts(db) { db.hasMany('Post'); }
}

const user = await db.User.findById(1);
await user.delete();  // ✅ Also deletes related Posts automatically

.reload()

Refresh an entity from the database, discarding any unsaved changes.

Example:

const user = await db.User.findById(1);
console.log(user.name);  // 'Alice'

user.name = 'Modified';
console.log(user.name);  // 'Modified'

await user.reload();  // ✅ Fetch fresh data from database
console.log(user.name);  // 'Alice' - changes discarded

Use Cases:

Discard unsaved changes
Refresh stale data after external updates
Synchronize after concurrent modifications
Reset entity to clean state

.clone()

Create a copy of an entity for duplication (primary key excluded).

Example:

const user = await db.User.findById(1);
const duplicate = user.clone();

duplicate.name = 'Copy of ' + user.name;
duplicate.email = '[email protected]';

await duplicate.save();
console.log(duplicate.id);  // ✅ New ID (different from original)

Notes:

Primary key is automatically excluded
Relationships are not cloned (set manually if needed)
Useful for templates and duplicating records

Query Helper Methods

.first()

Get the first record ordered by primary key.

Example:

// Automatically orders by primary key
const firstUser = await db.User.first();

// With custom order (respects existing orderBy)
const newestUser = await db.User
    .orderByDescending(u => u.created_at)
    .first();

// With conditions
const firstActive = await db.User
    .where(u => u.status == $$, 'active')
    .first();

.last()

Get the last record ordered by primary key (descending).

Example:

const lastUser = await db.User.last();

// With custom order
const oldestUser = await db.User
    .orderBy(u => u.created_at)
    .last();

.exists()

Check if any records match the query (returns boolean).

Example:

// Before
const count = await db.User
    .where(u => u.email == $$, '[email protected]')
    .count();
const exists = count > 0;

// After
const exists = await db.User
    .where(u => u.email == $$, '[email protected]')
    .exists();

if (exists) {
    throw new Error('Email already registered');
}

// Check if any users exist
const hasUsers = await db.User.exists();
if (!hasUsers) {
    // Create default admin user
}

.pluck(fieldName)

Extract a single column as an array.

Example:

// Get all active user emails
const emails = await db.User
    .where(u => u.status == $$, 'active')
    .pluck('email');
console.log(emails);
// ['[email protected]', '[email protected]', '[email protected]']

// Get all user IDs
const ids = await db.User.pluck('id');
console.log(ids);  // [1, 2, 3, 4, 5]

// With sorting
const recentEmails = await db.User
    .orderByDescending(u => u.created_at)
    .take(10)
    .pluck('email');

Lifecycle Hooks

Add lifecycle hooks to your entity definitions to execute logic before/after database operations.

Available Hooks:

beforeSave() - Execute before insert or update
afterSave() - Execute after insert or update
beforeDelete() - Execute before deletion
afterDelete() - Execute after deletion

Example:

import bcrypt from 'bcrypt';

class User {
    id(db) { db.integer().primary().auto(); }
    email(db) { db.string(); }
    password(db) { db.string(); }
    created_at(db) { db.datetime(); }
    updated_at(db) { db.datetime(); }
    role(db) { db.string(); }

    // Hash the password (when changed) and stamp timestamps before saving.
    beforeSave() {
        if (this.__dirtyFields.includes('password')) {
            this.password = bcrypt.hashSync(this.password, 10);
        }
        if (this.__state === 'insert') {
            this.created_at = new Date();
        }
        this.updated_at = new Date();
    }

    // Log after successful save
    afterSave() {
        console.log(`User ${this.id} saved successfully`);
    }

    // Prevent deleting admin users
    beforeDelete() {
        if (this.role === 'admin') {
            throw new Error('Cannot delete admin user');
        }
    }

    // Cleanup related data after deletion
    async afterDelete() {
        console.log(`User ${this.id} deleted, cleaning up related data...`);
        // Cleanup logic here (e.g., delete user files, clear cache)
    }
}

Usage:

// Hooks execute automatically during save
const user = db.User.new();
user.email = '[email protected]';
user.password = 'plain-text-password';
await user.save();
// ✅ beforeSave() hashes password automatically
// ✅ afterSave() logs success message

// Load and update
const user = await db.User.findById(1);
user.email = '[email protected]';
await user.save();
// ✅ beforeSave() sets updated_at timestamp
// ✅ Password not re-hashed (not in dirtyFields)

// Hooks can prevent operations
const admin = await db.User.where(u => u.role == $$, 'admin').single();
try {
    await admin.delete();
} catch (error) {
    console.log(error.message);  // "Cannot delete admin user"
}
// ✅ beforeDelete() prevented deletion

Hook Execution Order:

// Insert:
// 1. beforeSave()
// 2. SQL INSERT
// 3. afterSave()

// Update:
// 1. beforeSave()
// 2. SQL UPDATE
// 3. afterSave()

// Delete:
// 1. beforeDelete()
// 2. SQL DELETE
// 3. afterDelete()

Notes:

Hooks can be async (use async keyword)
Exceptions in before* hooks prevent the operation
Hooks execute for each entity during batch operations
Access entity state via this.__state ('insert', 'modified', 'delete')
Access changed fields via this.__dirtyFields array

Field Constraints & Indexes

Define database constraints and performance indexes using the fluent API:

class User {
    id(db) {
        db.integer().primary().auto();
    }

    email(db) {
        db.string()
          .notNullable()
          .unique()
          .index();  // Creates performance index
    }

    username(db) {
        db.string()
          .notNullable()
          .index('idx_username_custom');  // Custom index name
    }

    status(db) {
        db.string().nullable();
    }

    created_at(db) {
        db.timestamp().default('CURRENT_TIMESTAMP');
    }
}

Available Constraint Methods

.notNullable() - Column cannot be NULL
.nullable() - Column can be NULL (default)
.unique() - Unique constraint (enforces uniqueness at DB level)
.index() - Creates performance index (auto-generated name: idx_tablename_columnname)
.index('custom_name') - Creates index with custom name
.primary() - Primary key (automatically indexed)
.default(value) - Default value

Index vs Unique Constraint

Understanding the difference:

.unique() creates a UNIQUE constraint (prevents duplicate values, enforces data integrity)
.index() creates a performance index (improves query speed, allows duplicates)
You can use both together: .unique().index() creates a unique index for both integrity and performance

Examples:

// Email must be unique (no performance index)
email(db) {
    db.string().notNullable().unique();
}

// Username indexed for fast lookups (allows duplicates)
username(db) {
    db.string().notNullable().index();
}

// Email with both unique constraint AND performance index
email(db) {
    db.string().notNullable().unique().index();
}

Automatic Index Migration

When you add .index() to a field, MasterRecord automatically generates migration code:

// In your entity
class User {
    email(db) {
        db.string().notNullable().index();
    }
}

// Generated migration (automatic)
class Migration_20250101 extends masterrecord.schema {
    async up(table) {
        this.init(table);
        this.createIndex({
            tableName: 'User',
            columnName: 'email',
            indexName: 'idx_user_email'
        });
    }

    async down(table) {
        this.init(table);
        this.dropIndex({
            tableName: 'User',
            columnName: 'email',
            indexName: 'idx_user_email'
        });
    }
}

Rollback support:

Migrations automatically include rollback logic. Running masterrecord migrate down will drop all indexes created by that migration.

Composite Indexes

Create multi-column indexes for queries that filter or sort on multiple columns together.

API - Two Ways to Define

Option A: Entity Class (Recommended for core indexes)

class CreditLedger {
    id(db) {
        db.integer().primary().auto();
    }

    organization_id(db) {
        db.integer().notNullable();
    }

    created_at(db) {
        db.timestamp().default('CURRENT_TIMESTAMP');
    }

    resource_type(db) {
        db.string().notNullable();
    }

    resource_id(db) {
        db.integer().notNullable();
    }

    // Define composite indexes in entity
    static compositeIndexes = [
        // Simple array - auto-generates name
        ['organization_id', 'created_at'],
        ['resource_type', 'resource_id'],

        // With custom name
        {
            columns: ['status', 'created_at'],
            name: 'idx_status_timeline'
        },

        // Unique composite index
        {
            columns: ['email', 'tenant_id'],
            unique: true
        }
    ];
}

Option C: Context-Level (For environment-specific or centralized schema)

class AppContext extends context {
    onConfig() {
        this.dbset(CreditLedger);

        // Define composite indexes in context
        this.compositeIndex(CreditLedger, ['organization_id', 'created_at']);
        this.compositeIndex(CreditLedger, ['resource_type', 'resource_id']);
        this.compositeIndex(CreditLedger, ['status', 'created_at'], {
            name: 'idx_status_timeline'
        });
        this.compositeIndex(CreditLedger, ['email', 'tenant_id'], {
            unique: true
        });

        // Can also use table name as string
        this.compositeIndex('CreditLedger', ['user_id', 'created_at']);
    }
}

Combined Usage (Best of Both)

class User {
    email(db) { db.string(); }
    tenant_id(db) { db.integer(); }
    last_name(db) { db.string(); }
    first_name(db) { db.string(); }

    // Core indexes in entity
    static compositeIndexes = [
        ['last_name', 'first_name']
    ];
}

class AppContext extends context {
    onConfig() {
        this.dbset(User);

        // Add tenant-specific index for multi-tenant deployments
        if (process.env.MULTI_TENANT === 'true') {
            this.compositeIndex(User, ['tenant_id', 'email'], { unique: true });
        }

        // Add performance index for production
        if (process.env.NODE_ENV === 'production') {
            this.compositeIndex(User, ['tenant_id', 'last_name']);
        }
    }
}

When to Use Composite Indexes

Composite indexes are most effective for queries that:

Filter on multiple columns: WHERE org_id = ? AND status = ?
Filter and sort: WHERE status = ? ORDER BY created_at
Enforce uniqueness: Unique constraint on multiple columns together

Example queries that benefit:

// Benefits from composite index (organization_id, created_at)
const ledger = await db.CreditLedger
    .where(c => c.organization_id == $$, orgId)
    .orderBy(c => c.created_at)
    .toList();

// Benefits from composite index (resource_type, resource_id)
const entry = await db.CreditLedger
    .where(c => c.resource_type == $$ && c.resource_id == $$, 'Order', 123)
    .single();

Column Order Matters

The order of columns in a composite index affects query performance:

static compositeIndexes = [
    // Index: (status, created_at)
    ['status', 'created_at']
];

// ✅ FAST: Uses index efficiently
// WHERE status = ? ORDER BY created_at
await db.Orders
    .where(o => o.status == $$, 'pending')
    .orderBy(o => o.created_at)
    .toList();

// ⚠️ SLOWER: Can only use first column
// WHERE created_at > ?
await db.Orders
    .where(o => o.created_at > $$, yesterday)
    .toList();

Rule of thumb: Put the most selective (filtered) columns first, then sort columns.

Automatic Migration Generation

// Your entity definition triggers migration
class CreditLedger {
    organization_id(db) { db.integer(); }
    created_at(db) { db.timestamp(); }

    static compositeIndexes = [
        ['organization_id', 'created_at']
    ];
}

// Generated migration (automatic)
class Migration_20250101 extends masterrecord.schema {
    async up(table) {
        this.init(table);
        this.createCompositeIndex({
            tableName: 'CreditLedger',
            columns: ['organization_id', 'created_at'],
            indexName: 'idx_creditleger_organization_id_created_at',
            unique: false
        });
    }

    async down(table) {
        this.init(table);
        this.dropCompositeIndex({
            tableName: 'CreditLedger',
            columns: ['organization_id', 'created_at'],
            indexName: 'idx_creditleger_organization_id_created_at',
            unique: false
        });
    }
}

Single vs Composite Indexes

class User {
    email(db) {
        db.string().index();  // Single-column index
    }

    first_name(db) {
        db.string();  // Part of composite below
    }

    last_name(db) {
        db.string();  // Part of composite below
    }

    static compositeIndexes = [
        // Composite index for name lookups
        ['last_name', 'first_name']
    ];
}

When to use single vs composite:

Single index: Column queried independently (WHERE email = ?)
Composite index: Columns queried together (WHERE last_name = ? AND first_name = ?)

Partial / Filtered Indexes

Add a where predicate to index on (and enforce uniqueness over) only the rows that match — the same capability as EF Core's HasFilter, TypeORM/Sequelize where, Rails :where, and Django condition.

The canonical use is one-default-per-scope — at most one is_default = 1 row per scope_id, enforced by the database:

// Declaratively, on the context:
class AppContext extends context {
    onConfig() {
        this.dbset(Setting);
        this.compositeIndex(Setting, ['scope_id'], {
            unique: true,
            where: 'is_default = 1',   // raw SQL predicate
            name: 'one_default_per_scope',
        });
    }
}

// Or in a hand-written migration:
await this.createCompositeIndex({
    tableName: 'Setting',
    columns: ['scope_id'],
    indexName: 'one_default_per_scope',
    unique: true,
    where: 'is_default = 1',
});
// single-column form: this.createIndex({ tableName, columnName, indexName, unique: true, where })

Other common uses: unique email among non-deleted rows (where: 'deleted_at IS NULL'), indexing only active records (where: 'status = \'active\'').

| Engine | Behavior | | --- | --- | | PostgreSQL | native partial index (CREATE UNIQUE INDEX … WHERE …) | | SQLite | native partial index (3.8+) | | MySQL | not supported — MySQL has no partial indexes. masterrecord throws at migration time (rather than silently creating a non-filtered index that would enforce the wrong constraint). Enforce the invariant in the write path (a transactional clear-then-set) or use a generated column. |

where is a raw SQL predicate authored by you (like table/column names), not a place for user input.

Seed Data

Define seed data in your context file that automatically generates migration code using the ORM.

Context-Level Seed API (Recommended)

class AppContext extends context {
    onConfig() {
        // Single seed record
        this.dbset(User).seed({
            user_name: 'admin',
            first_name: 'System',
            last_name: 'Administrator',
            email: '[email protected]',
            system_role: 'system_admin',
            admin_type: 'engineering',
            onboarding_completed: 1,
            availability_status: 'online'
        });

        // Chain multiple records
        this.dbset(Post)
            .seed({ title: 'Welcome', content: 'Hello world', author_id: 1 })
            .seed({ title: 'Getting Started', content: 'Tutorial', author_id: 1 });

        // Bulk seed with array
        this.dbset(Category).seed([
            { name: 'Technology', slug: 'tech' },
            { name: 'Business', slug: 'biz' },
            { name: 'Science', slug: 'science' }
        ]);
    }
}

Automatic Migration Generation

When you define seed data on the context, add-migration compiles it into idempotent this.seed(...) calls in the generated migration:

// Your context definition triggers this migration
class Migration_20250205_123456 extends masterrecord.schema {
    async up(table) {
        await this.init(table);

        // Generated idempotent seed calls — INSERT OR IGNORE (SQLite) /
        // INSERT IGNORE (MySQL) / ON CONFLICT DO NOTHING (PostgreSQL)
        await this.seed('User', {
            user_name: 'admin',
            first_name: 'System',
            last_name: 'Administrator',
            email: '[email protected]',
            system_role: 'system_admin',
            admin_type: 'engineering',
            onboarding_completed: 1,
            availability_status: 'online'
        });

        await this.seed('Post', { title: 'Welcome', content: 'Hello world', author_id: 1 });
        await this.seed('Post', { title: 'Getting Started', content: 'Tutorial', author_id: 1 });
    }

    async down(table) {
        await this.init(table);
        // Seed data typically not removed in down migrations
    }
}

Benefits of context-level seed

Declarative: seed data lives with your context definition and is versioned alongside the schema.
Idempotent: the generated this.seed() calls use the engine's insert-or-ignore form, so re-running migrations never duplicates rows or errors on an existing key.
Environment-aware: scope seeds with .when(env), and use upsert / seedFactory for generated data.

Note: generated seeds are raw parameterized INSERTs (via this.seed()), so they do not run entity lifecycle hooks (beforeSave/afterSave) or validators. If you need those to run on seeded rows, insert them through context.Entity.new() + save() in application code instead.

Manual Seed Methods (Advanced)

For more control, use raw SQL seed methods directly in migrations:

class Migration_20250205_123456 extends masterrecord.schema {
    async up(table) {
        this.init(table);

        // Single record with raw SQL
        this.seed('User', {
            user_name: 'admin',
            email: '[email protected]'
        });

        // Bulk insert with raw SQL (more performant for large datasets)
        this.bulkSeed('Category', [
            { name: 'Technology', slug: 'tech' },
            { name: 'Business', slug: 'biz' },
            { name: 'Science', slug: 'science' }
        ]);
    }
}

When to use manual seed methods:

Large datasets (1000+ records) - bulkSeed() is more performant
Need raw SQL control
Don't need lifecycle hooks or validation

Idempotency

Both routes are idempotent — context-level seed compiles to this.seed(), and this.seed() emits the engine's insert-or-ignore form:

SQLite: INSERT OR IGNORE INTO
MySQL: INSERT IGNORE INTO
PostgreSQL: INSERT ... ON CONFLICT DO NOTHING

Re-running a migration never creates duplicates or throw

Published

Vulnerabilities

Links

Maintainers

Keywords

Readme

MasterRecord

Key Features

Database Support

Table of Contents

⚠️ Best Practices (CRITICAL)

1. Creating Entity Instances

2. Saving Changes - ALWAYS use await

3. Quick Reference Card

Installation

Dependencies

Two Patterns: Entity Framework & Active Record

Active Record Style (Recommended for beginners)

Entity Framework Style (Efficient for batch operations)

Quick Start

1. Create a Context

2. Define Entities

3. Run Migrations

4. Query Your Data

Database Configuration

PostgreSQL (Async)

MySQL (Async with Connection Pooling)

SQLite (Async API)

Environment Files

Entity Definitions

Basic Entity

Field Types

Relationships

Field Transformers

Querying

Basic Queries

Parameterized Queries

IN Clauses

Query Chaining

Ordering

Pagination

Counting

Complex Queries

Full-Text Search

Set up the index in a migration

Query

Migrations

CLI Commands

Migration File Structure

Migration Operations

Seed Data

Advanced Features

Type Validation

Field Transformers (Advanced)

Table Prefixes

Transactions (PostgreSQL)

Query Result Caching

How It Works

Basic Usage (Opt-In, Request-Scoped)

Configuration

Enable Caching for Specific Queries

Manual Cache Control

Redis-Based Distributed Caching (Production)

Cache Invalidation Strategy

Performance Impact

Best Practices

Monitoring Cache Performance

Request-Scoped Caching (Like Active Record)

Important: Shared Cache Behavior

Multi-Context Applications

Raw SQL Queries

API Reference

Context Methods

Query Methods

Entity Serialization

.toObject(options)

.toJSON()

Entity Instance Methods

.delete()

.reload()

2. Saving Changes - ALWAYS use `await`