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
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
-
Red Hat Migrates RHEL from Xorg to Wayland
If you've been wondering when Xorg will finally be a thing of the past, wonder no more, as Red Hat has made it clear.
-
PipeWire 1.0 Officially Released
PipeWire was created to take the place of the oft-troubled PulseAudio and has finally reached the 1.0 status as a major update with plenty of improvements and the usual bug fixes.
-
Rocky Linux 9.3 Available for Download
The latest version of the RHEL alternative is now available and brings back cloud and container images for ppc64le along with plenty of new features and fixes.
-
Ubuntu Budgie Shifts How to Tackle Wayland
Ubuntu Budgie has yet to make the switch to Wayland but with a change in approaches, they're finally on track to making it happen.
-
TUXEDO's New Ultraportable Linux Workstation Released
The TUXEDO Pulse 14 blends portability with power, thanks to the AMD Ryzen 7 7840HS CPU.
-
AlmaLinux Will No Longer Be "Just Another RHEL Clone"
With the release of AlmaLinux 9.3, the distribution will be built entirely from upstream sources.
-
elementary OS 8 Has a Big Surprise in Store
When elementary OS 8 finally arrives, it will not only be based on Ubuntu 24.04 but it will also default to Wayland for better performance and security.
-
OpenELA Releases Enterprise Linux Source Code
With Red Hat restricting the source for RHEL, it was only a matter of time before those who depended on that source struck out on their own.
-
StripedFly Malware Hiding in Plain Sight as a Cryptocurrency Miner
A rather deceptive piece of malware has infected 1 million Windows and Linux hosts since 2017.
-
Experimental Wayland Support Planned for Linux Mint 21.3
As with most Linux distributions, the migration to Wayland is in full force. While some distributions have already made the move, Linux Mint has been a bit slower to do so.