Tool Up: mycli/pgcli for MySQL and PostgreSQL
PublishedThe 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