Installation & API keys
Branching workflow
CLI
Available regions
Serverless data platform
How Xata works
Data model
User model
Schema
Data copying
Migration files
File attachments
Pricing
Getting records
Creating records
Updating records
Deleting records
Searching records
Vector searching records
Filtering records
Aggregations
Summarize
Transactions
SQL
Ask AI
File attachments
Image transformations
Overview
Authentication
API contexts
REST API limits
Airbyte
Auth JS
Drizzle
GitHub
Kysely
LangChain
Netlify
OAuth 2
Vercel
Visual Studio Code
Zapier
Create schema from code
Xata examples
Gallery app
Tupu.io
Xata ChatGPT
XMDb

Getting records

Edit on GitHub

The general form of a query is:

import { getXataClient } from "./xata";
 
const client = getXataClient();
const data = await client
.db[table]
.select([...])
.filter({ ... })
.sort({ ... })
.page({ ... })
.getMany();

For the REST API example, note that a POST request is used, even though the data is not modified, in order to be able to use a body for the request (GET requests with a body are non-standard).

All the requests are optional, so the simplest query request looks like this:

const teams = await xata.db.Teams.getMany();

The response looks like this:

[
  {
    "id": "rec_cd8s4kbo8dsvsjilo1ug",
    "name": "Matrix",
    "owner": {
      "id": "myid"
    }
  }
]

For the REST API example, note that the id, xata.version, xata.updatedAt and xata.createdAt are included in the returned records. We will discuss the meta.page object when we talk about paginating through the records.

#

The TypeScript SDK functions for querying

The getFirst() method returns the first record matching the query, in ascending order of the id string field. In case there are no matching records it returns null. Alternatively, the getFirstOrThrow() method can be used to throw a "No results found." console error in case there are no results to return.

The TypeScript SDK provides three methods for querying multiple records:

  • getPaginated(): returns a page of records in the query results. Note that the response type is different from getMany() and getAll().
const page = await xata.db.Posts.getPaginated({
  pagination: { size: 100, offset: 0 }
});
 
const firstPageRecords = page.records; // Items 1...100
 
const hasNextPage = page.hasNextPage();
const nextPage = await page.nextPage();
const nextPageRecords = nextPage.records; // Items 101...200
 
const hasAnotherNextPage = page.hasNextPage();
// Request different page size for this next call for items 101...110
const anotherPageWithDifferentSize = await page.nextPage(10);
const differentSizedPage = anotherPageWithDifferentSize.records;
  • getAll(): returns all the records in the query results. Warning: this is dangerous on large tables (more than 10,000 records), as it will potentially load a lot of data into memory and create a lot of requests to the server.
  • getMany(): returns the requested number of records for the query in a single response. The default is 20 records, but you can change it by modifying the pagination size.
const page = await xata.db.Posts.getMany({
  pagination: { size: 100 }
});

Both the getAll() and getMany() will produce multiple requests to the server if the query should return more than the maximum page size, combining all results in a single response. It's important to be aware of this because it can cause multiple round-trips to the server, which can affect the latency. The getPaginated() call always performs a single request, therefore it performs a single round-trip.

Unless you have a specific reason to prefer another method, we generally recommend getMany(), as the best balance between usability and performant defaults, and it is the method that we use in our examples.

#

Getting a record by ID

You can retrieve a record with a given ID using a request like this:

const user = await xata.db.Users.read('myid');

#

Columns Selection

By default, the Query API returns all columns of the queried table. For link columns, only the ID column of the linked records is included in the response. You can use column selection to both reduce the number of columns returned, and to include columns from linked tables.

For example, if you are only interested in the name and the city of the user, you can make a request like this:

const users = await xata.db.Users.select(['name', 'city']).getMany();

A sample response will look like this:

{
  "address": {
    "city": "New York"
  },
  "id": "myid",
  "name": "Keanu Reeves",
  "xata": {
    "version": 1,
    "createdAt": "2023-05-15T08:21:31.96526+01:00",
    "updatedAt": "2023-05-15T21:58:54.072595+01:00"
  }
}

It's worth noting that the special columns id, xata.version, xata.createdAt and xata.updatedAt are always returned, even if they are not explicitly requested.

#

Selecting Columns from the Linked Tables

The same syntax can be used to select columns from a linked table, therefore adding new columns to the response. For example, to query all the columns of the teams table and also add all the columns of the owner user, you can use:

const teams = await xata.db.Teams.select(['*', 'owner.*']).getMany();

You can do this transitively as well, for example:

const posts = await xata.db.Posts.select([
  "title",
  "author.*",
  "author.team.*",
]).getMany();

In this example, the author is a link column from Posts to Users and team is a link column from Users to Teams.

By default, when retrieving a record by its ID, only the ID column of linked records is included in the response. However, you have the option to request the linked columns to be expanded. In the following examples, the code is using the .read() method to retrieve a record with the ID myid from the Posts table. The code specifies it wants the author column to be expanded and to retrieve more information from the author linked record.

const user = await xata.db.Posts.read('myid', ['author.*']);

#

Filtering Records

This section contains a few examples of how to use filtering. To find all supported operators and examples for them, see the Filtering section of the API Guide.

To filter the results, use the filter function/parameter. For example:

const users = await xata.db.Users.filter({ email: 'keanu@example.com' }).getMany();

