smart-db

A unified, type-safe ORM abstraction layer for SQLite (better-sqlite3), MySQL, MariaDB, PostgreSQL, and Oracle. Provides a single API across all supported databases with both synchronous and asynchronous access, a composable SQL builder, schema versioning, and optional database-backed logging.

V5 is a breaking release. LIKE patterns now behave the same on every database, which changes query results on SQLite and MySQL/MariaDB without raising errors. Read Upgrading from V4 to V5 before updating. Projects still on V3 must first complete Upgrading from V3 to V4.

Installation

npm install @egi/smart-db

Node.js 22 or newer is required.

Install only the driver(s) you need:

npm install better-sqlite3   # SQLite
npm install mysql2            # MySQL / MariaDB
npm install pg                # PostgreSQL
npm install pg-copy-streams   # optional: COPY-based PostgreSQL insertStream()
npm install oracledb          # Oracle

Quick Start

SQLite

import { SmartDbBetterSqlite3 } from "@egi/smart-db/drivers/smart-db-better-sqlite3";

const db = new SmartDbBetterSqlite3(
  { filename: "./my-database.db" },
  { module: "my-app", onReady: (db, err) => { /* ... */ } }
);

MySQL

import { SmartDbMysql } from "@egi/smart-db/drivers/smart-db-mysql";

const db = new SmartDbMysql(
  { host: "localhost", user: "root", password: "secret", database: "mydb" },
  { module: "my-app", onReady: (db, err) => { /* ... */ } }
);

PostgreSQL

import { SmartDbPostgres } from "@egi/smart-db/drivers/smart-db-postgres";

const db = new SmartDbPostgres(
  { host: "localhost", port: 5432, user: "myuser", password: "secret", database: "mydb" },
  { module: "my-app", onReady: (db, err) => { /* ... */ } }
);

Oracle

import { SmartDbOracle } from "@egi/smart-db/drivers/smart-db-oracle";

const db = new SmartDbOracle(
  { user: "hr", password: "secret", connectString: "localhost/XEPDB1" },
  { module: "my-app", onReady: (db, err) => { /* ... */ } }
);

Without connectString or connectionString, the driver connects to the identifier in the ORACLE_SID environment variable, or in TWO_TASK when ORACLE_SID is not set. The value can be an Easy Connect string such as dbhost:1521/FREEPDB1 or a net service name from the tnsnames.ora in TNS_ADMIN. Without either variable, node-oracledb applies its own default.

// ORACLE_SID=SALES, defined in $TNS_ADMIN/tnsnames.ora
const db = new SmartDbOracle({ user: "hr", password: "secret" }, { module: "my-app" });

node-oracledb runs in thin mode by default. Set thickMode: true to load the Oracle Client libraries; the first successful load applies to the whole process:

const db = new SmartDbOracle(
  { user: "hr", password: "secret", connectString: "localhost/XEPDB1" },
  {
    module: "my-app",
    thickMode: true,
    oracleClientLibDir: "/opt/oracle/instantclient_23_5", // Windows and macOS only
    oracleClientConfigDir: "/opt/oracle/network/admin",   // tnsnames.ora, sqlnet.ora
  }
);

Without oracleClientLibDir, node-oracledb searches the platform paths: PATH on Windows; DYLD_LIBRARY_PATH, /usr/local/lib, or a symlink in node_modules/oracledb/build/Release on macOS; and ldconfig, LD_LIBRARY_PATH, or $ORACLE_HOME/lib on Linux. Configure the environment before Node.js starts. On Linux, oracleClientLibDir is rejected because node-oracledb requires the system library search path there. oracleClientConfigDir overrides TNS_ADMIN.

Waiting for the Database

All drivers signal readiness asynchronously via an RxJS BehaviorSubject. Use databaseReady() before running queries, or pass onReady in options.

await db.databaseReady();

// or subscribe to state changes:
db.onReady.subscribe(state => console.log(state));

Models

Models map TypeScript classes to database tables. Generate them from a live schema using the CLI (see Schema Extraction), or write them manually:

import { AbstractModel, GenericModelData, ModelAttributeMap } from "@egi/smart-db";

interface UserData extends GenericModelData {
  user_id: number;
  user_name: string;
}

class UserModel extends AbstractModel<UserModel, UserData> {
  static readonly attributeMap: ModelAttributeMap = {
    id:   { attribute: "_id",   alias: "user_id",   type: "number", typeScriptStyle: true },
    name: { attribute: "_name", alias: "user_name",  type: "string", typeScriptStyle: true },
  };

  static getTableName()     { return "users"; }
  static getPrimaryKey()    { return "user_id"; }
  static getClassName()     { return "UserModel"; }
  static getPkSequenceName(){ return ""; }
  static from(other: UserModel | UserData) { const m = new UserModel(); m.assign(other); return m; }

  private _id: number;
  private _name: string;

  constructor(data?: UserModel | UserData) { super(data); }

  get id()   { return this._id; }
  set id(v)  { this._id = v; }
  get name() { return this._name; }
  set name(v){ this._name = v; }

  clone()           { return UserModel.from(this); }
  getClassName()    { return UserModel.getClassName(); }
  getTableName()    { return UserModel.getTableName(); }
  getPrimaryKey()   { return UserModel.getPrimaryKey(); }
  getPkSequenceName(){ return UserModel.getPkSequenceName(); }
  getAttributeMap() { return UserModel.attributeMap; }
}

Date-only fields

