TypeScript Quickstart

This guide shows how to define a schema, run migrations, and perform CRUD with @visorcraft/mongreldb-kit.

Installation

npm install @visorcraft/mongreldb-kit @visorcraft/mongreldb

@visorcraft/mongreldb is a peer dependency providing the native database bindings. In a local checkout, build the sibling crates/mongreldb-node addon with npm run build (release mode) before benchmarking; debug builds make bulk writes and pushed-down queries look much slower than they are.

Complete example

import {
  KitDatabase,
  Schema,
  table,
  int,
  text,
  bool,
  foreignKey,
  check,
  index,
  staticDefault,
  sequenceDefault,
  eq,
  desc
} from '@visorcraft/mongreldb-kit';

// ---------------------------------------------------------------------------
// Schema
// ---------------------------------------------------------------------------

const users = table('users', {
  columns: [
    int('id', { primaryKey: true, default: sequenceDefault('users_id_seq') }),
    text('email'),
    text('name', { nullable: true })
  ],
  primaryKey: 'id',
  indexes: [index(['email'], { unique: true, name: 'uq_user_email' })]
});

const posts = table('posts', {
  columns: [
    int('id', { primaryKey: true, default: sequenceDefault('posts_id_seq') }),
    int('user_id'),
    text('title'),
    text('body', { nullable: true }),
    bool('published', { default: staticDefault(false) }),
    text('created_at', { generated: 'now' })
  ],
  primaryKey: 'id',
  foreignKeys: [
    foreignKey(['user_id'], { table: 'users', columns: ['id'] }, { onDelete: 'cascade' })
  ],
  checks: [check('title_not_empty', (row) => (row.title as string).length > 0 || 'title must not be empty')]
});

const schema = new Schema([users, posts]);

// ---------------------------------------------------------------------------
// Open or create the database and run migrations
// ---------------------------------------------------------------------------

const db = KitDatabase.openSync('./app-data', schema);
// A second live open of this path throws with code MONGRELDB_DATABASE_LOCKED.

db.migrateSync(schema, [
  {
    version: 1,
    name: 'initial',
    up({ ensureTable }) {
      ensureTable(users);
      ensureTable(posts);
    }
  }
]);

// ---------------------------------------------------------------------------
// Insert
// ---------------------------------------------------------------------------

// `id` is omitted: the sequence assigns a 1-based id (the first row is 1n, never 0n).
// Columns with a default (and nullable columns) are optional in `.values(...)`; only
// non-nullable, no-default columns are required. int64 columns are `bigint` (alice.id === 1n).
const alice = db.insertInto(users).values({ email: 'alice@example.com', name: 'Alice' }).executeSync();
const bob = db.insertInto(users).values({ email: 'bob@example.com' }).executeSync();

const post = db.insertInto(posts)
  .values({ user_id: alice.id, title: 'Hello Kit', body: 'First post.' })
  .executeSync();

// ---------------------------------------------------------------------------
// Query
// ---------------------------------------------------------------------------

const publishedPosts = db
  .selectFrom(posts)
  .where(eq(posts.published, false))
  .orderBy(desc(posts.created_at))
  .limit(10)
  .executeSync();

const titles = db.selectFrom(posts).select([posts.title]).executeSync();

// ---------------------------------------------------------------------------
// Update — partial patch only (omit keys you do not change)
// ---------------------------------------------------------------------------

db.updateTable(posts)
  .set({ published: true })
  .where(eq(posts.id, post.id))
  .executeSync();
// set() sanitizes then merges onto the stored row: omit = leave unchanged,
// null = SQL NULL, undefined = omit. Prefer partial patches over full-row spreads.
// See docs/query-builder.md.

// ---------------------------------------------------------------------------
// Delete
// ---------------------------------------------------------------------------

// Deleting Alice cascades to her posts because of the FK onDelete action.
const deleted = db.deleteFrom(users).where(eq(users.id, alice.id)).executeSync();
console.log('deleted users:', deleted);

// ---------------------------------------------------------------------------
// Cleanup
// ---------------------------------------------------------------------------

db.close();

Column helpers

• int(name, opts?)
• text(name, opts?)
• real(name, opts?) - float64
• bool(name, opts?)
• timestamp(name, opts?)
• date(name, opts?)
• json(name, opts?)
• blob(name, opts?) - bytes

Column options

Option	Effect
nullable?: boolean	Allow null values
primaryKey?: boolean	Mark as part of the primary key
default?: DefaultValue	Static, now, UUID, sequence, or custom default
generated?: 'uuid' | 'now'	Auto-generate on insert/update
enumValues?: string[]	Restrict string values
min?: number, max?: number	Numeric range
minLength?: number, maxLength?: number	String/bytes length
regex?: RegExp	Pattern match
check?: (value) => boolean | string	Per-column custom check

