PostgreSQL databases

Overview

Each app gets one database and one database user of its own. That user can reach only its own database, and every connection is encrypted with TLS. The database runs next to your app but outside its container, so it keeps your data when you redeploy, restart or put the app to sleep.

There are three ways to work with it, and you can mix them:

• Your own code, with any PostgreSQL driver and the connection string from the console.
• The console: a table editor and a SQL console on the site, no tools needed.
• The DB API: REST endpoints for your tables with an app key, for scripts and back ends that would rather not hold a database connection.

Create a database

1. Open your app in the console and go to Database.
2. Click Create database. It is ready in a few seconds.
3. The Connection card shows the host, port, database name, user and password.

The database and the user are both named after your app's number, for example app_123. A few extensions are already installed: pgcrypto, uuid-ossp, citext and pg_trgm.

Connect from your app

Copy the connection string from the console (Copy connection string). It looks like this:

postgresql://app_123:YOUR_PASSWORD@db.systemi.in:5433/app_123?sslmode=require

Keep it in an environment variable such as DATABASE_URL rather than in your code. sslmode=require is needed: connections without TLS are refused. Most drivers read the full URL; for the ones that want separate settings, use the values from the Connection card.

import os, psycopg

conn = psycopg.connect(os.environ["DATABASE_URL"])   # ...?sslmode=require
with conn.cursor() as cur:
    cur.execute("SELECT id, name FROM orders WHERE status = %s", ["paid"])
    print(cur.fetchall())

$u = parse_url(getenv('DATABASE_URL'));
$pdo = new PDO(
  sprintf('pgsql:host=%s;port=%d;dbname=%s;sslmode=require', $u['host'], $u['port'], ltrim($u['path'], '/')),
  $u['user'], $u['pass']
);
$rows = $pdo->query('SELECT id, name FROM orders')->fetchAll();

// PostgreSQL JDBC driver
String url = "jdbc:postgresql://db.systemi.in:5433/app_123?sslmode=require";
try (Connection c = DriverManager.getConnection(url, "app_123", System.getenv("DB_PASSWORD"))) {
    ResultSet rs = c.createStatement().executeQuery("SELECT id, name FROM orders");
}

var cs = "Host=db.systemi.in;Port=5433;Database=app_123;Username=app_123;"
       + $"Password={Environment.GetEnvironmentVariable("DB_PASSWORD")};SSL Mode=Require";
await using var db = NpgsqlDataSource.Create(cs);
await using var cmd = db.CreateCommand("SELECT id, name FROM orders");

Changing the password. New password on the Connection card sets a new one at once and the old one stops working, so update your app's environment variable and redeploy right after.

Connections. Each plan allows a set number of open connections per database. Use a small connection pool (two to five connections is plenty for most apps) and close connections you no longer need. If every connection is in use, new ones are refused until one closes.

Table editor

The Tables tab lists your tables and views. From there you can:

• Create a table: give it a name and columns with a type, a default, and whether they are required or unique. A default is a fixed value, except now(), current_date and gen_random_uuid(), which are worked out for each new row. By default each new table gets an id column (a bigint filled in automatically) as its primary key.
• Add or drop columns on an existing table.
• Browse rows 50 at a time, filter them by a column (the same operators as the DB API), and add, edit or delete rows. Editing and deleting need a primary key on the table; for tables without one, use the SQL console.
• Drop a table with all its rows. This can't be undone.

Column types: text, varchar(n), integer, bigint, smallint, boolean, numeric(p,s), real, double precision, date, time, timestamp, timestamptz, uuid, json, jsonb, citext and bytea. Anything else (indexes, foreign keys, other types) you can make with SQL.

SQL console

The SQL console tab runs SQL as your app's own database user, so it can do what your app can do: create indexes, views, functions and foreign keys, change data, or look at query plans with EXPLAIN. A script runs in one transaction; if a statement fails, nothing in that run is kept. You see the rows of the last statement, up to 500.

CREATE INDEX orders_status_idx ON orders (status);
SELECT status, count(*) FROM orders GROUP BY status ORDER BY 2 DESC;

DB API

The DB API gives every table REST endpoints without any code on your side. Make a key on the DB API keys tab (it is shown once, so copy it then) and send it with each request. Choose read-only for keys that should never change data. Revoke a key any time; it stops working at once.

Keys are for server-side use: your back end, scripts and jobs. Don't put a key in a web page or mobile app, where anyone could read it.

The base address is https://systemi.in. Send the key as Authorization: Bearer sdb_... (or in an apikey header). PATCH and DELETE need at least one filter, so a request can't change a whole table by mistake.

Filters and options

Filters are query parameters in the form column=operator.value; several filters must all match.

• select=id,name: only these columns.
• order=created_at.desc,id: sort order (.asc is the default).
• limit and offset: pages; 100 rows unless you ask for more, at most 1000 per request.

Reads return {"rows": [...], "more": true|false}; more says whether another page follows.

Examples

export SYSTEMI_KEY=sdb_xxxxxxxx_your-secret

# paid orders, newest first
curl -s "https://systemi.in/api/db/v1/orders?status=eq.paid&order=created_at.desc&limit=20" \
  -H "Authorization: Bearer $SYSTEMI_KEY"

# add two rows
curl -s -X POST "https://systemi.in/api/db/v1/orders" \
  -H "Authorization: Bearer $SYSTEMI_KEY" -H "Content-Type: application/json" \
  -d '[{"name": "Green tea", "total": 4.50}, {"name": "Masala chai", "total": 3.20}]'

# mark one shipped
curl -s -X PATCH "https://systemi.in/api/db/v1/orders?id=eq.42" \
  -H "Authorization: Bearer $SYSTEMI_KEY" -H "Content-Type: application/json" \
  -d '{"status": "shipped", "shipped_at": "2026-10-25T10:00:00Z"}'

# delete cancelled orders
curl -s -X DELETE "https://systemi.in/api/db/v1/orders?status=eq.cancelled" \
  -H "Authorization: Bearer $SYSTEMI_KEY"

Errors come back with a status code and a detail message: 401 for a missing or revoked key, 403 for a write with a read-only key, 400 for a database error (the message says what PostgreSQL objected to), 404 for an unknown table and 503 when every connection of the database is in use.

Migrations with Pilotbase

For schema changes you want to keep in git, write them as SQL migration files and run them against your database. Any migration tool that speaks PostgreSQL works with the connection string (the ones built into Django, Laravel, Rails, Entity Framework or Flyway, for example).

Pilotbase is a free database client that makes this easy to do by hand:

1. Download Pilotbase from pilotbase.pro and add a PostgreSQL connection with the values from the Connection card (SSL mode require).
2. Before you run a migration, paste it into the SQL inspector: it flags statements that lock a big table while they run, such as an index made without CONCURRENTLY.
3. Paste the migration into a Pilotbase query tab and run it against your database. Try it on a copy first if the data matters.
4. Check the result in Pilotbase or in the table editor here.

Good to know

• Storage. Your database's size counts towards your account's storage and data, together with your apps. The Usage page shows how much is used. An account that stays over its limit may have its databases paused until it is back under the limit or on a larger plan.
• Long queries. Very long running statements and transactions left open are stopped, and connections that stay idle for a long time are closed. Pools reconnect on their own.
• Backups. Keep your own exports of data you can't lose (pg_dump with the connection string works).
• Deleting the app deletes its database and revokes its DB API keys.

Questions? Write to us.