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
Let’s get into
usql shell by typing in
$ usql Type "help" for help. (not connected)=>
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 is the driver name or alia and
transport is one of
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
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