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
-
TUXEDO Computers Unveils Linux Laptop Featuring AMD Ryzen CPU
This latest release is the first laptop to include the new CPU from Ryzen and Linux preinstalled.
-
XZ Gets the All-Clear
The back door xz vulnerability has been officially reverted for Fedora 40 and versions 38 and 39 were never affected.
-
Canonical Collaborates with Qualcomm on New Venture
This new joint effort is geared toward bringing Ubuntu and Ubuntu Core to Qualcomm-powered devices.
-
Kodi 21.0 Open-Source Entertainment Hub Released
After a year of development, the award-winning Kodi cross-platform, media center software is now available with many new additions and improvements.
-
Linux Usage Increases in Two Key Areas
If market share is your thing, you'll be happy to know that Linux is on the rise in two areas that, if they keep climbing, could have serious meaning for Linux's future.
-
Vulnerability Discovered in xz Libraries
An urgent alert for Fedora 40 has been posted and users should pay attention.
-
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.