What is Xata?
Command line interface
Dedicated clusters
Migrations
PR based workflow
beta
Usage metrics
beta
Installation & API keys
Getting records
Creating records
Updating records
Deleting records
Filtering records
Transactions
Searching records
Vector searching records
beta
Aggregations
beta
Summarize
Ask AI
File attachments
Image transformations
CSV
Airbyte
beta
Auth JS
Cloudflare Workers
GitHub
LangChain
Netlify
OAuth 2
alpha
Vercel
Zapier
Available regions
Pricing
Service level agreement
Limitations
Branch inactivity
Support
Docs contribution guide

Transactions

Edit on GitHub

The single-call transaction endpoint allows you to execute multiple operations together as a single call. In this guide, we'll run through why transactions are important and run through Xata's single-call transaction endpoint.

#

Why transactions are important

Reliability is important when it comes to your data. You want to know that when you ask for something to be stored, that it'll be stored or that you'll receive an error explaining why not. No half-written records, no returning "OK" but not writing it, no writing it and then removing it again.

As you add new features to your application, you'll see some patterns emerge. You want to create a user and a workspace at the same time. Or you'll want to remove a user from the wait-list when they sign-up. Your users will also hope that when they make a payment to your product, that their credits show up.

Transactions are in the business of making these promises. A transaction lets you - the developer - run multiple requests as one. These requests are all guaranteed to succeed, or all guaranteed to fail. The database guarantees that a user and workspace will be created, that the user is removed from the wait-list, and it absolutely guarantees that your users' credits will show up in their account upon payment.

#

Xata's approach

Transactions come with a lot of knobs and dials. Transactions give control over how operations are executed, as well as what is and is not allowed to happen in a database while the transactions is executing.

Xata's role is to help you build and iterate on your product faster, so we've started off with features to help you make use of transactions, but without you needing to dive into the nitty gritty.

The single-call transaction endpoint enables you to run a number of sequential operations under a single API call. In case any of the operations fails, all operations in the single-call transaction are aborted and their changes are rolled back.

The number of operations allowed in a single-call transaction is limited by the total number of operations that can be run at once. It is not possible to chain multiple single-call transactions to logically form a larger one. Each single-call transaction request runs atomically and either succeeds or fails in its entirety.

#

API

The Xata single-call transaction API can be thought of as a way to wrap our existing insert, update, and delete operations into a single operation. The options for each operation are almost identical to their non-transactional counterparts.

We'll start by taking a look at a full request-response, and then we'll step into each operation and its options.

const result = await xata.transactions.run([
  { insert: { table: 'titles', record: { originalTitle: 'A new film' } } },
  { insert: { table: 'titles', record: { id: 'new-0', originalTitle: 'The squeel' }, createOnly: true } },
  { update: { table: 'titles', id: 'new-0', fields: { originalTitle: 'The sequel' }, ifVersion: 0 } },
  { update: { table: 'titles', id: 'new-1', fields: { originalTitle: 'The third' }, upsert: true } },
  { get: { table: 'titles', id: 'new-0', columns: ['id', 'originalTitle'] } },
  { delete: { table: 'titles', id: 'new-0' } }
]);

If successful, you can be certain that all operations have succeeded. You will receive a response like below:

{
  "results": [
    { "operation": "insert", "rows": 1, "id": "rec-123456789" },
    { "operation": "insert", "rows": 1, "id": "new-0" },
    { "operation": "update", "rows": 1, "id": "new-0" },
    { "operation": "update", "rows": 1, "id": "new-1" },
    { "operation": "get", "columns": { "id": "new-0", "originalTitle": "The sequel" } },
    { "operation": "delete", "rows": 1 }
  ]
}

Or, in case of error, you know that all operations have been rolled back for you. You will receive a response like below:

{
  "errors": [
    { "index": 0, "message": "table [invalid] not found" },
    { "index": 7, "message": "table [titles]: column [x]: column not found" }
  ]
}

In order to access the errors array returned from the Typescript SDK you can handle errors with a try catch statement:

import { FetcherError } from '@xata.io/client';

try {
    const result = await xata.transactions.run([{...operations...}]);
} catch (error: FetcherError) {
    console.log(error.status);
    console.log(error.errors);
}

#

inserts

The insert operation allows you to add new records to any table in your database. You have the option to either set an explicit ID for each record or allow Xata to auto-generate one. If successful, Xata returns the ID of the inserted record.

By default, inserting a record with an existing ID will overwrite the old record. You can change this default behavior by setting createOnly to true. This ensures that the operation only creates new records and does not overwrite existing ones.

const result = await xata.transactions.run([
  {
    insert: {
      table: 'titles',
      record: { id: 'rec_cfl4g6v838og05h0iv6g', originalTitle: 'A new film' },
      createOnly: true
    }
  }
]);

#

ifVersion

The ifVersion parameter is used to control updates based on the version of the record. It prevents updates to a record unless the version matches the specified ifVersion. You can use the ifVersion flag to avoid overwriting unexpected versions of the record.

const result = await xata.transactions.run([
  {
    insert: {
      table: 'titles',
      record: { id: 'rec_cfl4g6v838og05h0iv6g', originalTitle: 'An updated title' },
      ifVersion: 0
    }
  }
]);

If an insert fails for any reason, such as invalid data, an unknown table, or ifVersion conflicts, the transaction will return an error.

#

update

Use update to modify records across any number of tables in your database. For an update operation to work, you must explicitly define an ID parameter. This operation only updates the fields you have specifically mentioned.

Additionally, the update operation includes an upsert option, which is disabled by default. If enabled, set to true, and no existing record matches the provided ID, the operation will insert a new record instead.

As with insert operations, update supports ifVersion.

Update operations can be used on operations from the same transaction, and will return an error if the ID is not found in the database.

For performing multiple updates in bulk, you can use the following code snippet:

const result = await xata.transactions.run([
  {
    update: {
      table: 'Users',
      id: 'rec_cmf56ugvjdkilkpcdeu0',
      fields: { email: 'neo@matrix.com', name: 'Neo' }
    }
  },
  {
    update: {
      table: 'Users',
      id: 'rec_cmf572veoo9ahn3r96p0',
      fields: { email: 'trinity@matrix.com', name: 'Trinity' }
    }
  }
  // You can add additional transactions
]);

#

delete

Use delete to remove multiple records. Delete can operate on records from the same transaction, and will not cancel a transaction if no record is found.

const result = await xata.transactions.run([
  {
    delete: {
      table: 'titles',
      id: 'rec_cfl4g6v00023'
    }
  }, 
  {
    delete: {
      table: 'titles',
      id: 'rec_cfl4g6v00056'
    }
  },
  // You can add additional operations
]);

#

get

Use get to fetch multiple records using IDs. It can retrieve records generated within the same transaction. If no record is found, the operation does not result in a canceled transaction.

const result = await xata.transactions.run([
  {
    get: {
      table: 'titles',
      id: 'rec_cfl4g6v00023',
      columns: ['id', 'originalTitle']
    }
  },
  {
    get: {
      table: 'titles',
      id: 'rec_cfl4g6v00056',
      columns: ['id', 'originalTitle']
    }
  }
]);

#

Limitations

  • A maximum of 1000 operations are allowed in a single-call transaction. If you exceed this limit, you will receive an error message.
  • Inserting and updating content to file and file\[] (file array) columns is not permitted with transactions.

On this page

Why transactions are importantXata's approachAPIinsertsifVersionupdatedeletegetLimitations