Querying and Filtering Data

Learn how to retrieve, filter, sort, and paginate your data using powerful query capabilities.

Basic Querying

Get All Records

Retrieve all records from a table:

GET /api/apps/{app-slug}/datatables/{table-name}/data/

Example:

GET /api/apps/blog-app/datatables/users/data/

Response:

{
  "status": "success",
  "message": "Data retrieved successfully",
  "data": [
    {
      "id": 1,
      "username": "john_doe",
      "email": "john@example.com",
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "id": 2,
      "username": "jane_smith",
      "email": "jane@example.com",
      "created_at": "2024-01-16T14:20:00Z"
    }
  ],
  "total": 2
}

Get Single Record

Retrieve a specific record by ID:

GET /api/apps/{app-slug}/datatables/{table-name}/data/{id}/

Example:

GET /api/apps/blog-app/datatables/users/data/1/

Response:

{
  "status": "success",
  "message": "Data retrieved successfully",
  "data": {
    "id": 1,
    "username": "john_doe",
    "email": "john@example.com",
    "bio": "Software developer",
    "created_at": "2024-01-15T10:30:00Z"
  }
}

Filtering

Filter records using query parameters with powerful operators.

Using curl

All REST examples below can be run with curl by prepending the base URL and auth headers:

curl "https://your-site.taruvi.cloud{ENDPOINT}" \
  -H "Authorization: Bearer YOUR_SESSION_TOKEN" \
  -H "x-api-key: YOUR_API_KEY"

Replace {ENDPOINT} with any path shown in the examples (e.g. /api/apps/blog-app/datatables/posts/data/?status=published).

Exact Match

REST API
Python
JavaScript

GET /api/apps/blog-app/datatables/posts/data/?status=published

# Exact match filter
posts = auth_client.database.from_("posts").filter("status", "eq", "published").execute()

print(f"Found {len(posts['data'])} published posts")

import { Client, Database } from '@taruvi/sdk'

const client = new Client({ apiKey, appSlug, apiUrl })

// Exact match filter
const posts = await new Database(client).from("posts")
    .filters("status", "eq", "published")
    .execute()

console.log(`Found ${posts.data.length} published posts`)

Comparison Operators

Operator	Description	Example
`_gt`	Greater than	`?views_gt=100`
`_gte`	Greater than or equal	`?score_gte=80`
`_lt`	Less than	`?age_lt=30`
`_lte`	Less than or equal	`?price_lte=50`
`_ne`	Not equal	`?status_ne=draft`

REST API
Python
JavaScript

Examples:

# Posts with more than 100 views
GET /api/apps/blog-app/datatables/posts/data/?views_gt=100

# Products under $50
GET /api/apps/shop-app/datatables/products/data/?price_lte=50

# Users not from USA
GET /api/apps/crm-app/datatables/users/data/?country_ne=USA

# Greater than filter
popular_posts = auth_client.database.from_("posts").filter("views", "gt", 100).execute()

# Less than or equal filter
affordable_products = auth_client.database.from_("products").filter("price", "lte", 50).execute()

# Not equal filter
non_us_users = auth_client.database.from_("users").filter("country", "ne", "USA").execute()

print(f"Popular posts: {len(popular_posts['data'])}")
print(f"Affordable products: {len(affordable_products['data'])}")
print(f"Non-US users: {len(non_us_users['data'])}")

const database = new Database(client)

// Greater than filter
const popularPosts = await database.from("posts")
    .filters("views", "gt", 100)
    .execute()

// Less than or equal filter
const affordableProducts = await database.from("products")
    .filters("price", "lte", 50)
    .execute()

// Not equal filter
const nonUSUsers = await database.from("users")
    .filters("country", "ne", "USA")
    .execute()

console.log(`Popular posts: ${popularPosts.data.length}`)
console.log(`Affordable products: ${affordableProducts.data.length}`)
console.log(`Non-US users: ${nonUSUsers.data.length}`)

IN Operators

Operator	Description	Case Sensitivity
`_in`	Value in list	Case-sensitive
`_nin`	Value not in list	Case-sensitive
`_ina`	Value in list	Case-insensitive
`_nina`	Value not in list	Case-insensitive

REST API
Python
JavaScript

# Posts with specific statuses
GET /api/apps/blog-app/datatables/posts/data/?status_in=published,featured

# Exclude certain categories
GET /api/apps/blog-app/datatables/posts/data/?category_nin=spam,archived

# Case-insensitive search
GET /api/apps/blog-app/datatables/posts/data/?tag_ina=javascript,PYTHON,Ruby

# Posts with specific statuses (IN operator)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('status', 'in', ['published', 'featured']) \
    .get()

# Exclude certain categories (NOT IN operator)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('category', 'nin', ['spam', 'archived']) \
    .get()

# Case-insensitive IN
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('tag', 'ina', ['javascript', 'PYTHON', 'Ruby']) \
    .get()

const database = new Database(client)

// Posts with specific statuses (IN operator)
const posts = await database.from("posts")
    .filters("status", "in", ["published", "featured"])
    .execute()

// Exclude certain categories (NOT IN operator)
const filtered = await database.from("posts")
    .filters("category", "nin", ["spam", "archived"])
    .execute()

// Case-insensitive IN
const tagged = await database.from("posts")
    .filters("tag", "ina", ["javascript", "PYTHON", "Ruby"])
    .execute()

// Case-insensitive NOT IN
const excluded = await database.from("posts")
    .filters("tag", "nina", ["spam"])
    .execute()

String Matching

Contains

Operator	Description	Case Sensitivity
`_contains`	Contains substring	Case-sensitive
`_ncontains`	Does not contain	Case-sensitive
`_icontains`	Contains substring	Case-insensitive
`_nicontains`	Does not contain	Case-insensitive

