best practice for creating multiple records with foreign key relations in a single mutation

[leoschweizer]

I'm submitting a ...

• bug report
• feature request
• question

PostGraphile version: Library: 4.0.0

Suppose I have a relation of Customer(id, name, email, ...) and Address(id, customerId, street, zip, city, ...). From the business logic perspective, I don't want a customer to exist without at least one address, so when creating a customer, I always want to create an address as well in an atomic way (if the address creation fails for some reason, the customer creation should fail as well).

I suppose this use case is fairly common, however I couldn't find any recommendations on how to handle it.

The crucial point is probably that the id of the customer has to be known in order to create the Address. I can think of two options about handling that:

1. Use client-side generated ids, which would allow to create a "compound mutation" with payloads for a Customer and an Address which can then simply be inserted on the server side. However I really dislike this option, since it feels like this opens a whole attack surface (or I need to introduce custom validation logic to ensure that ids match etc).

2. Use a custom mutation like the following:

CREATE FUNCTION "createCustomerWithAddress"(customer "Customer", address "Address")
RETURNS "Customer" AS $$
WITH 
  c AS (
    INSERT INTO "Customer" VALUES (customer) RETURNING *
  ),
  a AS (
    INSERT INTO "Address" ("customerId", "street", ...)
    VALUES ((SELECT id FROM s), address."street", ...)
  )
SELECT * FROM c
$$ LANGUAGE sql VOLATILE;

This is picked up nicely by the automatic schema generation of Postgraphile. However, what I dislike about this approach is that I have to re-enumerate all the fields of the Address table in the function. That means that I would have to update the stored procedure when the schema changes. Or is there a clever way to use everything from the address input and add the id of the newly generated Customer to that input?

Any advice on how to handle this use case cleanly would be appreciated!

(btw: this project is awesome 🥇)

[benjie]

What you're looking for is nested mutations (ref: #361) which we don't officially have yet; however there is a plugin from the community which implements it that I was told is working just this morning - I suggest you hop on to gitter and chat to @mlipscombe - I'm sure an offer to test the software would be welcome.

Another option is to add a custom mutation via a plugin, the easiest way to achieve this is with the new graphile-utils package: https://www.graphile.org/postgraphile/extending/#the-easy-way-graphile-utils I only released this last week so any help tidying up the docs for it would be welcome.

From the SQL point of view you could write code in one of the more dynamic languages (plpython, plv8, etc) that constructs the column list dynamically.

[mlipscombe]

I've published a very alpha version of postgraphile-plugin-nested-mutations to npm. Github project here: https://github.com/mlipscombe/postgraphile-plugin-nested-mutations. Feedback is very much welcome!

[leoschweizer]

Thanks for the prompt reply @benjie and great work @mlipscombe 👍.

I will evaluate the options, although I will probably have to go the route of the stored procedure outlined above since at least in some cases, I need to support more than a single level of nesting.

[mlipscombe]

@leoschweizer unlimited depth of nesting is high on my todo list -- I'd say in the next few days.

Before writing this plugin, I tried using functions to achieve the same thing, but it's not practical, because a function with a table type argument will not "do the right thing" when columns have defaults, because there's no way of specifying "default value" for a column ... if it is omitted, it will be null, and null violates whatever not null constraint is on a row that has a default value.

[leoschweizer]

@mlipscombe that's good to know. I guess for the time being I have to enumerate all the columns explicitly then, including the default values...

[mlipscombe]

@leoschweizer the latest release I just made (1.0.0-alpha.5) of postgraphile-plugin-nested-mutations supports an infinite depth of nested mutations.