Stabilize ORM

A Modern, Type-Safe, and Expressive ORM for Bun and Node.js

Stabilize is a lightweight, feature-rich ORM designed for performance and developer experience. It provides a unified, database-agnostic API for PostgreSQL, MySQL/MariaDB, SQLite, and SQL Server, plus a MongoDB document backend with the boundaries spelled out below. Powered by a robust query builder, programmatic model definitions, automatic versioning, and a full-featured command-line interface, Stabilize is built to scale with your app.

🚀 Features

• Unified API: Write once, run on PostgreSQL, MySQL/MariaDB, SQLite, or SQL Server.
• MongoDB Backend: DBType.MongoDB runs the same repositories, relations, hooks and structured query builder against a document store, through the optional mongodb driver. Raw SQL, joins, unions, CTEs and raw clauses are refused with a MONGO_UNSUPPORTED error rather than mistranslated; the boundaries are documented in full.
• Programmatic Model Definitions: Define models and columns using the defineModel API with the DataTypes enum for database-agnostic schemas.
• Full-Featured CLI: Generate models, manage migrations, seed data, and reset your database from the command line with stabilize-cli.
• Automatic Migrations: Generate database-specific SQL schemas directly from your model definitions.
• First-Class SQL Server Support: DBType.MSSQL selects the mssql v12 driver, with T-SQL-specific query generation (OUTPUT INSERTED.*, OFFSET … FETCH, MERGE for upserts) and schema generation via OBJECT_ID / sys.indexes probes. Placeholders are rewritten automatically — you keep writing ?.
• Versioned Models & Time-Travel: Enable versioning in your model configuration for automatic history tables and snapshot queries.
• Retry Logic: Automatic exponential backoff for database queries to handle transient connection issues.
• Connection Pooling: Efficient connection management for PostgreSQL, MySQL, and SQL Server, with poolStats() reporting borrowed/available/size on SQL Server.
• Transactional Integrity: Built-in support for atomic transactions with automatic rollback on failure.
• Advanced Query Builder: Fluent, chainable API for building complex queries, including joins, filters, ordering, and pagination.
• Pagination Helper: Easily paginate any query with .paginate(page, pageSize) and get { data, total, page, pageSize }.
• Advanced Model Validation: Enforce rules like required, minLength, maxLength, pattern, and custom validators—errors are thrown on invalid input.
• Model Relationships: Define OneToOne, ManyToOne, OneToMany, and ManyToMany relationships in the model configuration, eager-loaded with relations or .withRelations().
• Many-to-Many Link Management: attach(), detach() and sync() edit a join table directly, without loading either side.
• validateAll: Collect every validation failure at once, rather than only the first.
• findOrFail / firstOrFail: Throw a NOT_FOUND_ERROR instead of returning null.
• Raw Clause Builders: .orderByRaw(), .groupByRaw() and .havingRaw() for expressions that are not column names.
• Soft Deletes: Enable soft deletes in the model configuration for transparent "deleted" flags and safe row removal.
• Lifecycle Hooks: Define hooks in the model configuration or as class methods for lifecycle events like beforeCreate, afterUpdate, etc.
• Pluggable Logging: Includes a robust StabilizeLogger with support for file-based, rotating logs.
• Custom Errors: StabilizeError provides clear, consistent error handling.
• Caching Layer: Optional caching with cache-aside and write-through strategies — over Redis when redisUrl is set, and over StabilizeKV, the in-process store, when it is not. The in-process backend is a self-contained key-value store with TTL, metadata and cursors.
• Column Encryption: Mark a column with encrypted: true to transparently encrypt it on write and decrypt it on read with AES-256-GCM — including for rows served from cache. The key comes from ORM_ENCRYPTION_KEY or a key file, and each value names the key that wrote it, so keys can be rotated one at a time.
• Custom Query Scopes: Define reusable query conditions (scopes) in models for simplified, reusable filtering logic.
• Timestamps: Automatically manage createdAt and updatedAt columns for tracking record creation and update times.
• SQL Default Expressions: Support database-side default expressions (e.g., gen_random_uuid(), NOW()) for columns using the sqlDefault() helper.
• Nested Relations (Eager Loading): Load deeply nested relations using dot notation like "roles.permissions".
• AutoMigrate with Index Management: Create missing tables, add missing columns, and add missing indexes and unique constraints in one pass. It is additive only — it never drops a column, never changes a column type, and never removes an index.
• Advanced Query Builder Filters: Chainable .orWhere(), .whereIn(), .whereNotIn(), .whereNull(), .whereNotNull(), .whereBetween(), .groupBy(), .having(), .lock() methods.
• Optimistic Locking: Add optimisticLock: true to a version column to automatically detect concurrent modification conflicts and throw CONCURRENT_MODIFICATION errors.
• findAndCount: Get paginated results with a total count in one call.
• findOneBy / findBy: TypeORM-style conditional finders without writing raw SQL.
• Aggregate Queries: Run count(), sum(), avg(), min(), max() directly from the repository or query builder.
• Cursor-Based Pagination: Efficient forward/backward cursor pagination for large datasets (Prisma-style findMany).
• exists: Check if a record exists without loading it.
• recoverAll: Bulk restore all soft-deleted records.
• truncate: Clear all rows from a table.
• seed / defineSeed: Laravel-style seeding framework with defineSeed and runSeeds.
• resetDatabase: Drop tables, re-migrate, and optionally re-seed for development.
• healthCheck: Get database and table health status with latency for monitoring endpoints.
• rawQuery / rawExec: Execute raw SQL directly from the Stabilize instance.
• bulkUpsert: Upsert multiple records in a single transaction.
• findMany: Prisma-style query with where, cursor, take, skip, orderBy.
• countDistinct: Count unique values in a column.
• increment / decrement: Atomically increment or decrement a numeric field.
• pluck: Get an array of a single column's values (Rails-style).
• selectColumns: Get only specific columns from a query.
• toggle: Toggle a boolean field (Rails-style).
• updateBy / deleteBy: Conditional bulk updates and deletes without writing SQL.
• restoreBy: Restore soft-deleted records matching conditions.
• findDeleted / withTrashed: Query soft-deleted or all records.
• upsertMany: Batch upsert in configurable batch sizes.
• map / each / eachBatch: Transform, iterate, and batch-process query results.
• lockForUpdate: Pessimistic row locking for read-modify-write.
• firstOrCreate / updateOrCreate: Laravel-style find-or-create patterns.
• first / last / random: Single-record shortcuts.
• StabilizeEmitter: Event system for query, error, connection:open/close, transaction:start/complete/error.
• TransactionIsolationLevel: Type for READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
• generateUUID: Cross-runtime UUID generation helper.
• poolStats: Get connection pool statistics.
• Database Backup & Restore: db:backup and db:restore commands for database backup management.
• API Generation: generate:api command scaffolds full CRUD REST API routes from models.
• Fresh Migrations: migrate:fresh drops all tables and re-runs migrations without seeding.
• Database Size Analysis: db:size command shows table sizes and row count statistics.

