Usql offers a single user interface for managing multiple database systems
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:
Listing 1
Shell Script Pseudo Code
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.
Infos
- Introduction to SQL: http://www.w3schools.com/sql/
- Usql: https://github.com/xo/usql
- "Usql – A Universal Command-Line Interface for SQL Databases": https://news.ycombinator.com/item?id=13780587
- Usql release page: https://github.com/xo/usql/releases
- Installing Go: https://www.howtoforge.com/how-to-install-go-programming-language-on-linux-ubuntu-debian-centos/
« Previous 1 2 3
Buy Linux Magazine
Direct Download
Read full article as PDF:
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.
News
-
Armbian 23.05 is Now Available
Based on Debian 12, the latest version of the ARM/RISC-V distribution is now available to download and install.
-
Linux Mint Finally Receiving Support for Gestures
If you use the Linux Mint Cinnamon desktop, you'll be thrilled to know that 21.2 is getting support for gestures on touchscreen devices and touchpads.
-
An All-Snap Version of Ubuntu is In The Works
Along with the standard deb version of the open-source operating system, Canonical will release an-all snap version.
-
Mageia 9 Beta 2 Ready for Testing
The latest beta of the popular Mageia distribution now includes the latest kernel and plenty of updated applications.
-
KDE Plasma 6 Looks to Bring Basic HDR Support
The KWin piece of KDE Plasma now has HDR support and color management geared for the 6.0 release.
-
Bodhi Linux 7.0 Beta Ready for Testing
The latest iteration of the Bohdi Linux distribution is now available for those who want to experience what's in store and for testing purposes.
-
Changes Coming to Ubuntu PPA Usage
The way you manage Personal Package Archives will be changing with the release of Ubuntu 23.10.
-
AlmaLinux 9.2 Now Available for Download
AlmaLinux has been released and provides a free alternative to upstream Red Hat Enterprise Linux.
-
An Immutable Version of Fedora Is Under Consideration
For anyone who's a fan of using immutable versions of Linux, the Fedora team is currently considering adding a new spin called Fedora Onyx.
-
New Release of Br OS Includes ChatGPT Integration
Br OS 23.04 is now available and is geared specifically toward web content creation.