Skip to content

std::sql

SQL databases: the vocabulary every driver and every SQL library shares. A driver module implements Conn for its engine; code that talks to a database takes a Conn and sends Statements, written with the Sql"..." typed literal, whose ${...} slots become parameters and never SQL text. Values cross as Value, converted by ToSql and FromSql, with NULL as Maybe’s None. A result row is a Row, read by column name or position. Failures are a sql.Error, which keeps a violated constraint’s name. sql.transaction runs a body in a transaction, or in a savepoint when the connection already is one. A connection is closed with defer sql.close_quietly(conn) on the line after it is opened, and Conn.close(conn)? on the success path when a failed close should be reported.

std holds no driver. The first-party drivers and the checked-SQL library sqlkit are modules of their own in the nomi repository, under lib/sql/, which a project adds with nomi add: github.com/nomi-language/nomi/lib/sql/sqlite, github.com/nomi-language/nomi/lib/sql/postgres and github.com/nomi-language/nomi/lib/sql/sqlkit (Databases shows them in use). std::sql::testing has a recording fake Conn and the conformance suite every driver runs.

Import with import std::sql.

interface Conn
    fn query(conn: self, statement: Statement) -> Result<List<Row>, Error>
    fn each_row(conn: self, statement: Statement, f: (Row) -> Bool) -> Result<Unit, Error>
    fn exec(conn: self, statement: Statement) -> Result<Int, Error>
    fn begin_transaction(conn: self) -> Result<Conn, Error>
    fn commit(tx: self) -> Result<Unit, Error>
    fn rollback(tx: self) -> Result<Unit, Error>
    fn ping(conn: self) -> Result<Unit, Error>
    fn dialect(conn: self) -> Dialect
    fn close(conn: self) -> Result<Unit, Error>
end

A connection to a database, or a transaction on one. A driver implements it; code that reads or writes a database takes a Conn, so one function serves a connection, a transaction and a test’s fake.

Every driver follows these rules, and std::sql::testing’s conformance suite checks each:

  • begin on a connection starts a transaction; begin on a transaction starts a savepoint inside it, and the returned Conn’s commit releases the savepoint and its rollback rolls back to it.
  • commit or rollback of a transaction ends it and every savepoint inside it; after that, commit and any statement are Err(.Finished), and rollback is a no-op that returns Ok.
  • A statement’s parameters are sent as values, never spliced into the SQL text.
  • each_row calls f with each row in order and stops when f returns False.
  • exec returns the number of rows the statement changed.
  • A Timestamptz compares in time order with the timestamps the database writes itself (DEFAULT CURRENT_TIMESTAMP), in WHERE and ORDER BY.
  • A constraint error’s name is the constraint’s name as Postgres reports it (see Error.Constraint).
  • close closes the connection, whichever of its Conns it is called on: the connection itself or a transaction or savepoint begin returned. A transaction still open is rolled back with every savepoint inside it, as the engine does when a connection ends. After that, every operation of every Conn of the connection but dialect, rollback included, is Err(.Closed), and close again is a no-op that returns Ok, as rollback after a finished transaction is. A transaction’s Conn is a view of its connection, so there is one thing to close, and closing only the transaction would be rollback.

A connection is meant to be shared: a program opens one in its boot and every task uses it. While one task’s transaction is open, a statement another task sends through the connection runs outside the transaction, on another connection of a pool or after the transaction ends on a single connection, and never fails or joins the transaction for it. Inside a transaction, use the Conn its begin returned: a statement the same task sends through the connection may wait for its own transaction.

fn query(conn: self, statement: Statement) -> Result<List<Row>, Error>

interface Conn

fn each_row(conn: self, statement: Statement, f: (Row) -> Bool) -> Result<Unit, Error>

interface Conn

fn exec(conn: self, statement: Statement) -> Result<Int, Error>

interface Conn

fn begin_transaction(conn: self) -> Result<Conn, Error>

interface Conn

fn commit(tx: self) -> Result<Unit, Error>

