Overview

Dart's standard approach to SQL — passing query strings and receiving dynamic maps back — pushes every type-safety concern onto the developer. Column names are stringly-typed, result shapes are unchecked, and failures surface as runtime exceptions rather than types.

ribs_sql is a functional SQL library inspired by Scala's Doobie. It treats SQL as plain strings (no query DSL, no reflection) while giving you:

• Typed rows — a Read<A> describes how to decode a row into A, checked at compile time
• Typed parameters — a Write<A> describes how to bind A as SQL parameters, eliminating positional mistakes
• Either-based errors — no unexpected exceptions; failures are values
• Composable transactions — ConnectionIO<A> is a pure description of database work; execution is deferred until you hand it a Transactor
• Streaming — large result sets stream row-by-row via Rill<A> rather than buffering everything in memory

Domain model

Start by defining plain Dart classes and describing how to map them to and from SQL rows using Read and Write. No annotations, no code generation:

Domain model + codecs

// A plain Dart class with no special annotations or generated code required.
final class Person {
  final String name;
  final int age;

  const Person(this.name, this.age);
}

// Describe how to read a Person from two consecutive columns.
final personRead = (Read.string, Read.integer).tupled.map(
  (t) => Person(t.$1, t.$2),
);

// Describe how to write a Person's fields as two SQL parameters.
final personWrite = (Write.string, Write.integer).tupled.contramap(
  (Person p) => (p.name, p.age),
);

Tuple extensions such as (Read.string, Read.integer).tupled compose primitive column readers into a multi-column Read<(String, int)>. .map then converts the tuple to your domain type. Write works in the opposite direction via .contramap.

Built-in primitives: Read/Write/ReadWrite for integer, string, boolean, double, dateTime, bigInt, blob, and json.

Transactor

A Transactor manages the database connection and transaction lifecycle. For SQLite, SqliteTransactor provides two constructors:

• SqliteTransactor.memory() — in-memory database, useful for tests and prototyping
• SqliteTransactor.file(path) — persistent file-backed database

Both return Resource<Transactor>, which guarantees the connection is closed on success, error, or cancellation:

Creating a Transactor

// SqliteTransactor.memory() gives a Resource<Transactor> backed by an
// in-memory SQLite database. Use .file(path) for a persistent database.
// Resource ensures the connection is closed on success, error, or
// cancellation.
IO<Unit> runWithTransactor() => SqliteTransactor.memory().use((xa) {
  // xa : Transactor — passed to .transact() on every ConnectionIO
  return IO.unit;
});

Strategy

Strategy controls the four transaction lifecycle hooks that every Transactor runs around each ConnectionIO:

Hook	Default	Purpose
before	BEGIN	Start the transaction
after	COMMIT	Commit on success
oops	ROLLBACK	Roll back on error or cancellation
always	no-op	Guaranteed cleanup, runs unconditionally

Strategy.defaultStrategy() covers the common case. Pass a custom Strategy to any transactor constructor to override individual hooks:

Custom Strategy

// Strategy controls the four transaction lifecycle hooks. The default strategy
// issues BEGIN before the ConnectionIO, COMMIT on success, and ROLLBACK on any
// error or fiber cancellation. Override individual hooks when needed — for
// example to use SAVEPOINTs or to add application-level auditing.
final customStrategy = Strategy(
  before: ConnectionIO.fromConnection((conn) => conn.beginTransaction()),
  after: ConnectionIO.fromConnection((conn) => conn.commit()),
  oops: ConnectionIO.fromConnection((conn) => conn.rollback()),
  always: ConnectionIO.unit, // runs unconditionally after the above
);

// Pass a custom Strategy to any transactor constructor.
final Resource<Transactor> xa = SqliteTransactor.memory(strategy: customStrategy);

DDL — Update0

Update0 represents a parameterless SQL statement. The .update0 extension on String creates one. Use it for CREATE TABLE, DROP TABLE, and any DDL that takes no bound values:

DDL with Update0

// update0 is an extension on String that creates an Update0 — a
// parameterless write statement. Use it for DDL and any SQL that
// takes no bound parameters.
ConnectionIO<int> createTable() =>
    '''
CREATE TABLE IF NOT EXISTS person (
  name TEXT NOT NULL,
  age  INTEGER NOT NULL
)'''.update0.run();

Insert — Update

Update<A> represents a parameterized write statement. The .update(Write<A>) extension on String creates one. run(value) executes once; runMany(list) executes once per element using the same prepared statement:

Insert rows

// update<A>(Write<A>) creates an Update<A>.
// run(value)    — execute once, returns affected row count.
// runMany(list) — execute for each element in the list.
ConnectionIO<int> insertOne() => 'INSERT INTO person (name, age) VALUES (?, ?)'
    .update((Write.string, Write.integer).tupled)
    .run(('Alice', 30));

ConnectionIO<Unit> insertMany() => 'INSERT INTO person (name, age) VALUES (?, ?)'
    .update((Write.string, Write.integer).tupled)
    .runMany([('Alice', 30), ('Bob', 25), ('Carol', 35)]);

