Copy page

Operate services

PostgreSQL databases

There are two kinds of database on Zaroz and the same API reads both.

	Included database	Dedicated PostgreSQL
What it is	A database on a shared server, bundled with a subscription	A PostgreSQL server of your own, sold as a product with its own plan and volume
Cost	Included: ten databases of 64 MiB per subscription	Billed by its plan (CPU, memory, storage), like any service
Isolation	Shared server, your own database and user	Your own instance and credentials
Storage limit	A quota (64 MiB), locks writes when exceeded	Sized by its own volume; no quota
Created with	POST /subscriptions/{id}/shared-databases	Buying the PostgreSQL product (Subscriptions and billing)

A dedicated PostgreSQL runs a fixed, pinned major version of PostgreSQL that the console shows on the service. Zaroz does not promise any particular extension: use what the version ships with, and ask support before you depend on anything else. A dedicated database's volume can be backed up and restored like any other service volume; see Backups and restore.

The samples use the helper from Using the Zaroz API.

Create an included database

Check what you still have and which engines this deployment hosts, then create one.

curl

Code
curl -sS "$ZAROZ_API/subscriptions/$SUBSCRIPTION/shared-databases/entitlement" -H "$AUTH"
curl -sS "$ZAROZ_API/subscriptions/$SUBSCRIPTION/shared-databases/engines" -H "$AUTH"
# {"engines": ["postgres", ...]}

curl -sS -X POST "$ZAROZ_API/subscriptions/$SUBSCRIPTION/shared-databases" -H "$AUTH" \
  -H "Content-Type: application/json" -d '{"engine": "postgres", "display_name": "app-db"}'

Code
{
  "data": {
    "id": "7c0f…",
    "display_name": "app-db",
    "engine": "postgres",
    "database": "db_3f9a…",
    "username": "u_3f9a…",
    "password": "…",
    "external_host": "db.example.zaroz.cloud",
    "external_port": 5432,
    "internal_host": "…",
    "internal_port": 5432,
    "quota_bytes": 67108864,
    "included": true
  }
}

TypeScript

Code
import { call } from "./zaroz";

const ent = await call("GET", `/subscriptions/${SUB}/shared-databases/entitlement`);
console.log(ent.body);

const db = await call("POST", `/subscriptions/${SUB}/shared-databases`, {
  engine: "postgres",
  display_name: "app-db",
});
const d = db.body.data;
console.log(`postgresql://${d.username}:${d.password}@${d.external_host}:${d.external_port}/${d.database}`);
const DATABASE_ID = d.id;

Python

Code
from zaroz import call

_, ent = call("GET", f"/subscriptions/{SUB}/shared-databases/entitlement")
print(ent)

_, db = call("POST", f"/subscriptions/{SUB}/shared-databases", {"engine": "postgres", "display_name": "app-db"})
d = db["data"]
print(f"postgresql://{d['username']}:{d['password']}@{d['external_host']}:{d['external_port']}/{d['database']}")
DATABASE_ID = d["id"]

• The fields in the answer are everything a client needs: external_host and external_port from the internet, internal_host and internal_port from inside Zaroz (use those from a Workload in the same region when internal_connection_available is true).
• You cannot hold more than ten: the eleventh answers 429 quota-exceeded. region_id is optional; when given and no host serves that region and engine, the answer is 503 instead of being placed elsewhere.
• The subscription must be active (422 otherwise), and creating needs the authority to buy for the organization (Owner or Admin).
• GET /subscriptions/{id}/shared-databases lists them, and shows the password of each, so treat that response as secret.
• Rotate the password with POST .../shared-databases/{database_id}/rotate-credentials (only the password changes), and delete with DELETE .../shared-databases/{database_id}: the slot is freed at once and the data lingers on the host for a short recovery window before it is dropped.

Connect to a dedicated database

A dedicated PostgreSQL is a service instance. Its user and password are chosen in its setup: read the answers with GET /service-instances/{id}/setup and, for a password you need to see again, the reveal route (POST /service-instances/{id}/setup/steps/server_passwords/{password_id}/reveal), which is recorded in the activity log. You can open a public port to it like any service (Public ports and endpoints); keep it closed unless you need it from outside.