interface Conn

fn rollback(tx: self) -> Result<Unit, Error>

interface Conn

fn ping(conn: self) -> Result<Unit, Error>

interface Conn

fn dialect(conn: self) -> Dialect

interface Conn

fn close(conn: self) -> Result<Unit, Error>

interface Conn

impl Release for Conn

fn release(conn: Conn) -> Unit

impl Release.release

struct Statement
    parts: List<String>
    params: List<Value>
end

A statement and its parameters: parts is the SQL around each parameter, one more than params, so a driver writes its own dialect’s placeholders between them. Write one with the Sql"..." literal.

Two slots side by side, or a slot at either end, have an empty part between them or beside them. Statement is Equatable, and Display writes the SQL with ?1, ?2, … for the parameters, numbered from 1 in the order they appear, whatever placeholders a driver sends (SELECT id FROM users WHERE name = ?1).

s = Sql"SELECT name FROM users WHERE id = ${7} AND active = ${True}"
assert s.parts == ["SELECT name FROM users WHERE id = ", " AND active = ", ""]
assert s.params == [Value.Int(7), Value.Bool(True)]
assert "${s}" == "SELECT name FROM users WHERE id = ?1 AND active = ?2"

impl Display for Statement

fn to_string(statement: Statement) -> String

impl Display.to_string

impl Add<Statement, Statement> for Statement

fn add(lhs: Statement, rhs: Statement) -> Statement

impl Add.add

fn join(statements: Iter<Statement>, separator: Statement) -> Statement

type function on Statement

The statements one after another, with separator, itself a Statement, between each two: a list of placeholders for IN (...), the rows of a multi-row INSERT, or a query’s filters joined by Sql" AND ". No statements is the empty statement. join and the builders below (list, values, all, any, where_all, where_any) take an Iter, so a list or a pipe passes.

ids = [3, 5, 8]
placeholders = ids |> Iter.map(fn id -> Sql"${id}") |> Statement.join(Sql", ")
s = Sql"SELECT name FROM users WHERE id IN (" + placeholders + Sql")"
assert "${s}" == "SELECT name FROM users WHERE id IN (?1, ?2, ?3)"
assert s.params == [Value.Int(3), Value.Int(5), Value.Int(8)]
assert Statement.join([], Sql" AND ") == Sql""
fn list(statements: Iter<Statement>) -> Statement

type function on Statement

The statements joined by , in parentheses: (a, b, c), for one row of a multi-row INSERT or any other tuple. No statements is (), which is not SQL, so leave the list out when there is nothing to list.

users = [{email: "ada@example.com", name: "Ada"}, {email: "bob@example.com", name: "Bob"}]

rows =
    users
    |> Iter.map(fn u -> Statement.list([Sql"${u.email}", Sql"${u.name}"]))
    |> Statement.join(Sql", ")

s = Sql"INSERT INTO users (email, name) VALUES " + rows
assert "${s}" == "INSERT INTO users (email, name) VALUES (?1, ?2), (?3, ?4)"

assert s.params == [
    Value.Text("ada@example.com"),
    Value.Text("Ada"),
    Value.Text("bob@example.com"),
    Value.Text("Bob"),
]

assert "${Statement.list([])}" == "()"
fn values<T>(items: Iter<T>) -> Statement where T: ToSql

type function on Statement

A parenthesized list of parameters for SQL’s IN: (?1, ?2, ?3), the list of each item as a parameter. No items is (NULL), which matches no row, since IN () is not SQL.

s = Sql"SELECT name FROM users WHERE id IN " + Statement.values([3, 5, 8])
assert "${s}" == "SELECT name FROM users WHERE id IN (?1, ?2, ?3)"
assert s.params == [Value.Int(3), Value.Int(5), Value.Int(8)]
evens = [1, 2, 3, 4] |> Iter.filter(fn n -> n % 2 == 0) |> Statement.values()
assert "${evens}" == "(?1, ?2)"
nothing: List<Int> = []
assert "${Statement.values(nothing)}" == "(NULL)"
fn all(conditions: Iter<Statement>) -> Statement

