Upsert records with GraphQL

Using upsert to perform GraphQL insert or update operations.

In addition to insert, update, and delete, Skedulo provides an upsert mutation for Pulse tenants to insert or update an existing record in a single GraphQL operation. An upsert uses a unique field to check whether a record already exists, then either updates the record or creates a new one with the provided field values.

An upsert operation requires the name of the key field to be provided in the request. This is usually a unique ID from an external system. For example, if you have a record associated with a client in another system, such as an ordering system or health care provider, you can use the unique ID of that record as the key field in an upsert.

The key field must be marked as ‘unique’ in your Skedulo data model.

Because the upsert may perform an insert operation, all fields marked as ‘required’ in your data model must be provided. Any optional fields that are not provided are set to their default values if an insert is performed or remain unchanged if an update is performed.

Upsert example

Set up the data model

Create a new custom object called ClientCase with text fields Name and ClientCaseNumber. The ClientCaseNumber field is the external key and must be marked as unique. Note that upsert can also be used on standard objects like Jobs and Accounts so long as they have a unique field to use as the key.

See the Skedulo CLI command reference for information about using the CLI to create custom objects and fields. See below for the artifact files you can use to create the data model for this example using the CLI. Note the CLI or Skedulo Lens API must be used when creating a unique custom field as this is not supported in the Skedulo web app.

Use upsert to insert a new record

Use GraphQL to upsert a new ClientCase record, providing a ClientCaseNumber. This will perform an insert since there is no existing record with the given ClientCaseNumber.

Note we are capturing the UID of the inserted record in an idAlias variable called UPSERTED_ID. This allows us to refer to this record in the ‘get’ operation.

mutation {
    schema {
        upsertClientCase(
            keyField: "ClientCaseNumber"
            input: {
                Name: "Inserting a new record"
                ClientCaseNumber: "CaseID_0001"
            }
            idAlias: "UPSERTED_ID"
        )
        getClientCase(id: "UPSERTED_ID") {
            UID
            Name
            ClientCaseNumber
            CreatedDate
            LastModifiedDate
        }
    }
}

Use upsert to update an existing record

Execute the upsert again with a different Name. This will update the existing record since the ClientCaseNumber key input hasn’t changed.

You will see that the Name field has been updated, and the UID and CreatedDateremain unchanged, while theLastModifiedDate` is updated to the current date and time.

mutation {
    schema {
        upsertClientCase(
            keyField: "ClientCaseNumber",
            input: {
                Name: "Updating an existing record"
                ClientCaseNumber: "CaseID_0001"
            }, idAlias: "UPSERTED_ID"
        ),
        getClientCase(id: "UPSERTED_ID") {
            UID
            Name
            CreatedDate
            LastModifiedDate
        }
   }
}

Use upsert to insert another new record

Now run the same query as above, but change the ClientCaseNumber field value to CaseID_0002:

mutation {
    schema {
        upsertClientCase(
            keyField: "ClientCaseNumber",
            input: {
                Name: "Inserting another new record"
                ClientCaseNumber: "CaseID_0002"
            }, idAlias: "UPSERTED_ID"
        ),
        getClientCase(id: "UPSERTED_ID") {
            UID
            Name
            CreatedDate
            LastModifiedDate
        }
    }
}

Check the results

Execute a query for ClientCase.

query {
    clientCase(filter: "ClientCaseNumber LIKE 'CaseID%'") {
        edges {
            node {
                UID
                Name
                ClientCaseNumber
                CreatedDate
                LastModifiedDate
            }
        }
    }
}

There will be two records returned, one for each ClientCaseNumber value used in the upsert operations.

{
  "data": {
    "clientCase": {
      "edges": [
        {
          "node": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Updating an existing record",
            "ClientCaseNumber": "CaseID_0001",
            "CreatedDate": "2024-08-07T06:10:05.426Z",
            "LastModifiedDate": "2024-08-07T06:13:39.861Z"
          }
        },
        {
          "node": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Inserting another new record",
            "ClientCaseNumber": "CaseID_0002",
            "CreatedDate": "2024-08-07T06:15:42.644Z",
            "LastModifiedDate": "2024-08-07T06:15:42.644Z"
          }
        }
      ]
    }
  }
}

Data model artifacts

You can use the following artifact definitions to create the data model for this example using the Skedulo CLI.

ClientCase object 

{
  "metadata": {
    "type": "CustomObject"
  },
  "name": "ClientCase",
  "label": "Client Case",
  "description": "An Client Case custom object for GraphQL examples"
}

Name field 

{
  "metadata": {
    "type": "CustomField"
  },
  "objectName": "ClientCase",
  "name": "Name",
  "field": {
    "type": "String",
    "description": "A name for the client case",
    "display": {
      "label": "Name",
      "order": 0,
      "isAlert": false,
      "showIf": null,
      "showOnDesktop": true,
      "showOnMobile": false,
      "editableOnMobile": false,
      "requiredOnMobile": false
    },
    "constraints": {
      "required": false,
      "unique": false,
      "accessMode": "ReadWrite",
      "maxLength": 255
    }
  }
}

ClientCaseNumber field 

{
  "metadata": {
    "type": "CustomField"
  },
  "objectName": "ClientCase",
  "name": "ClientCaseNumber",
  "field": {
    "type": "String",
    "description": "The ID of the client case in the external system",
    "display": {
      "label": "Client Case Number",
      "order": 0,
      "isAlert": false,
      "showIf": null,
      "showOnDesktop": true,
      "showOnMobile": false,
      "editableOnMobile": false,
      "requiredOnMobile": false
    },
    "constraints": {
      "required": false,
      "unique": true,
      "accessMode": "ReadWrite",
      "maxLength": 255
    }
  }
}