pgcli

Postgres CLI with autocompletion and syntax highlighting

11,975

550

11,975

146

View on GitHub

Top Related Projects

dbeaver

39,201

Free universal database tool and SQL client

mycli

11,404

A Terminal Client for MySQL with AutoCompletion and Syntax Highlighting.

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.

cockroach

30,019

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.

pgadmin4

2,395

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

Quick Overview

pgcli is a command-line interface for PostgreSQL with auto-completion and syntax highlighting. It provides a more user-friendly and efficient alternative to the standard psql client, offering features like smart completion, multiline editing, and a customizable interface.

Pros

Advanced auto-completion for SQL keywords, table names, and column names
Syntax highlighting for easier query reading and writing
Multiline editing support with configurable keybindings
Configurable interface with support for custom color schemes and output formats

Cons

May have a steeper learning curve for users accustomed to traditional psql
Some advanced psql features might not be available or work differently
Potential performance overhead compared to psql for very large databases
Requires additional setup and dependencies compared to the built-in psql client

Getting Started

To install pgcli, you can use pip:

pip install pgcli

To connect to a PostgreSQL database:

pgcli [database_name]

Or with more connection details:

pgcli postgresql://[user[:password]@][host][:port][/dbname]

Once connected, you can start writing SQL queries with auto-completion and syntax highlighting. For example:

SELECT * FROM users WHERE age > 18;

Press Tab to activate auto-completion for table names, column names, and SQL keywords. Use Ctrl+R to search through command history.

For more information and advanced usage, refer to the official documentation at https://www.pgcli.com/docs.

Competitor Comparisons

dbeaver

39,201

Free universal database tool and SQL client

Pros of DBeaver

Supports multiple database systems, not limited to PostgreSQL
Offers a graphical user interface, making it more accessible for users who prefer visual interactions
Provides advanced features like data modeling, ERD visualization, and data export/import tools

Cons of DBeaver

Heavier resource consumption due to its Java-based nature and extensive feature set
Steeper learning curve for users who are comfortable with command-line interfaces
May be overkill for users who only need basic PostgreSQL interactions

Code Comparison

While a direct code comparison isn't particularly relevant for these tools, we can compare their usage:

pgcli:

$ pgcli mydatabase
mydatabase=> SELECT * FROM users;

DBeaver:

// Connect to database
Connection connection = DriverManager.getConnection(url, user, password);

// Execute query
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

DBeaver uses Java for its core functionality, while pgcli is primarily written in Python. The code examples show the difference between a command-line approach (pgcli) and a programmatic approach (DBeaver) for executing SQL queries.

mycli

11,404

A Terminal Client for MySQL with AutoCompletion and Syntax Highlighting.

Pros of mycli

Supports MySQL-specific features and syntax
Includes MySQL-specific auto-completion and syntax highlighting
Generally faster for MySQL operations due to specialized optimizations

Cons of mycli

Limited to MySQL databases only
May lack some advanced PostgreSQL-specific features
Smaller community and fewer contributors compared to pgcli

Code Comparison

mycli:

def mysql_special_command(self, sql):
    if sql.startswith('\\'):
        return self.handle_special_command(sql)
    return None

pgcli:

def postgres_special_command(self, sql):
    if sql.startswith('\\'):
        return self.handle_special_command(sql)
    return None

Both projects share similar codebases and structures, as they are part of the dbcli family. The main differences lie in database-specific implementations and optimizations. mycli focuses on MySQL-specific features and syntax, while pgcli is tailored for PostgreSQL databases. Both tools offer similar command-line interfaces and functionality, but are optimized for their respective database systems.

azuredatastudio

7,537

Pros of Azure Data Studio

Supports multiple database systems (SQL Server, PostgreSQL, MySQL)
Offers a graphical user interface with advanced features like query plan visualization
Provides extensibility through extensions and notebooks

Cons of Azure Data Studio

Heavier resource usage due to being based on Electron
Steeper learning curve for users familiar with command-line interfaces
Less focused on PostgreSQL-specific features compared to pgcli

Code Comparison

pgcli:

\d+ table_name
SELECT * FROM table_name LIMIT 10;
\timing

Azure Data Studio:

-- No direct equivalent for \d+
SELECT TOP 10 * FROM table_name;
SET STATISTICS TIME ON;

Summary

