Connection

An open database connection.

Returned by open. A connection may only be used by one strand at a time; concurrent access from another strand raises a concurrency error.

Busy retry

An operation that hits a busy error outside a transaction is retried with exponential backoff, using the retries, min_wait, and max_wait settings given to open. The wait doubles after each attempt, plus a small random jitter, until it reaches max_wait. Passing retries: 0 disables retrying, so Busy surfaces to the caller instead.

Statements inside a transaction are not retried individually -- the transaction is retried as a whole.

Example

# High contention: retry up to 20 times, waiting up to 5s
open "mydb.sqlite" retries: 20 max_wait: 5000 do |conn|
  conn.execute t"UPDATE counters SET value = value + 1"

# No retry: handle the busy error directly
open "mydb.sqlite" retries: 0 do |conn|
  try
    conn.execute t"UPDATE counters SET value = value + 1"
  catch Busy: _
    echo "database is busy"

Methods

close()

Closes the connection and releases its resources.

A connection that is never closed explicitly is closed when it is collected.

Example

let conn = open "mydb.sqlite"
conn.close()

execute sql ...args -> Int

Prepares a statement, runs it once, and returns the number of rows it affected.

Shorthand for prepare plus Statement.execute when the statement is not reused.

Parameters

Name	Type	Description
sql	std.Fmt	SQL template. A Str raises, because a Str cannot say which of its text is data. Only the literal text becomes SQL; an interpolated value is bound as a parameter, so it is data whatever it looks like. See Parameter Binding.
...args	Value	Values filling the template's ${#...} parameters.

Returns

The number of rows affected.

Example

open "mydb.sqlite" do |conn|
  conn.execute t"CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)"

  let name = "Alice"
  conn.execute t"INSERT INTO users (name) VALUES ($name)"

prepare sql -> Statement

Prepares a statement for repeated execution.

An interpolated value is bound once, here. A parameter written ${#name} or ${#0} stays unfilled and is supplied at each query or execute.

Parameters

Name	Type	Description
sql	std.Fmt	SQL template. See Parameter Binding.

Example

open "mydb.sqlite" do |conn|
  conn.prepare t"SELECT * FROM users WHERE id = ${#id}" do |stmt|
    for row = stmt.query id: 1
      echo "User: $(row["name"])"

  let stmt = conn.prepare t"INSERT INTO users (name) VALUES (${#name})"
  stmt.execute name: "Charlie"
  stmt.close()

prepare[R] sql func -> R

Prepares a statement and calls func with it. The statement is closed when func returns.

Parameters

Name	Type	Description
sql	std.Fmt	SQL template. See Parameter Binding.
func	((Statement) -> R)	Called with the Statement.

transaction[R] func -> R

Runs func inside a database transaction.

The transaction is committed when func returns and rolled back if it raises. Call Transaction.commit or Transaction.rollback to finish it early.

A busy error inside the transaction raises immediately rather than retrying the failing statement: a rolled-back transaction has to start over anyway. The block is rolled back and re-invoked until it succeeds, is explicitly rolled back, or the connection's retry limit is exhausted.

Parameters

Name	Type	Description
func	((Transaction) -> R)	Called with the Transaction.

Example

open "mydb.sqlite" do |conn|
  conn.transaction do |_|
    conn.execute t"UPDATE accounts SET balance = balance - 100 WHERE id = 1"
    conn.execute t"UPDATE accounts SET balance = balance + 100 WHERE id = 2"

  conn.transaction do |tx|
    conn.execute t"INSERT INTO audit (action) VALUES ('attempt')"
    if should_cancel
      tx.rollback()