SQLite

Connecting

The connection string is a file path, or :memory: for a database that lives only as long as the process. The file is created if it does not exist yet, so there is nothing to provision first.

import { DBType, type DBConfig } from "stabilize-orm";

const dbConfig: DBConfig = {
  type: DBType.SQLite,
  connectionString: process.env.DATABASE_PATH || "./data/app.db",
  retryAttempts: 3,
  retryDelay: 1000,
};

export default dbConfig;

The CLI scaffolds the same file with --type sqlite:

There is nothing to install for this dialect, because neither driver is an npm package — each is part of its runtime. node:sqlite arrived in Node 22.5 behind a flag and is available without one from Node 22.13, which is therefore the version this dialect needs on Node. Bun has shipped bun:sqlite built in throughout.

// An in-memory database is the usual choice for a test suite:
const orm = new Stabilize({
  type: DBType.SQLite,
  connectionString: ":memory:",
});

What the Dialect Changes

SQLite is the dialect the builder departs from least: most of the SQL it emits is the SQLite spelling. What does differ is the return path for writes, the storage classes, and the absence of locking clauses:

Writes and last_insert_rowid()

Instead of a RETURNING clause, create() inserts, asks the connection for the rowid it just assigned, and then reads the row back by id:

-- await userRepo.create({ email: "ada@example.com", name: "Ada" })
INSERT INTO users (email, name) VALUES (?, ?)
SELECT last_insert_rowid() as id
SELECT * FROM users WHERE users.id = ? LIMIT 1

The rowid probe only runs when the database assigned the key. A model whose id is STRING or UUID holds a value the caller supplied, so that value is used directly and only the read-back follows.

bulkCreate() inserts the whole batch in one statement and recovers the ids from the rowid it gets back — which is the last id of the run, with the earlier ones counted backwards from it. MySQL reports the first instead, so the same code cannot share the arithmetic between the two.

Upsert with ON CONFLICT

SQLite supports the conflict target Postgres uses, but its DO UPDATE clause binds the new values as parameters rather than referring to EXCLUDED, so the update payload appears a second time in the parameter list.

-- await userRepo.upsert({ email: "ada@example.com", name: "Ada" }, ["email"])
INSERT INTO users (email, name) VALUES (?, ?)
ON CONFLICT (email) DO UPDATE SET name = ?

The statement returns nothing — the SQLite path emits no RETURNING — so the row is read back afterwards with a SELECT … WHERE email = ? LIMIT 1. It is also read before the write, to decide whether this is an insert or an update, which is what selects the hook pair that runs. The conflict target has to be backed by a unique index, so mark the key column unique: true on the model and let autoMigrate create it.

Row Limiting

limit() and offset() are emitted as written. A bare OFFSET is not accepted here, so an offset with no limit is padded with the largest signed 64-bit value rather than being rejected at the server:

-- repo.find().limit(10).offset(20)
SELECT * FROM users
LIMIT 10 OFFSET 20

-- repo.find().offset(20) -- no limit set
SELECT * FROM users
LIMIT 9223372036854775807 OFFSET 20

-- repo.find().paginate(2, 25) composes the two: offset = (page - 1) * pageSize
SELECT * FROM users
LIMIT 25 OFFSET 25

Storage Classes

SQLite has five storage classes — NULL, INTEGER, REAL, TEXT and BLOB — and no boolean, date, datetime, JSON or UUID type. Those members all land on TEXT or INTEGER, and the ORM does the conversion in JavaScript. BIGINT maps to INTEGER for the same reason INTEGER does: a SQLite integer is already 64-bit.

Two consequences are worth spelling out. A Date is bound as an ISO string rather than handed to the driver as an object — SQLite rejects a Date parameter outright, which is why the time-travel path converts one before binding it. And a boolean column is an INTEGER, so the generated toggle is a CASE expression rather than the NOT col Postgres and MySQL accept. A declared length never reaches the DDL — SQLite's declared type decides type affinity rather than constraining the value — so the limit is enforced by the ORM on write instead.

A SQLite integer is 64-bit, which is wider than a JavaScript number represents exactly, and past that point the two drivers diverge. On Node.js a value beyond Number.MAX_SAFE_INTEGER is returned as a bigint, so it stays exact; bun:sqlite has no equivalent escape hatch and returns the rounded number. Within the safe range both return plain numbers, so ordinary id columns are the same on either runtime — the difference only appears if you actually store integers that large.

Primary Keys and AUTOINCREMENT

An integer id becomes INTEGER PRIMARY KEY AUTOINCREMENT and is assigned by the database, so it must be left out of the payload. A STRING or UUID id is a TEXT primary key the caller supplies:

-- id: DataTypes.INTEGER -- generated
CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "email" TEXT NOT NULL)

-- id: DataTypes.UUID or DataTypes.STRING -- caller supplied
CREATE TABLE IF NOT EXISTS "accounts" ("id" TEXT NOT NULL PRIMARY KEY, "email" TEXT NOT NULL)

The NOT NULL on the second is deliberate. A SQLite primary key that is not an INTEGER PRIMARY KEY may still hold NULL, so required: true is only enforced if the constraint is written out — and the DDL generator writes it.

One Connection and Cached Statements

The other three dialects are pool-backed; this one is a single connection object held for the life of the client. Two things follow from that, both of which are why the client has SQLite-specific branches at all.

First, every statement is prepared once and kept in a cache keyed by the SQL text, so a query that is run repeatedly — and any statement whose values arrive as parameters rather than being interpolated — reuses the compiled statement. Second, a transaction cannot borrow a second connection, because there is no second connection to borrow.

Transactions

BEGIN, COMMIT and ROLLBACK are ordinary statements here, sent on the one connection the client owns. The callback therefore receives the same client you already have rather than a fresh handle, and the ORM marks it as being inside a transaction for the duration so that a repository write inside your callback does not open a second one.

await orm.transaction(async (txClient) => {
  const userRepo = orm.getRepository(User);
  const profileRepo = orm.getRepository(Profile);

  const user = await userRepo.create({ email: "ada@example.com" }, {}, txClient);
  await profileRepo.create({ userId: user.id }, {}, txClient);
});

One implementation note, since it changes what you can rely on: the driver's own transaction helper is not used. bun:sqlite's db.transaction() commits as soon as the callback returns, and an async callback returns a pending promise at its first await — the commit would land before the work finished, and a later throw would roll nothing back. node:sqlite offers no such helper at all. The statements are issued explicitly on either driver, so a failed callback really does undo every write inside it.

Limitations