SQL

Xata uses PostgreSQL under the hood to store your data. You can use SQL to work with your data. The results come back in a typical Xata JSON response mechanism over HTTP. If the Xata API doesn't cover the query you need, you can use SQL directly to do queries, insert data, update information, and delete records.

Xata offers support for a subset of PostgreSQL functions. This includes functions such as subquery, comparison, aggregate, window, range, json, string, datetime, and array functions. Functions beyond these are not permitted.

Database tables are stored in PostgreSQL as tables. Xata data types are mapped to PostgreSQL types with additional constraints:

In PostgreSQL tables are used to store and organize data. Linked columns serve as foreign keys, establishing connections to other tables. To access data from a linked column, use JOIN operations or apply WHERE conditions to locate the related records.

For example, the following query shows how to retrieve email addresses and corresponding cities using a LEFT JOIN operation between the "Users" and "address" tables.:

SELECT "Users".email, address.city FROM "Users"
LEFT JOIN address ON "Users".id=address.id;

For specific Xata data types like email, it is required you use the Xata API or SDK. This ensures that Xata can validate the inserted data's accuracy. We recommend refraining from directly writing to columns of this type and instead using the provided API for better results.

Note when you are working with names that include upper-case letters like Users or zipCode, you must quote those otherwise PostgreSQL treats it as a lowercase name.

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

• This URL represents the endpoint for making SQL queries within the Xata platform. It's intended to enable database interaction within a designated Workspace.

Method: POST

• The HTTP method used to interact with the provided URL is POST. This means you'll be sending data to the server, specifically your SQL statement and any additional parameters.

Parameters:

• statement: Your SQL statement (required).
• params: A list of parameters when submitting a parameterized statement.
• consistency: This parameter refers to the level of consistency when executing a statement. It offers options such as strong and eventual. The default setting is strong.

The following are instances of working with data, including retrieving, selecting, inserting, and deleting records, using SQL in the Xata platform.

const { records } = await xata.sql<TeamsRecord>`SELECT * FROM teams`;

records = xata.sql().query("SELECT * FROM \"teams\"")

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
 
{
  "statement": "SELECT * FROM \"teams\""
}

The following response is returned:

{
  "records": [
    {
      "id": "rec_c8hng2h26un90p8sr7k0",
      "name": "Matrix",
      "owner": {
        "id": "myid"
      },
      "xata": {
        "version": 0,
        "createdAt": "2023-05-15T08:21:31.96526+01:00",
        "updatedAt": "2023-05-15T21:58:54.072595+01:00"
      }
    }
  ]
}

const { records } = await xata.sql<TeamsRecord>`SELECT name, city FROM "Users"`;

records = xata.sql().query("SELECT name, city FROM \"Users\"")

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
 
{
  "statement": "SELECT name, city FROM \"Users\""
}

Response:

{
  "records": [
    {
      "city": "New York",
      "name": "Keanu Reaves"
    }
  ]
}

const user = { name: "Keanu Reeves", email: "keanu@example.com" };
const { records: [record] } = await xata.sql<TeamsRecord>`INSERT INTO "Users" (name, email) VALUES (${user.name}, ${user.email})`;

records = xata.sql().query(
  "INSERT INTO \"Users\" (name, email) VALUES ($1, $2)",
  ["Keanu Reeves", "keanu@example.com"]
)

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
 
{
"statement": "INSERT INTO \"Users\" (name, email) VALUES ($1, $2)",
"params": ["Keanu Reeves", "keanu@example.com"]
}
 

const user = { id: "my-user-id" };
await xata.sql<TeamsRecord>`DELETE FROM "Users" WHERE id=${user.id})`;

xata.sql().query(
  "DELETE FROM \"Users\" WHERE id=$1",
  ["my-record-id"]
)

// POST https://{workspace}.{region}.xata.sh/db/{db}:{branch}/sql
 
{
  "statement": "DELETE FROM  \"Users\" WHERE id=$1",
  "params": ["my-record-id"]
}

When using SQL in Xata, there are a few constraints to keep in mind.

• The xata.* internal fields are read-only. You cannot modify these fields.
• You can only submit one statement per request.
• Xata returns 1000 records at a time. Access your data using LIMIT and OFFSET to iterate and work through your data.
• JSON formatter functions do not work as the endpoint already returns a JSON response.
• The work_mem setting in PostgreSQL is set to 4 MB. so a query can use only 4 MB of memory (before writing to temp files)

• In PostgreSQL, the work_mem configuration is set to 4 MB. A query is limited to 4 MB of memory and this limit is enforced before the data is saved into temporary files. The work_mem configuartion sets the baseline maximum memory for query operations like sorting or using hash tables before writing to temporary disk file.
• The hash_mem_multiplier is set to 1. The hash_mem_multiplier computes the maximum memory for hash-based operations by multiplying it by work_mem. For additional information on resource consumption in Postgres, see the PostgreSQL docs.

Columns with matching names overwrite each other by default. To address this, especially when dealing with multiple columns that have the same name, you can use column aliasing as a solution. For instance:

SELECT address AS physical_address, address AS email
FROM people, emails
WHERE people.id=emails.id;

By assigning aliases, you can distinguish between columns with similar names and manage them effectively.

Certain commands are not supported in Xata:

• Data definition language (DDL) commands such as CREATE, DROP, ALTER and TRUNCATE are not supported.

• Data control language (DCL) commands such as GRANT and REVOKE are not supported.

• Transaction control language (TCL) commands such as COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT, and SET TRANSACTION are not supported.