📦 Installation

# Using Bun
bun add stabilize-orm

# Using npm
npm install stabilize-orm

🧩 Runtimes

Stabilize runs on both Bun and Node.js. The package is one build; the SQLite driver is selected at runtime, so nothing in your code changes between them.

| Runtime | Version | SQLite driver | Everything else | | --- | --- | --- | --- | | Bun | 1.3+ | bun:sqlite (built in) | ✅ | | Node.js | 22.13+ | node:sqlite (built in) | ✅ |

PostgreSQL, MySQL/MariaDB, SQL Server and MongoDB need no built-in driver: they run on either runtime through their own npm packages. SQLite on Node.js needs Node 22.13 or later, where node:sqlite is available without a flag (it was introduced in 22.5 behind one). Nothing has to be installed for it either way.

Integers past 2^53

SQLite stores 64-bit integers, which is more than a JavaScript number can hold exactly. On Node.js a value beyond Number.MAX_SAFE_INTEGER is returned as a bigint, so it comes back correct rather than rounded. Values within the safe range stay plain numbers on both runtimes, so ordinary id columns behave identically.

bun:sqlite has no equivalent escape hatch and returns such values as a lossy number. If you store integers that large, read them on Node.js, or keep them under 2^53.

📃 Documentation & Community

• Changelog
• License
• Code of Conduct
• Contributing Guide
• Security Policy
• Support
• Funding

Examples

• Blog - Blog with users, posts, comments, and versioning
• E-Commerce - Products, categories, orders with transactions
• REST API - Express.js REST API with pagination and optimistic locking
• SaaS - Multi-tenant SaaS with tenants, members, and scoped projects
• CMS - Content management with authors, categories, articles, and versioning
• Analytics - Event tracking with aggregations and metrics

⚙️ Configuration

Create a database configuration file.

// config/database.ts
import { DBType, type DBConfig } from "stabilize-orm";

const dbConfig: DBConfig = {
  type: DBType.Postgres,
  connectionString:
    process.env.DATABASE_URL || "postgres://user:password@localhost:5432/mydb",
  retryAttempts: 3,
  retryDelay: 1000,
};

export default dbConfig;

Next, create a central ORM instance for your application.

// db.ts
import {
  Stabilize,
  type CacheConfig,
  type LoggerConfig,
  LogLevel,
} from "stabilize-orm";
import dbConfig from "./database";

const cacheConfig: CacheConfig = {
  enabled: process.env.CACHE_ENABLED === "true",
  // Optional. Without it the cache runs in process — see Caching below.
  redisUrl: process.env.REDIS_URL,
  ttl: 60,
};

const loggerConfig: LoggerConfig = {
  level: LogLevel.Info,
  filePath: "logs/stabilize.log",
  maxFileSize: 5 * 1024 * 1024, // 5MB
  maxFiles: 3,
};

export const orm = new Stabilize(dbConfig, cacheConfig, loggerConfig);

Caching

CacheConfig.redisUrl is genuinely optional. With it, the cache is Redis — shared between every instance of your application. Without it, the cache runs in process, backed by StabilizeKV: an in-process key-value store with get/put/delete/list, expiration/expirationTtl, metadata and cursor pagination. Its API follows Cloudflare Workers KV, so code written against Workers KV works here unchanged.

import { Stabilize, StabilizeKV } from "stabilize-orm";

// Shared: any number of servers see the same cache.
const shared = new Stabilize(dbConfig, {
  enabled: true,
  ttl: 60,
  redisUrl: process.env.REDIS_URL,
});

// In process: no Redis to run, and the cache dies with the process. Right for
// a single-server app, a test suite and local development — wrong for a fleet,
// where each instance would hold its own copy and an invalidation on one would
// leave the others stale.
const local = new Stabilize(dbConfig, { enabled: true, ttl: 60, maxEntries: 5000 });

cache.get(), set(), invalidate(), invalidatePattern() (Redis-style globs — user:*, user:?, user:[0-9]), getStats() and disconnect() behave identically on both. Which store is live is reported rather than left to be guessed, so a misconfigured deployment is visible:

await orm.getCacheStats();
// { hits: 12, misses: 3, keys: 40, backend: "memory" }

await orm.healthCheck();
// { status: "healthy", database: "postgres", latencyMs: 1.2, cacheStatus: "in-memory" }

backend is "redis", "memory" or "disabled". "redis" reports the configuration, not the connection — ioredis connects lazily, and a failed round trip surfaces as cacheStatus: "unknown" from healthCheck().

StabilizeKV is exported on its own too, for a single-process app that wants a KV store with TTL, metadata and cursors without running one.

🍃 MongoDB

DBType.MongoDB selects a document backend rather than a fifth SQL dialect. Its driver is the only optional dependency in the package, so install it alongside:

bun add mongodb

// config/database.ts
import { DBType, type DBConfig } from "stabilize-orm";

const dbConfig: DBConfig = {
  type: DBType.MongoDB,
  connectionString: process.env.MONGO_URL || "mongodb://localhost:27017/mydb",
  // Optional. The fallback database for a URI that omits one from its path,
  // which is how a mongo URI is usually written in development.
  database: "mydb",
  // Optional. Passed verbatim to the driver's `MongoClient` — `tls`,
  // `authSource`, `maxPoolSize`, `retryWrites` and anything else the ORM has no
  // opinion about.
  mongoOptions: { maxPoolSize: 20 },
};

export default dbConfig;

Models, repositories, relations, hooks, versioning, soft deletes, validation, encryption, aggregates and transactions work as they do on SQL. A query is written with the query builder's structured methods — the ones that record a condition rather than SQL text:

const users = await orm
  .getRepository(User)
  .find()
  .whereEq("isActive", true)
  .whereIn("role", ["admin", "editor"])
  .orderBy("createdAt", "DESC")
  .withRelations("roles")
  .execute(orm.client);

healthCheck() pings the server. poolStats() returns { active: -1, idle: -1, total: -1 }: the driver's pool is internal and per-server, so there is no honest number to report and the sentinel says so rather than inventing one.

What MongoDB cannot do

A document store is not a SQL engine, and Stabilize refuses to guess where the two disagree. Every one of these is deliberate, and each is reported rather than silently mistranslated — a dropped join() would return the wrong rows with no error to notice.

• Raw SQL is refused. rawQuery(), rawExec(), query() and queryExec() throw a StabilizeError with code MONGO_UNSUPPORTED. Use the repository API or the query builder's structured methods instead.

  await orm.rawQuery("SELECT * FROM users WHERE age > ?", [25]);
  // StabilizeError: Raw SQL is not available on MongoDB. ...

• Joins, unions, CTEs and raw clauses throw. join(), innerJoin(), leftJoin(), rightJoin(), fullJoin(), crossJoin(), union(), unionAll(), with(), withRecursive(), whereRaw(), whereRef(), whereExists(), whereNotExists(), selectRaw(), orderByRaw(), groupByRaw(), having(), distinct() and the SQL-text forms of where()/orWhere()/whereNot() have no MongoDB equivalent. The throw happens when the query is executed, not when the clause is added, and it names every offending method at once:

  await repo
    .find()
    .innerJoin("posts", "posts.user_id = users.id")
    .whereRaw("LOWER(name) = 'ada'")
    .execute(orm.client);
  // StabilizeError: This query cannot be translated to MongoDB: innerJoin,
  // whereRaw have no MongoDB equivalent. ... Use withRelations() for related
  // documents, or run this query against a SQL backend.

  For a join, the replacement is withRelations(), which loads related documents with batched reads rather than one statement:

  await repo.find().withRelations("posts", "posts.comments").execute(orm.client);

• lock() / forUpdate() is a no-op. MongoDB has no row lock to map it onto, so the clause is not rendered and the query runs unlocked rather than failing. lockForUpdate() therefore reads the row without protecting it — use updateBy() with a condition, or an optimistic lock column, for a read-modify-write that has to be safe.

• DECIMAL is stored as a double. MongoDB has no exact decimal unless the caller supplies a Decimal128, so a DECIMAL column loses precision the way a binary float does. For money, store the smallest unit as an INTEGER/BIGINT or the value as a STRING.

• Auto-increment ids come from a counters collection — and they roll back. Ids are reserved by a $inc against stabilize_counters, keyed by collection name, rather than by the server. Because that reservation runs inside the same transaction as the write, an aborted transaction gives its ids back: the next insert re-uses them. MySQL behaves the opposite way — InnoDB's auto-increment counter is not transactional, so an aborted insert leaks the gap.

• Transactions need a replica set or a sharded cluster. A standalone mongod serves reads but rejects every transaction — and every repository write (create(), update(), delete(), bulkCreate(), upsert() …) runs inside one, so a standalone makes writes fail generally, not only explicitly-transactional code. The client warns at connect time and reports the failure as TX_ERROR:

  // Standalone mongod, no replica set:
  await repo.create({ name: "Ada" });
  // StabilizeError (TX_ERROR): MongoDB transactions require a replica set or
  // sharded cluster, and this server is a standalone. Every write goes through
  // a transaction, so start the server with --replSet and run rs.initiate()
  // (or point the connection at an existing replica set).

  Run a single-node replica set in development (rs.initiate() on a mongod --replSet rs0) and every write path works unchanged.

🏗️ Models & Relationships

Define your tables as classes using the defineModel function. The DataTypes enum ensures database-agnostic schemas.

Example: Users and Roles (Many-to-Many) with Versioning

// models/User.ts
import { defineModel, DataTypes, RelationType } from "stabilize-orm";
import { UserRole } from "./UserRole";

