Module Sql

SQL database access, backed by sqlx. PostgreSQL, MySQL, and SQLite are selected from the connection URL scheme.

A Database is a linear reference to a shared pool. Query streams and transactions are independent linear leases. .split creates another linear pool handle; .close drops only this handle and never waits for siblings or outstanding leases.

Temporal values travel as text. The portable driver does not decode dedicated temporal column types, so on PostgreSQL and MySQL, cast temporal columns to text when reading (e.g. at::text) and cast text parameters when writing (e.g. $1::timestamptz); SQLite stores temporals as text natively.

type Sql.Database = iterative choice {
  .close => !,
  .execute(String, List<Sql.Value>) => (Try<Sql.Error, Nat>) self,
  .query(String, List<Sql.Value>) => (Try<Sql.Error, Stream<Sql.Error, Sql.Row>>) self,
  .split => (self) self,
  .transaction => (Try<Sql.Error, Sql.Transaction>) self,
}

A linear handle to a shared pool. It is not a connection. Splitting creates another handle to the same pool; both handles must eventually be closed.

type Sql.Error = String

Error type for SQL operations (a human-readable message).

type Sql.Format<a> = box choice {
  .parse(Sql.Value) => Try<Sql.Error, a>,
}

A reusable decoder for one present SQL value.

Required formats report SQL NULL, wrong value variants, and invalid textual temporal values as .err with a human-readable message. Missing columns are handled by Decode, before the format is called.

type Sql.Row = List<(String) Sql.Value>

Ordered result row. Column names are included because most everyday code wants name lookup, but duplicate names are still represented faithfully.

type Sql.Transaction = iterative choice {
  .commit => Try<Sql.Error, !>,
  .execute(String, List<Sql.Value>) => Try<Sql.Error, (Nat) self>,
  .query(String, List<Sql.Value>) => Try<Sql.Error, (List<Sql.Row>) self>,
  .rollback => Try<Sql.Error, !>,
}

A transaction owns one connection. Statement failure consumes the transaction and the implementation rolls back or drops the connection before reporting the error. Successful statements return self. A .commit error means the commit may or may not have taken effect (e.g. when it times out).

type Sql.Value = either {
  .bool Bool,
  .bytes Bytes,
  .float Float,
  .int Int,
  .null!,
  .text String,
}

A portable SQL scalar for parameters and result cells.

This intentionally remains ordinary Par data. Par comparison/display on Value is structural Par behavior, not SQL comparison semantics.

Only PostgreSQL produces .bool result cells; SQLite and MySQL surface stored booleans as integers (AsBool accepts both). Columns declared BOOLEAN on SQLite and MySQL cannot be decoded by the portable driver at all — store booleans in integer columns there.

dec Sql.AsBool : Sql.Format<Bool>

Required boolean format. A check, not a coercion, with one pragmatic exception: .int 0 and .int 1 are accepted as booleans, because SQLite and MySQL surface stored booleans as integers.

dec Sql.AsBytes : Sql.Format<Bytes>

Required byte-sequence format; accepts exactly .bytes values.

dec Sql.AsDate : [Time.Zone] Sql.Format<Time.Zoned>

Required date format: parses YYYY-MM-DD text, interpreted in the given zone.

dec Sql.AsDateTime : [Time.Zone] Sql.Format<Time.Zoned>

Required date-time format: parses zone-free civil text (T or space separator, optional fractional seconds), interpreted in the given zone. Covers text written by SQL itself, such as SQLite's datetime('now').

dec Sql.AsFloat : Sql.Format<Float>

Required floating-point format; accepts exactly .float values.

dec Sql.AsInt : Sql.Format<Int>

Required integer format; accepts exactly .int values.

dec Sql.AsText : Sql.Format<String>

Required text format; accepts exactly .text values.

dec Sql.AsTimestamp : Sql.Format<Time.Instant>

Required timestamp format: parses zone-aware text into an instant. Accepts RFC 3339, with T or a space as the separator, and Z or any numeric offset — this covers PostgreSQL's timestamptz::text output.

dec Sql.Date : [Time.Zoned] Sql.Value

Renders a zoned date-time as ISO date text, e.g. 2024-07-01, as a .text parameter. Cast in SQL when the backend needs a concrete temporal type.

dec Sql.DateTime : [Time.Zoned] Sql.Value

Renders a zoned date-time as zone-free ISO date-time text, e.g. 2024-07-01T12:34:56, as a .text parameter; sub-second precision is dropped. Cast in SQL when the backend needs a concrete temporal type.

dec Sql.Decode : [Option<Sql.Value>] <a>[Sql.Format<a>] Try<Sql.Error, a>

Decode an optional value with a format. Missing columns return .err. Designed for pipe chains: row->Sql.Field("age")->Sql.Decode(Sql.AsInt)

dec Sql.Field : [Sql.Row, String] Option<Sql.Value>

Look up the first column with this name. .none! means missing column; .some.null! means present SQL NULL. Rows may contain duplicate names; pattern match on Row directly if duplicates matter.

dec Sql.Nullable : <a>[Sql.Format<a>] Sql.Format<Option<a>>

Accept SQL NULL as .none!; otherwise parse with the provided format and wrap the result in .some. Missing columns still error.

dec Sql.Open : [String] Try<Sql.Error, Sql.Database>

Connect and validate connectivity. The URL scheme picks the backend: postgres://..., mysql://..., sqlite://file.db, sqlite::memory:.

SQLite does not create a missing database file by default; open with sqlite://file.db?mode=rwc to create it. An in-memory SQLite database is pinned to a single connection, so drain or cancel its query streams before running further statements.

dec Sql.Timestamp : [Time.Instant] Sql.Value

Renders an instant as RFC 3339 text, as a .text parameter; cast in SQL (e.g. $1::timestamptz) when the backend needs a concrete temporal type.