The database id

Everything below is addressed by one id:

• for an included database, the id of the database (the id in the creation answer);
• for a dedicated database, the service instance id of the PostgreSQL service.

The routes are /databases/{database_id}/.... Reading needs access to the subscription; changing things needs the Owner or Admin role (an ordinary member can browse but not change). An id you may not use answers 404, the same as one that does not exist.

List tables

curl

Code
curl -sS "$ZAROZ_API/databases/$DATABASE_ID/tables" -H "$AUTH"
curl -sS "$ZAROZ_API/databases/$DATABASE_ID/tables/users" -H "$AUTH"

TypeScript

Code
const tables = await call("GET", `/databases/${DATABASE_ID}/tables`);
console.log(tables.body.data.map((t: any) => t.name));

Python

Code
_, tables = call("GET", f"/databases/{DATABASE_ID}/tables")
print([t["name"] for t in tables["data"]])

Each table lists its columns, primary key and size estimates. The sizes (row_estimate, data_length, index_length) are the engine's estimates, not counts; show them as approximations.

Read rows

Pages are cursor-based. limit defaults to 50 and is clamped to 500, so check the limit in the answer. Pass back next_cursor as cursor; never build a cursor yourself. sort is column, column:asc or column:desc. filters is a JSON array of {column, operator, value}, where operator is one of equals, not_equals, contains, starts_with, ends_with, greater_than, less_than, greater_than_or_equal, less_than_or_equal, is_null, is_not_null (the last two take no value). A sort or filters that cannot be parsed or names an unknown column is refused, never silently dropped.

curl

Code
curl -sS -G "$ZAROZ_API/databases/$DATABASE_ID/tables/users/rows" -H "$AUTH" \
  --data-urlencode 'limit=100' --data-urlencode 'sort=created_at:desc' \
  --data-urlencode 'filters=[{"column":"email","operator":"ends_with","value":"@example.com"}]'

TypeScript

Code
const filters = JSON.stringify([{ column: "email", operator: "ends_with", value: "@example.com" }]);
const q = new URLSearchParams({ limit: "100", sort: "created_at:desc", filters });
const page = await call("GET", `/databases/${DATABASE_ID}/tables/users/rows?${q}`);
console.log(page.body.data);

Python

Code
import json
from urllib.parse import urlencode

filters = json.dumps([{"column": "email", "operator": "ends_with", "value": "@example.com"}])
q = urlencode({"limit": 100, "sort": "created_at:desc", "filters": filters})
_, page = call("GET", f"/databases/{DATABASE_ID}/tables/users/rows?{q}")
print(page["data"])

Change data and structure

POST inserts one row (values maps column to value), PATCH updates one and DELETE deletes one, found by the value of the table's single-column primary key: a table with no primary key, or a composite one, cannot be edited this way (use SQL). Create a table from engine-neutral column types (integer, decimal, float, text, boolean, date_time, date, time, json, binary, uuid); PATCH /tables/{name} adds, drops, renames or modifies columns or renames the table; DELETE /tables/{name} drops it, permanently. Each of these is checked against the table's real columns.

Run SQL

One statement per call, run as the database's own user, cancelled after 15 seconds, and recorded in the organization's activity log. A statement that returns rows answers {"kind": "rows", "columns", "rows", "truncated"} (at most 200 rows); anything else answers {"kind": "command", "affected_rows"}.

curl

Code
curl -sS -X POST "$ZAROZ_API/databases/$DATABASE_ID/sql" -H "$AUTH" -H "Content-Type: application/json" \
  -d '{"sql": "SELECT id, email FROM users ORDER BY id LIMIT 10"}'

TypeScript

Code
const r = await call("POST", `/databases/${DATABASE_ID}/sql`, { sql: "SELECT count(*) AS n FROM users" });
if (r.body.data.kind === "rows") console.log(r.body.data.rows);

Python

Code
_, r = call("POST", f"/databases/{DATABASE_ID}/sql", {"sql": "SELECT count(*) AS n FROM users"})
if r["data"]["kind"] == "rows":
    print(r["data"]["rows"])

For anything bigger than a query, connect with psql or your application's driver instead: this endpoint is for inspecting and for small fixes.

