Skip to content

Tree DML

In the chapter Single-table DML each operation worked with one table. But data often comes as a whole tree: a project with its tasks, and tasks with their events. Writing such a tree piece by piece is awkward: you have to insert the project, learn its id, put it into the tasks, insert them, and so on. pg-schema can write the whole tree in one operation. There are three functions for this: insertJSON inserts new data, upsertJSON inserts new data and updates existing data, and updateJSON only updates. Each has a variant with an underscore (insertJSON_ and so on) that returns nothing from the database.

All three functions work the same way, as we already saw in the chapter Quick start. From the types of the input and the result, pg-schema generates a temporary plpgsql function, creates it in the database, calls it, passing the whole tree as one parameter of type jsonb, and drops it. These are three commands, and their number does not change however many rows the tree has. If no transaction is open yet (this is seen from the connection state), all of it runs in a separate transaction, so the tree is written entirely or not at all.

The examples of this chapter are in tutorial/app/app/Tut/TreeDml.hs. Before them the program recreates the schema and adds one user with id = 1, the owner of all projects.

Insert: insertJSON

We describe the input data with types. Child rows are fields with a list of rows, named after the foreign key. As in the chapters Quick start and Select, this is the name of the constraint in the database (tasks_project_id_fkey, task_events_project_id_seq_fkey) passed through the renamer MyRenamer, so we write it in camelCase:

type EventIn = "eventNo" := Int32 :. "kind" := Text

type TaskIn = "seq" := Int32 :. "title" := Text :. "priority" := Int32
  :. "taskEventsProjectIdSeqFkey" := [EventIn]

type ProjectIn = "ownerId" := Int64 :. "title" := Text :. "status" := ProjectStatus
  :. "tags" := PgArr Text :. "tasksProjectIdFkey" := [TaskIn]

The task type has no project_id column, and the event type has no project_id and seq columns, although they are all mandatory. Their values come from the parent row: insertJSON first inserts the project, gets its id and puts it into the tasks, and then does the same with the events. So insertJSON requires all mandatory columns in the input except those that come from the relation with the parent and those that have a default value. If a mandatory column is missing, the program does not compile, just as with insertSch in the chapter Single-table DML.

The trees themselves are convenient to build with the function rowsOf. It is similar to named from the chapter Single-table DML, but named gets the field names as a list of strings, while rowsOf takes both the names and the types of the fields from the row type, which is passed to it as rowsOf @ProjectIn. So values can be written without names and without type annotations, and child rows simply as nested lists.

As with single-table operations, the result type defines what to return after the insert. Let us return the keys of all inserted rows, on all three levels:

type ProjectKeys = "id" := Int64
  :. "tasksProjectIdFkey" := [ "seq" := Int32 :. "taskEventsProjectIdSeqFkey" := ["eventNo" := Int32] ]

(keys, sqlIns) <- insertJSON (MyAnn "projects") conn $ rowsOf @ProjectIn
  [ 1 :. "Website" :. Project_status_active :. pgArr' ["web"] :.
      [ 1 :. "Design" :. 10 :. [ 1 :. "created", 2 :. "assigned" ]
      , 2 :. "Frontend" :. 30 :. [] ]
  , 1 :. "API" :. Project_status_draft :. pgArr' [] :.
      [ 1 :. "Schema" :. 20 :. [ 1 :. "created" ] ] ]
mapM_ print (keys :: [ProjectKeys])

"id" =: 1 :. "tasksProjectIdFkey" =: ["seq" =: 1 :. "taskEventsProjectIdSeqFkey" =: ["eventNo" =: 1,"eventNo" =: 2],"seq" =: 2 :. "taskEventsProjectIdSeqFkey" =: []]
"id" =: 2 :. "tasksProjectIdFkey" =: ["seq" =: 1 :. "taskEventsProjectIdSeqFkey" =: ["eventNo" =: 1]]
Formatted SQL 
create or replace function pg_temp.__ins(data_0 jsonb) returns jsonb as $$
declare
  found_0 boolean;
  row_0 record;
  arr_0 jsonb[];
  id0 int8; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  arr_1 jsonb[];
  project_id1 int8; 
  seq1 int4; 
  data_2 jsonb;
  found_2 boolean;
  row_2 record;
  arr_2 jsonb[];
  event_no2 int4; 
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    insert into tut.projects(owner_id, title, status, tags)
      values ((row_0.value->>'ownerId')::int8, (row_0.value->>'title')::text, (row_0.value->>'status')::tut.project_status, case when jsonb_typeof(row_0.value->'tags') = 'array' then (select coalesce(array_agg(__x)::text[], '{}') from jsonb_array_elements_text(row_0.value->'tags') __x) else null end)
      returning id into id0;
      found_0 := found;
    data_1 := row_0.value->'tasksProjectIdFkey';
    arr_1:= '{}';
    for row_1 in select * from jsonb_array_elements(data_1)
    loop
      insert into tut.tasks(project_id, seq, title, priority)
        values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text, (row_1.value->>'priority')::int4)
        returning project_id, seq into project_id1, seq1;
          found_1 := found;
      data_2 := row_1.value->'taskEventsProjectIdSeqFkey';
      arr_2:= '{}';
      for row_2 in select * from jsonb_array_elements(data_2)
      loop
        insert into tut.task_events(project_id, seq, event_no, kind)
          values (project_id1, seq1, (row_2.value->>'eventNo')::int4, (row_2.value->>'kind')::text)
          returning event_no into event_no2;
              found_2 := found;
        arr_2 := array_append(arr_2, jsonb_build_object('eventNo', event_no2));
      end loop;
      arr_1 := array_append(arr_1, jsonb_build_object('seq', seq1, 'taskEventsProjectIdSeqFkey', to_jsonb(arr_2)));
    end loop;
    arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'tasksProjectIdFkey', to_jsonb(arr_1)));
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