const User = defineModel({
  tableName: "users",
  versioned: true,
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    email: {
      type: DataTypes.STRING,
      length: 100,
      required: true,
      unique: true,
    },
  },
  relations: [
    {
      type: RelationType.OneToMany,
      target: () => UserRole,
      property: "roles",
      // The key lives on the target table, so the OneToMany side names it
      // with inverseKey. `foreignKey` is accepted as a synonym here.
      inverseKey: "userId",
    },
  ],
  hooks: {
    beforeCreate: (entity) => console.log(`Creating user: ${entity.email}`),
  },
});

export { User };

🔍 Pagination

The built-in pagination helper makes it easy to retrieve a page of rows using LIMIT/OFFSET semantics.

const users = await userRepository.find().paginate(2, 10).execute(dbClient);
// users = [...] // Returns an array of rows for that page

Or, use the query builder:

const users = await userRepository
  .find()
  .where('isActive = ?', true)
  .paginate(1, 20)
  .execute(dbClient);

🛡️ Advanced Validation

Models can define advanced validation rules for columns, including:

• required
• minLength / maxLength
• pattern (RegExp)
• customValidator (function)

Validation errors are thrown on create/update if data is invalid.

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    email: {
      type: DataTypes.STRING,
      required: true,
      unique: true,
      minLength: 6,
      pattern: /^[^@]+@[^@]+\.[^@]+$/,
      customValidator: (val) =>
        val.endsWith("@offbytesecure.com") ||
        "Must use an @offbytesecure.com email",
    },
    password: { type: DataTypes.STRING, minLength: 8 },
  },
});

🔐 Column Encryption

Mark a column with encrypted: true and the ORM encrypts it on the way in and decrypts it on the way out. Your code keeps reading and writing ordinary strings; the ciphertext is only ever visible in the database.

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    email: { type: DataTypes.STRING, required: true, unique: true },
    nationalId: { type: DataTypes.STRING, encrypted: true },
  },
});

await userRepository.create({
  email: "[email protected]",
  nationalId: "9001015800085", // stored as v3:<keyId>:<iv>:<tag>:<ciphertext>
});

const user = await userRepository.findOne(1);
console.log(user.nationalId); // "9001015800085" — decrypted on read

Encryption uses AES-256-GCM, so a value that has been tampered with or truncated fails to decrypt rather than quietly returning corrupted plaintext. Values carry a v3: prefix followed by a short id for the key that wrote them — see Rotating a key. Encrypted columns are also decrypted when a row is served from cache, so a cache hit returns the same shape as a miss.

A column can be encrypted and renamed. { name: "diagnosis_code", encrypted: true } stores ciphertext in diagnosis_code and, as with any renamed column, the value reads back under the column name.

The encryption key

The key is looked for in this order, and the first source that yields one wins:

| Source | | |---|---| | ORM_ENCRYPTION_KEY | 32 bytes, as 64 hex characters or as raw UTF-8 | | ORM_ENCRYPTION_KEY_FILE | a path, defaulting to .stabilize/encryption.key |

Both are read on every call, so either can be set after your modules are imported.

# Supply it yourself — the right choice wherever the filesystem is ephemeral.
export ORM_ENCRYPTION_KEY="$(openssl rand -hex 32)"

If neither is present, a key is generated and written to the key file with mode 0600, and a warning is emitted naming the path. That is safe only because the key is stored before it is used: a key generated in memory and never written down would orphan every encrypted value at the next restart, leaving ciphertext in the database that nothing can ever read. If the file cannot be created — a read-only filesystem, a directory the process may not write — the call throws rather than inventing a key it cannot keep.

There is deliberately no built-in key. Earlier versions fell back to a constant compiled into the package, so a deployment that never set the variable encrypted its columns with a value anyone who read the published source could reproduce. Those rows are still readable; see Rotating a key.

The generated key file has to survive a redeploy. On a host with an ephemeral filesystem it will not, and the redeploy that discards it is the one that loses the data. Set ORM_ENCRYPTION_KEY there. Add .stabilize/ to your .gitignore — committing the key hands over every encrypted column.

Rotating a key

Because each value names the key that wrote it, a new key can be introduced without re-encrypting everything at once. Retired keys keep decrypting; only the active key encrypts.

// .stabilize/encryption.key
{
  "active": "<the new 64-hex key>",
  "retired": ["<the previous 64-hex key>"]
}

Retired keys can also come from ORM_ENCRYPTION_KEYS_OLD, comma-separated, for a deployment that keeps its secrets in the environment and has no file:

export ORM_ENCRYPTION_KEY="<new key>"
export ORM_ENCRYPTION_KEYS_OLD="<previous key>"

A row still encrypted under a retired key reads normally. Re-save it to move it to the active key, then drop the retired key once nothing refers to it — a value whose key is missing fails with an error naming the id, so you can tell which key is still in use.

activeKeyId() returns the id new values are being written with, for checking that a rotation has finished.

Data written by earlier versions

Rows written before key ids existed carry a v2: prefix, and rows written before that used unauthenticated AES-CBC with an iv:ciphertext shape. Neither names a key, so both are tried against every key in the ring. If you have data from a version that used the built-in constant, put f71a3c8e9b12d5a49c0a3f98b1f2e46d in retired, read those rows, and re-save them under a key of your own.

⏳ Versioning & Auditing

⏳ Versioning & Auditing

Enable automatic history tracking and time-travel queries by setting versioned: true in your model configuration.

• Each change is recorded in a <table>_history table with version, operation, and audit columns.
• Supports snapshot queries, rollbacks, audits, and time-travel.

