Upsert records with GraphQL

Using upsert to perform GraphQL insert or update operations.

In addition to insert, update, and delete, Skedulo provides upsert<Schema> methods for standalone tenants for inserting or updating existing records in a single GraphQL operation.

An upsert uses a unique ID field to assess whether or not an object already exists, then either updates the record or creates a new object if it does not already exist.

An upsert operation requires the same input as insert operations, as well as the name of the field being upserted.

For Skedulo, the field being upserted must be unique. Creating a unique field using the Skedulo Lens API /custom/standalone/fields endpoint automatically marks the field as valid for upsert operations.

See the Skedulo API Reference Guide for information about using the API to create custom objects and fields.

Create a custom field in Skedulo

Create a custom standalone object with a unique field and track it using the Skedulo API:

Make a request to the /custom/standalone/schemas endpoint to create a custom external object with an external ID field:

{
  "name": "CustomExternalObject",
  "label": "Custom External Object",
  "fields": [
    {
      "name": "Name",
      "column": {
        "type": "string"
      }
    },
    {
      "name": "ExternalId",
      "column": {
        "type": "string",
        "unique": true
      }
    }
  ]
}

This returns the following information, including IDs for the object and related fields. Note that the ExternalId field includes the "upsertKey": true setting, indicating that it can be used for upsert operations:

{
  "result": {
    "schema": {
      "name": "CustomExternalObject",
      "description": null,
      "label": "Custom External Object",
      "mapping": "__customexternalobject",
      "id": "9c07a34c-7b08-4bfc-a1fb-5b108f6e1cf0"
    },
    "fields": [
      {
        "id": "68ef7fe1-9ce2-429a-bfbc-997cdc494787",
        "name": "UID",
        "schemaName": "CustomExternalObject",
        "label": "UID",
        "description": null,
        "fieldType": "id",
        "mapping": "uid",
        "referenceSchemaName": null,
        "referenceSchemaFieldName": null,
        "required": true,
        "upsertKey": true,
        "accessMode": "read_only",
        "readOnly": false,
        "maxLength": null,
        "precision": null,
        "scale": null
      },
      {
        "id": "701cfcaa-c277-420f-8bfd-c4b4c18f6b5f",
        "name": "CreatedDate",
        "schemaName": "CustomExternalObject",
        "label": "Created Date",
        "description": null,
        "fieldType": "datetime",
        "mapping": "created_date",
        "referenceSchemaName": null,
        "referenceSchemaFieldName": null,
        "required": true,
        "upsertKey": false,
        "accessMode": "read_only",
        "readOnly": false,
        "maxLength": null,
        "precision": null,
        "scale": null
      },
      {
        "id": "96a69056-a923-4f3e-819f-cc09aadb074b",
        "name": "CreatedBy",
        "schemaName": "CustomExternalObject",
        "label": "Created By",
        "description": null,
        "fieldType": "reference",
        "mapping": "created_by",
        "referenceSchemaName": "Users",
        "referenceSchemaFieldName": null,
        "required": true,
        "upsertKey": false,
        "accessMode": "read_only",
        "readOnly": false,
        "maxLength": null,
        "precision": null,
        "scale": null
      },
      {
        "id": "0420b6d0-e062-43f4-a835-128c4b1f7e2e",
        "name": "LastModifiedDate",
        "schemaName": "CustomExternalObject",
        "label": "Last Modified Date",
        "description": null,
        "fieldType": "datetime",
        "mapping": "last_modified_date",
        "referenceSchemaName": null,
        "referenceSchemaFieldName": null,
        "required": true,
        "upsertKey": false,
        "accessMode": "read_only",
        "readOnly": false,
        "maxLength": null,
        "precision": null,
        "scale": null
      },
      {
        "id": "c741a3a7-ac0e-44ff-9082-f362d6ab94ae",
        "name": "LastModifiedBy",
        "schemaName": "CustomExternalObject",
        "label": "Last Modified By",
        "description": null,
        "fieldType": "reference",
        "mapping": "last_modified_by",
        "referenceSchemaName": "Users",
        "referenceSchemaFieldName": null,
        "required": true,
        "upsertKey": false,
        "accessMode": "read_only",
        "readOnly": false,
        "maxLength": null,
        "precision": null,
        "scale": null
      },
      {
        "id": "49c4a0e8-f177-482f-a795-cfa561a7a4e5",
        "name": "Name",
        "schemaName": "CustomExternalObject",
        "label": null,
        "description": null,
        "fieldType": "string",
        "mapping": "__name",
        "referenceSchemaName": null,
        "referenceSchemaFieldName": null,
        "required": false,
        "upsertKey": false,
        "accessMode": "read_write",
        "readOnly": false,
        "maxLength": 255,
        "precision": null,
        "scale": null
      },
      {
        "id": "ae9d248d-12be-483c-a14a-44816b894976",
        "name": "ExternalId",
        "schemaName": "CustomExternalObject",
        "label": null,
        "description": null,
        "fieldType": "string",
        "mapping": "__externalid",
        "referenceSchemaName": null,
        "referenceSchemaFieldName": null,
        "required": false,
        "upsertKey": true,
        "accessMode": "read_write",
        "readOnly": false,
        "maxLength": 255,
        "precision": null,
        "scale": null
      }
    ]
  }
}

