Skip to content

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 ConcurrencyError.

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.

Inside a transaction, the whole transaction is retried instead; see transaction.

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[S @ {*SqlValue, **SqlValue}] 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

NameTypeDescription
sql std.Fmt[SqlValue, S] SQL template. See Parameter Binding.
...args S Values filling the template's ${#...} parameters.

Returns

The number of rows affected.

Errors

ValueError if the SQL text contains a NUL.

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[S @ {*SqlValue, **SqlValue}] sql -> Statement[S] overload 1 of 2

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

NameTypeDescription
sql std.Fmt[SqlValue, S] SQL template. See Parameter Binding.

Errors

ValueError if the SQL text contains a NUL.

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[S @ {*SqlValue, **SqlValue}, R] sql func -> R overload 2 of 2

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

Parameters

NameTypeDescription
sql std.Fmt[SqlValue, S] SQL template. See Parameter Binding.
func ((Statement[S]) -> 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 statement that hits a busy error inside the block is not retried on its own. Instead the transaction is rolled back and the block runs again, with the same backoff and limit as busy retry.

Parameters

NameTypeDescription
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()