SQLite

ribs_sqlite is the SQLite backend for ribs_sql. It provides two concrete Transactor implementations that differ in how they manage connections:

Implementation	Connections
SqliteTransactor	Single shared connection
SqlitePoolTransactor	Writer + reader pool

Both return Resource<Transactor>. The Resource wrapper guarantees the underlying connection (or pool) is closed cleanly on success, error, or fiber cancellation.

SqliteTransactor — single connection

SqliteTransactor opens one connection and reuses it for every transact() call. Because the connection is shared, concurrent transactions are serialized — this is fine for most single-process use cases and is the simplest option.

SqliteTransactor

// SqliteTransactor.memory() — in-memory database, single shared connection.
// Useful for tests or short-lived programs that need no persistence.
final Resource<Transactor> memoryXa = SqliteTransactor.memory();

// SqliteTransactor.file(path) — file-based database, single connection.
// The connection is opened once and reused across all transact() calls.
final Resource<Transactor> fileXa = SqliteTransactor.file('/var/app/data.db');

// Both return Resource<Transactor>. Use .use((xa) => ...) to acquire the
// transactor, run your program, and release the connection automatically.
IO<Unit> withMemory() => SqliteTransactor.memory().use((xa) {
  return 'SELECT 1'.query(Read.integer).unique().transact(xa).voided();
});

• memory() — opens an in-memory database. Schema and data live only for the lifetime of the Resource. Ideal for tests and short-lived programs.
• file(path) — opens a file-backed database at path. Data persists across Resource acquisitions.

Both constructors accept an optional strategy parameter — see Strategy in the overview for details.

SqlitePoolTransactor — reader/writer pool

SqlitePoolTransactor wraps a SqliteConnectionPool from the sqlite3_connection_pool package. It maintains a single writer connection and one or more read-only connections, allowing concurrent reads while a write transaction is in progress.

For this to work the database must use WAL (Write-Ahead Logging) journal mode. WAL allows readers to operate against the last committed snapshot while the writer is active. Without WAL, SQLite's default journal mode serialises all connections.

SqlitePoolTransactor

// SqlitePoolTransactor uses the sqlite3_connection_pool package to maintain
// separate writer and reader connections, enabling concurrent reads while a
// write transaction is in progress.
//
// Requires WAL journal mode so readers do not block the writer.
Resource<Transactor> openPool(String path) {
  final pool = SqliteConnectionPool.open(
    name: path,
    openConnections: () {
      sq.Database open(bool write) {
        final db = sq.sqlite3.open(path);
        // WAL mode is required for reader/writer concurrency.
        db.execute('PRAGMA journal_mode = WAL;');
        if (!write) db.execute('PRAGMA query_only = true;');
        return db;
      }

      // One writer connection, two read-only connections.
      return PoolConnections(open(true), [open(false), open(false)]);
    },
  );

  return SqlitePoolTransactor.create(pool);
}

When transact() is called, the pool transactor acquires the writer connection. When stream() is called — or when connectReader() is used directly — a reader connection is acquired instead, leaving the writer free for concurrent mutations.

note

SqlitePoolTransactor is intended for file-based databases. An in-memory SQLite database is isolated per connection, so using a pool would give each connection a separate, empty database.

Example program

The following snippet wires together a schema, several writes, and a query into a single transaction using an in-memory SqliteTransactor. All ConnectionIO operations compose with flatMap and run atomically under one BEGIN/COMMIT.

End-to-end example

// ── Domain ───────────────────────────────────────────────────────────────────

final class Task {
  final int? id;
  final String title;
  final bool done;

  const Task({this.id, required this.title, required this.done});
}

// Read<Task> decodes three consecutive columns.
final taskRead = (Read.integer.optional(), Read.string, Read.boolean).tupled.map(
  (t) => Task(id: t.$1.toNullable(), title: t.$2, done: t.$3),
);

// ── Schema ───────────────────────────────────────────────────────────────────

ConnectionIO<int> createSchema() =>
    '''
CREATE TABLE IF NOT EXISTS task (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT    NOT NULL,
  done  INTEGER NOT NULL DEFAULT 0
)'''.update0.run();

// ── Writes ───────────────────────────────────────────────────────────────────

ConnectionIO<int> insertTask(String title) =>
    'INSERT INTO task (title, done) VALUES (?, 0)'.update(Write.string).run(title);

ConnectionIO<int> markDone(int id) =>
    'UPDATE task SET done = 1 WHERE id = ?'.update(Write.integer).run(id);

// ── Queries ──────────────────────────────────────────────────────────────────

ConnectionIO<IList<Task>> pendingTasks() =>
    'SELECT id, title, done FROM task WHERE done = 0 ORDER BY id'.query(taskRead).ilist();

// ── Program ──────────────────────────────────────────────────────────────────

// All ConnectionIO operations compose with flatMap inside a single transaction.
// SqliteTransactor.memory() provides the Transactor for this example.
IO<IList<Task>> program() => SqliteTransactor.memory().use((xa) {
  final setup = createSchema()
      .flatMap((_) => insertTask('Write docs'))
      .flatMap((_) => insertTask('Add tests'))
      .flatMap((_) => insertTask('Ship it'));

  final work = setup.flatMap((_) => markDone(1)).flatMap((_) => pendingTasks());

  return work.transact(xa);
});

program() returns IO<IList<Task>> — a pure description of the entire database interaction. Nothing is executed until the IO is run at the application boundary.