Storage and the quota

curl

Code
curl -sS "$ZAROZ_API/databases/$DATABASE_ID/usage" -H "$AUTH"
# {"database": "...", "data": {"used_bytes": 1048576, "data_length": ..., "index_length": ..., "quota_bytes": 67108864, "over_quota_since": null}}

An included database has a quota; a dedicated one reports quota_bytes: null and is sized by its volume. When an included database is over its quota it is locked read-only (over_quota_since is set) and everything that would grow it (insert, update, create table, raw SQL) answers 507. Deleting is always allowed, including dropping tables: delete data and the lock lifts. The figure is cached for up to a minute and refreshed by any write made through this API.

Export, import and upload (included databases)

Moving data in and out of an included database runs as a queued operation: the call answers 202 with an operation id, not the result. Poll the operation until it leaves pending/processing (completed, or dead_letter when the retry budget is spent), then download.

• Export (POST .../transfers/export) writes a JSON dump of the row data only, never the CREATE TABLE statements. It is available for PostgreSQL.
• Import (POST .../transfers/import, body names an export's source_operation_id from the same subscription) restores a previous export, possibly into a different database of the subscription. The tables must already exist with the same names and columns; a row for a table that does not exist fails the whole operation.
• Upload (POST .../transfers/upload, the raw file as the body) imports a SQL dump: plain SQL or gzipped (.sql.gz), pg_dump --format=plain (including COPY ... FROM stdin), up to 256 MiB. A pg_dump -Fc archive, an empty or a binary file is 422. Statements run as the database's user in order and stop at the first error, so a failed import can leave the earlier statements applied. USE, CREATE|DROP|ALTER DATABASE and ALTER ... OWNER TO are skipped.

curl

Code
T=/subscriptions/$SUBSCRIPTION/shared-databases/$DATABASE_ID/transfers

# a plain SQL dump straight in
curl -sS -X POST "$ZAROZ_API$T/upload" -H "$AUTH" -H "Content-Type: application/octet-stream" \
  --data-binary @dump.sql.gz
# 202 {"operation_id": "...", "status": "pending", ...}

curl -sS "$ZAROZ_API$T/$OPERATION" -H "$AUTH"                         # poll
curl -sS -X POST "$ZAROZ_API$T/export" -H "$AUTH"                      # 202
curl -sS -o export.json "$ZAROZ_API$T/$EXPORT_OPERATION/download" -H "$AUTH"

TypeScript

Code
import { readFile } from "node:fs/promises";

const T = `/subscriptions/${SUB}/shared-databases/${DATABASE_ID}/transfers`;
const up = await fetch(`${process.env.ZAROZ_API}${T}/upload`, {
  method: "POST",
  headers: { Authorization: `Bearer ${process.env.ZAROZ_TOKEN}`, "Content-Type": "application/octet-stream" },
  body: await readFile("dump.sql.gz"),
});
const { operation_id } = await up.json();

for (;;) {
  const op = await call("GET", `${T}/${operation_id}`);
  if (op.body.status === "completed" || op.body.status === "dead_letter") {
    console.log(op.body.status, op.body.last_error);
    break;
  }
  await new Promise((r) => setTimeout(r, 3000));
}

Python

Code
import os, time, requests
from zaroz import call

T = f"/subscriptions/{SUB}/shared-databases/{DATABASE_ID}/transfers"
with open("dump.sql.gz", "rb") as f:
    up = requests.post(os.environ["ZAROZ_API"] + T + "/upload", data=f, timeout=600, headers={
        "Authorization": "Bearer " + os.environ["ZAROZ_TOKEN"],
        "Content-Type": "application/octet-stream",
    })
operation_id = up.json()["operation_id"]

while True:
    _, op = call("GET", f"{T}/{operation_id}")
    if op["status"] in ("completed", "dead_letter"):
        print(op["status"], op["last_error"])
        break
    time.sleep(3)

Polling is always safe: it never disturbs the worker. The import stops, failed, if it fills the quota while restoring.

What a dedicated database does differently

The quota, usage limits and the transfer routes above belong to included databases. For a dedicated database, move data with pg_dump and psql over a public port, and rely on the service backups for recovery. The browser and SQL routes work for both.