Database API | Athena | API Reference Docs

This guide explains how to use the Database API to perform CRUD operations on your database tables. The API uses PostgREST-style query parameters for filtering, which provides a powerful and flexible way to query data.

Overview

The Database API provides four main operations:

Operation	HTTP Method	Description
Select	`GET`	Read rows from a table with filtering, ordering, and pagination
Insert	`POST`	Insert one or more rows into a table
Update	`PATCH`	Update rows matching filter conditions
Delete	`DELETE`	Delete rows matching filter conditions

All operations use the same endpoint pattern:

/api/v0/databases/{asset_id}/data/{table_name}

Filter Syntax

Filters are passed as query parameters where:

The parameter name is the column name
The parameter value is the operator and value in the format operator.value

SDK Filter Usage:

Python: Pass filters as keyword arguments directly (e.g., status="eq.active")
TypeScript: Pass filters via the queryParams option in the 4th argument (e.g., { queryParams: { status: "eq.active" } })
cURL: Pass filters as query string parameters (e.g., ?status=eq.active)

Supported Operators

Operator	Description	Example	SQL Equivalent
`eq`	Equal	`?status=eq.active`	`WHERE status = 'active'`
`neq`	Not equal	`?status=neq.deleted`	`WHERE status != 'deleted'`
`gt`	Greater than	`?age=gt.21`	`WHERE age > 21`
`gte`	Greater than or equal	`?age=gte.18`	`WHERE age >= 18`
`lt`	Less than	`?price=lt.100`	`WHERE price < 100`
`lte`	Less than or equal	`?price=lte.50`	`WHERE price <= 50`
`like`	Pattern match (case-sensitive)	`?name=like.John`	`WHERE name LIKE '%John%'`
`ilike`	Pattern match (case-insensitive)	`?email=ilike.*@gmail.com`	`WHERE email ILIKE '%@gmail.com'`
`in`	In list	`?status=in.(active,pending)`	`WHERE status IN ('active', 'pending')`
`is`	IS NULL/TRUE/FALSE	`?deleted_at=is.null`	`WHERE deleted_at IS NULL`

Pattern Matching

For like and ilike operators, use * as a wildcard (it gets converted to % in SQL):

*value - Ends with “value”
value* - Starts with “value”
*value* - Contains “value”

# Find all users with emails ending in @example.com
?email=ilike.*@example.com
# Find all products starting with "Premium"
?name=like.Premium*
# Find all descriptions containing "sale"
?description=ilike.*sale*

Combining Multiple Filters

Multiple filters are combined with AND logic:

# Find active users over 21
?status=eq.active&age=gt.21
# Find products between $10 and $50
?price=gte.10&price=lte.50
# Find pending orders from today
?status=eq.pending&created_at=gte.2024-01-15

Select (Read) Rows

Read data from a table with optional filtering, ordering, and pagination.

TypeScript

Python

cURL

import { AthenaIntelligenceClient } from '@athenaintel/sdk';
const client = new AthenaIntelligenceClient({
  apiKey: process.env.ATHENA_API_KEY,
});
// Basic select - get all rows
const response = await client.databases.select(
  "your-database-asset-id",
  "users"
);
// With pagination and ordering options
const paginatedResponse = await client.databases.select(
  "your-database-asset-id",
  "users",
  {
    select: "id,name,email",           // Columns to return
    order: "created_at.desc",          // Sort by created_at descending
    limit: 50,                         // Max 50 rows
    offset: 0,                         // Skip 0 rows (for pagination)
  }
);
// With filters - pass filter conditions via queryParams
const filteredResponse = await client.databases.select(
  "your-database-asset-id",
  "users",
  {
    select: "id,name,email",
    limit: 50,
  },
  {
    queryParams: {
      status: "eq.active",             // Filter: status = 'active'
      age: "gt.21",                    // Filter: age > 21
    }
  }
);
console.log(filteredResponse.data);
// [{ id: 1, name: "John", email: "john@example.com" }, ...]

Select Query Parameters

Parameter	Type	Default	Description
`select`	string	`*` (all)	Comma-separated list of columns to return
`order`	string	-	Order by clause (e.g., `created_at.desc`, `name.asc`)
`limit`	integer	100	Maximum rows to return (1-1000)
`offset`	integer	0	Number of rows to skip
`{column}`	string	-	Filter condition (e.g., `status=eq.active`)

Insert Rows

Insert one or more rows into a table.

TypeScript

Python

cURL

// Insert a single row
const response = await client.databases.insert(
  "your-database-asset-id",
  "users",
  {
    data: {
      name: "John Doe",
      email: "john@example.com",
      status: "active",
    },
    returnRepresentation: true, // Return the inserted row
  }
);
console.log(response.data);
// [{ id: 1, name: "John Doe", email: "john@example.com", status: "active" }]
// Insert multiple rows
const bulkResponse = await client.databases.insert(
  "your-database-asset-id",
  "users",
  {
    data: [
      { name: "Alice", email: "alice@example.com", status: "active" },
      { name: "Bob", email: "bob@example.com", status: "pending" },
    ],
    returnRepresentation: true,
  }
);

Update Rows

Update rows that match filter conditions.

Safety Feature: Filters are required by default to prevent accidental bulk updates. To update all rows intentionally, you must pass force=true.

TypeScript

Python

cURL

