psql
20.4k stars
official open-source Has MCP macOS Linux Actively maintained
The official PostgreSQL CLI. Run queries, inspect schemas, export data, and manage databases with JSON, CSV, and HTML output formats.
psql has both a CLI and an MCP server. See when to use each
Part of the PostgreSQL CLI tools for AI agents
What your agent can do
Your database has a problem. A query is slow, a migration didn't apply cleanly, or a table is missing a column. You open pgAdmin, wait for it to connect, navigate the object tree, find the table, open the query tool, type the query, run it. Your agent runs `psql -c "EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42"` and has the execution plan in two seconds.psql outputs structured data in any format your agent needs. `\pset format json` returns query results as a JSON array of objects. `\pset format csv` for spreadsheet-ready output. `COPY ... TO STDOUT WITH CSV HEADER` for bulk exports. Pipe the output to `jq`, `csvkit`, or directly into another command. The format flexibility means your agent always gets clean, parseable data.Scripting is where psql separates from GUI tools. Your agent runs `psql -f migrations/003_add_indexes.sql -v ON_ERROR_STOP=1 --single-transaction` to apply a migration file inside a single transaction that rolls back on any error. The `-v ON_ERROR_STOP=1` flag is critical. Without it, psql continues executing after errors, leaving your database in a half-migrated state. Most tutorials skip this flag. Your agent includes it automatically.The `-X` flag skips the user's `.psqlrc` configuration file. This matters for agent-driven workflows because `.psqlrc` can set custom prompts, pagers, or output formats that break machine-readable output. `-t` strips headers and footers. `-A` removes alignment padding. Together, `psql -XtAc "SELECT email FROM users"` gives your agent one clean value per line, no decoration.psql connects directly to Supabase, Neon, AWS RDS, Railway, and any PostgreSQL-compatible service. The same commands work everywhere. Your agent runs `psql $DATABASE_URL -c "\\dt"` against any database the connection string points to. Local dev, staging, production — one tool, one interface.
Limitations
PostgreSQL-specific with no support for MySQL, MongoDB, or other databases. The `\` meta-commands (like `\dt` and `\d`) only work in psql, not in standard SQL clients. Requires the PostgreSQL client library (`libpq`). On macOS, `brew install libpq` installs only the client tools, but on some Linux distributions the package includes the full server.
Key Commands
psql -c Execute a single SQL command and return results
psql \dt List all tables in the current database
psql \d Describe a table's columns, types, indexes, and constraints
psql -f Execute a SQL file against the database
COPY ... TO STDOUT Export query results as CSV, JSON, or custom format
psql --pset format=json Output query results as a JSON array of objects
EXPLAIN ANALYZE Show the query execution plan with actual timing and row counts
GitHub Stats
repo postgres/postgres
stars 20.4k
language C
license PostgreSQL
last commit Mar 24, 2026
FAQ
- Is psql free?
- Yes. psql is part of PostgreSQL, which is open-source under the PostgreSQL license (similar to MIT/BSD). The client tools can be installed standalone without the server. On macOS, `brew install libpq` installs just psql, pg_dump, and pg_restore. On Ubuntu, `sudo apt install postgresql-client` does the same.
- Can AI agents use psql?
- Yes. psql runs fully non-interactive with `-c` for single commands and `-f` for SQL files. Output comes in JSON (`\pset format=json`), CSV, or HTML. The `-X` flag skips user config for deterministic output, `-t` strips headers, and `-A` removes padding. PostgreSQL also has official and community MCP servers that give agents structured database access.
- How do you connect psql to Supabase?
- Supabase is PostgreSQL. Copy your connection string from the Supabase dashboard (Settings → Database) and run `psql 'postgresql://postgres:[password]@[host]:5432/postgres'`. Every psql command works: `\dt` lists tables, `\d tablename` shows schema, SQL queries return data. Your agent uses the same connection string stored in `DATABASE_URL`.
- What is the difference between psql and pgAdmin?
- psql is the command-line interface. pgAdmin is a GUI. The key difference for agents: psql is scriptable, composable, and automatable. Your agent chains psql with shell pipes, runs bulk operations through SQL files, and outputs structured JSON for downstream processing. pgAdmin requires clicking through menus. For production debugging and automated workflows, psql wins. For visual schema exploration by humans, pgAdmin is friendlier.
- How do you output JSON from psql?
- Two ways. First, `\pset format json` switches psql output to JSON arrays of objects — all subsequent queries output JSON. Second, use PostgreSQL's built-in JSON functions: `SELECT json_agg(t) FROM (SELECT * FROM users LIMIT 5) t` returns a JSON array. Your agent typically uses `\pset format json` for ad-hoc queries and `row_to_json()` or `json_agg()` for precise JSON shaping.
Related tools in Databases
Redis CLI official
brew install redis
category Databases
The official CLI for Redis. Query keys, manage data structures, monitor server health, and execute Lua scripts across any Redis instance.
73.5k
Supabase CLI official
brew install supabase/tap/supabase
category Databases
The official CLI for Supabase. Run Supabase locally, manage database migrations, deploy edge functions, and generate TypeScript types from your schema.
1.6k
mongosh official
brew install mongosh
category Databases
The official MongoDB Shell. Query documents, run aggregation pipelines, manage collections, and script database operations with native JSON output.
383
Last verified: Mar 25, 2026