type function on Statement

A condition that holds when every one of conditions does: the conditions joined by AND. With more than one, each is parenthesized, so one holding an OR, such as an any, keeps its meaning. One condition is itself, with no parentheses. An empty statement among them is left out, so an any([]) inside an all drops out, and no conditions is the empty statement.

paid = Sql"status = ${"paid"}"
big = Sql"total >= ${20}"
rush = Sql"rush = ${True}"
assert "${Statement.all([paid, big])}" == "(status = ?1) AND (total >= ?2)"
assert "${Statement.all([paid])}" == "status = ?1"
assert Statement.all([]) == Sql""
nested = Statement.all([paid, Statement.any([big, rush])])
assert "${nested}" == "(status = ?1) AND ((total >= ?2) OR (rush = ?3))"
assert Statement.all([paid, Statement.any([])]) == paid
fn any(conditions: Iter<Statement>) -> Statement

type function on Statement

A condition that holds when any one of conditions does: the conditions joined by OR, parenthesized and simplified as all does.

a = Sql"status = ${"paid"}"
b = Sql"status = ${"shipped"}"
assert "${Statement.any([a, b])}" == "(status = ?1) OR (status = ?2)"
assert "${Statement.any([a])}" == "status = ?1"
assert Statement.any([]) == Sql""
fn where_all(conditions: Iter<Statement>) -> Statement

type function on Statement

WHERE and all of the conditions, or nothing when that is empty, so a query’s filters can each be optional.

paid = Sql"status = ${"paid"}"
big = Sql"total >= ${20}"
rush = Sql"rush = ${True}"
assert "${Sql"SELECT id FROM orders" + Statement.where_all([])}" == "SELECT id FROM orders"
assert "${Statement.where_all([paid])}" == " WHERE status = ?1"
s = Statement.where_all([paid, Statement.any([big, rush])])
assert "${s}" == " WHERE (status = ?1) AND ((total >= ?2) OR (rush = ?3))"
fn where_any(conditions: Iter<Statement>) -> Statement

type function on Statement

WHERE and any of the conditions, or nothing when that is empty.

a = Sql"status = ${"paid"}"
b = Sql"status = ${"shipped"}"
assert "${Statement.where_any([a, b])}" == " WHERE (status = ?1) OR (status = ?2)"
assert Statement.where_any([]) == Sql""

impl Debug for Statement

fn inspect(value: Statement) -> String

impl Debug.inspect

impl Equatable for Statement

fn equal(a: Statement, b: Statement) -> Bool

impl Equatable.equal

type Sql

The tag of the Sql"..." literal, which builds a Statement.

impl Literal for Sql

fn from_fragments(fragments: List<Fragment<ToSql>>) -> Statement

impl Literal.from_fragments

Sql"SELECT * FROM users WHERE id = ${id}" is a Statement: the text between the slots is its SQL, and each slot’s value, which must implement ToSql, is a parameter. A value never becomes SQL text, so a slot cannot inject SQL whatever it holds. Sql is the tag type, and its Literal impl takes List<Fragment<ToSql>> (Typed literals).

name = "x'); DROP TABLE users; --"
s = Sql"SELECT id FROM users WHERE name = ${name}"
assert s.parts == ["SELECT id FROM users WHERE name = ", ""]
assert s.params == [Value.Text(name)]
assert Sql"${1}${2}".parts == ["", "", ""]

impl Debug for Sql

fn inspect(value: Sql) -> String

impl Debug.inspect

enum Value
    Null
    Int Int
    Float Float
    Decimal Decimal
    Text String
    Bool Bool
    Bytes Bytes
    Date Date
    Time Time
    Timestamp NaiveDateTime
    Timestamptz Instant
    Uuid Uuid
    Json Json
    Array List<Value>
end

