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.md for 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):

BuilderEffect
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" vliteral 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 ()))

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 builderAdded column (SQLite / PostgreSQL)
Maybe, no defaultADD COLUMN c <type> (nullable)
Maybe + a declared defaultnullable, DEFAULT <d>
required + defaultInt/Text/Bool/FloatNOT NULL DEFAULT <d>
required, no builderNOT 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) fieldNOT 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>'
uniqueadded, 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+.

VerbStatementReturns
insertOrIgnore conn s rowINSERT … ON CONFLICT DO NOTHING (any unique key)1 written, 0 already there
insertOrIgnoreOn [ "dedupe_key" ] conn s row… ON CONFLICT (dedupe_key) DO NOTHING1 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 … ENDrows affected
copyInto / copyIntoOrIgnore conn target query [ ( col, from / const ) ]INSERT … SELECT …rows inserted
insertReturning conn s rowINSERT … RETURNING …the stored row
updateFieldsReturning conn s cond setsUPDATE … RETURNING …the updated rows
deleteWhereReturning conn s condDELETE … 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:

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:

ColumnTakes
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:

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:

ConditionMatches
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)
AggRendersResult 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" condrows 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 emptythe 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.

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:

Use Schema.json for JSON, and Schema.blob only for a table you create with createSchema and read with Db.query.

Naming tip. Schema.text collides with Std.Html/Std.Ui's text if both are exposed unqualified. In a module that already does import Std.Html exposing (..) (or Std.Ui), import the schema module qualified — import Std.Db.Schema as Schema and write Schema.text "col" — rather than exposing (text). int / bigInt / bool don'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

FunctionTypeNotes
Db.connect() -> Task Error DbReads the DSN from sky.toml [database] path/url (or SKY_DB_PATH / DATABASE_URL); the driver follows from its shape. Preferred shape.
Db.openString -> String -> Task Error DbTakes a DSN, with the driver name as documentation — the shape of the DSN still decides, so Db.open "postgres" "./app.db" opens SQLite.
Db.closeDb -> Task Error ()Releases the connection pool

Statements

FunctionTypeNotes
Db.execDb -> String -> List a -> Task Error IntParameterised 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.execRawDb -> String -> Task Error IntDDL or multi-statement script — no parameter binding (vulnerable to injection if sql is built from user input). Use for CREATE TABLE, CREATE INDEX.
Db.queryDb -> 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.queryDecodeDb -> 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.updateFieldsDb -> String -> List (String, SqlValue) -> List (String, SqlField) -> Task Error Intv0.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.insertFieldsDb -> String -> List (String, SqlField) -> Task Error Intv0.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.insertFieldsReturningDb -> 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:

FunctionTypeNotes
Db.insertRowDb -> String -> Dict String String -> Task Error IntReturns new row id
Db.getByIdDb -> 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.updateByIdDb -> String -> String -> Dict String String -> Task Error IntReturns affected rows
Db.deleteByIdDb -> String -> String -> Task Error IntReturns affected rows
Db.findOneByFieldDb -> String -> String -> a -> Task Error (Maybe (Dict String String))Single-row equality lookup
Db.findManyByFieldDb -> String -> String -> a -> Task Error (List (Dict String String))All matches by equality
Db.findByConditionsDb -> String -> Dict String String -> Task Error (List (Dict String String))AND-joined equality across every key/value in the conditions dict
Db.unsafeFindWhereDb -> 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

FunctionTypeNotes
Db.withTransactionDb -> (Db -> Task Error a) -> Task Error aCommits on Ok, rolls back on Err automatically

Row accessors (default-supplied → bare return)

FunctionTypeNotes
Db.getFieldString -> row -> StringReads a field as a String (the canonical row-element shape)
Db.getStringString -> row -> StringSame as getField — kept for symmetry with the typed helpers below
Db.getIntString -> row -> IntParses to Int; 0 when missing or unparseable
Db.getBoolString -> row -> BoolParses 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:

ColumnText
timestamptz2026-11-01 02:13:48.987333+00, always in UTC
timestamp2026-11-01 02:13:48.987333
date2026-11-01
time02: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:

HelperTypeWhen
Task.fromResultResult e a -> Task e aLift a Result into a Task pipeline
Task.andThenResult(a -> Result e b) -> Task e a -> Task e bChain a Result step after a Task
Result.andThenTask(a -> Task e b) -> Result e a -> Task e bChain a Task step after a Result

See the effect boundary for the full cheatsheet.

Production checklist

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:

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