Sqlite

Render the connection without exposing its host handle.

Close the connection now. An open transaction is rolled back, and every unfinished Db.rows stream ends with Err(Closed).

After close!, every other method returns Err(Closed). Closing a closed connection is Ok({}).

Run one statement that returns no rows, such as an INSERT, UPDATE, DELETE or CREATE TABLE.

A statement with result columns is rejected before it runs with ReturnsRows, even if it would return no row; so an INSERT … RETURNING inserts nothing. Text holding a second statement is rejected with MultipleStatements; use [execute_script!] for that. Text holding no statement at all, only white space or comments, does nothing.

db.execute!("INSERT INTO todos (task) VALUES (${String(task)})")?

Run every statement in sql, in order, stopping at the first one that fails. The statements take no parameters, and any rows they return are discarded.

The statements are not wrapped in a transaction: those before a failing one have taken effect, unless the text itself has a BEGIN or the call is made inside [transaction!].

db.execute_script!(
    \\CREATE TABLE todos (id INTEGER PRIMARY KEY, task TEXT NOT NULL);
    \\CREATE INDEX todos_task ON todos (task);
)?

Run a query that returns exactly one row and decode it.

No row is Err(NoRows) and a second row is Err(TooManyRows). A row whose values the decoder rejects is Err(BadRow(err)).

count = db.query_one!("SELECT COUNT(*) FROM todos", Decoder.i64(0))?

Run a query and decode every row into a list.

The first row whose values the decoder rejects ends the query with Err(BadRow(err)); to skip such rows instead, use Db.rows.

tasks = db.query_all!("SELECT task FROM todos ORDER BY id", Decoder.str("task"))?

Lazily run a query, decoding one row for each item pulled.

Building the stream does nothing; the first pull prepares the statement, binds its parameters and resolves the decoder's columns. The stream ends after the last row.

A BadRow(err) item concerns one row, whose values the decoder rejected, and the stream continues with the next row. A SqliteErr or Closed item is the last one. A column the decoder names that the query does not return is Err(SqliteErr(ColumnNotFound(_))), the only item.

for! item in db.rows("SELECT id, payload FROM events ORDER BY id", decode_event) {
    match item {
        Ok(event) if event.id > last => break
        Ok(event) => Stdout.line!(event.payload)?
        Err(BadRow(_)) => {}
        Err(SqliteErr(err)) => { return Err(SqliteErr(err)) }
        Err(Closed) => { return Err(Closed) }
    }
}

The stream holds its statement, and with it SQLite's read lock, until it has finished or is released. The connection stays usable meanwhile, also for another stream of the same query.

A stream left with break is released when the function containing the loop returns, not at the end of the loop (roc-lang/roc#12266). Give the loop a function of its own if the lock has to be gone before the next statement, such as a DROP TABLE.

A string literal without interpolation: SQL with no parameters.

An interpolated string literal: each ${…} is a bound parameter.

SQL assembled at run time, with the values for its ? markers in order. A count that differs from the statement's number of parameters is WrongParamCount when the query is run.

marks = Str.join_with(ids.map(|_| "?"), ", ")
query = Query.new("SELECT task FROM todos WHERE id IN (${marks})", ids.map(|id| Integer(id)))

Describe the error for display.

Combine two decoders; this is what the record-builder suffix { … }.Decoder calls.

Transform the decoded value.

Transform the decoded value with a function that can fail; its error fails the row, beside the decoder's own errors.

Decoder.i64("age").map_try(|n| if n >= 0 { Ok(n) } else { Err(NegativeAge(n)) })

Accept SQL NULL: the result is Err(Null) when every column the decoder reads is NULL, and Ok of the decoded value otherwise.

Decode a floating-point column. An integer is not converted.

Decode an integer column as a boolean, the way SQLite does: zero is False and any other integer is True.

Decode a BLOB column.

Decode a text column. Text that is not valid UTF-8 is BadUtf8.

Decode a column of any type, keeping its type as a tag.

Decode every column of the row, with the column names.