Usql offers a single user interface for managing multiple database systems
Databases
© Lead Image © Kheng Ho Toh, 123RF.com
Usql is a useful tool that lets you manage many different databases from one prompt.
These days, databases are everywhere, from official Census records to personal music playlists. Linux offers many tools for creating, populating, and querying databases. Some users may even say too many, and this tutorial is an answer to that complaint. I will introduce you to usql, a little tool that is a lifesaver for many users who work with databases.
The most ubiquitous and flexible way to work with any database is in a text-based interface. Inside a client application, you type queries at a prompt. The syntax might vary depending on the implementation of Standard Query Language (SQL) [1] the database is using. Depending on the database type, the client either executes the query directly or, much more frequently, forwards it to a server that actually handles the data. The result of the query is then printed out, usually in a tabular format. Alternatively, you can store sequences of queries or commands in a text file and pass it to the client that will execute them automatically, possibly saving the result to a file.
If you always work with one type of database (for example, only SQLite), you can just choose a client for that specific database and get good at using it. However, if you frequently switch back and forth between different database clients, each with its own personality and feature set, it can get very confusing. It is a little like having to edit text files all day long and being forced to continuously alternate between the vi and emacs.
Usql [2] is a single database client that works with several different database systems. Although the syntax of the actual queries might vary slightly depending on the database, other commands for operating the client are unified in a convenient way that will simplify your database experience and lower the learning curve for adding new database systems to your repertoire. Usql is a clone of psql, the standard command-line client for PostgreSQL databases. The goal of usql is to "support all standard psql commands and features" but to extend those commands to include other database systems.
At the time I wrote this article, the default usql package included support for most of the major databases you are likely to access from Linux, including PostgreSQL, Oracle, MySQL/MariaDB, SQLite3, and even Microsoft SQL Server. However, usql also has drivers for many relational, non-relational, and even NoSQL databases. In addition to Linux, usql runs on Windows and macOS, with a modular architecture that facilitates code reuse.
But keep in mind that usql is "an adapter, not an abstractor" [3]. If you try to access ten different databases with usql, you will still need to know all the SQL dialects and how they differ from each other. With usql, however, you will be able to query all those databases in the same session of the same terminal, and you'll have access to some extra features built into usql, such as syntax highlighting.
Installing usql
Usql is written in the Go language. The easiest way to use it on Linux is to download the tar archive for amd64 systems from the release page [4] (version 0.7.8 at time of writing). Uncompress the tarball and place the resulting binary file, unsurprisingly called usql, in some directory of your path. For Ubuntu desktops:
#> tar xvf usql-0.7.8-linux-amd64.tar.bz2 usql #> sudo mv usql /usr/local/bin
At this point, you should be able to type usql at a command prompt and start using the program. If your Linux system has a version of some library that is older than what usql expects, it won't run. In that case, if you type usql at the prompt, you will get an error messages similar to the following:
#> usql libicuuc.so.60: cannot open shared object file
Of course, many Linux applications fail to execute if the libraries are out of sync. The problem with the usql binary from the website is that the dependencies are not documented. (See the box entitled "Libraries" for more on what to do if your libraries aren't what usql is expecting.)
Libraries
If you get a message that says the application can't open a shared object file, you have three choices. The easiest option is to install an older version of usql from the release page [4]; the most future-proof, if possible, is to upgrade your Linux system.
If neither of those alternatives is viable, you may still be able to make the last version of usql run. If you have the required library, but in a non-standard location, you can just add a symbolic link to that library in a place where usql can see it. For example, assuming usql complained because it could not find a library named libicuuc.so.60:
#> sudo find / -name libicuuc.so.* 2>/dev/null /var/lib/flatpak/.../libicuuc.so.60 ... #> sudo ln -s /var/lib/flatpak/.../libicuuc.so.60 /var/lib/libicuuc.so.60
The first command searches the whole file system to find files with a name that starts with the libicuuc.so string and prints their locations. The part after the asterisk redirects all warning messages to /dev/null. In this example, there were several versions of that library scattered around the system, but only one with the version number required by usql. As expected, that file is in a non-standard place, brought into the system by some flatpak package. Therefore, the second command creates a symbolic link named libicuuc.so.60 into the folder /var/lib, where usql can find it. If, after this operation, you get the same complaint about other libraries, you can repeat the procedure. Of course, it is up to you to decide if this (ugly) path makes more sense than using an older release of usql or upgrading your system.
Don't forget that, as far as Linux itself is concerned, you could create links called libicuuc.so.80 to any file, including older versions of that same library:
sudo ln -s /var/lib/flatpak/.../libicuuc.so.59 /var/lib/libicuuc.so.60
or newer versions:
sudo ln -s /var/lib/flatpak/.../libicuuc.so.61 /var/lib/libicuuc.so.60
and usql will happily load the linked libraries. What would happen afterwards, however, is anybody's guess. It is certainly possible that using a slightly higher or lower version of some library does not make any real difference for a given program, but do NOT count on it! I am mentioning this trick, which, by the way, you can apply to any program, more as a warning than as an actual suggestion. In any case, whatever you do on your own system, please report the problem to the usql developers!
Another option for obtaining usql is to build it yourself in Go. One benefit of this approach is that, if you build usql yourself, you can customize the executable to contain only the drivers (see the box entitled "Custom Versions of usql").
Custom Versions of usql
Another way to set up usql on your system is to build it yourself with the Go interpreter. One benefit of building usql yourself is you could create a custom version that supports all and only the databases you really need. Of course, you'll need to set up Go on your system, which could be a complex task [5]. Once you have Go up and running, you can build usql with:
#> GO111MODULE=on go get -u github.com/xo/usql
At this time of writing, the result of this operation would be a usql executable that only includes the drivers for PostgreSQL, MySQL, SQLite3, and Microsoft SQL Server. To add support for other databases, you need to explicitly declare what you want at build time. To include all the drivers for all the databases that usql can talk to, for example, you should add the -tags all option:
#> GO111MODULE=on go get -u -tags all github.com/xo/usql
You may use the same -tags switch to specify single drivers or a predefined groups of drivers – or even to exclude single drivers from the executable. For details, please see the usql website.
Getting Started
Usql provides two sets of commands: a big family of "normal" commands, plus the internal "meta" commands of usql itself. The normal commands are nothing more than standard SQL queries that you would use in other clients to insert, edit, or fetch data. I won't describe the syntax of those queries in this article because this is not a general introduction to SQL, and the commands vary depending on the database. (You will find many good SQL tutorials online.)
The usql meta commands are all prefixed by a backslash, and the first two commands to learn are the ones that tell you what is available in your usql installation. The \drivers command first lists all the database drivers that were compiled into your copy of usql. The backslashed question mark \? lists the available meta commands if typed without arguments. Add the name of a command to learn what it does, or use the options and variable keywords to view available options and variables.
Configuration
It is possible to define the general configuration and start-up behavior of usql by writing the meta commands in the $HOME/.usqlrc file. Usql will also execute all the commands contained in a file passed to it with the -f or -file switches:
#> usql -f some-database-script.txt
You might be wondering what happens if the file loaded using the -f switch contains commands and settings that conflict with the general $HOME/.usqlrc configuration file? This issue is important, especially if you want to prepare reusable usql scripts. Luckily, usql offers an easy solution: just add the -X or --no-rc option when launching usql at the prompt, and usql will completely ignore (for that session only!) the default configuration file. During an interactive session, you can load and execute a command file as follows:
\i FILE \ir FILE
\i executes the contents of FILE, and \ir is similar except it looks for the file in the directory of the current script. \ir is helpful because, if you use usql on a regular basis, sooner or later you will end up creating your own library of usql scripts that could be organized in several folders and might even call each other.
Buy Linux Magazine
US / Canada
UK / Australia