graphile
Auto-generated bulk insert mutations
Feature description
The PostGraphile documentation mentions bulk inserts, but the example there is not super helpful for implementing a custom mutation for a rather common (I'd imagine) use case for inserting multiple records of the same type in one query.
It seems there are other (commercial) products who expose bulk inserts automatically and I'm wondering if there's a reason behind PostGraphile not making these available in the default mutations?
Motivating example
I'm not a Postgres-expert, so it took me good bunch of research and learning about array and composite types and functions to finally come up with a solution that creates an endpoint which takes an array of table row records and bulk-inserts them:
-- header
CREATE TABLE "header" (
"mid" INTEGER,
"type" VARCHAR,
"contents" TEXT,
FOREIGN KEY("mid") REFERENCES "email"("mid")
);
-- allow bulk-creating header rows
CREATE FUNCTION public.create_headers(create_multiple public.header[])
RETURNS SETOF public.header
AS $$
INSERT INTO public.header
SELECT (UNNEST(create_multiple)).*
RETURNING *;
$$ LANGUAGE sql STRICT VOLATILE;
At the very least it might be useful to list (or, a simpler version, if one exists, this is the best I could come up with) in the docs, but having this as a built-in generated option seems like a useful thing to have, too.
Breaking changes
Implementing this would be an additional exposed default mutation, I don't see that resulting in breaking changes.
Supporting development
I [tick all that apply]:
- [ ] am interested in building this feature myself
- [x] am interested in collaborating on building this feature
- [x] am willing to help testing this feature before it's released
- [ ] am willing to write a test-driven test suite for this feature (before it exists)
- [ ] am a Graphile sponsor ❤️
- [ ] have an active support or consultancy contract with Graphile
I'm wondering if there's a reason behind PostGraphile not making these available in the default mutations?
The reason is that most tables in your database probably don't need bulk insertion mutations, so adding 100 bulk insert mutations just so that the 1 or 2 you need are present makes for a very messy/large schema. I also subscribe to Marc-Andre Giroux' opinions on mutation design, that is to say that I would rather describe what we're trying to accomplish ("add these items to the shopping basket") versus how we're doing it ("insert these records into the cart table"): https://xuorig.medium.com/graphql-mutation-design-anemic-mutations-dd107ba70496. Take a look at the Graphile Starter GraphQL schema and you'll notice that even CRUD mutations are rare: https://graphile-starter.herokuapp.com/graphiql
It would be quite achievable to add bulk mutation plugins, I've just not had cause to write any yet. You'd start with the existing mutation plugins and then apply some small modifications to them.
I'd also like bulk inserts.
I also subscribe to Marc-Andre Giroux' opinions on mutation design, that is to say that I would rather describe what we're trying to accomplish ("add these items to the shopping basket") versus how we're doing it ("insert these records into the cart table")
Doesn't this contradict how all the other mutations in postgraphile are generated? They're generated from the DB, so there's no semantic information.
Not if you use custom mutations (mutations based on functions). It’s rare I use the CRUD mutations in my own projects, I prefer custom mutations in the vast majority of cases.
For others searching for this functionality, this seems to be such a plugin: https://github.com/tjmoses/postgraphile-plugin-batch-create-update-delete
[semi-automated message] To keep things manageable I'm going to close this issue as I think it's solved; but if not or you require further help please re-open it.