Build

SQL, export, and restore

Your app is a real Postgres database. When the rows API is not enough, send SQL. When you want your data out, export a standard pg_dump file.

Example data

berth tables create --app "$APP" profiles display_name:text:notnull
PROFILE_ID=$(curl -sS -X POST https://api.atberth.com/v1/apps/$APP/tables/profiles/rows \
  -H "Authorization: Bearer $SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"display_name":"Ada"}' | jq -er .row.id)
berth tables create --app "$APP" notes title:text:notnull
berth columns add --app "$APP" notes profile_id:uuid --references profiles --on-delete cascade
berth rows add --app "$APP" notes title="Buy milk" profile_id="$PROFILE_ID"
berth rows add --app "$APP" notes title="Buy more milk" profile_id="$PROFILE_ID"

Run SQL

SQL needs the secret key. Use $1, $2 placeholders and pass values separately, never by pasting them into the query string.

berth sql --app "$APP" 'select count(*) from notes'
berth sql --app "$APP" 'select title from notes where title ilike $1 order by title' --param '%more%'

curl -sS -X POST https://api.atberth.com/v1/apps/$APP/sql \
  -H "Authorization: Bearer $SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query":"select title, created_at from notes where title ilike $1","params":["%milk%"],"timeout_ms":5000}'

The result is {"command", "columns", "rows", "row_count", "truncated"}. At most 1,000 rows come back; truncated tells you when there were more. Queries stop after 10 seconds by default, 30 at most.

Joins and views

The rows API reads one table at a time and has no embedded joins. Join in SQL:

berth sql --app "$APP" 'select n.title, p.display_name from notes n join profiles p on p.id = n.profile_id order by n.title'

Keep longer statements in a file:

cat > notes_per_profile.sql <<'EOF'
create view notes_per_profile as
select p.id, p.display_name, count(n.id) as notes
from profiles p left join notes n on n.profile_id = p.id
group by p.id, p.display_name
EOF
berth sql --app "$APP" -f notes_per_profile.sql
berth sql --app "$APP" 'select * from notes_per_profile'

Read only mode

read_only runs the statement in a read only transaction, so a reporting query cannot change data by mistake. A write answers 403 read_only:

curl -sS -X POST https://api.atberth.com/v1/apps/$APP/sql \
  -H "Authorization: Bearer $SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d '{"query":"delete from notes","read_only":true}'

berth sql --app "$APP" --read-only --timeout-ms 2000 'select count(*) from profiles'

Call a Postgres function

Create a function with SQL, then call it by name with named JSON arguments. This is the same idea as supabase.rpc(). It needs the secret key. The response is the function's result as JSON: a value for a scalar function, an array of objects for a function that returns a table.

berth sql --app "$APP" 'create function notes_by_profile_count(pid uuid) returns bigint language sql as $$ select count(*) from notes where profile_id = pid $$'
berth rpc --app "$APP" notes_by_profile_count pid="$PROFILE_ID"

curl -sS -X POST https://api.atberth.com/v1/apps/$APP/rpc/notes_by_profile_count \
  -H "Authorization: Bearer $SECRET_KEY" \
  -H "Content-Type: application/json" \
  -d "{\"pid\":\"$PROFILE_ID\"}"

A missing function is 404 not_found. A publishable key or user token gets 403 forbidden, so call it from your server or from a function.

Schema from the API

SQL can do anything the app's role can. For everyday changes the schema routes are safer, because they keep policies and owner columns in step:

berth columns add --app "$APP" notes pinned:boolean --default false --not-null
berth columns rename --app "$APP" notes pinned starred
berth columns alter --app "$APP" notes starred --drop-default --nullable
berth indexes create --app "$APP" notes profile_id created_at --name notes_by_profile
berth indexes list --app "$APP" notes

Export

An export is a pg_dump custom format file of the app's database: schema, rows, and policies. It opens with the standard Postgres tools too (pg_restore --list backup.dump).

berth apps export "$APP" -o backup.dump
curl -sS -o backup-curl.dump https://api.atberth.com/v1/apps/$APP/export \
  -H "Authorization: Bearer $SECRET_KEY"
ls -l backup.dump backup-curl.dump

Restore

Restore always creates a new app from a dump, so it can never overwrite a live one. It needs the account key and answers like app creation, with fresh keys. Up to 200 MB.

berth apps restore "${APP}_copy" backup.dump
berth sql --app "${APP}_copy" 'select count(*) from notes'

curl -sS -X POST https://api.atberth.com/v1/apps/${APP}_curl/restore \
  -H "Authorization: Bearer $BERTH_ACCOUNT_KEY" \
  -H "Content-Type: application/octet-stream" \
  --data-binary @backup-curl.dump | jq '.app.name'

Clean up the copies:

berth apps delete "${APP}_copy" --yes
berth apps delete "${APP}_curl" --yes