Writes

Write operations are available through Kraken.Db. Users should not need to import an internal write module.

This page uses the import pattern from Getting started.

Non-returning writes produce Db.Prepared Unit and run with Db.exec. Writes with RETURNING produce Db.Prepared row and run with Db.all, Db.one, or Db.exactly_one.

Insert

Use a record-builder insert shape:

pub fun user_insert : String -> Int -> Db.InsertOf Users
user_insert name age =
  build Db.Insert Users {
    id: Db.insert_auto,
    name: Db.insert_value name,
    age: Db.insert_value age,
  }

pub fun insert_user_query : Unit -> Db.Prepared Unit
insert_user_query () =
  Db.insert users (user_insert "Carol" 31)

Db.insert_auto / Db.insert_default render SQL DEFAULT. Db.insert_value value binds one explicitly. The Db.InsertOf Users annotation is what makes the compiler check against the table's full column record.

Run a non-returning insert with Db.exec:

let repo = Db.postgres_repo connection
Db.exec (insert_user_query ()) with repo

Insert returning

Return the whole inserted row:

pub fun insert_user_returning : Unit -> Db.Prepared User
insert_user_returning () = {
  let row = user_insert "Carol" 31
  Db.insert_returning users row users_row
}

Return selected fields:

pub fun insert_user_returning_id : Unit -> Db.Prepared { id: Int }
insert_user_returning_id () = {
  let row = user_insert "Carol" 31
  (
    Db.insert_returning users row (fun u ->
      build Selection { id: Db.read u.id })
  )
}

Run it with a query executor:

Db.one (insert_user_returning ())

Bulk insert

Db.insert_all users [
  user_insert "Carol" 31,
  user_insert "Dave" 40,
]

Db.insert_all [] is a no-op prepared statement. Db.exec returns Ok 0 and Db.all returns Ok [] without a database round trip.

Bulk inserts need one shared column list. Use Db.insert_default / Db.insert_auto when one row should use a default without changing that shared list.

Bulk returning:

(
  Db.insert_all_returning users rows (fun u ->
    build Selection { id: Db.read u.id })
)

Update

Use the Update effect operations inside the callback:

pub fun bump_age_query : Unit -> Db.Prepared Unit
bump_age_query () = Db.update users (fun u -> {
  set! u.age 43
  where_! (Db.eq u.id 1)
})

Repeated where_! calls are combined with AND.

Returning:

Db.update_returning users (fun u -> {
  set! u.age 43
  where_! (Db.eq u.id 1)
}) (fun u -> u)

Whole-row update

If the table has a primary key and a domain-record writer, use Db.update_all for a save-style update:

pub fun users_save_writer : Db.Writer Users User
users_save_writer = Db.writer (fun u user -> [
  Db.set_generated u.id (Db.provide user.id),
  Db.set u.name user.name,
  Db.set u.age user.age,
])

pub fun save_user_query : User -> Db.Prepared Unit
save_user_query user = Db.update_all users users_save_writer user

update_all updates every non-key field and builds the WHERE from the primary_key declared in ColumnSet. Use Db.update with set! for partial updates.

Delete

pub fun delete_user_query : Unit -> Db.Prepared Unit
delete_user_query () =
  Db.delete users (fun u -> Db.eq u.id 999)

Returning:

(
  Db.delete_returning users
    (fun u -> Db.eq u.id 999)
    (fun u -> u)
)

Insert or ignore

Use Db.insert_on_conflict_do_nothing:

let row = user_insert "Carol" 31
Db.insert_on_conflict_do_nothing users row (fun u -> [Db.ref u.name])

The target callback names conflict columns with Db.ref.

Returning version:

(
  Db.insert_on_conflict_do_nothing_returning users row
    (fun u -> [Db.ref u.name])
    users_row
)

If the row conflicts, Postgres returns no row, so Db.one yields Ok Nothing.

Upsert

Default upsert overwrites every inserted non-target column with EXCLUDED:

let row =
  build Db.Insert Users {
    id: Db.insert_generated (Db.provide 1),
    name: Db.insert_value "Carol",
    age: Db.insert_value 31,
  }

Db.upsert users row (fun u -> [Db.ref u.id])

Returning version:

(
  Db.upsert_returning users row
    (fun u -> [Db.ref u.id])
    users_row
)

Custom upsert

Use Db.upsert_set when you want explicit assignments:

(
  Db.upsert_set users row
    (fun u -> Db.on_columns [Db.ref u.id])
    (fun u -> [
      Db.assign u.age (Db.add u.age 1),
      Db.assign u.name (Db.excluded u.name),
    ])
)

Use a named constraint:

(
  Db.upsert_set users row
    (fun _ -> Db.on_constraint "users_pkey")
    (fun u -> [Db.assign u.age (Db.excluded u.age)])
)

Transactions

Wrap several operations in Db.transaction:

pub fun atomic_writes : Unit -> Result Int (Db.TransactionError Db.DbError) needs {Repo}
atomic_writes () = Db.transaction (fun () -> {
  let new_row = user_insert "Dave" 40
  case Db.exec (Db.insert users new_row) {
    Err err -> Err err
    Ok inserted -> case Db.exec (bump_age_query ()) {
      Err err -> Err err
      Ok bumped -> Ok (inserted + bumped)
    }
  }
})

The transaction commits when the body returns Ok and rolls back when the body returns Err. Body errors are returned as RolledBack err; failures before the body starts, such as failing to begin the transaction, are returned as TransactionFailed query_error.

Db.transaction is generic in the error type. Import Rollback when you want an early non-resuming rollback:

import Kraken.Db (Repo, Rollback)

Db.transaction (fun () -> {
  if invalid then rollback! (Validation "bad data")
  else Ok ()
})

At the application boundary, install the same handler used for ordinary queries:

let repo = Db.postgres_repo connection
atomic_writes () with repo

Db.transaction called inside another Db.transaction fails with TransactionFailed NestedTransaction. Nested savepoints are not currently supported; the outer transaction remains active and usable.