# Inspect tables and run read-only SQL from the CLI

> Run read-only SQL queries and inspect Postgres tables directly from the command line using the read view API or a local database branch.

**You can inspect tables and execute read-only SQL queries from the terminal by sending HTTP requests to `/v1/projects/:id/database` and `/v1/projects/:id/database/query`, or by using `DATABASE_URL` with your favorite SQL client.** thelastapi provides a built-in read-only query endpoint that accepts a single read statement, evaluates it safely, and returns JSON rows in seconds. For direct interactive terminal sessions, running `vibiz dev` provisions a development Postgres branch with your exact production schema and zero customer rows.

Whether you want to inspect schema health during a script, let an AI agent safely check row counts, or run ad-hoc analytics without write risk, this guide covers how the read view works, how to query it from the CLI, and how environments isolate your live data.

## How does the read-only query view work?

The read view (`/database`, `vibiz_database`) acts as a safeguarded window into your Postgres database. Instead of opening raw port connections or exposing credentials with write permissions, you submit SQL queries over HTTP. 

To prevent accidental data loss or heavy locks, the read view enforces strict guardrails:

- **Permitted statements only:** It accepts exactly one statement starting with `SELECT`, `WITH`, `TABLE`, `VALUES`, `EXPLAIN`, or `SHOW`.
- **Blocked mutations:** Any statement that attempts to write, alter schemas, insert, update, or drop tables fails immediately with an HTTP `400 invalid_request` error.
- **Query size limit:** Statements can be up to 20,000 characters long.
- **Execution timeout:** Every statement runs for at most 5 seconds before canceling.
- **Row caps:** Queries return up to the first 500 rows. If more data matches, the response includes `truncated: true`. Table listing pages show up to 200 rows.