REST API
Python
JavaScript

# Titles containing "tutorial"
GET /api/apps/blog-app/datatables/posts/data/?title_contains=tutorial

# Emails not containing "spam"
GET /api/apps/crm-app/datatables/users/data/?email_ncontains=spam

# Titles containing "tutorial" (case-insensitive — default)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('title', 'contains', 'tutorial') \
    .get()

# Titles NOT containing "spam" (case-insensitive)
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('title', 'ncontains', 'spam') \
    .get()

# Case-sensitive contains
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('title', 'containss', 'Tutorial') \
    .get()

const database = new Database(client)

// Titles containing "tutorial" (case-insensitive — default)
const posts = await database.from("posts")
    .filters("title", "contains", "tutorial")
    .execute()

// Titles NOT containing "spam" (case-insensitive)
const clean = await database.from("posts")
    .filters("title", "ncontains", "spam")
    .execute()

// Case-sensitive contains
const exact = await database.from("posts")
    .filters("title", "containss", "Tutorial")
    .execute()

// Case-sensitive NOT contains
const excluded = await database.from("posts")
    .filters("title", "ncontainss", "DRAFT")
    .execute()

Starts With

Operator	Description	Case Sensitivity
`_startswith`	Starts with	Case-sensitive
`_istartswith`	Starts with	Case-insensitive

REST API
Python
JavaScript

# Usernames starting with "admin"
GET /api/apps/blog-app/datatables/users/data/?username_startswith=admin

# Products not starting with "test"
GET /api/apps/shop-app/datatables/products/data/?sku_nstartswith=test

# Usernames starting with "admin" (case-sensitive)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('username', 'startswith', 'admin') \
    .get()

# Usernames starting with "admin" (case-insensitive)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('username', 'istartswith', 'admin') \
    .get()

const database = new Database(client)

// Starts with (case-insensitive — default)
const users = await database.from("users")
    .filters("username", "startswith", "admin")
    .execute()

// Starts with (case-sensitive)
const exact = await database.from("users")
    .filters("username", "startswiths", "Admin")
    .execute()

Ends With

Operator	Description	Case Sensitivity
`_endswith`	Ends with	Case-sensitive
`_iendswith`	Ends with	Case-insensitive

REST API
Python
JavaScript

# Emails ending with specific domain
GET /api/apps/crm-app/datatables/users/data/?email_endswith=company.com

# Files not ending with .tmp
GET /api/apps/docs-app/datatables/files/data/?filename_nendswith=.tmp

# Emails ending with specific domain (case-sensitive)
users = auth_client.database.from_('users', app_slug='crm-app') \
    .filter('email', 'endswith', 'company.com') \
    .get()

# Emails ending with domain (case-insensitive)
users = auth_client.database.from_('users', app_slug='crm-app') \
    .filter('email', 'iendswith', 'company.com') \
    .get()

const database = new Database(client)

// Ends with (case-insensitive — default)
const users = await database.from("users")
    .filters("email", "endswith", "company.com")
    .execute()

// Ends with (case-sensitive)
const exact = await database.from("users")
    .filters("email", "endswiths", "Company.com")
    .execute()

Range Queries

Operator	Description	Format
`_between`	Value between range	`?field_between=min,max`
`_nbetween`	Value not in range	`?field_nbetween=min,max`

REST API
Python
JavaScript

# Posts created in January 2024
GET /api/apps/blog-app/datatables/posts/data/?created_at_between=2024-01-01,2024-01-31

# Products outside price range
GET /api/apps/shop-app/datatables/products/data/?price_nbetween=10,20

# Posts created in January 2024
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('created_at', 'between', ['2024-01-01', '2024-01-31']) \
    .get()

# Products outside price range
products = auth_client.database.from_('products', app_slug='shop-app') \
    .filter('price', 'nbetween', [10, 20]) \
    .get()

const database = new Database(client)

// Posts created in January 2024
const posts = await database.from("posts")
    .filters("created_at", "between", ["2024-01-01", "2024-01-31"])
    .execute()

// Products outside price range
const products = await database.from("products")
    .filters("price", "nbetween", [10, 20])
    .execute()

Null Checks

Operator	Description	Value
`_null`	Is null/not null	`true` or `false`
`_nnull`	Is not null/null	`true` or `false`

REST API
Python
JavaScript

# Users without bio
GET /api/apps/blog-app/datatables/users/data/?bio_null=true

# Posts with published date
GET /api/apps/blog-app/datatables/posts/data/?published_at_nnull=true

# Users without bio (is null)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('bio', 'null', True) \
    .get()

# Users with bio (is not null)
users = auth_client.database.from_('users', app_slug='blog-app') \
    .filter('bio', 'nnull', True) \
    .get()

# Posts with published date
posts = auth_client.database.from_('posts', app_slug='blog-app') \
    .filter('published_at', 'nnull', True) \
    .get()

const database = new Database(client)

// Users without bio (is null)
const noBio = await database.from("users")
    .filters("bio", "null", true)
    .execute()

// Users with bio (is not null)
const hasBio = await database.from("users")
    .filters("bio", "nnull", true)
    .execute()

// Posts with published date
const published = await database.from("posts")
    .filters("published_at", "nnull", true)
    .execute()

JSONB Field Querying

For tables using the JSONB provider, query nested fields using JSON path syntax:

__ for JSONB paths, . for relationships

On the REST API, nested JSONB keys are separated with __ (double underscore): metadata__specs__weight. A dot (.) in a filter key is reserved for foreign-key traversal — metadata.color is interpreted as "follow the metadata relationship, then read color", and errors if metadata is not a relationship. SDK helpers may accept dot-separated paths and translate them to __ for you, but raw REST/curl calls must use __ for nested JSONB.

REST API
Python
JavaScript

