Skip to content

configuration.md

Latest commit

 

History

History
729 lines (553 loc) · 28.2 KB

configuration.md

File metadata and controls

729 lines (553 loc) · 28.2 KB
Raw

Configuration

ORM is configured through a single OrmConfig object — typically exported from orm.config.ts at your project root. The same file is read by the CLI (bunx orm migrate, etc.) and by the configureOrm() runtime facade your application calls at startup.

The minimum config

// orm.config.ts
export default {
  connection: { url: "sqlite://app.db" },
  migrationsPath: "./database/migrations",
};

That is enough for a single-database app with file-based migrations. Everything else is optional.

A representative config

// orm.config.ts
import type { OrmConfig } from "@rekkr/orm";

const config: OrmConfig = {
  connection: {
    url: process.env.DATABASE_URL!,
  },

  // Migrations
  migrations: {
    landlord: "./database/landlord-migrations",
    tenant: "./database/tenant-migrations",
    createIfMissing: { database: true, schema: true },
  },

  // Seeders
  seedersPath: "./database/seeders",

  // Tenancy
  tenancy: {
    resolveTenant: async (tenantId) => ({
      strategy: "schema",
      name: `tenant:${tenantId}`,
      schema: `tenant_${tenantId}`,
      mode: "qualify",
    }),
    listTenants: async () => ["acme", "globex", "initech"],
    idleTimeoutMs: 300_000,
    sweep: true,
  },

  // Models (used by type generation and the REPL)
  modelsPath: {
    landlord: "./src/models/landlord",
    tenant: "./src/models/tenant",
  },

  // Type generation overrides
  typeDeclarationImportPrefix: "$models",
  typeDeclarationSingularModels: true,

  // Queue
  queue: {
    defaultQueue: "default",
    workers: 2,
    jobsPath: "./app/jobs",
  },

  // Transactions
  transactions: {
    abandonedTimeoutMs: 60_000,
  },

  // Diagnostics
  log: false,
};

export default config;

connection

Required. A single database accepts either a URL or driver settings. For read replicas, use read/write URLs.

Read replicas

connection: {
  write: process.env.DATABASE_WRITE_URL!,
  read: [process.env.DATABASE_READ_1_URL!, process.env.DATABASE_READ_2_URL!],
  sticky: true,
}

Application SELECT queries rotate across the read URLs. Writes, locking reads, schema and migration operations, and every query inside a transaction use write. The URLs must use the same database driver and refer to databases with the same schema. Replication lag is possible when sticky is off.

For read-after-write consistency, wrap each request or job in DB.scope(). With sticky: true, a successful write keeps subsequent reads in that scope on write. Nested calls share the scope; concurrent scopes do not. Outside DB.scope(), reads continue to use replicas after a write because the ORM cannot infer a request boundary.

await DB.scope(async () => {
  await User.create({ name: "Ada" });
  const user = await User.where("name", "Ada").first(); // reads from write
});

DB.raw() and Connection.query() route a single plain SELECT or read-only WITH query to a replica. Other SQL, including locking queries and multiple statements, runs on the primary. Connection.queryPrimary() always uses the primary for operational reads that must see the latest state.

URL form

connection: { url: "postgres://user:pass@localhost:5432/mydb" }
connection: { url: "mysql://user:pass@localhost:3306/mydb" }
connection: { url: "sqlite://./app.db" }
connection: { url: "sqlite://:memory:" }

For MySQL and PostgreSQL, bigint: true asks the driver to decode large integer values as bigint. MySQL can still return safe values as number; PostgreSQL returns its BIGINT values as bigint. On Node.js the setting also reaches SQLite, whose integers past Number.MAX_SAFE_INTEGER come back as bigint instead of as exact strings. Leave it off when serializing rows directly to JSON, because JavaScript's native JSON.stringify does not accept bigint:

connection: {
  url: "mysql://user:pass@localhost:3306/mydb",
  bigint: true,
}

MySQL sessions run in UTC

On MySQL, ORM passes model dates to the driver as native Date bindings, which it encodes as a UTC wall clock whatever the process time zone — bun:sql natively, mysql2 because ORM configures it to. DATETIME stores that wall clock directly, while TIMESTAMP interprets it in the session's time zone: a session in another zone would store a different instant, and read every TIMESTAMP back shifted by its offset, without an error.

