Upsert queries on Postgres using Hasura

Performing upsert queries on Postgres using GraphQL.

TL;DR

This post talks about upsert queries, what they are and why they’re helpful and on how to make upsert queries on the Hasura platform using the GraphQL APIs. Here is a quick summary of what we will cover

• Upsert = Update or Insert. If the record to be inserted exists, it will be updated with the new values and if the record does not exist, it will be inserted as a new record on the table.
• Get started with a base project on the Hasura platform by running hasura quickstart base in your command shell.
• Create a driver_location (columns: driver_id, latitude, longitude) table using Hasura’s API console.
• Upsert into the table using a GraphQL insert mutation with the help of the on_conflict parameter.

What are upsert queries ?

Upsert stands for Update or Insert. This means that using a single statement you can update a record if it exists or insert a new record if it does not.

Let’s say you have a table driver_location (columns: driver_id and location) which stores the location of drivers. You want to update the driver location every 60 seconds. Updating the location of a driver is quite straightforward, but how do you account for the first time ? Since the record does not exist, you cannot update it. Instead, you need to insert data into the table.

The logic to handle this case will look something like this:

• Fetch the location of the driver.
• Make a select query to the driver_location table with the id of the driver.
• If the select query returns a record, then update the location of this record with the new location.
• If the record for the provided driver_id does not exist, make an insert query with the driver_id and location.

All of the above can be replaced with a single upsert query.

Advantage of using upsert queries

• Avoid two round trips to the database.
• Avoid complex logic to update or insert data which can lead to race conditions or duplicate records.
• Better readability. When compared to a single query the advantage might seem trivial but real world applications have dozens of queries and improved readability is a big plus in those situations.
• More performant. Upsert queries are faster than making a round trip or using common table expressions.

Now that we know upsert queries are awesome. Let’s take a look at how we can make upsert queries on Postgres using GraphQL mutations.

Pre-requisites

• Ensure that you have the hasura cli tool installed on your local machine.
• Login/Signup into the Hasura platform by running hasura login in your command shell.

Getting started

Let’s get started with a basic Hasura project. Run the following command in your command shell:

$ hasura quickstart hasura/base

This will

• Create a free Hasura cluster for you which will have a postgres service running on it.
• Create a new directory called base as your Hasura project directory. It also initialised base as a git repository and adds hasura as a remote to it.

Next, let’s deploy this project on to the Hasura cluster

cd base
git add . && git commit -m "Initial Commit"
git push hasura master

API Console

Every Hasura cluster comes with an API Console that you can use to build the backend for your application.

Run the command hasura api-console inside the base directory to open up the Api Console.

The API Console will open at http://localhost:9695/api-explorer

Creating a table

Head to the Data tab and click on Create Table to create a new table. Let’s create a driver_location table with the columns

• driver_id: Integer
• latitude: Numeric
• longitude: Numeric

Click on the create button to create the table.

Making upsert queries using GraphQL

To try out GraphQL APIs head on to the API Explorer

The mutation to upsert data into the table will be

mutation upsert_driver_location {
  insert_driver_location(
    objects: $driver_location, 
    on_conflict: $on_conflict_rule
  ) {
    returning {
      driver_id
      latitude
      longitude
    }
  }
}

The $driver_location and $on_conflict_rule will be

{
  "driver_location": [
    {
      "driver_id": 1,
      "latitude": 1.11,
      "longitude": 2.99
    }
  ],
  "on_conflict_rule": {
    "action": "update",
    "constraint_on": ["driver_id"]
  }
}

$driver_location is the value we want to insert/update. The $on_conflict_rule is what defines how this mutation should behave. In this case, we are specifying that we want to update the record when there is a conflict on the constraint placed on driver_id.

The conflict happens because driver_id is the primary key of the table. And when we try inserting a new row with the same driver_id a conflict occurs and our on_conflict_rule gets executed.

Apart from update you can also have ignore as an action (self explanatory).

And that is all! An upsert query is basically an insert query with a rule to handle insert conflicts. If you have any questions or suggestions, do not forget to drop in a comment!

Hasura gives you instant GraphQL APIs over any Postgres database without having to write any backend code.

If you liked this post, do consider signing up for our mailing list to receive updates on all the content we publish.