Calendar dates without a time or timezone can opt in to the standard Temporal.PlainDate API:

import {PlainDate, Temporal, plainDate} from "@egi/smart-db";

const dueDate: PlainDate = Temporal.PlainDate.from("2026-07-15");
const migrated = plainDate.fromDate(existingDate, "Europe/Zurich");

Use type: "PlainDate" in model metadata, or generate eligible fields with extract-db-api --date-type plain-date. Existing and newly generated fields remain JavaScript Date by default; PlainDate generation is opt-in for the foreseeable future. See PlainDate fields for database mapping, strict input, JSON, Oracle, SQLite, and migration details.

CRUD Operations

All methods have async and sync variants. Sync variants return false on error; async variants throw. SQLite supports both; MySQL, MariaDB, PostgreSQL, and Oracle are async-only.

Insert

const newId = await db.insert(UserModel, { name: "Alice" });
const newId = db.insertSync(UserModel, { name: "Alice" }); // SQLite only

Query

// All rows
const users = await db.getAll(UserModel);

// With WHERE
const admins = await db.getAll(UserModel, { role: "admin" });

// First match
const user = await db.getFirst(UserModel, { id: 42 });

// Full options
const results = await db.get(UserModel, {
  where: { status: "active" },
  orderBy: ["name asc"],
  limit: { limit: 10, offset: 20 },
});

Update

const affected = await db.update(UserModel, { name: "Bob" }, { id: 42 });

Delete

const deleted = await db.delete(UserModel, { id: 42 });

Truncate

await db.truncate(UserModel);

truncate() empties the table, restarts its generated primary-key counter, and does not cascade to referencing tables. PostgreSQL uses RESTART IDENTITY; MySQL/MariaDB reset AUTO_INCREMENT natively; Oracle resets the associated identity or PK sequence after truncation; SQLite uses an atomic DELETE plus sqlite_sequence reset. SQLite therefore retains DELETE trigger semantics.

MySQL/MariaDB and Oracle truncation cannot run through a SmartDB transaction because their native DDL commits implicitly.

Raw SQL

const rows = await db.query("SELECT * FROM users WHERE id = ?", [42]);
await db.exec("CREATE INDEX idx_name ON users(name)");

WHERE Clauses

WHERE conditions are plain objects. Keys are model attribute names; values can be literals or operator descriptors from smart-db-globals.

import { GT, LT, ILIKE, IN, LIKE, REGEXP_LIKE, IS_NULL, BETWEEN, NE } from "@egi/smart-db";

// Simple equality
const where = { status: "active" };

// Operators
const where = {
  age:    GT(18),
  score:  BETWEEN(80, 100),
  name:   LIKE("%smith%"),
  email:  ILIKE("%@example.com"),
  code:   REGEXP_LIKE(/^INV-[0-9]+$/i),
  role:   IN(["admin", "editor"]),
  deletedAt: IS_NULL(),
};

// Nested AND / OR
const where = {
  and: [
    { status: "active" },
    { or: [{ role: "admin" }, { role: "editor" }] },
  ],
};

SQL Helper Functions

Imported from @egi/smart-db (all re-exported from smart-db-globals):

| Function | SQL equivalent | |---------------------------|-----------------------------| | GT(v) | > v | | GE(v) | >= v | | LT(v) | < v | | LE(v) | <= v | | NE(v) | != v | | IN([...]) | IN (...) | | NOT_IN([...]) | NOT IN (...) | | LIKE(v) | LIKE v | | ILIKE(v) | case-insensitive LIKE v | | NOT_LIKE(v) | NOT LIKE v | | NOT_ILIKE(v) | case-insensitive NOT LIKE v | | REGEXP_LIKE(v) | case-sensitive regex match | | REGEXP_ILIKE(v) | case-insensitive regex match | | NOT_REGEXP_LIKE(v) | negated regex match | | NOT_REGEXP_ILIKE(v) | negated case-insensitive regex match | | IS_NULL() | IS NULL | | IS_NOT_NULL() | IS NOT NULL | | BETWEEN(min, max) | BETWEEN min AND max | | LITERAL(expr) | raw SQL fragment | | COUNT(field?, alias?) | COUNT(*) / COUNT(field) | | SUM / MIN / MAX / AVG | aggregate functions | | COALESCE([...], alias?) | COALESCE(...) | | FIELD(name, alias?) | column reference | | VALUE(val, alias?) | scalar value in SELECT |

All LIKE and regular-expression helpers use one portable contract on SQLite, MySQL/MariaDB, PostgreSQL, and Oracle. Pattern values are always bound; combining these operators with LITERAL() is rejected.

LIKE() and NOT_LIKE() are case-sensitive on every database, including SQLite and MySQL/MariaDB, whose native LIKE usually ignores case. Use ILIKE() or NOT_ILIKE() for case-insensitive matching. A plain string WHERE value that contains % or _, such as { name: "%smith%" }, is a LIKE pattern and follows the same rules.

Pattern operators also accept typed model fields. Integer fields are converted to ungrouped decimal text. Decimal and lossless-decimal fields use their declared precision and scale, never add thousands separators, and always use . as the decimal separator. Date and PlainDate fields use patternDateFormat, which defaults to YYYY-MM-DD and supports the portable tokens DD, MM, and YYYY. The format has no time tokens, so patterns on DATETIME or TIMESTAMP columns match only the date part. Native UUID and enum fields are cast to their database text representation. Time-only fields use patternTimeFormat, which defaults to HH:MM:SS and supports the 24-hour tokens HH, MM, and SS. Boolean fields are matched exactly: their operand must be true or false, and negated pattern operators use inequality. These rules apply to explicit helpers, regular-expression helpers, expression comparisons, and implicit %/_ WHERE patterns.

