pgcli: a Postgres REPL with autocompletion and syntax highlighting

The problem pgcli solves for interactive Postgres work

psql is the reference client, and it is deliberately plain: you type SQL, it prints rows. It has no completion of table or column names and no colour. For a schema with a few dozen tables that is fine. For a schema with hundreds, remembering whether the column is called created_at or creation_date means running a \d and scrolling.

pgcli targets exactly that friction. The README describes it as "a postgres client that does auto-completion and syntax highlighting", and the audience is the person sitting at a terminal writing ad hoc queries. It is not a migration runner, not an ORM, and not a GUI. The homepage is pgcli.com and the README points to mycli.net as the MySQL equivalent, which tells you the project is one of a family of REPLs sharing the same design ideas.

If your Postgres usage is a cron job that pipes a .sql file into psql, pgcli adds nothing. The value is concentrated in the interactive loop: fewer typos, faster exploration, readable output.

How completion actually works: prompt_toolkit, Pygments and a schema cache

The README states pgcli is written using prompt_toolkit, with syntax highlighting from Pygments. Those two libraries explain most of the behaviour you see at the prompt. prompt_toolkit supplies the line editor and the completion menu; Pygments tokenises the buffer so keywords, strings and identifiers are coloured differently.

The interesting part is where the completions come from. pgcli queries the database for its schema, which is why it opens a second connection by default. The --single-connection flag exists precisely to turn that off: "Do not use a separate connection for completions." That flag is the honest admission that completion costs an extra connection, which matters when you are near max_connections or connecting through a pooler that caps sessions.

Smart-completion, on by default, makes the suggestions context-sensitive. The README gives two examples: SELECT * FROM <tab> lists table names only, and SELECT * FROM users WHERE <tab> lists column names only. That is a parser-level distinction, not a string prefix match, and it is the feature that separates pgcli from shell history tricks.

Output rendering is separate again. The README credits tabulate for pretty-printing tabular data, and --auto-vertical-output switches to vertical layout when a row is wider than the terminal, which is the same instinct as psql's expanded display.

Installing pgcli and running your first query

The README's quick start is one line if you already manage Python packages. On Debian-based Linux there is also an apt package, and Homebrew covers macOS.

pip install -U pgcli

Alternatively, for an isolated install that does not touch your system Python, the README points at pipx or uvx on Linux.

pipx install pgcli

The Python requirement is worth checking before you start: pyproject.toml sets requires-python to ">=3.10" and classifies the package against Python 3.10 through 3.14, with no Windows-specific classifier. On Windows the dependency list swaps psycopg for psycopg-binary, and setproctitle is only installed on non-Windows platforms, so the install path differs by operating system.

Connecting follows psql conventions. You can pass a database name, a full URL, or a DSN alias.

pgcli local_database
pgcli postgres://amjith:[email protected]:5432/app_db?sslmode=verify-ca&sslrootcert=/myrootcert

The README also documents that pgcli honours the same libpq environment variables as psql, including PGHOST, PGPORT, PGUSER, PGPASSWORD and PGDATABASE, plus the SSL ones such as PGSSLMODE, PGSSLCERT, PGSSLKEY and PGSSLROOTCERT. So an existing psql setup usually needs no new configuration.

On first launch a config file is created at ~/.config/pgcli/config, and the README says to read the file itself for the available options. Useful flags from --help include --list to list databases and exit, --row-limit to set the threshold for the row limit prompt (0 disables it), --less-chatty to skip the intro and goodbye, --warn to warn before a destructive query, and --list-dsn to show aliases configured under [alias_dsn].

Where pgcli is the wrong tool

The README is candid that psql compatibility is incomplete: it says pgcli has "primitive support for psql back-slash commands". If your muscle memory is built on \copy, \gexec, \set with variable interpolation, or any of the more elaborate meta-commands, expect gaps. A script that leans on those will not port cleanly.

The second limitation is structural. Completion needs schema metadata, and by default that comes from a second connection. Against a connection pooler that limits sessions, or a database where you are close to the connection ceiling, you will want --single-connection, which trades completion freshness for one fewer session. The README does not describe how stale the cached schema gets after a DDL change, so do not assume a newly added column appears instantly.

Third, there is no documented non-interactive mode. The README describes a REPL, not a batch executor, and the related searches around running a SQL file have no matching section in the documentation. For CI, migrations or anything you want to pipe, psql remains the correct choice.

Finally, the dependency pins are tight in places. pyproject.toml excludes click 8.1.8, 8.2.* and 8.3.0 because of a pager regression, and constrains sqlparse to >=0.3.0,<0.7. In a shared environment where another tool pins an excluded click version, resolution can fail. That is a packaging reality, not a defect, but it is the kind of thing that surfaces at install time rather than at query time.

pgcli versus psql, and versus a GUI like pgAdmin

The honest comparison is pgcli against psql, because they occupy the same seat. psql ships with Postgres, has complete backslash command coverage, and is scriptable. pgcli adds completion and highlighting and loses some meta-command depth. The trade is editor experience for compatibility. If you have never felt the absence of completion, psql is not missing anything you need.

Against a GUI such as pgAdmin, the difference is not features but posture. A GUI gives you a schema tree, query history panels and result grids you can click through. pgcli gives you a prompt, a keystroke away from the shell, and it composes with the rest of your terminal tooling. Neither replaces the other: browsing an unfamiliar schema is faster in a tree, and iterating on a query is faster at a prompt you never leave.

The README also notes the MySQL equivalent, mycli, which matters if your team works across both engines. The two share the prompt_toolkit and Pygments foundation, so the interaction model transfers even though the SQL dialects do not.

Maintenance, licence and upgrade cost

The repository is not archived, and the last push was on 2026-09-20, the same day as the v4.7.1 release. v4.7.0 landed on 2026-09-19. That cadence, with releases a day apart, suggests a small follow-up fix rather than a long-running branch, and it is the only maintenance signal available here.

The licence is BSD-3-Clause, declared both in pyproject.toml and in LICENSE.txt at the repository root. That is a permissive licence, which in practice means you can bundle pgcli with internal tooling without the copyleft obligations of a GPL client. This is a description of the licence text, not legal advice; if you are redistributing it inside a product, read LICENSE.txt yourself.

Upgrade cost is dominated by the Python dependency graph rather than by pgcli's own code. Because it depends on prompt_toolkit (>=2.0.6,<4.0.0), psycopg (>=3.0.14) and a version-constrained click, a system-wide pip install can collide with other tools. The README's own suggestion of pipx or uvx for Linux is the cleaner route: it puts pgcli in its own virtual environment and upgrades become a single command. The repository also ships a Dockerfile that installs the package in editable mode and sets CMD pgcli, which is the option to reach for when you want the client without touching the host Python at all.

Editorial conclusion

Adopt pgcli if you type SQL interactively and want completion driven by your live schema; skip it if your workflow is non-interactive scripts, since the README documents no scripting mode and the backslash command coverage is described as primitive. Before rolling it out, check the two things that vary most between machines: whether your Python is 3.10 or newer, and whether your distribution already packages pgcli so you avoid a pip install entirely.

Frequently asked questions

Official sources

1. dbcli/pgcli on GitHub
2. License: BSD-3-Clause
3. Project website
4. README
5. Releases