# Database REST API

Query your PostgreSQL databases directly from your application using a REST API.

Query your PostgreSQL databases directly from your application using a REST API.

## Overview

The Database REST API lets you perform CRUD operations on your database tables using HTTP requests.

| Feature               | Description                                  |
| --------------------- | -------------------------------------------- |
| Frontend applications | Query databases directly from browser/mobile |
| Quick prototyping     | No SQL knowledge required                    |
| Row-level security    | Your RLS policies apply to user requests     |
| Type-safe             | Structured queries with validation           |

**Authentication:** Requires an auth user access token (obtained via signup/signin)

**Endpoint:** `POST /databases/{databaseName}/query/{operation}`

To run the same operations against a branch instead of the database itself, put
the branch in the path: `POST
/databases/{databaseName}/branches/{branchName}/query/{operation}`. Everything on
this page applies unchanged. See [Branching](https://volcano.dev/docs/platform/databases/branching.md).

A request body can be up to 4 MiB. A larger one is refused with `413` and
`{"error": "request body too large"}`. A smaller body that packs in very many
small JSON values, such as more than about 27,000 small objects or 340,000
numbers, is refused with `413` and
`{"error": "request body has too many JSON objects, arrays or values"}`. Keep
large files in [Storage](https://volcano.dev/docs/platform/storage/overview.md) and store their path in the
row instead.

---

## Getting Started

### 1. Sign Up a User

```javascript
const response = await fetch('https://api.yourapp.com/auth/signup', {
  method: 'POST',
  headers: {
    Authorization: 'Bearer YOUR_ANON_KEY',
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    email: 'user@example.com',
    password: 'securePassword123',
  }),
});

const { access_token } = await response.json();
// Save this access_token for database queries
```

### 2. Query Your Database

```javascript
const response = await fetch('https://api.yourapp.com/databases/notes_db/query/select', {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${access_token}`, // ← Your access token (proves your identity!)
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    table: 'posts',
    select: ['id', 'title', 'content'],
    filters: [{ column: 'status', operator: 'eq', value: 'published' }],
    order: [{ column: 'created_at', ascending: false }],
    limit: 10,
  }),
});

