Databases

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.

Connecting to Databases

Usql opens a database connection by parsing a string and passing its content to the appropriate database driver. Database connection strings (aka "data source names" or DSNs) can be passed to usql directly on the command line or at any moment during an interactive session. In an interactive session, you can pass all the necessary parameters (driver, database name, etc.) separately via the \c meta command. In general, DSNs that connect to multi-user database servers like PostgreSQL or MariaDB have the following structure:

driver+transport://user:pass@host/dbname

The driver part is the name of the driver you wish to use, which corresponds to the type of database, or any of its aliases allowed by usql. When connecting to a PostgreSQL database, for example, the driver may be postgres or pg, whereas with MySQL, you should use mysql or just my. The user, pass (that is, password), and dbname components are self-explanatory, and the host is the name or IP address of the computer hosting the database. If that computer is the same as the computer where you run usql, host should be the same as localhost.

The transport part is not mandatory, and unless you work with a database that requires it, or with non-default connection protocols, you may never need it.

To connect to a database from the Linux command line, use the following command:

#> usql my://marco:mypassword@localhost/customers

or, after launching usql, from its own prompt:

\c my://marco:mypassword@localhost/customers

The steps are slightly different if you want to connect to a serverless, file-based database like SQLite 3. In this case, the DSN has a much simpler structure:

driver:/path/to/file-on-disk

where the driver could be sqlite3, sq, or file, or the command might not specify a driver. Either of the following commands would open an already existing SQLite3 database contained in the single file $HOME/my-sqlite-db.sqlite3:

#> usql sqlite3://$HOME/my-sqlite-db.sqlite3
#> usql $HOME/my-sqlite-db.sqlite3

The second command will work without errors only if the $HOME/my-sqlite-db.sqlite3 file already exists and is, indeed, an SQLite3 database. If your intention is to make usql create a new, empty SQLite3 database and save it into a file with the specified name, you must use the first command, which includes a recognizable driver name. In this case, be sure to use the right number of slashes: the sq: and sqlite3: driver names want two slashes after the colon, whereas the file: name only requires one:

#> usql sqlite3://$HOME/my-sqlite-db.sqlite3
#> usql file:/$HOME/my-sqlite-db.sqlite3

To close an open connection from inside usql, just type \Z. In any moment, you can also verify the parameters of the connection you are using by typing \conninfo.

Editing and Reusing Queries

Once you are connected to a database from inside a usql session, you can communicate with it just as if you were using a native client. Usql also keeps the current query in a dedicated buffer that you can edit and reuse. Use the \e meta command to edit the buffer and the \w command to write a query into the buffer. Optional arguments for a file name and line number allow you to edit queries saved to disk in previous sessions. The \p command shows the buffer contents and \r resets the buffer.

Use the \g command to re-execute the query in the buffer. If you also want to execute every value of the result, type \gexec. The \gset command stores the result of the query in usql variables for further reuse.

Output Formatting

A nice feature of usql is its many options for how to print the result of a query. You can print in CSV, JSON, or table format, with either plain text or HTML. Print to CSV or JSON with the -C and -J options. The options for printing to tables are too many to describe, but they are mostly self-explanatory. \H toggles the HTML output mode and \T, followed by a string of valid HTML table attributes, will apply the attributes to the current table.

Variables and System Interaction

One thing I particularly like in usql is its capability to interact with the system it runs on. In Linux, you can type \! to open an interactive shell. Typing \! followed by a shell command will just execute the command and then return you to the usql prompt. Usql also lets you set and use environmental and also internal variables. For example:

#> \set MY_NAME 'Marco'
#> \set MY_NAME = 'Marco'
#> \unset MY_NAME
#> \setenv NUMBER_OF_CUSTOMERS FOUND_CUSTOMERS

In the first line, the \set meta command defines a variable and assigns a value to it. Without parameters, it shows names and values of all the previously defined variables. The \unset command, of course, deletes the specified variable. The command on the last line is, in my opinion, the most powerful, because it assigns a value (in this case, the current value of an internal variable called FOUND_CUSTOMERS) to an external variable. This feature is an efficient way for a usql script to pass what it finds in a database to whatever other script had called it. The snippet of pseudo code in Listing 1, which may be a part of any Linux shell script using usql to interact with databases, sums up the two methods:

01 usql -f myusqlscript_1 -C -o USQL-OUTPUT.csv
02 usql -f myusqlscript_2
03 echo "The number of customers is $NUMBER_OF_CUSTOMERS"

In Line 1, usql executes the queries found in the file myusqlscript_1 and writes the result, in CSV format, to a file called USQL-OUTPUT.csv. In Line 2, usql executes the contents of the file myusqlscript_2 without creating any file. However, if the file myusqlscript_2 contains this combination of usql commands already:

\gset FOUND_CUSTOMERS
 \setenv NUMBER_OF_CUSTOMERS FOUND_CUSTOMERS

then the shell script of Listing 1 will find the result of the work done by usql inside $NUMBER_OF_CUSTOMERS and will reuse as needed (see line 3). Of course, no data exchange method between usql and shell script is better than the other.

Most of the time, an output file is a more efficient way to store and pass around lots of output, and environment variables may be more convenient to exchange one or a few values.

Like normal shell scripts, usql meta commands can use back ticks, that is, inverted single quotes, to assign the output of a command to a variable. At the usql prompt, or in a usql file, you may write statements like the following:

\echo Welcome `echo $USER`
Welcome marco
=>

but you may also combine this feature with the \set meta command to save values obtained from the shell in some variable:

=> \set MY_NAME `echo $USER`
=> \echo :MY_NAME
marco

The last \echo statement introduces one final feature of usql: variable interpolation. Once you have created a variable and assigned a value to it with \set, you can substitute its value to its name in composite statements, just like you can in shell scripts. All you have to do is prefix the variable name with a colon:

=> \set CURRENT_CUSTOMER marco
=> \set CURRENT_TABLE sales
=> select * from :CURRENT_TABLE where "name" = :'CURRENT_CUSTOMER';
=> \p
=> select * from sales where "name" = 'marco';
=> \g

Usql can handle variable names and interpolated strings – either alone or enclosed in single or double quotes, depending on the SQL syntax of the current database. As long as you place a colon before the name and any quotes, usql will understand that what follows is a variable name that should be replaced with the value of the variable before executing the query.

Conclusion

Usql is a useful tool that lets you interact with several different database systems from a single interface. You can also run scripts and access Linux command line programs. You can even run scripts that let you generate usql command files on the fly. If you work with database systems, I hope this tutorial will encourage you to try out usql, because it could save you a lot of time.

The Author

Marco Fioretti is a freelance author, trainer, and researcher based in Rome, Italy, who has been working with Free and Open Source Software since 1995 – and on open digital standards since 2005. Marco is a Board Member of the Free Knowledge Institute (http://freeknowledge.eu), and he blogs about digital rights at http://stop.zona-m.net.