pgcli is a lightweight, command-line PostgreSQL client focused on productivity and ease of use for PostgreSQL databases. Azure Data Studio is a more comprehensive, cross-platform database tool with a graphical interface, supporting multiple database systems and offering advanced features like query plan visualization and notebook integration. While pgcli excels in PostgreSQL-specific operations and quick command-line interactions, Azure Data Studio provides a broader set of features and a more visual approach to database management, making it suitable for users who prefer GUI-based tools and work with multiple database systems.

cockroach

30,019

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.

Pros of Cockroach

Distributed SQL database with horizontal scalability and high availability
Built-in support for geo-partitioning and multi-region deployments
ACID-compliant transactions with strong consistency guarantees

Cons of Cockroach

Higher resource requirements and complexity compared to pgcli
Steeper learning curve for setup and management
May be overkill for simple, single-node database needs

Code Comparison

pgcli:

# Simple connection to PostgreSQL
pgcli -h localhost -U myuser -d mydatabase

Cockroach:

-- Start a multi-node cluster
cockroach start --insecure --store=node1 --listen-addr=localhost:26257 --http-addr=localhost:8080 --join=localhost:26257,localhost:26258,localhost:26259 --background
cockroach start --insecure --store=node2 --listen-addr=localhost:26258 --http-addr=localhost:8081 --join=localhost:26257,localhost:26258,localhost:26259 --background
cockroach start --insecure --store=node3 --listen-addr=localhost:26259 --http-addr=localhost:8082 --join=localhost:26257,localhost:26258,localhost:26259 --background

While pgcli is a command-line interface for PostgreSQL, Cockroach is a full-fledged distributed SQL database. pgcli offers a simpler, more lightweight solution for interacting with PostgreSQL databases, while Cockroach provides a robust, scalable database system with advanced features for distributed environments. The code comparison illustrates the difference in complexity, with pgcli requiring a simple connection command, while Cockroach involves setting up multiple nodes for a distributed cluster.

pgadmin4

2,395

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

Pros of pgAdmin 4

Graphical user interface for easier database management
Comprehensive features including query tool, schema browser, and backup/restore
Cross-platform support (web and desktop applications)

Cons of pgAdmin 4

Heavier resource usage due to its full-featured nature
Steeper learning curve for new users
Slower startup time compared to lightweight CLI tools

Code Comparison

pgAdmin 4 (Python):

@blueprint.route('/browser/')
@login_required
def browser():
    return render_template(MODULE_NAME + '/browser.html')

pgcli (Python):

@click.command()
@click.option('-h', '--host', default='', help='Host address of the postgres database.')
@click.option('-p', '--port', default=5432, help='Port number at which the '
        'postgres instance is listening.')
def cli(host, port):
    run_cli(host, port)

pgAdmin 4 focuses on web-based interfaces and routing, while pgcli emphasizes command-line interactions and options. pgAdmin 4's code reflects its more complex, feature-rich nature, whereas pgcli's code demonstrates its streamlined, CLI-oriented approach.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

We stand with Ukraine

Ukrainian people are fighting for their country. A lot of civilians, women and children, are suffering. Hundreds were killed and injured, and thousands were displaced.

This is an image from my home town, Kharkiv. This place is right in the old city center.

.. image:: screenshots/kharkiv-destroyed.jpg

Picture by @fomenko_ph (Telegram).

Please consider donating or volunteering.

A REPL for Postgres

This is a postgres client that does auto-completion and syntax highlighting.

Home Page: http://pgcli.com

MySQL Equivalent: http://mycli.net

.. image:: screenshots/pgcli.gif .. image:: screenshots/image01.png

Quick Start

If you already know how to install python packages, then you can simply do:

$ pip install -U pgcli

or

$ sudo apt-get install pgcli # Only on Debian based Linux (e.g. Ubuntu, Mint, etc)
$ brew install pgcli  # Only on macOS

If you don't know how to install python packages, please check the detailed instructions_.

.. _detailed instructions: https://github.com/dbcli/pgcli#detailed-installation-instructions

Usage

$ pgcli [database_name]

or

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

Examples:

$ pgcli local_database

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

For more details:

$ pgcli --help

Usage: pgcli [OPTIONS] [DBNAME] [USERNAME]