const { data } = await response.json();
console.log('Posts:', data);
// Data is automatically filtered to your posts only
// The access_token identifies you to the database
// auth.uid() returns your user_id
// RLS filters the results
```

---

## Operations

### SELECT - Query Data

**Endpoint:** `POST /databases/{databaseName}/query/select`

**Request Body:**

```json
{
  "table": "posts",
  "select": ["id", "title", "content"],
  "filters": [
    { "column": "status", "operator": "eq", "value": "published" },
    { "column": "views", "operator": "gt", "value": 100 }
  ],
  "order": [{ "column": "created_at", "ascending": false }],
  "limit": 20,
  "offset": 0
}
```

**Response:**

```json
{
  "data": [
    {
      "id": "uuid",
      "title": "My Post",
      "content": "Post content here",
      "created_at": "2026-01-13T10:00:00Z"
    }
  ],
  "count": 1
}
```

**Parameters:**

- `table` (required): Table name
- `select` (optional): Columns to return (omit for `*`)
- `filters` (optional): WHERE conditions
- `order` (optional): ORDER BY clauses
- `limit` (optional): Maximum rows to return
- `offset` (optional): Number of rows to skip (pagination)

---

### INSERT - Create Data

**Endpoint:** `POST /databases/{databaseName}/query/insert`

**Request Body:**

```json
{
  "table": "posts",
  "values": {
    "title": "My New Post",
    "content": "This is the content",
    "status": "draft"
  }
}
```

**Response:**

```json
{
  "data": [
    {
      "id": "uuid",
      "title": "My New Post",
      "content": "This is the content",
      "status": "draft",
      "user_id": "user-uuid",
      "created_at": "2026-01-13T10:00:00Z"
    }
  ],
  "count": 1
}
```

**Note:** If your table has a trigger that auto-sets `user_id` using `auth.uid()`, it will be set to the authenticated user automatically!

JSON objects and arrays in `values` are stored as JSON, so send them to `json` or `jsonb` columns. For a Postgres array column such as `text[]`, send an array literal string like `"{support,urgent}"`. Update `values` follow the same rule.

---

### UPDATE - Modify Data

**Endpoint:** `POST /databases/{databaseName}/query/update`

**Request Body:**

```json
{
  "table": "posts",
  "values": {
    "title": "Updated Title",
    "status": "published"
  },
  "filters": [{ "column": "id", "operator": "eq", "value": "post-uuid" }]
}
```

**Response:**

```json
{
  "data": [
    {
      "id": "post-uuid",
      "title": "Updated Title",
      "status": "published",
      "updated_at": "2026-01-13T10:05:00Z"
    }
  ],
  "count": 1
}
```

**Security:** Row-Level Security ensures you can only update your own data!

---

### DELETE - Remove Data

**Endpoint:** `POST /databases/{databaseName}/query/delete`

**Request Body:**

```json
{
  "table": "posts",
  "filters": [{ "column": "id", "operator": "eq", "value": "post-uuid" }]
}
```

**Response:**

```json
{
  "data": [
    {
      "id": "post-uuid",
      "title": "Deleted Post"
    }
  ],
  "count": 1
}
```

**Safety:** UPDATE and DELETE require at least one filter to prevent accidental mass changes.

**Security:** Row-Level Security ensures you can only delete your own data!

---

## Filter Operators

| Operator | SQL Equivalent | Example                                                                              |
| -------- | -------------- | ------------------------------------------------------------------------------------ |
| `eq`     | `=`            | `{ "column": "status", "operator": "eq", "value": "published" }`                     |
| `neq`    | `<>`           | `{ "column": "status", "operator": "neq", "value": "draft" }`                        |
| `gt`     | `>`            | `{ "column": "views", "operator": "gt", "value": 100 }`                              |
| `gte`    | `>=`           | `{ "column": "views", "operator": "gte", "value": 100 }`                             |
| `lt`     | `<`            | `{ "column": "price", "operator": "lt", "value": 50 }`                               |
| `lte`    | `<=`           | `{ "column": "price", "operator": "lte", "value": 50 }`                              |
| `like`   | `LIKE`         | `{ "column": "title", "operator": "like", "value": "%search%" }`                     |
| `ilike`  | `ILIKE`        | `{ "column": "title", "operator": "ilike", "value": "%search%" }` (case-insensitive) |
| `is`     | `IS NULL`      | `{ "column": "deleted_at", "operator": "is", "value": null }`                        |
| `in`     | `IN`           | `{ "column": "id", "operator": "in", "value": ["id1", "id2"] }`                      |

---

## Complete Examples

### E-commerce Order Search

```javascript
const response = await fetch(`${apiUrl}/databases/${databaseName}/query/select`, {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${access_token}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    table: 'orders',
    select: ['id', 'order_number', 'customer_name', 'total', 'status', 'created_at'],
    filters: [
      { column: 'status', operator: 'neq', value: 'cancelled' },
      { column: 'total', operator: 'gte', value: 50 },
      { column: 'created_at', operator: 'gte', value: '2026-01-01' },
      { column: 'customer_name', operator: 'ilike', value: '%smith%' },
    ],
    order: [{ column: 'created_at', ascending: false }],
    limit: 100,
  }),
});

const { data, count } = await response.json();
console.log(`Found ${count} orders:`, data);
```

### Social Media Feed

```javascript
const response = await fetch(`${apiUrl}/databases/${databaseName}/query/select`, {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${access_token}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    table: 'posts',
    select: ['id', 'content', 'like_count', 'comment_count', 'created_at'],
    filters: [
      { column: 'like_count', operator: 'gt', value: 10 },
      { column: 'visibility', operator: 'eq', value: 'public' },
      { column: 'deleted_at', operator: 'is', value: null },
    ],
    order: [
      { column: 'like_count', ascending: false },
      { column: 'created_at', ascending: false },
    ],
    limit: 50,
  }),
});

const { data } = await response.json();
// data is automatically filtered to posts user has access to (RLS)
```

### Create a Post

```javascript
const response = await fetch(`${apiUrl}/databases/${databaseName}/query/insert`, {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${access_token}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    table: 'posts',
    values: {
      title: 'My New Post',
      content: 'This is my first post!',
      status: 'published',
    },
  }),
});