Versioning Example

import { defineModel, DataTypes } from "stabilize-orm";

const User = defineModel({
  tableName: "users",
  versioned: true,
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    name: { type: DataTypes.STRING, length: 100 },
  },
});

// --- Using versioning features:

const userRepository = orm.getRepository(User);

// Rollback to a previous version
await userRepository.rollback(1, 3); // roll back user with id=1 to version 3

// Get a snapshot as of a specific date
const userAsOf = await userRepository.asOf(1, new Date("2025-01-01T00:00:00Z"));
console.log(userAsOf);

// View full version history
const history = await userRepository.history(1);
console.log(history);

🔄 Model Lifecycle Hooks

Stabilize ORM supports lifecycle hooks defined in the model configuration or as class methods. You can run logic before/after create, update, delete, or save.

Hooks Example

import { defineModel, DataTypes } from "stabilize-orm";

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    name: { type: DataTypes.STRING, length: 100 },
    createdAt: { type: DataTypes.DATETIME },
    updatedAt: { type: DataTypes.DATETIME },
  },
  hooks: {
    beforeCreate: (entity) => {
      entity.createdAt = new Date();
    },
    beforeUpdate: (entity) => {
      entity.updatedAt = new Date();
    },
    afterCreate: (entity) => {
      console.log(`User created: ${entity.name}`);
    },
  },
});

// Add a hook as a class method
User.prototype.afterUpdate = async function () {
  console.log(`Updated user: ${this.name}`);
};

export { User };

Supported hooks: beforeCreate, afterCreate, beforeUpdate, afterUpdate, beforeDelete, afterDelete, beforeSave, afterSave.

💻 Command-Line Interface (CLI)

Stabilize includes a powerful CLI with 31 commands. See: stabilize-cli on GitHub

Generate

stabilize-cli generate:model User name:string email:string age:int --versioned  # g:m
stabilize-cli generate:migration User                                           # g:mg
stabilize-cli generate:seed User --count 10                                     # g:s
stabilize-cli generate:api Product --prefix /v1                                # g:a
stabilize-cli generate:all Order userId:string total:decimal --count 20         # g:x
stabilize-cli generate:test User                                               # g:t

Migrate

stabilize-cli migrate
stabilize-cli migrate:rollback
stabilize-cli migrate:fresh --force
stabilize-cli migrate:status
stabilize-cli migrate:pending
stabilize-cli migrate:auto

migrate:auto runs AutoMigrate against the live database — creating missing tables, adding missing columns and adding missing indexes. Like the library API it is additive only: it never drops a column and never changes a column type.

Database

stabilize-cli db:drop --force
stabilize-cli db:reset --force
stabilize-cli db:truncate users --force
stabilize-cli db:backup --output ./backups
stabilize-cli db:restore backups/backup.db --force
stabilize-cli db:tables
stabilize-cli db:size
stabilize-cli db:diff
stabilize-cli db:console
stabilize-cli db:table:info users

Model & Config

stabilize-cli model:validate
stabilize-cli model:info User
stabilize-cli config:init --type postgres

Diagnostics

stabilize-cli seed
stabilize-cli status
stabilize-cli health
stabilize-cli health:json
stabilize-cli query 'SELECT * FROM users LIMIT 5'
stabilize-cli info

🧑‍💻 Querying Data

Basic CRUD with Repositories

import { orm } from "./db";
import { User } from "./models/User";

const userRepository = orm.getRepository(User);

const newUser = await userRepository.create({ email: "[email protected]" });
const foundUser = await userRepository.findOne(newUser.id);
const updatedUser = await userRepository.update(newUser.id, {
  email: "[email protected]",
});
await userRepository.delete(newUser.id);

Advanced Queries with the Query Builder

const activeAdmins = await orm
  .getRepository(UserRole)
  .find()
  .join("users", "user_roles.user_id = users.id")
  .join("roles", "user_roles.role_id = roles.id")
  .select("users.id", "users.email", "roles.name as role_name")
  .where("roles.name = ?", "Admin")
  .orderBy("users.email ASC")
  .execute();

console.log(activeAdmins);

Query Builder API

{
  select(...fields: string[]): QueryBuilder<User>;
  where(condition: string, ...params: any[]): QueryBuilder<User>;
  orWhere(condition: string, ...params: any[]): QueryBuilder<User>;
  whereIn(column: string, values: any[]): QueryBuilder<User>;
  whereNotIn(column: string, values: any[]): QueryBuilder<User>;
  whereNull(column: string): QueryBuilder<User>;
  whereNotNull(column: string): QueryBuilder<User>;
  whereBetween(column: string, start: any, end: any): QueryBuilder<User>;
  groupBy(clause: string): QueryBuilder<User>;
  having(condition: string, ...params: any[]): QueryBuilder<User>;
  join(table: string, condition: string): QueryBuilder<User>;
  orderBy(clause: string): QueryBuilder<User>;
  limit(limit: number): QueryBuilder<User>;
  offset(offset: number): QueryBuilder<User>;
  lock(mode?: "FOR UPDATE" | "FOR SHARE"): QueryBuilder<User>;
  withRelations(...relations: string[]): QueryBuilder<User>;
  scope(name: string, ...args: any[]): QueryBuilder<User>;
  paginate(page: number, pageSize: number): QueryBuilder<User>;
  build(): { query: string; params: any[] };
  execute(client?: DBClient, cache?: Cache, cacheKey?: string): Promise<User[]>;
}

Custom Query Scopes

Define reusable query conditions (scopes) in your model configuration to simplify and reuse common filtering logic. Scopes are applied via the scope method on Repository or QueryBuilder, allowing you to chain them with other query operations.

