> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.athenaintel.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.athenaintel.com/_mcp/server.

# Database API

> Query, update, and delete data from your databases using PostgREST-style filters

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`

> **Note**
>
> **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"

```bash
# 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:

```bash
# 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

```typescript
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" }, ...]
```

#### Python

```python
from athena.client import Athena

client = Athena(api_key="your-api-key")

# Basic select - get all rows
response = client.databases.select(
    asset_id="your-database-asset-id",
    table_name="users"
)

# With filters and options
filtered_response = client.databases.select(
    asset_id="your-database-asset-id",
    table_name="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)
)

print(filtered_response.data)
# [{"id": 1, "name": "John", "email": "john@example.com"}, ...]
```

#### cURL

```bash
# Basic select
curl -X GET "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users" \
  -H "Authorization: Bearer YOUR_API_KEY"

# With filters
curl -X GET "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users?status=eq.active&age=gt.21&select=id,name,email&order=created_at.desc&limit=50" \
  -H "Authorization: Bearer YOUR_API_KEY"
```

### 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

```typescript
// 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,
  }
);
```

#### Python

```python
# Insert a single row
response = client.databases.insert(
    asset_id="your-database-asset-id",
    table_name="users",
    data={
        "name": "John Doe",
        "email": "john@example.com",
        "status": "active",
    },
    return_representation=True,  # Return the inserted row
)

print(response.data)
# [{"id": 1, "name": "John Doe", "email": "john@example.com", "status": "active"}]

# Insert multiple rows
bulk_response = client.databases.insert(
    asset_id="your-database-asset-id",
    table_name="users",
    data=[
        {"name": "Alice", "email": "alice@example.com", "status": "active"},
        {"name": "Bob", "email": "bob@example.com", "status": "pending"},
    ],
    return_representation=True,
)
```

#### cURL

```bash
# Insert a single row
curl -X POST "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "data": {
      "name": "John Doe",
      "email": "john@example.com",
      "status": "active"
    },
    "return_representation": true
  }'

# Insert multiple rows
curl -X POST "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "data": [
      {"name": "Alice", "email": "alice@example.com", "status": "active"},
      {"name": "Bob", "email": "bob@example.com", "status": "pending"}
    ],
    "return_representation": true
  }'
```

## Update Rows

Update rows that match filter conditions.

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

#### TypeScript

```typescript
// 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() },
  }
);
```

#### Python

```python
# Update rows matching a filter
response = client.databases.update(
    asset_id="your-database-asset-id",
    table_name="users",
    data={
        "status": "inactive",
        "updated_at": "2024-01-15T10:00:00Z",
    },
    return_representation=True,
    # Filter parameters
    status="eq.pending",
    created_at="lt.2024-01-01",
)

print(f"Updated {len(response.data)} rows")

# Update all rows (requires force=True)
all_response = client.databases.update(
    asset_id="your-database-asset-id",
    table_name="users",
    data={"last_sync": "2024-01-15T10:00:00Z"},
    force=True,
)
```

#### cURL

```bash
# Update rows matching a filter
curl -X PATCH "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users?status=eq.pending&created_at=lt.2024-01-01" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "data": {
      "status": "inactive",
      "updated_at": "2024-01-15T10:00:00Z"
    },
    "return_representation": true
  }'

# Update all rows (requires force=true)
curl -X PATCH "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users?force=true" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "data": {
      "last_sync": "2024-01-15T10:00:00Z"
    }
  }'
```

## Delete Rows

Delete rows that match filter conditions.

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

#### TypeScript

```typescript
// 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 }
);
```

#### Python

```python
# Delete rows matching a filter
response = client.databases.delete(
    asset_id="your-database-asset-id",
    table_name="users",
    return_representation=True,  # Return deleted rows
    # Filter parameters
    status="eq.deleted",
    deleted_at="lt.2024-01-01",
)

print(f"Deleted {len(response.data)} rows")

# Delete a specific row by ID
single_delete = client.databases.delete(
    asset_id="your-database-asset-id",
    table_name="users",
    id="eq.123",
)

# Delete all rows (requires force=True) - USE WITH CAUTION!
all_delete = client.databases.delete(
    asset_id="your-database-asset-id",
    table_name="temp_logs",
    force=True,
)
```

#### cURL

```bash
# Delete rows matching a filter
curl -X DELETE "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users?status=eq.deleted&deleted_at=lt.2024-01-01" \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"return_representation": true}'

# Delete a specific row by ID
curl -X DELETE "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/users?id=eq.123" \
  -H "Authorization: Bearer YOUR_API_KEY"

# Delete all rows (requires force=true) - USE WITH CAUTION!
curl -X DELETE "https://api.athenaintel.com/api/v0/databases/{asset_id}/data/temp_logs?force=true" \
  -H "Authorization: Bearer YOUR_API_KEY"
```

## Common Filter Examples

### Filter by Status

```bash
# 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

```bash
# 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

```bash
# 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)

```bash
# 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

```bash
# 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

```typescript
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++;
}
```

#### Python

```python
page_size = 20
page = 0
has_more = True

while has_more:
    response = client.databases.select(
        asset_id="your-database-asset-id",
        table_name="users",
        limit=page_size,
        offset=page * page_size,
        order="id.asc",
    )

    print(f"Page {page + 1}:", response.data)

    has_more = len(response.data) == page_size
    page += 1
```

## Additional Operations

### List Tables

Get a list of all tables in the database:

#### TypeScript

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

#### Python

```python
tables = client.databases.list_tables(asset_id="your-database-asset-id")
print(tables.tables)
# [{"name": "users", "schema_name": "public", "row_count": 1000}, ...]
```

### Get Table Schema

Get column information for a specific table:

#### TypeScript

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

#### Python

```python
schema = client.databases.get_table_schema(
    asset_id="your-database-asset-id",
    table_name="users"
)
print(schema.columns)
# [{"name": "id", "data_type": "integer", "is_nullable": False}, ...]
```

### Check Database Status

Check if a serverless database is running or suspended:

#### TypeScript

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

#### Python

```python
status = client.databases.get_status(asset_id="your-database-asset-id")
print(status.status)  # "running", "suspended", "starting", etc.
```

## Error Handling

#### TypeScript

```typescript
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);
    }
  }
}
```

#### Python

```python
from athena.core.api_error import ApiError

try:
    response = client.databases.select(
        asset_id="your-database-asset-id",
        table_name="users",
        id="eq.invalid"
    )
except ApiError as error:
    if error.status_code == 400:
        print("Bad request - check your filter syntax")
    elif error.status_code == 403:
        print("Access denied to this database")
    elif error.status_code == 404:
        print("Database or table not found")
    elif error.status_code == 503:
        print("Database is starting up - retry in a moment")
    else:
        print(f"API error: {error.body}")
```

## Best Practices

1. **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.

2. **Use specific column selection** - Instead of selecting all columns (`*`), specify only the columns you need with the `select` parameter for better performance.

3. **Implement pagination** - For large datasets, use `limit` and `offset` to paginate results rather than fetching all rows at once.

4. **Handle 503 errors gracefully** - Serverless databases may be suspended. Implement retry logic for 503 errors as the database wakes up.

5. **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

6. **Validate input** - Always validate and sanitize any user input before using it in filter values to prevent injection attacks.