The database

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

Connect from your app

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

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:

Most clients need nothing more than DATABASE_URL:

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.

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, or an export.

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:

Storage depends on your plan. See 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:

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:

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())"}'

{ "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.

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. From a long-running process, the same route is also POST $FORTLY_API/sql. See the server data API.

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:

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.
• 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.

Next