November 13, 2018

3550 words 17 mins read



Universal command-line interface for SQL databases

repo name xo/usql
repo link
language Go
size (curr.) 669 kB
stars (curr.) 5767
created 2017-03-02
license Other

usql Build Status

A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQL and non-relational databases!

Installing | Building | Using | Database Support | Features and Compatibility | Releases


usql provides a simple way to work with SQL and NoSQL databases via a command-line inspired by PostgreSQL’s psql. usql supports most of the core psql features, such as variables, backticks, and commands and has additional features that psql does not, such as syntax highlighting, context-based completion, and multiple database support.

Database administrators and developers that would prefer to work with a tool like psql with non-PostgreSQL databases, will find usql intuitive, easy-to-use, and a great replacement for the command-line clients/tools for other databases.


usql can be installed via Release, via Homebrew, via Scoop or via Go:

Installing via Release

  1. Download a release for your platform
  2. Extract the usql or usql.exe file from the .tar.bz2 or .zip file
  3. Move the extracted executable to somewhere on your $PATH (Linux/macOS) or %PATH% (Windows)

Installing via Homebrew (macOS)

usql is available in the xo/xo tap, and can be installed in the usual way with the brew command:

# add tap
$ brew tap xo/xo

# install usql with "most" drivers
$ brew install usql

Additional support for Oracle and ODBC databases can be installed by passing --with-* parameters during install:

# install usql with oracle and odbc support
$ brew install --with-oracle --with-odbc usql

Please note that Oracle Database support requires using the xo/xo tap’s instantclient-sdk formula. Any other instantclient-sdk formulae or older versions of the Oracle Instant Client SDK should be uninstalled prior to attempting the above:

# uninstall the instantclient-sdk formula
$ brew uninstall InstantClientTap/instantclient/instantclient-sdk

# remove conflicting tap
$ brew untap InstantClientTap/instantclient

Installing via Scoop (Windows)

usql can be installed using Scoop:

# install scoop if not already installed
iex (new-object net.webclient).downloadstring('')

scoop install usql

Installing via Go

usql can be installed in the usual Go fashion:

# install usql with basic database support (includes PosgreSQL, MySQL, SQLite3, and MS SQL drivers)
$ go get -u

Support for additional databases can be specified with build tags:

# install usql with most drivers (excludes drivers requiring CGO)
$ go get -u -tags most

# install usql with all drivers (includes drivers requiring CGO, namely Oracle and ODBC drivers)
$ go get -u -tags all


When building usql with Go, only drivers for PostgreSQL, MySQL, SQLite3 and Microsoft SQL Server will be enabled by default. Other databases can be enabled by specifying the build tag for their database driver. Additionally, the most and all build tags include most, and all SQL drivers, respectively:

# install all drivers
$ go get -u -tags all

# install with most drivers (same as all but excludes Oracle/ODBC)
$ go get -u -tags most

# install with base drivers and Oracle/ODBC support
$ go get -u -tags 'oracle odbc'

For every build tag <driver>, there is also the no_<driver> build tag disabling the driver:

# install all drivers excluding avatica and couchbase
$ go get -u -tags 'all no_avatica no_couchbase'

Release Builds

Release builds are built with the most build tag. Additional SQLite3 build tags are also specified for releases.


An effort has been made to keep usql’s packages modular, and reusable by other developers wishing to leverage the usql code base. As such, it is possible to embed or create a SQL command-line interface (e.g, for use by some other project as an “official” client) using the core usql source tree.

Please refer to main.go to see how usql puts together its packages. usql’s code is also well-documented – please refer to the GoDoc listing for an overview of the various packages and APIs.

Database Support

usql works with all Go standard library compatible SQL drivers supported by

The list of drivers that usql was built with can be displayed using the \drivers command:

$ cd $GOPATH/src/
$ go build -tags 'no_most postgres mysql cql sqlite3' && ./usql
Type "help" for help.

(not connected)=> \drivers
Available Drivers:
  cockroachdb (postgres) [cr, cdb, crdb, cockroach]
  memsql (mysql) [me]
  mssql [ms, sqlserver]
  mysql [my, maria, aurora, mariadb, percona]
  postgres [pg, pgsql, postgresql]
  redshift (postgres) [rs]
  sqlite3 [sq, file, sqlite]
  tidb (mysql) [ti]
  vitess (mysql) [vt]
(not connected)=>

