dbgate-sqlite-dumper

Standalone, client-agnostic SQLite dump and restore library for Node.js.

Produces the same plain-SQL dump the sqlite3 shell's .dump command writes, and restores it back — entirely through a database handle. No sqlite3 shell, no external process is ever invoked. Framework-independent: it does not depend on DbGate internals and works outside DbGate.

• Node.js >= 20, ESM and CJS builds, full TypeScript types
• better-sqlite3 is an optional peer dependency, reachable only through the separate dbgate-sqlite-dumper/better-sqlite3 entry point — the core never imports a driver
• Streaming both ways: a multi-gigabyte database dumps, and a multi-gigabyte .sql file restores, in constant memory

Two-way native compatibility

Both directions are proven by automated tests against the real sqlite3 shell, not assumed:

• Dumps produced by this library restore with the native shell.

  sqlite3 copy.db < dump.sql

• Dumps produced by the native .dump restore with this library.

  await restoreSqlDump({ connection, source: createReadStream('native-dump.sql') });

There is no custom format, no archive wrapper, and no metadata sidecar. A .sql file this package writes is byte-identical to sqlite3 database.db .dump — and to its --data-only, --preserve-rowids, --newlines and --nosys variants — with one exception that is a property of SQLite itself: the digits past the 17th in a REAL literal come from SQLite's own printf("%!.20g"), which changed between releases, so a driver bundling a different SQLite than the shell may spell the same double differently (0.100000000000000005 vs 0.1000000000000000056). Both parse to the identical value; the tests compare such lines as doubles and every other byte exactly. See docs/native-compatibility.md.

Every path in the matrix ends by reading back the restored database and deep-comparing its schema and every table's rows — text compared as bytes — against the source.

Path	Tested
this library → native sqlite3 restore	✅
native .dump → this library's restore	✅
this library → this library	✅
native .dump → native restore (baseline)	✅

Install

npm install dbgate-sqlite-dumper
# optional, for the bundled better-sqlite3 adapter:
npm install better-sqlite3

Quick start

Dump

import { createWriteStream } from 'node:fs';
import { dumpSqlite } from 'dbgate-sqlite-dumper';
import { connectBetterSqlite3 } from 'dbgate-sqlite-dumper/better-sqlite3';

const { connection, close } = await connectBetterSqlite3('shop.db', { readonly: true });

try {
  const result = await dumpSqlite(
    connection,
    { mode: 'full' },
    createWriteStream('shop.sql'),
    event => console.log(event.phase, event.objectName ?? '', event.bytesWritten ?? ''),
  );

  console.log(`${result.rowsExported} rows in ${result.statementsWritten} statements`);
  for (const warning of result.warnings) {
    console.warn(`[${warning.severity}] ${warning.code}: ${warning.message}`);
  }
} finally {
  await close();
}

The result is restorable by sqlite3 shop_copy.db < shop.sql.

Restore

import { createReadStream } from 'node:fs';
import { restoreSqlDump } from 'dbgate-sqlite-dumper';
import { connectBetterSqlite3 } from 'dbgate-sqlite-dumper/better-sqlite3';

const { connection, close } = await connectBetterSqlite3('shop_copy.db');

const result = await restoreSqlDump({
  connection,
  source: createReadStream('shop.sql'),
  progress: event => console.log(event.phase, event.currentObject, event.rowsRestored),
});

console.log(`${result.statementsExecuted} statements, ${result.rowsRestored} rows`);
for (const error of result.errors) {
  console.error(
    `statement ${error.statementIndex} (line ${error.location.startLine}): ${error.message}`,
  );
  console.error(`  ${error.sqlPreview}`); // truncated, key-redacted
  console.error(`  code=${error.sqliteError?.code}`);
}
await close();

source accepts a string, a Buffer, a Readable, or any AsyncIterable of text or Buffer chunks. Input is parsed incrementally, so restoring a multi-gigabyte dump does not read it into memory.

Using a database you already have open

import Database from 'better-sqlite3';
import { fromBetterSqlite3 } from 'dbgate-sqlite-dumper/better-sqlite3';

const connection = fromBetterSqlite3(new Database('shop.db'));

A database you supply is borrowed and never closed. See docs/better-sqlite3-adapter.md; any other driver works by implementing the small SqliteConnection interface.

Public API

