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.
-
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); -
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'); -
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 } } -
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"
}
]
}
}
```
Summary
By following these steps, we can easily implement upsert operations in Hasura, allowing for efficient data management and updates.
Top comments (0)