Safely update and delete data in your database over GraphQL
This blog post will be purely based of examples about bulk update and delete mutations using the Hasura GraphQL Engine.
If you bulk edit the state of your application, there is a significant chance of messing up the state if the server implementation does not ensure a transaction. What is a transaction? Roughly put, a transaction is an independent task that must be processed independently and without loss of data in case of failures.
Hasura GraphQL Engine
We will use the open source Hasura GraphQL Engine (HGE) for showing examples of bulk mutations.
- It gives instant GraphQL APIs over Postgres without us needing to write a single line of code.
- It resolves all the bulk queries and mutations as transactions
Setup a GraphQL Project
Try this out by setting up a GraphQL backend using Hasura Cloud. You will get a Hasura GraphQL Engine instantly that you can connect to a Postgres. Get started here.
Let us consider a simple blog schema where we have the following
author tables with the
author_id of the
article table being the foreign key from the
The following are the update mutation fields generated by Hasura GraphQL Engine for the above tables:
Now if an author decides to deactivate their author profile. In that case, we have to update the
author table to deactivate the user and also update the
article table to set that author’s articles to unpublished. If the
author has the
id = 5 , the bulk mutation will look like:
In the above mutation, we are first updating the
author table and then the
article table. In both cases we are requesting the number of rows affected by the mutation. The entire mutation is treated as a transaction; which means, even if one of the queries fail, the entire query fails.
Lets consider the same blog schema as mentioned above. The Hasura GraphQL Engine generates the following delete mutation fields for the two tables:
author decides to delete their profile, we have to delete their entry in the
author table and also delete all the articles by that author in the
But in this case, the order of deletion matters. We cannot delete the entry from the
author table before deleting the entries from the
article table. This is because all the rows in the
article table with
author_id = 5 depend on the row in the
author table with
id = 5 (foreign key constraint). Therefore, we will first delete all the
author_id = 5 and then delete the
id = 5 . The mutation will look something like:
Update and Delete together
Consider a simple e-commerce schema which has the tables
user and more.
product tables are related through a many-to-many relationships using an
Say the user has placed an order for two items and just initiated the payment. At this time, the state of the
order_products related to this order is:
cart: [(1111, 2222, 3333), (1112, 4444, 3333)] order: [(5555, 3333, true, false)] order_products: [(6666, 5555, 2222), (6666, 5555, 4444)]
As soon as the payment is complete, we have to update the
order table to update the
payment_successful . We also have to delete the items from the cart of the user. Let us do this using a bulk update and delete mutation.
In the above mutation, we have updated the row (
id=5555 ) of the
order table with
payment_processing to false and
payment_successful to true. We have also deleted all the items from the cart of user with
id = 3333 .