← all modules

Std.Db.Store

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_`