SQL over HTTP

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.

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' };
await xata.sql`INSERT INTO "Users" (name, email) VALUES (${user.name}, ${user.email})`;

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

// 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-record-id' };
await xata.sql`DELETE FROM "Users" WHERE id=${user.id}`;

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

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

{
  "statement": "DELETE FROM  \"Users\" WHERE id=$1",
  "params": ["my-record-id"]
}

const response = await xata.sql<UsersRecord>({
  statement: `SELECT * FROM "Users" LIMIT 10;`,
  consistency: 'eventual'
});

xata.sql().query(
  statement = "SELECT * FROM \"Users\" LIMIT 10;",
  consistency = "eventual"
)

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

{
  "statement": "SELECT * FROM \"Users\" LIMIT 10;",
  "consistency": "eventual"
}

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 configuration 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.

Xata supports the following essential Data Manipulation Language (DML) SQL commands, which are fundamental for managing and manipulating data within database tables:

• SELECT: This command is used to retrieve data from a database. It allows you to select one or more columns of a table, with various options for filtering, sorting, and grouping the data.
• INSERT: This command is used to add new records (rows) to a table. It specifies the table to insert into and the values for the new row.
• DELETE: This command removes existing records from a table. It can be used with conditions to specify which records should be deleted.
• UPDATE: This command modifies existing records in a table such as correcting errors or updating information. Like DELETE, it can be used with conditions to specify which records should be updated and how.

Please refer to the supported commands table to get a full break down.

The following table provides examples of commands that are currently not supported in Xata:

SQL access in Xata is achieved via an HTTP or HTTPS proxy. Tools that use a wire protocol with a connection string to PostgreSQL are not compatible with Xata's SQL proxy.

You can use SQL in Xata with any of the following methods:

• Use the built-in PostgreSQL Playground in the Xata Web UI.
• Execute API calls to your branch's /sql endpoint using web clients such as Postman.
• Use the SQL methods provided in the Typescript and Python SDKs.
• Use the Kysely and Drizzle integrations.