Query builder

Select:

db.selectFrom(table)
  .where(predicate)
  .orderBy(asc(column), desc(column2))
  .limit(n)
  .offset(n)
  .select([col1, col2])
  .executeSync();

Insert:

db.insertInto(table).values({ ... }).executeSync();
db.insertInto(table).valuesMany([{ ... }, { ... }]).executeSync();

Update (partial patch — omit unchanged columns; null → SQL NULL; undefined → omit):

db.updateTable(table).set({ status: 'shipped' }).where(predicate).executeSync();
// Avoid full-row spreads; prefer true partial patches.

Delete:

db.deleteFrom(table).where(predicate).executeSync();

Predicates

• eq(column, value)
• ne(column, value)
• gt(column, value), gte(column, value), lt(column, value), lte(column, value)
• isNull(column), isNotNull(column)
• inList(column, values), notInList(column, values)
• like(column, pattern), contains(column, substring)
• bytesPrefix(column, prefix) - anchored LIKE 'prefix%' on a bitmap-indexed Bytes column (exact pushdown; see Query builder)
• and(...predicates), or(...predicates), not(predicate)

Joins, aggregates, groupBy/having, distinct, subqueries, exists, and CTEs are part of the same builder - see the Query builder guide for the full surface.

Database helpers

• db.tableNames() returns application tables and hides the reserved __kit_* namespace.
• db.renameTable(oldName, newName) durably renames a live table. Pair it with a matching schema update in a migration; it rejects __kit_ names.
• db.createTriggerSync(spec), db.createOrReplaceTriggerSync(spec), db.dropTriggerSync(name), db.triggers(), and db.trigger(name) manage engine-side triggers.
• await db.sql(sql) returns an Apache Arrow table. await db.sqlRows(sql) decodes SQL results to plain objects.
• await db.createVirtualTable(spec) and await db.dropVirtualTable(name) run the SQL DDL for module-backed virtual/external tables.
• await db.createView(spec) / await db.dropView(name) create/drop a SQL view (CREATE VIEW / DROP VIEW IF EXISTS). Views are session-scoped - see SQL views.
• db.updateWhere(table, patch, predicate) and db.deleteWhere(table, predicate) are one-shot convenience twins of Rust/Python update_where/delete_where, wrapping the updateTable/deleteFrom builders. updateWhere returns the updated rows; deleteWhere returns the deleted count as a bigint.
• await db.analyze() and await db.vacuum() rebuild index statistics and reclaim space (the engine's ANALYZE / VACUUM equivalents), routing through the SQL surface.
• Async / non-blocking I/O: the Kit wraps the addon's spawn_blocking async variants so hot read/write paths don't block the Node event loop: db.putAsync(table, cells), db.getAsync(table, rowId), db.queryAsync(table, conditions), db.countAsync(table), db.countWhereAsync(table, conditions), db.queryArrowAsync(table, conditions), db.setSimilarityAsync(...), plus async twins of the maintenance methods (flushAsync, compactAllAsync, compactTableAsync, snapshotEpochAsync, approxAggregateAsync). Caveat: the maintenance twins where the addon has no native async variant (compactAllAsync/compactTableAsync/approxAggregateAsync/snapshotEpochAsync) wrap the sync call in a Promise - they match the async signature but still block; the TableHandle async methods (putAsync/queryAsync/…) are genuinely non-blocking. See runTxn for an async transaction helper.
• Bulk ingest: db.bulkLoadTyped(table, columns) is the fastest ingest path - column-major Int64/Float64/Bool buffers laid out little-endian. Commits internally (returns the epoch); not transactional; bypasses Kit constraints. For typed columnar loads of numeric tables it beats insertMany. Re-exported types: TypedColumn, PutResult, RowJs, ConditionSpec, CommitResultJs.
• db.nativeDb exposes the underlying mongreldb database for raw operations that intentionally bypass Kit validation, defaults, and relational guards.
• Storage tuning & introspection: db.setSpillThreshold(bytes), db.setRecursiveTriggers(enabled), db.triggerConfig() / db.setTriggerConfig(cfg) (trigger recursion/depth/loop caps), per-table tuning (setTableCompactionZstdLevel, setTableResultCacheMaxBytes, setTableMutableRunSpillBytes, setTableSyncByteThreshold, setTableIndexBuildPolicy), and per-table introspection (tableRunCount, tableMemtableLen, tablePageCacheStats, tablePageCacheLen, tableDecodedCacheLen). Re-exported types: CacheStatsJs, TriggerConfigJs, IndexBuildPolicyJs.
• WriteBuffer: db.writeBuffer(table, threshold?) creates a micro-batching write buffer - writes are not durable until flush() (the opposite of put()). Auto-flushes at threshold rows (default 1000). Bypasses Kit constraints; for high-throughput ingest.

The kit's SQL session is held for the database's lifetime, so views (CREATE VIEW) created via db.sql() persist across subsequent sql() / sqlRows() calls. See Migrations → SQL views.

Remote SQL control, pagination, and retry-safe writes

Remote SQL requires daemon cancellation capability version 2. Every call gets a client query ID, including calls without explicit timeout options. If the response is lost or cannot be decoded, the client resolves the durable status and throws CommitOutcomeError, SerializationError, or QueryOutcomeUnknownError instead of guessing.

For unknown outcomes, status committed and durable counters are null, and QueryOutcomeUnknownError.committed is null. Only committed === false proves that no statement committed.

const page = await remote.sqlPage(
  "SELECT id, title FROM documents ORDER BY id",
  {
    projection: ["id", "title"],
    pageSizeRows: 500,
    maxPageBytes: 1_000_000,
    maxPageTokens: 100_000,
  },
);

const next = page.nextCursor
  ? await remote.continueSqlPage(page.nextCursor)
  : undefined;

const receipt = await remote.executeIdempotentSql(
  "UPDATE jobs SET claimed = true WHERE id = 42",
  { idempotencyKey: "claim-job-42-attempt-1" },
);
console.log(
  receipt.committed,
  receipt.lastCommitEpoch, // bigint, exact
  receipt.firstCommitStatementIndex,
  receipt.lastCommitStatementIndex,
);

Pagination accepts a read-only SELECT, requires an explicit projection, and returns an opaque owner-bound cursor. Idempotent SQL accepts one write statement. Reuse the same key after transport loss. Never retry automatically when the receipt proves a commit or reports an unknown outcome.

Remote procedure and trigger writes use the same typed contract. CommitOutcomeError preserves committed, exact lastCommitEpoch as a bigint, and retryable; QueryOutcomeUnknownError.committed remains null. Never replay either response automatically.

History retention and time-travel reads

The embedded KitDatabase and the daemon client RemoteDatabase both expose history-retention controls. Set retention before writing data you want to read back at an older snapshot. Embedded databases initially keep only the latest epoch; the daemon defaults to 1024 epochs unless MONGRELDB_HISTORY_RETENTION_EPOCHS overrides it. Raising retention later cannot recover pruned history.

Embedded:

// Keep the last 100 committed epochs visible to MVCC time-travel reads.
db.setHistoryRetentionEpochs(100);
console.log(db.historyRetentionEpochs());  // 100n
console.log(db.earliestRetainedEpoch());   // oldest epoch still retained

// Read the whole table as it looked at a past epoch.
const pastRows = db.rowsAtEpoch('users', epoch);

Remote (daemon client):

remote.setHistoryRetentionEpochs(100n);
console.log(remote.historyRetentionEpochs()); // 100n
console.log(remote.earliestRetainedEpoch());  // oldest epoch still retained

You can also query a past snapshot through SQL, both embedded and on the daemon, with the AS OF EPOCH extension:

await db.sqlRows('SELECT name FROM users AS OF EPOCH 42 WHERE id = 1');

Advanced SQL via db.sqlRows()

The embedded SQL session runs DataFusion 54, which supports the full SQL stdlib - recursive CTEs, window functions, regex matching, catalog introspection, cross-database queries, and sub-transactions:

// Recursive CTE (tree traversal).
await db.sqlRows(`
  WITH RECURSIVE tree AS (
    SELECT id, parent, 0 AS depth FROM nodes WHERE parent IS NULL
    UNION ALL
    SELECT n.id, n.parent, t.depth + 1 FROM nodes n JOIN tree t ON n.parent = t.id
  )
  SELECT id, depth FROM tree ORDER BY id
`);

// Window function (ranking within partitions).
await db.sqlRows(`
  SELECT category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) AS rank
  FROM orders
`);

// Regex match.
await db.sqlRows("SELECT id FROM users WHERE regexp('^admin.*', name) = 1");

// Catalog introspection.
await db.sqlRows("SELECT type, name FROM information_schema.tables ORDER BY name");

// Cross-database query.
await db.sqlRows("ATTACH './other-data' AS other");
await db.sqlRows("SELECT id FROM other_items");
await db.sqlRows("DETACH other");

// Sub-transaction.
await db.sqlRows("BEGIN");
await db.sqlRows("INSERT INTO logs VALUES (1, 'hello')");
await db.sqlRows("SAVEPOINT sp1");
await db.sqlRows("INSERT INTO logs VALUES (2, 'world')");
await db.sqlRows("ROLLBACK TO sp1"); // discards 'world'
await db.sqlRows("COMMIT");

Savepoints require an explicit transaction. ROLLBACK TO name keeps the target active, removes savepoints created after it, and can recover an aborted transaction so work can continue.

Triggers and SQL helpers

Assuming users, audit, and events table specs already exist:

import {
  groupConcat,
  newColumn,
  percentileCont,
  textValue,
  trigger,
  virtualTable,
} from '@visorcraft/mongreldb-kit';

db.createTriggerSync(trigger({
  name: 'users_ai',
  target: { kind: 'table', name: 'users' },
  timing: 'after',
  event: 'insert',
  program: {
    steps: [{
      kind: 'insert',
      table: 'audit',
      cells: [
        { column_id: audit.user_id.id, value: newColumn(users.id.id) },
        { column_id: audit.note.id, value: textValue('created') },
      ],
    }],
  },
}));

await db.sqlRows(`SELECT ${percentileCont(events.latency_ms, 0.95).sql} AS p95 FROM events`);
await db.sqlRows(`SELECT ${groupConcat(events.tag, '|').sql} AS tags FROM events`);
await db.createVirtualTable(virtualTable('docs_fts', 'fts_docs', ['content=docs']));

See Triggers and Extended SQL & virtual tables for the full API.

Migrations

Call db.migrateSync(schema, migrations) to apply pending migrations in version order. The runner acquires an advisory lock, records each migration in __kit_schema_migrations, and updates __kit_schema_catalog.

Error handling

Catch typed errors by name:

import { KitDuplicateError, KitForeignKeyError, KitRestrictError, KitValidationError } from '@visorcraft/mongreldb-kit';

try {
  db.insertInto(users).values({ email: 'alice@example.com' }).executeSync();
} catch (err) {
  if (err instanceof KitDuplicateError) {
    console.error('duplicate email');
  }
}

Users, roles & permissions

The Kit forwards the engine's catalog-stored auth model - Argon2id-hashed users, roles that bundle permissions, and GRANT/REVOKE table-level access control. Permission strings use the compact form: "all", "admin", "ddl", or "select:table", "insert:table", "update:table", "delete:table".

// db is a KitDatabase opened with KitDatabase.openSync(...)
db.createUser('alice', 's3cret-pw');
db.alterUserPassword('alice', 'new-pw');
console.log(db.verifyUser('alice', 'new-pw')); // true
db.setUserAdmin('alice', true);                // admin bypasses all permission checks
console.log(db.users());                       // ['alice']

db.createRole('analyst');
db.grantPermission('analyst', 'select:orders');
db.grantPermission('analyst', 'insert:orders');
db.grantRole('alice', 'analyst');
console.log(db.roles());                       // ['analyst']

// Reverse
db.revokePermission('analyst', 'insert:orders');
db.revokeRole('alice', 'analyst');
db.dropRole('analyst');
db.dropUser('alice');

The full model (including SQL DDL like CREATE USER / GRANT and the HTTP daemon's Bearer + Basic auth modes) is documented in the engine Users, Roles & Permissions guide. The Kit CLI exposes the same operations as user and role subcommands.

Credential enforcement

A database with require_auth set rejects every open that does not supply valid credentials. Use the credentialed constructors to open or create such a database, and the enableAuth/disableAuth helpers to flip the flag in code. requireAuthEnabled() reports the current state.

// Create a new database with require_auth on, bootstrapping the first admin.
const db = KitDatabase.createWithCredentialsSync('./app-data', schema, 'alice', 's3cret-pw');

// Open an existing require_auth database.
const db2 = KitDatabase.openSync('./app-data', schema, {
  credentials: { username: 'alice', password: 's3cret-pw' },
});

console.log(db.requireAuthEnabled()); // true

// Turn require_auth on for an existing credentialless database.
db.enableAuth('alice', 's3cret-pw');

// Recovery: clear require_auth (needs an open handle).
db.disableAuth();

// Encrypted + credentialed: both layers in one call.
const secure = KitDatabase.createEncryptedWithCredentialsSync(
    './app-data', schema, 'passphrase', 'admin', 's3cret-pw'
);

// Long-lived handles call refreshPrincipal after a REVOKE to pick up
// permission changes made by other handles.
db.refreshPrincipal();

The full model and recovery flow are documented in the engine credential enforcement guide.

Running this example

Save the file as kit-demo.ts and run it with Node 22+:

npx tsx kit-demo.ts

The first run creates ./app-data. Subsequent runs open the existing database directory.

See also

• Schema DSL and Types - column/table specs and Row/Insert/Update inference.
• Defaults & sequences - defaults and 1-based auto-increment ids.
• Query builder - the complete query surface.
• Triggers and Extended SQL & virtual tables.
• Constraints · Errors - enforcement and the typed failures.
• Transactions · Migrations · Testing.