const { data } = await response.json();
const newPost = data[0];
console.log('Created post:', newPost.id);
// user_id is automatically set to your user ID!
```

### Update a Post

```javascript
const response = await fetch(`${apiUrl}/databases/${databaseName}/query/update`, {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${access_token}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    table: 'posts',
    values: {
      title: 'Updated Title',
      status: 'published',
    },
    filters: [{ column: 'id', operator: 'eq', value: postId }],
  }),
});

const { data } = await response.json();
// RLS ensures you can only update your own posts
```

### Delete a Post

```javascript
const response = await fetch(`${apiUrl}/databases/${databaseName}/query/delete`, {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${access_token}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    table: 'posts',
    filters: [{ column: 'id', operator: 'eq', value: postId }],
  }),
});

const { data } = await response.json();
// RLS ensures you can only delete your own posts
```

---

## Using with Volcano SDK

The `@volcano.dev/sdk` makes this even simpler:

```javascript
import { VolcanoAuth } from '@volcano.dev/sdk';

const volcano = new VolcanoAuth({
  apiUrl: 'https://api.yourapp.com',
  anonKey: 'your-anon-key',
});

// Sign in
await volcano.auth.signIn({
  email: 'user@example.com',
  password: 'password',
});

// Query database (future SDK feature)
const posts = await volcano
  .from('posts')
  .select('id, title, content')
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .limit(10);
```

---

## Row-Level Security

A request with a user's access token runs as `authenticated`, so the RLS policies on your tables apply to it. A request with a service key holding `*` bypasses RLS. Until Volcano applies [service key scopes](https://volcano.dev/docs/platform/authentication/security/service-keys.md#scope-a-service-key) to the database API, so does every other service key; after that, a key scoped without `*` gets `403`. RLS applies only to tables where you enable it: on a table without RLS, any user session can read, change, and delete every row. See [Default grants](https://volcano.dev/docs/platform/databases/database-roles.md#default-grants).

### How It Works

1. **You sign in** - Receive an access token with your user ID
2. **You query** - Send access token with request
3. **Volcano validates** - Checks your token and identity
4. **Your identity is injected** - `auth.uid()` returns your user ID
5. **RLS filters data** - On tables with RLS enabled, you see only what your policies allow

### Example RLS Policy

```sql
-- Create policy on your table
CREATE POLICY "users_own_posts" ON posts
  FOR ALL
  USING (user_id = auth.uid());
```

**Result:** Each user automatically sees only their own posts!

### No manual work required

You don't need to:

- Manually add `WHERE user_id = $1` to queries
- Pass user ID in every request
- Worry about forgetting security filters

Volcano automatically:

- Injects your user ID into the database session
- Ensures `auth.uid()` returns your ID
- Lets RLS policies filter data

---

## Pagination

Combine `limit` and `offset` for pagination:

```javascript
// Page 1 (rows 1-10)
const page1 = await query({
  table: 'posts',
  limit: 10,
  offset: 0,
});

// Page 2 (rows 11-20)
const page2 = await query({
  table: 'posts',
  limit: 10,
  offset: 10,
});

// Page 3 (rows 21-30)
const page3 = await query({
  table: 'posts',
  limit: 10,
  offset: 20,
});
```

---

## Ordering

### Single Column

```json
{
  "table": "posts",
  "order": [{ "column": "created_at", "ascending": false }]
}
```

### Multiple Columns

```json
{
  "table": "posts",
  "order": [
    { "column": "like_count", "ascending": false },
    { "column": "created_at", "ascending": false }
  ]
}
```

### With NULL Handling

```json
{
  "table": "posts",
  "order": [{ "column": "priority", "ascending": false, "nulls_first": true }]
}
```

---

## Advanced Filtering

### Multiple Conditions (AND)

All filters are combined with AND:

```json
{
  "table": "products",
  "filters": [
    { "column": "category", "operator": "eq", "value": "electronics" },
    { "column": "price", "operator": "gte", "value": 100 },
    { "column": "price", "operator": "lte", "value": 500 },
    { "column": "in_stock", "operator": "eq", "value": true }
  ]
}
```

SQL equivalent:

```sql
WHERE category = 'electronics'
  AND price >= 100
  AND price <= 500
  AND in_stock = true