Query

Query<A> represents a SELECT statement. The .query(Read<A>) extension on String (or Fragment) creates one. It offers several result-collection strategies:

Method	Returns	Semantics
ilist()	ConnectionIO<IList<A>>	All rows
ivector()	ConnectionIO<IVector<A>>	All rows as IVector
unique()	ConnectionIO<A>	Exactly one row; error otherwise
option()	ConnectionIO<Option<A>>	Some if one row, None if zero; error if more than one
nel()	ConnectionIO<NonEmptyIList<A>>	One or more rows; error if empty
stream()	ConnectionRill<A>	Lazy streaming

Querying rows

// query<A>(Read<A>) creates a Query<A>.
// ilist()  — collect all rows into an IList<A>.
// unique() — expect exactly one row; error otherwise.
// option() — Some if one row, None if zero; error if more than one.
ConnectionIO<IList<Person>> queryAll() => 'SELECT name, age FROM person'.query(personRead).ilist();

ConnectionIO<Option<Person>> queryByName(String name) =>
    (Fragment.raw('SELECT name, age FROM person WHERE name = ') + Fragment.param(name, Put.string))
        .query(personRead)
        .option();

Fragment composition

Fragment lets you build SQL dynamically by concatenating raw SQL strings and typed parameters with +. Fragment.raw(sql) contributes literal SQL; Fragment.param(value, Put<A>) contributes a single ? placeholder with a bound value. The .fr extension on String is shorthand for Fragment.raw:

final frag =
    'SELECT name, age FROM person WHERE age > '.fr +
    Fragment.param(18, Put.integer) +
    Fragment.raw(' AND name LIKE ') +
    Fragment.param('%Alice%', Put.string);

All parameters are bound separately and never interpolated into the SQL string, so Fragment is safe from SQL injection by construction.

ParameterizedQuery

ParameterizedQuery<P, A> bundles a SQL template with a Read<A> and a Write<P> so the query can be reused with different parameters without reconstructing the object each time:

Reusable parameterized query

// ParameterizedQuery bundles a SQL template, a Read, and a Write.
// Bind parameters lazily via unique(), option(), ilist(), or stream().
final byName = ParameterizedQuery(
  'SELECT name, age FROM person WHERE name = ?',
  personRead,
  Write.string,
);

final byMinAge = ParameterizedQuery(
  'SELECT name, age FROM person WHERE age >= ? ORDER BY name',
  personRead,
  Write.integer,
);

ConnectionIO<Person> lookupPerson(String name) => byName.unique(name);

ConnectionIO<IList<Person>> adults() => byMinAge.ilist(18);

ParameterizedQuery mirrors all of Query's result methods (unique, option, ilist, stream) as convenience shortcuts that accept the parameter value directly.

Streaming

.stream() on a Query<A> (or ParameterizedQuery) returns a ConnectionRill<A>. Calling .transact(xa) on it returns a Rill<A> that emits rows lazily, keeping the database connection open for the duration of the stream and releasing it automatically when the stream terminates:

Streaming results

// .stream() returns a ConnectionRill<A> — a lazy, streaming query.
// Call .transact(xa) to obtain a Rill<A> that emits rows one at a time.
// The connection stays open for the duration of the stream and is
// released automatically when the stream terminates.
IO<IList<Person>> streamAll(Transactor xa) =>
    'SELECT name, age FROM person'.query(personRead).stream().transact(xa).compile.toIList;

Use streaming when result sets are large enough that buffering the entire collection in memory would be wasteful.

Insert Returning

UpdateReturning<A, B> handles INSERT ... RETURNING (and any statement that both writes and reads). run(value) reads a single row back; runMany(list) collects all returned rows:

INSERT ... RETURNING

// updateReturning<A, B>(Write<A>, Read<B>) supports INSERT ... RETURNING.
// run(value)    — execute and read the single returned row.
// runMany(list) — execute for each element; collect all returned rows.
ConnectionIO<int> insertReturningId(String label) =>
    'INSERT INTO item (label) VALUES (?) RETURNING id'
        .updateReturning(Write.string, Read.integer)
        .run(label);

Transactions

ConnectionIO<A> is a pure description of database work — it does nothing until you call .transact(xa). Calling transact wraps the operation in a BEGIN / COMMIT block. Any error, including fiber cancellation, triggers an automatic ROLLBACK:

Composing and running a transaction

// .transact(xa) wraps the entire ConnectionIO in BEGIN / COMMIT.
// Any error — including fiber cancellation — triggers an automatic ROLLBACK.
IO<Unit> runTransaction(Transactor xa2) =>
    createTable().flatMap((_) => insertMany()).flatMap((_) => queryAll()).transact(xa2).voided();

Because ConnectionIO is a monad, operations compose with flatMap. The entire chain runs inside a single transaction, so partial failures roll back all work completed so far.