DEV Community

Rogério Rodrigues de Alcântara
Rogério Rodrigues de Alcântara

Posted on

Convert insert mutation to upsert

Upsert operations are crucial for maintaining data consistency.

In this quick post, we’ll walk through implementing an Upsert operation in Hasura using PostgreSQL and GraphQL.

  1. Given some tables, i.e.:

    -- a companies table
    CREATE TABLE public.companies (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name VARCHAR(255) NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    );
    
    -- a customers table
    CREATE TABLE public.customers (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        customer_id VARCHAR(255) NOT NULL,
        token VARCHAR(255) NOT NULL,
        company_id UUID NOT NULL,
        acquirer VARCHAR(255) NOT NULL,
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
    );
    
    -- a unique constraint for some fields to be used by the mutation's on_conflict clause
    ALTER TABLE customers ADD CONSTRAINT customers_customer_id_company_id_acquirer_key 
        UNIQUE (customer_id, company_id, acquirer);
    
    -- an index on the unique constraint for better performance
    CREATE INDEX idx_customers_customer_id_company_id_acquirer 
        ON customers (customer_id, company_id, acquirer);
    
    -- a foreign key constraint for company_id
    ALTER TABLE customers ADD CONSTRAINT fk_customers_company
        FOREIGN KEY (company_id) REFERENCES companies(id);    
    
  2. And the following executed seed

    -- a few existing companies
    INSERT INTO public.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');
    
  3. 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 duplicates
    mutation UpsertCustomer($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]
        }
      ) {
        id
        customer_id
        token
        company_id
        acquirer
      }
    }
    
  4. Insert a New Customer

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

- **Query Variables**
Enter fullscreen mode Exit fullscreen mode
    ```json
    {
      "customer_id": "CUST123",
      "company_id": "a75645ac-b99e-477f-b4a9-b59380b48693",
      "acquirer": "ACQUIRER789",
      "token": "TOKEN456"
    }
    ```
Enter fullscreen mode Exit fullscreen mode
- **Response**
Enter fullscreen mode Exit fullscreen mode
    ```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"
          }
        ]
      }
    }
    ```
Enter fullscreen mode Exit fullscreen mode
  1. Update an Existing Customer

    Whereas the unique constraint fields already exist, the mutation updates the token field.

- **Query Variables**
Enter fullscreen mode Exit fullscreen mode
    ```json
    {
      "customer_id": "CUST123",
      "company_id": "a75645ac-b99e-477f-b4a9-b59380b48693",
      "acquirer": "ACQUIRER789",
      "token": "TOKEN4567"
    }
    ```
Enter fullscreen mode Exit fullscreen mode
- **Response**
Enter fullscreen mode Exit fullscreen mode
    ```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"
          }
        ]
      }
    }
    ```
Enter fullscreen mode Exit fullscreen mode

Summary

By following these steps, we can easily implement upsert operations in Hasura, allowing for efficient data management and updates.

References

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more