The result type may contain any columns of the tables, including those that were not in the input, like the id of a project. But the branches of the result must be among the branches of the input: you can return tasks only if you inserted them. This, like the other rules for the result type, is checked at compile time.

insertJSON_ does the same but returns nothing from the database. Instead of a function it creates a temporary procedure and returns to the program only the generated SQL. If you do not need the result, this saves a little work: the database does not have to build a jsonb answer, and the program does not have to parse it.

sqlInsNr <- insertJSON_ (MyAnn "projects") conn $ rowsOf @ProjectIn
  [ 1 :. "Docs" :. Project_status_draft :. pgArr' [] :. [] ]
Formatted SQL 
create or replace procedure pg_temp.__ins(data_0 jsonb) as $$
declare
  found_0 boolean;
  row_0 record;
  id0 int8; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  project_id1 int8; 
  seq1 int4; 
  data_2 jsonb;
  found_2 boolean;
  row_2 record;
begin
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    insert into tut.projects(owner_id, title, status, tags)
      values ((row_0.value->>'ownerId')::int8, (row_0.value->>'title')::text, (row_0.value->>'status')::tut.project_status, case when jsonb_typeof(row_0.value->'tags') = 'array' then (select coalesce(array_agg(__x)::text[], '{}') from jsonb_array_elements_text(row_0.value->'tags') __x) else null end)
      returning id into id0;
      found_0 := found;
    data_1 := row_0.value->'tasksProjectIdFkey';
    for row_1 in select * from jsonb_array_elements(data_1)
    loop
      insert into tut.tasks(project_id, seq, title, priority)
        values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text, (row_1.value->>'priority')::int4)
        returning project_id, seq into project_id1, seq1;
          found_1 := found;
      data_2 := row_1.value->'taskEventsProjectIdSeqFkey';
      for row_2 in select * from jsonb_array_elements(data_2)
      loop
        insert into tut.task_events(project_id, seq, event_no, kind)
          values (project_id1, seq1, (row_2.value->>'eventNo')::int4, (row_2.value->>'kind')::text);
              found_2 := found;
      end loop;
    end loop;
  end loop;

end; 
$$ language plpgsql;

Insert or update: upsertJSON

insertJSON only inserts. If a row with the same key already exists, PostgreSQL reports a uniqueness violation, and the whole operation is rolled back. When data may be either new or already existing, use upsertJSON. It decides what to do with the rows for each level of the tree (that is, for each table) from the fields of its type, and it decides this at compile time:

  • if the type has a key but not all mandatory columns, the rows are updated by that key (update);
  • if it has both a key and all mandatory columns, the rows are inserted, and on a key conflict updated (insert … on conflict … do update);
  • if it has all mandatory columns but no key, the rows are simply inserted (insert);
  • if it has neither a key nor all mandatory columns, the program does not compile: there is nothing to do with such a row.

Mandatory columns here are the same as for insertJSON: NOT NULL without a default, except those that come from the parent. The key is the primary key, or, if its columns are not in the type, a unique key all of whose columns are in the type. Unique keys over NOT NULL columns are considered first, and keys with nullable columns only if the table has none of the former. Columns that come from the parent count too. After the parent row, its child rows are processed the same way. Let us change project 1: rename its first task, add an event to it, and add a new task to the project:

type TaskPatch = "seq" := Int32 :. "title" := Text
  :. "taskEventsProjectIdSeqFkey" := [EventIn]

type ProjectPatch = "id" := Int64 :. "tasksProjectIdFkey" := [TaskPatch]

type PatchOut = Maybe ("id" := Int64 :. "tasksProjectIdFkey" := ["seq" := Int32 :. "title" := Text])