Track the new custom object by its id returned above. This maps the custom field to the object:

You can do this with the following cURL command:

curl -X POST -H "Authorization: Bearer $AUTH_TOKEN" "https://api.skedulo.com/custom/standalone/schema/<object id>/track"

Or by making a POST request with an empty body to: https://api.skedulo.com/custom/standalone/schema/<object id>/track

Be sure to replace <object id> with the ID of your object, which was returned when you created it in step 1.

(Optional) Add a webhook for the custom object. A webhook allows you to observe changes to field values. A webhook is not a requirement for using upserts; one is being used here for demonstration purposes only.

3a. Start a HTTP server and connect to it (this example uses ngrok). See the Webhooks chapter for instructions.

3b. Create the following file called webhook.js to track the custom object and its custom fields:

const url = "https://c3f43fbb.ngrok.io"

const json = {
  name: "Upsert custom object",
  url: url,
  type: "graphql",
  query: `
    subscription {
      schemaCustomExternalObject {
        operation
        timestamp
        data {
          UID
          Name
          ExternalId
          CreatedDate 
          LastModifiedDate
        }
        previous {
          UID 
          Name 
          ExternalId 
          CreatedDate
          LastModifiedDate
        }
      }
    }
  `
}

console.log(JSON.stringify(json, null, 2))

3c. Convert the webhook.js file to JSON:

$ node webhook.js > temp.json

3d. Create a webhook using the following request:

curl -s -X POST -H "Authorization: Bearer $AUTH_TOKEN"  -H "Content-Type: application/json" -d @temp.json 'https://api.skedulo.com/webhooks' | jq

Learn more about webhooks in the Integration section.

Insert a new object using `upsert`

Use GraphQL to insert a new CustomExternalObject. The ExternalId field value can be any value, and can be used to add an ID from your own environment that relates to a record in another system.

For example, if you have a case number associated with a client in another system, such as an ordering system or healthcare provider, you can use this identifier in this field when creating a record for the same client.

mutation upsertNewClient {
	schema {
		insertCustomExternalObject(input: {
			Name: "New Upsert"
			ExternalId: "ClientCaseNumber_ID01"
		})
	}
}

The webhook response shows that a new object was created with ClientCaseNumber_ID01 as the ExternalId value:

Webhook response

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "New Upsert",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:10:05.426Z"
          },
          "previous": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "New Upsert",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:10:05.426Z"
          },
          "operation": "INSERT",
          "timestamp": "2019-08-07T06:10:05.426Z"
        }
      }
    }
  ]
}

Note that both the data and previous blocks are identical, as no changes or modifications have been made to the object yet

Use `upsert` to update an existing object or create a new one

The following upsert GraphQL mutation is the same as the insert operation in the previous section however, rather than create a new object, the upsert will search for an existing object based on the value in the ExternalId key field, and update the Name field:

mutation upsertNewClientUpsert {
	schema {
		upsertCustomExternalObject(input: {
			Name: "Client Case Test"
			ExternalId: "ClientCaseNumber_ID01"
		}, keyField: "ExternalId")
	}
}

The first part of the webhook response shows the Name field has been updated while the second part shows the previous values:

Webhook response

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:16:39.861Z"
          },
          "previous": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "New Upsert",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:10:05.426Z"
          },
          "operation": "UPDATE",
          "timestamp": "2019-08-07T06:16:39.861Z"
        }
      }
    }
  ]
}

Now run the same query as above, but change the ExternalId field value to ClientCaseNumber_ID02:

mutation upsertNewClientUpsert {
	schema {
		upsertCustomExternalObject(input: {
			Name: "Client Case Test"
			ExternalId: "ClientCaseNumber_ID02"
		}, keyField: "ExternalId")
	}
}

You can see from the webhook output, or from performing a GraphQL query that this has created a new CustomExternalObject object with Name ("Client Case Test"), but with the new ExternalId value:

