Std.Db.Store
Std.Db.Store — codec-driven persistence (Layer 3 Sky source).
Types
type Store a
type alias StoreRec a = { name : String , codec : Codec a , pk : String , cols : List ( String, String ) , generated : List String , computed : List ( String, () -> SqlValue ) , extras : List ( String, String ) , problems : List String , indexes : List IndexSpec , nodes : List ( String, Value ) }
type alias IndexSpec = { name : String , cols : List String , unique : Bool , predicate : String }
type OnConflict
type SetExpr
type alias SetSpec = { kind : String , target : String , source : String , cond : Cond , value : SqlValue , keepOld : Bool , orElse : SqlValue }
type Otherwise
type CopyFrom
type alias Table = { name : String , pk : String , cols : List ( String, String ) , indexes : List IndexSpec , problems : List String }
type Project
type Cond
type alias Leaf = { col : String , op : String , value : SqlValue , values : List SqlValue , text : String , flag : Bool }
type alias Nest = { kind : String , side : String , col : String , subCol : String , negated : Bool , source : Source , pairs : List ( String, String ) , inner : Cond , sub : QuerySpec }
type alias OrderTerm = { side : String , col : String , dir : String }
type Subquery
type JoinOn
type Correlate
type Agg
type alias AggSpec = { kind : String , field : String , col : String , cond : Cond }
type Output
type Dialect
type alias Source = { table : String , pk : String , cols : List ( String, String ) , problems : List String , nodes : List ( String, Value ) }
type alias QuerySpec = { source : Source , joined : Bool , leftOuter : Bool , right : Source , onPairs : List ( String, String ) , onConds : List Cond , conds : List Cond , orders : List OrderTerm , limitN : Maybe Int , offsetN : Maybe Int , distinct : Bool , groups : List String , out : Output , outCols : List ( String, String ) , outNodes : List ( String, Value ) , having : List Cond , latest : Maybe ( List String, String ) }
type Query r
type alias Ref = ( String, List SqlValue, String )
type alias Ctx = { dialect : Dialect , cols : List ( String, String ) , nodes : List ( String, Value ) , rightNodes : List ( String, Value ) , aliases : List ( String, Ref ) , having : Bool , table : String , qual : String , right : List ( String, String ) , rightTable : String , joined : Bool , rightNull : Bool , side : String , depth : Int }
Values
addExcluded : String -> OnConflict
Add the new row's value to the stored one: `count = t.count + excluded.count`.
adjust : Db -> Store a -> Cond -> List ( String, Int ) -> Task Error Int
Atomic relative adjustment of a required `Int` or `Float` column (a NULL
aggregate : Codec r -> List Agg -> Query a -> Query r
Turn a query into one row per group, decoded by `codec`. Each field of the
all : Db -> Store a -> Task Error (List a)
Every row, decoded via the codec.
and_ : List Cond -> Cond
All of the conditions (AND). An empty list is always true.
checkProjectData : Db -> Project -> Task Error String
For each table of the project that the database has, the refusal
column : String -> Query a -> Subquery
One column of a query, for `inQuery` / `notInQuery`:
const : SqlValue -> CopyFrom
A `copyInto` column set to one value for every copied row. The value
contains : String -> String -> Cond
`col` contains `needle`, case-sensitive on both dialects. `%`, `_` and `!`
containsCi : String -> String -> Cond
`col` contains `needle`, ignoring case. `%`, `_` and `!` in the needle are
copyInto : Db -> Store t -> Query s -> List ( String, CopyFrom ) -> Task Error Int
Insert the rows of a query in one `INSERT … SELECT`: a copy between
copyIntoOrIgnore : Db -> Store t -> Query s -> List ( String, CopyFrom ) -> Task Error Int
`copyInto` that skips a row that would break the primary key or a unique
correlate : String -> String -> Correlate
Tie an inner column (of the `exists` store) to an outer column (of the
correlateAll : List ( String, String ) -> Correlate
Several `correlate` pairs, all of which must be equal (a tenant column and
count : Db -> Query r -> Task Error Int
Count result rows (filters apply; order and paging are ignored). After
countAs : String -> Agg
`COUNT(*)` into an `Int` field.
countDistinctAs : String -> String -> Agg
`COUNT(DISTINCT col)` into an `Int` field (NULL is not counted).
countIfAs : String -> Cond -> Agg
The number of rows in the group that match `cond`, into an `Int` field (0
create : Db -> Store a -> Task Error ()
Create the table (dialect-correct DDL derived from the codec). A store
defaultBool : String -> Bool -> Store a -> Store a
Give a column a `DEFAULT` boolean value in the generated DDL (`TRUE`/`FALSE`
defaultDecimal : String -> Decimal -> Store a -> Store a
Give a decimal column (`Codec.decimal`, `Codec.moneyIn`) a `DEFAULT`
defaultFloat : String -> Float -> Store a -> Store a
Give a column a `DEFAULT` float value in the generated DDL:
defaultInt : String -> Int -> Store a -> Store a
Give a column a `DEFAULT` integer value in the generated DDL:
defaultNow : String -> Store a -> Store a
Give a text column a `DEFAULT` of the current timestamp (`now()` on
defaultText : String -> String -> Store a -> Store a
Give a column a `DEFAULT` text value in the generated DDL:
defaultWith : String -> (() -> SqlValue) -> Store a -> Store a
Give a column an APP-SIDE computed default: a `() -> SqlValue` the runtime
delete : Db -> Store a -> String -> String -> Task Error Int
Delete rows where `col = value` (single column). `col` must be a codec
deleteWhere : Db -> Store a -> Cond -> Task Error Int
Delete every row matching a `Cond` — for compound conditions the single-column
deleteWhereReturning : Db -> Store a -> Cond -> Task Error (List a)
`deleteWhere` that returns the deleted rows (`DELETE … RETURNING`).
distinct : Query r -> Query r
`SELECT DISTINCT`: drop duplicate result rows. With `select`, every order
dropProject : Db -> Project -> Task Error (List String)
DROP every table the project declares PLUS `_sky_migrations` — a fresh
dropTable : Db -> String -> Task Error (List String)
DROP a single named table. Does NOT touch `_sky_migrations`.
dumpSchema : Project -> Task Error ()
Print the project's schema as JSON (between SKY_SCHEMA_* markers) and exit —
endsWith : String -> String -> Cond
`col` ends with `suffix`, case-sensitive on both dialects:
eq : String -> SqlValue -> Cond
`col = value`. The value follows the column's kind, checked before the
eqCi : String -> String -> Cond
`col` equals `text`, ignoring case (`LOWER(col) = LOWER(?)`). A text column
eqCol : String -> String -> Cond
`left = right`, two columns of the same kind.
exists : Store b -> Correlate -> Cond -> Cond
`EXISTS (SELECT 1 FROM inner WHERE <correlate pairs> AND (<cond>))`: true
findBy : Db -> Store a -> String -> String -> Task Error (Maybe a)
The single row where `col = value`, or Nothing. `col` must be a codec
from : String -> CopyFrom
A `copyInto` column read from the source query's column (a field or
fromCodec : String -> Codec a -> Store a
Define a store from a table name and its codec. The columns are derived
generated : List String -> Store a -> Store a
Mark columns the DATABASE fills — a `serial` PK, a `DEFAULT`/`defaultNow`
groupBy : List String -> Query r -> Query r
`GROUP BY` these columns (or `jsonText` references). Use with `aggregate`.
gt : String -> SqlValue -> Cond
`col > value`.
gtCol : String -> String -> Cond
`left > right`, two columns of the same kind:
gte : String -> SqlValue -> Cond
`col >= value`.
gteCol : String -> String -> Cond
`left >= right`, two columns of the same kind.
having : Cond -> Query r -> Query r
A condition on the aggregate results (`HAVING`). Name result fields:
inList : String -> List SqlValue -> Cond
`col IN (v1, v2, …)`. An empty list matches nothing. A list of any
inQuery : String -> Subquery -> Cond
`col IN (SELECT …)`: the outer column equals a value of the subquery
index : List String -> Store a -> Store a
Index the columns, in order: `Store.index [ "customer_id", "created_at" ]`.
indexNamed : String -> List String -> Store a -> Store a
`Store.index` with the index NAME given, for a table whose index already
insert : Db -> Store a -> a -> Task Error Int
Insert a record. Columns marked `generated` (a `serial` PK, `defaultNow`) are
insertMany : Db -> Store a -> List a -> Task Error Int
Bulk-insert many records in ONE multi-row INSERT — for time-series / batch
insertOrIgnore : Db -> Store a -> a -> Task Error Int
Insert the record, or do nothing when it would break the primary key or
insertOrIgnoreOn : List String -> Db -> Store a -> a -> Task Error Int
`insertOrIgnore` that ignores only a conflict on these columns:
insertReturning : Db -> Store a -> a -> Task Error a
Insert the record and return the row as the database stored it — with a
isNull : String -> Cond
`col IS NULL`.
join : Store a -> Store b -> JoinOn -> Query ( a, b )
An inner join: every pair of a left row and a right row whose ON columns
jsonText : String -> String -> String
A reference to one key of a JSON text column, usable wherever a column name
keep : Otherwise
`setWhen`'s ELSE: the row keeps the value it holds.
keepMax : String -> OnConflict
Keep the larger of the stored and the new value (NULL rules as `keepMin`).
keepMin : String -> OnConflict
Keep the smaller of the stored and the new value. A NULL on either side
key : String -> Agg
The group column itself, into the result field of the same name. The
keyAs : String -> String -> Agg
A group column (or `jsonText` reference) into the named result field.
latestPer : List String -> String -> Query r -> Query r
Keep the newest row per key: for each distinct value of the `keys`
leftJoin : Store a -> Store b -> JoinOn -> Query ( a, Maybe b )
A left join: every left row, paired with `Just` its matching right row, or
like : String -> String -> Cond
`col LIKE pattern`. The pattern is used as given: `%` and `_` are
limit : Int -> Query r -> Query r
`LIMIT n`. A negative `n` makes the query fail before it runs.
lt : String -> SqlValue -> Cond
`col < value`.
ltCol : String -> String -> Cond
`left < right`, two columns of the same kind.
lte : String -> SqlValue -> Cond
`col <= value`.
lteCol : String -> String -> Cond
`left <= right`, two columns of the same kind.
maxAs : String -> String -> Agg
The largest value of a column (not a `Bool` column). The field needs
migrate : Db -> Store a -> Task Error (List String)
Safe additive migration: create the table (with its indexes) if absent,
minAs : String -> String -> Agg
The smallest value of a column (not a `Bool` column). The field needs
neq : String -> SqlValue -> Cond
`col <> value`.
neqCol : String -> String -> Cond
`left <> right`, two columns of the same kind.
noCond : Cond
The condition every row meets (`1 = 1`), the same as `and_ []`. Use it
notExists : Store b -> Correlate -> Cond -> Cond
`NOT EXISTS (…)`: true when the other store has no such row.
notInQuery : String -> Subquery -> Cond
`col NOT IN (SELECT …)`, the same as `not_ (inQuery col sub)`.
notNull : String -> Cond
`col IS NOT NULL`.
not_ : Cond -> Cond
Negate a condition (NOT).
offset : Int -> Query r -> Query r
`OFFSET n`, with or without `limit`. A negative `n` makes the query fail
on : String -> String -> JoinOn
One ON pair: a left column and a right column that must be equal.
onAll : List ( String, String ) -> JoinOn
Several ON pairs, all of which must be equal. An empty list is an `Err`.
or_ : List Cond -> Cond
Any of the conditions (OR). An empty list is always false.
orderAsc : String -> Query r -> Query r
Append `ORDER BY col ASC` (chain for multi-column ordering). On a nullable
orderDesc : String -> Query r -> Query r
Append `ORDER BY col DESC`. On a nullable column NULLs come last, on both
orderLeftAsc : String -> Query r -> Query r
`ORDER BY` a left column, ascending. Only on a join.
orderLeftDesc : String -> Query r -> Query r
`ORDER BY` a left column, descending. Only on a join.
orderRightAsc : String -> Query r -> Query r
`ORDER BY` a right column, ascending. Only on a join.
orderRightDesc : String -> Query r -> Query r
`ORDER BY` a right column, descending. Only on a join.
otherwise : SqlValue -> Otherwise
`setWhen`'s ELSE: set this value.
primaryKey : String -> Store a -> Store a
Mark the primary-key column (NOT NULL; used by `findBy`/`delete`/`update`).
project : List Table -> Project
Build a `Project` from a list of erased tables (via `toTable`). Expose the
projectTableCount : Project -> Int
How many tables the project declares — the count `sky db reset` / `sky db
pushProject : Db -> Project -> Task Error (List String)
Push the whole project's schema to the live DB — create each missing table,
query : Store a -> Query a
Start a query over a store:
resetProject : Db -> Project -> Task Error (List String)
EMPTY the data from every table the project declares — keeps the schema and
resetTable : Db -> String -> Task Error (List String)
EMPTY the data from a single named table (schema + ledger untouched).
select : Codec r -> Query a -> Query r
Read only the columns of a smaller record: each field of `codec` takes the
selectAs : Codec r -> List ( String, String ) -> Query a -> Query r
`select` with explicit sources: each `( field, source )` pair reads `source`
selectColumn : String -> Codec c -> Query a -> Query c
Read one column into a list of plain values:
selectRaw : Db -> Codec row -> String -> List SqlValue -> Task Error (List row)
Run ANY SQL and decode each row into a typed projection record via a codec.
serial : String -> Store a -> Store a
Mark an INTEGER primary key as auto-increment (SQLite `INTEGER PRIMARY KEY
setCol : String -> String -> SetExpr
Copy another column of the same row: `Store.setCol "wanted_at" "status_at"`
setExcluded : String -> OnConflict
Set the column to the new row's value: `c = excluded.c`.
setFields : Db -> Store a -> SqlValue -> List ( String, SqlValue ) -> Task Error Int
PARTIAL-column update by primary key — the everyday PATCH-by-id:
setValue : String -> SqlValue -> SetExpr
Set a value: `Store.setValue "status" (SqlString "open")` renders `status = ?`.
setWhen : String -> Cond -> SqlValue -> Otherwise -> SetExpr
Set a value on the rows where a condition holds:
sqlOf : Codec a -> a -> SqlValue
Encode a TYPED value to a `SqlValue` via its codec, so a filter can compare
startsWith : String -> String -> Cond
`col` starts with `prefix`, case-sensitive on both dialects. An empty
sumAs : String -> String -> Agg
The sum of an `Int`, `Float` or decimal column (`Codec.decimal`,
toList : Db -> Query r -> Task Error (List r)
Run the query, decoding every matching row.
toMaybe : Db -> Query r -> Task Error (Maybe r)
Run the query with `LIMIT 1`, returning the first matching row or `Nothing`.
toPairs : Db -> Query ( a, b ) -> Task Error (List ( a, b ))
Run a `join`: each row is a ( left, right ) pair. The same as `toList`,
toPairsMaybe : Db -> Query ( a, Maybe b ) -> Task Error (List ( a, Maybe b ))
Run a `leftJoin`: each row is a left row and `Just` its right row, or
toRows : Db -> Query r -> Task Error (List r)
The same as `toList`. Reads well after `aggregate`.
toSql : Db -> Query r -> Result Error ( String, List SqlValue )
The SQL text and parameters a query renders on this connection's dialect,
toTable : Store a -> Table
Erase a store to its `Table` schema. The store's builder problems (an
touchOnUpdate : String -> Store a -> Store a
Mark a timestamp column that the DB stamps on INSERT (`defaultNow`) AND that
transaction : Db -> (Db -> Task Error a) -> Task Error a
Run a callback inside a DB transaction — Store operations compose by taking
unique : String -> Store a -> Store a
Mark a column `UNIQUE` in the generated DDL (e.g. a login email):
uniqueOn : List String -> Store a -> Store a
A UNIQUE index over the columns, in order:
uniqueOnNamed : String -> List String -> Store a -> Store a
`Store.uniqueOn` with the index NAME given (see `indexNamed`):
uniqueWhere : List String -> Cond -> Store a -> Store a
A partial UNIQUE index: the columns are unique among the rows where the
update : Db -> Store a -> a -> Task Error Int
Update the row identified by the record's PRIMARY KEY, setting every column
updateFields : Db -> Store a -> Cond -> List ( String, SqlValue ) -> Task Error Int
PARTIAL-column update by `Cond`: `SET` only the named columns and leave the
updateFieldsReturning : Db -> Store a -> Cond -> List ( String, SqlValue ) -> Task Error (List a)
`updateFields` that returns the updated rows (`UPDATE … RETURNING`),
updateSet : Db -> Store a -> Cond -> List SetExpr -> Task Error Int
Set columns from expressions in one `UPDATE` over the rows a condition
updateWhere : Db -> Store a -> Cond -> a -> Task Error Int
Update every row matching a `Cond` to the record's column values (PK +
upsert : Db -> Store a -> a -> Task Error Int
Insert the record, or UPDATE it in place if its primary key already exists
upsertWith : Db -> Store a -> a -> { conflictOn : List String, onConflict : List OnConflict } -> Task Error Int
Insert the record, or apply the rules to the row that exists — a partial
whereLeft : Cond -> Query r -> Query r
A condition on the left store of a join. On a query that is not a join it
whereRight : Cond -> Query r -> Query r
A condition on the right store of a join. On a `leftJoin` it goes into the
where_ : Cond -> Query r -> Query r
Add a condition. Multiple `where_` calls are AND-combined; use `and_` / `or_`