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
-
Canonical Bumps LTS Support to 12 years
If you're worried that your Ubuntu LTS release won't be supported long enough to last, Canonical has a surprise for you in the form of 12 years of security coverage.
-
Fedora 40 Beta Released Soon
With the official release of Fedora 40 coming in April, it's almost time to download the beta and see what's new.
-
New Pentesting Distribution to Compete with Kali Linux
SnoopGod is now available for your testing needs
-
Juno Computers Launches Another Linux Laptop
If you're looking for a powerhouse laptop that runs Ubuntu, the Juno Computers Neptune 17 v6 should be on your radar.
-
ZorinOS 17.1 Released, Includes Improved Windows App Support
If you need or desire to run Windows applications on Linux, there's one distribution intent on making that easier for you and its new release further improves that feature.
-
Linux Market Share Surpasses 4% for the First Time
Look out Windows and macOS, Linux is on the rise and has even topped ChromeOS to become the fourth most widely used OS around the globe.
-
KDE’s Plasma 6 Officially Available
KDE’s Plasma 6.0 "Megarelease" has happened, and it's brimming with new features, polish, and performance.
-
Latest Version of Tails Unleashed
Tails 6.0 is based on Debian 12 and includes GNOME 43.
-
KDE Announces New Slimbook V with Plenty of Power and KDE’s Plasma 6
If you're a fan of KDE Plasma, you'll be thrilled to hear they've announced a new Slimbook with an AMD CPU and the latest version of KDE Plasma desktop.
-
Monthly Sponsorship Includes Early Access to elementary OS 8
If you want to get a glimpse of what's in the pipeline for elementary OS 8, just set up a monthly sponsorship to help fund its continued existence.