Insert multiple relational objects transactionally in Hasura

I am 99% sure Hasura is made by an extraterrestrial life form with near super intelligence because Hasura is just amazing! Maybe I am hyping this just a little bit too much but let me elaborate with one example why Hasura is great.

We will look how Hasura can handle insertion of multiple relational objects transactionally.

The use case

We are building a service to save data of customer offers. Our MVP currently has two tables. customer_offers and customer_offer_products.

Create customer_offers table

Create customerofferproducts table

After creating the two tables we would like to tell Hasura that there is a relationship between the two tables. customer_offer_products.customer_offer_id is a foreign key to customer_offer.id. This is easily done by editing the field customer_offer_id in the table customer_offer_products.

Add Foregin Key between the two tables

Next track all relations by going back to the first page on “Data” in the top menu. Click on “Track All Relations”-button.

Track All Records

The database setup is completed. The two tables is set up and we have created a relationship between the two tables using foreign keys. We have also made sure Hasura knows about this relationshop by tracking it.

Next thing is to insert some data.

The trivial way to insert a customer offer, with products, is to:

1. Insert the customer_offer data.
2. Retrieve the id of our newly created customer_offer row.
3. Loop through our products and insert each tocustomerofferproducts

This approach obviously work but comes with a few downsides. First, the queries is not done transactionally which means if the potential third product you are trying to insert fails, you are still left with data in your database. And you need to find a way to fix your incorrect data. Second, you are doing multiple queries adding latency and bandwidth between your client and server.

Next optimization you could do is to:

1. Insert the customer_offer data (same as previous)
2. Retrieve the id of our newly created customer_offer row (same as previous)
3. Add all products with one insert mutation (new)

This approach has the benefit that all products gets inserted correctly, or none gets inserted. However, if your products insertion would fail we still have to handle the fact that our customer_offer was written without any products (since they failed). With regards to number of queries and latency we are down to only two hits to our Hasura GraphQL back-end server.

But we can do better!

Our next and final optimization is to insert everything in a single insert mutation. This is what we want to do. We are going to insert multiple relational objects transactionally. This has the benefit that either all data (both customer_offer and all products to customer_offer_products) gets inserted correctly of none gets inserted, if the insertion fails. Also we are only hitting our GraphQL back-end with a single request for our insertion. Brilliant!

Our insert mutation

mutation (
  $customer_offer: customer_offers_insert_input!
) {
  insert_customer_offers (
  objects: [$customer_offer]
  ) {
    returning {
      id
      offer_start_at
      offer_end_at
      customerOfferProductssBycustomerOfferId {
        id,
        name
        quantity
        unit
        price
        customer_offer_id
      }
    }
  }
}

Our variables

{
  "customer_offer": {
    "offer_start_at": "2019-01-21T14:23:56.750Z",
    "offer_end_at": "2019-02-21T14:23:56.750Z",
    "customerOfferProductssBycustomerOfferId": {
      "data": [{
        "name": "Järnfilter RIF 100",
        "price": 14900,
        "vat": 25,
        "quantity": 1,
        "unit": "st"
      }, {
        "name": "Frakt",
        "price": 900,
        "vat": 25,
        "quantity": 1,
        "unit": "st"
      }]
    }
  }
}

Our Result

{
  "data": {
    "insert_customer_offers": {
      "returning": [
        {
          "offer_end_at": "2019-02-21T14:23:56.75+00:00",
          "offer_start_at": "2019-01-21T14:23:56.75+00:00",
          "customerOfferProductssBycustomerOfferId": [
            {
              "quantity": 1,
              "customer_offer_id": 5,
              "name": "Järnfilter RIF 100",
              "id": 9,
              "price": 14900,
              "unit": "st"
            },
            {
              "quantity": 1,
              "customer_offer_id": 5,
              "name": "Frakt",
              "id": 10,
              "price": 900,
              "unit": "st"
            }
          ],
          "id": 5
        }
      ]
    }
  }
}

As viewed from GraphiQL in the Hasura console.

View from GraphiQL in the Hasura console

What should be noted is that our two customer_offer_products rows has the same customer_offer_id as our customer_offer.id.