# Query JSONB field properties
GET /api/apps/shop-app/datatables/products/data/?metadata__color=red

# Query nested properties
GET /api/apps/shop-app/datatables/users/data/?settings__notifications__email=true

# JSONB contains (PostgreSQL @> operator)
GET /api/apps/blog-app/datatables/posts/data/?tags__contains=["beginner","tutorial"]

# Query JSONB field properties (use dot notation for nested fields)
products = auth_client.database.from_('products', app_slug='shop-app') \
    .filter('metadata.color', 'eq', 'red') \
    .get()

# Query deeply nested properties
users = auth_client.database.from_('users', app_slug='shop-app') \
    .filter('settings.notifications.email', 'eq', True) \
    .get()

# Note: Advanced JSONB operators (@>, ?, etc.) may not be fully supported
# For complex JSONB queries, use REST API directly

SDK Note: Use dot notation (field.nested.property) for nested JSONB queries. Advanced PostgreSQL JSONB operators may require REST API.

const database = new Database(client)

// Query JSONB field properties (use dot notation for nested fields)
const products = await database.from("products")
    .filters("metadata.color", "eq", "red")
    .execute()

// Query deeply nested properties
const users = await database.from("users")
    .filters("settings.notifications.email", "eq", true)
    .execute()

JSONB Operators:

field__property: Access nested property
field__contains: JSONB contains (exact match)
field__contained_by: JSONB is contained by
field__has_key: JSONB has top-level key
field__has_any_keys: JSONB has any of keys
field__has_all_keys: JSONB has all keys

Example with nested data:

{
  "id": 1,
  "name": "Premium Widget",
  "metadata": {
    "color": "red",
    "specs": {
      "weight": 150,
      "dimensions": {"width": 10, "height": 20}
    },
    "tags": ["electronics", "featured"]
  }
}

# Query nested specs
GET /data/?metadata__specs__weight_gt=100

# Query array elements
GET /data/?metadata__tags__contains=["featured"]

Case-Sensitive Filtering

Most string operators have both case-insensitive (default) and case-sensitive variants:

Pattern: Add s suffix for case-sensitive matching

Operation	Case-Insensitive	Case-Sensitive
Contains	`_contains`	`_containss`
Starts with	`_startswith`	`_startswiths`
Ends with	`_endswith`	`_endswiths`
In list	`_in`	`_ins`
In array	`_ina`	(case-insensitive only)

Examples:

# Case-insensitive: matches "Python", "python", "PYTHON"
GET /data/?title_contains=python

# Case-sensitive: matches only "Python" (exact case)
GET /data/?title_containss=Python

# Case-insensitive list matching
GET /data/?tag_ina=JavaScript,python,Ruby

When to use case-sensitive:

Matching code identifiers (className, functionName)
Exact acronyms (API, REST, HTTP)
File extensions (.PDF vs .pdf)
Programming language keywords

When to use case-insensitive (default):

User search queries
Email addresses
Natural language content
Most user-facing filters

Complex Filters (AND / OR / NOT)

For nested logical conditions, use the JSON filter tree API:

REST API
Python
JavaScript

# OR filter: posts in "tech" or "news" category
GET /api/apps/blog-app/datatables/posts/data/?filters=[{"operator":"or","value":[{"field":"category","operator":"eq","value":"tech"},{"field":"category","operator":"eq","value":"news"}]}]

# AND + OR: active posts that are either featured or have 100+ views
GET /api/apps/blog-app/datatables/posts/data/?filters=[{"operator":"and","value":[{"field":"status","operator":"eq","value":"active"},{"operator":"or","value":[{"field":"featured","operator":"eq","value":true},{"field":"views","operator":"gte","value":100}]}]}]

# NOT: posts that are NOT archived
GET /api/apps/blog-app/datatables/posts/data/?filters=[{"operator":"not","value":{"field":"status","operator":"eq","value":"archived"}}]

# Object form is also accepted: {"and"|"or"|"not": ...}
GET /api/apps/blog-app/datatables/posts/data/?filters={"not":{"status":"archived"}}

# OR filter
results = (
    auth_client.database.from_("posts")
    .filter({"or": [{"category": "tech"}, {"category": "news"}]})
    .execute()
)

# Nested AND + OR
results = (
    auth_client.database.from_("posts")
    .filter({
        "and": [
            {"status": "active"},
            {"or": [{"featured": True}, {"views__gte": 100}]},
        ]
    })
    .execute()
)

const database = new Database(client)

// OR filter: posts in "tech" or "news" category
const posts = await database.from("posts")
    .filters([{
        operator: "or",
        value: [
            { field: "category", operator: "eq", value: "tech" },
            { field: "category", operator: "eq", value: "news" },
        ],
    }])
    .execute()

// Nested AND + OR: active posts that are either featured or have 100+ views
const featured = await database.from("posts")
    .filters([{
        operator: "and",
        value: [
            { field: "status", operator: "eq", value: "active" },
            {
                operator: "or",
                value: [
                    { field: "featured", operator: "eq", value: true },
                    { field: "views", operator: "gte", value: 100 },
                ],
            },
        ],
    }])
    .execute()

The .filters(tree) overload accepts a BackendFilterTreeRoot — an array of logical nodes. Each node is either:

A logical node: { operator: "and" | "or", value: [...children] }, or { operator: "not", value: <single child> }
A leaf node: { field, operator, value }

A leaf node's field may be a dotted foreign-key traversal path (e.g. deal_id.company_id.name), so related-table conditions compose inside and / or / not just like plain columns — see Composing Traversal Filters with OR / NOT.

Array Operators (PostgreSQL)

For columns storing PostgreSQL arrays:

