cake/insert

A DSL to build INSERT queries.

Aliases

import cake/insert as i
import cake/where as w
import cake/fragment as f

Query Lifecycle

flowchart LR
    A[i.from_records / i.from_values / i.new] --> B[configure table & columns]
    B --> C[on_conflict strategy]
    C --> D[returning]
    D --> E[i.to_query]

Constructors

There are three ways to start building an INSERT query.

`from_records(table_name, columns, records, encoder) -> Insert(a)`

The idiomatic way. Supply a list of typed Gleam records and an encoder function that maps each record to an InsertRow.

type User {
  User(name: String, age: Int)
}

fn encode_user(u: User) -> i.InsertRow {
  i.row([i.string(u.name), i.int(u.age)])
}

[User("Alice", 30), User("Bob", 25)]
|> i.from_records(
  table_name: "users",
  columns: ["name", "age"],
  encoder: encode_user,
)
|> i.to_query
// INSERT INTO users (name, age) VALUES ($1, $2), ($3, $4)

`from_values(table_name, columns, values) -> Insert(a)`

Supply pre-built InsertRow values directly.

[
  i.row([i.string("Alice"), i.int(30)]),
  i.row([i.string("Bob"), i.int(25)]),
]
|> i.from_values(table_name: "users", columns: ["name", "age"])
|> i.to_query

`new() -> Insert(a)`

Creates a completely empty Insert. Useful when building the query incrementally.

i.new()
|> i.table("users")
|> i.columns(["name", "age"])
|> i.source_values([i.row([i.string("Alice"), i.int(30)])])
|> i.to_query

`to_query(insert: Insert(a)) -> WriteQuery(a)`

Converts an Insert into a WriteQuery for execution.

Row and Value Constructors

`row(values: List(InsertValue)) -> InsertRow`

Wraps a list of InsertValues into a single InsertRow.

i.row([i.string("Alice"), i.int(30), i.bool(True)])

Value constructors

| Function | SQL type |

Function	SQL type
`bool(value)`	Boolean param
`float(value)`	Float param
`int(value)`	Integer param
`string(value)`	String param
`null()`	NULL
`date(value)`	Date param
`fragment(value)`	Raw SQL fragment

// Inserting a UUID via a database cast
import cake/fragment as f

i.fragment(f.prepared("?::uuid", [f.string("0000-0000-4000-a000-a00000000000")]))

Setting the Table

When using new() you can configure (or override) the target table:

i.new() |> i.table("users")

Configuring the Source

`source_records(insert, records, encoder) -> Insert(a)`

Attach a list of records and an encoder to an existing Insert.

`source_values(insert, rows) -> Insert(a)`

Attach raw InsertRow values to an existing Insert.

`columns(insert, columns) -> Insert(a)`

Set the column list. The number of columns must match the number of values in each InsertRow.

Conflict Strategies

flowchart TD
    A[Insert] --> B{on conflict}
    B -->|error| C[InsertConflictError\ndefault - raise error]
    B -->|ignore columns| D[on_columns_conflict_ignore\nPG + SQLite]
    B -->|ignore constraint| E[on_constraint_conflict_ignore\nPG + SQLite]
    B -->|upsert columns| F[on_columns_conflict_update\nPG + SQLite]
    B -->|upsert constraint| G[on_constraint_conflict_update\nPG only]
    B -->|duplicate key| H[on_duplicate_key_update\nMySQL + MariaDB]

`on_conflict_error(insert) -> Insert(a)`

The default. Any uniqueness/constraint violation raises an error.

`on_columns_conflict_ignore(insert, columns, where) -> Insert(a)`

Silently skip conflicting rows when the conflict is on the given columns.

Supported by 🐘 PostgreSQL and 🪶 SQLite.

i.from_values("users", ["email"], [i.row([i.string("a@b.com")])])
|> i.on_columns_conflict_ignore(
  columns: ["email"],
  where: w.none(),
)
// INSERT INTO users (email) VALUES ($1) ON CONFLICT (email) DO NOTHING

`on_constraint_conflict_ignore(insert, constraint, where) -> Insert(a)`

Same as above but identifies the conflict target by constraint name.

Supported by 🐘 PostgreSQL and 🪶 SQLite.

