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.

Avatica
Clickhouse
Cockroachdb
Cassandra 
FirebirdSQL 
Sap Hana
Apache Ignite
Memsql 
MS SQL 
MySQL 
Couchbase
Postgres
PrestoDB
AWS Redshift
Snowflake
Sqlite3
TiDB
Vertica
Vitess
VoltDB

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)=> \?
General
  \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

Help
  \? [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

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

Formatting
  \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

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

Connection
  \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

Variables
  \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

driver+transport://user:pass@host/dbname?opt1=a&opt2=b

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;
 Tables_in_mysql
---------------------------
 columns_priv
 component
 db
 default_roles
 engine_cost
 func
 general_log
 ....
 time_zone_transition
 time_zone_transition_type
 user
(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`) 
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; 

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:

WordPress.com Logo

You are commenting using your WordPress.com 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