Operator	Description	Example
`_acontains`	Array contains all values	Column `@>` ARRAY[values]
`_nacontains`	Array does NOT contain all
`_acontainedby`	Array is contained by	Column `<@` ARRAY[values]
`_nacontainedby`	Array is NOT contained by
`_aoverlap`	Arrays have overlap	Column `&&` ARRAY[values]
`_naoverlap`	Arrays have no overlap
`_aelement`	Value exists in array	value = ANY(column)
`_naelement`	Value not in array	value != ALL(column)

REST API
Python
JavaScript

# Posts tagged with both "python" AND "tutorial"
GET /data/?tags__acontains=python,tutorial

# Posts with any of these tags
GET /data/?tags__aoverlap=python,rust,go

# Posts tagged with both "python" AND "tutorial"
posts = auth_client.database.from_("posts") \
    .filter("tags", "acontains", ["python", "tutorial"]).execute()

# Posts with any of these tags
posts = auth_client.database.from_("posts") \
    .filter("tags", "aoverlap", ["python", "rust", "go"]).execute()

const database = new Database(client)

// Posts tagged with both "python" AND "tutorial"
const posts = await database.from("posts")
    .filters("tags", "acontains", ["python", "tutorial"])
    .execute()

// Posts with any of these tags
const overlap = await database.from("posts")
    .filters("tags", "aoverlap", ["python", "rust", "go"])
    .execute()

// Check if a single value exists in the array column
const hasPython = await database.from("posts")
    .filters("tags", "aelement", "python")
    .execute()

Range Operators (PostgreSQL)

For columns storing PostgreSQL range types (int4range, tsrange, daterange, etc.):

Operator	Description	SQL Equivalent
`_rcontains`	Range contains point or range	`@>`
`_rcontainedby`	Range is contained by	`<@`
`_roverlaps`	Range overlaps	`&&`
`_radjacent`	Range is adjacent	`-\|-`
`_rstrictleft`	Range is strictly left	`<<`
`_rstrictright`	Range is strictly right	`>>`

REST API
Python
JavaScript

# Shifts that include 9am
GET /data/?shift_hours__rcontains=9

# Shifts that overlap 9am-5pm
GET /data/?shift_hours__roverlaps=9,17

# Events within Q1 2024
GET /data/?event_dates__rcontainedby=2024-01-01,2024-03-31

# Shifts that include 9am
shifts = auth_client.database.from_("schedules") \
    .filter("shift_hours", "rcontains", 9).execute()

# Shifts that overlap 9am-5pm
shifts = auth_client.database.from_("schedules") \
    .filter("shift_hours", "roverlaps", [9, 17]).execute()

const database = new Database(client)

// Shifts that include 9am
const shifts = await database.from("schedules")
    .filters("shift_hours", "rcontains", 9)
    .execute()

// Shifts that overlap 9am-5pm
const overlap = await database.from("schedules")
    .filters("shift_hours", "roverlaps", [9, 17])
    .execute()

// Events within Q1 2024
const q1 = await database.from("events")
    .filters("event_dates", "rcontainedby", ["2024-01-01", "2024-03-31"])
    .execute()

Full-Text Search

For advanced text search across multiple fields:

REST API
Python
JavaScript

# Search across indexed fields
GET /api/apps/blog-app/datatables/posts/data/?search=beginner tutorial

# Combine with filters
GET /api/apps/blog-app/datatables/posts/data/?search=guide&status=published

# Search across indexed fields
results = auth_client.database.from_("posts") \
    .search("beginner tutorial").execute()

# Combine with filters
results = auth_client.database.from_("posts") \
    .search("guide") \
    .filter("status", "eq", "published") \
    .execute()

const database = new Database(client)

// Search across indexed fields
const results = await database.from("posts")
    .search("beginner tutorial")
    .execute()

// Combine with filters
const published = await database.from("posts")
    .search("guide")
    .filters("status", "eq", "published")
    .execute()

Requirements:

Table must have search_vector field configured
GIN index recommended for performance
See Indexes Guide for setup

Search Features:

Multi-word queries
Relevance ranking
Stemming support (e.g., "running" matches "run", "runs")
Stop word filtering
AND/OR logic

Example with ranking:

# Returns results sorted by relevance
GET /api/apps/docs-app/datatables/articles/data/?search=rest api guide

Response includes relevance score:

{
  "data": [
    {
      "id": 1,
      "title": "REST API Tutorial",
      "rank": 0.95,
      "excerpt": "Learn how to build REST APIs..."
    },
    {
      "id": 2,
      "title": "Building APIs",
      "rank": 0.72,
      "excerpt": "A comprehensive guide to API development..."
    }
  ]
}

Advanced search syntax:

# Phrase search (exact match)
GET /data/?search="rest api framework"

# Exclude terms with minus
GET /data/?search=guide -archived

# OR operator
GET /data/?search=tutorial OR guide

Filter rows by fields on a foreign-keyed (related) table in a single request, using dot-notation in the filter key.

This avoids the two-step pattern of fetching IDs first and then filtering by __in, and works correctly with combined filter + sort + pagination flows.

Key guarantees:

The related row is not added to the response unless you also pass populate=. Filtering on a related field does not bloat the payload.
All existing operators (__contains, __gt, __in, __between, __null, __search, etc.) work on related fields.
Existing literal-FK filters (?deal_id__contains=aaa) are unchanged — they still match against the FK column itself.
Traversal filters compose in any boolean position — plain conjunctions and inside or / not filter trees. See Composing Traversal Filters with OR / NOT.

Forward Foreign Key (Many-to-One)

For a belongsTo relationship (e.g., activities.deal_id → deals.id), filter by any field on the related record:

REST API
Python
JavaScript

# Activities whose related deal's name contains "Acme"
GET /api/apps/crm/datatables/activities/data/?deal_id.name__contains=Acme

