Posted onSep 18, 2024

Convert insert mutation to upsert

Upsert operations are crucial for maintaining data consistency.

In this quick post, we’ll walk through implementing anUpsert operation inHasura usingPostgreSQL and GraphQL.

Given some tables, i.e.:

-- a companies tableCREATETABLEpublic.companies(idUUIDPRIMARYKEYDEFAULTgen_random_uuid(),nameVARCHAR(255)NOTNULL,created_atTIMESTAMPWITHTIMEZONEDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPWITHTIMEZONEDEFAULTCURRENT_TIMESTAMP);-- a customers tableCREATETABLEpublic.customers(idUUIDPRIMARYKEYDEFAULTgen_random_uuid(),customer_idVARCHAR(255)NOTNULL,tokenVARCHAR(255)NOTNULL,company_idUUIDNOTNULL,acquirerVARCHAR(255)NOTNULL,created_atTIMESTAMPWITHTIMEZONEDEFAULTCURRENT_TIMESTAMP,updated_atTIMESTAMPWITHTIMEZONEDEFAULTCURRENT_TIMESTAMP);-- a unique constraint for some fields to be used by the mutation's on_conflict clauseALTERTABLEcustomersADDCONSTRAINTcustomers_customer_id_company_id_acquirer_keyUNIQUE(customer_id,company_id,acquirer);-- an index on the unique constraint for better performanceCREATEINDEXidx_customers_customer_id_company_id_acquirerONcustomers(customer_id,company_id,acquirer);-- a foreign key constraint for company_idALTERTABLEcustomersADDCONSTRAINTfk_customers_companyFOREIGNKEY(company_id)REFERENCEScompanies(id);

And the following executed seed

-- a few existing companiesINSERTINTOpublic.companies(id,name)VALUES('a75645ac-b99e-477f-b4a9-b59380b48693','Acme Corporation'),('b75645ac-b99e-477f-b4a9-b59380b48693','Globex Corporation'),('c75645ac-b99e-477f-b4a9-b59380b48693','Soylent Corp'),('d75645ac-b99e-477f-b4a9-b59380b48693','Initech'),('e75645ac-b99e-477f-b4a9-b59380b48693','Umbrella Corporation');

Upsert Customer GraphQL Mutation

The following mutation either inserts a new customer or updates the token field of an existing customer if a conflict occurs.

# upsert mutation using on_conflict to handle duplicatesmutationUpsertCustomer($customer_id:String!,$token:String!,$company_id:uuid!,$acquirer:String!){insert_customers_one(object:{customer_id:$customer_id,token:$token,company_id:$company_id,acquirer:$acquirer},on_conflict:{constraint:customers_customer_id_company_id_acquirer_key,update_columns:[token]}){idcustomer_idtokencompany_idacquirer}}

Insert a New Customer
When the unique constraint fields do not exist, the mutation inserts a new customer.

- **Query Variables**

    ```json    {      "customer_id": "CUST123",      "company_id": "a75645ac-b99e-477f-b4a9-b59380b48693",      "acquirer": "ACQUIRER789",      "token": "TOKEN456"    }    ```

- **Response**

    ```json    {      "data": {        "customers": [          {            "id": "fd285dfc-b818-4db0-bd15-80cb015983d7",            "token": "TOKEN456",            "company_id": "a75645ac-b99e-477f-b4a9-b59380b48693",            "created_at": "2024-09-17T19:37:44.350509+00:00",            "acquirer": "ACQUIRER789",            "updated_at": "2024-09-17T19:40:01.456019+00:00"          }        ]      }    }    ```

Update an Existing Customer
Whereas the unique constraint fields already exist, the mutation updates the token field.

- **Query Variables**

    ```json    {      "customer_id": "CUST123",      "company_id": "a75645ac-b99e-477f-b4a9-b59380b48693",      "acquirer": "ACQUIRER789",      "token": "TOKEN4567"    }    ```

- **Response**

    ```json    {      "data": {        "customers": [          {            "id": "fd285dfc-b818-4db0-bd15-80cb015983d7",            "token": "TOKEN4567",            "company_id": "a75645ac-b99e-477f-b4a9-b59380b48693",            "created_at": "2024-09-17T19:37:44.350509+00:00",            "acquirer": "ACQUIRER789",            "updated_at": "2024-09-17T19:42:01.456019+00:00"          }        ]      }    }    ```