Std.Db overview
Status: the Rust compiler (
rust/,cargo build --release -p sky) is the Sky compiler (the earlier compilers were removed in v0.29.0). Verified by the example sweep + compiler test suite (cargo test+ xtask gates). See../history/compiler/journey.mdfor the changelog.
One database API, two backends. Std.Db works identically against SQLite and PostgreSQL — pick the driver in sky.toml, never touch it again in your code. For record-shaped tables the recommended default is Std.Db.Store + Std.Codec: write one codec per type and let the Store drive the schema, the reads, and the writes — no hand-written SQL, no row mappers. A Store query also joins two stores (join / leftJoin), groups and aggregates (groupBy / aggregate / having), projects (select), filters on a subquery (inQuery / exists), and upserts (upsert / upsertWith). Drop down to raw Db.exec / Db.query only for what it cannot express: a join of three or more tables, a window function, a CTE, a regular-expression match, or a trigger (such as a counter column the database maintains on each insert) (the escape hatch shown later).
module Main exposing (main)
import Std.Codec as Codec
import Std.Db as Db
import Std.Db.Store as Store exposing (Store)
import Sky.Core.Task as Task
import Std.Log exposing (println)
type alias Todo =
{ id : Int, text : String, done : Bool }
todos : Store Todo
todos =
Store.fromCodec "todos" (Codec.auto { id = 0, text = "", done = False })
|> Store.serial "id" -- auto-increment Int PK
main =
Db.connect () -- reads `[database]` from sky.toml
|> Task.andThen
(\conn ->
Store.create conn todos
|> Task.andThen (\_ -> Store.insert conn todos { id = 0, text = "Write the doc", done = False })
|> Task.andThen (\_ -> Store.all conn todos)
|> Task.andThen
(\rows ->
println ("Got " ++ String.fromInt (List.length rows) ++ " todos")
)
)
|> Task.run
Std.Db.Store + Std.Codec — codec-driven persistence (recommended default)
For record-shaped tables, write one Codec per type (with Std.Codec) and
let Std.Db.Store drive the schema, the reads, and the writes — no hand-written
SQL, no row mappers, no SqlValue lists. The same codec also serves JSON.
import Std.Codec as Codec exposing (Codec)
import Std.Db.Store as Store exposing (Store)
import Std.Db as Db exposing (SqlValue(..))
type alias User =
{ id : String, email : String, verified : Int, createdAt : String }
users : Store User
users =
Store.fromCodec "users" (Codec.auto { id = "", email = "", verified = 0, createdAt = "" })
|> Store.primaryKey "id" -- or Store.serial "id" for an auto-increment Int PK
|> Store.unique "email"
|> Store.defaultInt "verified" 0
|> Store.defaultNow "createdAt"
-- Store.create conn users : Task Error () (dialect-correct DDL)
-- Store.insert conn users user : Task Error Int
-- Store.all conn users : Task Error (List User)
-- Store.findBy conn users "id" "u1" : Task Error (Maybe User)
Codec.auto blank derives the codec from a zero-value witness (since
v0.29.9 the compiler generates a typed encoder and decoder for a record of
scalars, enums, records, Maybe, List, Dict String and Dict Int; any
other record uses reflection, with the same text and errors):
scalars → typed columns, Maybe → nullable, list / nested-record → JSON TEXT
blob, nullary enum → readable name, a custom type with data → a tagged object
({"tag":"Circle","v0":2.5}, read back by each variant's payload types, v0.29.2).
A variant carrying a Decimal, Money, Currency, Json.Value or a type
variable cannot be read back: the field is a builder problem and
Store.fromCodec refuses the store. Give that field a taggedUnion codec
through Codec.autoWith [ ( "shape", shapeCodec ) ] blank. Two fields that
become one column or key (aB and a_b), at the top or in any record the field
holds (a nested record, a List, Maybe or Dict of records, a variant's payload),
are a builder problem too. So is a Json.Value slot whose blank holds no JSON
value (items = [] for a List Json.Value, more = Nothing for a Maybe Json.Value): put one in ([ Json.Encode.null ], Just Json.Encode.null) or
give the field Codec.value through Codec.autoWith. A Just written as null
(Just Nothing of a Maybe (Maybe a), Just Json.Encode.null) is refused by a
write wherever the Maybe is (a List, a record, a Dict value, a variant's
payload): it would read back as Nothing. Columns + JSON keys are
snake_case by default (priceMinor → price_minor); Codec.autoCamel
keeps camelCase; Codec.autoWith [ ("active", intBool) ] blank overrides one
field's codec (a Bool stored 0/1, a custom enum) while auto-deriving the rest
(a key is the column, "price_list", or the record field, "priceList"; a key
that is neither makes Store.fromCodec refuse the store, naming the keys); a
fully custom mapping uses Codec.object/Codec.field "col" .field.
Schema / DDL builders
Pipe these onto the store — each accepts the record field name or the snake column (a typo fails fast with the column list):
| Builder | Effect |
|---|---|
Store.primaryKey "id" | mark the PK (String/UUID key you provide) |
Store.serial "id" | auto-increment PK (INTEGER … AUTOINCREMENT / BIGSERIAL) |
Store.unique "email" | UNIQUE constraint |
Store.defaultNow "created_at" | a text column: DEFAULT now()/datetime('now'), stamped on insert (on SQLite insert/insertMany/upsert bind the app clock) |
Store.touchOnUpdate "updated_at" | stamped on insert and auto-bumped to now() on every update — no raw SQL |
Store.defaultText/defaultInt/defaultBool/defaultFloat "col" v | literal column DEFAULT (defaultBool → TRUE/FALSE on Postgres, 1/0 on SQLite; defaultFloat refuses NaN and Infinity) |
Store.index [ "customer_id", "created_at" ] | CREATE INDEX named ix_<table>_<h> |
Store.uniqueOn [ "customer_id", "code" ] | a unique index, ux_<table>_<h>; every column must be required (NULLs never conflict) |
Store.uniqueWhere [ "slug" ] (Store.neq "slug" (SqlString "")) | a partial unique index; the condition is written as constants (eq/neq/gt/…, isNull/notNull, inList, eqCol…, and_/or_/not_) |
Store.indexNamed "orders_customer" [ "customer_id" ] / Store.uniqueOnNamed "acct_code" [ "tenant_id", "code" ] | index / uniqueOn under a name you give: for a table whose index already exists under its own name (sky db check --code writes them when it adopts a table, v0.29.2) |
Store.defaultWith "id" (\_ -> SqlValue) | app-side computed default at insert (e.g. a UUID PK via the unit-arg Task.run idiom) |
Store.generated [ "id", "created_at" ] | columns insert/update OMIT so the DB fills them, added to the ones serial and the other builders marked |
<h> is 8 hex digits of a hash of the table, the columns, the unique flag
and the predicate, so every definition has its own name, and a changed
uniqueWhere condition gives a new index. create and migrate create the
declared indexes.
External columns. defaultWith, defaultNow, touchOnUpdate, the
default* builders and generated may also name a column the table has and
the codec does not carry, such as a created_at the record leaves out:
leads : Store Lead
leads =
Store.fromCodec "leads" leadCodec
|> Store.primaryKey "id"
|> Store.defaultWith "created_at" (\_ -> SqlInt (nowMs ()))
- The name is used as written, with no snake_case mapping. On PostgreSQL write it in lower case, because an unquoted name folds to lower case.
defaultWithwrites the computed value on every insert (copyIntoleaves the column to the database default).defaultNowstamps it on insert, andtouchOnUpdatealso bumps it onupdate. Adefault*builder andgenerateddeclare it only: the database default applies.- Nothing reads it back into the record.
- The Store never creates, adds or drops it. A
Std.Db.Schematable or a migration owns it.migrateandsky db pushcheck that the table has it, and a missing table or column is anErrnaming the column and the builder.createof a table that does not exist is anErr.sky db migrate --gennever writes an op for it. primaryKey,serial,unique,index,uniqueOnanduniqueWheretake codec columns only, so a typo in them stays an error.
Store.migrate and sky db migrate --gen follow one rule (user decision
D1): "A Maybe field gives a nullable column, any other field gives NOT NULL.
A declared builder default wins (defaultInt / defaultText / defaultBool /
defaultNow, plus a new defaultFloat), else the zero value of the type.
Existing columns are never altered. No builder makes a required field's column
nullable." Both paths render an added column through one runtime function, so
the DDL is the same per dialect:
| Field and builder | Added column (SQLite / PostgreSQL) |
|---|---|
Maybe, no default | ADD COLUMN c <type> (nullable) |
Maybe + a declared default | nullable, DEFAULT <d> |
required + defaultInt/Text/Bool/Float | NOT NULL DEFAULT <d> |
| required, no builder | NOT NULL DEFAULT 0 / '' / false |
required enum field (a Codec.enum field, or a nullary custom type under Codec.auto) | NOT NULL DEFAULT '<name>': the first name of the Codec.enum, or the witness's constructor name under Codec.auto ('' is not one of its names, so it would not decode) |
| required JSON (blob) field | NOT NULL DEFAULT '<json>', where <json> is that field's own zero (Codec.fieldZeros): its value in the blank record of Codec.auto, or its codec's zero in a Codec.object codec ([] / "" / 0 / null, an object of its fields' zeros). A sibling field with no zero does not take it away |
required JSON field with no zero (a taggedUnion, result or Codec.error field of a Codec.object codec, an object field holding one, a Schema.blob column) | NOT NULL with no default: an empty table takes it, a table with 1 or more rows is Err (a sky db migrate file fails at the same count, with the same advice) |
defaultNow (text) | PostgreSQL NOT NULL DEFAULT now(); SQLite NOT NULL DEFAULT '<time of the ALTER>' |
unique | added, then a unique index; a required unique column on a table with 2 or more rows is Err (the same count as a sky db migrate file) |
Notes: migrate adds and creates only, it never drops, renames or retypes,
and it never makes an existing nullable column NOT NULL. Run it from one
process: two replicas that migrate at once can race on ADD COLUMN. On
PostgreSQL, CREATE INDEX locks writes to the table while it runs, and from
PostgreSQL 11 ADD COLUMN … NOT NULL DEFAULT <constant or now()> needs no
rewrite (now() stamps the rows that exist with the time of the ALTER). A
plain String column keeps the zero ''. A Codec.map wrapper over a string
with its own rule cannot tell '' is wrong: declare a default for such a
field.
A required unique column in a --gen migration file is an addColumn op
(NOT NULL DEFAULT <d>) and then an addIndex op. On a table with 2 or more
rows every row takes the same default, so the index cannot be built: sky db migrate rolls the migration back and names the table, the column and the fix.
Give each row its own value in a data step, a {"kind": "raw", "sql": "UPDATE t SET c = …"} op between the two ops, or make the field a Maybe.
The _sky_migrations ledger checks a --gen migration file by a checksum of
its ops (a canonical form of the op list, the same on every dialect), not
by the SQL they render to. A later change to how an op renders never makes an
applied file look edited; an edit to the file is still a checksum mismatch.
A ledger that v0.28 wrote is upgraded on the first sky db migrate, with no
configuration (see docs/migration/v0.29.md).
pushProject and dumpSchema (so sky db migrate --gen and sky db push)
refuse a store whose builders recorded a problem (for example an unknown column
in unique, index or uniqueOn), naming the column, before any DDL. So
does the stores step of sky verify, when the entry module exposes db.
sky check (and sky build) warns first, at compile time (v0.29.2): a literal
column name given to a Store builder (primaryKey, serial, unique,
index, uniqueOn, uniqueWhere), to Store.findBy, or to orderAsc /
orderDesc / a where_ condition on a Store.query chain, that the store's
codec does not have. The warning lists the columns and a did-you-mean. It
speaks only when the source shows the columns (a Codec.auto / autoCamel /
autoWith over a record literal, or a Codec.object chain of literal names,
through top-level definitions). A name the chain declares outside the codec
(defaultWith, defaultNow, touchOnUpdate, the default* builders,
generated) is a column. Those builders take any column the table has, so
they warn only for a name that is a codec column spelled with another case or
_ ("Created_At" for created_at).
Writes
insert · insertMany (multi-row INSERTs in one transaction, split at the
engine's bound-parameter limit, so any number of rows is all-or-nothing — bulk /
time-series) · update
(by PK, whole record) · updateWhere (by Cond, whole record) · setFields
(PATCH by PK — SET only the named columns) · updateFields (PATCH by
Cond) · adjust (atomic SET col = col + delta for counters/stock) ·
upsert (INSERT … ON CONFLICT(pk) DO UPDATE — idempotent config rows;
needs a non-generated PK) · delete · deleteWhere.
Partial-column PATCH — the way to set one or two columns without rewriting the whole record (or naming a column that isn't in the codec's read shape):
-- mark an order shipped, touching only two columns
Store.setFields conn orders (SqlString orderId)
[ ( "status", SqlString "shipped" ), ( "tracking", SqlString code ) ]
-- atomic stock decrement (value depends on the current value — no read first)
Store.adjust conn products (Store.eq "id" (SqlString pid)) [ ( "stock", -qty ) ]
Conflict rules, expression SET, copy and RETURNING (v0.29.0). Each renders ONE statement, checks every column against the codec first, and binds every value as a parameter. They need SQLite 3.35+ (the bundled engine is 3.46) or PostgreSQL 9.5+.
| Verb | Statement | Returns |
|---|---|---|
insertOrIgnore conn s row | INSERT … ON CONFLICT DO NOTHING (any unique key) | 1 written, 0 already there |
insertOrIgnoreOn [ "dedupe_key" ] conn s row | … ON CONFLICT (dedupe_key) DO NOTHING | 1 or 0 |
upsertWith conn s row { conflictOn, onConflict } | … ON CONFLICT (cols) DO UPDATE SET … | rows affected |
updateSet conn s cond [ setCol / setValue / setWhen ] | UPDATE … SET c = other_col, d = CASE WHEN … END | rows affected |
copyInto / copyIntoOrIgnore conn target query [ ( col, from / const ) ] | INSERT … SELECT … | rows inserted |
insertReturning conn s row | INSERT … RETURNING … | the stored row |
updateFieldsReturning conn s cond sets | UPDATE … RETURNING … | the updated rows |
deleteWhereReturning conn s cond | DELETE … RETURNING … | the deleted rows |
-- claim once: of two concurrent calls with one key, exactly one gets 1
Store.insertOrIgnore conn sentEmails { key = "welcome:" ++ userId, sentAt = now }
-- a counter row per day, customer and event, in one atomic statement
Store.upsertWith conn dailyCounts row
{ conflictOn = [ "day", "customer_id", "event" ]
, onConflict =
[ Store.addExcluded "count" -- count = daily_counts.count + excluded.count
, Store.keepMin "first_at" -- the smaller; a NULL side gives the other
, Store.keepMax "last_at"
, Store.setExcluded "source" -- source = excluded.source
]
}
-- set columns from expressions, every row from its own values
Store.updateSet conn orderLines (Store.eq "wanted_at" (SqlInt 0))
[ Store.setCol "wanted_at" "status_at"
, Store.setWhen "status" (Store.eq "status" (SqlString "ai")) (SqlString "open") Store.keep
]
-- a copy or a roll-up from any query (after select / aggregate, a result field)
Store.copyInto conn enquiries
(Store.query supportRequests |> Store.where_ (Store.gt "created_at" (SqlInt cutoff)))
[ ( "id", Store.from "id" ), ( "kind", Store.const (SqlString "support") ), ( "customer_id", Store.from "customer_id" ) ]
-- the read-after-write of a claim, in one statement
Store.updateFieldsReturning conn jobs
(Store.and_ [ Store.eq "id" (SqlString jobId), Store.isNull "owner" ])
[ ( "owner", SqlString me ) ] -- [ job ] for the one caller that won, [] for the others
The rules, all checked before any statement:
conflictOn(andinsertOrIgnoreOn) is the primary key, auniquecolumn or auniqueOnset (not auniqueWhere), of required columns: both engines treat NULLs as distinct, so a nullable key never conflicts.- A conflict rule never names the primary key, a
conflictOncolumn, or a generated column (serial,defaultNow,defaultWith,generated), and a column has one rule.addExcludedneeds a requiredInt/Floatcolumn.keepMin/keepMaxrefuse a bool or JSON column. An empty rule list isDO NOTHING.touchOnUpdatecolumns take the clock on the update. upsertWithreturns rows affected;upsertreturns the primary key for an Int-keyed store (see Writes above).updateSet: a target is a codec column, not the primary key or a generated column, set once.setColcopies a column of the same kind (a nullable source needs a nullable target).setWhen target cond value Store.keepleaves the other rows as they are;Store.otherwise vsetsvon them. Each CASE parameter is cast to the column type, so it works on PostgreSQL forIntandBoolcolumns. EverysetValue,setWhenandotherwisevalue follows the value rule below. An empty list runs nothing and returns 0.copyInto: the source is any query, read as a subquery.fromnames a column of its rows (afterselect/aggregate, a result field),constbinds one value for every row. Afromcolumn fits its target as aselectsource fits its field (kinds and codecs, see Projection below), aconstvalue follows the value rule below, every required target column without a default is mapped, and so is everydefaultWithcolumn (the copy does not compute it). Aserialcolumn is never mapped. A join source needsselectfirst.copyIntoOrIgnoreskips a row that breaks a unique key.- RETURNING decodes each row through the codec.
insertReturninggives the row with itsserialid anddefaultNowstamp, and fails if no row comes back. A write that the database refuses (a UNIQUE violation inUPDATE … RETURNING) is anErr, never an empty list. An emptyupdateFieldsReturningset list is anErr. Inside a GET server-side render every one of these writes is suppressed, asDb.execis.
Value kinds. A SqlValue never reaches the database to be converted:
SQLite's CAST('abc' AS BIGINT) is 0, and a 2.7 in an integer column
truncates on SQLite and rounds on PostgreSQL. So one rule, the same on both
dialects, is checked before any statement:
| Column | Takes |
|---|---|
text (String, an enum) | SqlString |
int (Int) | SqlInt |
real (Float) | SqlFloat (finite) or SqlInt |
bool (Bool) | SqlBool |
| JSON (a list, record or custom type field) | SqlString holding JSON |
decimal (Codec.decimal, Codec.moneyIn) | SqlDecimal or SqlInt (a condition also takes SqlString decimal text, so findBy "12.30" matches 12.3) |
SqlBytes, SqlTime, SqlDecimal and SqlMoney fit no other Store column
(bind the text, SqlString (Decimal.toString d)). A decimal column refuses
SqlFloat (not exact: bind SqlDecimal (Decimal.fromFloat f)) and SqlMoney
(its currency would be dropped). NaN and Infinity fit none (SQLite stores NaN
as NULL).
Decimal columns (v0.29.2). Codec.decimal and Codec.moneyIn GBP give a
decimal column: plain NUMERIC on PostgreSQL (never NUMERIC(p,s), which
rounds on insert), and TEXT holding the canonical decimal (12.5) on SQLite,
where NUMERIC affinity would turn 0.1 into a REAL. The values are exact on
both:
-
On SQLite the Store reads the column through the
sky_decimalcollation in a comparison, anorderAsc/orderDescandminAs/maxAs, and sums it with thesky_decimal_sumaggregate (0.1 + 0.2is0.3). Sky registers both in every SQLite connection it opens.Store.toSqlshowsCOLLATE sky_decimal, which thesqlite3shell does not know, and a range condition with the collation cannot use a plain index. Raw SQL against the column sees text. -
On PostgreSQL the column is native
NUMERIC;sumAscasts toNUMERIC. -
Store.defaultDecimal "price" dgives aDEFAULT,defaultIntfits too, and a required decimal column added to a table with rows defaults to'0'. -
adjust,addExcluded,keepMin/keepMaxand the text matches (like,contains) refuse a decimal column before any statement. -
Codec.moneyIn GBPstores one currency. A Money in another currency is refused before the statement, never converted or relabelled. The code is read asMoney.currencyFromCodereads it (v0.29.2): 3 to 10 ASCII letters or digits in upper case, soCodec.moneyIn USDT,USDC,BTCand aCurrencyRawcode keep their currency, andCodec.moneyIn (CurrencyRaw "gbp")isCodec.moneyIn GBP. A code that is not a currency code is aStore.fromCodecbuilder problem. -
A
Codec.autoDecimal or Money field keeps its v0.23 JSON-text column in v0.29 ("12.34",{"amount":"12.34","currency":"GBP"}), so no existing table changes. -
Moving such a field to
Codec.decimalorCodec.moneyIn: convert the column first, then switch the codec. A JSON-text column compares as text ("10"sorts before"9.5"), and new writes (20) would mix with the old spelling ("9.5"). SoStore.migrateandStore.pushProjectrefuse a decimal field on such a column, namingtable.columnand the statement that converts it, and change nothing (migratenever alters a column).sky db checkruns the same check (Store.checkProjectData, v0.29.2), so it reports the same column as drift with the same text, on SQLite too, where the column type alone fits. A write never stores a JSON value its codec does not read back, in a JSON column or a scalar one (aCodec.list (Codec.moneyIn GBP)holding a USD Money, aCodec.enumvalue not in its pairs, a computed Decimal over 1000 characters, a NaN or infinite Float are refused, by every write verb, naming the verb and the column). Only the partial fields are checked (Codec.writeChecks, derived from the codec tree), on the encoded value in memory, so a store of plain fields pays nothing for the check. The conversion never drops a currency: forCodec.moneyIn GBPonly a Money in GBP becomes its amount, and forCodec.decimalno Money object converts.On PostgreSQL (NULL stays NULL; drop a
DEFAULTfirst and set it again after), aCodec.decimalfield (a quoted or bare decimal converts, a Money object makes the statement fail):ALTER TABLE items ALTER COLUMN price TYPE NUMERIC USING (CASE WHEN price::text LIKE '{%' THEN ('a Money object is not a decimal: ' || price::text) ELSE trim(both '"' from price::text) END)::numericA
Codec.moneyIn GBPfield: first check that this query returns no rows (each row it returns is a Money in another currency, or with no currency; change or move it, it is never relabelled GBP):SELECT cost FROM items WHERE cost::text LIKE '{%' AND upper(cost::json->>'currency') IS DISTINCT FROM 'GBP'then convert (a Money in another currency, or with no currency, makes it fail):
ALTER TABLE items ALTER COLUMN cost TYPE NUMERIC USING (CASE WHEN cost::text LIKE '{%' THEN (CASE WHEN upper(cost::json->>'currency') = 'GBP' THEN cost::json->>'amount' ELSE 'a Money object that is not in GBP: ' || cost::text END) ELSE trim(both '"' from cost::text) END)::numericOn SQLite the column stays
TEXT; the rows are rewritten to the canonical text (the refusal applies while any row still holds the quoted or object spelling). ACodec.decimalfield:UPDATE items SET price = substr(price, 2, length(price) - 2) WHERE price LIKE '"%"'A
Codec.moneyIn GBPfield, after the same check (SELECT cost FROM items WHERE cost LIKE '{%' AND upper(json_extract(cost, '$.currency')) IS NOT 'GBP'); a row in another currency is left as it is, and the store refuses the column again until it is gone:UPDATE items SET cost = CASE WHEN cost LIKE '{%' THEN (CASE json_type(cost, '$.amount') WHEN 'text' THEN json_extract(cost, '$.amount') ELSE cost -> '$.amount' END) ELSE substr(cost, 2, length(cost) - 2) END WHERE cost LIKE '"%"' OR (cost LIKE '{%' AND upper(json_extract(cost, '$.currency')) = 'GBP')A column of Money objects has no conversion to
Codec.decimal: it would drop each row's currency, so the refusal gives none. Read it withCodec.moneyIn <Currency>(every row in one currency) orCodec.moneyObject(a JSON column that keeps each row's currency).A JSON-number amount (
{"amount":0.12345678901234567891,...}) is copied as its JSON text, so it keeps every digit (v0.29.2;json_extractalone gives a REAL, which rounds it). A value the store does not read as a decimal (a REAL,1e3) makesStore.migraterefuse the column, naming the values.Put the statement in a migration file (
sky db migrate), then change the field toCodec.autoWith [ ( "price", Codec.decimal ) ] blank. A read withoutmigratestill reads a legacy"12.34"value, but only the converted column orders and compares by value. -
A written value (
updateFields,setFields,setValue,setWhen,otherwise,const, adefaultWithresult) is also checked against the field's codec:SqlNullonly into aMaybefield, an enum column takes one of its names, and a JSON column takes JSON its codec reads (SqlString "[\"a\"]"into aList Intfield is refused). A JSON column whose JSON Schema leaves a part open (a custom type, aResult, aDict) is checked by the store's codec reading the value, so JSON of another custom type is refused too. -
A compared value (
eq,neq,gt,gte,lt,lte,inList,findBy,delete, auniqueWherepredicate) follows the table, but aSqlStringcompared with an int, real or bool column is read as the column's kind ("42","1.5","true"), so an id taken from a URL compares the same way on both dialects. Text that is not a value of the kind is anErr.SqlNullis refused:col = NULLis never true, so useisNullornotNull. -
A declared default fits its column:
defaultTexta text column (an enum's default is one of its names) or a JSON column (JSON its codec reads),defaultIntanIntorFloatcolumn,defaultFloataFloatcolumn,defaultBoolaBoolcolumn.migratewrites the default into the rows that exist, so a default that does not fit would give every old row a value that does not read back. ASchema.Tabledefault follows the same rule (createTable,createSchemaandtoProjectrefuse it), and so does anaddColumnop in a migration file. -
adjustadds to a requiredIntorFloatcolumn: NULL plus a delta stays NULL.
A codec's own encoding is checked too: a value whose JSON is not of the
column's kind (an encoder and a shape that disagree) is an Err naming the
column, never 0, false or the value's Go text.
Counters that the database keeps by itself (an aggregate table kept current by triggers) stay raw SQL: write the trigger in a migration.
Reads — query builder
Composable, injection-safe Cond values; you never touch a SQL string:
Store.query users
|> Store.where_ (Store.eq "verified" (SqlInt 1))
|> Store.where_ (Store.or_ [ Store.endsWith "email" "@work.io", Store.gt "createdAt" (SqlString cutoff) ])
|> Store.orderDesc "createdAt"
|> Store.limit 20
|> Store.toList conn -- terminals: toList / toRows / toMaybe / count
Leaves (eq/neq/gt/gte/lt/lte/like/isNull/notNull/inList "col" v)
combine with and_/or_/not_; multiple where_ clauses AND together (so OR /
nesting is first-class). Store.noCond is the condition every row meets (the
same as and_ []), for a verb that takes a Cond when the code means every row. Store.sqlOf codec value filters by a typed value
(enum / Money / Time / a Codec.map wrapper) via its codec. Whole-table /
single-row shortcuts: all / findBy (its text is read as the column's
kind: Store.findBy conn notes "id" "42" on an Int key). Store.toSql conn q returns the SQL text
(with ?) and the parameters a query renders on that connection's dialect, or
the Err it would fail with.
inList takes a list of any length. Up to 1,000 values it renders
col IN (?, ?, …), one parameter per value. A longer list has one
placeholder, marked with its count (col = ANY(?/*sky-list:N*/) on
PostgreSQL, col IN (SELECT +value FROM json_each(?/*sky-list:N*/)) on
SQLite), and the runtime binds its N values as one parameter, so the list is
not stopped by the engine's limit on the parameters of one statement (SQLite
32,766, PostgreSQL 65,535). Both forms match the same rows, under not_ too,
and the SQL and parameters toSql returns run unchanged through Db.query.
On PostgreSQL an Int compared with a column names it in its placeholder
(?/*sky-int:table|col*/, and ?/*sky-list:N:int:table|col*/ for a long
list, v0.29.2). The runtime reads the column's type from the catalogue (once
per pool and table) and casts the value to BIGINT on an INTEGER or
SMALLINT column, so a value outside the column's range matches nothing, as
SQL compares an integer with a wider one, instead of failing with "out of
range"; the column's index is still used. Every other column (the Store's own
BIGINT, a NUMERIC, a TEXT column an Int field reads) compares the
value uncast, as before.
Every name is checked before any statement runs. A column argument is a
codec column (the record field or its snake name) or a jsonText reference. A
table, column, alias or JSON key must match [A-Za-z_][A-Za-z0-9_]* and be at
most 63 bytes, the primary key must be a codec column, and a builder that names
an unknown column (Store.unique "emial") makes every verb fail with that
name. The problem is loud even when the app shows the Err as an empty list:
it is logged at ERROR level once per table and problem, shown in the Sky
Console, and counted in the metric sky_store_problems_total{table}. An
unknown column in a query condition, an order, a set or a conflict rule is an
Err that is loud the same way. findBy and delete follow the same rule. A negative limit or offset
is an Err; offset without limit works on both dialects. A table or column
name that is an SQL keyword on both SQLite and PostgreSQL (order, select)
is a builder problem (v0.29.2): Store.fromCodec refuses the store, so
Store.migrate and every verb are an Err naming the column, before any
statement. A name only one engine reserves (user on PostgreSQL, index on
SQLite) is refused by Store.create and Store.migrate on that engine, naming
the column; nothing is created. The Store writes names unquoted, so give such a
field another column name (Codec.field "sort_order" .order Codec.int in a
Codec.object, or rename the field). Never build a column argument from user input: a
jsonText reference can read any key of a JSON column.
Text matching. like uses its pattern as given: % and _ are wildcards,
it does not escape %, _ or \, and it is case-sensitive on PostgreSQL but
ignores ASCII case on SQLite. Store.like "email" ("%" ++ domain) matches wrong
addresses when domain holds _. For text a user typed, use the escaped forms, which match the
same way on both dialects:
| Condition | Matches |
|---|---|
Store.contains "name" "50%" | the text contains the needle; %, _, ! are literal; case-sensitive |
Store.startsWith "name" "Ab" / Store.endsWith "email" "@work.io" | a prefix / suffix, case-sensitive |
Store.eqCi "name" "ada" | equal, ignoring case (LOWER(col) = LOWER(?)) |
Store.containsCi "email" "WORK" | contains, ignoring case |
An empty needle matches every non-NULL row. The column must be a text column.
SQLite's LOWER folds ASCII letters only; PostgreSQL folds all letters. To
escape a like pattern by hand, prefix !, % and _ with ! and use raw
SQL with ESCAPE '!'.
Regular expressions: not offered, on purpose. SQLite has no built-in
REGEXP (a portable form would need a function registered on every
connection), so Std.Db.Store has no regex condition. Store a normalised
column (lower case, punctuation removed) when you write the row, and match it
with contains / startsWith / endsWith / eqCi / containsCi. A regex
that must run in the database stays raw SQL for the one dialect you run.
Column against column. eqCol, neqCol, gtCol, gteCol, ltCol and
lteCol compare two columns of the same kind:
Store.where_ (Store.gtCol "last_staff_at" "customer_read_at").
Inside a JSON column. Store.jsonText "props" "source" names one key of a
JSON text column and works wherever a column name does (a condition,
groupBy, keyAs, selectAs, an order term). It reads the key's text, or
NULL when the key is missing. A JSON true reads "true" on both dialects. A
number reads as its text, which can differ (1e2 reads 100.0 on SQLite and
100 on PostgreSQL), and an object differs in spacing. The column must be a
text or blob column, and a compared value must be SqlString. A row with
malformed JSON makes the query Err, never a short list.
Grouped aggregates
groupBy + aggregate give one row per group, decoded by a result codec.
Each field of the result codec is covered by exactly one Agg:
type alias Tally = { customerId : String, sent : Int, opened : Int, lastSentAt : Int }
Store.query invoices
|> Store.where_ (Store.gt "sent_at" (SqlInt since))
|> Store.groupBy [ "customer_id" ]
|> Store.aggregate (Codec.auto { customerId = "", sent = 0, opened = 0, lastSentAt = 0 })
[ Store.key "customer_id"
, Store.countAs "sent"
, Store.countIfAs "opened" (Store.gt "opened_at" (SqlInt 0))
, Store.maxAs "last_sent_at" "sent_at"
]
|> Store.having (Store.gte "sent" (SqlInt 2))
|> Store.orderDesc "sent"
|> Store.toRows conn -- : Task Error (List Tally)
Agg | Renders | Result field |
|---|---|---|
key "col" / keyAs "field" "col" | the group column (it must be in groupBy) | the column's kind; Maybe if the column is nullable |
countAs "field" | COUNT(*) | Int |
countIfAs "field" cond | rows of the group that match cond (0 when none) | Int |
countDistinctAs "field" "col" | COUNT(DISTINCT col) | Int |
sumAs "field" "col" | the sum of an Int / Float column, or of a decimal column (Codec.decimal, Codec.moneyIn) exactly on both engines; 0 when empty | the column's kind |
minAs / maxAs "field" "col" | the smallest / largest value (not a Bool column) | the column's kind; Maybe when the column is nullable or there is no groupBy |
having and the order terms after aggregate name result fields. Without
groupBy the result is one row over the selection: an empty table gives zero
counts and sums and Nothing for minAs / maxAs. A wrong kind, an unknown
column or field, a key that is not grouped, or a field with no Agg is an
Err before any statement runs. Text compares by the database collation
(SQLite BINARY, PostgreSQL the database collation), so minAs / maxAs, gt
and ORDER BY on text can differ between the dialects.
Projection, DISTINCT, newest row per key
Store.query orders |> Store.select summaryCodec |> Store.toList conn
-- each field of summaryCodec reads the column of the same name
Store.query orders |> Store.selectColumn "customer_id" Codec.string |> Store.distinct |> Store.toList conn
-- : Task Error (List String)
Store.query events
|> Store.selectAs sourcedCodec [ ( "source", Store.jsonText "props" "source" ) ]
Store.query orders
|> Store.where_ (Store.eq "status" (SqlString "open"))
|> Store.latestPer [ "customer_id" ] "created_at" -- the newest open order per customer
|> Store.toList conn
A nullable column needs a Maybe field. A field's codec must read every value
its source column holds, so a projection that could fail on a row is refused
before it runs: a Maybe x field reads a source x or Maybe x, a
String field reads a String or an enum, a Float field an Int, an enum
field an enum whose names it all has, and a list, record or custom type is
compared part by part. A List Int column into a Maybe (List String) field,
or a String column into a Maybe enum field, is an Err before any
statement. The same rule holds for selectAs, selectColumn, a key,
minAs or maxAs result, a copyInto from column and setCol. With distinct and a projection,
every order column must be a selected column. latestPer never picks a NULL as
the newest, breaks a tie by the larger primary key, and does not combine with
distinct, groupBy or aggregate. count counts the rows such a query
returns.
What a read refuses. Each stored value is read by its column's kind. SQL
NULL is Nothing. A value that does not read as the kind ('abc' in an
integer column, text that is not JSON in a JSON column, 2 in a bool column)
is an Err naming the table, the column and the kind, never Nothing. The
shapes earlier versions wrote still read: an integer stored as text ('5', a
v0.28 nested Maybe (Maybe Int)), an integral number in a float form, a bool
stored as 1 / 0 on SQLite or true / false on PostgreSQL, and a
defaultNow timestamp, which is text. The TEXT column v0.28 made for a
nested Maybe scalar stays TEXT and takes v0.29 writes on both dialects, but
it sorts as text (the v0.29 migration guide has the one-time fix). A column whose value its field's codec
does not read, at any depth (JSON of another custom type, an element of a
List (Maybe Shade) that is not a Shade), is an Err naming the table and
the column too: Codec.maybe reads only null as Nothing, so a JSON null
element is Nothing and any other bad value fails. setValue and a declared
default of such JSON are refused before the statement. A selectRaw result
must hold every codec column (write NULL AS col for a Maybe field it
leaves out).
NULL ordering. An order term on a nullable column sorts NULLs first ascending and last descending, on both dialects (SQLite's own default, now explicit for PostgreSQL). A term on a required column renders as before.
Joins of two stores
Store.join invoices customers
(Store.onAll [ ( "region_id", "region_id" ), ( "customer_id", "id" ) ])
|> Store.whereLeft (Store.eq "region_id" (SqlString regionId))
|> Store.whereRight (Store.eq "archived" (SqlBool False))
|> Store.orderLeftDesc "sent_at"
|> Store.limit 50
|> Store.toPairs conn -- Task Error (List ( Invoice, Customer ))
Store.leftJoin invoices customers (Store.on "customer_id" "id")
|> Store.toPairsMaybe conn -- Task Error (List ( Invoice, Maybe Customer ))
One statement. In SQL the left store is a and the right store is b, and
each result column has a quoted alias (a.sent_at AS "a__sent_at"), so two
tables with the same column names never clash and PostgreSQL keeps a camelCase
name. Each side decodes through its own codec.
- Names.
whereLeft/whereRightandorderLeftAsc/orderLeftDesc/orderRightAsc/orderRightDescread one side. Inwhere_,orderAsc,select,groupByand anAgg, a bare name reads the store that has it; a name both stores have must be written"a.col"or"b.col", else the query is anErr. ON columns must be of one kind. Every name is checked against its own codec before the query runs. - LEFT join.
whereRightgoes into the ON clause, so it picks the right row and never drops a left row (awhere_on a right column filters rows as usual). A right column can be NULL in any row, soselectand anAggneed aMaybefield for it. - Reshape a join.
select/selectAs,groupBy+aggregateandcounttake a join:Store.join bills clients (Store.on "client_id" "id") |> Store.groupBy [ "b.name" ] |> Store.aggregate totalCodec [ Store.keyAs "name" "b.name", Store.sumAs "total" "a.amount" ]. - Limits. A join reads two stores: a join of three tables is not
expressible (use
selectRaw).latestPerdoes not take a join.whereLeft/whereRight/orderLeft*/orderRight*on a query that is not a join are anErrwhen it runs (the type cannot tell).
Subquery conditions
-- Delete the rows of old batches in ONE statement: rows added meanwhile are seen.
Store.deleteWhere conn importRows
(Store.inQuery "batch_id"
(Store.query importBatches
|> Store.where_ (Store.lt "created_at" (SqlInt cutoff))
|> Store.column "id"))
-- Customers with no invoice sent since a date.
Store.query customers
|> Store.where_
(Store.notExists invoices
(Store.correlate "customer_id" "id")
(Store.gt "sent_at" (SqlInt since)))
column "id" q names one column of a query (a Subquery), checked against the
inner store's codec. inQuery / notInQuery compare an outer column with it
(the two of one kind). exists / notExists take a store, the correlate inner outer pairs that tie it to the outer row (correlateAll for several), and a
condition on the inner store (Store.noCond for none). They work in where_,
in a join, and in the writes that take a Cond (deleteWhere, updateWhere,
updateFields, adjust). The inner query is sq1 (sq2 inside it), and it
takes where_, orderAsc / orderDesc, limit and offset only (a limit
or offset needs an order); a select, aggregate, distinct, latestPer or
join there is an Err. Subquery conditions do not apply in having.
NULL rules. The subquery leaves out its NULLs (… AND sq1.id IS NOT NULL),
so an outer NULL never matches inQuery, and notInQuery (or not_ (inQuery …)) keeps an outer value that is not in the set even when the inner column
holds NULL. An outer NULL is left out by notInQuery when the inner set has
rows, and kept when it is empty (NULL NOT IN () is true on both engines).
Store.selectRaw — the last escape hatch
selectRaw runs any SQL and decodes each row into a typed projection
record via a codec (the sqlx split: you own the SQL, the codec owns the mapping
— no ORM, no relations, no N+1). Use it for a shape the builders do not model
(a join of three tables, a window function, a CTE):
type alias Pair = { ideaId : String, author : String }
Store.selectRaw conn (Codec.auto { ideaId = "", author = "" })
"SELECT i.id AS idea_id, u.name AS author FROM ideas i JOIN users u ON u.id = i.author_id"
[] -- : Task Error (List Pair)
Raw Std.Db (query/exec/withTransaction) remains the escape hatch for
anything else. Store.transaction conn (\tx -> …) groups Store ops atomically.
Store.toTable + Store.project build a db : Store.Project for
sky db migrate --gen (see Schema migrations). Import
Std.Db.Store and Std.Db qualified — query/migrate overlap.
Exact signatures are the source of truth in sky doc:
sky doc Std.Db.Store · sky doc Std.Codec.
Typed schema — Std.Db.Schema (dialect-safe DDL)
Hand-written CREATE TABLE is the one place the "two backends, one API"
promise leaks: INTEGER is 8-byte on SQLite but 4-byte on Postgres (a
millisecond timestamp overflows it), AUTOINCREMENT is BIGSERIAL on
Postgres, and datetime('now') is now(). Develop on SQLite, deploy on
Postgres, and these bite you in production.
Std.Db.Schema closes that: define the table as a typed value, and
Schema.createTable emits the dialect-correct DDL for whichever backend the
connection uses. The same definition is right on both — Ecto/Diesel in
spirit (explicit, composable), no magic ORM.
import Std.Db.Schema as Schema exposing (text, int, bigInt, bool)
products : Schema.Table
products =
Schema.table "products"
[ Schema.id "id" -- TEXT PRIMARY KEY
, text "slug" |> Schema.notNull |> Schema.unique
, text "name" |> Schema.notNull
, int "price_minor" |> Schema.notNull |> Schema.defaultInt 0
, bool "active" |> Schema.notNull |> Schema.defaultBool True
, bigInt "created_at" |> Schema.notNull |> Schema.defaultInt 0
]
|> Schema.withIndex "idx_products_slug" [ "slug" ]
setup : Db -> Task Error ()
setup conn =
Schema.createTable conn products
created_at above renders as INTEGER on SQLite (which is 8-byte, so millis
fit) and BIGINT on Postgres — one bigInt declaration, correct on both.
Column types (Schema.<type> "name"): text, int, bigInt, real,
bool, timestamp, blob, json, plus id (TEXT primary key — the common
Sky pattern) and serial (auto-increment integer PK → INTEGER PRIMARY KEY AUTOINCREMENT on SQLite, BIGSERIAL PRIMARY KEY on Postgres).
Modifiers (pipe them on): primaryKey, notNull, unique,
autoIncrement, defaultInt n, defaultText s, defaultBool b, defaultNow
(datetime('now') / now()), references "table" "col" (foreign key).
Type mapping — each backend's natural column type, with dev==prod
consistency at the decoded-value level (via Std.Db.Decode): bool →
BOOLEAN on Postgres / INTEGER 0/1 on SQLite (a SqlBool param binds to
both, and Decode.bool reads both back to a Sky Bool); int/bigint/timestamp
→ BIGINT on Postgres / INTEGER on SQLite (the overflow-safe one — both read
as int64); real→DOUBLE PRECISION, blob→BYTEA on Postgres; json→TEXT
on both. createTable is idempotent (CREATE TABLE / INDEX IF NOT EXISTS);
createSchema conn tables runs a list in order.
Schema only builds the tables — you still write Db.exec / Db.query for
data. It removes the one dialect-specific string from your app; the parameter
layer (below) already handles the rest.
Reaching the migration tooling — Schema.toProject. sky db push and
sky db migrate --gen read a db : Store.Project from your entry module. A
Schema.Table-based app bridges into that with one line — no rewrite into
codec stores:
-- Data.sky
allTables : List Schema.Table
allTables = [ products, users, orders, ... ]
-- Main.sky (or re-export from Data)
db : Store.Project
db = Schema.toProject allTables
Table name, primary key, UNIQUE, NOT NULL, DEFAULT, autoincrement and
the withIndex / withUniqueIndex indexes (with their own names) carry
through, so sky db push creates the columns and missing indexes and
migrate --gen diffs them (addIndex / dropIndex). A column added to an
existing table follows the Store.migrate rule above. defaultNow on a
timestamp / bigInt / int column is epoch milliseconds.
One column type on both paths (v0.29.2). Schema.int is 64-bit, like a
Sky Int: createSchema / createTable and pushProject (toProject …) (and
sky db push / migrate) all create BIGINT on Postgres. Before v0.29.2
createSchema made a 4-byte INTEGER and pushProject a BIGINT, so a
fresh database differed with the path that made it. BIGINT is the size of a
Sky Int (a Go int64): an INTEGER column fails with "integer out of range"
for any value above 2,147,483,647 (epoch milliseconds, large ids, money in
minor units). Neither path alters a column that exists, and migrate --gen
proposes no change for it: a table made by an older createSchema keeps
INTEGER. Store.migrate and pushProject treat it as a match, and log a
one-time WARN that names the column, says it holds only 32-bit values and
gives the statement that widens it (ALTER TABLE orders ALTER COLUMN day TYPE BIGINT); widening is your choice. SQL that needs a 4-byte integer casts
(DATE '1970-01-01' + day::int).
Schema.blob has two types, and the difference is loud (v0.29.2).
createSchema makes a Schema.blob column binary (BYTEA on Postgres,
BLOB on SQLite). toProject (sky db push, Store.pushProject) makes the
same column TEXT holding JSON, because a Std.Db.Store reads and writes a
JSON field (a list, a record, a custom type, a Json.Value, Schema.json)
as text. Both types are kept, so:
Store.migrateandStore.pushProjectrefuse a Store JSON field whose column the table has asBYTEA/BLOB(anErrnamingtable.column): PostgreSQL would read the JSON's escapes into the bytes and every row would be misread. The column is not changed. Fix it once withALTER TABLE t ALTER COLUMN c TYPE TEXT USING convert_from(c, 'UTF8')on Postgres (on SQLite copy the table into one with aTEXTcolumn), and declare the columnSchema.json; or keep binary data out of the Store and read it withDb.query.- A
Schema.blobcolumn that the table has asTEXT, or thatpushis about to create asTEXT, is named once at WARN: binary bytes written to it withDb.execfail on Postgres. ASchema.blobcolumn onBYTEA/BLOBis a match. sky verify's stores step names everySchema.blobcolumn ofdb(! stores Std.Db.Schema: column files.data is declared Schema.blob …). It is a notice, not a failure.
Use Schema.json for JSON, and Schema.blob only for a table you create with
createSchema and read with Db.query.
Naming tip.
Schema.textcollides withStd.Html/Std.Ui'stextif both are exposed unqualified. In a module that already doesimport Std.Html exposing (..)(orStd.Ui), import the schema module qualified —import Std.Db.Schema as Schemaand writeSchema.text "col"— rather thanexposing (text).int/bigInt/booldon't collide.
Schema is declarative table setup (idempotent CREATE … IF NOT EXISTS). For
versioned schema evolution with checksums and an applied-migrations ledger,
use Db.migrate — the two are complementary (see examples/36-composite-server
for the migration-tooling shape).
Std.Db.Table — one definition, no decoder, no SqlValue lists
Schema + a hand-written decoder + hand-written insertFields means restating
your columns three times. Std.Db.Table collapses that: one Table a value —
carrying a zero-value witness of your record — is the single source of truth
for the DDL, typed reads, and typed writes. The record type declares the columns
(the runtime reflects the Go struct it lowers to); field ↔ column is
camelCase ↔ snake_case, and type ↔ column is the same dialect-safe mapping
(Int→BIGINT, Bool→bool, Maybe a→nullable, String→TEXT, Float→REAL).
import Std.Db.Table as T exposing (Table)
type Category = Stickers | Bookmarks | Prints
type alias Product =
{ id : String, slug : String, priceMinor : Int, active : Bool
, category : Category, note : Maybe String }
blank : Product
blank = { id = "", slug = "", priceMinor = 0, active = False, category = Stickers, note = Nothing }
products : Table Product
products =
T.table "products" blank
|> T.primaryKey "id"
|> T.unique "slug"
|> T.enum "category" [ ( Stickers, "stickers" ), ( Bookmarks, "bookmarks" ), ( Prints, "prints" ) ]
-- setup: T.createTable conn products
-- read: T.all conn products : Task Error (List Product)
-- T.select conn products "WHERE active = ? ORDER BY slug" [ SqlBool True ]
-- T.findBy conn products "slug" "sticker-pack" : Task Error (Maybe Product)
-- write: T.insert conn products p / T.update conn products "id" p.id p / T.delete conn products "id" p.id
The boundary (the sqlx split): Table owns the record↔row mapping; SQL
stays SQL. select takes a raw WHERE/ORDER BY/JOIN/LIMIT tail, and a
join decodes into any record whose fields match the projection — define an
OrderSummary = { id, reference, itemCount } and
T.select conn orderSummary "SELECT o.id, o.reference, COUNT(i.id) AS item_count FROM orders o JOIN order_items i … GROUP BY o.id" [].
No relations / eager-loading magic — associations are an explicit second query.
Enums & custom types. Nullary enums lower to a runtime int (no name), so map
them with explicit (value, name) pairs via T.enum (stable across reordering,
stored as readable TEXT). For any other type — data-carrying ADTs, JSON blobs,
nested records — use T.codec "col" encode decode with your own encode : v -> String / decode : String -> v (the runtime calls them).
When to drop down. Schema / Decode / SqlValue remain the escape hatch
for the cases Table doesn't cover (partial indexes, bespoke projections,
performance-critical hot paths). Table is the default for single-table CRUD.
What's in the surface
Every operation that touches the disk returns Task Error a (per the Task-everywhere doctrine). Parameter-supplied helpers (Db.getString, Db.getInt) return bare values because the default plugs the failure case at the call site.
Connect / open / close
| Function | Type | Notes |
|---|---|---|
Db.connect | () -> Task Error Db | Reads the DSN from sky.toml [database] path/url (or SKY_DB_PATH / DATABASE_URL); the driver follows from its shape. Preferred shape. |
Db.open | String -> String -> Task Error Db | Takes a DSN, with the driver name as documentation — the shape of the DSN still decides, so Db.open "postgres" "./app.db" opens SQLite. |
Db.close | Db -> Task Error () | Releases the connection pool |
Statements
| Function | Type | Notes |
|---|---|---|
Db.exec | Db -> String -> List a -> Task Error Int | Parameterised insert / update / delete; returns affected rows. v0.16.26+: passing List SqlValue gives per-column type fidelity; v0.16.24+: Maybe a binds as SQL NULL / unwrapped value directly. |
Db.execRaw | Db -> String -> Task Error Int | DDL or multi-statement script — no parameter binding (vulnerable to injection if sql is built from user input). Use for CREATE TABLE, CREATE INDEX. |
Db.query | Db -> String -> List a -> Task Error (List (Dict String String)) | Returns rows as Dict String String (every column stringified at the boundary). Same param semantics as Db.exec. |
Db.queryDecode | Db -> String -> List a -> b -> Task Error (List b) | Decoder is parametric — typically a Dict String String -> Result Error a function; failures abort the whole query |
Db.updateFields | Db -> String -> List (String, SqlValue) -> List (String, SqlField) -> Task Error Int | v0.16.26+ PATCH-style update with dynamic SQL. SetField v includes the column with ? placeholder; OmitField drops it from the SET clause entirely (database keeps existing value). Column-name validation prevents SQL injection via identifiers. |
Db.insertFields | Db -> String -> List (String, SqlField) -> Task Error Int | v0.16.29+ (#585) INSERT counterpart of updateFields. SetField v includes the column with ? placeholder; OmitField drops it from the column list so the database applies its DEFAULT. All columns OmitField → INSERT INTO <table> DEFAULT VALUES. Same identifier validation + dbBindArg normalisation as updateFields. |
Db.insertFieldsReturning | Db -> String -> List (String, SqlField) -> String -> Decoder a -> Task Error (List a) | v0.16.30+ (#586) Decoding counterpart of insertFields. Appends RETURNING <projection> (caller-controlled — same trust model as queryDecode's SQL), then decodes each returned row through decoder. Requires SQLite ≥ 3.35 (Mar 2021) or PostgreSQL. Unblocks emission of id / created_at autodefaults + sky-sqlgen's @omit + RETURNING shapes. |
Typed parameter binding via SqlValue (v0.16.26+)
Sky's HM keeps List a homogeneous, so mixed-type SQL params (e.g. String + Maybe Int + Bool) need a tagged variant. The SqlValue ADT in Std.Db covers SQLite's 5 storage classes plus PostgreSQL's common extensions:
type SqlValue
= SqlString String -- TEXT / VARCHAR / CHAR / UUID-as-text / JSON-as-text
| SqlInt Int -- INTEGER / SMALLINT / BIGINT / SERIAL
| SqlFloat Float -- REAL / DOUBLE PRECISION
| SqlBool Bool -- BOOLEAN
| SqlBytes String -- BLOB / BYTEA
| SqlDecimal Decimal -- NUMERIC / DECIMAL
| SqlTime Int -- TIMESTAMP / DATE / TIMETZ (Unix millis)
| SqlMoney Money -- TEXT as "ISO_CODE AMOUNT" (lossless round-trip)
| SqlNull SqlValue -- typed NULL via wrapped type-witness
Maybe-lifting helpers cover the common nullable-column case: fromMaybeString / fromMaybeInt / fromMaybeFloat / fromMaybeBool / fromMaybeBytes / fromMaybeDecimal / fromMaybeTime / fromMaybeMoney.
-- INSERT with mixed types — no stringify, no Ffi.toAny
Db.exec conn
"INSERT INTO orders (id, customer, total, paid_at) VALUES (?, ?, ?, ?)"
[ SqlInt orderId
, SqlString customerUuid
, SqlMoney total -- serialises as "USD 1234.56"
, fromMaybeTime maybePaidAt -- nullable column
]
For partial UPDATEs where you want to skip columns entirely (PATCH semantics — set this, clear that, leave the rest alone), Db.updateFields takes a List (String, SqlField):
type SqlField
= SetField SqlValue -- column = ?, bind value (which may be SqlNull)
| OmitField -- column not in SET clause; database keeps existing value
Db.updateFields conn "orders"
[ ("id", SqlInt orderId) ] -- WHERE
[ ("status", SetField (SqlString "refunded")) -- change
, ("paid_at", SetField (SqlNull (SqlTime 0))) -- explicit NULL
, ("notes", OmitField) -- leave alone
]
-- → UPDATE orders SET status = ?, paid_at = ? WHERE id = ?
For INSERTs with DEFAULT-omittable columns (set this, NULL that, let the database fill the rest), Db.insertFields is the INSERT counterpart — same SqlField three-state model, no WHERE clause:
Db.insertFields conn "items"
[ ("name", SetField (SqlString "Widget")) -- value
, ("status", OmitField) -- → DEFAULT
, ("note", SetField (SqlString "first batch")) -- value
]
-- → INSERT INTO items (name, note) VALUES (?, ?)
-- (status omitted; database applies its DEFAULT)
All columns OmitField → INSERT INTO <table> DEFAULT VALUES (one all-defaults row). Returns the affected-row count.
When you need the values the database picked — autoincrement id, DEFAULT created_at, a generated column — pair with Db.insertFieldsReturning instead:
Db.insertFieldsReturning conn "items"
[ ("name", SetField (SqlString "Widget"))
, ("status", OmitField) -- → DEFAULT 'pending'
, ("note", SetField (SqlString "first batch"))
]
"id, status" -- RETURNING clause
rowDecoder
-- → INSERT INTO items (name, note) VALUES (?, ?)
-- RETURNING id, status
-- decoded as List Row (typically one row).
The projection string is a caller-controlled SQL fragment — the same trust model as queryDecode's SQL. Schema-derived literals (sky-sqlgen) are safe; user input is not. Requires SQLite ≥ 3.35 (Mar 2021) or PostgreSQL — same as every other RETURNING use already in Std.Db.
Money round-trips via Std.Db.Decode.money on the read side — paired with SqlMoney on the write side for lossless single-TEXT-column storage that survives PostgreSQL NUMERIC + CHAR(3) if you decompose at the call site instead.
Conventional CRUD (auto-generated SQL)
For any table with an id column, these save you from hand-writing SELECT/UPDATE/DELETE:
| Function | Type | Notes |
|---|---|---|
Db.insertRow | Db -> String -> Dict String String -> Task Error Int | Returns new row id |
Db.getById | Db -> String -> String -> Task Error (Maybe (Dict String String)) | Single row by primary key (id is a string at the wire boundary). Nothing when missing. |
Db.updateById | Db -> String -> String -> Dict String String -> Task Error Int | Returns affected rows |
Db.deleteById | Db -> String -> String -> Task Error Int | Returns affected rows |
Db.findOneByField | Db -> String -> String -> a -> Task Error (Maybe (Dict String String)) | Single-row equality lookup |
Db.findManyByField | Db -> String -> String -> a -> Task Error (List (Dict String String)) | All matches by equality |
Db.findByConditions | Db -> String -> Dict String String -> Task Error (List (Dict String String)) | AND-joined equality across every key/value in the conditions dict |
Db.unsafeFindWhere | Db -> String -> String -> List a -> Task Error (List (Dict String String)) | Raw WHERE + bound params — clause is appended verbatim, vulnerable to injection if built from user input |
Transactions
| Function | Type | Notes |
|---|---|---|
Db.withTransaction | Db -> (Db -> Task Error a) -> Task Error a | Commits on Ok, rolls back on Err automatically |
Row accessors (default-supplied → bare return)
| Function | Type | Notes |
|---|---|---|
Db.getField | String -> row -> String | Reads a field as a String (the canonical row-element shape) |
Db.getString | String -> row -> String | Same as getField — kept for symmetry with the typed helpers below |
Db.getInt | String -> row -> Int | Parses to Int; 0 when missing or unparseable |
Db.getBool | String -> row -> Bool | Parses to Bool; False when missing |
These return bare values — see default-supplied helpers stay bare. Reach for a typed decoder via Db.queryDecode when "missing" needs to fail loud.
Walkthrough — CRUD with transactions
A canonical flow: create the table, insert rows in a transaction (atomic), and query back — with Std.Db.Store driving the schema, the writes, and the reads from one codec.
module Main exposing (main)
import Sky.Core.Prelude exposing (..)
import Sky.Core.Task as Task
import Std.Codec as Codec
import Std.Db as Db
import Std.Db.Store as Store exposing (Store)
import Std.Log exposing (println)
type alias Todo =
{ id : Int
, text : String
, done : Bool
}
todos : Store Todo
todos =
Store.fromCodec "todos" (Codec.auto { id = 0, text = "", done = False })
|> Store.serial "id"
main =
Db.connect ()
|> Task.andThen
(\conn ->
Store.create conn todos
|> Task.andThen
(\_ ->
-- All three inserts atomic. If any fails, none commit.
Store.transaction conn
(\tx ->
Store.insert tx todos { id = 0, text = "Write the doc", done = False }
|> Task.andThen (\_ -> Store.insert tx todos { id = 0, text = "Ship the release", done = False })
|> Task.andThen (\_ -> Store.insert tx todos { id = 0, text = "Take a break", done = False })
)
)
|> Task.andThen (\_ -> Store.all conn todos)
|> Task.andThen
(\todoList ->
println
("Loaded "
++ String.fromInt (List.length todoList)
++ " todos"
)
)
)
|> Task.run
Partial-column updates without rewriting the whole record: Store.setFields /
Store.updateFields PATCH only the named columns, and Store.adjust runs an
atomic SET col = col + delta (counters, stock levels) — see
the Store writes list above.
Dropping to raw SQL
When you need SQL the Store can't express (a join of three or more tables, a
window function, a CTE, a regular-expression match, a trigger such as a counter
column the database maintains, a hand-tuned query), drop to raw Db.exec / Db.execRaw / Db.queryDecode with a
hand-written row decoder. The row shape from the runtime is Dict String String
— every column lands stringified, and the typed accessors (Db.getInt /
Db.getString / Db.getBool) parse on read with a default-supplied fallback.
Time columns as text. A time column reads as PostgreSQL's own text on a
UTC server, whatever the time zone of the app's host. This holds for a raw
row, a Store or Codec String field, Std.Db.Decode and Std.Db.Table:
| Column | Text |
|---|---|
timestamptz | 2026-11-01 02:13:48.987333+00, always in UTC |
timestamp | 2026-11-01 02:13:48.987333 |
date | 2026-11-01 |
time | 02:13:48.25 |
Trailing zeros of the fraction are dropped, and a BC year ends in BC.
SQLite returns a DATE, DATETIME or TIMESTAMP column in the same shapes,
in the clock the app wrote, followed by +HH:MM when the written text had a
non-zero offset. The text is a valid parameter: written back to the same
column it stores the same value, on both dialects. SELECT t::text gives the
server's own TimeZone, so use the plain column when you want UTC. Before
v0.29.1 these columns read as Go's time text in the host's zone.
import Sky.Core.Error as Error exposing (Error)
-- Decode one row into a Todo (or fail loudly).
decodeTodo : Dict String String -> Result Error Todo
decodeTodo row =
Ok
(Todo
(Db.getInt "id" row)
(Db.getString "text" row)
(Db.getBool "done" row)
)
loadTodos : Db -> Task Error (List Todo)
loadTodos db =
Db.execRaw db
"""CREATE TABLE IF NOT EXISTS todos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
done INTEGER NOT NULL DEFAULT 0
)"""
|> Task.andThen
(\_ ->
Db.withTransaction db
(\tx ->
Db.exec tx "INSERT INTO todos (text) VALUES (?)" [ "Write the doc" ]
|> Task.andThen (\_ -> Db.exec tx "INSERT INTO todos (text) VALUES (?)" [ "Ship the release" ])
)
)
|> Task.andThen
(\_ ->
Db.queryDecode db
"SELECT id, text, done FROM todos ORDER BY id"
[]
decodeTodo
)
Configuration — [database] section
sky.toml:
[database]
path = "./app.db" # SKY_DB_PATH — and it is this that selects the driver
The driver comes from the connection string's shape, not from a config key. A
postgres:// / postgresql:// URL (or a libpq host=… user=… DSN) opens
Postgres; anything else is a SQLite file path.
For Postgres, point path at a postgres://... URL or set DATABASE_URL
(Postgres-conventional fallback):
[database]
# Connection string from DATABASE_URL — never commit a real one to sky.toml.
An optional driver = "sqlite" | "postgres" may be declared as an assertion:
it selects nothing, but the build reports a contradiction between it and
path/url, so driver = "postgres" beside ./app.db no longer opens SQLite
in silence. (Before v0.19.9 it emitted a SKY_DB_DRIVER variable that nothing in
the runtime read.)
.env:
DATABASE_URL=postgres://user:pass@localhost:5432/myapp
Three-layer precedence (highest wins): process env → .env file → sky.toml. See environment-variable precedence.
Patterns
Always parameterise
Db.exec and Db.query take a List any of bind values. Driver-specific placeholders are inserted automatically (? for SQLite, $1, $2, ... for Postgres) — your code stays portable.
-- ✅ Safe
Db.exec db "INSERT INTO users (email) VALUES (?)" [ email ]
-- ❌ SQL injection — never do this
Db.execRaw db ("INSERT INTO users (email) VALUES ('" ++ email ++ "')")
Decode at the boundary
For anything beyond a debug log, decode rows into a typed record at the query site. Db.queryDecode short-circuits on the first Err from your decoder, so a partial / malformed row aborts the whole load instead of silently producing zero values further down:
Db.queryDecode db
"SELECT id, email, role FROM users WHERE active = 1"
[]
decodeUser -- Dict String any -> Result Error User
Group with transactions
Anything that mutates two or more rows together belongs inside Db.withTransaction:
Db.withTransaction db
(\tx ->
Db.exec tx "UPDATE accounts SET balance = balance - ? WHERE id = ?" [ amount, fromId ]
|> Task.andThen (\_ -> Db.exec tx "UPDATE accounts SET balance = balance + ? WHERE id = ?" [ amount, toId ])
)
If either UPDATE returns an error (FK violation, deadlock, anything), the runtime calls ROLLBACK and surfaces the Err to your caller. Both succeed → COMMIT.
What a transaction guarantees, precisely. It is atomic — all of it lands or none of it does. Its isolation is the driver's default, which on PostgreSQL is READ COMMITTED and on SQLite is whatever the single pooled connection serialises into. So the balance-transfer above is atomic, but under READ COMMITTED a concurrent transfer touching the same rows can still interleave in ways a SELECT-then-UPDATE written as two statements will not notice. Where that matters, do the arithmetic in SQL (balance = balance - ?, as above) rather than reading a value into Sky and writing it back, or raise the level with [database] isolation in sky.toml — and read what that key says about retries before enabling one, because a retried transaction body runs twice.
Result/Task bridges
Decoders are Result-shaped, but DB calls are Task. Three helpers compose them without nested case:
| Helper | Type | When |
|---|---|---|
Task.fromResult | Result e a -> Task e a | Lift a Result into a Task pipeline |
Task.andThenResult | (a -> Result e b) -> Task e a -> Task e b | Chain a Result step after a Task |
Result.andThenTask | (a -> Task e b) -> Result e a -> Task e b | Chain a Task step after a Result |
See the effect boundary for the full cheatsheet.
Production checklist
- Connection pooling is on by default, and sized by Sky rather than inherited.
Db.openreturns a*sql.DB, andDb.connectconfigures it: SQLite is pinned to one connection by its global writer lock, and PostgreSQL gets a bounded pool whose size depends on whether the runtime detects a VM or request-billed serverless — because the number that matters is how many instances hold a pool, not how many the app opens. Override per-deployment with[database] maxOpenConns/maxIdleConns/connMaxLifetime/connMaxIdleTime; seesky.toml. Before v0.20.3 the PostgreSQL pool was unconfigured —MaxOpenConns = 0, meaning unlimited backends against a server whose ownmax_connectionsdefault is 100. - Set explicit timeouts for production. The default driver timeouts are generous; tighten via the connection URL (
?statement_timeout=5sfor Postgres). - Never embed secrets in
sky.toml. UseDATABASE_URLfrom the environment in production; keepsky.tomlfor local-dev defaults only. - Index columns you query. The
findOneByField/findManyByField/findByConditionshelpers don't add indexes — that's still a deliberate schema decision. - Use
Db.migratefor schema changes. Versioned, forward-only, checksum-tracked — see the Schema migrations section below. Wiresky db statusinto CI as a drift gate; runsky db migrateahead of cutover so a bad migration blocks a deploy rather than crash-looping the app.
Sky.Live integration
Inside a Sky.Live update, dispatch DB work via Cmd.perform:
type Msg
= LoadTodos
| TodosLoaded (Result Error (List Todo))
update msg model =
case msg of
LoadTodos ->
( { model | loading = True }
, Cmd.perform
(Store.all model.db todos)
TodosLoaded
)
TodosLoaded (Ok todos) ->
( { model | todos = todos, loading = False }, Cmd.none )
TodosLoaded (Err _) ->
( { model | loading = False, error = Just "could not load todos" }
, Cmd.none
)
The DB call runs in a goroutine; the result comes back as a Msg through the same SSE channel as user events.
Schema migrations
Db.migrate applies versioned, forward-only schema migrations. A
migration is a record — a stable name and the sql that applies
it:
import Std.Db as Db
import Std.Db exposing (Migration)
migrations : List Migration
migrations =
[ { name = "0001_users", sql = """
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE
)
""" }
, { name = "0002_posts", sql = """
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL,
title TEXT NOT NULL
)
""" }
]
main =
Db.connect ()
|> Task.andThen (\db -> Db.migrate db migrations)
|> Task.run
How it works:
- A
_sky_migrationstable (created automatically) records thename, achecksumof the SQL,applied_atandkind(sqlhere,opsfor asky db migratefile) of every applied migration. - On each
migratecall, migrations whosenameis not yet recorded run in list order, each in its own transaction, and are recorded. Already-recorded migrations are skipped. migratereturns the names applied this run — empty when the schema was already current, so it is safe to call on every start-up.- Checksum guard. If the SQL of an already-applied migration
changes,
migratefails loudly (checksum mismatch) rather than silently diverging. Treat a shipped migration'snameandsqlas immutable. - Forward-only. There are no down migrations. To undo a change, ship a new compensating migration.
For zero-downtime deploys use the expand/contract pattern — a migration must be safe under both the old and new code, since they overlap briefly during a rollout: add a nullable column, deploy code that writes it, backfill in a later migration, and only drop the old column once nothing reads it.
Inspecting & applying from the CLI
The migration list lives in your app (migrations : List Migration),
so the sky CLI drives it through the built binary:
sky db status # report applied / pending / drifted, then exit
sky db migrate # apply all pending migrations in order, then exit
Both build the project, then run it in DB-ops mode: the app's
Db.migrate call detects the mode, does the work, and exits before
serving. Behind the scenes this is the SKY_DB_OP environment
variable (status / migrate), so a deploy pipeline that can't run
the sky CLI can use it directly:
SKY_DB_OP=migrate ./sky-out/app # apply migrations, exit 0 (1 on failure)
SKY_DB_OP=status ./sky-out/app # print the status report, exit 0
sky db status exits non-zero when it detects drift (an applied
migration whose SQL was edited) — wire it into CI as a schema-drift
gate. sky db migrate exits non-zero if a migration fails, so a
deploy step running it ahead of cutover blocks a bad rollout instead
of crash-looping the app.
There is no sky db migrate <file>: migrations are an ordered,
checksum-tracked set — migrate always means "apply every pending
one, in order."
Checking a live database against the code — sky db check
sky db status compares the migration files with the ledger. sky db check compares the database itself with the code: every table of db : Store.Project (stores, and the Std.Db.Schema tables Schema.toProject
adds) against the live tables, columns, types, NOT NULL, defaults,
UNIQUE (column and multi-column constraints), primary keys, serial keys
and indexes (partial and unique ones included; an index a Store cannot
declare, such as an expression index, is a notice). It
also reports each db/migrations file as applied, pending or edited. It
reads only, on any DSN the app would use, PostgreSQL or SQLite (it opens
the database read-only and writes no telemetry; a missing SQLite file is
refused, not created), and it exits 1 on drift:
sky db check # plain-words report; exit 1 on drift
sky db check --sql # the SQL that makes the database match the code, not run
sky db check --sql --write # save the safe steps as a migration file
sky db check --code # what the code needs to read the database safely
It reads the live schema with Std.Db.Migrate.inspectSchema and compares it
through the same diff as sky db migrate --gen, so the two never
disagree about a missing table, column or index or a type change. In
--sql, a step that cannot lose data is plain SQL; a type change, a
NOT NULL on a column that can hold NULL, and any drop are commented
out with the reason. A PostgreSQL INTEGER column for an Int is
accepted with a notice and a commented ALTER … TYPE BIGINT. --code
prints a record type alias, a Codec and a Store for each table the
code has no Store for. See sky db check.
When adopting an existing table, --write also includes the operations a
fresh database needs. Snapshot-only unconditional unique indexes carry
skipEquivalentConstraint: true: migration application checks the current
transaction and skips an index already enforced by an ordinary equivalent
UNIQUE constraint. Partial indexes, expression indexes and special index
semantics are not equivalent. Explicitly authored named index operations keep
their normal behaviour. This adoption check belongs to migration application;
executing the rendered CREATE INDEX IF NOT EXISTS SQL alone only checks the
index name. The written migration also verifies the resulting uniqueness
inside its transaction. An occupied name on fresh replay returns an error and
rolls back rather than recording a migration that failed to enforce uniqueness.
See also
examples/07-todo-cli— SQLite CLI todo app, showcaseswithTransactionandqueryDecodeexamples/08-notes-app— Full CRUD web app on SQLite, with authexamples/16-skychess— Sky.Live game with persistent move history- Sky.Auth overview — uses
Dbforregister/login/setRole - Standard library reference — full kernel surface