# Database Clusterbase provides managed Postgres databases, each running on its own isolated machine with a SQL-over-HTTP proxy for secure access. A database can optionally be attached to a [Project](/docs/ccp/projects) or left standalone. ## Create a Database [#create-a-database] ```bash ccp db create --name my-app-db # ✓ Created database my-app-db # # ID db-c1cf9a9be667 # Host db-c1cf9a9be667.clusterbase.dev # Token a9db7b185e40... # # ✓ Token saved to .ccp/config.json # ✓ Injected managed database environment into function fn-... # ✓ Wrote 2 vars to .env (DATABASE_HTTP_URL, DATABASE_HTTP_TOKEN) ``` If you run this from a project directory with a `.ccp/config.json`, the database credentials for the HTTP proxy (`DATABASE_HTTP_URL` and `DATABASE_HTTP_TOKEN`) are automatically injected as environment variables into your linked function **and** written to your local `.env` file, so `ccp dev` and your deployed function see the same values. A redeploy [merges env rather than replacing it](/docs/ccp/environment-variables#how-env-updates-merge), so these injected values are preserved server-side even when they're absent from your local `.env`. `DATABASE_HTTP_URL` is written as a bare host (`db-.clusterbase.dev`) with no scheme. Prepend `https://` in your handler code, which matches the pattern shown in the [Querying from Handlers](#querying-from-handlers) examples below. If the linked function is a networked serverless App bound to this database, `ccp db create` also injects a native PostgreSQL `DATABASE_URL` (a full `postgres://` connection string) server-side. That value is server-managed and is **not** mirrored into your local `.env` — only the HTTP compatibility values are — so a code-only `ccp deploy` from this project never overwrites it. Use `DATABASE_URL` with a Postgres client library (Postgres.js, Drizzle, etc.) inside the App; use `DATABASE_HTTP_URL`/`DATABASE_HTTP_TOKEN` for the SQL-over-HTTP proxy as described in [Querying from Handlers](#querying-from-handlers). If a database with the given name failed to finish creating (or the create was canceled) and the platform is still cleaning up the old attempt, retrying `ccp db create` with the same name returns `503 provision_pending` until cleanup finishes. Retry after a moment — once cleanup completes, the name is free again and the create proceeds normally. ### Private Networking [#private-networking] Pass `--network ` to put the database's backing VM on the private overlay network ``, alongside any compute service that declares the same `[network].name`, so the two reach each other without going through the public host: ```bash ccp db create --name my-app-db --network backend ``` The network name is a lowercase DNS label (1-63 characters), matching the [`[network].name` rule for `ccp compute deploy`](/docs/ccp/compute#network--private-overlay-networking-optional). A few things to know: * Overlay networking is disabled by default; if it isn't enabled for your environment, the request fails with `503 overlay_disabled`. * An invalid network name fails with `400 invalid_network_name`. * A name collision with another service already on that network fails with `409 network_service_name_conflict`. * The database only publishes onto the network once it's durably ready; a failed or in-progress provision never appears on the network. Compute services on that network reach the database at `..internal`, wherever each one is placed. ## Native PostgreSQL Access [#native-postgresql-access] By default, a database only accepts the SQL-over-HTTP proxy — there's no way to connect a normal PostgreSQL client (`psql`, an ORM driver, etc.) directly over the wire protocol. **Native access** opens a direct PostgreSQL login on port `5432` for the `application` role, alongside the HTTP proxy, so tools that speak the wire protocol can connect over your [private overlay network](#private-networking). There's no `ccp db` subcommand for this yet — manage it by calling the infra API directly with your CLI auth token: ```bash curl -X POST https://api.clusterbase.dev/api/v1/databases/db-c1cf9a9be667/native-access/enable \ -H "Authorization: Bearer $(ccp auth print-access-token)" # {"credential_generation":1,"password":"9f2c...redacted...a71b"} ``` The `password` field is the plaintext PostgreSQL password for the `application` role and is returned **only** by the operation that issues it — it is never retrievable again afterward. Connect with any PostgreSQL client at `..internal:5432`, database `default`, user `application`. * The database must already have a [ready network attachment](#private-networking) (`--network ` at create time); otherwise these operations fail with `409 native_access_requires_network`. * A concurrent conflicting change to native-access state fails with `409 native_access_conflict`. * If credential reconciliation for a previous operation hasn't finished yet, the next one fails with `503 native_access_pending` — retry after a moment. ### Connection Limits [#connection-limits] Native PostgreSQL connections — whether opened directly against `..internal:5432` or via the `DATABASE_URL` injected into a networked Serverless App — are bounded, not unlimited: * Each Serverless isolate permits at most 4 active and 2 pending connections to a database, with a 10-second connect timeout. Excess connections from the same isolate are rejected locally — `managed_database_active_limit`, `managed_database_pending_limit`, or `managed_database_connect_timeout` — rather than queued indefinitely. * The database's own gateway permits at most 8 active and 4 pending native connections per deployment across all runtime instances, with a 10-second backend connect timeout and a 10-minute idle timeout on established connections. A connection that arrives once those ceilings are reached is rejected with `429 Too Many Requests`. * Keep your pool small and reuse warm connections (e.g. a small Postgres.js/Drizzle pool) rather than opening one connection per request. ### Rotate the Credential [#rotate-the-credential] ```bash curl -X POST https://api.clusterbase.dev/api/v1/databases/db-c1cf9a9be667/native-access/rotate \ -H "Authorization: Bearer $(ccp auth print-access-token)" # {"credential_generation":2,"password":"...new plaintext password..."} ``` Rotation issues a fresh password, invalidates the old one, and terminates any already-authenticated `application` sessions using it. ### Disable Native Access [#disable-native-access] ```bash curl -X POST https://api.clusterbase.dev/api/v1/databases/db-c1cf9a9be667/native-access/disable \ -H "Authorization: Bearer $(ccp auth print-access-token)" # {"credential_generation":3} ``` Disabling revokes the `application` login and terminates any authenticated sessions; the HTTP proxy master token keeps working throughout. Calling `enable` again afterward issues a new password. ### Reading the Current Generation [#reading-the-current-generation] `ccp db info` and the underlying database read don't expose the password, but every database read includes a non-secret `native_credential_generation` counter that increments on each enable, rotate, or disable — use it to detect that a rotation happened without needing the plaintext. ## List Databases [#list-databases] ```bash ccp db list # • my-app-db ready db-c1cf9a9be667 # db-c1cf9a9be667.clusterbase.dev ``` `ccp db ls` (alias for `ccp db list`) shows every database in the resolved organization, sorts the project-linked database first, and marks its row `linked`. Pass `--json` to emit the complete, unstyled list — including the `organization_id` and `linked_database_id` — instead of the styled table. ## Show Database Details [#show-database-details] ```bash ccp db info db-c1cf9a9be667 # Database my-app-db # # ID db-c1cf9a9be667 # Status ready # Host db-c1cf9a9be667.clusterbase.dev # Database default # User clusterbase ``` `DB_ID` is now optional: run `ccp db info` with no argument to inspect the database linked in `.ccp/config.json` or the current `.env` (the same identity `ccp db exec`/`migrate`/`connect` use). Outside a linked project, pass the ID explicitly. ## Delete a Database [#delete-a-database] ```bash ccp db destroy db-c1cf9a9be667 # Delete database db-c1cf9a9be667? This will destroy all data. (y/N) ``` Use `-y` to skip the confirmation prompt. Destroying a database also removes `DATABASE_HTTP_URL` and `DATABASE_HTTP_TOKEN` (and any legacy `DATABASE_URL`/`DATABASE_TOKEN`) from your local `.env` file, mirroring the sync that `ccp db create` performs. This keeps the next `ccp deploy` from re-uploading the stale credentials and silently re-pointing the function at the destroyed database. The local cleanup runs only when the current project is linked to the database being destroyed (or has no database linked); destroying a different database by ID from a project linked to another one leaves that project's `.env` untouched. `ccp db destroy` is the only supported way to delete a database. Each managed database runs on its own dedicated VM, and deleting that VM directly through the VM API (`DELETE /api/v1/vms/{vm_id}`) is refused with `409 vm_backs_managed_database`, naming the database and pointing back here. The check fails closed: if the platform cannot determine whether a VM backs a database, it refuses the delete rather than risk it. Only the database surface tears down backups, routes, and env vars in the right order. Deletion is durable and idempotent: once requested it survives a crash or retry. If you re-run `ccp db destroy` (or call the delete endpoint again) while an earlier request is still tearing down the backing VM, backups, and linked env vars, the API returns `503 delete_pending` — wait a few seconds and retry rather than treating it as a failure. ## Interactive SQL Shell [#interactive-sql-shell] Open an interactive SQL session with `ccp db connect`: ```bash ccp db connect ``` This opens a terminal UI with a SQL prompt. Type queries ending with `;` to execute them: ``` ccp db connect ● connected · database default · db-c1cf9a9be667.clusterbase.dev › SELECT * FROM users; id │ name │ email ───┼───────┼────────────── 1 │ Alice │ alice@co.com (1 row) ✓ 43ms ╭──────────────────────────────────────────────╮ │› Enter SQL, ending with ; │ ╰──────────────────────────────────────────────╯ ↑↓ history · \dt tables · ctrl+d exit ``` ### Meta-commands [#meta-commands] | Command | Description | | ------------ | --------------------------------- | | `\dt` | List all tables | | `\d ` | Describe a table (columns, types) | | `\q` | Quit | ### Navigation [#navigation] * **Up/Down arrows** — cycle through query history * **Ctrl+D** or **Ctrl+C** — exit (clears input if non-empty) ## One-Shot SQL [#one-shot-sql] Execute a single SQL statement without opening the interactive shell: ```bash ccp db exec "SELECT count(*) FROM users;" # # count # ───── # 42 # # (1 row) # # 38ms ``` Works for any SQL — SELECT, INSERT, UPDATE, DELETE: ```bash ccp db exec "INSERT INTO users (name, email) VALUES ('Bob', 'bob@co.com');" # # 1 row affected · 45ms ``` Statements with `RETURNING` clauses display the returned rows: ```bash ccp db exec "DELETE FROM users WHERE id = 1 RETURNING *;" ``` ## Migrations [#migrations] Run SQL migration files against your database with `ccp db migrate`. Migrations are tracked in a `_ccp_migrations` table so they only run once. ### Setup [#setup] Create a `migrations/` directory in your project with numbered `.sql` files: ``` migrations/ 001_create_users.sql 002_add_posts.sql 003_add_indexes.sql ``` Each file can contain multiple SQL statements. PL/pgSQL functions with `$$` dollar-quoting are fully supported. ### Run Migrations [#run-migrations] ```bash ccp db migrate # › Running 3 pending migrations... # # ✓ 001_create_users.sql 48ms # ✓ 002_add_posts.sql 37ms # ✓ 003_add_indexes.sql 42ms # # ✓ 3 migrations applied ``` Running again is safe — only pending migrations are applied: ```bash ccp db migrate # ✓ All 3 migrations already applied ``` ### Check Status [#check-status] See which migrations have been applied without running anything: ```bash ccp db migrate --status # › Migration status: # # ✓ 001_create_users.sql # ✓ 002_add_posts.sql # ○ 003_add_indexes.sql # # 1 pending migration ``` ### Options [#options] | Flag | Description | | ----------------- | ------------------------------------------------------------------------------------------------------ | | `--dir ` | Migrations directory (default: `./migrations`) | | `--db-id ` | Database ID (if omitted, reads `.ccp/config.json`, falling back to `DATABASE_HTTP_URL` in `.env`) | | `--token ` | Database token (if omitted, reads `.ccp/config.json`, falling back to `DATABASE_HTTP_TOKEN` in `.env`) | | `--status` | Show migration status without applying | If you provide `--db-id`, you must also provide `--token` (and vice versa). This same config-first, `.env`-fallback resolution applies to `--db-id`/`--token` on `ccp db exec`, `ccp db connect`, `ccp db client-access`, and `ccp db backup`. A database ID derived from `DATABASE_HTTP_URL` must be a host in the environment's database domain — a foreign host is rejected before any request. Native `DATABASE_URL` is never read as an HTTP credential. ### How It Works [#how-it-works] Each migration runs in a database transaction. The migration SQL and the tracking insert are atomic — if the migration fails, nothing is committed. Migrations are applied in filename order, so use a numeric prefix (`001_`, `002_`, etc.) to control the sequence. ## Backups [#backups] Databases are backed up automatically once per day and backups are retained for 7 days. You can also create manual backups on demand. If the database's backing VM is paused when a backup runs (scheduled or manual), the backup automatically wakes it first, then lets it return to idle afterward — you don't need to wake the database yourself before backing it up. ### Create a Backup [#create-a-backup] ```bash ccp db backup create # ✓ Backup created # ID bkp-8f3e2a1c # Created 2026-04-12 14:22 UTC ``` Use `--db-id ` to target a database other than the one in your `.ccp/config.json`. ### List Backups [#list-backups] ```bash ccp db backup list # • bkp-8f3e2a1c manual 2026-04-12 14:22 UTC # • bkp-7d1f9e4b scheduled 2026-04-11 00:00 UTC # • bkp-6c9a2d7e scheduled 2026-04-10 00:00 UTC ``` `ls` is an alias for `list`. ### Restore from a Backup [#restore-from-a-backup] ```bash ccp db backup restore bkp-8f3e2a1c # Restore database db-c1cf9a9be667 from bkp-8f3e2a1c? This will overwrite current data. (y/N) ``` Use `-y` / `--yes` to skip the confirmation prompt. ### Delete a Backup [#delete-a-backup] ```bash ccp db backup delete bkp-8f3e2a1c ``` `rm` is an alias for `delete`. If a restore fails, the platform retains the exact backup it restored from so recovery can be retried; deleting that specific backup while it's retained fails with `409 restore_backup_in_use`. Delete another backup, or wait for the database to leave `restore_failed`, and retry. ## Querying from Handlers [#querying-from-handlers] Access your database from serverless functions using the SQL-over-HTTP proxy: ```typescript const res = await fetch(`https://${process.env.DATABASE_ID}.clusterbase.dev/query`, { method: "POST", headers: { "Content-Type": "application/json", Authorization: `Bearer ${process.env.DATABASE_HTTP_TOKEN}`, }, body: JSON.stringify({ sql: "SELECT * FROM users WHERE id = $1", params: [42], }), }); const { rows } = await res.json(); ``` ### Endpoints [#endpoints] | Endpoint | Method | Description | | -------------- | ------ | --------------------------------------------------------- | | `/query` | POST | Execute a SELECT query, returns `{ rows, fields }` | | `/execute` | POST | Execute INSERT/UPDATE/DELETE, returns `{ rows_affected }` | | `/transaction` | POST | Execute multiple statements atomically | ### Request Format [#request-format] ```json { "sql": "SELECT * FROM users WHERE id = $1", "params": [42] } ``` Parameters use `$1`, `$2`, etc. for positional binding. ### Transaction Format [#transaction-format] ```json { "statements": [ { "sql": "INSERT INTO users (name) VALUES ($1)", "params": ["Alice"] }, { "sql": "INSERT INTO logs (action) VALUES ($1)", "params": ["user_created"] } ] } ``` ## Client-Mode Authentication [#client-mode-authentication] By default, the database accepts only the master token (`DATABASE_HTTP_TOKEN`) and runs every query as the privileged `clusterbase` role. That's fine for server-side handlers, but it means you can't safely expose the db-proxy host directly to browsers or scope queries per user without manual `WHERE user_id = $N` filters. **Client-mode** opens two additional auth paths on the same database: * **Anon** — requests with no `Authorization` header are allowed and run as the read-only `anon` Postgres role. * **JWT** — requests with a `Bearer ` header are validated against your OIDC issuer; the JWT's `sub` is exposed inside Postgres via `auth.uid()`, and the query runs as the `authenticated` role. The master token continues to work alongside both. Row-Level Security policies then scope rows per user via `auth.uid()`. Client-mode is off at create time and can be toggled on or off at any point without redeploying or recreating the database. ### Enable Client-Mode [#enable-client-mode] ```bash ccp db client-access enable # ✓ client-access enabled on db-c1cf9a9be667 ``` This commits the desired mode and reconciles db-proxy to match it, in place. In-flight queries can fail during reconciliation and should be retried; the master path resumes once convergence completes. If convergence can't finish within the request, the API returns `client_access_pending` (503) — retry the toggle; background recovery also resumes the same operation on its own. Don't destroy and recreate the database to work around a pending toggle. Run with no arguments to target the DB linked in `.ccp/config.json`, or pass `ccp db client-access enable ` to target another DB. ### Disable Client-Mode [#disable-client-mode] ```bash ccp db client-access disable # ✓ client-access disabled on db-c1cf9a9be667 ``` Closes the anon and JWT paths. The master token continues to work. Both commands are idempotent — calling `enable` on an already-enabled DB is a no-op success and does not restart the proxy. A toggle already in progress for the same DB returns 409 (`status_conflict`); retry once the prior toggle settles. ### Anon Queries [#anon-queries] Once enabled, requests with no `Authorization` header run as the `anon` role: ```typescript const res = await fetch(`https://${process.env.DATABASE_HTTP_URL}/query`, { method: "POST", headers: { "Content-Type": "application/json" }, body: JSON.stringify({ sql: "SELECT * FROM public_posts", params: [] }), }); ``` By default `anon` has no permissions — grant only what you want exposed publicly: ```sql GRANT SELECT ON public_posts TO anon; ``` ### JWT Queries [#jwt-queries] Forward a user's Cluster OIDC access token as the bearer (see [Sign in with Cluster](/docs/ccp/oidc) for how to obtain one): ```typescript const res = await fetch(`https://${process.env.DATABASE_HTTP_URL}/query`, { method: "POST", headers: { "Content-Type": "application/json", Authorization: `Bearer ${userJWT}`, }, body: JSON.stringify({ sql: "SELECT * FROM todos", params: [] }), }); ``` The query runs as the `authenticated` role with `auth.uid()` populated from the JWT's `sub` claim. The JWT is validated against the OIDC issuer's JWKS — invalid, expired, or wrong-issuer tokens return 401. ### Row-Level Security with `auth.uid()` [#row-level-security-with-authuid] The typical pattern: enable RLS on user-owned tables and scope rows by the authenticated user's `sub`. ```sql CREATE TABLE todos ( id TEXT PRIMARY KEY, user_id TEXT NOT NULL, title TEXT NOT NULL ); ALTER TABLE todos ENABLE ROW LEVEL SECURITY; CREATE POLICY todos_owner ON todos FOR ALL TO authenticated USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid()); GRANT SELECT, INSERT, UPDATE, DELETE ON todos TO authenticated; ``` With this policy, `SELECT * FROM todos` returns only the calling user's rows even with no `WHERE` clause, and inserts or updates referencing other users' rows are rejected. ### Roles [#roles] | Role | Used when | Defaults | | --------------- | ------------------------------------ | --------------------------------------- | | `clusterbase` | Master token (`DATABASE_HTTP_TOKEN`) | Owner of all objects; bypasses RLS | | `authenticated` | Valid JWT | Subject to RLS; grant tables explicitly | | `anon` | No `Authorization` header | Subject to RLS; grant tables explicitly | ### Requirements [#requirements] * The DB must be running. Paused DBs return 409; wake the DB with any query first, then retry the toggle. * If reconciliation can't finish within the request, the toggle returns 503 (`client_access_pending`); retry it, or wait — background recovery resumes the same operation automatically. * The toggled state persists across pause/resume and across db-proxy restarts — you don't need to re-enable after a wake. ## Aliases [#aliases] | Command | Alias | | ---------------- | ----------- | | `ccp db list` | `ccp db ls` | | `ccp db destroy` | `ccp db rm` |