(res, sqlUps) <- upsertJSON (MyAnn "projects") conn $ rowsOf @ProjectPatch
  [ 1 :.
      [ 1 :. "Design v2" :. [ 3 :. "renamed" ]
      , 3 :. "Backend" :. [] ] ]
mapM_ print (res :: [PatchOut])

Just ("id" =: 1 :. "tasksProjectIdFkey" =: ["seq" =: 1 :. "title" =: "Design v2","seq" =: 3 :. "title" =: "Backend"])
Formatted SQL 
create or replace function pg_temp.__ins(data_0 jsonb) returns jsonb as $$
declare
  found_0 boolean;
  row_0 record;
  arr_0 jsonb[];
  id0 int8; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  arr_1 jsonb[];
  project_id1 int8; 
  seq1 int4; 
  title1 text; 
  data_2 jsonb;
  found_2 boolean;
  row_2 record;
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    update tut.projects
      set id = (row_0.value->>'id')::int8
      where id IS NOT DISTINCT FROM (row_0.value->>'id')::int8
      returning id into id0;
      found_0 := found;
    if found_0 then
      data_1 := row_0.value->'tasksProjectIdFkey';
      arr_1:= '{}';
      for row_1 in select * from jsonb_array_elements(data_1)
      loop
        insert into tut.tasks(project_id, seq, title)
          values (id0, (row_1.value->>'seq')::int4, (row_1.value->>'title')::text)
          on conflict (project_id, seq)
            do update set title = EXCLUDED.title
          returning project_id, seq, title into project_id1, seq1, title1;
              found_1 := found;
        data_2 := row_1.value->'taskEventsProjectIdSeqFkey';
        for row_2 in select * from jsonb_array_elements(data_2)
        loop
          insert into tut.task_events(project_id, seq, event_no, kind)
            values (project_id1, seq1, (row_2.value->>'eventNo')::int4, (row_2.value->>'kind')::text)
            on conflict (project_id, seq, event_no)
              do update set kind = EXCLUDED.kind;
                  found_2 := found;
        end loop;
        arr_1 := array_append(arr_1, jsonb_build_object('seq', seq1, 'title', title1));
      end loop;
    end if;
    if found_0 then
      arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'tasksProjectIdFkey', to_jsonb(arr_1)));
    else
      arr_0 := array_append(arr_0, 'null'::jsonb);
    end if;
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

The root here is only the key of the project, so the project is updated by id (there is nothing to change in it, but it must be found to get to the tasks). The tasks have the key (project_id, seq), where project_id comes from the project, and all mandatory columns: we passed title, and priority has a default. So for the tasks an insert … on conflict runs: task 1 already existed, and its title changed, while task 3 did not exist and was inserted. On a conflict only the columns that are in the type are updated, so the priority of task 1 stayed the same. The new event was inserted the same way.

Note the result type PatchOut: the root in it is wrapped in Maybe, but the tasks are not. The project is updated by key (update) and may not be found, while the tasks go through insert … on conflict, after which the task row always exists. So Maybe is needed only where an update runs. If a row is not found, its place in the result holds Nothing, and its child rows are not processed:

(res, _) <- upsertJSON (MyAnn "projects") conn $ rowsOf @ProjectPatch
  [ 999 :. [ 1 :. "Ghost" :. [] ] ]
print (res :: [PatchOut])

[Nothing]

There is no project 999, and the task "Ghost" was not inserted. Where Maybe is required in the result of upsertJSON and where it is forbidden is summarized in a table in the reference.

Update: updateJSON

updateJSON never inserts anything: it updates each row of the tree by its key. So a key is needed on every level, but mandatory columns are not. Let us set project 2 to the status active, change the priority of its task 1, and try to update the non-existent task 9 and the non-existent project 999:

type TaskUpd = "seq" := Int32 :. "priority" := Int32

type ProjectUpd = "id" := Int64 :. "status" := ProjectStatus
  :. "tasksProjectIdFkey" := [TaskUpd]

type UpdOut = Maybe ("id" := Int64 :. "status" := ProjectStatus
  :. "tasksProjectIdFkey" := [Maybe ("seq" := Int32 :. "priority" := Int32)])

(res, sqlUpd) <- updateJSON (MyAnn "projects") conn $ rowsOf @ProjectUpd
  [ 2 :. Project_status_active :. [ 1 :. 5, 9 :. 50 ]
  , 999 :. Project_status_active :. [] ]
mapM_ print (res :: [UpdOut])

Just ("id" =: 2 :. ("status" =: Project_status_active :. "tasksProjectIdFkey" =: [Just ("seq" =: 1 :. "priority" =: 5),Nothing]))
Nothing
Formatted SQL 
create or replace function pg_temp.__ins(data_0 jsonb) returns jsonb as $$
declare
  found_0 boolean;
  row_0 record;
  arr_0 jsonb[];
  id0 int8; 
  status0 tut.project_status; 
  data_1 jsonb;
  found_1 boolean;
  row_1 record;
  arr_1 jsonb[];
  seq1 int4; 
  priority1 int4; 