A value as it crosses to or from a database. Null is SQL’s NULL; Timestamp is a timestamp without a time zone and Timestamptz one with, which every engine stores as an instant. Array is a Postgres array (WHERE id = ANY(${ids})), its elements in order; an engine without arrays refuses one as a parameter.

assert ToSql.to_sql([1, 2]) == Value.Array([Value.Int(1), Value.Int(2)])
assert Value.kind(Value.Array([])) == "Array"
fn kind(value: Value) -> String

type function on Value

The variant’s name, "Int" or "Null": what Error.Decode reports it found.

assert Value.kind(Value.Text("x")) == "Text"

impl ToSql for Value

fn to_sql(value: Value) -> Value

impl ToSql.to_sql

impl FromSql for Value

fn from_sql(value: Value) -> Result<Value, String>

impl FromSql.from_sql

impl Debug for Value

fn inspect(value: Value) -> String

impl Debug.inspect

impl Equatable for Value

fn equal(a: Value, b: Value) -> Bool

impl Equatable.equal

interface ToSql
    fn to_sql(value: self) -> Value
end

A type that can be sent as a parameter. std implements it for Int, Float, Decimal, String, Bool, Bytes, Date, Time, NaiveDateTime, Instant, Uuid, Json, Value itself, Maybe<T>, whose None is NULL, and List<T>, which is an Array.

assert ToSql.to_sql(Some(3)) == Value.Int(3)
nothing: Maybe<Int> = None
assert ToSql.to_sql(nothing) == Value.Null
fn to_sql(value: self) -> Value

interface ToSql

interface FromSql
    fn from_sql(value: Value) -> Result<self, String>
end

A type that can be read from a column. from_sql returns Err with the name of the type it expected ("Int") when the value is not one; Row.get turns that into an Error.Decode, or an Error.UnexpectedNull when the value was NULL. std implements it for the types ToSql covers; Maybe<T> reads NULL as None. Each type maps to its own variant and reads back only from it, so Float.from_sql(Value.Int(1)) is an Err.

assert Int.from_sql(Value.Int(3)) == Ok(3)
assert Float.from_sql(Value.Int(1)) == Err("Float")
assert Int.from_sql(Value.Text("3")) == Err("Int")
assert String.from_sql(Value.Null) == Err("String")
fn from_sql(value: Value) -> Result<self, String>

interface FromSql

struct Row

opaque — construction surface is private to its defining module

One result row: its columns’ names and values, and the SQL of the statement that read it, which its errors name. It is opaque; a driver builds it with Row.new. A column is found by its name (the first of that name) or by its position from 0.

row = Row.new("SELECT id, name FROM users", [("id", Value.Int(7)), ("name", Value.Null)])
assert Row.columns(row) == ["id", "name"]
id: Int = Row.get(row, "id")?
assert id == 7
assert Row.get_position<Maybe<String>>(row, 1) == Ok(None)
fn new(query: String, cells: List<(String, Value)>) -> Row

type function on Row

The row that query read, with each column’s name and value in order.

fn query(row: Row) -> String

type function on Row

The SQL of the statement that read the row.

fn columns(row: Row) -> List<String>

type function on Row

The column names, in order.

fn values(row: Row) -> List<Value>

type function on Row

The values, in column order.

fn value(row: Row, column: String) -> Result<Value, Error>

type function on Row

The value of the first column named column, or Err(.MissingColumn) when there is none.

row = Row.new("SELECT 1 AS n", [("n", Value.Int(1))])
assert Row.value(row, "n") == Ok(Value.Int(1))
assert Row.value(row, "m") == Err(Error.MissingColumn{query: "SELECT 1 AS n", column: "m"})
fn value_position(row: Row, position: Int) -> Result<Value, Error>

type function on Row

The value at position, counting from 0, or Err(.ColumnPosition) when the row has no column there.

row = Row.new("SELECT 1 AS n", [("n", Value.Int(1))])
assert Row.value_position(row, 0) == Ok(Value.Int(1))

assert Row.value_position(row, 1)
    == Err(Error.ColumnPosition{query: "SELECT 1 AS n", position: 1, count: 1})