Will return only the records with the given email address.

You can also refer to a linked column in filters, for example:

const users = await xata.db.Users.filter({ 'team.name': 'Matrix' }).getMany();

Note that in the above, name is a column in the Teams table, and we can refer to it even when querying the Users table.

To give a more complex filtering example, consider the following:

const users = await xata.db.Users.filter({
  zipcode: { $gt: 100 },
  $any: [{ name: { $contains: 'Keanu' } }, { name: { $contains: 'Carrie' } }]
}).getMany();

Translating the above filter in English: filter all users with a zipcode greater than 100, and the full name contains either "Keanu" or "Carrie".

The example above demonstrates several operators:

  • $gt: which can be applied to number columns, and means "greater than".
  • $contains: which can be applied to string columns, and does a substring match.
  • $any: which can be used to create OR conditions. The record matches if any of the conditions enclosed are true.

To see the rest of the operators available, check out the Filtering guide.

#

Sorting the Results

To sort the results, use the sort function/parameter. For example:

const users = await xata.db.Users.sort('name', 'asc').getMany();

To sort descending, use desc instead of asc:

const users = await xata.db.Users.sort('name', 'desc').getMany();

It is also possible to have secondary sort criteria. For example:

const users = await xata.db.Users.sort('city', 'desc').sort('name', 'asc').getMany();

To sort results in random order, use random instead of desc or asc:

const users = await xata.db.Users.sort('*', 'random').getMany();

Note that random sorting does not apply to a specific column, hence the special column name "*".

#

Paginating Results

This section doesn't apply to the TypeScript SDK, because it is offering a higher-level abstraction over the pagination mechanism. See the query functions section. Only REST API examples are provided in this section.

Xata offers two types of pagination:

  • cursor-based, using after and before parameters. It is optimal for building next / prev navigation patterns.
  • offset-based, using offset and limit parameters. It is optimal for building 1..2..10...19..20 navigation patterns.

#

Cursor-based Pagination

The depth of cursor-based pagination is unrestricted. When running a query, you can specify a particular page size.

For example:

const page = await xata.db.Users.getPaginated({
  pagination: { size: 2 }
});

Returns only the first two records:

{
  "records": [
    {
      "address": {
        "street": "123 Main St",
        "zipcode": 12345
      },
      "email": "keanu@example.com",
      "full_name": "Keanu Reeves",
      "id": "rec_c8hnbch26un1nl0rthkg",
      "team": {
        "id": "rec_c8hng2h26un90p8sr7k0"
      },
      "xata": {
        "version": 2,
        "createdAt": "2023-05-15T08:21:31.96526+01:00",
        "updatedAt": "2023-05-15T21:58:54.072595+01:00"
      }
    },
    {
      "address": null,
      "email": "laurence@example.com",
      "full_name": "Laurence Fishburne",
      "id": "rec_c8hnnh126unff00ifhhg",
      "team": {
        "id": "rec_c8hng2h26un90p8sr7k0"
      },
      "xata": {
        "version": 0,
        "createdAt": "2023-05-15T08:21:31.96526+01:00",
        "updatedAt": "2023-05-15T21:58:54.072595+01:00"
      }
    }
  ],
  "meta": {
    "page": {
      "cursor": "VMoxDsIwDAXQnWP8OUPSASFfAnZUoZDWtSEEyTFT1bujion9PdKK_",
      "more": true
    }
  }
}

In this case, notice that the meta.page objects contains "more": true. This is an indication that there are more records available. The "cursor" key is a pointer to the current page. To retrieve the next page of results, you can make a request like this:

const page1 = await xata.db.Users.getPaginated({
  pagination: { size: 2 }
});
 
const page2 = await page1.nextPage();

You can continue like this until "more" is returned as false.

#

Offset-based Pagination

Offset-based pagination lets you request specific pages of data using the offset parameter (offset), relative to a specified page size (size). While this offers flexibility, it requires knowing the total record count, which can affect performance. As a safeguard, Xata limits offset-based pagination to 50,000 total records to ensure timely responses.

An example of offset based pagination:

const page = await xata.db.Users.getPaginated({
  pagination: { size: 10, offset: 10 }
});

You can find more information about pagination in the API reference. When trying to make a decision between cursor and pagination styles we recommend starting with offset-based pagination since it provides more flexibility. If a table becomes very large and you need to page through it all, switch to the cursor-based method.

#

Next Steps

Now that we can get retrieve data from a database, we might be interested in creating more data, updating data or deleting data. We've got guides for each of these operations.

#

Using the Replica Store

By specifying the option consistency: eventual the query request will be serviced by the Replica Store which has a small, typically insignificant, propagation delay compared to the Primary Store as outlined in the Data Model guide.

The default value for the consistency option is strong, which retrieves data from the Primary Store and guarantees that the response is up to date with the latest state of the record content.

It is recommended to use the Replica Store for queries wherever possible, in order to get the best possible performance out of your current plan.

const page = await xata.db.Users.getPaginated({
  consistency: "eventual"
})

On this page

The TypeScript SDK functions for queryingGetting a record by IDColumns SelectionSelecting Columns from the Linked TablesFiltering RecordsSorting the ResultsPaginating ResultsCursor-based PaginationOffset-based PaginationNext StepsUsing the Replica Store