Postgrest: A relational insert

[dawidcxx]

Having a (simplified) model with a many to many relation like so

| customers | |
|------------|-----:|
| id | uuid |
| first_name | text |
| last_name | text |

| banks | |
|---------|-----:|
| id | uuid |
| name | text |

| customer_bank_accounts | |
|-----------------------|-------:|
| account_number | bigint |
| customer_id | uuid |
| bank_id | uuid |

Now lets assume that in my application I have a form that lets you add a new customer. On that form, besides creating a new customer I also want to immediately assign him to a known bank. Of course I could just: (in pseudo code)

1. newCustomerId = POST Prefer: return=representation /customers?select=id { "first_name": "John", "last_name": "Doe" }
2. POST /customer_bank_accounts { "account_number": 123456788901234556152, "customer_id": ${newCustomerId}, "bank_id": "some_preexisting_id_of_a_bank_that_the_user_selected"

However this of course is not trasactional and the problem here is that if step 2 fails I would like to rollback and discard the creation of a customer. But boom tss, the customer has already been created!

Is there an api that would let me to do this like so?

POST http://localhost:3000/customers?select=*,customer_bank_accounts{account_number, bank_id}

 {  
   "first_name":"John",
   "last_name":"Doe",
   "customer_bank_accounts":[  
      {  
         "account_number":5851859590151,
         "bank_id":"some_preexisting_id_of_a_bank_that_the_user_selected",
         "customer_id": "$parent_id????"
      }
   ]
}

From the docs I figure that the only way to achieve such would be to manually write a procedure and then call it via rpc, would that be correct?

[ruslantalpa]

Yes, that is correct, for now, RPCs are the only* way to do that.
It might be also possible to have the same effect using views and triggers.
for example if customers is a view and it has a column "customer_bank_accounts" which is a json, it might be possible for you to write an trigger that will allow you to do a POST to this view in the form that you specified above. I have no specific example but i remember someone implementing just this in one of the issues.

[dawidcxx]

@ruslantalpa Eh thats a bit of a bummer. Both solution sound to me more compilicated than it should be. There really needs to be a streamlined way of doing transactions, just as there is an easy and streamlined way of getting your data. But reading through other issues I see that there are already some ideas to implement transactions. Guess I will have to wait for them before adopting postgREST.

[steve-chavez]

I have a proposal for this. Once #690 is solved, we could take advantage of the new query parameter(columns) to do this:

-- having these tables
CREATE TABLE items (
  id serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE subitems (
    id serial PRIMARY KEY,
    name text NOT NULL,
    item_id integer REFERENCES items(id)
);

```http
POST /items?columns=name,subitems(name)
{
"name": "item 1",
"subitems": [
{"name": "subitem 1"},
{"name": "subitem 2"}
]
}

This would generate the following SQL(using [data-modifying CTEs](https://www.postgresql.org/docs/10/static/queries-with.html#QUERIES-WITH-MODIFYING)):
```sql
WITH 
payload AS (
  select '{"name": "item 1", "subitems": [{"name": "subitem 1"}, {"name": "subitem 2"}]}'::json as data),
ins1 AS (
  insert into items(name)
  select 
    name 
  from json_populate_record(null::items, (select data from payload)) _
  returning id AS item_id) 
insert into subitems(name, item_id)
select 
  name, 
  (select item_id from ins1) as item_id 
from json_populate_recordset(null::subitems, (select data from payload)->'subitems') _;

Since we know the tables relationship, we can infer that the item_id column is the fk column for subitems. This would work in a similar way for a M2M relationship(would have one more insert cte) and I think the query could be arranged to allow inserting many parent/child rows at the same time(by using pg json_array_elements).

This would be a major feature and requires a fair amount of work, so I'll require sponsorship to implement it.
For anyone that's interested in this see PostgREST's Patreon page or you could also contact me directly(email in profile).

[steve-chavez]

The query gets more complicated when there's multiple parent records to insert with their childs.

But here's a query that works https://gist.github.com/steve-chavez/60473d1765b5175012f5cc15695ae0b1.

@ruslantalpa Perhaps you may have some feedback about that.

[ruslantalpa]

the direction is good however:
to know all the CTEs in this query, you need to know all the levels in the payload, this means to generate this query as it is now, you need to inspect the payload in haskell and if that is the case and there is no way arround it then you might as well simplify the queries and have haskell do some work.

but again, the direction is good, maybe leave this part as it is and now focus on the following problem:
given a single json payload, how could it be split into multiple CTEs like payload_level_1, payload_level_2 ... and since we are splitting them, those CTE outputs might have some kind of virtual PKs (so that items from levels below can reference those from the level above, a kind of FK) and these virtual PKs can be used when inserting in stages and somehow eliminate the problem of order.

maybe what I am saying is, it might be doable but it's not trivial so maybe first try to split the problem into a few smaller ones and solve each stage (you started from the last stage):

1. given a payload, how many levels does it have (has to be done in sql, one could consider the primary keys and related tables as "know"). Is there some info that Haskell knows about the schema (without looking at the payload) that it can feed to SQL to make this task easier?
2. knowing the number of levels, is there a way to recursively create a CTE for each level?
3. once one has CTEs 1/2/3 is it possible to have virtual PKs in them linking the items/rows between levels
4. the last stage, recursively (based on the number from level 1) generate an insert for each level that uses the CTEs from 2.

the above is "brainstorming" around this feature, trying to split the problem into stages

[daurnimator]

to know all the CTEs in this query, you need to know all the levels in the payload, this means to generate this query as it is now, you need to inspect the payload in haskell

Isn't that information in the query string?

[steve-chavez]

@ruslantalpa With the new ?columns query arg we'd have what we need to know the levels without looking at the payload.

For example for inserting items + subitems + pieces, querystring would be:

POST /items?columns=name,subitems(name,pieces(name))
[
  {"name": "item 1", "subitems": [
    {"name": "subitem 1", "pieces": [{"name": "piece 1"}, {"name": "piece 2"}, {"name": "piece 3"}]}, 
    {"name": "subitem 2", "pieces": [{"name": "piece 4"}, {"name": "piece 5"}, {"name": "piece 6"}]}, 
    {"name": "subitem 3", "pieces": [{"name": "piece 7"}, {"name": "piece 8"}]}]}, 
  {"name": "item 2", "subitems": [
    {"name": "subitem 4", "pieces": [{"name": "piece 9"}, {"name": "piece 10"}]},
    {"name": "subitem 5", "pieces": [{"name": "piece 11"}]}]}
]

Besides that, looking at the schema cache is necessary to complete these parts:

json_to_recordset((select data from payload)) as (name text, subitems json)

Because of name text, we need to know the type of the column.

Edit: added some comments in the gist.

[ruslantalpa]

Ok, this kind of takes care of 1,2,4
So is it possible in 3 to generate some kind of virtual PKs so as to not rely on the returned order

[steve-chavez]

That's what I do with ordinality in the gist, those are the virtual pks. Though relying on the order is needed on the INSERT .. RETURNING parts.

[tomsaleeba]

For an example of what...

Yes, that is correct, for now, RPCs are the only* way to do that.

...from this comment looks like, check out the repo I made with a demo of how to build an RPC:

https://gitlab.com/tomsaleeba/postgrest-rpc-complex-insert-demo

Be warned, there was a lot of learning PL/pgpsql as I went to get that to work, so it's probably not pretty.