A date-field LIKE or REGEXP SqlOperation may override the database-wide format without mutating shared options:

import {OP, SqlOperationType} from "@egi/smart-db";

const where = {
    dueDate: OP(
        SqlOperationType.REGEXP_LIKE,
        "^[0-3][0-9]\\.[01][0-9]\\.2026$",
        undefined,
        {patternDateFormat: "DD.MM.YYYY"}
    )
};

Operation-local formats must contain DD, MM, and YYYY exactly once each, may otherwise contain only spaces or ., /, _, and -, and are limited to 64 characters. They are rejected on non-date fields and non-pattern operations. The database-wide patternDateFormat option needs at least one of these tokens and otherwise follows the same rules. Custom drivers that override buildPatternInputExpression() must accept and use or forward its new optional third argument to support operation-local formats.

For LIKE, % matches zero or more characters, _ matches one character, and backslash escapes %, _, or another backslash. Other and dangling backslash escapes are invalid. ILIKE, NOT_ILIKE, REGEXP_ILIKE, and NOT_REGEXP_ILIKE perform deterministic ASCII-only case folding: A through Z compare case-insensitively, while non-ASCII characters remain exact.

The regular-expression helpers accept a pattern string or a JavaScript RegExp. SmartDB serializes a RegExp through .source; the i flag passed to REGEXP_LIKE() or NOT_REGEXP_LIKE() silently selects the corresponding case-insensitive operation. The i and u flags are accepted and all other flags are rejected.

The portable regex grammar is a JavaScript-compatible subset: literals, escaped punctuation, ., character classes, ascending ASCII letter/digit ranges, capturing groups, alternation, *, +, ?, {m}, {m,n}, and ^/$ anchors. Matches search within the value unless anchored; ^ matches only the start, while $ matches the end or the position before one final LF. Dot and negated classes follow JavaScript behavior: dot does not match LF, while a negated class does unless it excludes the character. null never matches. Lookarounds, backreferences, named or engine-specific groups, inline flags, shorthand and engine-specific escapes, unbounded {m,}, and unsafe nested repetition are rejected.

Browser consumers can validate without loading a database driver:

import {SmartDbPattern, SmartDbPatternErrorCode} from "@egi/smart-db";

const like = SmartDbPattern.normalizePortableLike(String.raw`100\%`);
const regexp = SmartDbPattern.normalizePortableRegexp("^item[0-9]+$", false);
const format = SmartDbPattern.validateDateFormat("DD.MM.YYYY", {requireCompleteDate: true});
const limits = SmartDbPattern.limits;

validateDateFormat() applies the date-format rules above; pass requireCompleteDate: true for operation-local formats. SmartDbPattern.isDateField() reports whether field metadata uses a date format.

Current limits are 512 characters for LIKE, 256 for regex, eight nested regex groups, 32 quantifiers, and 100 as the largest bounded-quantifier value. Validation failures are SmartError values with a stable SmartDbPatternErrorCode code.

Advanced Queries

Joins

import { FIELD, JOIN, SqlJoinType } from "@egi/smart-db";

const orders = await db.get(OrderModel, {
  fields: ["id", FIELD("customers.name", "customerName")],
  join: JOIN(
    "customers",
    { expression: [{ compare: "orders.customer_id", with: "customers.id" }] },
    { type: SqlJoinType.Left }
  ),
});

Bulk operations

const result = await db.insertBulk(UserModel, users, {
  chunkSize: 500,
  returnGeneratedIds: true,
  onProgress: async (done, total) => {
    await reportProgress(done, total);
  },
});

console.log(result.inserted, result.errors, result.lastId);
console.log(result.generatedIds); // source-aligned number | null entries

const controller = new AbortController();
const streamed = await db.insertStream(UserModel, userSource(), {
  chunkSize: 500,
  targetChunkBytes: 8 * 1024 * 1024,
  signal: controller.signal,
  onProgress: ({ completed, estimatedTotal }) => {
    console.log(completed, estimatedTotal);
  },
});

await db.updateBulk(UserModel, [
  { values: { status: "active" }, where: { id: 42 } },
]);

insertBulk() and insertStream() use one logical transaction per root call, including all effective chunks. Array rows must target the same physical field set, though object key order may differ. continueOnError isolates bad rows but does not commit successful chunks early; issue separate calls when restartable checkpoints are required. When the optional pg-copy-streams package is installed, PostgreSQL streams use COPY FROM STDIN; without it they use the multi-row path, which is equally atomic but slower. Requesting continueOnError also retains the savepoint-backed multi-row path because COPY is atomic. MySQL/MariaDB requires a verified InnoDB target by default. See Transactional bulk inserts and streams for limits, migration guidance, cancellation semantics, and measured throughput.

Aggregates and Field Selection

import { COUNT, SUM, FIELD } from "@egi/smart-db";

const stats = await db.get(UserModel, {
  fields: [COUNT("*", "total"), SUM("score", "totalScore")],
  groupBy: "role",
});

UNION / INTERSECT / MINUS

const results = await db.get(UserModel, {
  where: { role: "admin" },
  union: [{ model: UserModel, where: { role: "superadmin" } }],
  orderBy: "name asc",
});