fn get<T>(row: Row, column: String) -> Result<T, Error> where T: FromSql

type function on Row

The value of the column named column as a T. A value of another type is Err(.Decode), whose expected is what from_sql returned and found the value’s Value.kind, and NULL read as a type that is not a Maybe is Err(.UnexpectedNull). T is inferred from the expected type (n: Int = Row.get(row, "n")?) or given (Row.get<Int>(row, "n")).

row = Row.new("SELECT n, t", [("n", Value.Int(1)), ("t", Value.Null)])
n: Int = Row.get(row, "n")?
assert n == 1

assert Row.get<String>(row, "n")
    == Err(Error.Decode{query: "SELECT n, t", column: "n", expected: "String", found: "Int"})

assert Row.get<String>(row, "t") == Err(Error.UnexpectedNull{query: "SELECT n, t", column: "t"})
assert Row.get<Maybe<String>>(row, "t") == Ok(None)
fn get_position<T>(row: Row, position: Int) -> Result<T, Error> where T: FromSql

type function on Row

The value at position, counting from 0, as a T, with get’s errors. The column the errors name is the one at position.

impl Debug for Row

fn inspect(value: Row) -> String

impl Debug.inspect

impl Equatable for Row

fn equal(a: Row, b: Row) -> Bool

impl Equatable.equal

enum Dialect
    Sqlite
    Postgres
    Other String
end

The engine a connection talks to, for code that writes SQL that differs between them.

Variants

  • Other

    Any other engine, by name.

impl Display for Dialect

fn to_string(dialect: Dialect) -> String

impl Display.to_string

SQLite, Postgres or the other engine’s name.

assert "${Dialect.Sqlite}" == "SQLite"
assert "${Dialect.Postgres}" == "Postgres"
assert "${Dialect.Other("duckdb")}" == "duckdb"

impl Debug for Dialect

fn inspect(value: Dialect) -> String

impl Debug.inspect

impl Equatable for Dialect

fn equal(a: Dialect, b: Dialect) -> Bool

impl Equatable.equal

enum Error
    Connection { message: String }
    Constraint { kind: ConstraintKind, name: String, table: Maybe<String>, column: Maybe<String> }
    Serialization { message: String }
    Database { code: String, message: String }
    RowCount { query: String, expected: String, found: Int }
    UnexpectedNull { query: String, column: String }
    Decode { query: String, column: String, expected: String, found: String }
    MissingColumn { query: String, column: String }
    ColumnPosition { query: String, position: Int, count: Int }
    Finished
    Closed
    Timeout
end

Why a statement or a connection failed: sql.Error is every failure. A driver maps its engine’s errors to these; the variants that name a query come from reading a Row, and query is the SQL of the statement that read it. Constraint keeps the violated constraint’s name, so a library can map it to a field. Error and ConstraintKind are both Equatable and Display.

Variants

  • Connection

    The database cannot be reached, or the connection broke.

  • Constraint

    A constraint rejected the statement. name is the constraint’s name, the same on every engine: the name the schema gives it (CONSTRAINT name, or a unique index’s name), else the name Postgres gives a constraint the schema leaves unnamed: <table>_pkey for a primary key, <table>_<columns>_key for a unique one (users_email_key), <table>_<columns>_fkey for a foreign key, <table>_<column>_check for a check that reads one column (<table>_check for one that reads several), and <table>_<column>_not_null for a not-null column. Postgres reports these names itself, but for not-null, which it reports by column; a driver for an engine that does not (SQLite reports columns or an expression) works them out from the schema. table and column are there when the driver knows them.

  • Serialization

    The transaction conflicted with another and was rolled back. It should run again.

  • Database

    Any other error the engine reports, with its own code (a Postgres SQLSTATE, an SQLite result code).

  • RowCount

    A statement read found rows where expected says how many it should have.

  • UnexpectedNull

    A column held NULL where the type read cannot hold it.

  • Decode

    A column held a found value (a Value variant’s name) where expected (the type read) cannot hold it.

  • MissingColumn

    No column of the row has the name.

  • ColumnPosition

    The row has count columns, so none is at position, which counts from 0.

  • Finished

    The transaction was already committed or rolled back. The connection still works.

  • Closed

    The connection was closed, by close on it or on one of its transactions. The caller needs a new connection, which is why this is a variant of its own rather than Finished.

  • Timeout

    The statement or the connection ran out of time.

