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.
Introduction
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
Bulk Update
Let us consider a simple blog schema where we have the following article and author tables with the author_id of the article table being the foreign key from the author table’s id .
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.
Bulk Delete
Lets consider the same blog schema as mentioned above. The Hasura GraphQL Engine generates the following delete mutation fields for the two tables:
If the 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 article table.
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 thearticle 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 articles with author_id = 5 and then delete the author with id = 5 . The mutation will look something like:
Update and Delete together
Consider a simple e-commerce schema which has the tables cart , order, product, user and more. order and product tables are related through a many-to-many relationships using an order_product table.
Say the user has placed an order for two items and just initiated the payment. At this time, the state of the cart , order and 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_processing and 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 ordertable withpayment_processing to false and payment_successful to true. We have also deleted all the items from the cart of user with id = 3333 .
Top comments (0)