Distinct and Count

const count = await db.get(UserModel, { count: true });
const unique = await db.get(UserModel, { distinct: true, fields: "role" });

Transactions

Callback style — transactionWith() (recommended)

transactionWith() is the safest pattern. It commits on success and automatically rolls back if the callback throws or if the connection is never committed — no try/finally required on the call site.

await db.transactionWith(async (tx) => {
  await tx.insert(OrderModel, order);
  await tx.insert(OrderLineModel, line);
  // commits on return; rolls back if this function throws
});

Scoped handle — transaction()

transaction() returns a dedicated connection handle. Use this when you need explicit control — for example, to run concurrent transactions or to interleave reads between steps.

const tx = await db.transaction();
try {
  await tx.insert(OrderModel, order);
  await tx.insert(OrderLineModel, line);
  await tx.commit();
} finally {
  await tx.close(); // no-op after commit; rolls back if commit was never reached
}

Locking reads — getFirstForUpdate() / getAllForUpdate()

Use a locking read when the selected row must remain exclusively claimed until the surrounding transaction commits or rolls back. The caller must own the transaction; a locking read never opens or closes one implicitly. For a multi-worker queue, skipLocked: true lets each worker claim the next currently unlocked job instead of waiting behind another worker:

await db.transactionWith(async (tx) => {
  const job = await tx.getFirstForUpdate(
    JobModel,
    { status: "PENDING" },
    ["id", "payload"],
    "createdAt ASC",
    { skipLocked: true }
  );

  if (job) {
    await tx.update(JobModel, { status: "PROCESSING" }, { id: job.id });
  }
});

getAllForUpdate() has the same lock options after its limit argument. These are row/index-record locks, not field locks: selecting only id and payload still locks the complete physical row. SKIP LOCKED deliberately returns an inconsistent view and is intended for queue-like access, not general reporting.

• PostgreSQL, MySQL/InnoDB, MariaDB/InnoDB, and Oracle support these methods. SQLite rejects them explicitly.
• SKIP LOCKED requires MySQL 8.0+ or MariaDB 10.6+; older or unrecognized server versions fail instead of silently falling back to a waiting lock.
• Under MySQL/MariaDB REPEATABLE READ, range predicates may take next-key or gap locks as well as record locks.
• Oracle applies a logical limit by fetching only the required rows from a transaction-owned cursor because its row-limiting clause cannot be combined with FOR UPDATE.
• At PostgreSQL READ COMMITTED, a waiting locking query with ORDER BY can return rows whose current ordering-column values appear out of order if those values changed while the query waited.

Two transactions can run concurrently on pooled drivers (MySQL, MariaDB, PostgreSQL, Oracle) because each transaction() call checks out a dedicated connection:

const [txA, txB] = await Promise.all([db.transaction(), db.transaction()]);
// txA and txB operate on separate connections — neither sees the other's uncommitted rows
await txA.commit();
await txB.commit();

Legacy shared-instance pattern — begin() / commit() / rollback()

begin() acquires a shared connection on the db instance. Only one transaction can be active at a time with this pattern.

await db.begin();
try {
  await db.insert(OrderModel, order);
  await db.commit();
} catch (err) {
  await db.rollback();
}

Savepoints

Savepoints allow partial rollback within a transaction:

await db.begin();
await db.insert(OrderModel, order);
const sp = await db.savepoint();          // mark a point
await db.insert(OrderLineModel, line);
await db.rollbackToSavepoint(sp);         // undo only the line insert
await db.commit();                        // keep the order insert

Oracle note: releaseSavepoint() is a no-op on Oracle — Oracle releases savepoints implicitly on commit.

Schema Versioning and Upgrades

SmartDB has a built-in schema versioning system. On every startup it checks the installed version of each registered module against the SQL upgrade scripts on disk and automatically applies any that are missing — no migration runner required.

How it works

1. On first startup the init script is executed and a row is inserted into smart_db_version.
2. On subsequent startups each update script whose sequence number is greater than the stored version is applied in order.
3. After all scripts have run the stored version is updated to match the highest applied sequence number.

If a script fails, the upgrade is rolled back and the database transitions to ERROR state. The application never reaches READY with a partially-applied migration.

Script naming convention

Scripts live in a single flat directory. The filename encodes the module name and sequence number:

sql/
  my-app-init.sql          # executed once on first startup
  my-app-update.001.sql    # applied when stored sequence < 1
  my-app-update.002.sql    # applied when stored sequence < 2
  my-app-update.003.sql    # ...

Sequence numbers must be exactly three digits, zero-padded (001–999).

Init script

The init script creates the application schema. It must insert a row into smart_db_version as the last step — SmartDB uses the presence of that row to confirm the script completed successfully:

-- my-app-init.sql

drop table if exists users;
create table users (
    usr_id     serial primary key,
    usr_name   varchar(100) not null,
    usr_email  varchar(200)
);

insert into smart_db_version
    (ver_sequence, ver_module, ver_version, ver_sub_version, ver_revision, ver_release_type)
values
    (0, 'my-app', '1', '0', '0', null);

Update scripts

Each update script applies one incremental change. SmartDB records the new sequence after the script succeeds; update scripts must not update smart_db_version themselves:

-- my-app-update.001.sql

alter table users add column usr_created_at timestamp default current_timestamp;

MySQL and Oracle can implicitly commit DDL. Write idempotent update scripts so an interrupted upgrade can safely run again.

