6 Popular PostgreSQL CLI Libraries

Paolo Redaelli

6 Popular PostgreSQL CLI Libraries

  • pgcli – Postgres CLI with autocompletion and syntax highlighting
  • pgsh – Branch your PostgreSQL Database like Git
  • psql – The built-in PostgreSQL CLI client
  • psql2csv – Run a query in psql and output the result as CSV
  • nancy – The Nancy CLI is a unified way to manage automated database experiments either in clouds or on-premise
  • schemaspy – SchemaSpy is a JAVA JDBC-compliant tool for generating your database to HTML documentation, including Entity Relationship diagrams

What is PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organisations.

10 Popular PostgreSQL CLI Libraries

  1. pgcli

Postgres CLI with autocompletion and syntax highlighting

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

Quick Start

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

$ pip <span class="token function">install</span> -U pgcli

or

$ <span class="token function">sudo</span> <span class="token function">apt-get</span> <span class="token function">install</span> pgcli <span class="token comment"># Only on Debian based Linux (e.g. Ubuntu, Mint, etc)</span>
$ brew <span class="token function">install</span> pgcli  <span class="token comment"># Only on macOS</span>

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

Usage

$ pgcli <span class="token punctuation">[</span>database_name<span class="token punctuation">]</span>

or

$ pgcli postgresql://<span class="token punctuation">[</span>user<span class="token punctuation">[</span>:password<span class="token punctuation">]</span>@<span class="token punctuation">]</span><span class="token punctuation">[</span>netloc<span class="token punctuation">]</span><span class="token punctuation">[</span>:port<span class="token punctuation">]</span><span class="token punctuation">[</span>/dbname<span class="token punctuation">]</span><span class="token punctuation">[</span>?extra<span class="token operator">=</span>value<span class="token punctuation">[</span><span class="token operator">&</span><span class="token assign-left variable">other</span><span class="token operator">=</span>other-value<span class="token punctuation">]</span><span class="token punctuation">]</span>

Examples:

$ pgcli local_database

$ pgcli postgres://amjith:pa<span class="token variable">$</span>w0rd@example.com:5432/app_db?sslmode<span class="token operator">=</span>verify-ca<span class="token operator">&</span><span class="token assign-left variable">sslrootcert</span><span class="token operator">=</span>/myrootcert

View on GitHub

2.  pgsh

Branch your PostgreSQL Database like Git

Finding database migrations painful to work with? Switching contexts a chore? Pull requests piling up? pgsh helps by managing a connection string in your .env file and allows you to branch your database just like you branch with git.

Prerequisites

There are only a couple requirements:

  • your project reads its database configuration from the environment
  • it uses a .env file to do so in development.

See dotenv for more details, and The Twelve-Factor App for why this is a best practice.

Language / Framework<strong>.env</strong> solutionMaturity
javascriptdotenvhigh

pgsh can help even more if you use knex for migrations.

Installation

  1. yarn global add pgsh to make the pgsh command available everywhere
  2. pgsh init to create a .pgshrc config file in your project folder, beside your .env file (see src/pgshrc/default.js for futher configuration)
  3. You can now run pgsh anywhere in your project directory (try pgsh -a!)
  4. It is recommended to check your .pgshrc into version control. Why?

View on GitHub

3. psql

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

View on source

4.  psql2csv

Run a query in psql and output the result as CSV.

Installation

Mac OS X

psql2csv is available on Homebrew.

$ brew install psql2csv

Manual

Grab the file psql2csv, put in somewhere in your $PATH, and make it executable:

$ curl https://raw.githubusercontent.com/fphilipe/psql2csv/master/psql2csv > /usr/local/bin/psql2csv && chmod +x /usr/local/bin/psql2csv

Usage

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

Options

The query is assumed to be the contents of STDIN, if present, or the last argument. All other arguments are forwarded to psql except for these:

-?, --help                 show this help, then exit
--delimiter=DELIMITER      set the field delimiter (default: ,)
--quote=QUOTE              set the quote delimiter (default: ")
--escape=ESCAPE            set the escape character (default: QUOTE)
--null=NULL                set the string representing NULL; printed without quotes (default: empty string)
--force-quote=FORCE_QUOTE  set the columns to be force quoted; comma separated list of columns or * for all (default: none)
--encoding=ENCODING        set the output encoding; Excel likes latin1 (default: UTF8)
--no-header                do not output a header
--timezone=TIMEZONE        set the timezone config before running the query
--search-path=SEARCH_PATH  set the search_path config before running the query
--dry-run                  print the COPY statement that would be run without actually running it

View on GitHub

5.  Nancy

Nancy helps to conduct automated database experiments.

The Nancy Command Line Interface is a unified way to manage automated database experiments either in clouds or on-premise.

What is a Database Experiment?

Database experiment is a set of actions performed to test

  • (a) specified SQL queries (“workload”)
  • (b) on specified machine / OS / Postgres version (“environment”)
  • (c) against specified database (“object”)
  • (d) with an optional change – some DDL or config change (“target” or “delta”).

Two main goals for any database experiment:

  • (1) validation – check that the specified workload is valid,
  • (2) benchmark – perform deep SQL query analysis.

Database experiments are needed when you:

  • add or remove indexes;
  • for a new DB schema change, want to validate it and estimate migration time;
  • want to verify some query optimization ideas;
  • tune database configuration parameters;
  • do capacity planning and want to stress-test your DB in some environment;
  • plan to upgrade your DBMS to a new major version;
  • want to train ML model related to DB optimization.

View on Gitlab

6.  SchemaSpy

Please support the project by simply putting a Github star. Share this library with friends on Twitter and everywhere else you can.

If you notice a bug or have something not working, please report an issue, we’ll try to fix it as soon as possible. More documentation and features expected to be soon. Feel free to contribute.

This is a new code repository for SchemaSpy tool initially created and maintained by John Currier. I personally believe that work on SchemaSpy should be continued, and a lot of still existing issues should be resolved. Last released version of the SchemaSpy was in 2010, and I have a plan to change this.

I would like to say thank you to John Currier for the invention of this database entity-relationship (ER) diagram generator.

My plan is to release new SchemaSpy version and concentrate on refactoring and improving it.

In new SchemaSpy 6.0 version you will find:

Absolutely new amazing look and feel generated content

You can very easily change SchemaSpy theme because all of the logic was moved outside the Java code

Speed of database generation improved a little

Generation of the html pages was changed from plain concatenated text in Java code to Moustache engine

In database comments you can use Markdown language what should improve user experience from reading your database documentation

You can also easily create in database comments links to the tables and columns

More detail will came in next days plus I have plan to start working on all not resolved issues.

View on GitHub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related