The above shows that usql was built with only the postgres, mysql, cql, and sqlite3 drivers. The output above reflects information about the drivers available to usql, specifically the available driver and its primary URL scheme, the driver’s available aliases (shown in [...]), and the real/underlying driver (shown in (...)) for the database.

Any of the protocol schemes or aliases shown above can be used in conjunction with the \connect command when connecting to a database.

Supported Database Schemes and Aliases

The following is a table of all drivers, schemes, and aliases that usql supports:

Database (scheme/driver) Protocol Aliases [real driver]
Microsoft SQL Server (mssql) ms, sqlserver
MySQL (mysql) my, mariadb, maria, percona, aurora
Oracle Database (godror) or, oracle, oci8, oci, odpi, odpi-c
PostgreSQL (postgres) pg, postgresql, pgsql
SQLite3 (sqlite3) sq, sqlite, file
Amazon Redshift (redshift) rs [postgres]
CockroachDB (cockroachdb) cr, cockroach, crdb, cdb [postgres]
MemSQL (memsql) me [mysql]
TiDB (tidb) ti [mysql]
Vitess (vitess) vt [mysql]
Google Spanner (spanner) gs, google, span (not yet public)
MySQL (mymysql) zm, mymy
PostgreSQL (pgx) px
Apache Avatica (avatica) av, phoenix
Apache Ignite (ignite) ig, gridgain
Cassandra (cql) ca, cassandra, datastax, scy, scylla
ClickHouse (clickhouse) ch
Couchbase (n1ql) n1, couchbase
Cznic QL (ql) ql, cznic, cznicql
Firebird SQL (firebirdsql) fb, firebird
Microsoft ADODB (adodb) ad, ado
ODBC (odbc) od
OLE ODBC (oleodbc) oo, ole, oleodbc [adodb]
Presto (presto) pr, prestodb, prestos, prs, prestodbs
SAP ASE (tds) ax, ase, sapase
SAP HANA (hdb) sa, saphana, sap, hana
Snowflake (snowflake) sf
Vertica (vertica) ve
VoltDB (voltdb) vo, volt, vdb

Go Drivers and Build Tags

The following are the Go SQL drivers that usql supports, and the associated Go build tag:

Driver Build Tag Driver Used
Microsoft SQL Server mssql
MySQL mysql
PostgreSQL postgres
SQLite3 sqlite3
Oracle Database oracle
MySQL mymysql
PostgreSQL pgx
Apache Avatica avatica
Apache Ignite ignite
Cassandra cassandra
ClickHouse clickhouse
Couchbase couchbase
Cznic QL ql
Firebird SQL firebird
Microsoft ADODB adodb
ODBC odbc
Presto presto
Snowflake snowflake
Vertica vertica
VoltDB voltdb
Google Spanner spanner (not yet public)
MOST DRIVERS most all drivers excluding Oracle and ODBC (requires CGO and additional dependencies)
ALL DRIVERS all all drivers


After installing, usql can be used similarly to the following:

# connect to a postgres database
$ usql postgres://booktest@localhost/booktest

# connect to an oracle database
$ usql oracle://user:pass@host/oracle.sid

# connect to a postgres database and run script.sql
$ usql pg://localhost/ -f script.sql

Command-line Options

Supported command-line options:

$ usql --help
usql, the universal command-line interface for SQL databases

  usql [OPTIONS]... [DSN]

  DSN                            database url

  -c, --command=COMMAND ...    run only single command (SQL or internal) and exit
  -f, --file=FILE ...          execute commands from file and exit
  -w, --no-password            never prompt for password
  -X, --no-rc                  do not read start up file
  -o, --out=OUT                output file
  -W, --password               force password prompt (should happen automatically)
  -1, --single-transaction     execute as a single transaction (if non-interactive)
  -v, --set=, --variable=NAME=VALUE ...
                               set variable NAME to VALUE
  -P, --pset=VAR[=ARG] ...     set printing option VAR to ARG (see \pset command)
  -A, --no-align               unaligned table output mode
  -F, --field-separator=TEXT   field separator for unaligned output (default, "|")
  -H, --html                   HTML table output mode
  -R, --record-separator=TEXT  record separator for unaligned output (default, \n)
  -t, --tuples-only            print rows only
  -T, --table-attr=TEXT        set HTML table tag attributes (e.g., width, border)
  -x, --expanded               turn on expanded table output
  -z, --field-separator-zero   set field separator for unaligned output to zero byte
  -0, --record-separator-zero  set record separator for unaligned output to zero byte
  -J, --json                   JSON output mode
  -C, --csv                    CSV output mode
  -V, --version                display version and exit

