Data with Std.Db

Most apps need to store data. In Sky the default is a codec-driven store: you write one codec, and it drives the JSON shape and the database — read, write, and schema — with no drift between them.

One codec, everything derived

import Std.Db as Db
import Std.Codec as Codec exposing (Codec)
import Std.Db.Store as Store exposing (Store)


type alias Todo =
    { id : Int
    , title : String
    , done : Int
    }


todos : Store Todo
todos =
    Store.fromCodec "todos" (Codec.auto { id = 0, title = "", done = 0 })
        |> Store.serial "id"

Codec.auto takes a zero-value witness (a blank record) and reflects its fields into snake_case columns — title → title, a nested record or list → a JSON blob column. Store.serial "id" marks the integer primary key as auto-increment, so insert lets the database assign it.

That one definition now gives you the table schema, the insert/read mapping, and Codec.toJson / fromJson — all consistent, because they come from the same place.

Opening, creating, writing, reading

run : Task Error ()
run =
    Db.connect ()
        |> Task.andThen
            (\conn ->
                Store.create conn todos                       -- ensure the table
                    |> Task.andThen (\_ -> Store.insert conn todos { id = 0, title = "Buy milk", done = 0 })
                    |> Task.andThen (\_ -> Store.query todos |> Store.orderAsc "id" |> Store.toList conn)
                    |> Task.map (\rows -> logCount rows)
            )

SQLite now, Postgres later — same code

The database is a tier decision, not a code decision:

The app code above doesn't change between them — only the driver (a connection string / config) differs. The codec emits dialect-correct SQL for whichever backend you connect to.

When you outgrow the store

Grouped counts and sums (groupBy + aggregate), projections (select), the newest row per key (latestPer), text search (contains, eqCi), joins of two stores (join, leftJoin), subquery conditions (inQuery, exists), the "claim once" insert (insertOrIgnore), counter and partial upserts (upsertWith), expression updates (updateSet), insert from a query (copyInto) and RETURNING (insertReturning, updateFieldsReturning) are Store builders. For transactions and other SQL, drop to raw Std.Db (query / exec / withTransaction) — or use Store.selectRaw to run any SQL and decode each row into a typed projection record via a codec. The store is the default, not a cage. Schema changes ship as committed migration files: sky db migrate --gen diffs your types and writes one; sky db migrate applies it. Store.migrate and --gen follow one rule: a Maybe field is a nullable column, any other field NOT NULL DEFAULT its declared default (else zero), and an existing column is never altered. Indexes are builders too: Store.index, Store.uniqueOn and Store.uniqueWhere.

Full surface: the Std.Db guide, Std.Db.Store, and Std.Codec.

Next → Auth