So every pooled connection runs SET time_zone = '+00:00' as it opens, before its first statement, whatever the server's default. bun:sql does it itself (Bun 1.4.1 and later), and ORM's mysql2 adapter does the same on Node.js. There is nothing to configure, even on a server whose default you cannot change, and it costs one statement per physical connection, not per query. A proxy that rejects SET time_zone, or does not keep it on the session, is not supported.

A SET time_zone the application runs afterwards still changes the one physical connection that executes it. ORM checks the session before every statement carrying a native Date binding that runs directly on a pool, because consecutive statements may use different sessions, and refuses to write the date to a session that is no longer UTC. Inside connection.transaction(...) the session is pinned and the successful check is reused for the rest of that transaction. Group related date writes in a short transaction when the extra round trip matters; max: 1 alone does not suppress the check because that physical connection can still be replaced after a disconnect.

To see what a session is using (offset_seconds must be 0):

SELECT
  @@session.time_zone AS time_zone,
  TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), NOW()) AS offset_seconds;

SQLite connections apply production-friendly defaults before the first query:

PRAGMA foreign_keys=ON;
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

Override or disable them only when your deployment needs a different SQLite mode:

connection: {
  url: "sqlite://./app.db",
  sqlitePragmas: { journalMode: "DELETE", synchronous: "FULL", foreignKeys: true },
}

connection: {
  url: "sqlite://./app.db",
  sqlitePragmas: false,
}

foreignKeys defaults to true, so SQLite enforces declared foreign keys like MySQL and PostgreSQL. Set it to false only when the application intentionally manages referential integrity outside SQLite.

You can also pin a Postgres schema and tune the connection pool from the URL form:

connection: {
  url: "postgres://localhost/mydb",
  schema: "app",   // default search_path / qualifier
  max: 20,         // pool size
  prepare: false,  // opt out of named statements on Node; default on Bun
}

Driver form

Useful when secrets come from multiple env vars:

connection: {
  driver: "postgres",
  host: "localhost",
  port: 5432,
  database: "mydb",
  username: "app",
  password: process.env.DB_PASSWORD!,
  max: 20,
  prepare: false,
}

SQLite uses filename instead of host/port:

connection: { driver: "sqlite", filename: "./app.db" }

ORM forwards the driver config to the driver as-is — bun:sql on Bun, pg or mysql2 on Node.js — and does not substitute defaults of its own. Every field you omit is therefore resolved from the adapter's standard environment variables — PGHOST, PGPORT, PGDATABASE, PGUSER, PGPASSWORD for Postgres, and MYSQL_HOST, MYSQL_PORT, MYSQL_DATABASE, MYSQL_USER, MYSQL_PASSWORD for MySQL — falling back to localhost and the adapter's default port when the variable is unset. So { driver: "postgres" } in an environment with PGHOST set connects to that host, not to localhost. Pass the field explicitly whenever you need it to win over the environment:

connection: { driver: "postgres", host: "localhost", database: "mydb" }

Credentials in this form are handed to the driver verbatim instead of being assembled into a URL, so usernames and passwords containing /, ?, #, @ or % need no escaping. The url form is parsed as a URL and still requires percent-encoded credentials.

For MySQL, tls: "require" encrypts the connection without verifying the server certificate, as ?ssl-mode=require does in the URL form. MySQL 8's default caching_sha2_password authentication needs it on a server that has not yet cached the user's password, which otherwise refuses the connection with ERR_MYSQL_PUBLIC_KEY_RETRIEVAL_NOT_ALLOWED:

connection: { driver: "mysql", host: "db.internal", database: "mydb", username: "app", password: process.env.DB_PASSWORD!, tls: "require" }

For PostgreSQL, prepare defaults to true on Node.js and false on Bun. Node.js requires pg 8.21.0 or newer and names up to 1000 statements through it; if a schema change invalidates a statement's result shape, the adapter gives it a fresh name and retries once outside a transaction. Inside a transaction PostgreSQL aborts that transaction, but the next one uses the fresh name. Bun's bun:sql cannot recover a stale named plan yet, so it runs statements unnamed by default. Set prepare: false on Node.js if your pooler does not support prepared statements. For MySQL, Node.js ignores prepare: mysql2 prepares every statement that carries bindings.

