Getting started

Kraken is a typed PostgreSQL query builder for Saga. It gives table and column references real Saga types, so common mistakes are caught before the query reaches Postgres:

selecting u.name decodes as String
comparing u.id to a String does not typecheck
selecting a whole left-joined row decodes as Maybe Row
generated columns can be read, filtered, and ordered, but inserts omit them unless you explicitly provide a value

The public module is Kraken.Db. Qualified calls use the module's final segment, Db.

Imports used in this guide

Most examples assume this import set:

import Kraken.Db (QueryBuild, Repo, select)
import Kraken.Query

Define a table

Start with the row you want queries to decode:

record User {
  id: Int,
  name: String,
  age: Int,
}

Then define a column record for the table. This is the typed scope that from! returns:

record Users {
  id: Db.Generated Int,
  name: Db.Col String,
  age: Db.Col Int,
}

Db.Generated a is a readable column that the database usually fills in.

Map the column record to the SQL table:

impl ColumnSet for Users {
  columns source = Users {
    id: Db.generated "id" source,
    name: Db.col "name" source,
    age: Db.col "age" source,
  }
}

pub fun users : Db.Table Users
users = Db.table "users"

The first string in Db.col "name" source is the real SQL column name. The record field name is the Saga field (u.name) and the default select alias.

For whole-row reads, write a projection:

pub fun users_row : Users -> Db.Projection User
users_row u =
  build Selection User {
    id: Db.read u.id,
    name: Db.read u.name,
    age: Db.read u.age,
  }

For inserts and save-style updates, add the manual insert shape / writer described in the schema and writes guides.

Query

Define a query with Db.query. The type annotation says what each returned row will decode to:

pub fun users_query : Unit -> Db.Prepared { id: Int, name: String, age: Int }
users_query () = Db.query (fun () -> {
  let u = from! users
  where_! (Db.gt u.age 18)
  order_by! [Db.asc u.id]
  limit! 20
  select build Selection {
    id: Db.read u.id,
    name: Db.read u.name,
    age: Db.read u.age,
  }
})

Inside the query body, from!, where_!, order_by!, and limit! build SQL. The final select names the values to decode from each row.

The selected record determines the decoded row type:

{ id: Int, name: String, age: Int }

Db.query returns a Db.Prepared row: rendered SQL, bound parameters, and a decoder for row. You pass that prepared query to Db.all, Db.one, or Db.exactly_one to run it.

Anonymous record projections

Kraken uses anonymous records as the normal way to shape query results. In a select, the labels you write become fields on the decoded row:

select build Selection {
  id: Db.read u.id,
  display_name: Db.read u.name,
}

That query returns rows shaped like:

{ id: Int, display_name: String }

You can select expressions, columns, whole rows, and preloaded relations in the same anonymous record:

select build Selection {
  user: users_row u,
  title: Db.read p.title,
  age_text: Db.read (Db.as_text u.age),
}

The decoded row type is:

{ user: User, title: String, age_text: String }

Whole-row selections use a projection such as users_row. Scalar fields use their column or SQL expression type. This gives query-local result shapes without defining a new named record for every query.

Anonymous records are not required. If a result shape is part of your domain or is reused across modules, define a named record and map into it with Db.into:

record UserSummary {
  id: Int,
  name: String,
}

pub fun user_summaries : Unit -> Db.Prepared UserSummary
user_summaries () =
  Db.query (fun () -> {
    let u = from! users
    select build Selection UserSummary {
      id: Db.read u.id,
      name: Db.read u.name,
    }
  })

Use anonymous records for local query shapes; use named records when the shape deserves a name in your application API.

Select a whole table scope to decode a domain record:

pub fun active_users : Unit -> Db.Prepared User
active_users () = Db.query (fun () -> {
  let u = from! users
  where_! (Db.gt u.age 18)
  select (users_row u)
})

Execute

Use Db.all, Db.one, or Db.exactly_one through the ambient repository:

pub fun load_users : Unit
  -> Result (List { id: Int, name: String, age: Int }) Db.DbError
  needs {Repo}
load_users () = Db.all (users_query ())

At the application boundary, create a connection-bound PostgreSQL repository and install it once. It covers reads, writes, relation preloads, and transactions while keeping saga_pgo's lower-level handlers internal:

main () = {
  let connection = SagaPgo.connect config
  let repo = Db.postgres_repo connection
  load_users () with repo
}

The handler owns the connection. Ordinary application functions depend only on {Repo} and do not thread a pool through every call.

Multiple repositories

A single-database application can use Db.Repo directly. When an application needs distinct databases, mint nominal copies of its interface:

pub neweffect AuthRepo = Db.Repo
pub neweffect DataRepo = Db.Repo

These effects are deliberately unrelated: AuthRepo does not satisfy Db.Repo. Define an application-owned adapter for each one, delegating every operation to a concrete Handler Db.Repo. Domain code then calls the copied operations directly:

fun find_user : Int -> Result (Maybe User) Db.DbError needs {AuthRepo}
find_user id = AuthRepo.one! (user_query id)

fun dashboard : Int -> Response needs {AuthRepo, DataRepo}
dashboard id = {
  let user = find_user id
  let activity = DataRepo.all! (activity_query id)
  render_dashboard user activity
}

At the root, adapt independently captured backends and install both handlers:

let auth = auth_repo (Db.postgres_repo auth_connection)
let data = data_repo (Db.postgres_repo data_connection)
dashboard id with {auth, data}

Keep concrete handlers at composition boundaries. Tests can replace either backend with Db.empty_repo, Db.rejecting_repo "unexpected database call", or another Db.postgres_repo test_connection without changing domain functions.

Inspect SQL

A prepared query carries the rendered SQL and parameters:

let prepared = users_query ()
prepared.sql
prepared.params

This is useful for logging and for checking what the builder produced. Kraken numbers parameters at render time, so nested subqueries and CTEs still produce $1, $2, ... in the final SQL order.

Schema explains the table setup and insert shapes.
Repositories covers production wiring, nominal multi-database effects, transactions, and test handlers.
Queries covers joins, grouping, subqueries, CTEs, and execution.
Expressions covers predicates, raw SQL, JSONB, arrays, and window functions.
Writes covers insert, update, delete, upsert, and transactions.
Relations covers preloads and nullable joins.