Updating Records in a Xata Database

You can update a record with optimistic locking by overwriting it with a PUT request on the .../data/{record_id} endpoint. For example, to add the address fields and the team field to the "Keanu Reeves" record inserted at the beginning of this tutorial, you can send the following request as a JSON body to the endpoint at the top of the code snippet.

// PUT https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/data/rec_c8hnbch26un1nl0rthkg

{
  "email": "keanu@example.com",
  "full_name": "Keanu Reeves",
  "address": {
    "street": "123 Main St",
    "zipcode": 12345
  },
  "team": "rec_c8hng2h26un90p8sr7k0"
}

This returns a JSON object like this:

{
  "id": "rec_c8hnbch26un1nl0rthkg",
  "xata": {
    "version": 1
  }
}

Note that in the above example, the whole record was overwritten. If you want to update only some of the fields, see the Partial Update section below. Also, the xata.version field was automatically incremented to 1. We'll talk more about this special column in the Optimistic Concurrency Control section.

Partial Update

In order to do a partial update to a record, you can can replace the PUT method with the PATCH method. For example, the below example only updates the address.street column and leaves the rest of the columns untouched:

// PATCH https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/data/rec_c8hnbch26un1nl0rthkg

{
  "address": {
    "street": "123 New St"
  }
}

If you do a GET request now, you should see something similar to this:

// GET https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/data/rec_c8hnbch26un1nl0rthkg

{
  "address": {
    "street": "123 New St",
    "zipcode": 12345
  },
  "email": "keanu@example.com",
  "full_name": "Keanu Reeves",
  "id": "rec_c8hnbch26un1nl0rthkg",
  "xata": {
    "version": 2
  }
}

Optimistic Concurrency Control

The xata.version field can be used to perform optimistic concurrency control, also known as "optimistic locking". Let's say you have two users of your web applications that are both trying to update the same record. They both retrieve the record at version 0, modify a field, and then write it back. Without locking or version checks, it's possible that the users will overwrite each other's changes.

To prevent this, you can pass the ifVersion parameter to the PUT request. The value for ifVersion should be the value of xata.version from when you retrieved the record. For example, if you want to update the record only if the version is 0, you can send the following request:

// PUT https://tutorial-ng7s8c.xata.sh/db/tutorial:main/tables/users/data/rec_c8hnbch26un1nl0rthkg?ifVersion=0

{
  "email": "keanu@example.com",
  "full_name": "Keanu Reeves"
}

If someone else has updated the record since you have read it, you will get a 422 Unprocessable Entity response:

{
  "message": "version condition not met",
  "status": 422
}

The way to treat this error depends on your application. You might be able to re-read the record, resolve the conflict, and then re-try the update. Or you might need to show the user a conflict error message.

Next Steps

Great! We can now update data safely in our databases. Let's now explore how we delete data from a database. Alternatively, we can also look into updating data or inserting data. We've got guides for each of these operations.


Last modified 1 mo1 day ago