Function	Purpose
dumpSqlite(connection, options, output, onProgress?, signal?)	Full pipeline: snapshot → introspect → plan → render → stream rows
restoreSqlDump({ connection, source, options?, progress?, signal? })	Streaming parser → statements → database
introspectSqlite(connection, options?, signal?)	Normalized SqliteDatabase + version/capabilities/diagnostics
inspectDumpArchive(database, options?)	Pure planning → ordered, verified ArchiveEntry[]
renderPlainSql(request)	Pure model → plain SQL text (never touches the database)
exportTableDataAsInserts(request)	Stream one table's rows as INSERT statements
preflightRestore(request)	Target version, modules, conflicts, and what the dump needs
isSqliteDump(sample)	Recognizes native and this package's dumps
parseSqlStatements(sql) / streamSqlStatements(source)	The shell-compatible statement splitter, usable on its own
isCompleteStatement(sql)	A port of sqlite3_complete()
beginSqliteDumpSession(connection, options?)	The read snapshot, on its own
checkTargetCompatibility(database, target)	Which features a target cannot accept
fromBetterSqlite3(database)	Adapter (from dbgate-sqlite-dumper/better-sqlite3)
connectBetterSqlite3(filename, options?)	Convenience opener (from dbgate-sqlite-dumper/better-sqlite3)

Each stage is independently usable: inspectDumpArchive and renderPlainSql are pure functions of the model and need no connection at all.

Why a real parser, not split(';')

Splitting a SQLite script on semicolons breaks on the first trigger in any dump:

CREATE TRIGGER audit_books AFTER INSERT ON books BEGIN
  INSERT INTO audit VALUES ('book ' || new.title || '; END');
  UPDATE authors SET books = books + 1 WHERE id = new.author_id;
END;

This package splits a script exactly where the sqlite3 shell does, by porting the two pieces of SQLite that decide it: sqlite3_complete() — the state machine that keeps a trigger body's inner statements together — and the shell's own line rules:

• GO and / lines end a statement, as in the shell (SQL Server and Oracle habits).
• Dot-commands are the shell's, not SQL. Presentational ones (.mode, .headers, .print) are skipped with a warning; ones that change the database (.read, .import, .open) are refused with a precise error rather than silently ignored.
• A \r before a newline is dropped, even inside a string literal — the shell's line reader does that, so a CRLF file restores identically.
• Bytes that are not valid UTF-8 survive. The native .dump writes such text raw; a JavaScript driver cannot pass it through a string, so the literal is rewritten to the exact-byte CAST(X'...' AS TEXT).

Boundary correctness is not assumed: the parser's output is asserted identical at every chunk size and every single split point, and a suite of hand-written scripts is run through both the shell and this package, which must leave identical databases.

Documentation

Document	Contents
docs/native-compatibility.md	The two-way promise, what is reproduced and why, native quirks
docs/dump-api.md	dumpSqlite options, modes, consistency, progress, batching
docs/restore-api.md	restoreSqlDump, the parser, session cleanup, errors, preflight
docs/better-sqlite3-adapter.md	Ownership, defensive mode, the SqliteConnection contract
docs/supported-objects.md	Object matrix: dumped / restored / round-trip tested
docs/supported-data-types.md	Per-storage-class fidelity and how each value is written
docs/known-limitations.md	What this package does not do, and why
docs/round-trip-testing.md	Running the interop suite; the fixture
docs/architecture.md	Layer-by-layer design and the reasoning behind it

Fidelity highlights

• 64-bit integers are exact. Every value is rendered to text by SQLite itself, so 9223372036854775807 never passes through a JavaScript number.
• REAL digits are SQLite's own, from the same printf the shell uses.
• Virtual tables come back byte-identical, FTS5 indexes and R-trees included: like the native .dump, the schema row is written directly and the shadow tables' contents are copied, instead of rebuilding the index.
• Text is bytes. Invalid UTF-8 and embedded NULs survive a round trip; the native shell silently truncates text at the first NUL, this package does not.
• Connections are left as they were found. A native dump turns foreign_keys off and never turns it back on; a restore that stops early leaves a transaction open. Both are put back before the handle is returned.
• Consistent under concurrent writes. A dump reads every table inside one snapshot.

Development

npm install
npm run typecheck
npm run lint
npm test                   # unit tests; better-sqlite3 in-process, nothing else needed

npm run test:integration   # needs the sqlite3 shell on PATH (apt install sqlite3)
npm run test:package       # builds, then smoke-tests dist/ as ESM and CJS

Integration tests skip themselves with a clear message when the sqlite3 shell is not installed; set SQLITE_TEST_REQUIRED=1 (as CI does) to make that a hard error. SQLITE3_BIN selects a specific shell binary.

License

GPL-3.0-only. See LICENSE.