0

I am building an API on top of my PostgreSQL database, using Hasura and GraphQL as the middle layer.

I run a service business, where customers can subscribe to services such as "weekly cleaning of the coffee machine" or "refilling of the mojito cabinet". A customer can only subscribe to the same service once.

We are a sales driven organization, so we have different apps where people can add (customer, subscription) combination. We want an end point which accepts this (customer, subscription) combo, and takes care of details like inserting customer if it does not exist.

We first tried using the following mutation against Hasura:

mutation InsertOrders {
  insert_orders(objects: [
    { 
      customer: {data: {name: "GE"}}, 
      subscription:  {data: {name: "Coffee Refill"}}
    }
  ]) 
  {
    affected_rows
  }
}

This operation only succeeds if "GE" is not an existing customer and "Coffee Refill" is not an existing subscription. We then tried using the "on_conflict" functionality in Hasura:

mutation InsertOrders {
  insert_orders(objects: [
    { 
      customer: {data: {name: "GE"}, 
        on_conflict: { constraint:customers_name_unique , update_columns:[name] }
      }, 
      subscription:  {data: {name: "Coffee Refill"},
        on_conflict: { constraint:subscriptions_name_unique , update_columns:[name] }
      }
    }
  ]) 
  {
    affected_rows
  }
}

This kind of works, but it has several drawbacks:

  1. It updates the customer (or subscription) row if the customer exists, effectively a no-op update. This also updates a potential updated_at column.
  2. It's inefficient (see 1.). We have a tiny database (hundreds of rows), and our attempt to insert around 50 (customer, subscription) combos in one request timed out.

Database definition is here:

CREATE TABLE customer (
  id UUID PRIMARY KEY, 
  name VARCHAR(50) UNIQUE
);

CREATE TABLE subscription (
  id UUID PRIMARY KEY,
  name VARCHAR(50) UNIQUE
);

CREATE TABLE orders (
  customer UUID REFERENCES customer (id),
  subscription UUID REFERENCES subscription (id),
  PRIMARY KEY (customer, subscription)
)
tboerstad
  • 51
  • 4
  • To ignore the update you should leave the update_columns empty. Something like { on_conflict: { constraint:products_name_unique , update_columns:[] } } – Leonardo Alves Aug 21 '20 at 14:27
  • @LeonardoAlves Thank you for your input. I've tried this, but I get an error `cannot proceed to insert object relation "subscription" since insert to table "subscription" affects zero rows`. This is mentioned under "Nested upsert caveats" [in the Hasura documentation](https://hasura.io/docs/1.0/graphql/manual/mutations/upsert.html#update-selected-columns-on-conflict) – tboerstad Aug 21 '20 at 15:52
  • ease of app usage should not result in bad db design ... one action in app can result in a few chained actions under the hood (update/upsert customer, create product, use results to insert order - for invoice copy user/product data, not only link them) ... order [line] should not contain direct relation to product (no consistency - product change affects earlier relations) – xadm Aug 21 '20 at 16:11

0 Answers0