cake/update

A DSL to build UPDATE queries.

Aliases

import cake/update as u
import cake/where as w
import cake/join as j

Query Lifecycle

flowchart LR
    A[u.new] --> B[u.table]
    B --> C[u.set / u.sets]
    C --> D[u.from_table / u.from_sub_query]
    D --> E[u.join]
    E --> F[u.where]
    F --> G[u.returning]
    G --> H[u.to_query]

Constructor

new() -> Update(a)

Creates an empty Update query.

to_query(update: Update(a)) -> WriteQuery(a)

Converts an Update into a WriteQuery for execution.

Table

table(update, name) -> Update(a)

Sets the target table for the UPDATE.

u.new() |> u.table("users")
// UPDATE users SET ...

SET clauses

Each UpdateSet represents one column = value assignment. You accumulate sets with set() / sets(), then Cake renders them as a SET col = val, ... clause.

Value setters

u.new()
|> u.table("users")
|> u.set(u.set_string("name", "Alice"))
|> u.set(u.set_int("age", 31))
|> u.set(u.set_null("deleted_at"))
// UPDATE users SET name = $1, age = $2, deleted_at = NULL

set_expression(col, expr) -> UpdateSet

Injects a raw SQL expression on the right-hand side. Useful for self-referencing updates or database functions.

u.set_expression("score", "score + 10")
// score = score + 10

u.set_expression("updated_at", "NOW()")
// updated_at = NOW()

set_fragment(col, fragment) -> UpdateSet

Binds a prepared fragment as the RHS. Preferred over set_expression when user-controlled values are involved.

import cake/fragment as f

u.set_fragment(
  "org_id",
  f.prepared("?::uuid", [f.string("0000-0000-4000-a000-a00000000000")]),
)

set_sub_query(col, query) -> UpdateSet

Sets a column to the result of a sub-query.

import cake/select as s

let sub =
  s.new()
  |> s.from_table("profiles")
  |> s.col("display_name")
  |> s.where(w.eq(w.col("profiles.user_id"), w.col("users.id")))
  |> s.to_query

u.new()
|> u.table("users")
|> u.set(u.set_sub_query("cached_name", sub))

Multi-column setters

Accumulating vs replacing sets

FROM clause

On 🐘 PostgreSQL and 🪶 SQLite an UPDATE supports a FROM clause to join additional tables for use in SET expressions or WHERE conditions.

🦭 MariaDB and 🐬 MySQL do not use FROM in UPDATE; use JOIN instead.

from_table(update, name) -> Update(a)

u.new()
|> u.table("employees")
|> u.from_table("departments")
|> u.set(u.set_expression("salary", "departments.budget / 10"))
|> u.where(w.eq(w.col("employees.dept_id"), w.col("departments.id")))
// UPDATE employees SET salary = departments.budget / 10
// FROM departments WHERE employees.dept_id = departments.id

from_sub_query(update, query, alias) -> Update(a)

Use an aliased sub-query as the FROM source.

no_from(update) -> Update(a)

Remove the FROM clause.

JOIN

On 🦭 MariaDB and 🐬 MySQL JOIN is the standard way to reference other tables in an UPDATE.

On 🐘 PostgreSQL and 🪶 SQLite, JOIN is only allowed when a FROM clause is also set.

u.new()
|> u.table("orders")
|> u.join(j.inner(
  with: j.table("users"),
  on: w.eq(w.col("orders.user_id"), w.col("users.id")),
  alias: "users",
))
|> u.set(u.set_expression("orders.status", "'shipped'"))
|> u.where(w.eq(w.col("users.tier"), w.string("premium")))

WHERE clause

See cake/where for building Where values.

where(update, where) -> Update(a)

Adds a condition with AND semantics.

u.new()
|> u.table("users")
|> u.set(u.set_bool("active", False))
|> u.where(w.lt(w.col("last_login"), w.date(cutoff_date)))
// UPDATE users SET active = $1 WHERE last_login < $2

or_where(update, where) -> Update(a)

Combines with OR semantics.

xor_where(update, where) -> Update(a)

Combines with exactly-one-true XOR semantics. Implemented via a custom OR / AND / NOT expansion on all adapters — native XOR is not used on any adapter, including 🦭 MariaDB / 🐬 MySQL.

For odd-parity XOR (matching 🦭 MariaDB / 🐬 MySQL native XOR), use w.xor_parity instead.

not_where(update, where) -> Update(a)

Negates the given condition with NOT and combines with AND semantics.

• If there is no current WHERE, the condition is set as a standalone NOT.
• If the outermost WHERE is an AndWhere, the negated condition is appended to it.
• Otherwise, the existing WHERE and the new NOT condition are wrapped in an AndWhere.

RETURNING

Fetch column values from the updated rows.

Only supported by 🐘 PostgreSQL and 🪶 SQLite. 🦭 MariaDB and 🐬 MySQL do not support RETURNING in UPDATE.

u.new()
|> u.table("users")
|> u.set(u.set_int("login_count", 1))
|> u.returning(["id", "login_count"])
// UPDATE users SET login_count = $1 RETURNING id, login_count

Epilog and Comment