Connecting to Databases

usql opens a database connection by parsing a URL and passing the resulting connection string to a database driver. Database connection strings (aka “data source name” or DSNs) have the same parsing rules as URLs, and can be passed to usql via command-line, or to the \connect or \c commands.

Connection strings look like the following:


Where the above are:

Component Description
driver driver name or alias
transport tcp, udp, unix or driver name (for ODBC and ADODB)
user username
pass password
host hostname
dbname* database name, instance, or service name/ID
?opt1=a&… additional database driver options (see respective SQL driver for available options)
/path/to/file a path on disk

* for Microsoft SQL Server, /dbname can be /instance/dbname, where /instance is optional. For Oracle Database, /dbname is of the form /service/dbname where /service is the service name or SID, and /dbname is optional. Please see below for examples.

Driver Aliases

usql supports the same driver names and aliases from the dburl package. Most databases have at least one or more alias - please refer to the dburl documentation for all supported aliases.

Short Aliases

All database drivers have a two character short form that is usually the first two letters of the database driver. For example, pg for postgres, my for mysql, ms for mssql, or for oracle, or sq for sqlite3.

Passing Driver Options

Driver options are specified as standard URL query options in the form of ?opt1=a&obt2=b. Please refer to the relevant database driver’s documentation for available options.

Paths on Disk

If a URL does not have a driver: scheme, usql will check if it is a path on disk. If the path exists, usql will attempt to use an appropriate database driver to open the path.

If the specified path is a Unix Domain Socket, usql will attempt to open it using the MySQL driver. If the path is a directory, usql will attempt to open it using the PostgreSQL driver. If the path is a regular file, usql will attempt to open the file using the SQLite3 driver.

Driver Defaults

As with URLs, most components in the URL are optional and many components can be left out. usql will attempt connecting using defaults where possible:

# connect to postgres using the local $USER and the unix domain socket in /var/run/postgresql
$ usql pg://

Please see documentation for the database driver you are connecting with for more information.

Connection Examples

The following are example connection strings and additional ways to connect to databases using usql:

# connect to a postgres database
$ usql pg://user:pass@host/dbname
$ usql pgsql://user:pass@host/dbname
$ usql postgres://user:pass@host:port/dbname
$ usql pg://
$ usql /var/run/postgresql
$ usql pg://user:pass@host/dbname?sslmode=disable # Connect without SSL

# 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

# connect to a mssql (Microsoft SQL) database
$ usql ms://user:pass@host/dbname
$ usql ms://user:pass@host/instancename/dbname
$ usql mssql://user:pass@host:port/dbname
$ usql ms://

# connect to a mssql (Microsoft SQL) database using Windows domain authentication
$ runas /user:ACME\wiley /netonly "usql mssql://host/dbname/"

# connect to a oracle database
$ usql or://user:pass@host/sid
$ usql oracle://user:pass@host:port/sid
$ usql or://

# connect to a cassandra database
$ usql ca://user:pass@host/keyspace
$ usql cassandra://host/keyspace
$ usql cql://host/
$ usql ca://

# connect to a sqlite database that exists on disk
$ usql dbname.sqlite3

# NOTE: when connecting to a SQLite database, if the "<driver>://" or
# "<driver>:" scheme/alias is omitted, the file must already exist on disk.
# if the file does not yet exist, the URL must incorporate file:, sq:, sqlite3:,
# or any other recognized sqlite3 driver alias to force usql to create a new,
# empty database at the specified path:
$ usql sq://path/to/dbname.sqlite3
$ usql sqlite3://path/to/dbname.sqlite3
$ usql file:/path/to/dbname.sqlite3

# connect to a adodb ole resource (windows only)
$ usql adodb://Microsoft.Jet.OLEDB.4.0/myfile.mdb
$ usql "adodb://Microsoft.ACE.OLEDB.12.0/?Extended+Properties=\"Text;HDR=NO;FMT=Delimited\""

# connect with ODBC driver (requires building with odbc tag)
$ cat /etc/odbcinst.ini
Description=DB2 driver
FileUsage = 1
DontDLClose = 1

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
# connect to db2, postgres databases using ODBC
$ usql odbc+DB2://user:pass@localhost/dbname
$ usql odbc+PostgreSQL+ANSI://user:pass@localhost/dbname?TraceFile=/path/to/trace.log