`on_columns_conflict_update(insert, columns, where, update) -> Insert(a)`

Upsert — insert or update on conflict (PostgreSQL / SQLite style).

Use excluded.column in your Update expressions to reference the values that were being inserted.

Supported by 🐘 PostgreSQL ✅ and 🪶 SQLite ✅. Not supported by 🦭 MariaDB or 🐬 MySQL — use on_duplicate_key_update instead.

import cake/update as u

i.from_values("scores", ["username", "score"], [
  i.row([i.string("alice"), i.int(100)]),
])
|> i.on_columns_conflict_update(
  columns: ["username"],
  where: w.none(),
  update: u.new()
    |> u.set(u.set_expression("score", "excluded.score")),
)
// INSERT INTO scores (username, score) VALUES ($1, $2)
// ON CONFLICT (username) DO UPDATE SET score = excluded.score

`on_constraint_conflict_update(insert, constraint, where, update) -> Insert(a)`

Same as above but targets a named constraint.

Supported by 🐘 PostgreSQL only.

`on_duplicate_key_update(insert, update) -> Insert(a)`

MySQL / MariaDB upsert using ON DUPLICATE KEY UPDATE syntax.

Use VALUES(column) (not excluded.column) in your Update expressions.

Supported by 🦭 MariaDB ✅ and 🐬 MySQL ✅ only.

i.from_values("scores", ["username", "score"], [
  i.row([i.string("alice"), i.int(100)]),
])
|> i.on_duplicate_key_update(
  update: u.new()
    |> u.set(u.set_expression("score", "VALUES(score) + scores.score")),
)
// INSERT INTO scores (username, score) VALUES (?, ?)
// ON DUPLICATE KEY UPDATE score = VALUES(score) + scores.score

RETURNING

Fetch column values from the inserted rows (🐘 PostgreSQL and 🪶 SQLite).

🦭 MariaDB and 🐬 MySQL do not support RETURNING in INSERT queries.

i.from_values("users", ["name"], [i.row([i.string("Alice")])])
|> i.returning(["id", "name"])
// INSERT INTO users (name) VALUES ($1) RETURNING id, name

| Function | Effect |

Function	Effect
`returning(insert, cols)`	Return the listed columns
`no_returning(insert)`	Remove RETURNING clause

Modifier

A raw string modifier inserted after INSERT (e.g. OR IGNORE for SQLite).

i.new() |> i.modifier("OR IGNORE")
// INSERT OR IGNORE INTO ...

Epilog and Comment

i.new()
|> i.from_values("logs", ["msg"], [i.row([i.string("hello")])])
|> i.epilog("RETURNING id")
|> i.comment("audit log insert")

Full Example

import cake/insert as i
import cake/update as u
import cake/where as w

type Product { Product(sku: String, price: Float) }

fn encode_product(p: Product) -> i.InsertRow {
  i.row([i.string(p.sku), i.float(p.price)])
}

[Product("ABC-1", 9.99), Product("ABC-2", 14.99)]
|> i.from_records(
  table_name: "products",
  columns: ["sku", "price"],
  encoder: encode_product,
)
|> i.on_columns_conflict_update(
  columns: ["sku"],
  where: w.none(),
  update: u.new() |> u.set(u.set_expression("price", "excluded.price")),
)
|> i.returning(["id", "sku"])
|> i.to_query

Types

Comment

</>

pub type Comment =
  @internal Comment

Epilog

</>

pub type Epilog =
  @internal Epilog

Insert

</>

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

InsertColumns

</>

pub type InsertColumns =
  @internal InsertColumns

InsertConflictStrategy

</>

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

InsertIntoTable

</>

pub type InsertIntoTable =
  @internal InsertIntoTable

InsertRow

</>

pub type InsertRow =
  @internal InsertRow

InsertSource

</>

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

InsertValue

</>

pub type InsertValue =
  @internal InsertValue

Update

</>

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

Where

</>

pub type Where =
  @internal Where

WriteQuery

</>

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

Values

bool

</>

pub fn bool(value value: Bool) -> InsertValue

Create an InsertValue from a column String and a Bool value.

NOTICE: You have to specify the columns and ensure their names are correct, as well as their count which must be equal to the count of InsertRows the encoder function returns or is given as source values.