# Activities whose deal is in the "closed_won" stage (implicit equality)
GET /api/apps/crm/datatables/activities/data/?deal_id.stage=closed_won

# Multi-hop: activities whose deal's company name starts with "Acme"
GET /api/apps/crm/datatables/activities/data/?deal_id.company_id.name__startswith=Acme

# Combined with populate (one shared join, related row included in payload)
GET /api/apps/crm/datatables/activities/data/?deal_id.name__contains=Acme&populate=deal_id

# Activities whose related deal's name contains "Acme"
acme_activities = (
    auth_client.database.from_("activities")
    .filter("deal_id.name", "contains", "Acme")
    .execute()
)

# Multi-hop: activities whose deal's company name starts with "Acme"
results = (
    auth_client.database.from_("activities")
    .filter("deal_id.company_id.name", "startswith", "Acme")
    .execute()
)

const database = new Database(client)

// Activities whose related deal's name contains "Acme"
const acmeActivities = await database.from("activities")
    .filters("deal_id.name", "contains", "Acme")
    .execute()

// Multi-hop: activities whose deal's company name starts with "Acme"
const results = await database.from("activities")
    .filters("deal_id.company_id.name", "startswith", "Acme")
    .execute()

SQL behavior: an INNER JOIN to the related table with the predicate placed in the ON clause. Rows whose foreign key is NULL (no related record) are excluded.

Multiple traversal filters that share the same FK path are combined into one join with both predicates ANDed:

# One INNER JOIN to deals, two ANDed predicates in the ON clause
GET /api/apps/crm/datatables/activities/data/?deal_id.name__contains=Acme&deal_id.stage=open

Composing Traversal Filters with OR / NOT

A single-key forward-FK traversal in a plain (implicit-AND) query is hoisted to an INNER JOIN. The moment a traversal filter appears inside an or / not tree — or next to a non-traversal predicate inside the same and child — it is compiled instead to a correlated EXISTS subquery, so the boolean logic is preserved exactly and parent rows are never duplicated. Use the JSON filter tree to express these.

REST API
Python
JavaScript

# OR: activities whose deal is closed_won, OR whose own status is completed
GET /api/apps/crm/datatables/activities/data/?filters={"or":[{"deal_id.stage":"closed_won"},{"status":"completed"}]}

# NOT: activities whose deal is NOT in the closed_lost stage
GET /api/apps/crm/datatables/activities/data/?filters={"not":{"deal_id.stage":"closed_lost"}}

# OR with a multi-hop traversal (activities → deal → company)
GET /api/apps/crm/datatables/activities/data/?filters={"or":[{"deal_id.company_id.name__contains":"Acme"},{"status":"completed"}]}

# OR: a forward-FK traversal next to a plain field
results = (
    auth_client.database.from_("activities")
    .filter({"or": [{"deal_id.stage": "closed_won"}, {"status": "completed"}]})
    .execute()
)

# NOT EXISTS over a traversal
results = (
    auth_client.database.from_("activities")
    .filter({"not": {"deal_id.stage": "closed_lost"}})
    .execute()
)

const database = new Database(client)

// OR: a forward-FK traversal next to a plain field
const results = await database.from("activities")
    .filters([{
        operator: "or",
        value: [
            { field: "deal_id.stage", operator: "eq", value: "closed_won" },
            { field: "status", operator: "eq", value: "completed" },
        ],
    }])
    .execute()

SQL behavior: … WHERE (EXISTS (SELECT 1 FROM deals AS _x WHERE activities.deal_id = _x.id AND _x.stage = 'closed_won')) OR activities.status = 'completed'. A not wraps the subquery as NOT EXISTS. This works for forward (belongsTo), reverse (hasMany), and many-to-many relationships, at single or multi-hop depth — each extra hop nests another correlated EXISTS.

Reverse Foreign Key (One-to-Many)

For a hasMany relationship (e.g., a deal has many activities), filter parent rows by a property of any matching child:

# Deals that have at least one activity whose subject contains "follow up"
GET /api/apps/crm/datatables/deals/data/?activities.subject__contains=follow up

# Deals with at least one completed activity in the "demo" status
GET /api/apps/crm/datatables/deals/data/?activities.status=completed

Semantics: "at least one matching child" (default ANY). Implemented as EXISTS (SELECT 1 FROM activities WHERE activities.deal_id = deals.id AND ...) so parent rows are not duplicated and pagination/sort remain stable.

Multi-hop reverse chains are supported too — each hop nests another correlated EXISTS:

# Companies that have a department which has an employee named "Alice"
GET /api/apps/hr/datatables/companies/data/?departments.employees.name=Alice

Many-to-Many

For an M2M relationship (e.g., users ↔ roles via user_roles junction):

# Users who have at least one role named "admin"
GET /api/apps/cloud/datatables/users/data/?roles.name__contains=admin

# Multi-hop through M2M: users who have a role that grants the "billing" permission
GET /api/apps/cloud/datatables/users/data/?roles.permissions.code=billing

The query traverses the junction table inside an EXISTS subquery automatically — no need to know about the junction. Hops that continue past the M2M target (e.g. roles.permissions.code) nest a further correlated EXISTS for each subsequent relationship.

Limits and Validation

Constraint	Default	Setting
Max distinct traversal filters per request	5	`DATA_SERVICE_MAX_FILTER_TRAVERSALS`
Max chain depth (hops)	3	`DATA_SERVICE_MAX_POPULATE_DEPTH`

Both caps apply to the request as a whole, including traversal filters nested inside or / not trees, and bound multi-hop chains across every relationship type. Traversal filters cannot be combined with aggregation queries (_aggregate=).

Errors you may see:

'<segment>' is not a relationship on table '<table>' — the FK column or relationship name doesn't exist on the schema.
field '<leaf>' does not exist on related table '<table>' — leaf field doesn't exist on the related table's schema.
FK-traversal filter depth cannot exceed <n> hops — the chain is longer than DATA_SERVICE_MAX_POPULATE_DEPTH.
Cannot apply more than <n> FK-traversal filters per request — the request exceeds DATA_SERVICE_MAX_FILTER_TRAVERSALS.
Cannot use FK-traversal filters with aggregation queries — traversal and _aggregate= cannot be mixed.
Malformed filter field path '<path>' — the path has empty segments (.foo, foo., foo..bar).

Phase Compatibility

flat_table provider: fully supported — forward, reverse, and M2M; single and multi-hop; in every boolean position.
jsonb provider: traversal filters are rejected with a clear error in every position (including inside or / not) — full parity is on the roadmap. Nested JSONB column access on a jsonb table uses __ (see JSONB Field Querying), not dotted relationship traversal.
Aggregation queries (_aggregate=): traversal filters are not supported alongside aggregations.

Sorting

Sort results by one or more fields.

Single Field

REST API
Python
JavaScript

# Sort by username (ascending)
GET /api/apps/blog-app/datatables/users/data/?_sort=username

# Sort by created date (descending)
GET /api/apps/blog-app/datatables/posts/data/?_sort=created_at&_order=desc

# Sort by username (ascending)
users = auth_client.database.from_("users").sort("username", "asc").execute()

# Sort by created_at (descending)
posts = auth_client.database.from_("posts").sort("created_at", "desc").execute()

print(f"First user: {users['data'][0]['username']}")
print(f"Latest post: {posts['data'][0]['title']}")

import { Client, Database } from '@taruvi/sdk'

const client = new Client({ apiKey, appSlug, apiUrl })
const database = new Database(client)

// Sort by username (ascending)
const users = await database.from("users")
    .orderBy("username", "asc")
    .execute()

// Sort by created_at (descending)
const posts = await database.from("posts")
    .orderBy("created_at", "desc")
    .execute()

console.log(`First user: ${users.data[0].username}`)
console.log(`Latest post: ${posts.data[0].title}`)

Multiple Fields

REST API
Python
JavaScript

# Sort by status, then by created_at descending
GET /api/apps/blog-app/datatables/posts/data/?_sort=status,created_at&_order=asc,desc

# Sort by multiple fields
posts = (auth_client.database.from_("posts")
    .sort("status", "asc")
    .sort("created_at", "desc")
    .get())

for post in posts['data'][:5]:
    print(f"{post['status']}: {post['title']} ({post['created_at']})")

const database = new Database(client)

// Sort by multiple fields
const posts = await database.from("posts")
    .orderBy([
        { field: "status", order: "asc" },
        { field: "created_at", order: "desc" },
    ])
    .execute()

posts.data.slice(0, 5).forEach(post => {
    console.log(`${post.status}: ${post.title} (${post.created_at})`)
})

Parameters:

_sort: Comma-separated list of field names
_order: Comma-separated list of asc or desc (default: asc)

Sort by a field on a foreign-keyed table using the same dot notation as filters:

REST API
Python
JavaScript

# Sort activities by their related deal's name (descending)
GET /api/apps/crm/datatables/activities/data/?_sort=deal_id.name&_order=desc

# Multi-hop sort: by company name through deal
GET /api/apps/crm/datatables/activities/data/?_sort=deal_id.company_id.name

# Mixed root + traversal sort
GET /api/apps/crm/datatables/activities/data/?_sort=updated_at,deal_id.name&_order=desc,asc

# Sort activities by their related deal's name
activities = (
    auth_client.database.from_("activities")
    .sort("deal_id.name", "desc")
    .execute()
)

const database = new Database(client)

// Sort activities by their related deal's name
const activities = await database.from("activities")
    .orderBy("deal_id.name", "desc")
    .execute()

SQL behavior:

Sort-only traversal uses LEFT JOIN, so rows with NULL foreign keys are preserved (their sort key is NULL, placed at the end by PostgreSQL default).
If a filter is also active on the same path (e.g., ?deal_id.stage=open&_sort=deal_id.name), the join is shared and promoted to INNER JOIN.

Limits: same as filter traversal — DATA_SERVICE_MAX_POPULATE_DEPTH hops, belongsTo relationships only.

Pagination

Control the number of results returned.

Basic Pagination

REST API
Python
JavaScript

# Get first 10 records
GET /api/apps/blog-app/datatables/posts/data/?_start=0&_end=10

# Get records 10-20
GET /api/apps/blog-app/datatables/posts/data/?_start=10&_end=20

# Get 50 records starting from 100
GET /api/apps/blog-app/datatables/posts/data/?_start=100&_end=150

Parameters:

_start: Starting index (0-based)
_end: Ending index (exclusive)

Response Format: The response includes total count and meta with pagination information:

{
  "data": [...],
  "total": 250,
  "meta": {
    "offset": 0,
    "limit": 10,
    "count": 10,
    "has_more": true
  }
}

# Get first page (10 records)
page1 = auth_client.database.from_("posts").page_size(10).page(1).execute()

print(f"Total posts: {page1.get('total', 0)}")
print(f"Page 1 has {len(page1['data'])} records")

# Get second page
page2 = auth_client.database.from_("posts").page_size(10).page(2).execute()

print(f"Page 2 has {len(page2['data'])} records")

# Iterate through pages
current_page = 1
while True:
    results = auth_client.database.from_("posts").page_size(20).page(current_page).execute()

    for post in results['data']:
        print(f"- {post['title']}")

    if len(results['data']) < 20:
        break  # Last page

    current_page += 1

import { Client, Database } from '@taruvi/sdk'

const client = new Client({ apiKey, appSlug, apiUrl })
const database = new Database(client)

// Get first page (10 records)
const page1 = await database.from("posts")
    .pageSize(10)
    .page(1)
    .execute()