Executing Queries and Commands

The interactive intrepreter reads queries and meta (\ ) commands, sending the query to the connected database:

$ usql sqlite://example.sqlite3
Connected with driver sqlite3 (SQLite3 3.17.0)
Type "help" for help.

sq:example.sqlite3=> create table test (test_id int, name string);
sq:example.sqlite3=> insert into test (test_id, name) values (1, 'hello');
sq:example.sqlite3=> select * from test;
  test_id | name
        1 | hello
(1 rows)

sq:example.sqlite3=> select * from test
sq:example.sqlite3-> \p
select * from test
sq:example.sqlite3-> \g
  test_id | name
        1 | hello
(1 rows)

sq:example.sqlite3=> \c postgres://booktest@localhost
error: pq: 28P01: password authentication failed for user "booktest"
Enter password:
Connected with driver postgres (PostgreSQL 9.6.6)
pg:booktest@localhost=> select * from authors;
  author_id |      name
          1 | Unknown Master
          2 | blah
          3 | aoeu
(3 rows)


Commands may accept one or more parameter, and can be quoted using either ' or ". Command parameters may also be backtick’d.

Backslash Commands

Currently available commands:

$ usql
Type "help" for help.

(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 [on|off]                     show only rows
  \T [STRING]                     set HTML <table> tag attributes, or unset if none
  \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

Features and Compatibility

The usql project’s goal is to support all standard psql commands and features. Pull Requests are always appreciated!

Variables and Interpolation

usql supports client-side interpolation of variables that can be \set and \unset:

$ usql
(not connected)=> \set
(not connected)=> \set FOO bar
(not connected)=> \set
FOO = 'bar'
(not connected)=> \unset FOO
(not connected)=> \set
(not connected)=>

A \set variable, NAME, will be directly interpolated (by string substitution) into the query when prefixed with : and optionally surrounded by quotation marks (' or "):

pg:booktest@localhost=> \set FOO bar
pg:booktest@localhost=> select * from authors where name = :'FOO';
  author_id | name
          7 | bar
(1 rows)

The three forms, :NAME, :'NAME', and :"NAME", are used to interpolate a variable in parts of a query that may require quoting, such as for a column name, or when doing concatenation in a query:

pg:booktest@localhost=> \set TBLNAME authors
pg:booktest@localhost=> \set COLNAME name
pg:booktest@localhost=> \set FOO bar
pg:booktest@localhost=> select * from :TBLNAME where :"COLNAME" = :'FOO'
pg:booktest@localhost-> \p
select * from authors where "name" = 'bar'
pg:booktest@localhost-> \raw
select * from :TBLNAME where :"COLNAME" = :'FOO'
pg:booktest@localhost-> \g
  author_id | name
          7 | bar
(1 rows)


Note: variables contained within other strings will NOT be interpolated:

pg:booktest@localhost=> select ':FOO';
(1 rows)

pg:booktest@localhost=> \p
select ':FOO';

Backtick’d parameters

Meta (\ ) commands support backticks on parameters:

(not connected)=> \echo Welcome `echo $USER` -- 'currently:' "(" `date` ")"
Welcome ken -- currently: ( Wed Jun 13 12:10:27 WIB 2018 )
(not connected)=>

Backtick’d parameters will be passed to the user’s SHELL, exactly as written, and can be combined with \set:

pg:booktest@localhost=> \set MYVAR `date`
pg:booktest@localhost=> \set
MYVAR = 'Wed Jun 13 12:17:11 WIB 2018'
pg:booktest@localhost=> \echo :MYVAR
Wed Jun 13 12:17:11 WIB 2018


usql supports reading passwords for databases from a .usqlpass file contained in the user’s HOME directory at startup:

$ cat $HOME/.usqlpass
# format is:
# protocol:host:port:dbname:user:pass
$ usql pg://
Connected with driver postgres (PostgreSQL 9.6.9)
Type "help" for help.


Note: the .usqlpass file cannot be readable by other users. Please set the permissions accordingly:

$ chmod 0600 ~/.usqlpass

Runtime Configuration (RC) File

usql supports executing a .usqlrc contained in the user’s HOME directory:

$ cat $HOME/.usqlrc
\set SYNTAX_HL_STYLE paraiso-dark
$ usql
WELCOME TO THE JUNGLE Thu Jun 14 02:36:53 WIB 2018
Type "help" for help.

(not connected)=> \set
SYNTAX_HL_STYLE = 'paraiso-dark'
(not connected)=>

The .usqlrc file is read by usql at startup in the same way as a file passed on the command-line with -f / --file. It is commonly used to set startup environment variables and settings.

You can temporarily disable the RC-file by passing -X or --no-rc on the command-line:

$ usql --no-rc pg://

Host Connection Information

By default, usql displays connection information when connecting to a database. This might cause problems with some databases or connections. This can be disabled by setting the system environment variable USQL_SHOW_HOST_INFORMATION to false:

$ usql pg://booktest@localhost
Type "help" for help.


SHOW_HOST_INFORMATION is a standard usql variable, and can be \set or \unset. Additionally, it can be passed via the command-line using -v or --set:

$ usql --set SHOW_HOST_INFORMATION=false pg://
Type "help" for help.

pg:booktest@=> \set SHOW_HOST_INFORMATION true
pg:booktest@=> \connect pg://
Connected with driver postgres (PostgreSQL 9.6.9)

Syntax Highlighting

Interactive queries will be syntax highlighted by default, using Chroma. There are a number of variables that control syntax highlighting:

Variable Default Values Description
SYNTAX_HL true true or false enables syntax highlighting
SYNTAX_HL_FORMAT dependent on terminal support formatter name Chroma formatter name
SYNTAX_HL_OVERRIDE_BG true true or false enables overriding the background color of the chroma styles
SYNTAX_HL_STYLE monokai style name Chroma style name

Time Formatting

Some databases support time/date columns that support formatting. By default, usql formats time/date columns as RFC3339Nano, and can be set using the TIME_FORMAT variable:

$ usql pg://
Connected with driver postgres (PostgreSQL 9.6.9)
Type "help" for help.

pg:booktest@=> \set
pg:booktest@=> select now();
(1 rows)

pg:booktest@=> \set TIME_FORMAT Kitchen
pg:booktest@=> \g
(1 rows)

Any Go supported time format or const name (for example, Kitchen, in the above) can be used for TIME_FORMAT.


usql aims to eventually provide a drop-in replacement for PostgreSQL’s psql command. This is on-going – an attempt has been made in good-faith to provide support for the most frequently used aspects/features of psql. Compatability (where possible) with psql, takes general development priority.

  1. updated asciinema demo
  2. support more prompt configuration, colored prompt by default
  3. add window title / status output
  4. change drivers.Convert* to drivers.Marshal style interfaces
  5. allow configuration for JSON encoding/decoding output
  6. return single ‘driver’ type handling marshaling / scanning of types / columns
  7. implement “extended” display for queries (for \gx / formatting)
  8. implement better environment variable handling
  9. implement proper readline
  10. tab-completion of queries
  11. show hidden (client) queries (\set SHOW_HIDDEN)
  12. fix multiline behavior to mimic psql properly (on arrow up/down through history)
  13. proper PAGER support
  14. \qecho + \o support
  15. context-based completion (WIP)
  16. full \if \elif \else \endif support
  17. fix WITH ... DELETE queries (postgresql)
  18. better --help / man pages
  19. translations
  20. fix \command variable interpolation/parsing (\set NAME test \echo :NAME.dat \echo :NAME:NAME)
Command Processing + psql compatibility
  1. formatting settings (\pset, \a, etc)
  2. all \d* commands from psql (WIP, need to finish work extracting introspection code from xo)
  3. \ef and \ev commands from psql (WIP, need to finish work extracting stored procs / funcs / views for all the major databases)
  4. \watch
  5. \errverbose (show verbose info for last error)
  6. remaining psql cli parameters
  7. \j* commands (WIP)
  8. \copy (add support for copying between two different databases …?)
  1. test suite for databases, doing minimal of SELECT, INSERT, UPDATE, DELETE for every database
Future Database Support
  1. Redis CLI
  2. Native Oracle
  3. InfluxDB
  4. CSV via SQLite3 vtable
  5. Google Spanner
  6. Google Sheets via SQLite3 vtable
  7. Charlatan
  8. InfluxDB IQL
  9. Aerospike AQL
  10. ArrangoDB AQL
  11. OrientDB SQL
  12. Cypher / SparQL
  13. Atlassian JIRA JQL
  • dburl - Go package providing a standard, URL-style mechanism for parsing and opening database connection URLs
  • xo - Go command-line tool to generate Go code from a database schema
comments powered by Disqus