Data Manipulation Language (DML)¶

Delightql supports SQL’s tree mutation operations through three destructively sigilized pipe targets:

• update!(T(*))(*) – modify existing rows
• insert!(T(*))(*) – add new rows
• delete!(T(*))(*) – remove rows

The T(*) is the mutation target – a functor expression identifying which relation to mutate.

The !! Marker¶

For update! and delete!, the source relation is also the mutation target – the rows being sourced are the rows being mutated. Mark the source with !! to make this explicit:

employee!!(*)                      // !! = "these rows will be mutated"
  , title = "IT Staff"
  |> delete!(employee(*))(*)

The !! marker is required when the source is the mutation target. The compiler verifies that the !!-marked relation matches the terminal target.

For insert!, the source rows are read-only input – even when the source table happens to be the same as the target. Do not use !! on insert sources:

employee(*)  // no !! -- these rows are read-only
  , title = "IT Staff" |> (employee_id + 100 as employee_id,
                           last_name,
                           first_name,
                           title,
                           reports_to) |> insert!(employee(*))(*)

Insert¶

Use the insert! pseudo-predicate to insert rows. The relation entering the insert! pipe must contain a subset of the schema of the mutation target. Any extra or erroneously named columns are an error.

_(last_name, first_name, title @ "Eklund", "Daniel", "IT Staff")
  |> insert!(employee(*))(*)

INSERT INTO employee (last_name, first_name, title)
VALUES ('Eklund', 'Daniel', 'IT Staff');

You may union tables and predicate their tuples to provide input tuples:

mount!("etl.sqlite", "etl")(*)
// a new target: an empty table shaped like the loads
etl.partner_sale_2025_06_30(*), #<0 |> table!(etl.partner_sale_all(*))(*)

etl.partner_sale_2025_06_30(*)
  |;| etl.partner_sale_2025_07_31(, received_at >= "2025-07-01")
  |> insert!(etl.partner_sale_all(*))(*)

The target may already exist; the input names a subset of its columns:

mount!("etl.sqlite", "etl")(*)
etl.partner_sale_2025_07_31(*),
  quantity > 0 |> (sale_id, line_no, track_id, unit_price, quantity)
  |> insert!(etl.partner_sale_line(*))(*)

INSERT INTO etl.partner_sale_line (sale_id, line_no, track_id, unit_price, quantity)
SELECT sale_id, line_no, track_id, unit_price, quantity
FROM etl.partner_sale_2025_07_31
WHERE quantity > 0;

Update¶

To update a table, pipe a matching schema into update! pseudo-predicate. The name of the table to be updated must be the higher-order parameter – this is the mutation target. Note that this implies that the DML pseudo-predicate accepts two higher-order parameters: the contents of the query before the pipe and the table targeted. The trailing (*) is RECEIPT ACCESS – ordinary relation access over the directive’s return table – never an input group. The mutation target must be the source of the data as well.

Every DML directive returns a receipt relation of cardinality zero or one: one row (success first, then operation, then parameter echoes) answers YES – at least one row was affected; the empty relation answers NO. There is no success = false row, and failure aborts rather than encoding itself as data. DML receipts declare no returned payload, so unwrapping one (!> or .returned(*)) is a category error – the receipt continues through |>.

employee!!(*)
  , title = "IT Staff"
  |> $$("-------" as phone)
  |> update!(employee(*))(*)

UPDATE employee
SET phone = '-------'
WHERE title = 'IT Staff';

Delete¶

To delete from a table, use predication to select the rows that should be removed. The mutation target must also be the source table and the schemas must match.

employee!!(*)
  , title = "IT Staff"
  |> delete!(employee(*))(*)

DELETE FROM employee
WHERE title = 'IT Staff';

Without filters, all rows are deleted:

invoice_line!!(*) |> delete!(invoice_line(*))(*)

DELETE FROM invoice_line;

To keep only some rows, invert the predicate and delete the complement:

invoice_line!!(*)
  , unit_price != 0.99
  |> delete!(invoice_line(*))(*)