usql: The Universal command-line interface for SQL databases

Recently I discovered a useful utility called usql while trying to find a better CLI for MySQL. I personally prefer psql PostgreSQL command-line tool so I was trying to find a similar tool for MySQL. During my search for the psql like MySQL CLI I stumbled upon usql – universal command-line interface for SQL database. I like playing with CLI tools as they play a big role in improving the developer experience.

I discovered that there is another popular project in Microsoft world with the same name. It is called U-SQL. U-SQL is the new big data query language of the Azure Data Lake Analytics service.

So, let’s give usql a try.

There are multiple ways you can install usql as documented in the project Github README.

On Mac, installation is as simple as running following brew commands.

$ brew tap xo/xo
$ brew install usql

The above does take few minutes since it downloads and installs most of the driver.

At the time of the writing (September 2020), usql supports following databases.

Sap Hana
Apache Ignite
AWS Redshift

The above is an impressive list of databases supported by usql.

Let’s get into usql shell by typing in usql

$ usql
Type "help" for help.

(not connected)=>

All usql commands start with \. To see all the possible commands you can type

(not connected)=> \?
  \q                              quit usql
  \copyright                      show usql usage and distribution terms
  \drivers                        display information about available database drivers
  \g [FILE] or ;                  execute query (and send results to file or |pipe)
  \gexec                          execute query and execute each value of the result
  \gset [PREFIX]                  execute query and store results in usql variables

  \? [commands]                   show help on backslash commands
  \? options                      show help on usql command-line options
  \? variables                    show help on special variables

Query Buffer
  \e [FILE] [LINE]                edit the query buffer (or file) with external editor
  \p                              show the contents of the query buffer
  \raw                            show the raw (non-interpolated) contents of the query buffer
  \r                              reset (clear) the query buffer
  \w FILE                         write query buffer to file

  \echo [STRING]                  write string to standard output
  \i FILE                         execute commands from file
  \ir FILE                        as \i, but relative to location of current script

  \pset [NAME [VALUE]]            set table output option
  \a                              toggle between unaligned and aligned output mode
  \C [STRING]                     set table title, or unset if none
  \f [STRING]                     show or set field separator for unaligned query output
  \H                              toggle HTML output mode
  \T [STRING]                     set HTML <table> tag attributes, or unset if none
  \t [on|off]                     show only rows
  \x [on|off|auto]                toggle expanded output

  \begin                          begin a transaction
  \commit                         commit current transaction
  \rollback                       rollback (abort) current transaction

  \c URL                          connect to database with url
  \c DRIVER PARAMS...             connect to database with SQL driver and parameters
  \Z                              close database connection
  \password [USERNAME]            change the password for a user
  \conninfo                       display information about the current database connection

Operating System
  \cd [DIR]                       change the current working directory
  \setenv NAME [VALUE]            set or unset environment variable
  \! [COMMAND]                    execute command in shell or start interactive shell

  \prompt [-TYPE] [PROMPT] <VAR>  prompt user to set variable
  \set [NAME [VALUE]]             set internal variable, or list all if no parameters
  \unset NAME                     unset (delete) internal variable

Let’s connect to MySQL database. You can refer to usql documentation to learn about the connection strings for your favourite database.

(not connected)=> \connect mysql://root:password@localhost:3306/mysql

Once connected you will get message like as shown below.

Connected with driver mysql (8.0.17)

The general syntax for connecting to any database is


where driver is the driver name or alia and transport is one of tcp, udp, unix or driver name (for ODBC and ADODB)

For MySQL, following all are valid connection strings

# connect to a mysql database
$ usql my://user:pass@host/dbname
$ usql mysql://user:pass@host:port/dbname
$ usql my://
$ usql /var/run/mysqld/mysqld.sock

You can now use SQL commands to view the details. Let’s start by listing the tables.

my:root@localhost:3306/mysql=> show tables;
(33 rows)

Let’s create a new database and run few queries.

my:root@localhost:3306/mysql=> create database usqldemo
my:root@localhost:3306/mysql=> use usqldemo;

Now, we will create a table and insert few values in it.

my:root@localhost:3306/usqldemo=> CREATE TABLE `animal_types` ( 
  `animal_type_id` int(11) NOT NULL AUTO_INCREMENT, 
  `animal_type_description` varchar(255) NOT NULL, 
  PRIMARY KEY (`animal_type_id`) 

my:root@localhost:3306/usqldemo=> INSERT INTO `animal_types` VALUES(1, 'duck'); 
my:root@localhost:3306/usqldemo=> INSERT INTO `animal_types` VALUES(2, 'cow'); 
my:root@localhost:3306/usqldemo=> INSERT INTO `animal_types` VALUES(3, 'goose'); 
my:root@localhost:3306/usqldemo=> INSERT INTO `animal_types` VALUES(4, 'cat'); 
my:root@localhost:3306/usqldemo=> INSERT INTO `animal_types` VALUES(5, 'sheep'); 

Now, we can make select queries.

my:root@localhost:3306/usqldemo=> select * from animal_types;
 animal_type_id | animal_type_description
 1              | duck
 2              | cow
 3              | goose
 4              | cat
 5              | sheep
(5 rows)

You can look at the query buffer.

my:root@localhost:3306/usqldemo=> \p
select * from animal_types;

To view your connection info

my:root@localhost:3306/usqldemo=> \conninfo
Connected with driver mysql (root:password@tcp(localhost:3306)/usqldemo?loc=Local&parseTime=true&sql_mode=ansi)

You can also set and get variables

my:root@localhost:3306/usqldemo=> \set today `date`

We set a variable called today with value from date SHELL variable. Backtick’d parameter allow you to access SHELL variables.

We can then echo the today variable as shown below.

my:root@localhost:3306/usqldemo-> \echo :today
Wed Sep  9 21:14:09 IST 2020

The last useful feature that I want to cover is .usqlrc runtime configuration file. This is read by usql at startup. It should be in the user’s HOME directory.

Let’s create a new file .usqlrc with following content.

\set username `users` 
\echo Welcome :username , enjoy usql
\set today `date`
\echo Today's date is :today

Now, when you restart usql you will be greeted by the message.

$ usql
Welcome shekhargulati , enjoy usql
Today's date is Wed Sep  9 21:27:11 IST 2020

One feature that I missed in usql is the psql \timing command.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s