Compound MySQL/MariaDB statements

MySQL/MariaDB init and update scripts may use the client-style DELIMITER directive for triggers, functions, and procedures whose bodies contain semicolons:

DELIMITER $$

create or replace procedure rebuild_summary()
begin
    delete from account_summary;
    insert into account_summary select * from current_account_summary;
end;$$

DELIMITER ;

DELIMITER must appear between complete statements and must be followed by one non-whitespace literal token. While a custom delimiter is active, semicolons do not split statements. The custom delimiter must be the last token on its line. DELIMITER ; restores normal semicolon splitting.

SmartDB's shared Oracle and MySQL/MariaDB script parser supports Oracle-style / as a legacy block terminator only when it is the sole non-whitespace token on a line. Inline slashes, including division operators, are never treated as statement boundaries. The parser keeps delimiters inside quoted values and comments literal, tracks nested compound blocks, and supports Oracle alternative quotes such as q'[value;with;semicolons]'.

Native PostgreSQL scripts

PostgreSQL scripts bypass SmartDB's statement parser. The PostgreSQL driver passes the complete file to the server through pg's simple-query protocol, allowing PostgreSQL itself to parse multiple statements and untagged or tagged dollar-quoted bodies:

do $block$
begin
    perform rebuild_summary();
    perform refresh_statistics();
end;
$block$;

psql --single-transaction is not used by SmartDB: it is a psql client feature, while execScript() already starts a transaction when the caller has not started one, commits after the complete script succeeds, and rolls back on failure. A caller-owned transaction remains active for the caller to resolve. Scripts should not contain their own transaction-control statements.

Only SQL understood by the PostgreSQL server is supported. psql commands such as \i, \set, and \copy, and dump files containing inline COPY ... FROM STDIN data, require the psql client or the PostgreSQL COPY protocol.

Native execution cannot skip a failed DROP and resume at the next statement. Use PostgreSQL's guarded DROP ... IF EXISTS forms. Passing ignoreDropError: true to the PostgreSQL driver is rejected.

Native SQLite scripts

SQLite scripts bypass SmartDB's statement parser. The SQLite driver passes the complete file to better-sqlite3 Database.exec(), allowing SQLite itself to parse compound triggers and their internal semicolons:

create trigger account_ai
after insert on account
begin
    update account_summary set total = total + new.amount;
    insert into account_audit(message) values ('account;inserted');
end;

No slash or custom delimiter is required. The same script can be executed by the SQLite command-line client with .read script.sql.

SmartDB wraps native SQLite script execution in a transaction when the caller has not already started one. A failed script rolls back that owned transaction; a caller-owned transaction remains active for the caller to resolve. Scripts should not contain their own transaction-control statements.

Native execution cannot skip a failed DROP and resume at the next statement. Use SQLite's guarded forms such as DROP TABLE IF EXISTS, DROP VIEW IF EXISTS, DROP INDEX IF EXISTS, and DROP TRIGGER IF EXISTS. Passing ignoreDropError: true to the SQLite driver is rejected.

Registering a module

Pass module and sqlFilesDirectory in the driver options:

import { SmartDbBetterSqlite3 } from "@egi/smart-db/drivers/smart-db-better-sqlite3";

const db = new SmartDbBetterSqlite3(
  { filename: "./app.db" },
  {
    module: "my-app",
    sqlFilesDirectory: "./sql",
    onReady: (db, err) => {
      if (err) console.error("startup failed", err);
    },
  }
);

await db.databaseReady();

Multiple modules can be declared as an array — they are initialized in order:

new SmartDbBetterSqlite3(config, {
  module: ["core-module", "feature-a", "feature-b"],
  sqlFilesDirectory: "./sql",
});

Inspecting installed versions

const versions = db.getModuleVersions();
// [{ module: "my-app", versionString: "1.1.0", sequence: 1 }, ...]

To inspect the init and update scripts completed during the latest database initialization attempt, wait for initialization and call getExecutedUpdateScripts():

await db.databaseReady();

const scripts = db.getExecutedUpdateScripts();
// [
//   { module: "my-app", sequence: 0, scriptName: "my-app-init.sql" },
//   { module: "my-app", sequence: 1, scriptName: "my-app-update.001.sql" }
// ]

The list is reset for each initDb() attempt and contains SmartDB core and application-module scripts. An init script is included only after SmartDB verifies its module version row; its reported sequence is the value established by that row. An update script is included only after its SQL and version sequence have both been recorded successfully. If a later update fails, the successfully completed scripts remain available and the failed script's module, sequence, and filename are available in db.lastError.info.

Each module may have only one update script for a given sequence. SmartDB rejects duplicate sequence filenames before executing pending updates because their order and version history would otherwise be ambiguous.

Controlling upgrade behaviour

| Option | Effect | |-------------------------|----------------------------------------------------------------------------| | skipAutoUpgrade: true | Connect and load models but do not run any upgrade scripts | | connectOnly: true | Establish a connection only — no schema init at all | | delayInit: true | Skip initDb() in the constructor; call db.initDb() manually when ready |

Schema Extraction (extract-db-api)

extract-db-api is a CLI tool that connects to a live database, reads its schema, and generates ready-to-use TypeScript model files — one per table or view. You never write model boilerplate by hand.

Generated models expose relation metadata through both MyModel.isBaseTable and new MyModel().isBaseTable. These getters return true for base tables and false for views. Models created manually or by an older generator inherit AbstractModel's backward-compatible true default.

