aivi.db

Database records, query payloads, and handle vocabulary.

This module is the data vocabulary for database-backed features. It describes connections, statements, parameters, paging options, errors, and the DbSource handle marker. The current stdlib file does not export public query or commit functions on its own.

Current status: public database access is centered on @source db; this module is the shared vocabulary for that capability family.

Import

use aivi.db (
    DbSource
    DbError
    SchemaMismatch
    QueryFailed
    ConstraintViolation
    NestedTransaction
    ConnectionFailed
    SortDir
    Asc
    Desc
    Connection
    TableRef
    DbRow
    DbParam
    DbStatement
    DbPageOpts
)

Overview

Type	Purpose
Connection	Where to connect
TableRef A	Named table reference with a change signal
DbRow	Raw row data keyed by column name
DbParam	One bound query parameter
DbStatement	SQL text plus bound parameters
SortDir	Sort direction
DbPageOpts	Limit/offset paging options
DbError	Structured database failures
DbSource	Handle annotation for @source db

---

Connection

type Connection = {
    database: Text
}

A small record naming the database target to open. In practice this is often a filename or connection string.

use aivi.db (Connection)

value appDb : Connection = {
    database: "data/app.db"
}

---

TableRef A

type TableRef A = {
    name: Text,
    conn: Connection,
    changed: Signal Unit
}

Reference to a table together with the connection it belongs to and a signal you can watch for refreshes. The type parameter A lets you label the kind of rows you expect to read from that table.

use aivi.db (
    Connection
    TableRef
)

type User = {
    id: Int,
    email: Text
}

type Connection -> Signal Unit -> TableRef User
func usersTable = conn changed =>
    {
        name: "users",
        conn: conn,
        changed: changed
    }

---

DbRow

type DbRow = Dict Text Text

A raw result row keyed by column name. Every field value is stored as Text, so decoding into richer application types happens somewhere else.

use aivi.db (DbRow)

value sampleRow : DbRow = {
    entries: [
        { key: "id", value: "7" },
        { key: "email", value: "ada@example.com" }
    ]
}

---

DbParam

type DbParam = {
    kind: Text,
    bool: Option Bool,
    int: Option Int,
    float: Option Float,
    decimal: Option Decimal,
    bigInt: Option BigInt,
    text: Option Text,
    bytes: Option Bytes
}

A bound query parameter. kind tells the database layer which field to read. The matching optional field carries the actual value.

use aivi.db (DbParam)

type Text -> DbParam
func textParam = value =>
    {
        kind: "text",
        bool: None,
        int: None,
        float: None,
        decimal: None,
        bigInt: None,
        text: Some value,
        bytes: None
    }

---

DbStatement

type DbStatement = {
    sql: Text,
    arguments: List DbParam
}

A SQL statement paired with its bound arguments.

use aivi.db (
    DbParam
    DbStatement
)

type Text -> DbParam
func emailParam = value =>
    {
        kind: "text",
        bool: None,
        int: None,
        float: None,
        decimal: None,
        bigInt: None,
        text: Some value,
        bytes: None
    }

type Text -> DbStatement
func findUserByEmail = email =>
    {
        sql: "select * from users where email = ?",
        arguments: [emailParam email]
    }

---

SortDir

type SortDir = Asc | Desc

Sort direction for APIs that let you choose ordering.

---

DbPageOpts

type DbPageOpts = {
    limit: Int,
    offset: Int
}

Simple paging options.

• limit — how many rows to ask for
• offset — how many rows to skip first

use aivi.db (DbPageOpts)

value firstPage : DbPageOpts = {
    limit: 50,
    offset: 0
}

---

DbError

type DbError =
  | SchemaMismatch Text
  | QueryFailed Text
  | ConstraintViolation Text
  | NestedTransaction
  | ConnectionFailed Text

Structured failure reasons for database work.

• SchemaMismatch Text — the stored schema does not match what the code expects
• QueryFailed Text — the query could not be run
• ConstraintViolation Text — a constraint such as uniqueness or foreign keys was violated
• NestedTransaction — a second transaction was started before the first one finished
• ConnectionFailed Text — the database could not be opened or reached

use aivi.db (
    DbError
    SchemaMismatch
    QueryFailed
    ConstraintViolation
    NestedTransaction
    ConnectionFailed
)

type DbError -> Text
func describeDbError = error => error
 ||> SchemaMismatch msg     -> "schema mismatch: {msg}"
 ||> QueryFailed msg        -> "query failed: {msg}"
 ||> ConstraintViolation msg -> "constraint violation: {msg}"
 ||> NestedTransaction      -> "nested transactions are not supported"
 ||> ConnectionFailed msg   -> "connection failed: {msg}"

---

Using the handle

use aivi.db (
    DbSource
    Connection
    DbRow
    DbStatement
)

value connection : Connection = {
    database: "data/app.db"
}

@source db connection
signal database : DbSource

value loadUsersQuery : DbStatement = {
    sql: "select * from users",
    arguments: []
}

value loadUsers : Task Text (List DbRow) =
    database.query loadUsersQuery

The source-backed side of the family stays on db.connect / db.live. On-demand database work uses handle members such as database.query ... and database.commit ..., which return ordinary Task Text ... values on the current command path.