For PostgreSQL, the pool max defaults to 10 when unset (Connection.defaultPostgresPoolMax). Override per-connection with max, or globally before constructing connections:

import { Connection } from "@rekkr/orm";
Connection.defaultPostgresPoolMax = 20;

Unique constraint errors

Duplicate unique values and primary keys are normalized across SQLite, MySQL, and PostgreSQL, including deferred constraints that PostgreSQL detects only when a transaction commits:

import { UniqueConstraintViolationError } from "@rekkr/orm";

try {
  await User.create({ email: "already-taken@example.test" });
} catch (error) {
  if (error instanceof UniqueConstraintViolationError) {
    // Return a conflict response, report a validation issue, etc.
  }
}

The public message is stable and intentionally contains no SQL, bindings, table, column, or constraint names. The original driver error is available as error.cause for trusted server-side diagnostics. Treat that cause as sensitive: do not serialize it into an HTTP response or expose it to clients.

Only duplicate unique and primary-key violations use this error. NOT NULL, CHECK, foreign-key, connection, and syntax failures remain the original driver errors. insertOrIgnore() continues to suppress duplicate conflicts as requested instead of throwing.

Multi-tenant connection budget

Each distinct connection opens its own pool of up to max sockets. Postgres max_connections defaults to 100, so the ceiling is roughly:

total sockets ≈ (number of distinct connections) × max

Strategy implications:

  • qualify (recommended for high concurrency): all tenants share the base connection's pool — table names are schema-prefixed (tenant_x.users). One pool total. No connection pinned per request. Best scalability.
  • search_path: uses a dedicated reserved connection for the callback and sets search_path for that session, then resets it. One pool connection is pinned for the callback duration (including any external I/O inside the handler). Keep callbacks short; prefer qualify unless you need search_path behavior for raw SQL.
  • database: each distinct tenant database gets its own pool. T tenants ⇒ T × max sockets — this exhausts max_connections fast. This is mitigated by default: when tenancy.resolveTenant is set, configureOrm() applies a 5-minute idle TTL and a 60s background eviction sweep automatically. Tune or disable via tenancy.idleTimeoutMs and tenancy.sweep:
tenancy: {
  resolveTenant: ...,
  idleTimeoutMs: 5 * 60_000,   // default; idle database-strategy pools expire
  sweep: 60_000,               // default true@60s; false/0 to disable
}

For manual runtime wiring, configure the lifecycle directly:

import { ConnectionManager } from "@rekkr/orm";

ConnectionManager.defaultTenantTtl = 5 * 60_000;   // a resolution-level `ttl` always wins
ConnectionManager.enableTenantSweep(60_000);        // unref'd; call once at startup

With the sweep disabled, expired database-strategy pools are only reclaimed lazily the next time that same tenant is resolved — idle tenants in between keep their sockets open until ConnectionManager.closeAll().

migrationsPath vs migrations

There are two shapes for declaring where migration files live.

Flat (single-tenant)

migrationsPath: "./database/migrations",
// or multiple roots merged in glob order
migrationsPath: ["./database/migrations", "./database/extra"],

Grouped (multi-tenant)

migrations: {
  landlord: "./database/landlord-migrations",
  tenant:   "./database/tenant-migrations",
}

When migrations.landlord or migrations.tenant is set, use orm migrate --landlord, orm migrate --tenants, or orm migrate --tenant=<id> to target a scope. When using the grouped form, configure every scope you target; migrationsPath is not consulted.

createIfMissing

Tell the migrator to provision the database or schema before running migrations:

migrations: {
  landlord: "./database/migrations",
  createIfMissing: true,                          // both database and schema
  // — or fine-grained —
  createIfMissing: { database: true, schema: false },
}

Driver behavior:

  • PostgreSQL — connects to the postgres admin database, checks pg_database, and runs CREATE DATABASE if missing. Schemas use CREATE SCHEMA IF NOT EXISTS.
  • MySQL — CREATE DATABASE IF NOT EXISTS via the mysql admin database. MySQL does not have schemas, so the schema option is a no-op.
  • SQLite — the file is created on connect. Both database and schema flags are no-ops.

See Migrations for the full lifecycle.

