PGCLI: Python package for a interactive Postgres CLI

PGCLI is a Python package that is used as an interactive command-line interface for the PostgreSQL database server. For following with the article, make sure you have PostgreSQL installed and set up in your local system. You can follow the guide for Windows, Linux and macOS from the guide.

Also for performing any operations or testing out the package, create a simple database of your choice, There should be enough data for you to work with as it is just a REPL for running queries in a Postgres database.

Installation

As pgcli is a python package, it can be installed with pip. 

pip install pgcli 

If you want to set up the package in an isolated environment, you can use the virtualenv. Following are the steps to  install and setup pgcli in a virtual environment in python

pip install virtualenv
virtualenv venv

Windows:
venv\Scripts\activate

Linux/macos:
source venv/bin/activate

pip install pgcli

To make sure, you have installed pgcli in your system, you can execute the following command:

pgcli --version

After executing the command, the output should give out a particular/latest version of the package. 

$ pgcli --version
Version: 3.4.1

Connecting to a database

Now, you can select a particular database that you want to work with. It should at least contain some tables(at least one) to work with. As we will perform reading records and other operations from pgcli.  

pgcli <local_database_name> -U postgres

Here Postgres can be your user name(-U) which by default is postgres and the password is the master password for your postgres server on your system. The local database needs to be there in the postgres databases. I have selected the EMNS database in my local postgres server. 

You also have multiple options to select and use in it like the 

Get a List of all the databases for a user

We can obtain the list of all the databases in the postgres for a specific user using the list option. 

pgcli --list -u postgres

As we can the list of all the databases on the postgres server with the Postgres user. 

Running Queries in the repl

We can perform any queries as you would normally perform on the query tool in the pgadmin panel. If you want a simple guide on getting started with Postgres commands and queries, you can check this article.

For example, let us run a simple SELECT query in a particular database.  

So, as we can we were able to execute SQL queries in the python package which is a CLI for interacting with the postgres server as a query tool. 

Remote Database

We can even connect to a remote postgres database using some parameters or the URL itself. 

pgcli 'postgresql://user:password@nlocalhost:port/db_name'

OR

pgcli -h localhost -U user -W '@postgres' -d db_name

We can use either of the commands to connect to a remote database in pgcli. The first command is using a single string that contains all the config data about the database and the host in a URL format. Whereas in the second command, we have to pass separate data in the form of parameters.

For further options and commands in pgcli, you can get help from the –help command:

pgcli --help 

This will result in a list of commands and options which are available in the pgcli package to be used from the command line.

Thus, we can get more options like no-password or row limit for displaying only a few records in a query and so on. 

So, pgcli is a command-line tool that can be used very similarly to the query tool in the pgAdmin provided by Postgres server.