dart_node_better_sqlite3: No transactions, no iterators, no named params — ~20% of API wrapped

[MelbourneDeveloper]

Summary

dart_node_better_sqlite3 wraps roughly 20% of the better-sqlite3 npm API. The biggest gap is the missing db.transaction() wrapper — without it, error-safe transactional code requires manual BEGIN/COMMIT/ROLLBACK with no automatic rollback on exceptions. Large result sets must be loaded entirely into memory (no iterate()), and only positional parameters are supported (no named $param / :param).

What exists today

• openDatabase(path) — opens DB, auto-enables WAL mode + busy timeout
• db.prepare(sql) — returns Statement
• db.exec(sql) — raw SQL execution
• db.close() — close connection
• db.pragma(value) — set pragmas
• db.isOpen — check if open
• stmt.run([params]) — execute with changes + lastInsertRowid
• stmt.get([params]) — fetch first row as Map<String, Object?>
• stmt.all([params]) — fetch all rows as List<Map<String, Object?>>

All public methods return Result<T, String>. This is solid.

What's missing — grouped by priority

P0: Production hazards

db.transaction(fn) — automatic transaction wrapper

File to reference: packages/dart_node_better_sqlite3/lib/src/database.dart

The better-sqlite3 transaction() method provides:

• Automatic BEGIN before the function runs
• Automatic COMMIT if the function returns normally
• Automatic ROLLBACK if the function throws
• Nested transaction support via savepoints
• .deferred(), .immediate(), .exclusive() modes

Current workaround:

db.exec('BEGIN');
// If this throws, the transaction is left DANGLING
// No automatic rollback, connection is in a broken state
final result = stmt.run(params);
db.exec('COMMIT');

Impact: In production, any exception between BEGIN and COMMIT leaves a dangling transaction. The connection is stuck in a transaction state, and subsequent operations may fail silently or see stale data. The too_many_cooks example uses this manual pattern (see examples/too_many_cooks/src/db.dart) and is exposed to this risk.

What to implement:

Result<T, String> transaction<T>(T Function() fn) {
  // Wrap better-sqlite3's transaction() method
  // Automatic BEGIN/COMMIT/ROLLBACK
}

stmt.iterate([params]) — row iterator for large results

Impact: all() loads every row into memory. A query returning 100k rows will consume significant memory and potentially OOM. iterate() returns one row at a time via an iterator pattern.

What to implement:

Result<Iterable<Map<String, Object?>>, String> iterate(
  Statement stmt, [List<Object?>? params]
)

P1: Standard SQLite usage patterns

Named parameters ($name, :name, @name)

Currently only positional ? parameters are supported because params are passed as a List<Object?>.

What to implement: Accept Map<String, Object?> for named parameters:

// Current (positional only):
stmt.get([userId])

// Needed (named):
stmt.get({r'$userId': userId, r'$status': 'active'})

Named parameters are standard practice in SQLite. Most real-world queries use them for readability and safety.

Database constructor options

openDatabase() accepts only a path string. Missing options:

• readonly: true — open read-only (important for replicas, read-heavy workloads)
• fileMustExist: true — don't create DB if missing (catches deployment bugs)
• timeout: ms — custom busy timeout (currently hardcoded to 5000ms)
• verbose: fn — debug logging

Structured error types

Errors are returned as String. You cannot programmatically distinguish:

• "table not found" vs "syntax error" vs "database locked" vs "constraint violation"

What to implement: A typed error enum or sealed class:

sealed class SqliteError {
  case syntaxError(String sql, String message);
  case constraintViolation(String constraint, String message);
  case databaseLocked(String message);
  case ioError(String message);
  // etc.
}

P2: Power user features

• stmt.columns() — column metadata (name, type, table) for introspection
• stmt.bind([params]) — pre-bind parameters for repeated execution
• stmt.raw() — return rows as arrays instead of maps (faster)
• stmt.pluck() — return single column value instead of map (convenient)
• db.function(name, fn) — register custom SQL functions
• db.aggregate(name, options) — register custom aggregate functions
• db.backup(filename) — online backup
• db.serialize() — serialize database to Buffer
• db.loadExtension(path) — load SQLite extensions
• db.table(name, factory) — virtual table support
• stmt.reader / stmt.readonly / stmt.source — statement metadata
• db.defaultSafeIntegers() — BigInt support for large rowids

Test gaps

File: packages/dart_node_better_sqlite3/test/database_test.dart (23 tests)

Well tested:

• CRUD operations (INSERT, SELECT, UPDATE, DELETE)
• Data types (integer, real, text, null, large integers)
• Error cases (invalid path, invalid SQL, constraint violations)
• Manual BEGIN/COMMIT and BEGIN/ROLLBACK
• Parameterized queries

Not tested:

• BLOB data type — schema defines blob_col but no test reads/writes blob data
• Multiple parameterized values in a single query
• Concurrent access / multiple connections to same file
• Reusing a statement across multiple calls
• Using a closed database (does it return proper Error result?)
• Using a statement after its database is closed
• Very large result sets (memory behavior)
• WAL mode actually being set (verify with PRAGMA)
• Busy timeout behavior under contention

Files to modify

• packages/dart_node_better_sqlite3/lib/src/database.dart — add transaction(), database options, structured errors
• packages/dart_node_better_sqlite3/lib/src/statement.dart — add iterate(), named params, columns(), raw(), pluck()
• packages/dart_node_better_sqlite3/lib/src/errors.dart — new file for structured error types
• packages/dart_node_better_sqlite3/test/database_test.dart — add tests for new features + missing edge cases