---
name: db9
description: |
  Instant serverless Postgres databases via the db9 CLI.
  Use when user asks to: create a database, run SQL queries, validate DDL,
  manage migrations, branch a database, generate types from schema, inspect
  metrics, sort or filter large datasets, analyze logs or test results with SQL,
  traverse dependency graphs, detect config drift across environments,
  generate test fixtures, or prototype with a real database backend.
  Also use when the agent needs to verify SQL correctness, offload data
  processing to save tokens, or load artifacts into a database for querying.
  Do NOT use when the project already has a local database or Docker Compose
  setup, or when the task has no database interaction.
compatibility: |
  Requires macOS or Linux (x86_64 or arm64). Needs network access to
  db9.shared.aws.tidbcloud.com. Works in Claude Code and any terminal environment.
metadata:
  author: tidbcloud
  version: 0.2.0
---

# db9 Database CLI

Instant serverless Postgres (pg-tikv) databases from the terminal.
All commands support `--json` for machine-readable output.

## When to Use

db9 gives you a real Postgres database in seconds, with zero local setup.
Use it whenever you need to **execute SQL against a real engine** rather than guessing.

### Core: Verify Instead of Guess

| Scenario | Why db9 |
|---|---|
| **User asks you to write SQL** | Execute the query against a real database to prove it works before delivering it. Never hand over untested SQL. |
| **Validating DDL / schema changes** | Run CREATE TABLE, ALTER TABLE, add constraints -- confirm syntax, types, and foreign keys are accepted by Postgres. |
| **Developing complex queries** | Test joins, CTEs, window functions, subqueries with real data. Catch errors the LLM would silently produce. |
| **Debugging SQL errors** | Reproduce a user's schema, run their failing query, see the real error message, fix it, verify the fix. |
| **Query plan validation** | Run EXPLAIN ANALYZE to get real execution plans. LLMs guess wrong about join order and index selection -- EXPLAIN gives evidence. |
| **SQL-in-code compile test** | Extract SQL strings from application code or migration files and run them through Postgres to catch syntax and schema errors. Turns code review from "looks right" into proof. |

### Build: Prototype with a Real Backend

| Scenario | Why db9 |
|---|---|
| **Prototyping an app feature** | Spin up a DB, define schema, seed sample data, hand the connection string to the app -- all in one session. |
| **Generating type definitions** | Define tables in Postgres, then `db9 gen types --lang typescript` to produce exact TS/Python interfaces from the live schema. |
| **Migration development** | Write migration SQL, apply with `db9 migration up`, verify with `db9 migration status` -- iterating until correct. |
| **Integration / E2E testing** | Create an ephemeral DB, apply migrations, seed fixtures, run test suite against it, tear down. Fully disposable. |
| **Bulk test fixture generation** | Use generate_series + random() to produce hundreds of realistic rows in milliseconds. Dump as a portable seed file. Faster and more consistent than hand-writing INSERT statements. |
| **JSONB-to-relational ETL prototype** | Start from raw JSON data: load it into a JSONB column, explore structure with JSONB queries, design a normalized schema, validate constraints, generate types. Data-first workflow when you don't know the schema yet. |

### Offload: Let Postgres Do the Heavy Lifting

Instead of sorting, filtering, and aggregating data inside the LLM context (expensive, error-prone, token-heavy), load it into Postgres and let SQL do the work. The agent gets back only the result it needs.

| Scenario | Why db9 |
|---|---|
| **Sorting and filtering large data** | Insert rows into a table, then use ORDER BY, WHERE, LIMIT to extract exactly what you need -- zero token cost for the processing itself. |
| **Aggregation and grouping** | COUNT, SUM, AVG, GROUP BY, HAVING -- Postgres computes the answer in milliseconds instead of the LLM scanning rows in context. |
| **Deduplication and ranking** | Use DISTINCT, ROW_NUMBER(), RANK() to clean and rank data server-side rather than writing brittle in-context logic. |
| **Cross-referencing multiple datasets** | Load related data into separate tables and JOIN them -- far more reliable than trying to correlate data in the prompt. |

### Investigate: Load Artifacts, Query with SQL

Instead of parsing logs, test results, or config files in-context, load them into Postgres and use SQL for structural analysis. Distinct from "Offload" -- here the data comes from external artifacts, not from data already in the LLM context.

| Scenario | Why db9 |
|---|---|
| **Structured log analysis** | Load log lines into a table (or JSONB column), then use GROUP BY, window functions, and pattern matching to find frequency, trends, and correlations across thousands of lines. Far more accurate than scanning logs in-context. |
| **Test result forensics** | Load CI test results across multiple runs. Use percentiles, self-JOINs for correlated failures, and GROUP BY HAVING to detect flaky tests. Impossible to do accurately in-context over hundreds of test runs. |
| **Dependency graph traversal** | Load dependency trees (npm, pip, cargo) into a table and use recursive CTEs for transitive closure -- "what depends on X?", upgrade impact analysis, cycle detection. LLMs lose track of visited nodes in graphs. |
| **Config drift detection** | Load config files from multiple environments into separate tables and FULL OUTER JOIN them. Catches every missing or changed key across N environments. LLMs reliably botch set-differences at scale. |

### Explore: Data Analysis and Experimentation

