cake/insert

A DSL to build INSERT queries.

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.