During the compatibility release, extract-db-api and extract-db-api-legacy run the frozen legacy interface. The redesigned, strictly validated interface is available as extract-db-api-next; see docs/extract-db-api-migration.md.

Important: Re-run extract-db-api after every @egi/smart-db upgrade, not only after schema changes. Generated base classes are tightly coupled to library internals and will silently break when the library version advances without regeneration.

Basic usage

The last positional argument is the output directory where model files are written.

# SQLite — pass the .db file path directly
extract-db-api --database ./app.db src/models

# PostgreSQL
extract-db-api --database postgres://localhost:5432/mydb \
  --username myuser --password secret src/models

# MySQL
extract-db-api --database mysql://localhost:3306/mydb \
  --username myuser --password secret src/models

# Oracle
extract-db-api --database oracle://localhost:1521/XEPDB1 \
  --username hr --password secret src/models

Credentials and database names can also be supplied through environment variables so they are not visible in shell history:

| Variable | Used for | |-----------------------------|------------------------------| | SQLITE3_DB | SQLite file path | | MYSQL_DB | MySQL connection string | | PG_DB | PostgreSQL connection string | | ORACLE_SID | Oracle connect string | | MYSQL_USER / MYSQL_PASS | MySQL credentials | | PG_USER / PG_PASS | PostgreSQL credentials | | ORA_USER / ORA_PASS | Oracle credentials |

What is generated

For every table and view the tool emits a .ts file containing a typed model class with:

• A ModelAttributeMap mapping camelCase property names to the actual column names
• Typed getters and setters for every column (types inferred from the database schema)
• All required AbstractModel lifecycle methods (clone, from, getTableName, etc.)

src/models/
  users-model.ts
  orders-model.ts
  order-lines-model.ts
  smart-db-dictionary.ts   # dictionary registering all generated models

The dictionary file must be passed as smartDbDictionary in the driver options so SmartDB can resolve model metadata at runtime:

import { SmartDbDictionary } from "./models/smart-db-dictionary";

new SmartDbBetterSqlite3(config, {
  module: "my-app",
  sqlFilesDirectory: "./sql",
  smartDbDictionary: SmartDbDictionary,
});

npm test runs the self-contained unit and SQLite integration suites. Run npm run test:external-integration to provision isolated disposable MySQL, MariaDB, PostgreSQL, and Oracle namespaces and execute the complete live driver contract. The system-catalog extraction scripts use the same disposable stack:

npm run extract:mysql:api
npm run extract:postgres:api
npm run extract:oracle:api

These commands use agents/multi-db-test/bin/tdb from the project root; they do not use fixed development database credentials.

Options reference