// Update rows matching a filter - pass filter conditions via queryParams
const response = await client.databases.update(
  "your-database-asset-id",
  "users",
  {
    data: {
      status: "inactive",
      updated_at: new Date().toISOString(),
    },
    return_representation: true,
  },
  {
    // Filter: update users where status is 'pending' and created more than 30 days ago
    queryParams: {
      status: "eq.pending",
      created_at: "lt.2024-01-01",
    }
  }
);
console.log(`Updated ${response.data?.length} rows`);
// Update all rows (requires force=true)
const allResponse = await client.databases.update(
  "your-database-asset-id",
  "users",
  {
    force: true,
    data: { last_sync: new Date().toISOString() },
  }
);

Delete Rows

Delete rows that match filter conditions.

Safety Feature: Filters are required by default to prevent accidental bulk deletes. To delete all rows intentionally, you must pass force=true.

TypeScript

Python

cURL

// Delete rows matching a filter - pass filter conditions via queryParams
const response = await client.databases.delete(
  "your-database-asset-id",
  "users",
  {
    body: { return_representation: true }, // Return deleted rows
  },
  {
    // Filter: delete users where status is 'deleted' and older than 90 days
    queryParams: {
      status: "eq.deleted",
      deleted_at: "lt.2024-01-01",
    }
  }
);
console.log(`Deleted ${response.data?.length} rows`);
// Delete a specific row by ID
const singleDelete = await client.databases.delete(
  "your-database-asset-id",
  "users",
  {},
  {
    queryParams: { id: "eq.123" }
  }
);
// Delete all rows (requires force=true) - USE WITH CAUTION!
const allDelete = await client.databases.delete(
  "your-database-asset-id",
  "temp_logs",
  { force: true }
);

Common Filter Examples

Filter by Status

# Active users only
?status=eq.active
# Users that are NOT deleted
?status=neq.deleted
# Users with pending or review status
?status=in.(pending,review)

Filter by Date/Time

# Created after a specific date
?created_at=gt.2024-01-01
# Created within a date range
?created_at=gte.2024-01-01&created_at=lt.2024-02-01
# Records with no deletion date (not deleted)
?deleted_at=is.null

Filter by Numeric Values

# Products under $100
?price=lt.100
# Products between $50 and $200
?price=gte.50&price=lte.200
# Orders with quantity greater than 10
?quantity=gt.10

Filter by Text (Pattern Matching)

# Email addresses from a specific domain
?email=ilike.*@company.com
# Names starting with "John"
?name=like.John*
# Descriptions containing "premium" (case-insensitive)
?description=ilike.*premium*

Complex Filters

# Active premium users created this year
?status=eq.active&tier=eq.premium&created_at=gte.2024-01-01
# Pending orders over $100 from verified customers
?order_status=eq.pending&total=gt.100&customer_verified=is.true
# Products in multiple categories with stock
?category=in.(electronics,accessories)&stock=gt.0

Pagination

Use limit and offset for pagination:

TypeScript

Python

const pageSize = 20;
let page = 0;
let hasMore = true;
while (hasMore) {
  const response = await client.databases.select(
    "your-database-asset-id",
    "users",
    {
      limit: pageSize,
      offset: page * pageSize,
      order: "id.asc",
    }
  );
  console.log(`Page ${page + 1}:`, response.data);
  hasMore = response.data.length === pageSize;
  page++;
}

Additional Operations

List Tables

Get a list of all tables in the database:

TypeScript

Python

const tables = await client.databases.listTables("your-database-asset-id");
console.log(tables.tables);
// [{ name: "users", schema_name: "public", row_count: 1000 }, ...]

Get Table Schema

Get column information for a specific table:

TypeScript

Python

const schema = await client.databases.getTableSchema(
  "your-database-asset-id",
  "users"
);
console.log(schema.columns);
// [{ name: "id", data_type: "integer", is_nullable: false }, ...]

Check Database Status

Check if a serverless database is running or suspended:

TypeScript

Python

const status = await client.databases.getStatus("your-database-asset-id");
console.log(status.status); // "running", "suspended", "starting", etc.

Error Handling

TypeScript

Python

import { AthenaIntelligenceError } from '@athenaintel/sdk';
try {
  const response = await client.databases.select(
    "your-database-asset-id",
    "users",
    {},
    { queryParams: { id: "eq.invalid" } }
  );
} catch (error) {
  if (error instanceof AthenaIntelligenceError) {
    switch (error.statusCode) {
      case 400:
        console.error("Bad request - check your filter syntax");
        break;
      case 403:
        console.error("Access denied to this database");
        break;
      case 404:
        console.error("Database or table not found");
        break;
      case 503:
        console.error("Database is starting up - retry in a moment");
        break;
      default:
        console.error("API error:", error.message);
    }
  }
}

Best Practices

Always use filters for UPDATE and DELETE - The API requires filters by default to prevent accidental bulk modifications. Only use force=true when you explicitly intend to affect all rows.
Use specific column selection - Instead of selecting all columns (*), specify only the columns you need with the select parameter for better performance.
Implement pagination - For large datasets, use limit and offset to paginate results rather than fetching all rows at once.
Handle 503 errors gracefully - Serverless databases may be suspended. Implement retry logic for 503 errors as the database wakes up.
Use appropriate operators - Choose the right operator for your use case:
- Use ilike for case-insensitive text searches
- Use in for checking against multiple values
- Use is.null instead of eq.null for NULL checks
Validate input - Always validate and sanitize any user input before using it in filter values to prevent injection attacks.