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.)

#> 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

sudo ln -s /var/lib/flatpak/.../libicuuc.so.59 /var/lib/libicuuc.so.60

sudo ln -s /var/lib/flatpak/.../libicuuc.so.61 /var/lib/libicuuc.so.60

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").

#> GO111MODULE=on go get -u github.com/xo/usql

#> GO111MODULE=on go get -u -tags all github.com/xo/usql

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.