Deleting records

To delete a record you can execute, for example:

const user = await xata.db.Users.delete('rec_cd8s3r8avc42pi67m13g');

// or, using the delete method on the record object:
user.delete();

user = xata.records().delete("Users", "rec_cd8s3r8avc42pi67m13g")

client, _ := xata.NewRecordsClient()
response = client.Delete(context.TODO(), xata.DeleteRecordRequest{
  RecordRequest: xata.RecordRequest{
    DatabaseName: xata.String("{db}"),
    TableName:    "Users",
  },
  RecordID: "rec_cd8s3r8avc42pi67m13g",
})

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql

{
  "statement": "DELETE FROM \"Users\" WHERE id = $1;",
  "params": ["rec_cd8s3r8avc42pi67m13g"]
}

// DELETE https://{workspace}.{region}.xata.sh/db/{db}:{branch}/tables/{table}/data/{record_id}

In case the record with the given ID doesn't exist, the REST API returns a 404 and the TypeScript SDK throws an exception.

Transactions are used when deleting multiple records:

const users = await xata.db.Users.delete(['rec_cd8s3r8avc42pi67m13g', 'rec_cgh9o1oncchhigq95n2g']);

users = xata.records().transaction({
  "operations": [
    {
      "delete": {
        "table": "Users",
        "id": "rec_cd8s3r8avc42pi67m13g"
      }
    },
    {
      "delete": {
        "table": "Users",
        "id": "rec_cgh9o1oncchhigq95n2g"
      }
    }
  ]
})

client, _ := xata.NewRecordsClient()
users, _ := client.Transaction(context.TODO(), xata.TransactionRequest{
  RecordRequest: xata.RecordRequest{
    DatabaseName: xata.String("{db}"),
  },
  Operations: []xata.TransactionOperation{
    xata.NewDeleteTransaction(xata.TransactionDeleteOp{
      Table: "Users",
      Id:    "rec_cd8s3r8avc42pi67m13g",
    }),
    xata.NewDeleteTransaction(xata.TransactionDeleteOp{
      Table: "Users",
      Id:    "rec_cgh9o1oncchhigq95n2g",
    }),
  },
})

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql

{
  "statement": "DELETE FROM \"Users\" WHERE id IN ($1,$2);",
  "params": ["rec_cd8s3r8avc42pi67m13g", "rec_cgh9o1oncchhigq95n2g"]
}

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/transaction
{
  "operations": [
    {
      "delete": {
        "table": "Users",
        "id": "rec_cd8s3r8avc42pi67m13g"
      }
    },
    {
      "delete": {
        "table": "Users",
        "id": "rec_cgh9o1oncchhigq95n2g"
      }
    }
  ]
}

To delete multiple records from the API, we recommend using transactions. Transactions will not fail a delete operation if a record is not found. All operations in a transaction must succeed otherwise it is rolled back.

Alternatively, you can use the DELETE statement via the SQL proxy, like in the following example:

When dealing with a large number of records, there is a 60-second timeout. To avoid the SQL proxy's timeout, delete in small batches. We recommend limiting each deletion batch to a maximum of 10,000 records.

DELETE FROM "titles"
WHERE id IN (
    SELECT id
    FROM titles
    WHERE "originalTitle" ILIKE '%Matrix%'
    ORDER BY "xata.createdAt" ASC
    LIMIT 10000
);

SELECT id: Specifies that only the id column will be retrieved in the query.
FROM titles: The subquery operates on the titles table.
WHERE "originalTitle" ILIKE '%Matrix%': Filters rows based on a case-insensitive partial match in the originalTitle column.
LIMIT 10000: Restricts the number of results to a maximum of 10,000 rows.

Next Steps

Now that you have seen how to do the basic create, remove, update, and delete operations, you can check out the docs on using the free text search API, which is a new super power that Xata gives you.

You can also review the pages on getting data, updating data, inserting data. We've got guides for each of these operations.