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.

V4 is a breaking release. Read Upgrading from V3 to V4 before updating. V4 requires Node.js 22 or newer, and every project must regenerate its SmartDB models after installing the new version.

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 pg-copy-streams # PostgreSQL
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) => { /* ... */ } }
);

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, IN, 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%"),
  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 | | NOT_LIKE(v) | NOT LIKE v | | 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 |

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. PostgreSQL streams use COPY FROM STDIN; requesting continueOnError 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
}

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, and SQL descriptor helpers. It excludes database drivers and other Node.js APIs. Bundlers automatically select the ESM browser build:

import { AbstractModel, FIELD, IN, SmartDbDictionary } 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) | | dateTimeMode | SmartDbDateTimeMode | Date/time timezone rule — see Date / Time Handling | | logOptions | SmartLogOptions | Logging configuration |

Migration Guide

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.