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:
- Download a release for your platform
- Extract the
usql
orusql.exe
file from the.tar.bz2
or.zip
file - Move the extracted executable to somewhere on your
$PATH
(Linux/macOS) or%PATH%
(Windows)
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 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
usql
can be installed using Scoop:
# install scoop if not already installed
iex (new-object net.webclient).downloadstring('https://get.scoop.sh')
scoop install usql
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 github.com/xo/usql
Support for additional databases can be specified with build tags:
# install usql with most drivers (excludes drivers requiring CGO)
$ go get -u -tags most github.com/xo/usql
# install usql with all drivers (includes drivers requiring CGO, namely Oracle and ODBC drivers)
$ go get -u -tags all github.com/xo/usql
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 github.com/xo/usql
# install with most drivers (same as all but excludes Oracle/ODBC)
$ go get -u -tags most github.com/xo/usql
# install with base drivers and Oracle/ODBC support
$ go get -u -tags 'oracle odbc' github.com/xo/usql
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' github.com/xo/usql
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.
usql
works with all Go standard library compatible SQL drivers supported by
github.com/xo/dburl
.
The list of drivers that usql
was built with can be displayed using the
\drivers
command:
(not connected)=> \drivers
(not connected)=> \drivers
Available Drivers:
mssql [ms, sqlserver]
mysql [my, maria, aurora, mariadb, percona]
postgres [pg, pgsql, postgresql]
sqlite3 [sq, file, sqlite]
The above shows that usql
was built with 4 drivers (mssql
, mysql
,
postgres
, and sqlite3
). The names contained within [...]
are the
additional scheme aliases recognized by \connect
command when
connecting to a database.
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 (ora) | or, oracle, oci8, oci |
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 |
VoltDB (voltdb) | vo, volt, vdb |
The following are the Go SQL drivers that usql
supports, and the
associated Go build tag:
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
Supported command-line options:
$ usql --help
usql, the universal command-line interface for SQL databases
Usage:
usql [OPTIONS]... [DSN]
Arguments:
DSN database url
Options:
-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
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:
driver+transport://user:pass@host/dbname?opt1=a&opt2=b
driver:/path/to/file
/path/to/file
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, the syntax to supply an
instance and database name is /instance/dbname
, where /instance
is
optional. For Oracle databases, /dbname
is the unique database ID (SID).
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.
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
.
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.
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.
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.
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
[DB2]
Description=DB2 driver
Driver=/opt/db2/clidriver/lib/libdb2.so
FileUsage = 1
DontDLClose = 1
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
# 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
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);
CREATE TABLE
sq:example.sqlite3=> insert into test (test_id, name) values (1, 'hello');
INSERT 1
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)
pg:booktest@localhost=>
Commands may accept one or more parameter, and can be quoted using either '
or "
. Command parameters may also be backtick'd.
Currently available commands:
$ usql
Type "help" for help.
(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 [on|off] show only rows
\T [STRING] set HTML <table> tag attributes, or unset if none
\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
The usql
project's goal is to support all standard psql
commands and
features. Pull Requests are always appreciated!
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)
pg:booktest@localhost=>
Note: variables contained within other strings will NOT be interpolated:
pg:booktest@localhost=> select ':FOO';
?column?
+----------+
:FOO
(1 rows)
pg:booktest@localhost=> \p
select ':FOO';
pg:booktest@localhost=>
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
pg:booktest@localhost=>
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
postgres:*:*:*:booktest:booktest
$ usql pg://
Connected with driver postgres (PostgreSQL 9.6.9)
Type "help" for help.
pg:booktest@=>
Note: the .usqlpass
file cannot be readable by other users. Please set the
permissions accordingly:
$ chmod 0600 ~/.usqlpass
usql
supports executing a .usqlrc
contained in the user's HOME
directory:
$ cat $HOME/.usqlrc
\echo WELCOME TO THE JUNGLE `date`
\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://
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
:
$ export USQL_SHOW_HOST_INFORMATION=false
$ usql pg://booktest@localhost
Type "help" for help.
pg:booktest@=>
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)
pg:booktest@=>
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 |
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
TIME_FORMAT = 'RFC3339Nano'
pg:booktest@=> select now();
now
+----------------------------------+
2018-06-14T03:24:12.481923+07:00
(1 rows)
pg:booktest@=> \set TIME_FORMAT Kitchen
pg:booktest@=> \g
now
+--------+
3:24AM
(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.
- updated asciinema demo
- support more prompt configuration, colored prompt by default
- add window title / status output
- change
drivers.Convert*
to drivers.Marshal style interfaces - allow configuration for JSON encoding/decoding output
- return single 'driver' type handling marshaling / scanning of types / columns
- implement "extended" display for queries (for
\gx
/ formatting) - implement better environment variable handling
- implement proper readline
- tab-completion of queries
- show hidden (client) queries (
\set SHOW_HIDDEN
) - fix multiline behavior to mimic
psql
properly (on arrow up/down through history) - proper
PAGER
support \qecho
+\o
support- context-based completion (WIP)
- full
\if
\elif
\else
\endif
support - fix
WITH ... DELETE
queries (postgresql) - better
--help
/ man pages - translations
- fix
\command
variable interpolation/parsing (\set NAME test \echo :NAME.dat \echo :NAME:NAME
)
- formatting settings (
\pset
,\a
, etc) - all
\d*
commands frompsql
(WIP, need to finish work extracting introspection code fromxo
) \ef
and\ev
commands frompsql
(WIP, need to finish work extracting stored procs / funcs / views for all the major databases)\watch
\errverbose
(show verbose info for last error)- remaining
psql
cli parameters \j*
commands (WIP)\copy
(add support for copying between two different databases ...?)
- test suite for databases, doing minimal of
SELECT
,INSERT
,UPDATE
,DELETE
for every database
- Redis CLI
- Native Oracle
- InfluxDB
- CSV via SQLite3 vtable
- Google Spanner
- Google Sheets via SQLite3 vtable
- Charlatan
- InfluxDB IQL
- Aerospike AQL
- ArrangoDB AQL
- OrientDB SQL
- Cypher / SparQL
- Atlassian JIRA JQL