console.log(`Total posts: ${page1.total || 0}`)
console.log(`Page 1 has ${page1.data.length} records`)

// Get second page
const page2 = await database.from("posts")
    .pageSize(10)
    .page(2)
    .execute()

console.log(`Page 2 has ${page2.data.length} records`)

// Iterate through pages
let currentPage = 1
const pgSize = 20

while (true) {
    const results = await database.from("posts")
        .pageSize(pgSize)
        .page(currentPage)
        .execute()

    results.data.forEach(post => {
        console.log(`- ${post.title}`)
    })

    if (results.data.length < pgSize) break
    currentPage++
}

Calculating Pages

import { Client, Database } from '@taruvi/sdk'

const client = new Client({ apiKey, appSlug, apiUrl })
const database = new Database(client)

const pageSize = 20
const page = 1

const result = await database.from("posts")
    .pageSize(pageSize)
    .page(page)
    .execute()

const totalPages = Math.ceil(result.total / pageSize)

Combining Filters

You can combine multiple filters, sorting, and pagination:

GET /api/apps/blog-app/datatables/posts/data/?\
  status=published&\
  views_gt=100&\
  category_in=tech,programming&\
  title_contains=python&\
  _sort=views,created_at&\
  _order=desc,desc&\
  _start=0&\
  _end=20

This query:

✅ Filters published posts
✅ With more than 100 views
✅ In tech or programming categories
✅ Title containing "python"
✅ Sorted by views (high to low), then by date (newest first)
✅ Returns first 20 results

Response Format

List Response

All list endpoints return a standardized response with data, total, and meta:

{
  "data": [
    {
      "id": 1,
      "title": "Getting Started with Python",
      "status": "published",
      "views": 1523,
      "category": "programming",
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "id": 5,
      "title": "Advanced Python Techniques",
      "status": "published",
      "views": 856,
      "category": "tech",
      "created_at": "2024-01-20T14:20:00Z"
    }
  ],
  "total": 42,
  "meta": {
    "offset": 0,
    "limit": 20,
    "count": 2,
    "has_more": true
  }
}

Response Fields

data: Array of records matching the query
total: Total count of records matching filters (not just current page)
meta: Pagination metadata
- offset: Starting position of current page
- limit: Maximum records per page
- count: Number of records in current response
- has_more: Whether there are more records available

Use total and meta to implement pagination UI.

Aggregations

Perform SQL-like aggregations directly in the database for analytics and reporting.

Basic Aggregates

Count all records:

GET /api/apps/blog-app/datatables/posts/data/?_aggregate=count(*)

Response:

{
  "data": [],
  "aggregates": {
    "count": 150
  }
}

Sum, Average, Min, Max:

# Total revenue
GET /api/apps/shop-app/datatables/orders/data/?_aggregate=sum(total)

# Average rating
GET /api/apps/blog-app/datatables/posts/data/?_aggregate=avg(rating)

# Price range
GET /api/apps/shop-app/datatables/products/data/?_aggregate=min(price),max(price)

GROUP BY

Group results and aggregate per group:

# Count posts by category
GET /api/apps/blog-app/datatables/posts/data/?_aggregate=count(*)&_group_by=category

# Total sales by product
GET /api/apps/shop-app/datatables/orders/data/?_aggregate=sum(amount)&_group_by=product_id

Response:

{
  "data": [
    {"category": "tutorial", "count": 25},
    {"category": "news", "count": 15},
    {"category": "review", "count": 10}
  ],
  "total": 3
}

HAVING Clause

Filter aggregated results:

# Categories with more than 10 posts
GET /data/?_aggregate=count(*)&_group_by=category&_having=count__gt=10

# Products with high sales
GET /data/?_aggregate=sum(amount)&_group_by=product_id&_having=sum_amount__gte=10000

Multiple Aggregates

Combine multiple aggregate functions:

GET /data/?_aggregate=count(*),sum(price),avg(price),min(price),max(price)

Response:

{
  "aggregates": {
    "count": 50,
    "sum_price": 5000.00,
    "avg_price": 100.00,
    "min_price": 10.00,
    "max_price": 500.00
  }
}

Combining with Filters

Apply WHERE filters before aggregation:

# Published posts only, grouped by author
GET /data/?status=published&_aggregate=count(*)&_group_by=author_id

# Sales this month
GET /data/?created_at__gte=2024-03-01&_aggregate=sum(amount)

For complete aggregation documentation, see Aggregations Guide which covers:

All aggregate functions (count, sum, avg, min, max, array_agg, string_agg, json_agg, stddev, variance)
Advanced GROUP BY with multiple fields
Date/time grouping with date_trunc
HAVING clause operators
Complex analytics queries
Performance optimization

Examples by Use Case

Search Functionality

# Search users by name or email
GET /api/apps/crm-app/datatables/users/data/?\
  name_contains=john&\
  email_contains=@company.com

Recent Items

# Last 10 posts
GET /api/apps/blog-app/datatables/posts/data/?\
  _sort=created_at&\
  _order=desc&\
  _start=0&\
  _end=10

Active Users

# Users who logged in recently
GET /api/apps/crm-app/datatables/users/data/?\
  last_login_nnull=true&\
  _sort=last_login&\
  _order=desc

Status Filtering

# Pending orders
GET /api/apps/shop-app/datatables/orders/data/?\
  status=pending&\
  _sort=created_at&\
  _order=asc

Client Libraries

JavaScript/TypeScript

import { Client, Database } from '@taruvi/sdk'

const client = new Client({ apiKey: 'your-key', appSlug: 'blog-app', apiUrl: 'https://your-site.taruvi.cloud' })