comment

</>

pub fn comment(
  insert insert: Insert(a),
  comment comment: String,
) -> Insert(a)

Specify a comment for the INSERT query.

date

</>

pub fn date(date value: calendar.Date) -> InsertValue

Create an InsertValue from a calendar.Date.

epilog

</>

pub fn epilog(
  insert insert: Insert(a),
  epilog epilog: String,
) -> Insert(a)

Specify an epilog for the INSERT query.

float

</>

pub fn float(value value: Float) -> InsertValue

Create an InsertValue from a column String and a Float value.

fragment

</>

pub fn fragment(value value: fragment.Fragment) -> InsertValue

Create an InsertValue from a Fragment.

Example

import cake/fragment as f
import cake/insert as i

i.fragment(f.prepared("$::uuid", [f.string("0000000000-0000-4000-a000-a00000000000")]))

from_records

</>

pub fn from_records(
  table_name table_name: String,
  columns columns: List(String),
  records records: List(a),
  encoder encoder: fn(a) -> InsertRow,
) -> Insert(a)

Create an INSERT query from a list of gleam records.

The encoder function is used to convert each record into an InsertRow.

from_values

</>

pub fn from_values(
  table_name table_name: String,
  columns columns: List(String),
  values values: List(InsertRow),
) -> Insert(a)

Create an INSERT query from a list of InsertRows.

get_columns

</>

pub fn get_columns(insert insert: Insert(a)) -> InsertColumns

Get the columns to insert into from an Insert query.

get_comment

</>

pub fn get_comment(insert insert: Insert(a)) -> Comment

Get the comment from an INSERT query.

get_epilog

</>

pub fn get_epilog(insert insert: Insert(a)) -> Epilog

Get the epilog from an INSERT query.

get_modifier

</>

pub fn get_modifier(insert insert: Insert(a)) -> String

Get the modifier from an Insert query.

get_on_conflict

</>

pub fn get_on_conflict(
  insert insert: Insert(a),
) -> InsertConflictStrategy(a)

Get the conflict strategy from an Insert query.

get_source

</>

pub fn get_source(insert insert: Insert(a)) -> InsertSource(a)

Get the source from an Insert query which is either a list of records, accompanied by an encoder function or a list of InsertRows.

get_table

</>

pub fn get_table(insert insert: Insert(a)) -> InsertIntoTable

Get the table name to insert into from an Insert query.

int

</>

pub fn int(value value: Int) -> InsertValue

Create an InsertValue from a column String and an Int value.

modifier

</>

pub fn modifier(
  insert insert: Insert(a),
  modifier modifier: String,
) -> Insert(a)

Specify a modifier for the INSERT query.

new

</>

pub fn new() -> Insert(a)

Create an empty INSERT query.

no_comment

</>

pub fn no_comment(insert insert: Insert(a)) -> Insert(a)

Specify that no comment should be added to the INSERT query.

no_epilog

</>

pub fn no_epilog(insert insert: Insert(a)) -> Insert(a)

Specify that no epilog should be added to the INSERT query.

no_modifier

</>

pub fn no_modifier(insert insert: Insert(a)) -> Insert(a)

Specify that no modifier should be used for the given INSERT query.

no_returning

</>

pub fn no_returning(insert insert: Insert(a)) -> Insert(a)

Specify that no columns should be returned after the INSERT query.

null

</>

pub fn null() -> InsertValue

Create a NULL InsertValue.

on_columns_conflict_ignore

</>

pub fn on_columns_conflict_ignore(
  insert insert: Insert(a),
  columns columns: List(String),
  where where: Where,
) -> Insert(a)

This specifies that specific conflicts do not result in an error but instead are just ignored and not inserted.

Conflict Target: Columns

on_columns_conflict_update

</>

pub fn on_columns_conflict_update(
  insert insert: Insert(a),
  columns columns: List(String),
  where where: Where,
  update update: Update(a),
) -> Insert(a)

Inserts or updates on conflict, also called ´UPSERT´.

This function generates PostgreSQL/SQLite-specific upsert queries using ON CONFLICT ... DO UPDATE syntax.

For MySQL/MariaDB, use on_duplicate_key_update() instead.