seedersPath

seedersPath: "./database/seeders",
// or
seedersPath: ["./database/seeders", "./database/test-fixtures"],

Used by bunx orm db:seed and orm.seed(). Each configured root runs its DatabaseSeeder when present, otherwise it falls back to all seeder files in filename order. See Seeders.

tenancy

Two callbacks turn an app into a multi-tenant system.

resolveTenant

Maps a tenant identifier to a connection or schema. Three strategies are supported:

// Database-per-tenant — each tenant on its own database
tenancy: {
  resolveTenant: async (tenantId) => ({
    strategy: "database",
    name: `tenant:${tenantId}`,
    config: { url: await lookupDsn(tenantId) },
  }),
}

// Schema-per-tenant (Postgres only) — shared database, qualified table names
tenancy: {
  resolveTenant: async (tenantId) => ({
    strategy: "schema",
    name: `tenant:${tenantId}`,
    schema: `tenant_${tenantId}`,
    mode: "qualify", // or "search_path"
  }),
}

// Row-level security (Postgres) — same database, session var set per request
tenancy: {
  resolveTenant: async (tenantId) => ({
    strategy: "rls",
    name: `tenant:${tenantId}`,
    tenantId,
    setting: "app.current_tenant",
  }),
}

name is an internal cache key; pick something stable. The resolution is cached for the duration of the process.

Model-level schema override (static modelSchema)

For PostgreSQL schema-per-tenant apps, you can pin specific models to a fixed schema (commonly landlord/shared models on public) while other models stay tenant-scoped:

class Plan extends Model {
  static override modelSchema = "public";
}

class Invoice extends Model {}

Resolution order for model table names:

  1. static modelSchema on the model
  2. Active tenant schema from TenantContext.run(...)
  3. Fallback public (PostgreSQL only)

listTenants

Only used by the CLI when running grouped tenant migrations across every tenant (bunx orm migrate --tenants):

tenancy: {
  listTenants: async () => {
    const rows = await landlordDb.query("SELECT id FROM tenants");
    return rows.map((r) => r.id);
  },
}

Application code never calls this — it is purely for batch operations.

idleTimeoutMs

tenancy: { idleTimeoutMs: 300_000 }

Idle TTL (milliseconds) for tenant contexts that own their own connection pool (the database strategy, and any resolution that builds a new connection from config). After this window with no use, the context expires and its pool can be closed by the sweep — preventing per-tenant pools from accumulating until the database hits its connection limit.

A resolution-level ttl always overrides this. Applies only to connection-owning contexts; shared schema/RLS connections (which do not own a pool) are unaffected.

Default: 300_000 (5 minutes) when tenancy.resolveTenant is set. Set to a different value to tune, or pair with sweep: false to opt out of reclamation entirely.

sweep

tenancy: { sweep: true }      // background sweep every 60s
tenancy: { sweep: 30_000 }    // custom interval (ms)
tenancy: { sweep: false }     // disable

Enables a background timer that closes expired tenant contexts (per idleTimeoutMs / resolution ttl), reclaiming idle pools. The timer is unref'd so it never keeps the process alive.

Default: enabled at a 60s interval when tenancy.resolveTenant is set. false or 0 disables it (idle pools then only reclaim when a tenant is re-resolved after expiry).

See Library Usage and the Query Builder's DB.tenant() section for runtime use.

modelsPath

Tells the REPL and the type generator where to look for model classes:

modelsPath: "./src/models",
// or a list
modelsPath: ["./src/models", "./src/admin/models"],
// or partitioned for multi-tenant projects
modelsPath: {
  landlord: "./src/models/landlord",
  tenant:   "./src/models/tenant",
}

The grouped form lets orm migrate --landlord --types regenerate types only for landlord-scoped models.

policyPath

Optional default output path for policy generators:

policyPath: "./app/policies",
// or
policyPath: ["./app/policies", "./modules/core/policies"],

Used by orm make:policy when --dir is not provided.

Type generation

typeDeclarationImportPrefix: "$models",
typeDeclarationSingularModels: true,
typeDeclarations: {
  admin_users: { path: "$models/admin/AdminAccount", className: "AdminAccount" },
},
typeStubs: false,                                  // true emits base-class stubs instead of declarations