This design makes the read view safe for automation, terminal scripts, and AI coding agents. If you manage a database alongside other tools, see how it fits into [serverless Postgres for side projects: one database per project](https://thelastapi.dev/blog/postgres-database-per-project) and read the full [Database](https://thelastapi.dev/docs/database) documentation.

## How to list tables and schemas from the CLI

Before running queries, you often need to check which tables exist and how many rows they hold. You can inspect the schema of your project's database with a single `curl` call:

```bash
curl "https://thelastapi.dev/v1/projects/prj_123/database?env=development" \
  -H "Authorization: Bearer $VIBIZ_API_KEY"
```

The response returns the schemas, tables, and row estimates. Setting `env=development` targets the development branch, while omitting it or setting `env=production` targets your live production database (provided your key has sufficient permissions).

If you are working with an AI agent or an MCP client, the equivalent tool call is:

```json
vibiz_database { "project_id": "prj_123" }
```

This lists all tables in the development environment without requiring any SQL knowledge or database client configuration.

## How to run a read-only SQL query with curl

To execute a query from the command line, make a POST request to `/v1/projects/:id/database/query` with your SQL string and target environment in the JSON payload:

```bash
curl https://thelastapi.dev/v1/projects/prj_123/database/query \
  -H "Authorization: Bearer $VIBIZ_API_KEY" \
  -H 'content-type: application/json' \
  -d '{
    "sql": "select table_name from information_schema.tables where table_schema = '\''public'\''",
    "env": "development"
  }'
```

You can run analytical queries, joins, and aggregations using standard Postgres syntax:

```bash
curl https://thelastapi.dev/v1/projects/prj_123/database/query \
  -H "Authorization: Bearer $VIBIZ_API_KEY" \
  -H 'content-type: application/json' \
  -d '{
    "sql": "select id, email, created_at from users order by created_at desc limit 10",
    "env": "development"
  }'
```

Agents using the MCP tool can submit statements directly:

```json
vibiz_database {
  "project_id": "prj_123",
  "sql": "SELECT count(*) AS total_signups FROM users"
}
```

If your query attempts a mutation such as `DELETE FROM users WHERE id = 'usr_1'`, the API rejects the request:

```json
{
  "error": {
    "code": "invalid_request",
    "message": "Write statements are not allowed in the read view."
  }
}
```

When you need to modify data on your development branch, connect your own migration tool or Postgres client directly using `DATABASE_URL` instead of the read view.

## Read view vs. interactive database connection

The HTTP read view is optimized for quick checks and safe inspection. When you need interactive exploration, transactions, or schema migrations, use a native Postgres connection.

| Feature | Read view API (`/database/query`) | Direct Postgres client (`DATABASE_URL`) |
| --- | --- | --- |
| Transport | HTTPS (`curl`, JSON) | Plain Postgres protocol over TCP |
| Allowed operations | Read-only (`SELECT`, `WITH`, `EXPLAIN`, etc.) | Full read and write access |
| Output format | JSON objects | Tabular terminal text or ORM objects |
| Result limit | 500 rows (`truncated: true`) | Unlimited (controlled by client) |
| Execution timeout | 5 seconds | Configured by server/client |
| Statement length | Up to 20,000 characters | Standard Postgres limit |
| Best used for | CLI scripts, sanity checks, AI agents | Migrations, seeding, ORMs, heavy analytics |

To run native tools like `psql`, migration scripts, or an ORM from the terminal, generate your local environment:

```bash
vibiz dev
```

This commands branches your schema, empties all application rows, and writes `DATABASE_URL` into `.env.development.local`. You can then pass the environment directly into any command without writing secrets to disk:

```bash
vibiz dev -- psql "$DATABASE_URL"
```

For connecting backend frameworks and query builders to this database, read our guide on how to [use Postgres from Next.js with Drizzle or Prisma](https://thelastapi.dev/blog/postgres-nextjs-drizzle-prisma).

## Access control: Full-access vs. Limited keys

thelastapi projects maintain two distinct environments: `production` and `development`. Who can query what depends on the access level of the API key holding the session. Read more in the documentation on [Keys and environments](https://thelastapi.dev/docs/keys).

### How environments protect user data

The production database holds live user rows. The development copy is an automated branch that duplicates production's tables and migration ledgers (such as Drizzle's and Prisma's) but contains **none of its rows**. Every table—including application users and auth sessions—is emptied and verified before the development branch is handed out.

### Key capabilities and permissions

When creating keys with `vibiz keys create`, you choose the access tier:

```bash
# Full access (default: can view production rows and run migrations)
vibiz keys create ci

# Limited access (defaults to development, cannot read live data)
vibiz keys create growth-agent --limited
```

Here is how access levels handle database queries:

| Capability | Development copy | Production database |
| --- | --- | --- |
| List schemas and tables | Allowed for all keys | Full-access keys only (or `database.production:read`) |
| Read-only SQL via read view | Allowed for all keys | Full-access keys only (or `database.production:read`) |
| Direct connection (`DATABASE_URL`) | Allowed via `vibiz dev` | Full-access keys only (owners) |
| Write or mutate data | Allowed with client + dev URL | Owners with direct connection |

If a Limited key attempts to run a query against `env=production`, the API halts with `403 forbidden`:

```json
{
  "error": {
    "code": "forbidden",
    "details": {
      "reason": "owner_only",
      "permission": "database.production:read",
      "grant_url": "https://thelastapi.dev/keys/key_123/allow?permission=database.production:read",
      "console_url": "https://thelastapi.dev/keys"
    }
  }
}
```

The owner can grant that single key permission to read live data without giving it full administrative rights:

```bash
vibiz keys access key_123 allow database.production:read
```

Alternatively, an owner can grant read-only visibility across all production settings and databases with the read-all preset (`vibiz keys access key_123 read`).

## Handling read view error codes

When querying the read view from shell scripts or automation tools, handle these standard HTTP status codes:

- **`400 invalid_request`**: The statement attempted a write operation (such as `UPDATE` or `DROP`), exceeded 20,000 characters, or failed SQL syntax parsing.
- **`401 unauthorized`**: The `VIBIZ_API_KEY` header is missing, malformed, or revoked.
- **`403 forbidden` (`owner_only`)**: A Limited key tried to read from `env=production`. Use `env=development` or grant `database.production:read`.
- **`409 dependency_not_ready`**: The database or its development branch is still provisioning. Run `vibiz dev` to trigger provisioning, poll `GET /v1/projects/:id/dev`, and retry.

## Backing up and exporting query data

The read view is meant for inspection, capped at 500 rows. If you need a complete dump of table contents for offline analysis, use the project export features instead of scripting repeated HTTP queries:

```bash
# Export the database snapshot as SQL and all tables as individual CSV files
vibiz export --db --csv
```

This creates an archive containing `<slug>-<env>-database.sql.gz` and `<slug>-<env>-database-csv.zip`. Full-access keys can export both production and development environments, while Limited keys default to development. Review the [Export](https://thelastapi.dev/docs/export) guide for instructions on restoring exported dumps into any standard Postgres instance using `psql`.

If your application server is running outside thelastapi hosting infrastructure, you can still access your managed Postgres database seamlessly. See our guide on how to [use a managed Postgres database from an app hosted anywhere](https://thelastapi.dev/blog/postgres-from-any-host) and read [Use one service](https://thelastapi.dev/docs/use-one).

Run `vibiz dev` in your project folder to generate your development branch, or call the `/database/query` endpoint with `curl` to run your first read-only SQL statement.

## Frequently asked questions

### What SQL statements are allowed in the database read view?

The read view accepts a single statement starting with `SELECT`, `WITH`, `TABLE`, `VALUES`, `EXPLAIN`, or `SHOW`. Any statement that attempts to write or mutate rows returns an HTTP `400 invalid_request` error.

### How many rows does a read-only CLI query return?

Queries run against `/v1/projects/:id/database/query` return up to 500 rows. If the result set exceeds 500 rows, the API returns the first 500 rows and sets `truncated: true` in the response payload.

### Can an agent or Limited API key read production database rows?

No, Limited keys receive a `403 forbidden` error with `owner_only` when attempting to read production rows. To read live production data, a key must either have full owner access or be explicitly granted the `database.production:read` permission.

### How can I run queries that exceed the 5-second read view timeout?

For long-running statements or analytical workloads that exceed the 5-second HTTP timeout, connect directly to the database using `DATABASE_URL` and a standard client like `psql`, or export table dumps using `vibiz export --db --csv`.

---

Published 2026-11-15 · https://thelastapi.dev/blog/read-only-sql-queries-cli