u.new()
|> u.table("sessions")
|> u.set(u.set_expression("expires_at", "NOW() + INTERVAL '1 hour'"))
|> u.epilog("RETURNING id")
|> u.comment("extend active sessions")

Full Example

import cake/update as u
import cake/where as w
import cake/fragment as f

u.new()
|> u.table("products")
|> u.sets([
  u.set_expression("price", "price * 0.9"),
  u.set_fragment("updated_at", f.literal("NOW()")),
])
|> u.where(w.and([
  w.eq(w.col("category"), w.string("electronics")),
  w.gt(w.col("stock"), w.int(0)),
]))
|> u.returning(["id", "price"])
|> u.to_query

pub type Comment =
  @internal Comment

pub type Epilog =
  @internal Epilog

pub type From =
  @internal From

pub type Join =
  @internal Join

pub type Joins =
  @internal Joins

pub type ReadQuery =
  @internal ReadQuery

pub type Update(a) =
  @internal Update(a)

pub type UpdateSet =
  @internal UpdateSet

pub type UpdateSets =
  @internal UpdateSets

pub type UpdateTable =
  @internal UpdateTable

pub type Where =
  @internal Where

pub type WriteQuery(a) =
  @internal WriteQuery(a)

pub fn comment(
  update update: Update(a),
  comment comment: String,
) -> Update(a)

pub fn epilog(
  update update: Update(a),
  epilog epilog: String,
) -> Update(a)

pub fn from_sub_query(
  update update: Update(a),
  query query: ReadQuery,
  alias alias: String,
) -> Update(a)

pub fn from_table(
  update update: Update(a),
  name table_name: String,
) -> Update(a)

pub fn get_comment(update update: Update(a)) -> Comment

pub fn get_epilog(update update: Update(a)) -> Epilog

pub fn get_from(update update: Update(a)) -> From

pub fn get_joins(update update: Update(a)) -> Joins

pub fn get_set(update update: Update(a)) -> List(UpdateSet)

pub fn get_table(update update: Update(a)) -> UpdateTable

pub fn get_where(update update: Update(a)) -> Where

pub fn join(
  update update: Update(a),
  join join: Join,
) -> Update(a)

pub fn joins(
  update update: Update(a),
  joins joins: List(Join),
) -> Update(a)

pub fn new() -> Update(a)

pub fn no_comment(update update: Update(a)) -> Update(a)

pub fn no_epilog(update update: Update(a)) -> Update(a)

pub fn no_from(update update: Update(a)) -> Update(a)

pub fn no_join(update update: Update(a)) -> Update(a)

pub fn no_returning(update update: Update(a)) -> Update(a)

pub fn no_where(update update: Update(a)) -> Update(a)

pub fn not_where(
  update update: Update(a),
  where where: Where,
) -> Update(a)

pub fn or_where(
  update update: Update(a),
  where where: Where,
) -> Update(a)

pub fn replace_join(
  update update: Update(a),
  join join: Join,
) -> Update(a)

pub fn replace_joins(
  update update: Update(a),
  joins joins: List(Join),
) -> Update(a)

pub fn replace_where(
  update update: Update(a),
  where where: Where,
) -> Update(a)

pub fn returning(
  update update: Update(a),
  returning returning: List(String),
) -> Update(a)

pub fn set(
  update update: Update(a),
  set set: UpdateSet,
) -> Update(a)

pub fn set_bool(
  column column: String,
  value value: Bool,
) -> UpdateSet

pub fn set_date(
  column column: String,
  date date: calendar.Date,
) -> UpdateSet

pub fn set_expression(
  column column: String,
  expression expression: String,
) -> UpdateSet

pub fn set_false(column column: String) -> UpdateSet

pub fn set_float(
  column column: String,
  value value: Float,
) -> UpdateSet

pub fn set_fragment(
  column column: String,
  value value: fragment.Fragment,
) -> UpdateSet

import cake/fragment as f
import cake/update as u

"org_id" |> u.set_fragment(f.prepared("$::uuid", [f.string("0000000000-0000-4000-a000-a00000000000")]))

pub fn set_int(
  column column: String,
  value value: Int,
) -> UpdateSet

pub fn set_null(column column: String) -> UpdateSet

pub fn set_replace(
  update update: Update(a),
  set set: UpdateSet,
) -> Update(a)

pub fn set_string(
  column column: String,
  value value: String,
) -> UpdateSet

pub fn set_sub_query(
  column column: String,
  query query: ReadQuery,
) -> UpdateSet

pub fn set_true(column column: String) -> UpdateSet

pub fn sets(
  update update: Update(a),
  set sets: List(UpdateSet),
) -> Update(a)

pub fn sets_expression(
  columns columns: List(String),
  expression expression: String,
) -> UpdateSet

pub fn sets_replace(
  update update: Update(a),
  sets sets: List(UpdateSet),
) -> Update(a)

pub fn sets_sub_query(
  columns columns: List(String),
  query query: ReadQuery,
) -> UpdateSet

pub fn table(
  update update: Update(a),
  name name: String,
) -> Update(a)

pub fn to_query(update update: Update(a)) -> WriteQuery(a)

pub fn where(
  update update: Update(a),
  where where: Where,
) -> Update(a)

pub fn xor_where(
  update update: Update(a),
  where where: Where,
) -> Update(a)