See Type Generation for the full feature reference.

transactions

transactions: {
  abandonedTimeoutMs: 60_000,   // default; set 0 to disable
}

Safety net for the manual transaction API (connection.beginTransaction() paired with commit() / rollback()). beginTransaction() reserves a pooled connection; if neither commit() nor rollback() is called within abandonedTimeoutMs, the transaction is force-rolled-back and the pooled slot released — so a code path that throws between begin and commit cannot leak a connection permanently.

The timer is unref'd and is cleared automatically on a normal commit() / rollback(), so well-behaved transactions never trigger it. The callback form (DB.transaction(cb) / connection.transaction(cb)) already releases on its own and is unaffected.

Default: 60_000 (60s) globally. Set abandonedTimeoutMs: 0 to disable. Raise it if you legitimately run long manual transactions; prefer the callback form where possible.

log

log: true                                  // SQL to console
log: { file: "./logs" }                    // SQL to ./logs/query-YYYY-MM-DD.log only
log: { file: "./logs", console: true }     // both file and console
log: { console: true, bindings: true }     // include binding values
log: false                                 // off (default)

Controls SQL query logging.

  • true — log every query to the console.
  • { file } — append queries to a dated file query-YYYY-MM-DD.log inside the given directory (rolls over daily; rotate/prune old files with your OS, e.g. logrotate). Console output is off unless console: true is also set.
  • { file, console: true } — write to both.
  • false / omitted — no logging.
  • bindings: true — write the binding values next to each query. Off by default: the log shows only how many bindings were hidden, since they can carry passwords, tokens or personal data.

Useful in development; in production prefer the file form (or leave off) and ensure query sampling if volume is high.

Listening to queries in code

log writes lines; DB.listen() hands each statement to a function, for slow query alerts, APM spans, or counting queries in a test:

import { DB } from "@rekkr/orm";

const stop = DB.listen(({ sql, bindings, durationMs, connection, error }) => {
  if (durationMs > 100) console.warn(`Slow query (${durationMs.toFixed(1)} ms): ${sql}`);
});

stop(); // no longer called
  • What is reported: every statement your code runs, on every connection, including tenant connections created later: queries, writes, raw SQL, and the queries ORM makes on your behalf, such as reading a table's primary key column the first time a model inserts into it. Not reported: BEGIN, COMMIT, ROLLBACK, savepoints, SQLite's connection pragmas, MySQL's UTC check and LAST_INSERT_ID(), and anything run under pretend().
  • When: after the statement finishes, whether it succeeded or failed. A failed statement carries error, the same error its caller receives. Whether a statement is reported is decided when it starts.
  • connection is the connection that ran it: inside a transaction, the transaction's session, so connection.isInTransaction() is true while your listener runs. To keep one configured connection's statements, compare connection.resourceConnection() with it.
  • Errors in a listener are caught and reported with console.error; they never reach the query, which has already run. An async listener is not awaited, so a slow one does not delay the query.
  • Bindings are in clear text. log hides them unless bindings: true; a listener that exports queries elsewhere should drop or mask them.
  • Cost: with no listener, one boolean check per statement. A listener runs in the query's async context, so APM tooling can attach it to the request that caused it; a query it runs inside a transaction joins that transaction.

The events are published on the node:diagnostics_channel channel @rekkr/orm:query, which tracing tools can subscribe to by name without touching the application. A subscriber added that way is not guarded: if it throws, the error surfaces as an uncaught exception.

// Count the statements a code path runs, e.g. to catch an N+1 in a test.
const statements: string[] = [];
const stop = DB.listen(({ sql }) => { statements.push(sql); });
await loadDashboard();
stop();
expect(statements).toHaveLength(3);

queue

Enables the background job queue. When present, configureOrm() configures the selected database, Redis, or custom driver. The database driver is the default.

queue: {
  driver: "db",                  // "db", "redis", or a QueueDriver instance
  defaultQueue: "default",        // queue name used when a job does not specify one
  workers: 2,                     // concurrent worker slots for `orm queue`
  jobsPath: "./app/jobs",         // directory the worker imports to register job classes
  retryAfterSeconds: 90,          // re-queue jobs reserved but not finished within this time
  retryDelaySeconds: 5,           // wait before retrying a failed job
  pollIntervalMs: 1_000,          // worker polling interval
  table: "jobs",                  // override the jobs table name
  failedTable: "failed_jobs",     // override the failed jobs table name
},

