One fluent API for your data. Run SQLite while you build, PostgreSQL in production —
the same call works on both — then migrate between engines with a single command.

---

Quick start

npm install sql-switch better-sqlite3

import{sqlSwitch}from'sql-switch';constdb=sqlSwitch();awaitdb.connect({db: 'local',local: {dataDir: './data/databases',wal: true},collector: {enabled: true,time: 3000},});// write — queued in RAM, flushed in bulk every 3sawaitdb.schema('antinuke').table('settings').key('guild_123').set({strict: true});// read — a queued write is visible to the next get() on the same keyconstsettings=awaitdb.schema('antinuke').table('settings').key('guild_123').get();// need it on disk right now? bypass the collectorawaitdb.schema('antinuke').table('settings').key('guild_123').set({strict: true}).force();

That is the whole surface: schema → table → key → operation. The same chain drives every engine.

Same code, production engine

Declare both engines up front and pick the active one with db — an env var is the usual selector. Only the config changes; not one line of your data code does:

awaitdb.connect({db: process.env.DB_MODE??'local',// 'local' | 'cloud'local: {dataDir: './data/databases',wal: true},cloud: {connectionString: process.env.DATABASE_URL,pool: {max: 5,statementTimeout: 30_000},},collector: {enabled: true,time: 3000},});

Both declared blocks are validated at connect(), so a bad cloud connectionString is caught at boot even while you're still running local. Flip engines at runtime — no data move, just repoint the client — with db.reconnect('cloud'); use db.swapEngine() when the rows need to travel too.

In local mode each schema is its own .db file (./data/databases/antinuke.db, WAL on by default); in cloud mode each schema is a Postgres logical schema (antinuke.settings). Your code never sees the difference.

statementTimeout (default 30s, 0 disables) caps a single operation. Without one, a query that never answers holds a pool connection for the life of the process — max of those and every later read blocks with no error at all.

The drivers are optional peer dependencies, loaded lazily the first time you connect() in that mode — a SQLite-only app never pulls in pg, and vice versa. Install just the one you run:

npm install better-sqlite3 # local mode
npm install pg # cloud mode

_{Prefer the branded name? @creative-softworks/sql-switch re-exports this package unchanged.}

How it works

• Write collector — buffers writes in RAM and flushes them in bulk on an interval, collapsing repeated writes to the same key inside the window. .force() bypasses it for an immediate write.
• Circuit breaker — caps pending writes at 5000 keys and trips to read-only on a Postgres outage instead of crashing, then heals itself once the database answers again.
• Exit flush — SIGINT, SIGTERM and beforeExit all drain the buffer on the way out; the library never calls process.exit() for you.
• Engine swap — moves data both directions, a chunk at a time, journalled so an interrupted run resumes deterministically.

Enumerate, scan & convenience helpers

Beyond get/set/delete, a table streams and a key has the usual key-value sugar. The enumeration methods return async iterators — a scan of a huge table never lands the whole thing in RAM (a cursor on SQLite, keyset-paged on Postgres), so peak memory is one row/chunk.

consttable=db.schema('economy').table('balances');// stream keys / values / entries in ascending id order — break to stop earlyforawait(const[id,balance]oftable.entries())console.log(id,balance);forawait(constidoftable.keys({prefix: 'guild-'}))console.log(id);// startsWith(prefix) is sugar for entries({ prefix }); the prefix is bound, never a patternforawait(const[id,bal]oftable.startsWith('guild-'))console.log(id,bal);consthowMany=awaittable.count({prefix: 'guild-'});// counted in the DB, rows never materializedawaittable.deleteAll({prefix: 'guild-'});// wipe a prefix (or the whole table)

constkey=db.schema('economy').table('balances').key('user_1');awaitkey.has();// true if a value is stored (or buffered) for this keyawaitkey.add(50);// numeric increment (a missing key counts as 0) → new totalawaitkey.sub(10);// decrementawaitkey.push('a','b');// array helpers: push / unshift / pop / shift / pullawaitkey.pull((x)=>x.done);

Enumeration reads committed rows, so a write still sitting in the collector buffer is not visible to a scan yet — await/.force() or close() first for an exact view. The numeric and array helpers are read-modify-write and not atomic: two un-awaited add()s on the same key can read the same base and lose an update. Sequential awaited calls are fine.

Reliability

Defaults are chosen so nothing is silently lost. All of it is configurable.

awaitdb.connect({db: 'cloud',cloud: {connectionString: process.env.DATABASE_URL},collector: {time: 3000,// flush intervalautoRecover: true,// breaker heals itself after an outagerecoverAfter: 10_000,// read-only window before one trial flush decidesflushOnExit: true,// drain on SIGINT / SIGTERM / beforeExithooks: {onStateChange: (state,reason)=>log.warn(`db breaker ${state}`,reason),},},});

Behaviour	What happens
Buffered writes	A queued set() is visible to the next get() on the same key, before the flush.
Postgres outage	Transient failures are retried inside the driver with bounded jittered backoff; a group that still fails goes back in the buffer for the next flush.
Sustained outage	The buffer hits its 5000-key cap and the breaker opens — writes raise DatabaseUnavailableError, reads keep working.
Recovery	After recoverAfter the breaker half-opens; one trial flush closes it again (autoRecover: false keeps it latched until the process restarts).
Shutdown	SIGINT, SIGTERM and beforeExit flush the buffer, then the signal is handed back — the library never calls process.exit() for you.
Unstorable values	undefined, NaN/Infinity, NUL characters, BigInt and circular references throw InvalidValueError at the call, not inside a flush.

Engine swap

Move your data between engines from the terminal or from code.

CLI

npm run db:engine-swap -- --up # local SQLite → production PostgreSQL
npm run db:engine-swap -- --down # production PostgreSQL → local SQLite

Flag	Description
--up / --down	Direction. One is required.
--url <conn>	PostgreSQL connection string. Falls back to DATABASE_URL.
--dir <path>	SQLite data directory. Default ./data/databases.
--keep	Upward only: keep the local .db files instead of deleting them.
--yes	Auto-answer Y to every overwrite prompt (CI / non-interactive).

From code

Anything you leave out is filled in — dataDir defaults to ./data/databases, connectionString to process.env.DATABASE_URL, and missing schemas, tables and directories are created on the target.

import{engineSwap}from'sql-switch';constresult=awaitengineSwap({direction: 'up',// 'up' = SQLite → PostgreSQL, 'down' = the reverseonConflict: 'overwrite',// default 'skip' — nothing is clobbered unless you say soonProgress: (line)=>console.log(line),});console.log(`${result.totalRows} rows across ${result.tables.length} tables`);

Or swap a live DAL and keep using the same object — pending writes are flushed and handles closed first, then it reconnects on the target engine with your existing collector settings:

awaitdb.swapEngine({direction: 'up',onConflict: 'overwrite'});// same db instance, now reading from PostgreSQLawaitdb.schema('antinuke').table('settings').key('guild_123').get();

onConflict also takes a callback if you want to decide per target:

awaitengineSwap({direction: 'up',onConflict: (c)=>c.schema!=='economy',// never clobber economy});

What the migration guarantees

Guarantee	Detail
Memory	Rows stream a chunk at a time in both directions — peak memory is one chunk, not one table.
Atomicity	Each table moves in its own transaction going up; going down the file is built as .tmp and renamed into place.
Resume	Every committed table (or renamed file) is journalled in the data dir, so an interrupted run resumes deterministically. A clean run removes the journal.
Shared databases	Schema/table names this DAL can't address (^[a-zA-Z0-9_-]+$) are skipped and listed in result.skippedNames instead of aborting the run. Nothing else is read or written.
Interruption	SIGINT/SIGTERM stops at the next boundary, sets result.aborted, deletes nothing, then hands the signal back. Run the same swap again to finish it.
Local files	Deleted only when every table landed and no DAL in this process still has the data dir open — a write sitting in a collector buffer is invisible to a migration reading the file.

constresult=awaitengineSwap({direction: 'up'});if(result.aborted)console.warn('stopped early, rerun to resume');if(result.skippedNames.length)console.warn('left alone:',result.skippedNames);

API

Method	Description
db.connect(config)	Initialise the DAL. Call once at startup.
.schema(name)	Select a module schema (maps to a .db file or Postgres schema).
.table(name)	Select a table inside the schema.
.key(id)	Select a key inside the table.
.get<T>()	Read. Returns T | null.
.set(value)	Queue a write (or flush immediately if collector disabled).
.set(value).force()	Bypass the collector, write immediately.
.delete()	Delete immediately. Never queued, even when awaited without .force().
.has()	true if a value is stored or buffered for the key.
.add(n) / .sub(n)	Numeric increment/decrement (missing key = 0). Non-atomic RMW.
.push/.unshift/.pop/.shift/.pull	Array helpers on the value. Non-atomic RMW.
table.keys/.values/.entries	Stream ids / values / [id, value], ascending id order. { prefix } narrows.
table.startsWith(prefix)	Sugar for .entries({ prefix }). Prefix is bound, never a pattern.
table.count(opts?)	Row count, done in the DB (rows never materialized).
table.deleteAll(opts?)	Delete every key (or just those under a prefix).
db.reconnect(target?)	Re-open the current engine (restart / recover a wedged connection), or repoint to the other declared engine ('local'/'cloud') without moving data. Flushes first; fail-safe.
db.swapEngine(options)	Migrate to the other engine & reconnect on it.
db.pendingWrites	Number of writes currently buffered in the collector.
db.close()	Flush pending writes and close all connections.
engineSwap(options)	Standalone engine swap, no DAL instance needed.

Limits

The only hard wall you hit is the database running out of storage. The few non-storage constraints below are deliberate, so they're documented rather than left as surprises.

Limit	Detail
Big integers in values	Values round-trip through JSON, so an integer past 2^53 loses precision (12345678901234567890 reads back as …567000). Store big integers as strings. Only the key is precision-safe.
Binary in values	A Buffer/typed array serializes to { "type": "Buffer", "data": [...] } and comes back a plain object, never a Buffer. Base64-encode binary yourself if you need it back intact.
Unstorable values	undefined, NaN/Infinity, NUL characters, BigInt and circular references are refused up front with InvalidValueError — they can't be stored the same way by both engines.
SQLite schema count	In local mode each schema is one .db file whose handle is cached for the life of the process (no LRU). Keep schemas coarse — one per module (antinuke, economy), not one per entity — or a schema-per-tenant layout hits the OS file-descriptor limit long before disk fills. Put the entity id in the key, not the schema. Cloud mode shares one pool and has no such ceiling.
SQLite writers	WAL allows concurrent readers with one writer. A second writer process waits up to busyTimeout (default 5s) for the lock, then throws SQLITE_BUSY. In-process, one cached handle per schema serializes writes already.

Scripts

npm run build # dual CJS/ESM bundle + per-file .d.ts
npm test# Vitest suite (Postgres files skip themselves without DATABASE_URL)
npm run typecheck # tsc on src/ and scripts/
npm run smoke # fast end to end check against real SQLite files
npm run swap-test # engine swap integration test (needs DATABASE_URL, skips without it)
npm run docs # TypeDoc HTML into /docs
npm run docs:serve # serve /docs on http://localhost:3000

Requirements

• Node.js >= 22.0.0 (tested on the active LTS / current lines, 22 and 24)

License

MIT