@egi/smart-db

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                # 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; }
}

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 });

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,
  onProgress: (done, total) => console.log(`${done}/${total}`),
});

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

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.

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 }, ...]

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.

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,
});

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)

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.