Bulk insert mutations with GraphQL

GraphQL insert mutations are used to create new objects in your back-end (typically a database) which can then be queried.

In any typical application, you will often need to insert multiple objects into the database in one go. These could be objects of the same type or objects of different types.

Some things to keep in mind in such a scenario would be:

• How many network requests are needed to achieve the inserts?
• How many and how efficient are the insert queries that will actually be run on the database?
• Is each insert independent of others? i.e. is the order of execution important.
• If some of the inserts fail, what happens to the rest of the inserts? Should the rest of the inserts go through or should all the inserts be rolled back?

The Hasura GraphQL engine

The Hasura GraphQL engine provides instant GraphQL APIs over any Postgres database.

With the above points considered, the Hasura GraphQL engine provides the following features for bulk insert mutations which guarantee a high performance and that the database is always in a consistent state:

• multiple objects of the same type can be inserted using a single insert mutation field within a mutation. This will be executed as a single SQL statement in the database.
• multiple objects of different types can be inserted using multiple insert mutation fields within the same mutation.
• if there are multiple mutation fields within a mutation, they will be executed sequentially on the database.
• if any of the mutation fields within a mutation fail, all the mutation fields in that mutation will be rolled back. i.e. all the mutation fields within a mutation are run as a transaction.

Let us assume we have the following tables in our database:

The following are the insert mutation fields generated by Hasura GraphQL engine for the above tables:

As you can see, the insert mutation fields expect an array of objects to be passed as arguments which will be inserted into the database.

Now let’s see a few examples of bulk insert mutations using the Hasura GraphQL engine:

Insert multiple objects of same type

Example : Insert authors in bulk using a single insert_author mutation field in a mutation:

Notes:

• If insertion of any row fails (for example due to a duplicate primary key), none of the rows will get inserted.

Insert multiple objects of different types

Example : Insert author and articles written by that author using the insert_author and insert_article mutation fields in the same mutation:

Notes:

• In this example, the articles can be inserted only after the author is inserted as it would otherwise violate the author_id foreign key constraint. Hence the sequential execution of inserts is necessary here.
• If the inserting of author fails, the execution of the entire mutation halts and inserting of articles is not even attempted.
• If the inserting of articles fails, the insertion of the author is rolled back and the database state is restored to what it was before the mutation was attempted.