Options:
  -h, --host TEXT            Host address of the postgres database.
  -p, --port INTEGER         Port number at which the postgres instance is
                             listening.
  -U, --username TEXT        Username to connect to the postgres database.
  -u, --user TEXT            Username to connect to the postgres database.
  -W, --password             Force password prompt.
  -w, --no-password          Never prompt for password.
  --single-connection        Do not use a separate connection for completions.
  -v, --version              Version of pgcli.
  -d, --dbname TEXT          database name to connect to.
  --pgclirc FILE             Location of pgclirc file.
  -D, --dsn TEXT             Use DSN configured into the [alias_dsn] section
                             of pgclirc file.
  --list-dsn                 list of DSN configured into the [alias_dsn]
                             section of pgclirc file.
  --row-limit INTEGER        Set threshold for row limit prompt. Use 0 to
                             disable prompt.
  --less-chatty              Skip intro on startup and goodbye on exit.
  --prompt TEXT              Prompt format (Default: "\u@\h:\d> ").
  --prompt-dsn TEXT          Prompt format for connections using DSN aliases
                             (Default: "\u@\h:\d> ").
  -l, --list                 list available databases, then exit.
  --auto-vertical-output     Automatically switch to vertical output mode if
                             the result is wider than the terminal width.
  --warn [all|moderate|off]  Warn before running a destructive query.
  --help                     Show this message and exit.

pgcli also supports many of the same environment variables_ as psql for login options (e.g. PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE).

The SSL-related environment variables are also supported, so if you need to connect a postgres database via ssl connection, you can set set environment like this:

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

.. _environment variables: https://www.postgresql.org/docs/current/libpq-envars.html

Features

The pgcli is written using prompt_toolkit_.

Auto-completes as you type for SQL keywords as well as tables and columns in the database.
Syntax highlighting using Pygments.
Smart-completion (enabled by default) will suggest context-sensitive completion.
- SELECT * FROM <tab> will only show table names.
- SELECT * FROM users WHERE <tab> will only show column names.
Primitive support for psql back-slash commands.
Pretty prints tabular data.

.. _prompt_toolkit: https://github.com/jonathanslenders/python-prompt-toolkit .. _tabulate: https://pypi.python.org/pypi/tabulate

Config

A config file is automatically created at ~/.config/pgcli/config at first launch. See the file itself for a description of all available options.

Contributions:

If you're interested in contributing to this project, first of all I would like to extend my heartfelt gratitude. I've written a small doc to describe how to get this running in a development setup.

https://github.com/dbcli/pgcli/blob/master/DEVELOP.rst

Please feel free to reach out to us if you need help.

Amjith, pgcli author: amjith.r@gmail.com, Twitter: @amjithr <http://twitter.com/amjithr>_
Irina, pgcli maintainer: i.chernyavska@gmail.com, Twitter: @irinatruong <http://twitter.com/irinatruong>_

Detailed Installation Instructions:

macOS:

The easiest way to install pgcli is using Homebrew.

$ brew install pgcli

Done!

Alternatively, you can install pgcli as a python package using a package manager called called pip. You will need postgres installed on your system for this to work.

In depth getting started guide for pip - https://pip.pypa.io/en/latest/installation/

$ which pip

If it is installed then you can do:

$ pip install pgcli

If that fails due to permission issues, you might need to run the command with sudo permissions.

$ sudo pip install pgcli

If pip is not installed check if easy_install is available on the system.

$ which easy_install

$ sudo easy_install pgcli

Linux:

In depth getting started guide for pip - https://pip.pypa.io/en/latest/installation/

Check if pip is already available in your system.

$ which pip

If it doesn't exist, use your linux package manager to install pip. This might look something like:

$ sudo apt-get install python-pip   # Debian, Ubuntu, Mint etc

or

$ sudo yum install python-pip  # RHEL, Centos, Fedora etc

pgcli requires python-dev, libpq-dev and libevent-dev packages. You can install these via your operating system package manager.

$ sudo apt-get install python-dev libpq-dev libevent-dev

or

$ sudo yum install python-devel postgresql-devel

Then you can install pgcli:

$ sudo pip install pgcli

Docker

Pgcli can be run from within Docker. This can be useful to try pgcli without installing it, or any dependencies, system-wide.

To build the image:

$ docker build -t pgcli .

To create a container from the image:

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