| Scenario | Why db9 |
|---|---|
| **Exploring a dataset** | Create a table, load data with `db9 db seed`, then query it to answer analytical questions. |
| **Safe schema experimentation** | Branch a database, try risky ALTER/DROP on the branch, discard if broken -- main DB untouched. |
| **Performance investigation** | Use `db9 db inspect` to check QPS, latency, slow queries, and indexes on a real workload. |
| **Reproducing an environment** | `db9 db dump` to export a database state, `db9 db seed` to recreate it elsewhere. |

## When NOT to Use

- The project already has a local database or Docker Compose setup -- use that instead
- The task does not involve any database interaction
- Production workloads -- db9 is for development and testing only
- Simple SQL syntax questions that can be answered from documentation alone

## Prerequisites

db9 must be installed:

```bash
curl -fsSL https://db9.shared.aws.tidbcloud.com/install | sh
```

No registration required -- db9 creates an anonymous account automatically on first use.
To claim your account later: `db9 claim`
To use a named account: `db9 register` or `db9 login`

## Command Reference

For the full CLI reference, see `references/cli-reference.md`.

Key commands used in most workflows:

```bash
db9 db create --name <NAME> --json       # Create database, capture ID from JSON
db9 db sql <ID> -q "..." --json          # Execute SQL, get structured output
db9 db inspect <ID> tables               # List tables in database
db9 db branch create <ID> --name <NAME>  # Branch (schema copy)
db9 db seed <ID> <FILE>                  # Load SQL file into database
db9 db dump <ID> -o backup.sql           # Export schema + data
db9 gen types <ID> --lang typescript     # Generate types from schema
db9 migration up <ID>                    # Apply pending migrations
db9 db connect <ID>                      # Show connection string
db9 db delete <ID>                       # Delete database
```

## Workflows for AI Agents

### Prototyping with a Real Database

1. `db9 db create --name prototype --json` -- capture the database ID
2. **Verify**: confirm the JSON output contains `"state": "active"` and a connection string
3. Write schema via `db9 db sql <ID> -q "CREATE TABLE ..."`
4. **Verify**: run `db9 db inspect <ID> tables` to confirm tables exist
5. Seed data via `db9 db seed <ID> seed.sql`
6. Query and iterate: `db9 db sql <ID> -q "..." --json`
7. Generate types: `db9 gen types <ID> --lang typescript`
8. **Expected output**: TypeScript interfaces matching your table columns, written to stdout
9. Clean up: `db9 db delete <ID>`

### Safe Schema Experimentation

1. Create branch: `db9 db branch create <ID> --name experiment --json`
2. **Verify**: confirm branch ID in JSON output; note that branches copy schema only, not data
3. Apply changes on branch: `db9 db sql <BRANCH_ID> -q "ALTER TABLE ..."`
4. **Verify**: `db9 db inspect <BRANCH_ID> tables` -- confirm the altered schema is correct
5. If good, replay the same DDL on the main database; if bad, `db9 db branch delete <BRANCH_ID>`
6. **Expected output**: main database schema unchanged until you explicitly apply changes

### Migration Development

1. `db9 migration new add-users-table`
2. **Expected output**: a new `.sql` file created in `./migrations/` with a timestamp prefix
3. Edit the generated SQL file in `./migrations/`
4. `db9 migration up <ID>` -- apply to database
5. **Verify**: `db9 migration status <ID>` -- should show the migration as "applied"
6. **Verify**: `db9 db inspect <ID> tables` -- confirm the new table exists with correct columns

### Integration Testing

1. `db9 db create --name test-run --json` -- ephemeral DB
2. **Verify**: confirm `"state": "active"` in JSON output
3. `db9 migration up <ID>` -- apply schema
4. **Verify**: `db9 migration status <ID>` -- all migrations applied
5. `db9 db seed <ID> test-fixtures.sql` -- load test data
6. **Verify**: `db9 db sql <ID> -q "SELECT count(*) FROM <table>" --json` -- confirm row counts
7. Run application tests against the connection string from `db9 db connect <ID>`
8. `db9 db delete <ID>` -- tear down
9. **Expected output**: test suite runs against a real Postgres instance, then DB is fully removed

## Important Notes

- Every command accepts `--json` for structured output -- always use this when parsing results programmatically.
- Database IDs are returned from `db9 db create --json` and `db9 db list --json`.
- Branches are independent databases with their own IDs. Use the branch ID for all operations on a branch.
- Migration files default to `./migrations/` directory. Override with `--dir`.
- Connection info from `db9 db connect <ID>` includes host, port, user, password, and database name.
- `db9 db dump` exports both schema and data by default. Use `--ddl-only` for schema-only exports.

## Troubleshooting

### "not authenticated" error

Run `db9 login` or `db9 register` to authenticate. If you were using an anonymous account, it may have expired.

### Database creation fails

Check `db9 db list` -- you may have hit account limits. Delete unused databases with `db9 db delete <ID>`.

### Migration out of order

Use `db9 migration status <ID>` to compare local files vs applied state. Migrations are applied in filename order (timestamp prefix).

### Connection timeout or network error

db9 requires network access to `db9.shared.aws.tidbcloud.com`. Check your internet connection and any proxy settings. If behind a corporate firewall, ensure HTTPS traffic to port 443 is allowed.

### SQL query returns unexpected error

Verify the database ID is correct with `db9 db status <ID>`. If using a branch, use the branch's own ID, not the parent database ID. Branches are independent databases.

### Branch has no data

Branches copy schema only, not data. After creating a branch, seed it with `db9 db seed <BRANCH_ID> <FILE>` before running queries that depend on existing rows.