pgcli

You can install pgcli using various package managers depending on your operating system.

Using pip (Python Package Manager)

pip install -U pgcli

macOS

Using Homebrew:

brew install pgcli

Linux (Debian-based, e.g., Ubuntu, Mint)

sudo apt-get install pgcli

Using pipx or uvx (Recommended for isolated environments)

pipx install pgcli
# or
uvx pgcli

You can start a pgcli session by providing a database name or a connection URI.

Basic Usage

Connect to a local database by name:

pgcli local_database

Connection URI

Connect using a full PostgreSQL URI, which allows specifying credentials, host, port, and SSL parameters:

pgcli postgresql://[user[:password@][netloc][:port][/dbname][?extra=value[&other=other-value]]]

Example with SSL:

pgcli postgres://amjith:pa$$w0rd@example.com:5432/app_db?sslmode=verify-ca&sslrootcert=/myrootcert

You can run pgcli inside a Docker container to avoid local installation.

Build the image

docker build -t pgcli .

Run a container

docker run --rm -ti pgcli pgcli <ARGS>

Access localhost databases

Use --net host to access a PostgreSQL server running on your host machine:

docker run --rm -ti --net host pgcli pgcli -h localhost foo

Access via Unix Socket

Bind the local socket to the container:

docker run --rm -ti -v /var/run/postgres:/var/run/postgres pgcli pgcli foo

To develop pgcli and see changes immediately without re-installing, use uv to create a virtual environment and install the package in editable mode.

1. Set up uv and create a virtual environment:

   cd pgcli
   uv venv
   source ./pgcli-dev/bin/activate

2. Install pgcli in editable mode:

   uv pip install -e .

By using the -e or --editable flag, any changes made to the source code are immediately reflected in the installed version of pgcli.

Unit tests can be executed using pytest from the project root.

cd pgcli
pytest

Integration tests use behave and pytest. They are located in the tests directory and are configured via a behave.ini file.

Prerequisites

1. Install development dependencies:

   uv pip install ".[dev]"

2. Database Permissions: Ensure your PostgreSQL user has permissions to create and drop databases. If using a local postgres user, ensure the authentication method in pg_hba.conf is set to trust. If you modify pg_hba.conf, restart the service:

   sudo service postgresql restart

Execution

Navigate to the tests directory and run behave:

cd pgcli/tests
behave

To see stdout/stderr output during tests, use:

behave --no-capture

Note: behave tests are currently incompatible with Windows due to pexpect limitations.

$ uv pip install ".[dev]"
$ cd pgcli/tests
$ behave

To debug pgcli in Visual Studio Code, create a .vscode/launch.json file in the project root. This configuration launches the pgcli.main module and allows you to set environment variables for database connection testing.

{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "Python: Module",
            "type": "python",
            "request": "launch",
            "module": "pgcli.main",
            "justMyCode": false,
            "console": "externalTerminal",
            "env": {
                "PGUSER": "postgres",
                "PGPASS": "password",
                "PGHOST": "localhost",
                "PGPORT": "5432"
            }
        }
    ]
}

The sqlcompletion module uses several namedtuple types to categorize what kind of completion should be suggested to the user based on the current SQL cursor position. Common suggestion types include:

• Table, View, Schema, Database: For structural database objects.
• Column: For column names, often scoped to specific tables.
• Function: For database functions.
• Keyword: For SQL keywords (e.g., SELECT, FROM).
• Datatype: For PostgreSQL data types.
• Join, JoinCondition: For JOIN clauses and ON conditions.
• Alias: For table aliases.
• Special: For psql-style special commands (e.g., \d).
• Path: For file paths (e.g., after \i ).
• NamedQuery: For saved named queries.

These types are returned by the suggest_type function to inform the UI which metadata to fetch and display.

pgcli can use PostgreSQL service files to manage connection parameters. The parse_service_info logic determines which service configuration to use based on the following priority and environment variables:

1. Service Name: Provided via the service argument or the PGSERVICE environment variable.
2. Service File Location: Determined by the PGSERVICEFILE environment variable. If not set, pgcli looks for .pg_service.conf in these locations:
   • Windows: The path constructed from PGSYSCONFDIR + \pg_service.conf.
   • Linux/macOS: The path constructed from PGSYSCONFDIR + /.pg_service.conf (if PGSYSCONFDIR is set), otherwise defaults to ~/.pg_service.conf.

If a valid service and file are found, the function returns the configuration object and the file path used.

Since pgcli uses libpq, it supports standard PostgreSQL environment variables for connection and SSL configuration.

Connection Variables

• PGHOST
• PGPORT
• PGUSER
• PGPASSWORD
• PGDATABASE

SSL Variables

To connect via SSL, export the following variables:

export PGSSLMODE="verify-full"
export PGSSLCERT="/your-path-to-certs/client.crt"
export PGSSLKEY="/your-path-to-keys/client.key"
export PGSSLROOTCERT="/your-path-to-ca/ca.crt"
pgcli -h localhost -p 5432 -U username postgres

When connecting to virtual databases like PgBouncer, standard psycopg cursors may raise ProtocolViolation errors. PGExecute uses ProtocolSafeCursor to wrap and suppress these errors.

Instead of raising an exception that crashes the session, ProtocolSafeCursor:

1. Sets a protocol_error flag.
2. Captures the error message.
3. Returns the error message as a single-element tuple in fetchone() or fetchall() calls.
4. Causes __iter__ to stop immediately.