# The database

> Every project has its own PostgreSQL database. Connect from your app, browse it in the console, change its schema and take a copy out.

Source: https://docs.fortly.com/data

Every Fortly project has its own PostgreSQL 16 database. It lives on a managed PostgreSQL server that is shared with other projects, but the database and the login role that owns it are your project's alone. Your code reaches it over HTTP data APIs, or, for a project that runs as a long-running process, over an ordinary connection string. You can browse and change it in the console, and export all of it whenever you want.

## What you get

| | |
| --- | --- |
| Engine | PostgreSQL 16. See [PostgreSQL compatibility](https://docs.fortly.com/data/postgres) for what that includes. |
| Isolation | A database of its own on a shared server, owned by your project's own role. The role is not a superuser and can't connect to any other project's database. |
| Created | The first time anything asks for it, such as opening the project's **Data** tab, a data API call or an import. A project that never uses a database never gets one. |
| Reached from | Server-side code, through the [server data API](https://docs.fortly.com/data/server-api). A browser, through the [frontend data API](https://docs.fortly.com/data/frontend-api). A long-running process, also through `DATABASE_URL`. You, through the console and the [HTTP API](https://docs.fortly.com/api/data). |
| Taken out | A full export in four formats, at any time. See [Backups and exports](https://docs.fortly.com/data/backups). |

## Connect from your app

How your code reaches the database depends on how the project runs:

| Your project runs as | How it reaches the database |
| --- | --- |
| A Worker. New deploys of server frameworks, such as Next.js and Nuxt, run this way | Over HTTPS: the [HTTP API table routes](https://docs.fortly.com/data/server-api#from-a-worker) with an API token kept in a runtime value, or the [frontend data API](https://docs.fortly.com/data/frontend-api) for tables you've opened. A Worker has no `DATABASE_URL` |
| A static site | From the browser, through the [frontend data API](https://docs.fortly.com/data/frontend-api) |
| A long-running process, from an earlier deploy | A direct PostgreSQL connection through `DATABASE_URL`, described below, or the [loopback data API](https://docs.fortly.com/data/server-api#from-a-long-running-process) |

### A direct connection, for a long-running process

When Fortly starts a project's process, the environment holds the database credentials under the names that PostgreSQL clients and most frameworks already look for:

| Variable | What it holds |
| --- | --- |
| `DATABASE_URL` | A full `postgres://` connection string for your database |
| `PGHOST`, `PGPORT`, `PGDATABASE`, `PGUSER`, `PGPASSWORD` | The same connection, split into parts, for libpq-based clients |
| `PGSSLMODE` | `prefer` |
| `DATABASE_TRANSACTION_URL` | The same database through a transaction-pooling endpoint. Set only where one is available. See [Connection pooling](https://docs.fortly.com/data/postgres#connection-pooling) before you use it |

Most clients need nothing more than `DATABASE_URL`:

```js title="db.js"
import pg from "pg";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5, // keep this under the database's connection limit
});

const { rows } = await pool.query(
  "select id, total from orders where status = $1 order by created_at desc limit 20",
  ["paid"],
);
```

Clients built on libpq, such as `psql` and Python's `psycopg`, read the `PG*` variables directly, so `psycopg.connect()` with no arguments connects to your database.

> **Note:**
>
> Your process reads these values once, when it starts. Because the database is created on first use, a process that started before the database existed has no `DATABASE_URL`. It gets one the next time it starts, for example when it wakes after going idle.

These names are reserved. If you set a secret named `DATABASE_URL`, `DATABASE_TRANSACTION_URL` or any of the `PG*` names above, Fortly refuses it with `{name} is set by Fortly and cannot be replaced`. No value of yours can stand in for the connection details Fortly manages.

### There is no public address

The database has no address outside Fortly. The host in `DATABASE_URL` is an internal name that only resolves inside Fortly. You can't connect `psql`, TablePlus, DataGrip or any other client to it from your own machine. To work with the data from outside, use the console's **Query** tab, the [HTTP API](https://docs.fortly.com/api/data), or [an export](https://docs.fortly.com/data/backups).

## Limits on each database

Each project's role carries these limits. PostgreSQL enforces them, so they apply to every connection, including the ones the data APIs make for you:

| Limit | Value | What happens when you reach it |
| --- | --- | --- |
| Connections | 10 | A new connection is refused until one closes. The data APIs and the console share up to 2 of these, and a process connecting through `DATABASE_URL` uses the rest. |
| Statement time | 30 seconds | The statement is cancelled with `canceling statement due to statement timeout`. |
| Idle inside a transaction | 60 seconds | The session is ended. |
| Waiting for a lock | 10 seconds | The statement gives up with a lock timeout error. |

Storage depends on your plan. See [Limits](https://docs.fortly.com/account/limits). The **Backups** section of the **Data** tab shows your database's current size, as measured by the server it runs on.

## Work with it in the console

Open a project and choose **Data**. The tab has five sections:

| Section | What it's for |
| --- | --- |
| **Explorer** | Browse tables, views, indexes and routines. For a table: **Data** (page, filter, sort, add and edit rows), **Structure** (columns, types, keys, defaults), **Indexes** and **Relationships** (its foreign keys). **Export** downloads the table as CSV, up to 50,000 rows. |
| **Query** | Run any SQL against your database, including reads and writes. Results show the first 500 rows and say when there were more. The editor stops waiting after 15 seconds. You can save queries as snippets and download a result as CSV. |
| **Routines** | Write, edit and call PostgreSQL functions and procedures. They're stored in your own database, so they travel with an export. Fortly keeps a history of who changed each one and when. |
| **Access** | The publishable key and the per-table switches for the [frontend data API](https://docs.fortly.com/data/frontend-api). |
| **Backups** | Your database's size, a full export in four formats, and restore from a SQL script. See [Backups and exports](https://docs.fortly.com/data/backups). |

## Change the schema

You can change your schema in three ways:

- **The console.** Create a table in **Explorer**, change one in its **Structure** view, or write the SQL yourself in **Query**.
- **The SQL route.** `POST /projects/{name}/sql` runs schema changes from anywhere you hold an API token, such as a deploy script or CI.
- **Your own migrations, from a long-running process.** A migration tool that the process runs over `DATABASE_URL`, for example as part of its start command, applies its migrations as it would to any PostgreSQL database its role owns. Tools that need to create a second, scratch database can't, because your role has no permission to create databases. A Worker has no direct connection, so send its migrations to the SQL route instead.

The SQL route accepts only statements that begin with `create`, `alter` or `drop`. Separate several statements with semicolons. If any statement starts with another word, Fortly refuses the whole request and runs nothing:

```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 bigint generated by default as identity primary key, email text not null unique, name text); create table orders (id bigint generated by default as identity primary key, customer_id bigint references customers(id), total numeric(12,2) not null, status text not null, created_at timestamptz not null default now())"}'
```

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

In API paths, `{name}` is the project's full name: the name you chose followed by your account's slug, so a project created as `my-app` by an account with the slug `ada` is `my-app-ada`. See [Project names in paths](https://docs.fortly.com/api#project-names-in-paths).

A statement that begins with any other word is refused with `only create, alter and drop are accepted here — use /v1/<table> to read and write`. To read and write rows, use the data APIs or the table routes in the [API reference](https://docs.fortly.com/api/data). From a long-running process, the same route is also `POST $FORTLY_API/sql`. See the [server data API](https://docs.fortly.com/data/server-api#run-schema-changes).

> **Note:**
>
> The data APIs read your schema when they open the database, and again after any change made through Fortly: the SQL route, the **Query** tab, an import or the table designer. If a process creates a table over `DATABASE_URL`, for example in a migration, the data APIs see it after the next of those changes, or after the project goes idle and wakes. The process's own connection sees it immediately.

## Primary keys fill themselves in

PostgreSQL has no hidden row id, so a table's key has to come from somewhere. When you create a table in the console with an `integer`, `bigint` or `smallint` primary key and no default, the designer declares that column as:

```sql
id bigint generated by default as identity primary key
```

Adding a row never has to name an `id`, because the database assigns the next one. You can still supply an explicit `id`, which is what restoring a dump needs. `generated always` would refuse one.

SQL you write yourself goes to the server unchanged. So `id integer primary key` means what PostgreSQL means by it: a column you must fill in on every insert. Write `generated by default as identity` when you want the database to choose the id.

## What you can take out

All of your data is exportable, whenever you want it:

- **The whole database** as PostgreSQL SQL, MySQL SQL or JSON, with tables, keys, constraints, defaults, indexes and views. Triggers and routines are included too, as SQL or as comments depending on the format. See [Backups and exports](https://docs.fortly.com/data/backups#choose-a-format).
- **One table** as CSV, from **Explorer** › **Export**.
- **Any query result** as CSV, from the **Query** tab.

Bringing data in works the same way in reverse. See [Importing a database](https://docs.fortly.com/data/import).

## Next

- [Server data API](https://docs.fortly.com/data/server-api): Read and write rows from server-side code with a URL, not a driver.
- [Frontend data API](https://docs.fortly.com/data/frontend-api): Call your tables from a browser with a publishable key and per-table switches.
- [Importing a database](https://docs.fortly.com/data/import): Bring an existing PostgreSQL database in with a pg_dump file.
- [PostgreSQL compatibility](https://docs.fortly.com/data/postgres): The version, extensions, pooling, and what your role can and cannot do.