Conflict Target: Columns

Important

Use excluded.column in UPDATE expressions to reference insert values
Explicitly specify which columns to check for conflicts
Supports optional WHERE clause for conditional updates

Database Support

🐘PostgreSQL: ✅ Fully supported
🪶SQLite: ✅ Fully supported
🦭MariaDB: ❌ Not supported - use on_duplicate_key_update()
🐬MySQL: ❌ Not supported - use on_duplicate_key_update()

on_conflict_error

</>

pub fn on_conflict_error(insert insert: Insert(a)) -> Insert(a)

This specifies that any conflicts result in the query to fail

This is the default behaviour.

on_constraint_conflict_ignore

</>

pub fn on_constraint_conflict_ignore(
  insert insert: Insert(a),
  constraint constraint: String,
  where where: Where,
) -> Insert(a)

This specifies that specific conflicts do not result in an error but instead are just ignored and not inserted.

Conflict Target: Constraint

on_constraint_conflict_update

</>

pub fn on_constraint_conflict_update(
  insert insert: Insert(a),
  constraint constraint: String,
  where where: Where,
  update update: Update(a),
) -> Insert(a)

Inserts or updates on conflict, also called ´UPSERT´.

This function generates PostgreSQL-specific upsert queries using ON CONFLICT ON CONSTRAINT ... DO UPDATE syntax.

For MySQL/MariaDB, use on_duplicate_key_update() instead. For SQLite, use on_columns_conflict_update() instead.

Conflict Target: Named Constraint

Database Support

🐘PostgreSQL: ✅ Fully supported
🪶SQLite: ❌ Not supported - use on_columns_conflict_update()
🦭MariaDB: ❌ Not supported - use on_duplicate_key_update()
🐬MySQL: ❌ Not supported - use on_duplicate_key_update()

on_duplicate_key_update

</>

pub fn on_duplicate_key_update(
  insert insert: Insert(a),
  update update: Update(a),
) -> Insert(a)

MySQL/MariaDB upsert using ON DUPLICATE KEY UPDATE syntax.

This function generates MySQL/MariaDB-specific upsert queries. Only use this when targeting MySQL or MariaDB databases.

For PostgreSQL/SQLite, use on_columns_conflict_update() or on_constraint_conflict_update() instead.

Important

Use VALUES(column) (not excluded.column) in your UPDATE expressions
Updates occur on the first matched unique/primary key constraint
No explicit conflict target - relies on existing indexes

Example

import cake/insert as i
import cake/update as u

[[i.string("user1"), i.int(100)] |> i.row]
|> i.from_values(table_name: "scores", columns: ["username", "score"])
|> i.on_duplicate_key_update(
  update: u.new()
    |> u.set("score" |> u.set_expression("VALUES(score) + scores.score")),
)

Generates:

INSERT INTO scores (username, score) VALUES (?, ?)
ON DUPLICATE KEY UPDATE score = VALUES(score) + scores.score

Database Support

🦭MariaDB: ✅ Supported
🐬MySQL: ✅ Supported
🐘PostgreSQL: ❌ Not supported - use on_columns_conflict_update()
🪶SQLite: ❌ Not supported - use on_columns_conflict_update()

returning

</>

pub fn returning(
  insert insert: Insert(a),
  returning returning: List(String),
) -> Insert(a)

Specify the columns to return after the INSERT query.

row

</>

pub fn row(values values: List(InsertValue)) -> InsertRow

Create an InsertRow from a list of InsertValues.

source_records

</>

pub fn source_records(
  insert insert: Insert(a),
  source records: List(a),
  encoder encoder: fn(a) -> InsertRow,
) -> Insert(a)

Specify the source records to insert.

source_values

</>

pub fn source_values(
  insert insert: Insert(a),
  source rows: List(InsertRow),
) -> Insert(a)

Specify the source values to insert.

string

</>

pub fn string(value value: String) -> InsertValue

Create an InsertValue from a column String and a String value.

table

</>

pub fn table(
  insert insert: Insert(a),
  table_name table_name: String,
) -> Insert(a)

Specify the table to insert into.

to_query

</>

pub fn to_query(insert insert: Insert(a)) -> WriteQuery(a)

Creates a WriteQuery from an Insert query.