The mycli and pgcli tools provide a useful alternative to the bundled command lines of MySQL and PostgreSQL. In this Tool Up article we have a quick look at their capabilities.

A command line is a powerful thing if you get along with it. It can provide a hook for scripting and managing your database which other tools only dream of. In some ways, it's talking with the database in the language it understands: text. Many people don't get on with command lines and that's ok; it's why there's plenty of web or GUI-based tools for databases out there. But if you have the inclination, it's worth adding some command line skills to your database interactions to unlock the capabilities.

Default command lines and beyond

Both MySQL and PostgreSQL come bundled with command line tools. MySQL has mysql and PostgreSQL psql and each has in-line editing of commands you are typing, the ability to invoke scripts and different levels of discovery.

Downsides are they both usually involve you installing the database in question. The bundled tools are not strong when it comes to auto-completion. Autocompletion is a great assistant when you are exploring a database, but on mysql it tends to show all available keywords even when a keyword isn't expected while on psql the matching is more contextually aware but shows the least possible information about available completions.

Both pgcli and mycli set out to address the latter downside with an emphasis on giving a cli with smarter auto-completion and syntax-highlighting. If you're eager. you'll find more details about them, and more importantly, installation instructions at www.pgcli.com and www.mycli.net. Otherwise, read on...

A Bit of History

These Python applications come from the same author, Amjith Ramanujam, who started writing pgcli in 2014 after being inspired by the interactive command-line experience of BPython. The mycli tool appeared later after MySQL users asked if he could bring the pgcli functionality to another database. The projects are actively maintained with fresh leadership after Amjith stepped down from driving the project.

What's the secret of these tools? In part, it's the sqlparse library which parses, but doesn't try to validate, SQL commands. That means it can look at a command line and tell the tool what the next appropriate entry could be from the context of where the cursor is in the partial statement. And to the user, that looks like, as if by magic, it knows you should either be looking for a field, function, keyword, table, database or so on.

Pro-Tip; avoid worrying about installation dependencies on database dependencies by installing whatever database you are remotely using on your local system. The great thing about open source is it won't cost you a thing to do and you'll have all the standard tools to hand.

The interlinking of drivers and databases does mean probably have to do this. For example, to connect to PostgreSQL, pgcli uses the psycopg2 library. The psycopg2 library uses the libpq driver and the libpq driver comes bundled with the PostgreSQL database distribution so you'll end up installing that to build the tool.

You can install both tools through the Python package installer with a pip install <name>. If you are on macOS, there are Homebrew packages for each too (brew install pgcli and brew install mycli) which will install without grabbing the full database distribution.

Connecting to Compose PostgreSQL with pgcli

The pgcli command is very amenable to different connection parameters. You can call pgcli <composeconnectionstring> and it will connect or you can use the Compose command line example for a database, swap psql for pgcli and it'll connect.

Connecting to Compose MySQL with mycli

Like the pgcli command, the mycli command will happily take the Compose connection string for a MySQL database. What it won't take are the command line parameters, so something like

mysql -u admin -p --host sl-eu-lon-2-portal.0.dblayer.com --port 17851 --ssl-mode=REQUIRED  

We will need to lose the -p and the --ssl-mode=REQUIRED and then switch the mysql for mycli. Oh, and be prepared to enter the password of course. You may find the connection string is a lot simpler in this case.

Working with the cli

The first command you are likely to need is \? which will display the various cli command native commands. The pgcli tool has a similar looking subset of commands to the psql command, especially with the \d* commands for displaying information and commands like \copy. That said, it's still a subset. As an interactive tool, you'll probably get a better idea of how it works by watching it:

Watching this, you'll see a connection being made, browsing the available commands, using a database and creating a select command. Note that we're not hitting the tab key to get the menus of options to display. They are coming up as soon as pgcli knows what it can offer. That preemption means you get a better idea of what options are available especially as the tool shows you the type. The most recent version of pgcli will also look at your history to figure out optimal suggestions too.

The syntax highlighting then picks out keywords that are in use making your parameters clearer. You'll also see the use of the multiline option here. This allows you to spread out a command over multiple lines to make it easier to see the structure of an SQL statement. This is, a blessing and a curse and the preemptive suggestions cover up the other parts of the statement being worked on and can make things a little confusing - watch where a "FROM" is added because we can't see the next line is a FROM. It's less of a problem in single-line mode though and easy enough to switch between the two modes. Here's a quick look at the command set for pgcli:

The \h command also offers up useful SQL syntax documentation for when the preemptive completion isn't quite hitting the mark. It's not as slick as say Bpython's "display the arguments and other info as part of the completion" but Python is a lot more discoverable than SQL dialects.

For comparison, the commands on mycli are more limited in range:

Far fewer display commands, none of the named query handling and no sql syntax information. Both tools offer the same set of core capabilities but pgcli takes the lead as feeling more mature and actively extended. Both projects share the dbcli Github organization so it's not as if they are overtly diverging beyond the differences that their target database brings.

The best way to understand these tools is to spend some time using them. Whether you find pgcli and mycli autocompletion a help or a hinderance will, like choice of editor, be a personal one. Getting to know how to explore your database at the command line level, whatever tool you use, is a powerful tool to add to your personal database skillset.

Read more articles about Compose databases - use our Curated Collections Guide for articles on each database type. If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

attribution Mitchel Lensink