All fields are optional. Omitting the entire queue key leaves the queue unconfigured; you can still call Queue.configure() manually.

For Redis, configuration is enough; the URL defaults to REDIS_URL (Bun's default client on Bun, ioredis on Node.js):

queue: {
  driver: "redis",
  redis: { url: process.env.QUEUE_REDIS_URL },
}

Use Queue.configure() manually only when wiring ORM without configureOrm() or when supplying a custom driver. See Queue Jobs for the full reference.

Subsystem configuration

These optional OrmConfig sections have dedicated guides:

  • cache — cache store, key prefix, and default TTL.
  • commands — command discovery paths.
  • search — engine, model discovery, batching, queues, and tenant-aware indexes.

Wiring it up at runtime

The CLI loads orm.config.ts automatically. Your application code activates the same config through configureOrm():

// src/app.ts
import { configureOrm } from "@rekkr/orm";
import config from "../orm.config";

const orm = configureOrm(config);

// orm.connection — the live Connection
// orm.migrate(), orm.seed(), orm.migrator(), orm.seeder() — facade helpers

configureOrm() does the following on call:

  1. Constructs a Connection from config.connection and registers it as the default.
  2. Sets the connection on Model and Schema so static helpers work.
  3. Wires up tenancy.resolveTenant if provided, and applies the tenant pool defaults (idleTimeoutMs, sweep).
  4. Applies the abandoned-transaction safety net (transactions.abandonedTimeoutMs, default 60s).
  5. Configures query logging from log.
  6. Configures the selected database, Redis, or custom queue driver if queue is set.

It returns a facade you can use to run migrations and seeders programmatically.

Reconfiguration and SvelteKit HMR

configureOrm(config) configures once. Replace an active configuration with await reconfigureOrm(config) from outside a tenant, query or transaction scope. It validates first, retires the old state, drains active work and search batches, closes owned pools, then installs the replacement. Omitted cache, queue, search, logging and tenancy options are cleared. A cleanup failure rejects; retry the asynchronous operation before admitting new requests.

Use a server-only bootstrap module. Keep ORM modules outside Vite's hot module replacement graph; restart the server if those modules themselves change. For application configuration reloads, serialize the replacement:

// src/lib/server/orm.ts
import { configureOrm, reconfigureOrm, type ConfiguredOrm } from "@rekkr/orm";
import config from "../../../orm.config";

declare global {
  var __ormReady: Promise<ConfiguredOrm> | undefined;
}

const previous = globalThis.__ormReady;
globalThis.__ormReady = previous
  ? previous.then(() => reconfigureOrm(config))
  : Promise.resolve(configureOrm(config));
export default await globalThis.__ormReady;

Import this module from server code (import orm from "$lib/server/orm"). Await readiness before handling requests; do not configure separately in routes.

await ConnectionManager.setTenantResolver(resolver) also retires previous cached tenant contexts and invalidates pending resolutions. Existing scopes and operations renew the idle TTL; a referenced model alone does not keep a pool alive. Connections created from configuration are owned. Passing an existing Connection to add or setDefault borrows it unless { owned: true } is explicit. Borrowed connections remain the caller's responsibility.

Environment variables (CLI only)

When neither orm.config.ts nor orm.config.js exists, the CLI falls back to env vars:

export DATABASE_URL="sqlite://app.db"
export MIGRATIONS_PATH="./database/migrations,./database/tenant-migrations"
export SEEDERS_PATH="./database/seeders"
export MODELS_PATH="./src/models"

Without DATABASE_URL, the connection can come from Laravel-style variables instead. DB_CONNECTION is sqlite, mysql or postgres; for SQLite, DB_DATABASE is the file name:

export DB_CONNECTION="postgres"
export DB_HOST="localhost"
export DB_PORT="5432"
export DB_DATABASE="mydb"
export DB_USERNAME="app"
export DB_PASSWORD="secret"

Comma-separated lists work where a config field accepts string[]. Prefer a real config file for anything beyond a quick experiment — the env-var path does not support tenancy, createIfMissing, or any of the type generation overrides.