begin
  arr_0:= '{}';
  for row_0 in select * from jsonb_array_elements(data_0)
  loop
    update tut.projects
      set id = (row_0.value->>'id')::int8, status = (row_0.value->>'status')::tut.project_status
      where id IS NOT DISTINCT FROM (row_0.value->>'id')::int8
      returning id, status into id0, status0;
      found_0 := found;
    if found_0 then
      data_1 := row_0.value->'tasksProjectIdFkey';
      arr_1:= '{}';
      for row_1 in select * from jsonb_array_elements(data_1)
      loop
        update tut.tasks
          set seq = (row_1.value->>'seq')::int4, priority = (row_1.value->>'priority')::int4
          where project_id = id0 and seq IS NOT DISTINCT FROM (row_1.value->>'seq')::int4
          returning seq, priority into seq1, priority1;
              found_1 := found;
        if found_1 then
        end if;
        if found_1 then
          arr_1 := array_append(arr_1, jsonb_build_object('seq', seq1, 'priority', priority1));
        else
          arr_1 := array_append(arr_1, 'null'::jsonb);
        end if;
      end loop;
    end if;
    if found_0 then
      arr_0 := array_append(arr_0, jsonb_build_object('id', id0, 'status', status0, 'tasksProjectIdFkey', to_jsonb(arr_1)));
    else
      arr_0 := array_append(arr_0, 'null'::jsonb);
    end if;
  end loop;
  return to_jsonb(arr_0);
end; 
$$ language plpgsql;

The result shows the fate of each row. Project 2 was found, and in its list of tasks the first one changed, while task 9 is replaced by Nothing. There is no project 999, and it is replaced by Nothing too. The lists of the result come in the same order as the input, so each Nothing is easy to match with what was not found. Any row may be missing, so in the result type of updateJSON both the root and the elements of child lists must be wrapped in Maybe.

If some row of the tree has no key, there is nothing to update it by, and the program does not compile. Let us remove, for example, seq from the task type:

• We can't upsert data because for table NameNS "tut" "tasks"
  not all mandatory fields are present and no full primary or
  unique key is covered by record fields.
  Missing mandatory fields: ["seq", "title"]
  Possible keys: '[["project_id", "seq"]]

The message is shared by upsertJSON and updateJSON, so it talks about upsert and mandatory columns. For updateJSON the last line matters: the list of keys, one of which must be entirely in the type. The column project_id comes from the project, so for the tasks seq is missing.

Transactions

As we said, each of these operations opens a transaction itself if none is open yet, and on an error nothing partly written remains. If a transaction is already open, the operation runs inside it and is subject to it: when the outer transaction is rolled back, everything the operation wrote is rolled back too. This lets you combine several operations into one transaction. In the next example we insert a project with the same id twice inside withTransaction from postgresql-simple:

let
  docs = rowsOf @("id" := Int64 :. ProjectIn)
    [ 100 :. 1 :. "Tx demo" :. Project_status_draft :. pgArr' [] :. [] ]
res <- try @SqlError $ withTransaction conn do
  _ <- insertJSON_ (MyAnn "projects") conn docs
  insertJSON_ (MyAnn "projects") conn docs  -- the same id: an error
putStrLn $ either (("error: " <>) . show . sqlErrorMsg) (const "ok") res
cnt <- countProjects conn "Tx demo"
putStrLn $ "projects \"Tx demo\": " <> show cnt

error: "duplicate key value violates unique constraint \"projects_pkey\""
projects "Tx demo": 0

The second insert violated uniqueness, and withTransaction rolled back the whole transaction, including the first, successful insert. There is no project "Tx demo" in the database. To count projects we used an aggregate from the chapter Select:

-- | How many projects have this title.
countProjects :: Connection -> Text -> IO Int64
countProjects conn title = do
  (rows, _) <- selectSch (MyAnn "projects") conn $ qRoot $ qWhere $ "title" =? title
  pure $ sum $ unAggr <$> plains @("cnt" := Aggr ACount Int64) rows

Summary

  • insertJSON inserts a tree in one operation, and columns that come from the relation with the parent need not be given.
  • The branches of the result must be among the branches of the input; the columns may be any.
  • upsertJSON chooses update, insert … on conflict or insert for each level from the fields of its type.
  • updateJSON never inserts and requires a key on every level.
  • Rows that are not found give Nothing in their place in the result.
  • The operations run in a transaction and are subject to an outer transaction.

What next

We have covered all the main operations of pg-schema: reading and changing data both in one table and in several related ones. The compiler messages we met along the way are collected in the chapter Compiler errors, and the rules we saw by example in the reference.