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.
On this page 8 sections
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, orSHOW. - Blocked mutations: Any statement that attempts to write, alter schemas, insert, update, or drop tables fails immediately with an HTTP
400 invalid_requesterror. - 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 and read the full 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:
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:
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:
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:
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:
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:
{
"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:
vibiz devThis 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:
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.
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.
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:
# 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 --limitedHere 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:
{
"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:
vibiz keys access key_123 allow database.production:readAlternatively, 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 asUPDATEorDROP), exceeded 20,000 characters, or failed SQL syntax parsing.401 unauthorized: TheVIBIZ_API_KEYheader is missing, malformed, or revoked.403 forbidden(owner_only): A Limited key tried to read fromenv=production. Useenv=developmentor grantdatabase.production:read.409 dependency_not_ready: The database or its development branch is still provisioning. Runvibiz devto trigger provisioning, pollGET /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:
# Export the database snapshot as SQL and all tables as individual CSV files
vibiz export --db --csvThis 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 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 and read Use one service.
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`.