```

### Case-Insensitive Search

```json
{
  "table": "users",
  "filters": [{ "column": "name", "operator": "ilike", "value": "%john%" }]
}
```

Matches: "John", "JOHN", "john", "Johnny", etc.

### NULL Values

```json
{
  "table": "posts",
  "filters": [{ "column": "deleted_at", "operator": "is", "value": null }]
}
```

Returns only posts where `deleted_at IS NULL` (not deleted).

### Date Ranges

```json
{
  "table": "orders",
  "filters": [
    { "column": "created_at", "operator": "gte", "value": "2026-01-01" },
    { "column": "created_at", "operator": "lt", "value": "2026-02-01" }
  ]
}
```

Returns orders created in January 2026.

---

## Common Patterns

### Get User's Recent Posts

```json
{
  "table": "posts",
  "select": ["id", "title", "created_at"],
  "order": [{ "column": "created_at", "ascending": false }],
  "limit": 10
}
```

With RLS enabled on `posts` and a policy like the one above, this returns only the current user's posts.

### Search Posts by Title

```json
{
  "table": "posts",
  "filters": [{ "column": "title", "operator": "ilike", "value": "%search term%" }]
}
```

### Get Popular Posts

```json
{
  "table": "posts",
  "filters": [
    { "column": "like_count", "operator": "gt", "value": 100 },
    { "column": "status", "operator": "eq", "value": "published" }
  ],
  "order": [{ "column": "like_count", "ascending": false }],
  "limit": 50
}
```

### Update User Profile

```json
{
  "table": "profiles",
  "values": {
    "bio": "Updated bio",
    "avatar_url": "https://example.com/avatar.jpg"
  },
  "filters": [{ "column": "id", "operator": "eq", "value": "profile-uuid" }]
}
```

---

## Error Handling

### Authentication Errors

```json
// 401 Unauthorized
{
  "error": "Authentication required for database queries"
}
```

**Fix:** Include valid access token in Authorization header

### Invalid Query

```json
// 400 Bad Request
{
  "error": "Failed to build query: invalid table name"
}
```

**Fix:** Ensure table and column names are valid identifiers

### Database Errors

```json
// 400 Bad Request
{
  "error": "Query failed: relation \"posts\" does not exist"
}
```

**Fix:** Ensure table exists in your database

Unexpected connection failures return `500 Internal Server Error` with a
generic message and an `X-Request-Id` response header:

```json
{
  "error": "Query failed"
}
```

Use the request ID when contacting support. Volcano keeps connection details
in server logs and does not return them to clients.

If the database ends the connection under a `select` or `ping`, for example
when it scales to zero or restarts, Volcano runs the query once more on another
connection, inside a read-only transaction, before it answers. Volcano never
reruns `insert`, `update`, or `delete`, because the database may already have
applied the change. A `select` that writes, such as one on a view over a
function that inserts rows, fails the read-only rerun instead of writing twice.
These return the `500` above; retry them only when running the change twice is
safe.

### Permission Denied (RLS)

```json
// 200 OK with empty results
{
  "data": [],
  "count": 0
}
```

Row-Level Security silently filters out data you don't have access to.

---

## Security

### Automatic protection

| Protection               | Description                        |
| ------------------------ | ---------------------------------- |
| Row-level security       | Applied on tables with RLS enabled |
| SQL injection prevention | All inputs validated               |
| Parameterized queries    | Values never interpolated          |
| Identity verification    | JWT validated on every request     |

### What's protected

**Your identity:**

- Extracted from your JWT access token
- Cannot be spoofed or modified
- Automatically used in RLS policies

**Your data:**

- RLS policies filter automatically
- You can only see/modify data you own
- No manual security checks needed

### Best practices

1. **Always use RLS policies** on tables with user data

   ```sql
   ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
   CREATE POLICY "users_own_posts" ON posts
     FOR ALL USING (user_id = auth.uid());
   ```

2. **Auto-set user_id** on INSERT using triggers

   ```sql
   CREATE TRIGGER set_user_id
     BEFORE INSERT ON posts
     FOR EACH ROW
     EXECUTE FUNCTION set_user_id_trigger();
   ```

3. **Validate input** in your application before sending queries

---

## When to use REST API vs Direct SQL

### Use REST API when:

| Use case               | Description                                |
| ---------------------- | ------------------------------------------ |
| Simple CRUD operations | SELECT, INSERT, UPDATE, DELETE             |
| Frontend applications  | Browser/mobile can't use direct PostgreSQL |
| Quick prototyping      | No SQL knowledge required                  |
| Standard queries       | Filtering, sorting, pagination             |

### Use Direct SQL when:

| Use case              | Description                               |
| --------------------- | ----------------------------------------- |
| Complex queries       | JOINs, subqueries, CTEs, window functions |
| Full PostgreSQL power | Advanced features and extensions          |
| ORM integration       | Sequelize, Prisma, TypeORM, etc.          |
| Transactions          | Multi-step operations                     |
| Performance           | Direct connection is faster               |

Both methods use the same RLS enforcement.

---

## Response headers

Every query response reports where its time went:

- `X-Volcano-Proxy-Ms`: milliseconds Volcano spent before running the query,
  counted from the moment your request arrived — authenticating the caller,
  validating the request, resolving the database, and building the SQL.
- `X-Volcano-Proxy-Handler-Ms`: the part of `X-Volcano-Proxy-Ms` spent in the
  query endpoint itself. Subtract it from `X-Volcano-Proxy-Ms` to see what
  authentication and request validation cost.
- `X-Volcano-Compute-Ms`: milliseconds the database took to run the query and
  return its rows.

All three are present whether the query succeeded or failed, so a slow
statement and a slow request are easy to tell apart.

```bash
curl -sD - -X POST "https://api.volcano.dev/databases/$DATABASE/query/select" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"table":"posts"}' -o /dev/null | grep -i x-volcano
```

```text
X-Volcano-Proxy-Ms: 6
X-Volcano-Proxy-Handler-Ms: 2
X-Volcano-Compute-Ms: 14
```

This query waited 6 ms on Volcano — 4 ms of it before the query endpoint was
reached — and 14 ms on the database.

---

## Limits

- **Tables:** Must exist in your database
- **Columns:** Must be valid identifiers (alphanumeric + underscore)
- **Operators:** See supported operators table above
- **Filters:** Combined with AND (OR not yet supported)
- **UPDATE/DELETE:** Requires at least one filter for safety
- **Connection capacity:** Each server shares up to 32 REST database connections across callers and accepts at most 32 active or waiting requests, with at most 20 requests from one project and at most 20 across all projects owned by one platform account. Connections remain specific to their authenticated identity. Under pressure, idle connections can be reclaimed without closing checked-out work. Acquiring a connection waits up to five seconds, or your earlier cancellation/deadline. A full request queue or acquisition failure returns `500 Internal Server Error` with `"error": "Failed to connect to database"`. Bound application concurrency and retry connection failures with bounded backoff. REST connections also share the [native connection limits](https://volcano.dev/docs/platform/databases/direct-connection.md#keep-connection-pools-within-capacity).
- **Query time:** A query must finish within 60 seconds. Volcano cancels one
  that runs longer and answers `500 Internal Server Error` with
  `"error": "Query failed"`.

---

## What's next

| Guide                                                    | Description                       |
| -------------------------------------------------------- | --------------------------------- |
| [Authentication flow](https://volcano.dev/docs/platform/databases/authentication-flow.md)            | How tokens are passed to database |
| [Query Builder API](https://volcano.dev/docs/platform/databases/query-builder-api.md)                | Easier query builder syntax       |
| [Row-level security](https://volcano.dev/docs/platform/databases/row-level-security.md)              | Secure your tables                |
| [Auth helpers](https://volcano.dev/docs/platform/databases/auth-helpers.md)                          | Using auth.uid(), auth.email()    |
| [Authentication overview](https://volcano.dev/docs/platform/authentication/overview.md) | User signup/signin                |

---

## FAQ

**Q: Can I use this from the browser?**  
A: Yes! That's exactly what it's designed for.

**Q: Is my data secure?**  
A: Requests with a user's access token run as `authenticated`, so your RLS policies apply. Enable RLS on every table first; a table without it is open to any user session. See [Default grants](https://volcano.dev/docs/platform/databases/database-roles.md#default-grants).

**Q: Do I need to write SQL?**  
A: No! The REST API handles SQL generation for you.

**Q: Can I do complex queries with JOINs?**  
A: For complex queries, use functions with direct SQL instead.

**Q: How fast is it?**  
A: Very fast! Direct connection to your database with connection pooling.

**Q: Do I need a backend server?**  
A: No! Call the REST API directly from your frontend.