Scopes Example

import { defineModel, DataTypes } from "stabilize-orm";
import { orm } from "./db";

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    email: { type: DataTypes.STRING, length: 100, required: true },
    isActive: { type: DataTypes.BOOLEAN, required: true },
    createdAt: { type: DataTypes.DATETIME },
    updatedAt: { type: DataTypes.DATETIME },
  },
  scopes: {
    active: (qb) => qb.where("isActive = ?", true),
    recent: (qb, days: number) =>
      qb.where(
        "createdAt >= ?",
        new Date(Date.now() - days * 24 * 60 * 60 * 1000),
      ),
  },
});

const userRepository = orm.getRepository(User);

// Fetch active users
const activeUsers = await userRepository.scope("active").execute();

// Fetch users created in the last 7 days
const recentUsers = await userRepository.scope("recent", 7).execute();

// Combine scopes with other query operations
const recentActiveUsers = await userRepository
  .scope("active")
  .scope("recent", 7)
  .orderBy("createdAt DESC")
  .limit(10)
  .execute();

console.log(recentActiveUsers);

Timestamps

Enable automatic management of createdAt and updatedAt columns by setting timestamps in your model configuration. The ORM automatically sets these fields during create, update, bulkCreate, bulkUpdate, and upsert operations in a TypeScript-safe manner, eliminating the need for manual hooks.

Timestamps Example

import { defineModel, DataTypes } from "stabilize-orm";
import { orm } from "./db";

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    email: { type: DataTypes.STRING, length: 100, required: true },
    createdAt: { type: DataTypes.DATETIME },
    updatedAt: { type: DataTypes.DATETIME },
  },
  timestamps: {
    createdAt: "createdAt",
    updatedAt: "updatedAt",
  },
});

const userRepository = orm.getRepository(User);

// Create a user (createdAt and updatedAt set automatically)
const newUser = await userRepository.create({ email: "[email protected]" });
console.log(newUser.createdAt, newUser.updatedAt); // Outputs current timestamp

// Update a user (updatedAt updated automatically)
const updatedUser = await userRepository.update(newUser.id, {
  email: "[email protected]",
});
console.log(updatedUser.updatedAt); // Outputs new timestamp

// Bulk create users
const newUsers = await userRepository.bulkCreate([
  { email: "[email protected]" },
  { email: "[email protected]" },
]);
console.log(newUsers.map((u) => u.createdAt)); // Outputs timestamps for each user

🗑️ Soft Deletes

Enable soft deletes by setting softDelete: true and marking a column (e.g., deletedAt) with softDelete: true in the model configuration.

• Use repository.delete(id) to mark an entity as deleted.
• Use repository.recover(id) to restore a soft-deleted entity.
• Queries automatically exclude soft-deleted rows unless specified otherwise.

Soft Delete Example

import { defineModel, DataTypes } from "stabilize-orm";

const User = defineModel({
  tableName: "users",
  softDelete: true,
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    email: { type: DataTypes.STRING, length: 100, required: true },
    deletedAt: { type: DataTypes.DATETIME, softDelete: true },
  },
});

const userRepository = orm.getRepository(User);
await userRepository.create({ email: "[email protected]" });
await userRepository.delete(1); // Soft delete
await userRepository.recover(1); // Recover

🌐 Express.js Integration

Stabilize ORM works seamlessly with web frameworks like Express.

import express from "express";
import { orm } from "./db";
import { User } from "./models/User";

const app = express();
app.use(express.json());

const userRepository = orm.getRepository(User);

app.get("/users", async (req, res) => {
  try {
    const users = await userRepository.find().execute();
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: "Failed to fetch users." });
  }
});

app.post("/users", async (req, res) => {
  try {
    const user = await userRepository.create(req.body);
    res.status(201).json(user);
  } catch (err) {
    res.status(500).json({ error: "User creation failed." });
  }
});

app.listen(3000, () => {
  console.log("Server listening on port 3000");
});

🧑‍🔬 Testing & Time-Travel

• Use time-travel queries to inspect historical entity states.
• Assert audit trails and rollback operations in your tests.

🔒 Optimistic Locking

Enable optimistic locking to detect concurrent modifications. Add optimisticLock: true to a version column in your model.

import { defineModel, DataTypes } from "stabilize-orm";

const User = defineModel({
  tableName: "users",
  columns: {
    id: { type: DataTypes.INTEGER, required: true },
    name: { type: DataTypes.STRING },
    version: { type: DataTypes.INTEGER, optimisticLock: true },
  },
});

const userRepository = orm.getRepository(User);

// Create with initial version
const user = await userRepository.create({ name: "Lwazi", version: 1 });

// Update - version is automatically incremented
// If another transaction modified the record, a CONCURRENT_MODIFICATION error is thrown
try {
  await userRepository.update(user.id, {
    name: "Updated",
    version: user.version,
  });
} catch (err) {
  if (err.code === "CONCURRENT_MODIFICATION") {
    console.log("Record was modified by another transaction");
  }
}

⏱️ SQL Default Expressions

Use sqlDefault() to set database-side default values for columns (e.g., gen_random_uuid(), NOW()).

import { defineModel, DataTypes, sqlDefault } from "stabilize-orm";

const User = defineModel({
  tableName: "users",
  columns: {
    id: {
      type: DataTypes.UUID,
      required: true,
      defaultExpression: sqlDefault("gen_random_uuid()"),
    },
    name: { type: DataTypes.STRING },
    createdAt: {
      type: DataTypes.DATETIME,
      defaultExpression: sqlDefault("NOW()"),
    },
  },
});