| Option | Description | |---------------------------------|---------------------------------------------------------------------------------------------------------------------| | --database <url> | Connection string or file path. Scheme determines the driver (sqlite3://, mysql://, postgres://, oracle://) | | --type <driver> | Force driver type when it cannot be inferred from the connection string | | --username <user> | Database username (overrides value embedded in --database) | | --password <pass> | Database password | | --schema <name> | Database/schema name (overrides value embedded in --database) | | --owner <name> | Oracle: restrict extraction to this schema owner | | --tables <t1,t2> | Extract only the listed tables (comma-separated) | | --additional-tables <t1,t2> | Include extra tables on top of the full schema scan | | --omit-table-prefix <prefix> | Strip a common table prefix from generated class names (usr_ → class name without Usr) | | --table-prefix <prefix> | Prepend a prefix to all generated class names | | --swap-prefix <old:new,...> | Replace specific table prefixes in class names | | --dictionary-prefix <prefix> | Prefix for the generated dictionary class name | | --class-suffix <suffix> | Override the default Model class suffix | | --filename-style <style> | Output filename casing: kebab (default), camel, pascal, snake | | --separate-api | Emit a separate *-api.ts file containing only the interface and type definitions | | --ignore-virtual-tables | Skip SQLite virtual tables | | --ignore-field-prefix <p1,p2> | Strip column prefixes from generated property names | | --view-indicator-prefix <s> | Mark views by a prefix on the table name | | --view-indicator-suffix <s> | Mark views by a suffix on the table name | | --create | Create the database file if it does not exist (SQLite only) | | --lossless-numbers <list> | Opt generated fields into exact numbers, bigint, or both categories | | --lossless-number-one | Treat NUMBER(1) and MySQL/MariaDB TINYINT(1) as lossless decimals; requires numbers | | --audit-sqlite-lossless-storage | Report SQLite storage-class counts and reject REAL/BLOB rows for marked fields | | --allow-unsafe-sqlite-numeric-affinity | Migration-only override for non-TEXT fractional decimal affinity; requires the audit |

Lossless numeric fields

Lossless generation is opt-in. Without the options above, generated output and runtime number behavior remain unchanged. With numbers, constrained NUMBER/NUMERIC/DECIMAL fields with a fractional scale or at least 15 integral digits use string backing. With bigint, wide integer types use string backing. Generated property getters return Decimal or primitive bigint, but getValue(), getPlainObject(), database binds, and JSON remain normalized strings. Install decimal.js when decimal models are generated and import the calculation facade from @egi/smart-db/lossless-numbers.

Primary keys intentionally remain JavaScript numbers. Any numeric declaration is accepted, but every actual key is checked at runtime and must be a finite safe integer. SQLite fractional lossless columns must have TEXT affinity; a normal DECIMAL(p,s) declaration has NUMERIC affinity and is rejected unless the audited migration-only override is explicit.

Canonical decimal strings always use the declared scale, expand exponents, remove redundant leading zeroes, and normalize negative zero. Setters accept a string or the matching Decimal/bigint calculation value; plain JavaScript numbers are rejected because their precision may already be lost. Unconstrained NUMBER, NUMERIC, and DECIMAL declarations intentionally remain ordinary JavaScript numbers.

import {Decimal, DecimalRoundingMode} from "@egi/smart-db/lossless-numbers";

const amount = invoice.amount;                 // Decimal; read once in hot code
invoice.amount = amount.plus("0.10");          // backing value becomes a string
invoice.setDecimalValue(
    "amount",
    new Decimal("1.245"),
    DecimalRoundingMode.ROUND_HALF_DOWN
);
const wireValue = invoice.getValue("amount"); // e.g. "1.24"

The default rounding mode is ROUND_HALF_UP; all decimal.js rounding modes can be selected per setDecimalValue call. Negative declared scales are normalized with the same rule (for example, scale -2 rounds to hundreds). Raw query(sql) results are deliberately unchanged because they have no model metadata with which to apply the lossless contract.

Logging

import { SmartSeverityLevel } from "@egi/smart-db";

new SmartDbBetterSqlite3(config, {
  logOptions: {
    level: SmartSeverityLevel.Info,
    dbLogging: true,   // also persist logs to smart_db_log table
  },
  silent: true,        // suppress all console output (Fatal only)
});

Browser Usage

The browser entry point exports AbstractModel, SmartDbDictionary, browser-safe interfaces and enums, SQL descriptor helpers, and the SmartDbPattern validator. It excludes database drivers and other Node.js APIs. Bundlers automatically select the ESM browser build:

import { AbstractModel, FIELD, IN, SmartDbDictionary, SmartDbPattern } from "@egi/smart-db";

Date / Time Handling

SmartDB applies a consistent timezone rule across all drivers, controlled by the dateTimeMode option:

| Mode | TIMESTAMP columns | DATE / DATETIME columns | |----------------------|--------------------------------|--------------------------------| | "rule" (default) | stored as UTC | stored as local time | | "utc" | stored as UTC | stored as UTC | | "local" | stored as local time | stored as local time | | "none" | no conversion (driver default) | no conversion (driver default) |

// Store all dates in UTC
new SmartDbMysql(config, { dateTimeMode: "utc" });

// Disable conversion entirely (e.g. when the DB session already handles it)
new SmartDbOracle(config, { dateTimeMode: "none" });

The MySQL driver issues SET time_zone = '+00:00' on connect for "rule" and "utc" modes. For "local" and "none" it does not, preserving the server's default timezone.

Date Utilities

import { tools } from "@egi/smart-db";

tools.toDbDate(new Date())                     // "2024-03-15 14:30:00"
tools.toDbTimestamp(new Date())                // "2024-03-15 14:30:00.000"
tools.toDate("2024-03-15 14:30:00")            // Date object

Options Reference

| Option | Type | Description | |-----------------------|----------------------------|----------------------------------------------------------------------------| | module | string \| string[] | Module name(s) for schema versioning | | sqlFilesDirectory | string | Directory for SQL upgrade scripts | | onReady | (db, err?) => void | Callback when DB reaches READY or ERROR | | delayInit | boolean | Skip initDb() in constructor; call manually | | connectOnly | boolean | Connect without running upgrade scripts | | skipAutoUpgrade | boolean | Skip schema version checks on init | | smartDbDictionary | typeof SmartDbDictionary | Register model dictionaries | | silent | boolean | Suppress all logging below Fatal | | needsExplicitEscape | boolean | Enable explicit escaping (Oracle) | | patternDateFormat | string | Date-to-text format for LIKE/REGEXP; default YYYY-MM-DD | | patternTimeFormat | string | Time-to-text format for LIKE/REGEXP; default HH:MM:SS | | dateTimeMode | SmartDbDateTimeMode | Date/time timezone rule — see Date / Time Handling | | logOptions | SmartLogOptions | Logging configuration |

Migration Guide

Upgrading from V4 to V5

V5 is a breaking major release. Most of its changes fail loudly, but the new LIKE case sensitivity on SQLite and MySQL/MariaDB changes query results without raising errors. Test the migration in a non-production environment and complete every applicable step below. CHANGELOG.md lists every change.

As after every upgrade, regenerate the models before compiling:

npm install @egi/smart-db@^5
npx extract-db-api --database <connection> src/models

Review every LIKE query on SQLite and MySQL/MariaDB. LIKE(), NOT_LIKE(), and plain string WHERE values that contain % or _ are now case-sensitive, and on MySQL/MariaDB also accent-sensitive. Queries that relied on the native case-insensitive behavior return fewer rows. Switch them to ILIKE() or NOT_ILIKE(), which fold ASCII letters only:

// V4 on SQLite or MySQL/MariaDB: also matched "Smith"
await db.getAll(UserModel, { name: "%smith%" });

// V5: case-insensitive on every database
await db.getAll(UserModel, { name: ILIKE("%smith%") });

Use EQ() for exact values that contain % or _. A plain string such as "[email protected]" is still a LIKE pattern. EQ() compares exactly and skips pattern validation:

await db.getFirst(UserModel, { email: EQ("[email protected]") });

Check backslashes in patterns. Backslash now escapes %, _, or another backslash on every database; SQLite and Oracle previously treated it as a literal character. Any other backslash sequence throws, including in plain WHERE values such as "C:\data_1".

Replace LITERAL() pattern operands with bound pattern strings. Patterns longer than 512 characters are rejected.

Pass strings to LIKE() and NOT_LIKE(). They no longer accept numbers, dates, or null; write LIKE("42%") for a numeric field. Invalid patterns now throw when the helper is called, not when the query runs.

Check pattern queries on non-text fields. Dates are formatted with patternDateFormat (default YYYY-MM-DD) instead of the database's own conversion, so patterns no longer match the time part of DATETIME or TIMESTAMP columns, and Oracle ignores NLS_DATE_FORMAT. Decimals are rendered at their declared scale (12.50), and boolean fields need an exact true or false. See SQL Helper Functions.

Configure Oracle thick mode explicitly. thickMode: true no longer derives the client library path from ORACLE_HOME. On Windows and macOS, set oracleClientLibDir; on Linux, make the libraries available through ldconfig or LD_LIBRARY_PATH before Node.js starts. See Oracle.

Call the function returned by SQLite registerTransaction(). It used to return a boolean. Now it returns the wrapped function, and each call runs atomically:

const transfer = db.registerTransaction((from: number, to: number, amount: number) => {
  // ...
});

transfer(1, 2, 100);

Adjust error handling. getTableInfo() rejects with a SmartError whose code is TableNotFound, and getSync(), insertSync(), updateSync(), deleteSync(), and execScriptSync() throw a SmartError instead of a string on MySQL/MariaDB, PostgreSQL, and Oracle. On MySQL/MariaDB, getTableInfo().name no longer includes the schema.

Narrow call() results. call() resolves with unknown instead of any; check or cast the result before using it.

Smaller changes. tools.millisToTimeString(ms, includeMillis, true) prints complete days separately (1d0h00m00s instead of 24h00m00s). The driver-level executeBulkRangeWithContinuation() statics were removed; use insertBulk() or insertStream().

Notable additions in V5 include regular-expression helpers (REGEXP_LIKE() and its variants), ILIKE() and NOT_ILIKE(), pattern matching on typed fields, browser-side pattern validation with SmartDbPattern, and locking reads with getFirstForUpdate() and getAllForUpdate().

Upgrading from V3 to V4

V4 is a breaking major release. Do not upgrade it as a routine dependency refresh. Test the migration in a non-production environment and complete every applicable step below.

Upgrade Node.js first. V4 requires Node.js 22 or newer. The supported optional drivers also moved to newer major/minor ranges; review the peer versions in package.json before installing.

Regenerate every generated model. V3-generated model files are not compatible with V4. Upgrade the package and drivers, then run your extract-db-api command before compiling the application:

npm install @egi/smart-db@^4
npx extract-db-api --database <connection> src/models

Replace positional getPlainObject() booleans. In V3 the boolean meant includeVirtuals; in V4 it means omitVirtuals, and virtual TypeScript-style fields are included by default. Use the options object so the intent is unambiguous:

// V3: include virtual fields
model.getPlainObject(true);

// V4 equivalent
model.getPlainObject({ omitVirtuals: false });

// V4: explicitly omit virtual fields
model.getPlainObject({ omitVirtuals: true });

Review direct driver access. MySQL/MariaDB and Oracle now use connection pools. getDb() returns the underlying pool rather than one connection. Code calling driver-specific connection methods must check out and release a connection, or use SmartDB's query and transaction APIs.

Move transactional work to scoped handles. Prefer transactionWith() or execute all statements through the handle returned by transaction(). The legacy begin() API keeps one shared transaction on the database instance and rejects concurrent begin() calls on pooled drivers.

Update SQL-builder integrations. getLastBuildData() and buildSelectStatement() now return SmartDbSqlBuilder; the old SmartDbSqlBuildData class was removed. Code using these low-level APIs must migrate to the builder's sql, values, and results() APIs. The former SmartDb.toDate() helper is now tools.toDate().

Simplify schema update scripts. V4 records each successfully applied update sequence itself. Remove updates to smart_db_version from *-update.NNN.sql scripts. Keep the version-row insert at the end of each *-init.sql file.

Notable additions in V4 include PostgreSQL support, pooled and scoped transactions, transactionWith(), savepoints, joins, bulk insert/update, subquery helpers, richer SQL expressions, structured error codes, browser ESM output, Luxon DateTime support, and safer recoverable schema upgrades. See CHANGELOG.md for the release summary.

Upgrading from V2 to V3

V3 is a breaking release. The following changes are required:

Regenerate all models. V2-generated model files are not compatible with V3. After upgrading the package, run extract-db-api immediately before doing anything else:

npm run extract:db:api

Update driver import paths. The driver entry points have moved to sub-path exports:

// V2
import { SmartDbBetterSqlite3 } from "@egi/smart-db";

// V3
import { SmartDbBetterSqlite3 } from "@egi/smart-db/drivers/smart-db-better-sqlite3";
import { SmartDbMysql }          from "@egi/smart-db/drivers/smart-db-mysql";
import { SmartDbOracle }         from "@egi/smart-db/drivers/smart-db-oracle";

Sync methods are currently available for SQLite-only. Calling getSync, insertSync, updateSync, deleteSync, or execSync on a MySQL or Oracle instance now throws. Replace all sync calls on those drivers with their async equivalents.

Review constructor options. SmartDbOptions has new and renamed fields in V3. Compare your constructor calls against the Options Reference above.