Webhook output

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T06:38:42.644Z"
          },
          "previous": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T06:38:42.644Z"
          },
          "operation": "INSERT",
          "timestamp": "2019-08-07T06:38:42.644Z"
        }
      }
    }
  ]
}

The following GraphQL query on the Name field that we used for both objects returns two objects and two insert events:

query upsertClientQuery {
	customExternalObject(filter: "Name == \"Client Case Test\"") {
		edges {
			node {
				UID
				Name
				ExternalId
				CreatedDate
				LastModifiedDate
			}
		}
	}
}

Returns:

{
  "data": {
    "customExternalObject": {
      "edges": [
        {
          "node": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T06:16:39.861Z"
          }
        },
        {
          "node": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Client Case Test",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T06:38:42.644Z"
          }
        }
      ]
    }
  }
}

Use `idAlias` to make multiple changes using upserts

You can use an upsert mutation to make changes to multiple objects in a single query using aliases.

mutation upsertWithAliases {
  schema {
    id1: upsertCustomExternalObject(input: {
      Name: "Alias test 01"
      ExternalId: "ClientCaseNumber_ID01"
      }, keyField: "ExternalId", idAlias: "ID01")
    id2: upsertCustomExternalObject(input: {
      Name: "Alias test 02"
      ExternalId: "ClientCaseNumber_ID02"
      }, keyField: "ExternalId", idAlias: "ID02")
    contactId1: insertContacts(input: {
      LastName: "Smith"
      CustomExternalObjId: "ID01"
      })
    contactId2: insertContacts(input: {
      LastName: "Jones"
      CustomExternalObjId: "ID02"
      })
    }
  }

The webhook response shows that both objects were updated successfully:

Webhook response

{
  "headers": {
    ...
  },
  "body": [
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Alias test 01",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T07:14:10.493Z"
          },
          "previous": {
            "UID": "03ec7f8a-8ac0-4fb7-86c6-5759884721dd",
            "Name": "Alias test 01",
            "ExternalId": "ClientCaseNumber_ID01",
            "CreatedDate": "2019-08-07T06:10:05.426Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z"
          },
          "operation": "UPDATE",
          "timestamp": "2019-08-07T07:14:10.493Z"
        }
      }
    },
    {
      "data": {
        "schemaCustomExternalObject": {
          "data": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Alias test 02",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T07:14:10.493Z"
          },
          "previous": {
            "UID": "03ecfbba-98de-4ef7-bf87-b3906fd7b302",
            "Name": "Alias test 02",
            "ExternalId": "ClientCaseNumber_ID02",
            "CreatedDate": "2019-08-07T06:38:42.644Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z"
          },
          "operation": "UPDATE",
          "timestamp": "2019-08-07T07:14:10.493Z"
        }
      }
    }
  ]
}

Run the following query that uses an EQL filter to return entries based on the LastName field values Smith and Jones that we just upserted to the two client cases:

query upsertClientQuery {
  contacts (filter: "LastName == \"Smith\" OR LastName == \"Jones\"" ){
    edges {
      node {
        UID
        CustomExternalObj {
          ExternalId 
        }
        CreatedDate
        LastModifiedDate
        LastName
      }
    }
  }
}

This returns the objects with the information we requested, and validates that the LastName fields have been updated successfully:


{
  "data": {
    "contacts": {
      "edges": [
        {
          "node": {
            "UID": "0004e60e-6ec5-4872-91ad-9f11db1ed21d",
            "CustomExternalObj": {
              "ExternalId": "ClientCaseNumber_ID01"
            },
            "CreatedDate": "2019-08-07T07:03:32.704Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z",
            "LastName": "Smith"
          }
        },
        {
          "node": {
            "UID": "00049259-395e-41ea-b685-acbf3f54540a",
            "CustomExternalObj": {
              "ExternalId": "ClientCaseNumber_ID02"
            },
            "CreatedDate": "2019-08-07T07:03:32.704Z",
            "LastModifiedDate": "2019-08-07T07:03:32.704Z",
            "LastName": "Jones"
          }
        },
      ]
    }
  }
}

For more information about using aliases for GraphQL mutations and queries, see Perform multiple actions using GraphQL aliases chapter.

Feedback

Was this page helpful?

On this page

Upsert records with GraphQL

Create a custom field in Skedulo

Insert a new object using upsert

Use upsert to update an existing object or create a new one

Use idAlias to make multiple changes using upserts

Feedback

Insert a new object using `upsert`

Use `upsert` to update an existing object or create a new one

Use `idAlias` to make multiple changes using upserts