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.
Shekhar Gulati 