# 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](/concepts/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](/guides/backups/overview). The samples use the helper from [Using the Zaroz API](/guides/api/overview). :::note A deployment can offer fewer database features than another. Ask [what it offers](/guides/api/overview#what-a-deployment-offers) and expect `501 capability-not-available` for what is absent. ::: ## Create an included database Check what you still have and which engines this deployment hosts, then create one. **curl** ```bash 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"}' ``` ```json { "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** ```ts 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** ```python 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](/units/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** ```bash curl -sS "$ZAROZ_API/databases/$DATABASE_ID/tables" -H "$AUTH" curl -sS "$ZAROZ_API/databases/$DATABASE_ID/tables/users" -H "$AUTH" ``` **TypeScript** ```ts const tables = await call("GET", `/databases/${DATABASE_ID}/tables`); console.log(tables.body.data.map((t: any) => t.name)); ``` **Python** ```python _, 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** ```bash 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** ```ts 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** ```python 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** ```bash 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** ```ts 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** ```python _, 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** ```bash 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** ```bash 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** ```ts 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** ```python 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](/guides/backups/overview) for recovery. The browser and SQL routes work for both.