Upsert support

[TSIA-SN]

Even though Supabase supports upsert, the mutation is not available from GraphQL

Steps to reproduce the behavior:

1. Create the GraphiQL page against your project using the code here: GraphiQL for self-documenting GraphQL supabase#6144
2. In the Documentation explorer, check any table's available mutations
3. Mutation.deleteFrom[TableName]Collection, Mutation.insertInto[TableName]Collection, and Mutation.update[TableName]Collection are available, but Mutation.upsertInto[TableName]Collection is not

Expected behavior
Would expect to see a Mutation.upsertInto[TableName]Collection operation that would allow a constraint name to be used, or alternatively insertInto[TableName]Collection could have onConflict support as in Hasura: https://hasura.io/docs/latest/graphql/core/databases/postgres/mutations/upsert/

Versions:

• PostgreSQL: [14.1]
• pg_graphql v0.3.2

[olirice]

upsert support is on the roadmap but the user facing API is still being spec'd

I'm familiar with Hasura and Graphile's upsert APIs and would like to avoid directly referencing the constraint names if possible

[unknown1337]

is there any update on this? :)

[olirice]

none yet, we'll be looking at user defined functions and views first

[unknown1337]

thanks @olirice ! do you have any educated guess on:

1. will upsert eventually be implemented
2. if so, a rough time line? Q2 vs 2024?
3. can you suggest a workaround? as now I need to execute 2 queries iso 1 every time (and as a seperate query there is a small risk as its not a single transaction):
   (1)check if item exists, if so (2a) update, if not (2b) insert

many thanks!

[olirice]

sure

1. yes, its definitely going to happen
2. Currently I'd it'll be in by EO Q2 but that may change depending on who get pulled into various other priorities

Assuming:

• "check if an item exists" is based on some unique combination of colums
• once inserted, the record is not deleted
• the columns making up the unique constraint don't change in value

I'd suggest

1. enforcing the unique constraint in postgres
2. attempting the insert
3. if a unique constraint violation occurs
   • perform an update

[unknown1337]

thanks a lot for the update and suggestion!

[unknown1337]

@olirice , a note if Im correct on your proposal:

scenario, upsert in a single transaction

1. multiple related tables (e.g. 1a upsert a post entity and 1b. upsert a reference in the blog_post table as well)
2. multiple entities (e.g. upsert an (aggregate root), in the blog scenario assume a blog (Agg root) that contains several posts. Thus a 2a upsert for the blog table (title, body) has to take place as well as an 2b upsert for each of its posts (table)

Assume:

1. I assume 1a&1b or 2a and 2b should be a transaction to ensure data integrity (agree?). Thus they should be combined in a single qraphql mutation request.

your suggestion if Im correct (not that I have a better idea) has issues
A. when combining GQL queries 1a&1b or 2a and 2b to a single query, the insert will fail if a part of the data already exits (e.g. 2b where one of the blog posts is already in the db). The update will fail as well as one of the blog posts does not exist..
B to overcome A I need to write frontend code that executes a graphql (try insert()? done: update()) once for every entity (table row) & I need as its multiple queries now I need to write frontend rollback logic (whick might not be fails safe)

what are your thoughts?

[jaytxng]

Any update on this? I'm currently using custom defined upsert functions and calling them via supabase.rpc, but would much prefer standardizing to use gql mutations everywhere.

[olirice]

Note yet but nested inserts is the next large project we're going to tackle for GraphQL and this is pre-req for that so it should start moving soon

[ste00martin]

any update on when supabase will have update mutations?

[olirice]

update mutations

update mutations are already available. you can read more about them here

Work on upsert support started last week. You can track in PR

[pawarren]

+1 for upsert support, we'd start using it immediately :)

[damassi]

Anything to reduce the number of required API requests for common DB operations will greatly improve the quality of the graphql platform 🙏