📊 Advanced Query Builder

The query builder now supports additional filter methods:

const results = await userRepository
  .find()
  .where("status = ?", "active")
  .orWhere("role = ?", "admin")
  .whereIn("age", [25, 30, 35])
  .whereBetween("createdAt", new Date("2025-01-01"), new Date("2025-12-31"))
  .whereNull("deletedAt")
  .groupBy("department")
  .having("COUNT(*) > ?", 5)
  .orderBy("createdAt DESC")
  .limit(10)
  .execute();

Raw Clauses

orderBy, groupBy and having take a column name. When you need an expression instead, use the raw variants:

const results = await orderRepository
  .find()
  .select("status", "COUNT(*) AS total")
  .groupByRaw("strftime('%Y-%m', createdAt)")
  .havingRaw("COUNT(*) > ?", 10)
  .orderByRaw("CASE WHEN status = 'urgent' THEN 0 ELSE 1 END")
  .execute(db.client);

orderByRaw takes an optional direction as its second argument (orderByRaw("LENGTH(title)", "DESC")). Raw and plain clauses compose, and raw parameters are bound in the order they appear.

🔗 Relations

Relations are eager-loaded, one batched query per relation. A to-many relation comes back as an array (empty when there is nothing linked), a to-one relation as the row or null.

const user = await userRepository.findOne(1, {
  relations: ["roles", "roles.permissions"],
});
// user.roles[0].permissions — nested paths use dot notation

Relations can also be requested on the query builder, alongside where, limit, orderBy and paginate:

const users = await userRepository
  .find()
  .where("isActive = ?", true)
  .withRelations("roles", "roles.permissions")
  .limit(10)
  .execute(db.client);

create, bulkCreate, findOne, findMany, findBy, findOneBy and findAndCount all accept a relations option:

const [user, post] = await userRepository.bulkCreate(
  [{ email: "[email protected]" }, { email: "[email protected]" }],
  { relations: ["roles"] },
);

Related rows are read through the target model, so its soft-delete filter applies — a deleted child is not returned as part of its parent.

Managing Many-to-Many Links

attach, detach and sync write the join table directly, so a many-to-many relation can be edited without loading and re-saving either side.

await postRepository.attach(postId, "tags", [1, 2]); // links 1 and 2
await postRepository.detach(postId, "tags", [2]);    // unlinks 2
await postRepository.detach(postId, "tags");         // unlinks everything

// Makes the link set exactly [3, 4]: adds what is missing, removes what is not
// in the list, leaves the rest alone.
const { attached, detached } = await postRepository.sync(postId, "tags", [3, 4]);

All three are idempotent, accept a single id or an array, and return how many links they changed. sync runs in a transaction.

🥇 findOrFail / firstOrFail

findOne and first return null on a miss. The *OrFail variants throw a StabilizeError with code NOT_FOUND_ERROR instead, so a miss cannot be mistaken for an empty result.

const user = await userRepository.findOrFail(1);            // never null
const admin = await userRepository.firstOrFail({ role: "admin" });

// Still loads relations
const post = await postRepository.findOrFail(7, { relations: ["author"] });

🛡️ validateAll

create and update validate and throw on the first failure. When validating input from a form you usually want every failure at once:

const errors = userRepository.validateAll({ email: "nope", name: "ab" });
// ["Field email does not match pattern", "Field name too short"]

if (errors.length) {
  return res.status(422).json({ errors });
}

Pass true as the second argument to skip the required rules, which is how an update validates a partial entity.

📊 Aggregation Queries

Run aggregate queries directly on the repository or query builder.

// Repository-level aggregates
const total = await userRepository.count();
const exists = await userRepository.exists({
  email: "[email protected]",
});

const stats = await userRepository.aggregate({
  count: "*",
  sum: ["salary"],
  avg: ["salary"],
  min: ["salary"],
  max: ["salary"],
});
// stats = { count_: 100, sum_salary: 5000000, avg_salary: 50000, min_salary: 20000, max_salary: 150000 }

🔎 findOneBy / findBy

TypeORM-style conditional finders without writing raw SQL.

// Find one record by condition
const user = await userRepository.findOneBy({ email: "[email protected]" });

// Find multiple records
const admins = await userRepository.findBy(
  { role: "admin" },
  { limit: 10, orderBy: "createdAt DESC" },
);

// Combined with relations
const user = await userRepository.findOneBy(
  { email: "[email protected]" },
  { relations: ["roles"] },
);

🔢 findAndCount

Get paginated results with a total count in a single call.

const { data, total } = await userRepository.findAndCount();
console.log(`Showing ${data.length} of ${total} total records`);

🖱️ Cursor-Based Pagination

Efficient cursor-based pagination for large datasets.

// First page
const page1 = await userRepository.findMany({
  take: 10,
  orderBy: { field: "id", direction: "ASC" },
});

// Next page using cursor
const lastId = page1[page1.length - 1].id;
const page2 = await userRepository.findMany({
  cursor: { field: "id", value: lastId, direction: "forward" },
  take: 10,
  orderBy: { field: "id", direction: "ASC" },
});

🌱 Database Seeding

Define and run seeds for populating development/test databases.

import { defineSeed, runSeeds, resetDatabase } from "stabilize-orm";

// Define a seed
defineSeed("create-default-roles", async (db) => {
  const roleRepo = orm.getRepository(Role);
  await roleRepo.bulkCreate([
    { name: "Admin", permissions: "all" },
    { name: "User", permissions: "read" },
  ]);
});

