# Server data API

> Read and write your project's tables over HTTP from server-side code, with PostgREST-style filters, related rows, paging and upserts.

Source: https://docs.fortly.com/data/server-api

Your server-side code can read and write the project's tables with plain HTTP requests instead of a database driver. Fortly serves one query language, PostgREST's (the one behind Supabase's REST API), in three places. Which one your code calls depends on how your project runs. This page shows how to reach each one, then describes the query language they share.

## Which route your code uses

New deploys run as **static sites** or as **Workers** (server frameworks that Fortly builds, such as Next.js and Nuxt). A project deployed earlier may still run as a **long-running process**, and it keeps that runtime until it is deployed again.

| Your project runs as | Read and write rows with | Credential |
| --- | --- | --- |
| A Worker | The [HTTP API table routes](#from-a-worker), `https://api.fortly.com/projects/{name}/tables/{table}/rows` | An API token, kept in a runtime secret |
| A Worker, for tables you've opened publicly | The [frontend data API](https://docs.fortly.com/data/frontend-api), `/_data/{table}` on the project's own address | The publishable key |
| A long-running process | The [loopback API](#from-a-long-running-process), `$FORTLY_API/{table}`, or a direct connection through `DATABASE_URL` | `FORTLY_TOKEN`, set by Fortly |
| Anywhere else: a script, CI, your laptop | The HTTP API table routes | An API token |

A Worker has no `DATABASE_URL` and no loopback API. Fortly gives it only the project's own runtime values (see [Environment and secrets](https://docs.fortly.com/deploy/environment)), so it reaches its data over HTTPS like any other client.

## From a Worker

Make an API token in the console under **Settings › API tokens**, and store it as a runtime value under a name of your own, such as `FORTLY_API_TOKEN`. `FORTLY_TOKEN` is reserved and can't be used. A Worker receives the runtime values whose names existed when it was built, so deploy again after you add the name.

> **Warning:**
>
> An API token acts as your account. It can change any project you can reach, and delete one. Keep it in a `runtime` value, never in a `build` value or a `NEXT_PUBLIC_` variable, and never send it to a browser. If a page only needs data you're happy to make public, use the [frontend data API](https://docs.fortly.com/data/frontend-api) with the publishable key instead.

Then call the table routes from your server code. In a Next.js route handler:

```js title="app/api/orders/route.js"
const BASE = "https://api.fortly.com/projects/my-app-ada/tables";

export async function GET() {
  const params = new URLSearchParams({
    status: "eq.paid",
    select: "id,total,created_at,customers(name)",
    order: "created_at.desc",
    limit: "20",
  });
  const res = await fetch(`${BASE}/orders/rows?${params}`, {
    headers: { Authorization: `Bearer ${process.env.FORTLY_API_TOKEN}` },
  });
  const body = await res.json();
  if (!res.ok) return Response.json({ error: body.error }, { status: 502 });
  return Response.json(body.rows);
}
```

`my-app-ada` is the project's full name: the name you chose plus your account's slug. See [Project names in paths](https://docs.fortly.com/api#project-names-in-paths).

- `GET /projects/{name}/tables/{table}/rows` — Read rows. The answer is an object with rows, limit, offset and total
- `POST /projects/{name}/tables/{table}/rows` — Add a row, or an array of rows in one transaction. Answers 201 with the stored rows
- `PATCH /projects/{name}/tables/{table}/rows` — Change the rows a filter matches. A filter is required
- `DELETE /projects/{name}/tables/{table}/rows` — Remove the rows a filter matches. A filter is required
- `POST /projects/{name}/routines/{routine}/call` — Call a function or procedure

These routes take the same [filters](#filters), [`select`](#choose-columns-and-related-rows) and [`order`](#sort-and-page) as the other two. They differ in a few ways:

- A read returns `{"rows": [...], "limit": 50, "offset": 0, "total": 148}`. It always includes the total matching your filters.
- `limit` defaults to 50 and is clamped to between 1 and 200.
- An insert answers `201`. There is no [upsert](#upsert) on these routes.
- A routine call returns up to 500 rows.

Every route is listed in the [API reference](https://docs.fortly.com/api/data).

## From a long-running process

A project that runs as a process has two more ways in. Fortly starts the process with these variables:

| Variable | What it holds |
| --- | --- |
| `FORTLY_API` | The loopback API's base URL, including `/v1`, for example `http://127.0.0.1:9100/v1`. Read it from the environment rather than hard-coding the port |
| `FORTLY_TOKEN` | The project's own token: 48 hex characters, no prefix. It works only on this loopback API, and only for its own project |
| `DATABASE_URL` and the `PG*` variables | A direct PostgreSQL connection. See [Connect from your app](https://docs.fortly.com/data#connect-from-your-app) |

> **Note:**
>
> Inside the process, `FORTLY_TOKEN` is the project token. It is not the `fly_…` account token that the [HTTP API](https://docs.fortly.com/api) examples on this site keep in a variable of the same name.

- `GET /v1/{table}` — Read rows. The answer is a JSON array
- `POST /v1/{table}` — Add a row, or an array of rows in one transaction. Upsert with a Prefer header
- `PATCH /v1/{table}` — Change the rows a filter matches. A filter is required
- `DELETE /v1/{table}` — Remove the rows a filter matches. A filter is required
- `POST /v1/sql` — Run create, alter and drop statements
- `POST /v1/rpc/{routine}` — Call a function or procedure

```js
const params = new URLSearchParams({ status: "eq.paid", order: "created_at.desc", limit: "20" });
const res = await fetch(`${process.env.FORTLY_API}/orders?${params}`, {
  headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` },
});
if (!res.ok) throw new Error((await res.json()).error);
const orders = await res.json(); // an array of rows
```

On the loopback API, `limit` defaults to 100 and can be up to 1000. A read returns a JSON array and no total: `count=exact` is accepted but ignored. `POST` and `PATCH` must send `Content-Type: application/json`, or the request is refused with `415`.

The loopback address can't be reached from outside Fortly, and the project token can read, change and delete any row. Keep both in server-side code.

## What the routes serve

All three routes serve the ordinary tables in your database's `public` schema. Views and tables in other schemas aren't served. Read them with SQL in a [routine](#call-a-routine), or through `DATABASE_URL` from a process.

The examples below use the HTTP API route, which works from every runtime. The query string is the same on all three routes.

## Filters

A filter is a query parameter named after a column, whose value is an operator and a value joined by a dot:

```text
column=operator.value
```

```bash
curl "https://api.fortly.com/projects/my-app-ada/tables/orders/rows?status=eq.paid&total=gte.100" \
  -H "Authorization: Bearer $FORTLY_TOKEN"
```

| Operator | Meaning | Example |
| --- | --- | --- |
| `eq` | equals | `status=eq.paid` |
| `neq` | does not equal | `status=neq.cancelled` |
| `gt` | greater than | `total=gt.100` |
| `gte` | greater than or equal | `created_at=gte.2026-10-01` |
| `lt` | less than | `total=lt.20` |
| `lte` | less than or equal | `created_at=lte.2026-10-31T23:59:59Z` |
| `like` | matches a pattern, case-sensitive | `email=like.*@example.com` |
| `ilike` | matches a pattern, ignoring case | `name=ilike.*lovelace*` |
| `in` | is one of a list | `id=in.(1,2,3)` |
| `is` | is `null`, `true` or `false` | `shipped_at=is.null` |
| `fts` | contains these words (see [full-text search](#full-text-search)) | `body=fts.fast delivery` |
| `not.` | negates any operator above | `status=not.in.(cancelled,refunded)` |

How filters behave:

- **Every condition must match.** Filters on different columns are combined with `and`. There is no `or` parameter.
- **One filter per column.** Each column takes a single filter per request. If you repeat a column, the filters aren't combined. For a range or an `or`, write a [routine](#call-a-routine) and call it.
- **Values are bound, never pasted into SQL.** Each value is sent as a parameter and cast to the column's own type. `total=gte.100` compares numbers, and `created_at=gte.2026-10-01` compares timestamps.
- **`*` is the wildcard** in `like` and `ilike`, standing for any run of characters. It's a literal `*` in every other operator.
- **`in` takes a comma-separated list**, with or without the brackets. A value inside the list can't contain a comma.
- **`is` takes only `null`, `true` or `false`.** `not.is.null` means `is not null`.
- **URL-encode values** that contain spaces or reserved characters. `URLSearchParams` does it for you.
- **Columns are checked against the schema.** An unknown column is refused with `orders has no column totl`, and an unknown operator with `unknown operator eqq`.

The names `select`, `order`, `limit`, `offset` and `count` are reserved for the parameters below, so they're never read as column filters.

### Full-text search

`fts` behaves differently depending on the table:

- **When the table has a `tsvector` column**, and you filter on a text column, Fortly searches that `tsvector` with `plainto_tsquery`. This is a real full-text search that uses the index and matches words rather than substrings. It covers every column the `tsvector` was built from, not only the one you named.
- **Otherwise**, `fts` is a case-sensitive substring match on the column you named (`column::text like '%value%'`). It finds the same rows on a small table but scans every row on a large one.

To make a table searchable, send `POST /projects/{name}/tables/{table}/search` with the columns to cover, for example `{"columns": ["title", "body"]}`. Fortly adds a generated `tsvector` column named after the table (`posts_search`) using English stemming, plus a GIN index on it. Run it again with other columns to change what's searched. `DELETE` on the same route removes the column and its index and leaves your rows alone. Only the account owner or an admin can do either. See the [API reference](https://docs.fortly.com/api/data).

> **Note:**
>
> Adding the generated column rewrites the table, and it holds a lock while it does. On a large table, do it at a quiet time.

## Choose columns and related rows

`select` lists the columns you want, separated by commas. Without it, every column comes back:

```text
GET …/orders?select=id,total,status&status=eq.paid
```

To include rows from a related table, name that table with its columns in brackets. Use `*` for all of its columns:

```text
GET …/orders?select=id,total,customers(name,email)        each order with its customer
GET …/customers?select=id,name,orders(id,total,status)    each customer with all of their orders
```

```json
[
  { "id": 42, "total": 129.50, "customers": { "name": "Ada Lovelace", "email": "ada@example.com" } }
]
```

The related rows come back under the related table's name:

- **An object (or `null`)** when the row you asked for holds the foreign key. Each order points at one customer.
- **An array** when the related table holds the foreign key. Each customer has many orders, and a customer with none gets `[]`.

The relationship comes only from a declared foreign key, such as `orders.customer_id references customers(id)`. Fortly never guesses a link from a column's name. If there's no foreign key between the two tables, the request fails with `orders and products are not related — an embedded table needs a declared foreign key between them`.

You can embed several tables in one `select`, but only one level deep: an embedded table can't embed another one. Fortly fetches the key columns it needs to match rows and removes them from the answer unless you asked for them.

## Sort and page

| Parameter | Format |
| --- | --- |
| `order` | `column`, `column.asc` or `column.desc`, with an optional `.nullsfirst` or `.nullslast`. Separate several with commas: `order=status.asc,created_at.desc.nullslast`. Without it, rows come back in the database's own order, which isn't guaranteed |
| `limit` | Rows to return. The default and maximum depend on the route: 50 and 200 on the HTTP API, 100 and 1000 on the loopback and frontend APIs |
| `offset` | Rows to skip, 0 or more. Defaults to 0 |
| `count` | `exact` asks for the total that matches your filters. The frontend data API returns it in a [`Content-Range` header](https://docs.fortly.com/data/frontend-api#count-the-matching-rows), and the HTTP API always includes it as `total`. The loopback API ignores it |

```text
GET …/orders?order=created_at.desc&limit=50&offset=100     page 3, 50 at a time, newest first
```

## Add rows

Send one JSON object to add one row. The answer is the row as the database stored it, with defaults and generated keys filled in:

**curl**

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/tables/orders/rows \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"customer_id": 7, "total": 129.50, "status": "pending"}'
```

**JavaScript**

```js
const res = await fetch("https://api.fortly.com/projects/my-app-ada/tables/orders/rows", {
  method: "POST",
  headers: {
    Authorization: `Bearer ${process.env.FORTLY_API_TOKEN}`,
    "Content-Type": "application/json",
  },
  body: JSON.stringify({ customer_id: 7, total: 129.5, status: "pending" }),
});
const order = await res.json(); // includes the new id and created_at
```

```json
{ "id": 43, "customer_id": 7, "total": 129.50, "status": "pending", "created_at": "2026-10-12T10:20:31.84+00:00" }
```

Send an array to add several rows. They're inserted in one transaction, so if one row fails, none are added, and the answer is an array of the stored rows:

```json
[{ "email": "ada@example.com", "name": "Ada Lovelace" }, { "email": "grace@example.com", "name": "Grace Hopper" }]
```

Columns you leave out get their default, or `null`. PostgreSQL converts each value to the column's type, so `numeric`, `timestamptz`, `uuid`, arrays and `jsonb` all accept their usual JSON forms. A key that isn't a column is refused with `customers has no column nmae`.

### Upsert

On the loopback and frontend APIs, add `Prefer: resolution=merge-duplicates` to turn a clash on the primary key into an update of the existing row:

```bash
curl -X POST "$FORTLY_API/customers" \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates" \
  -d '{"id": 7, "name": "Ada King"}'
```

- The match is on the table's **primary key** only. A table without one is refused with `customers has no primary key, so there is nothing for an upsert to match on`.
- Only the columns you sent are updated. The others keep their values.
- If you send only primary-key columns, an existing row is left as it is and isn't returned. For a single row, the answer is then `{}`.

The HTTP API table routes don't read `Prefer`. A clash there is an error.

## Change rows

`PATCH` with a filter and a JSON object of the columns to change. Columns you don't send keep their values:

```bash
curl -X PATCH "https://api.fortly.com/projects/my-app-ada/tables/orders/rows?id=eq.43" \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"status": "paid"}'
```

```json
{ "updated": 1 }
```

## Remove rows

`DELETE` with a filter:

```bash
curl -X DELETE "https://api.fortly.com/projects/my-app-ada/tables/orders/rows?status=eq.cancelled&created_at=lt.2026-01-01" \
  -H "Authorization: Bearer $FORTLY_TOKEN"
```

```json
{ "deleted": 12 }
```

> **Warning:**
>
> An update or delete without a filter is refused, with `an update needs a filter` or `a delete needs a filter`, because it would rewrite or empty the whole table. To clear a table on purpose, use a filter that matches every row, such as `id=not.is.null`.

## Run schema changes

Schema changes go through a separate SQL route that accepts only statements starting with `create`, `alter` or `drop`. From anywhere, it's `POST /projects/{name}/sql`. See [Change the schema](https://docs.fortly.com/data#change-the-schema). From a long-running process, `POST $FORTLY_API/sql` takes the SQL as the raw request body:

```bash
curl -X POST "$FORTLY_API/sql" \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  --data-binary 'create index orders_customer_id on orders (customer_id)'
```

```json
{ "ok": true, "tables": ["customers", "orders"] }
```

The data APIs read the new shape straight away, so the next request can use the new table or column.

## Call a routine

A routine is a function or procedure stored in your database. Call it with the arguments as a JSON object by parameter name, or as an array by position. Parameters you leave out use their declared defaults:

```bash
curl -X POST https://api.fortly.com/projects/my-app-ada/routines/revenue_between/call \
  -H "Authorization: Bearer $FORTLY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"since": "2026-10-01", "until": "2026-10-31"}'
```

```json
{
  "routine": "revenue_between",
  "kind": "function",
  "columns": ["day", "revenue"],
  "rows": [{ "day": "2026-10-01", "revenue": 1840.50 }],
  "truncated": false,
  "took_ms": 4
}
```

The same call is `POST $FORTLY_API/rpc/revenue_between` from a long-running process, and `POST /_data/rpc/revenue_between` from a browser once you've [opened the routine](https://docs.fortly.com/data/frontend-api#call-a-routine). A function returns its rows, up to 500 on the HTTP API and 1000 on the other two, with `truncated` set to `true` when there were more. A procedure that returns nothing answers with empty `columns` and `rows` and a `changed` count. Each argument is cast to the parameter's declared type.

A routine is how you run a query that filters can't express, such as an `or`, a range on one column, an aggregate or a join.

## How values come back

PostgreSQL renders the values, so `numeric` keeps its digits, `timestamptz` carries its zone, and `jsonb` and arrays come back as JSON.

A JavaScript number holds an integer exactly only up to 9,007,199,254,740,991, and a decimal to about 15 significant digits. A number in a row beyond that, such as a large `bigint` or a long `numeric`, arrives as a string holding its exact digits, so `JSON.parse` can't round it without telling you. Numbers inside a `jsonb` document come back exactly as stored.

## Errors

Errors are JSON with an `error` message:

```json
{ "error": "orders has no column totl" }
```

A request the query language can't use, such as an unknown table or column, a malformed filter, or an update or delete without a filter, answers `400`. So does any error from PostgreSQL itself, such as a constraint violation, in the database's own words. Each route reports authentication its own way:

| Route | Missing or wrong credential |
| --- | --- |
| HTTP API table routes | `401`, as described in [Authentication](https://docs.fortly.com/api#authentication). A project you can't access is `404`, `no project called {name}` |
| Loopback API | `401`, `unknown or missing token` |
| Frontend data API | `401` or `403`. See [Errors](https://docs.fortly.com/data/frontend-api#errors) |