# Data API > Read a project's schema, read and write rows, change tables, run SQL, import a dump and export the database, from outside the project. Source: https://docs.fortly.com/api/data Every project has its own PostgreSQL database. These routes manage it from outside the project with your account token — for scripts, CI and admin tools. Code running inside the project uses the [server data API](https://docs.fortly.com/data/server-api) on loopback instead, and a browser uses the [frontend data API](https://docs.fortly.com/data/frontend-api) with the publishable key. - `GET /projects/{name}/tables` — Tables, their columns and foreign keys - `GET /projects/{name}/views` — Views and their definitions - `GET /projects/{name}/indexes` — Indexes you created - `GET /projects/{name}/tables/{table}/rows` — Read a page of rows - `POST /projects/{name}/tables/{table}/rows` — Insert a row, or several - `PATCH /projects/{name}/tables/{table}/rows` — Update rows matching a filter - `DELETE /projects/{name}/tables/{table}/rows` — Delete rows matching a filter - `POST /projects/{name}/sql` — Run schema changes: create, alter, drop - `POST /projects/{name}/query` — Run one SQL statement and get its result - `POST /projects/{name}/batch` — Run several statements in one transaction - `POST /projects/{name}/import` — Import a SQL script - `GET /projects/{name}/export` — Download the whole database - `GET /projects/{name}/backup` — Database size and export formats A table name that is not in the schema is refused with `400` and `no table named {table}`; table and column names are checked against the schema before they reach SQL, and values are always bound. ## List tables - `GET /projects/{name}/tables` — Tables, their columns and foreign keys The tables in the `public` schema, read from the database itself. - `name` (string, required) — Path parameter. The project's `name`. **curl** ```bash curl https://api.fortly.com/projects/my-app-ada/tables \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/tables", { headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` }, }); const { tables } = await res.json(); ``` ```json { "tables": [ { "name": "orders", "columns": [ { "name": "id", "type": "integer", "not_null": true, "primary_key": true, "default_value": null }, { "name": "customer_id", "type": "integer", "not_null": true, "primary_key": false, "default_value": null }, { "name": "total", "type": "numeric", "not_null": false, "primary_key": false, "default_value": null }, { "name": "created", "type": "timestamp with time zone", "not_null": true, "primary_key": false, "default_value": "now()" } ], "links": [ { "column": "customer_id", "table": "customers", "to": "id", "on_delete": "CASCADE", "on_update": "" } ] } ] } ``` `links` are the table's declared foreign keys: `column` in this table points at column `to` of `table`. `on_delete` and `on_update` are the SQL action — `CASCADE`, `SET NULL` or `SET DEFAULT` — or empty for `NO ACTION` and `RESTRICT`, which behave as an omitted clause does. ## List views - `GET /projects/{name}/views` — Views and their definitions Views and materialized views in the `public` schema, with their definitions as PostgreSQL prints them. **curl** ```bash curl https://api.fortly.com/projects/my-app-ada/views \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/views", { headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` }, }); const { views } = await res.json(); ``` ```json { "views": [ { "name": "paid_orders", "sql": " SELECT id,\n total\n FROM orders\n WHERE status = 'paid'::text;" } ] } ``` ## List indexes - `GET /projects/{name}/indexes` — Indexes you created Indexes in the `public` schema that you created. The indexes PostgreSQL builds for primary keys and unique constraints are left out. **curl** ```bash curl https://api.fortly.com/projects/my-app-ada/indexes \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/indexes", { headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` }, }); const { indexes } = await res.json(); ``` ```json { "indexes": [ { "name": "orders_created_idx", "table": "orders", "unique": false, "columns": ["created"] } ] } ``` `columns` holds each indexed column or expression, so a functional index reads like `["lower(email)"]`. ## Read rows - `GET /projects/{name}/tables/{table}/rows` — Read a page of rows A page of rows, with the total that match. Filtering, sorting, column selection and embedding related tables use the same query grammar as the [server data API](https://docs.fortly.com/data/server-api#filters). - `name` (string, required) — Path parameter. The project's `name`. - `table` (string, required) — Path parameter. The table. - `limit` (integer, optional) — Rows per page. Default `50`; values are clamped to 1–200. - `offset` (integer, optional) — Rows to skip. Default `0`. - `order` (string, optional) — `column`, `column.asc` or `column.desc`, optionally followed by `.nullsfirst` or `.nullslast`. Comma-separate several. - `select` (string, optional) — Columns to return, comma-separated, such as `id,total`. A related table in parentheses embeds it through a declared foreign key: `id,customers(name)`. - `{column}` (string, optional) — A filter, written `column=op.value`: `status=eq.paid`, `total=gte.100`, `id=in.(1,2,3)`, `archived=is.null`. Prefix `not.` to negate. Every filter must match. **curl** ```bash curl "https://api.fortly.com/projects/my-app-ada/tables/orders/rows?status=eq.paid&order=created.desc&limit=20" \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js const q = new URLSearchParams({ status: "eq.paid", order: "created.desc", limit: "20" }); const res = await fetch(`https://api.fortly.com/projects/my-app-ada/tables/orders/rows?${q}`, { headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` }, }); const { rows, total } = await res.json(); ``` ```json { "rows": [ { "id": 42, "customer_id": 7, "total": 129.5, "status": "paid", "created": "2026-10-11T14:02:11.482+00:00" } ], "limit": 20, "offset": 0, "total": 1 } ``` `total` is the number of rows matching the filters, ignoring `limit` and `offset`. A filter on a column that does not exist, an unknown operator or a malformed value answers `400` with the reason. ## Insert rows - `POST /projects/{name}/tables/{table}/rows` — Insert a row, or several The body is one JSON object for one row, or an array of objects for several. Several rows are inserted in one transaction: all of them or none. Columns you leave out get their default, so an identity primary key fills itself in. - `name` (string, required) — Path parameter. The project's `name`. - `table` (string, required) — Path parameter. The table. **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.5,"status":"paid"}' ``` **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_TOKEN}`, "Content-Type": "application/json", }, body: JSON.stringify({ customer_id: 7, total: 129.5, status: "paid" }), }); const row = await res.json(); ``` Answers `201` with the row as the database stored it, every column included — an object for an object, an array for an array: ```json { "id": 43, "customer_id": 7, "total": 129.5, "status": "paid", "created": "2026-10-12T09:30:00.117+00:00" } ``` A constraint the database enforces — a duplicate key, a missing `not null` value, a foreign key with nothing at the other end — answers `400` with the database's message. An empty array answers `400` with `nothing to insert`. ## Update rows - `PATCH /projects/{name}/tables/{table}/rows` — Update rows matching a filter Sets the columns in the body on every row the filters match. Columns not in the body keep their values. - `name` (string, required) — Path parameter. The project's `name`. - `table` (string, required) — Path parameter. The table. - `{column}` (string, required) — At least one filter, as in [Read rows](#read-rows). Without one the request is refused with `an update needs a filter`, because it would rewrite the whole table. The body is a JSON object of column names and new values. A name that is not a column of the table answers `400`. **curl** ```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":"refunded"}' ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/tables/orders/rows?id=eq.43", { method: "PATCH", headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}`, "Content-Type": "application/json", }, body: JSON.stringify({ status: "refunded" }), }); const { updated } = await res.json(); ``` ```json { "updated": 1 } ``` ## Delete rows - `DELETE /projects/{name}/tables/{table}/rows` — Delete rows matching a filter Deletes every row the filters match. - `name` (string, required) — Path parameter. The project's `name`. - `table` (string, required) — Path parameter. The table. - `{column}` (string, required) — At least one filter, as in [Read rows](#read-rows). Without one the request is refused with `a delete needs a filter`. **curl** ```bash curl -X DELETE "https://api.fortly.com/projects/my-app-ada/tables/orders/rows?status=eq.cancelled" \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js const res = await fetch( "https://api.fortly.com/projects/my-app-ada/tables/orders/rows?status=eq.cancelled", { method: "DELETE", headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` } }, ); const { deleted } = await res.json(); ``` ```json { "deleted": 3 } ``` ## Change the schema - `POST /projects/{name}/sql` — Run schema changes: create, alter, drop Runs migrations. The SQL may hold several statements, and every one of them must start with `create`, `alter` or `drop`; anything else refuses the whole request before any of it runs. Read and write rows through the routes above, or use [Run a query](#run-a-query). - `name` (string, required) — Path parameter. The project's `name`. - `sql` (string, required) — The statements, separated by semicolons. The SQL reaches PostgreSQL as written. `id integer primary key` means a column you must fill in yourself; use `integer generated by default as identity primary key` for one that fills itself in. **curl** ```bash curl -X POST https://api.fortly.com/projects/my-app-ada/sql \ -H "Authorization: Bearer $FORTLY_TOKEN" \ -H "Content-Type: application/json" \ -d '{"sql":"create table customers (id integer generated by default as identity primary key, name text not null); create index customers_name_idx on customers (name);"}' ``` **JavaScript** ```js const sql = ` create table customers ( id integer generated by default as identity primary key, name text not null ); create index customers_name_idx on customers (name); `; const res = await fetch("https://api.fortly.com/projects/my-app-ada/sql", { method: "POST", headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}`, "Content-Type": "application/json", }, body: JSON.stringify({ sql }), }); const { tables } = await res.json(); ``` Answers with every table now in the database: ```json { "ok": true, "tables": ["customers", "orders"] } ``` A statement of another kind answers `400` with `only create, alter and drop are accepted here`; one PostgreSQL refuses answers `400` with its message. ## Run a query - `POST /projects/{name}/query` — Run one SQL statement and get its result Runs one statement of any kind and returns what it produced. This is the console's query editor; use it for ad-hoc reads, reports and one-off fixes. - `name` (string, required) — Path parameter. The project's `name`. - `sql` (string, required) — One SQL statement. **curl** ```bash curl -X POST https://api.fortly.com/projects/my-app-ada/query \ -H "Authorization: Bearer $FORTLY_TOKEN" \ -H "Content-Type: application/json" \ -d '{"sql":"select status, count(*) from orders group by status"}' ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/query", { method: "POST", headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}`, "Content-Type": "application/json", }, body: JSON.stringify({ sql: "select status, count(*) from orders group by status" }), }); const { columns, rows, truncated } = await res.json(); ``` A statement that returns rows answers with them, at most 500. `truncated` is `true` when there were more: ```json { "columns": ["status", "count"], "rows": [ { "status": "paid", "count": 118 }, { "status": "refunded", "count": 4 } ], "truncated": false, "took_ms": 3 } ``` A statement that returns no rows answers with how many it changed: ```json { "columns": [], "rows": [], "changed": 12, "took_ms": 5 } ``` A statement that runs longer than 15 seconds answers `400` with `that query took too long and was stopped`. A result with two columns of the same name is refused; alias one of them. An empty `sql` answers `400` with `there is nothing to run`. ## Run a batch - `POST /projects/{name}/batch` — Run several statements in one transaction Runs up to 200 statements in order, in one transaction. If any fails, none of them is applied. - `name` (string, required) — Path parameter. The project's `name`. - `statements` (string[], required) — The statements, one per item. Empty items are ignored. **curl** ```bash curl -X POST https://api.fortly.com/projects/my-app-ada/batch \ -H "Authorization: Bearer $FORTLY_TOKEN" \ -H "Content-Type: application/json" \ -d '{"statements":["alter table orders add column note text","update orders set note = '\''imported'\'' where note is null"]}' ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/batch", { method: "POST", headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}`, "Content-Type": "application/json", }, body: JSON.stringify({ statements: [ "alter table orders add column note text", "update orders set note = 'imported' where note is null", ], }), }); const { results } = await res.json(); ``` `results` holds one [query result](#run-a-query) per statement, in order: ```json { "results": [ { "columns": [], "rows": [], "changed": 0, "took_ms": 4 }, { "columns": [], "rows": [], "changed": 122, "took_ms": 9 } ] } ``` A failing statement answers `400` naming its position, such as `statement 2: …`. No statements answers `400` with `there is nothing to run`; more than 200 with `at most 200 statements in one batch`. ## Import a SQL script - `POST /projects/{name}/import` — Import a SQL script Runs a SQL script — a dump from any PostgreSQL, including a Fortly [export](#export-the-database) — against the database as it is. Nothing is emptied first. The whole script runs in one transaction, so a script that stops partway leaves the database exactly as it was. See [Importing a database](https://docs.fortly.com/data/import) for preparing a dump. Only the account owner or a team admin can import; a member gets `403`. - `name` (string, required) — Path parameter. The project's `name`. The body is the script itself, as UTF-8 text, up to 256 MB. Lines that only make sense to `psql` or to a session are skipped: backslash commands, `SET` (but not `SET CONSTRAINTS`), `select pg_catalog.set_config(…)`, and the dump's own `BEGIN`, `COMMIT`, `END` and `ROLLBACK`. Everything else runs, including `GRANT`, `COMMENT ON` and `ALTER TABLE`. **curl** ```bash curl -X POST https://api.fortly.com/projects/my-app-ada/import \ -H "Authorization: Bearer $FORTLY_TOKEN" \ -H "Content-Type: application/octet-stream" \ --data-binary @dump.sql ``` **JavaScript** ```js import { readFile } from "node:fs/promises"; const res = await fetch("https://api.fortly.com/projects/my-app-ada/import", { method: "POST", headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}`, "Content-Type": "application/octet-stream", }, body: await readFile("dump.sql"), }); const { ran, skipped, tables } = await res.json(); ``` ```json { "imported": "statements", "ran": 1284, "skipped": 9, "tables": 14 } ``` `ran` is statements sent to the database, `skipped` the lines left out as above, and `tables` the number of tables afterwards. | Status | When | | --- | --- | | `400` | The file is empty, binary (such as a SQLite database file) or not UTF-8 text, or a statement was refused. The error names the statement and how many ran before it; none of them is kept. | | `403` | You are a member, not the owner or an admin. | | `413` | The file is over 256 MB. | ## Export the database - `GET /projects/{name}/export` — Download the whole database The whole database as a file: schema and data. - `name` (string, required) — Path parameter. The project's `name`. - `format` (string, optional) — `postgres` (default; also `postgresql`, `pg`), `sql` (the same dialect), `mysql` (also `mariadb`), or `json`. - `gzip` (string, optional) — `1`, `true` or `yes` to compress it. **curl** ```bash curl -OJ "https://api.fortly.com/projects/my-app-ada/export?format=postgres&gzip=1" \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js import { writeFile } from "node:fs/promises"; const res = await fetch("https://api.fortly.com/projects/my-app-ada/export?format=postgres", { headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` }, }); const name = /filename="([^"]+)"/.exec(res.headers.get("content-disposition"))[1]; await writeFile(name, Buffer.from(await res.arrayBuffer())); ``` The response body is the file, not JSON: ```http HTTP/1.1 200 OK Content-Type: application/sql Content-Disposition: attachment; filename="my-app-ada-2026-10-12.sql" ``` `Content-Type` is `application/sql` for the SQL formats, `application/json` for `json`, and `application/gzip` when compressed, in which case the filename ends in `.gz`. An unknown `format` answers `400`. ## Read backup status - `GET /projects/{name}/backup` — Database size and export formats What can be taken from this database, and how big it is. **curl** ```bash curl https://api.fortly.com/projects/my-app-ada/backup \ -H "Authorization: Bearer $FORTLY_TOKEN" ``` **JavaScript** ```js const res = await fetch("https://api.fortly.com/projects/my-app-ada/backup", { headers: { Authorization: `Bearer ${process.env.FORTLY_TOKEN}` }, }); const status = await res.json(); ``` ```json { "automatic": false, "size_bytes": 8028160, "formats": ["sql", "postgres", "mysql", "json"] } ``` `automatic` is `false`: no continuous copy of the database is taken, so [export](#export-the-database) is how you keep one. See [Backups](https://docs.fortly.com/data/backups).