// Run all seeds
await runSeeds(orm.client);

// Reset database (drop, migrate, seed)
await resetDatabase(orm.client, [User, Role]);

🏥 Health Check

Monitor database and cache connectivity with latency.

const health = await orm.healthCheck();
// { status: "healthy", database: "postgres", latencyMs: 12.5, cacheStatus: "connected" }

// Per-table health
const userHealth = await userRepository.healthCheck();
// { status: "healthy", table: "users", rows: 142, latencyMs: 8.3 }

📂 Bulk Upsert

Upsert multiple records in a single transaction.

const users = await userRepository.bulkUpsert(
  [
    { email: "[email protected]", name: "Lwazi" },
    { email: "[email protected]", name: "Ciniso" },
  ],
  ["email"], // unique key(s)
);

🔧 Raw SQL

Execute raw SQL queries directly from the ORM instance.

const results = await orm.rawQuery("SELECT * FROM users WHERE age > ?", [25]);
const { affectedRows } = await orm.rawExec(
  "UPDATE users SET active = false WHERE last_login < ?",
  [oneYearAgo],
);

🗑️ recoverAll / truncate

// Restore all soft-deleted records
const recovered = await userRepository.recoverAll();
console.log(`Recovered ${recovered} records`);

// Clear the table
await userRepository.truncate();

⬆️⬇️ Increment / Decrement

Atomically update numeric fields without loading the record.

const updated = await userRepository.increment(user.id, "loginCount", 1);
const updated = await userRepository.decrement(user.id, "credits", 5);

🏷️ Pluck / SelectColumns

// Get just the email column as an array
const emails = await userRepository.pluck("email");
// ["[email protected]", "[email protected]", ...]

// Get specific columns
const users = await userRepository.selectColumns("id", "email");
// [{ id: 1, email: "[email protected]" }, ...]

🔄 Toggle

Toggle a boolean field.

const toggled = await userRepository.toggle(user.id, "isActive");
// isActive was true, now false (or vice versa)

✏️ updateBy / deleteBy

// Update all active users' role to "member"
const updated = await userRepository.updateBy(
  { isActive: true },
  { role: "member" },
);

// Delete all users with null email
const deleted = await userRepository.deleteBy({ email: null });

🕳️ findDeleted / withTrashed

// Get only soft-deleted records
const deletedUsers = await userRepository.findDeleted().execute();

// Get all records including soft-deleted
const allUsers = await userRepository.withTrashed().execute();

// Restore matching soft-deleted records
const restored = await userRepository.restoreBy({ role: "admin" });

🔄 firstOrCreate / updateOrCreate

// Find or create in one call
const user = await userRepository.firstOrCreate(
  { email: "[email protected]" },
  { name: "Lwazi" },
);

// Find and update, or create if not found
const user = await userRepository.updateOrCreate(
  { email: "[email protected]" },
  { name: "Updated Name" },
);

🥇 first / last / random

const firstUser = await userRepository.first();
const admin = await userRepository.first({ role: "admin" });
const lastUser = await userRepository.last();
const randomUser = await userRepository.random();

🔒 Pessimistic Locking (lockForUpdate)

const user = await userRepository.lockForUpdate(user.id);
// The row is now locked for the duration of the transaction

📡 Event Emitter

Subscribe to ORM lifecycle events. All nine are emitted:

const orm = new Stabilize(dbConfig);

orm.events.on("query", ({ dbType, query, params, executionTime }) => {
  console.log(`[${dbType}] ${executionTime}ms ${query}`);
});

orm.events.on("error", ({ phase, error, attempt, attempts }) => {
  console.error(`ORM error during ${phase} (attempt ${attempt}/${attempts}):`, error);
});

orm.events.on("transaction:start", ({ dbType }) => console.log("begin", dbType));
orm.events.on("transaction:complete", ({ dbType }) => console.log("commit", dbType));
orm.events.on("transaction:error", ({ dbType, error }) => console.error("rollback", error));

orm.events.on("migration:start", ({ name, index, total }) => {
  console.log(`migrating ${index + 1}/${total}: ${name}`);
});
orm.events.on("migration:complete", ({ name }) => console.log(`applied ${name}`));

orm.events.on("connection:close", () => console.log("disconnected"));

| Event | Payload | | --- | --- | | query | { dbType, query, params, executionTime } — every statement, including those run inside a transaction | | error | { dbType, phase, error, attempt, attempts, ... } — phase is "query" or "migration"; a retried statement reports each attempt | | migration:start / migration:complete | { dbType, name, index, total } — per migration from migrate(), per table from autoMigrate() | | transaction:start / transaction:complete | { dbType } | | transaction:error | { dbType, error } | | connection:open | the DBType | | connection:close | none |

connection:open fires from the constructor, before a handler registered afterwards could exist. To catch it, build the emitter first and pass it in:

import { Stabilize, StabilizeEmitter } from "stabilize-orm";

const events = new StabilizeEmitter();
events.on("connection:open", (dbType) => console.log(`Connected to ${dbType}`));

const orm = new Stabilize(dbConfig, { enabled: false, ttl: 60 }, {}, undefined, events);

A handler that throws is swallowed, so one failing subscriber cannot break the query that raised the event.

📦 Pool Stats

const stats = await orm.poolStats();
// { active: 5, idle: 10, total: 15 }

🆔 generateUUID

import { generateUUID } from "stabilize-orm";

const id = generateUUID();
// "550e8400-e29b-41d4-a716-446655440000"

📑 License

Licensed under the MIT License. See LICENSE for details.

Created with ❤️ by ElectronSz File last updated: 2026-09-15