impl Display for Error

fn to_string(e: Error) -> String

impl Display.to_string

e = Error.Constraint{kind: .Unique, name: "users_email_key", table: Some("users"), column: None}
assert "${e}" == "unique constraint `users_email_key` on table `users` failed"

both = Error.Constraint{
    kind:   .Unique,
    name:   "users_email_key",
    table:  Some("users"),
    column: Some("email"),
}

assert "${both}" == "unique constraint `users_email_key` on `users.email` failed"

impl Debug for Error

fn inspect(value: Error) -> String

impl Debug.inspect

impl Equatable for Error

fn equal(a: Error, b: Error) -> Bool

impl Equatable.equal

enum ConstraintKind
    Unique
    ForeignKey
    NotNull
    Check
    Exclusion
end

The kind of constraint an Error.Constraint reports.

impl Display for ConstraintKind

fn to_string(kind: ConstraintKind) -> String

impl Display.to_string

impl Debug for ConstraintKind

fn inspect(value: ConstraintKind) -> String

impl Debug.inspect

impl Equatable for ConstraintKind

fn equal(a: ConstraintKind, b: ConstraintKind) -> Bool

impl Equatable.equal

fn transaction<T>(conn: Conn, body: (Conn) -> Result<T, Error>) -> Result<T, Error>

Run body in a transaction on conn: begin, then commit when the body returns Ok, or rollback when it returns Err or traps. On a conn that is itself a transaction, the transaction is a savepoint, so an inner sql.transaction that fails rolls back only its own work. The result is the body’s, or the Err of a begin or commit that failed.

The rollback is a defer that runs whenever the transaction is left; after a commit it is the no-op Conn’s rules make it. A trap ends the task it happens in (Failure), so the rollback on a trap is seen from another task, after a spawned task’s Failed outcome.

fn close_quietly(conn: Conn) -> Unit

Close conn and drop a failed close: Conn.close for a defer, which cannot return anything. Defer it on the line after the connection is opened, so the connection is closed however the function ends; end the success path with Conn.close(conn)? when a failed close should be reported, since a close after a close does nothing (Conn’s rules).

fn purge(path: String) -> Result<Int, sql.Error>
    conn = sqlite.open(path)?
    defer sql.close_quietly(conn)

    count = Conn.exec(conn, Sql"DELETE FROM sessions WHERE expired")?
    Conn.close(conn)?
    Ok(count)
end

In a test group’s setup the deferred close runs when the test finishes:

group "sessions"
    setup begin
        conn = sqlite.open("app.db")?
        defer sql.close_quietly(conn)
        conn
    end

    test "deletes expired sessions", conn
        assert Conn.exec(conn, Sql"DELETE FROM sessions WHERE expired") == Ok(0)
    end
end
import std::sql::testing.Fake

fake = Fake.new()

begin
    defer close_quietly(fake)
    refute Fake.is_closed(fake)
    _ = Conn.exec(fake, Sql"DELETE FROM t")
end

assert Fake.is_closed(fake)
close_quietly(fake)
assert Fake.log(fake) == ["DELETE FROM t", "CLOSE"]

fn purge(conn: Fake) -> Result<Int, Error>
    defer close_quietly(conn)
    count = Conn.exec(conn, Sql"DELETE FROM sessions WHERE expired")?
    Conn.close(conn)?
    Ok(count)
end

purged = Fake.new()

assert purge(purged) == Ok(0)
assert Fake.log(purged) == ["DELETE FROM sessions WHERE expired", "CLOSE"]