pgcli

Connect to a PostgreSQL database using a connection string

$ pgcli postgresql://[user]@[host]/[database]

$ pgcli [[-h|--host]] [host] [[-U|--username]] [username] [[-d|--dbname]] [database]

$ pgcli --help

pgcli [OPTIONS] [DSN]
pgcli [OPTIONS] -h HOST -p PORT -U USER -d DBNAME

--host <hostname>, -h <hostname>
    Connect to the database server at the specified `hostname`.

--port <port>, -p <port>
    Port number to connect to on the database server.

--user <username>, -U <username>
    Connect as the specified `username`.

--dbname <dbname>, -d <dbname>
    Database name to connect to.

--password, -W
    Force pgcli to prompt for a password before connecting.

--dsn <dsn_string>
    Connect using a full DSN (Data Source Name) connection string.

--version, -v
    Show the pgcli version and exit.

--help
    Show the help message and exit.

--execute <command>, -e <command>
    Execute the specified command (SQL statement or internal pgcli command) and exit.

--query-file <file>, -f <file>
    Execute queries from the specified file and then exit.

--list-dsn
    List all saved DSNs from the configuration file.

--prompt-dsn
    Prompt for DSN selection from a list of saved DSNs.

--pgclirc <path>
    Specify a custom path to the pgcli configuration file.

--single-connection
    Use a single database connection for the entire session.

--local-file
    Enable reading and writing files on the local filesystem (disabled by default for security).

--csv
    Output query results in CSV (Comma Separated Values) format.

--tsv
    Output query results in TSV (Tab Separated Values) format.

--json
    Output query results in JSON format.

--table-format <format>
    Specify the output format for tables (e.g., `psql`, `plain`, `simple`).

--logfile <file>
    Log SQL statements and internal commands to the specified file.

--debug
    Enable debug mode for more verbose output and troubleshooting.

--auto-vertical-output
    Automatically switch to vertical output for wide tables that don't fit the screen.

--row-limit <num>
    Limit the number of rows displayed in query results.

--skip-casing
    Skip casing transformation during auto-completion for identifiers.

--expansion-mode <mode>
    Set the expansion mode for JSON/HSTORE columns (e.g., `no-json`, `json`).

pgcli is a powerful and user-friendly command-line interface for PostgreSQL databases, designed to significantly enhance developer productivity. Built on Pygments for syntax highlighting and prompt_toolkit for intelligent auto-completion, it offers a superior interactive experience compared to traditional psql. Key features include context-aware suggestions for tables, columns, SQL keywords, and functions, ensuring fewer typing errors and faster query construction. It also provides automatic formatting of query results into readable tables, complete with support for vertical output for wide datasets. pgcli supports connection via DSN (Data Source Name) or individual parameters, session logging, and execution of SQL scripts from files. Its intuitive design and rich feature set make it an invaluable tool for anyone working with PostgreSQL from the terminal.

While pgcli offers a rich interactive experience and improved usability over traditional clients, very large query results might still benefit from piping to external pagers for optimal performance. Additionally, some highly specific or advanced `psql` features, such as direct meta-command piping to shell commands, might require alternative approaches or are not directly supported.

pgcli supports many psql-like internal commands, often prefixed with a backslash (`\`). Examples include `\d` (describe table), `\dt` (list tables), `\l` (list databases), `\dn` (list schemas), and `\sf` (show function definition). These commands provide quick access to database metadata and common administrative tasks without writing full SQL queries.

pgcli can be extensively configured via a `~/.config/pgcli/config` file (or `~/.pgclirc` on older versions). This file allows customizing various aspects of the client, including key bindings, auto-completion behavior, session logging, default table formats, color schemes, and DSN entries, tailoring the experience to individual user preferences and workflows.

Developed by Amjith Ramanujam, pgcli was first released in 2015, quickly gaining popularity as an open-source project. Its primary goal was to provide a more intuitive and feature-rich command-line interface for PostgreSQL users, addressing common pain points found in traditional tools. The project has since seen continuous development, driven by community contributions, focusing on enhancing user experience through intelligent features like smart auto-completion and syntax highlighting, making it a staple for many PostgreSQL developers and DBAs.

psql(1), mysql(1), sqlite3(1)