New beta: REST API Upserts

[Eileen_Xing]

Hi everyone! We’re excited to announce one of our newest features - REST API Upserts.

You can now add an upsert option performUpsert to the Update records API endpoint, so that if a matching record is not found, it will create the record instead.

More details about this new feature can be found in the API documentation, under the Update records section.

If you have any feedback about this feature, please submit via this form.

Example request:

curl -X PATCH https://api.airtable.com/v0/[base ID]/[table name or ID] \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  --data '{
  "performUpsert": {
    "fieldsToMergeOn": [
      "Name"
    ]
  },
  "records": [
    {
      "fields": {
        "Name": "Kick off design work",
        "Status": "In progress"
      }
    },
    {
      "fields": {
        "Name": "New homepage",
        "Status": "To do"      
      }
    }
  ]
}'

Example response:

{
    "records": [
        {
            "id": [record ID 1],
            "createdTime": "2022-10-19T16:52:15.000Z",
            "fields": {
                "Name": "Kick off design work",
                "Status": "In progress"
            }
        },
        {
            "id": [record ID 2],
            "createdTime": "2022-10-17T21:31:06.000Z",
            "fields": {
                "Name": "New homepage",
                "Status": "To do"
            }
        }
    ],
    "updatedRecords": [[record ID 2]],
    "createdRecords": [[record ID 1]]
}

[kuovonne]

ScottWorld:

So this new upsert command can only update ONE record, even if multiple records match on the composite key?

If multiple records match the composite key, the request will throw an error.

ScottWorld:

In your example API response above, it uses the plural words “updatedRecords” & “createdRecords”, indicating that multiple records can both be updated & created. I don’t see how multiple records can be created, but I could see how multiple records can be updated if multiple records match on the criteria.

Having multiple records created/updated is unrelated to the new upsert feature. You can update up to 10 records at a time in one REST API call, but all 10 records are different records. All ten records should either have a record ID, or a unique set of values for the composite key.

[joseglego]

Is this working on production or should I request access to the beta? I've tried this following the documentation (https://airtable.com/${MyBaseId}/api/docs#curl/table:${TableName}:update)

My request looks like this:

  const url = `${process.env.AIRTABLE_API_URL}/v0/${baseId}/${viewName}`
  try {
    const { data } = await axios({
      method: 'patch',
      url,
      headers: {
        'Authorization': `Bearer ${process.env.AIRTABLE_API_KEY}`,
        'Content-Type': 'application/json'
      },
      data : {
        records,
        "performUpsert": {
          "fieldsToMergeOn": [
            fieldName
          ]
        }
      }
    })
  }

My request answer is: `'Request failed with status code 422',` (If I don't include the performUpsert the request works)

Also, I have a second question, can I use fields that are "link to another record"?

[Hunterhod]

Getting '422' response when I attempt to use this. Do I need to be a part of a private beta?

[Martin_Edwards]

I don't really understand how this can work properly.

What if you're changing a field value that is in the fieldsToMergeOn? Then it won't find the right record.

When can you guarantee a non-calculated field is going to be unique? All the field types mentioned could easily not be unique between rows: number, text, long text, single select, multiple select, date.

As soon as two rows have one of those fields the same, the API will fail. E.g. if you have a users table and two people had the same name.

Not sure how usable this is. I can only use this myself becasue we have a field that's stringified JSON that will always be unique.