To access postgresql databases listening on localhost, make sure to run the docker in "host net mode". E.g. to access a database called "foo" on the postgresql server running on localhost:5432 (the standard port):

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

To connect to a locally running instance over a unix socket, bind the socket to the docker container:

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

IPython

Pgcli can be run from within IPython <https://ipython.org>_ console. When working on a query, it may be useful to drop into a pgcli session without leaving the IPython console, iterate on a query, then quit pgcli to find the query results in your IPython workspace.

Assuming you have IPython installed:

$ pip install ipython-sql

After that, run ipython and load the pgcli.magic extension:

$ ipython

In [1]: %load_ext pgcli.magic

Connect to a database and construct a query:

In [2]: %pgcli postgres://someone@localhost:5432/world
Connected: someone@world
someone@localhost:world> select * from city c where countrycode = 'USA' and population > 1000000;
+------+--------------+---------------+--------------+--------------+
| id   | name         | countrycode   | district     | population   |
|------+--------------+---------------+--------------+--------------|
| 3793 | New York     | USA           | New York     | 8008278      |
| 3794 | Los Angeles  | USA           | California   | 3694820      |
| 3795 | Chicago      | USA           | Illinois     | 2896016      |
| 3796 | Houston      | USA           | Texas        | 1953631      |
| 3797 | Philadelphia | USA           | Pennsylvania | 1517550      |
| 3798 | Phoenix      | USA           | Arizona      | 1321045      |
| 3799 | San Diego    | USA           | California   | 1223400      |
| 3800 | Dallas       | USA           | Texas        | 1188580      |
| 3801 | San Antonio  | USA           | Texas        | 1144646      |
+------+--------------+---------------+--------------+--------------+
SELECT 9
Time: 0.003s

Exit out of pgcli session with Ctrl + D and find the query results:

someone@localhost:world>
Goodbye!
9 rows affected.
Out[2]:
[(3793, u'New York', u'USA', u'New York', 8008278),
 (3794, u'Los Angeles', u'USA', u'California', 3694820),
 (3795, u'Chicago', u'USA', u'Illinois', 2896016),
 (3796, u'Houston', u'USA', u'Texas', 1953631),
 (3797, u'Philadelphia', u'USA', u'Pennsylvania', 1517550),
 (3798, u'Phoenix', u'USA', u'Arizona', 1321045),
 (3799, u'San Diego', u'USA', u'California', 1223400),
 (3800, u'Dallas', u'USA', u'Texas', 1188580),
 (3801, u'San Antonio', u'USA', u'Texas', 1144646)]

The results are available in special local variable _, and can be assigned to a variable of your choice:

In [3]: my_result = _

Pgcli dropped support for Python<3.8 as of 4.0.0. If you need it, install pgcli <= 4.0.0.

Thanks:

A special thanks to Jonathan Slenders <https://twitter.com/jonathan_s>_ for creating Python Prompt Toolkit <http://github.com/jonathanslenders/python-prompt-toolkit>_, which is quite literally the backbone library, that made this app possible. Jonathan has also provided valuable feedback and support during the development of this app.

Click <http://click.pocoo.org/>_ is used for command line option parsing and printing error messages.

Thanks to psycopg <https://www.psycopg.org/>_ for providing a rock solid interface to Postgres database.

Thanks to all the beta testers and contributors for your time and patience. :)

.. |Build Status| image:: https://github.com/dbcli/pgcli/actions/workflows/ci.yml/badge.svg?branch=main :target: https://github.com/dbcli/pgcli/actions/workflows/ci.yml

.. |CodeCov| image:: https://codecov.io/gh/dbcli/pgcli/branch/master/graph/badge.svg :target: https://codecov.io/gh/dbcli/pgcli :alt: Code coverage report

.. |Landscape| image:: https://landscape.io/github/dbcli/pgcli/master/landscape.svg?style=flat :target: https://landscape.io/github/dbcli/pgcli/master :alt: Code Health

.. |PyPI| image:: https://img.shields.io/pypi/v/pgcli.svg :target: https://pypi.python.org/pypi/pgcli/ :alt: Latest Version

.. |netlify| image:: https://api.netlify.com/api/v1/badges/3a0a14dd-776d-445d-804c-3dd74fe31c4e/deploy-status :target: https://app.netlify.com/sites/pgcli/deploys :alt: Netlify

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot