Relational Database (PostgreSQL)
The tcb db command set provides SQL execution and migration management for CloudBase PostgreSQL environments.
tcb db execute: execute any SQL statement (DDL / DML / DQL).tcb db pg migration <subcommand>: manage PostgreSQL schema changes as versioned files. Supports create, list, fetch, apply, and history repair.
tcb db execute
Support for tcb db execute in PostgreSQL environments is available since CLI v3.4.0.
Execute a SQL statement. Common use cases include advanced Storage bucket management and RLS policy management.
tcb db execute --sql '<sql>' [options]
Parameters
| Parameter | Description | Default |
|---|---|---|
-s, --sql <sql> | The SQL statement to execute | Required |
--role <role> | Specify the role to execute the SQL (accepts any valid role, including user-defined roles) | — |
-e, --env-id <envId> | Environment ID | Required |
--json | Output results in JSON format | — |
CloudBase provides a built-in read-only role cloudbase_read_only_user. Using --role cloudbase_read_only_user prevents unintended write operations from succeeding.
Examples
# Basic query
tcb db execute -e <pgEnvId> --sql "SELECT 1"
# List Storage buckets
tcb db execute -e <pgEnvId> --sql "SELECT name, public FROM storage.buckets ORDER BY created_at"
# Use the built-in read-only role to prevent accidental writes
tcb db execute -e <pgEnvId> --role cloudbase_read_only_user --sql "SELECT name, public FROM storage.buckets"
# Create a Storage bucket
tcb db execute -e <pgEnvId> --sql "INSERT INTO storage.buckets (id, name, public, created_at, updated_at) VALUES ('avatars', 'avatars', false, now(), now())"
tcb db pg migration
Manage PostgreSQL schema changes as version-controlled files. The command set covers five actions: create, list, fetch, apply, and repair.
The tcb db pg migration subcommands require @cloudbase/cli v3.7.0+. Check your version with tcb -v and upgrade via npm i -g @cloudbase/cli.
Directory layout & naming rules
- The local migrations directory is fixed at
cloudbase/migrations/(relative to the current working directory when running the command). - Filename format:
<version>_<name>.sqlversion: 14-digit UTC timestamp (YYYYMMDDHHmmss), generated automatically bymigration new.name: only lowercase letters and underscores are allowed (e.g.create_users_table,add_index).
- The directory is created automatically by
migration newwhen it does not exist.migration upandmigration repairwill fail explicitly if the directory is missing.
Common conventions
| Item | Description |
|---|---|
-e, --env-id <envId> | Required for all subcommands except migration new |
--json | Global flag. Outputs structured JSON, suitable for scripts / CI |
| Login | Every subcommand checks login before running; unauthenticated calls are routed through tcb login |
tcb db pg migration new
Create a new empty migration file. The filename is prefixed with the current UTC timestamp.
tcb db pg migration new <name>
Parameters
| Parameter | Description | Default |
|---|---|---|
<name> | Migration name. Only lowercase letters and underscores are allowed | Required |
Examples
# Create an empty file
tcb db pg migration new create_users_table
# Pipe SQL from stdin directly into the new file
echo "CREATE TABLE t(id int)" | tcb db pg migration new add_t
This produces a file such as cloudbase/migrations/20260728141530_create_users_table.sql. If content is piped in via stdin, it is written into the file; otherwise the file is created empty for later editing.
tcb db pg migration list
List PostgreSQL migrations. By default, this compares the local directory against remote applied records.
# Default: compare local vs remote
tcb db pg migration list -e <envId>
# Remote-only (optionally paginated)
tcb db pg migration list -e <envId> --remote-only [--limit <n>] [--offset <n>]
Parameters
| Parameter | Description | Default |
|---|---|---|
--remote-only | Query remote applied migrations only; do not scan the local directory | false |
--limit <n> | Page size, range [1, 500]. Only valid under --remote-only | 100 |
--offset <n> | Page offset. Only valid under --remote-only | 0 |
-e, --env-id <envId> | Environment ID | Required |
--json | Output JSON | — |
--remote-onlyDefault mode needs a full local-vs-remote comparison and therefore always pulls remote migrations in full. Passing --limit / --offset in default mode raises an error.
Examples
# Compare local and remote
tcb db pg migration list -e <envId>
# JSON output for scripts
tcb db pg migration list -e <envId> --json
# Fetch the first 200 remote applied migrations
tcb db pg migration list -e <envId> --remote-only --limit 200 --offset 0
The output is a three-column table Local | Remote | Time (UTC). Rows that only exist on one side leave the other column blank, making unsynced items easy to spot.
tcb db pg migration fetch
Pull remote applied migrations to the local directory. Fetches everything by default; pass a version to pull a single one.
# Pull all remote migrations
tcb db pg migration fetch -e <envId>
# Pull a specific version
tcb db pg migration fetch <version> -e <envId>
Parameters
| Parameter | Description | Default |
|---|---|---|
[version] | Optional 14-digit version. If omitted, all migrations are pulled | — |
-f, --force | Overwrite existing local files with the same name | false |
--dry-run | Preview the files that would be written; do not actually write | false |
-e, --env-id <envId> | Environment ID | Required |
--json | Output JSON | — |
Examples
# First-time onboarding: pull all remote applied migrations locally
tcb db pg migration fetch -e <envId>
# Pull a specific version
tcb db pg migration fetch 20260724153000 -e <envId>
# Overwrite local files with remote content
tcb db pg migration fetch -e <envId> --force
# Preview only, do not write
tcb db pg migration fetch -e <envId> --dry-run
Existing local files are skipped by default; use --force to overwrite.
tcb db pg migration up
Apply pending local migrations. Internally the command first calls preview to build an execution plan, then submits the push task and polls until it reaches a terminal state.
tcb db pg migration up -e <envId> [--dry-run] [--include-all]
Parameters
| Parameter | Description | Default |
|---|---|---|
--dry-run | Preview the execution plan; do not actually submit | false |
--include-all | Allow out-of-order migrations (accept pending versions older than the remote latest version) | false |
-e, --env-id <envId> | Environment ID | Required |
--json | Output JSON | — |
Examples
# Preview only
tcb db pg migration up -e <envId> --dry-run
# Apply
tcb db pg migration up -e <envId>
# Allow out-of-order versions (when a local version is older than the remote latest)
tcb db pg migration up -e <envId> --include-all
On checksum_mismatch, run tcb db pg migration fetch <version> -e <envId> --force to sync remote content, or use migration repair to fix history records.
tcb db pg migration repair
Repair remote migration history records. This does not execute SQL. Use it to align local and remote states, e.g. backfilling history manually, marking rollbacks, or aligning checksums.
tcb db pg migration repair <version> --status <applied|reverted> --reason <reason> -e <envId>
Parameters
| Parameter | Description | Default |
|---|---|---|
<version> | 14-digit migration version | Required |
--status <status> | Target status: applied or reverted | Required |
--reason <reason> | Repair reason (for audit) | Required |
--dry-run | Only print the repair plan; do not execute | false |
-e, --env-id <envId> | Environment ID | Required |
--json | Output JSON | — |
Automatic Name resolution
repair does not require an explicit Name. It infers the name based on the target status:
--status applied: look up the file matching<version>in the localcloudbase/migrations/directory and use its SQL content as the repair payload. Missing files raise an error.--status reverted: look up theNamefrom remote history for thatversion. Local files are ignored.
Examples
# Manually mark a migration as applied (e.g. the SQL was already run against the database)
tcb db pg migration repair 20260724153000 --status applied --reason "manual_fix" -e <envId>
# Mark a remote history record as reverted
tcb db pg migration repair 20260724153000 --status reverted --reason "rollback_record" -e <envId>
# Preview the repair plan first
tcb db pg migration repair 20260724153000 --status applied --reason "checksum_align" -e <envId> --dry-run
Typical workflow
# 1. First-time onboarding: pull all remote applied migrations locally
tcb db pg migration fetch -e <envId>
# 2. New change: create a migration file and edit it
tcb db pg migration new create_orders_table
# Edit cloudbase/migrations/<version>_create_orders_table.sql with your SQL
# 3. Preview the execution plan
tcb db pg migration up -e <envId> --dry-run
# 4. Apply
tcb db pg migration up -e <envId>
# 5. Verify state
tcb db pg migration list -e <envId>