Go
universal command-line interface for SQL databases

usql

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 | Commands | Database Support | Variables | Releases

Overview

usql provides a simple way of working with SQL and NoSQL databases via a command-line inspired by PostgreSQL's psql. usql has a few additional features that psql does not, such as syntax highlighting and context-based completion.

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

Installing

usql can be installed by via Release, via Homebrew, 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 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 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 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

Building

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

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

Using as a Package

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 build 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 to see the various APIs available.

Database Support

usql works with all Go standard library compatible SQL drivers supported by github.com/xo/dburl.

The databases supported, the respective build tag, and the driver used by usql are:

Using

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

Connecting to a Database

usql opens a database connection by parsing a URL and passing the resulting connection string to the 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.

usql 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:

^* 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).

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.

Connection String Examples

The following are example connection strings and additional ways to connect to databases with 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

# 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\""

Executing Queries and Commands

usql provides a command intrepreter that intreprets meta (\) commands and sends queries to the 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

usql recognizes backslash (\) commands similar to psql. 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

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 [TEXT] NAME   prompt user to set internal 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. Pull Requests would be greatly 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)

Interpolation has three forms: :NAME, :'NAME', and :"NAME".

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=>

Backtick'd parameters

As with psql, meta (\) commands supports backticks on parameters and quotation marks:

(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=>

Related Projects

• dburl - a Go package providing a standard, URL style mechanism for parsing and opening database connection URLs
• xo - a command-line tool to generate Go code from a database schema

TODO

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.

General

1. support more prompt configuration, colored prompt by default
2. add window title / status output
3. change drivers.Convert* to drivers.Marshal style interfaces
4. allow configuration for JSON encoding/decoding output
5. return single 'driver' type handling marshaling / scanning of types / columns
6. implement a table writer that follows "optional func" parameter style, is streaming / handles marshalers, can handle the different configuration options for \pset
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 support/output cli, man pages
19. translations
20. \encoding and environment/command line options to set encoding of input (to convert to UTF-8 before feeding to SQL driver) (how important is this ... ?)

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 ...?)

Low priority compatibity fixes:

1. correct operation of interweaved -f/-c commands, ie: usql -f 1 -c 1 -c 2 -f 2 -f 3 -c 3 runs in the specified order

Testing

1. test suite for databases, doing minimal of SELECT, INSERT, UPDATE, DELETE for every database

Future Database Support

1. Native Oracle
2. InfluxDB
3. CSV via SQLite3 vtable
4. Google Spanner
5. Google Sheets via SQLite3 vtable
6. Charlatan
7. InfluxDB IQL
8. Aerospike AQL
9. ArrangoDB AQL
10. OrientDB SQL
11. Cypher / SparQL
12. Atlassian JIRA JQL