const fetchUsers = async (page = 1, pageSize = 20) => {
  const result = await new Database(client).from("users")
    .filters("status", "eq", "active")
    .orderBy("created_at", "desc")
    .pageSize(pageSize)
    .page(page)
    .execute()

  return {
    data: result.data,
    total: result.total,
    totalPages: Math.ceil(result.total / pageSize),
  }
}

Python

import requests

def fetch_posts(
    app_slug='blog-app',
    status='published',
    page=1,
    page_size=20,
    sort_by='created_at',
    order='desc'
):
    start = (page - 1) * page_size
    end = page * page_size

    params = {
        'status': status,
        '_sort': sort_by,
        '_order': order,
        '_start': start,
        '_end': end,
    }

    response = requests.get(
        f'https://api.yourapp.com/api/apps/{app_slug}/datatables/posts/data/',
        params=params,
        headers={'Authorization': f'Bearer {token}'}
    )

    result = response.json()

    return {
        'data': result['data'],
        'total': result['total'],
        'total_pages': -(-result['total'] // page_size),
        'meta': result.get('meta', {}),
        'has_more': result.get('meta', {}).get('has_more', False),
    }

Performance Tips

1. Use Specific Fields

Only select the fields you need (future feature):

# Coming soon: field selection
GET /api/apps/blog-app/datatables/posts/data/?fields=id,title,created_at

2. Limit Page Size

Don't request too many records at once:

# ✅ Good: Reasonable page size
?_start=0&_end=50

# ❌ Bad: Too many records
?_start=0&_end=10000

3. Use Indexes

Ensure your filtered and sorted fields have database indexes. Contact your administrator if queries are slow.

4. Cache Results

Cache frequently accessed data on the client side:

const cache = new Map();

async function fetchWithCache(url) {
  if (cache.has(url)) {
    return cache.get(url);
  }

  const response = await fetch(url);
  const data = await response.json();

  cache.set(url, data);
  setTimeout(() => cache.delete(url), 60000); // Cache for 1 minute

  return data;
}

Next Steps

CRUD Operations: Complete guide to creating, reading, updating, and deleting data
Aggregations: SQL-like GROUP BY, HAVING, and aggregate functions
Relationships: Query related data with populate
Indexes: Create indexes for better query performance
API Endpoints Reference: Complete endpoint listing

Common Patterns

Search with Autocomplete

# Search as user types
GET /api/apps/blog-app/datatables/users/data/?\
  username_startswith=joh&\
  _start=0&\
  _end=10

Infinite Scroll

import { Client, Database } from '@taruvi/sdk'

const client = new Client({ apiKey, appSlug, apiUrl })
const database = new Database(client)

let page = 1
const pageSize = 20

async function loadMore() {
  const result = await database.from("posts")
      .pageSize(pageSize)
      .page(page)
      .execute()

  appendToList(result.data)
  page++
}

Faceted Search

# Get counts for each category
GET /api/apps/shop-app/datatables/products/data/?status=active

# Then filter by category
GET /api/apps/shop-app/datatables/products/data/?\
  status=active&\
  category=electronics

Basic Querying​

Get All Records​

Get Single Record​

Filtering​

Exact Match​

Comparison Operators​

IN Operators​

String Matching​

Contains​

Starts With​

Ends With​

Range Queries​

Null Checks​

JSONB Field Querying​

Case-Sensitive Filtering​

Complex Filters (AND / OR / NOT)​

Array Operators (PostgreSQL)​

Range Operators (PostgreSQL)​

Full-Text Search​

Filtering on Related Records​

Forward Foreign Key (Many-to-One)​

Multiple Filters on the Same Related Table​

Composing Traversal Filters with OR / NOT​

Reverse Foreign Key (One-to-Many)​

Many-to-Many​

Limits and Validation​

Phase Compatibility​

Sorting​

Single Field​

Multiple Fields​

Sorting on Related Fields​

Pagination​

Basic Pagination​

Calculating Pages​

Combining Filters​

Response Format​

List Response​

Response Fields​

Aggregations​

Basic Aggregates​

GROUP BY​

HAVING Clause​

Multiple Aggregates​

Combining with Filters​

Examples by Use Case​

Search Functionality​

Recent Items​

Popular Content​

Active Users​

Status Filtering​

Client Libraries​

JavaScript/TypeScript​

Python​

Performance Tips​

1. Use Specific Fields​

2. Limit Page Size​

3. Use Indexes​

4. Cache Results​

Next Steps​

Common Patterns​

Search with Autocomplete​

Infinite Scroll​

Faceted Search​

Basic Querying

Get All Records

Get Single Record

Filtering

Exact Match

Comparison Operators

IN Operators

String Matching

Contains

Starts With

Ends With

Range Queries

Null Checks

JSONB Field Querying

Case-Sensitive Filtering

Complex Filters (AND / OR / NOT)

Array Operators (PostgreSQL)

Range Operators (PostgreSQL)

Full-Text Search

Filtering on Related Records

Forward Foreign Key (Many-to-One)

Multiple Filters on the Same Related Table

Composing Traversal Filters with OR / NOT

Reverse Foreign Key (One-to-Many)

Many-to-Many

Limits and Validation

Phase Compatibility

Sorting

Single Field

Multiple Fields

Sorting on Related Fields

Pagination

Basic Pagination

Calculating Pages

Combining Filters

Response Format

List Response

Response Fields

Aggregations

Basic Aggregates

GROUP BY

HAVING Clause

Multiple Aggregates

Combining with Filters

Examples by Use Case

Search Functionality

Recent Items

Popular Content

Active Users

Status Filtering

Client Libraries

JavaScript/TypeScript

Python

Performance Tips

1. Use Specific Fields

2. Limit Page Size

3. Use Indexes

4. Cache Results

Next Steps

Common Patterns

Search with Autocomplete

Infinite Scroll

Faceted Search