README
¶
About usql
usql is a universal command-line interface for PostgreSQL, MySQL, Oracle,
SQLite3, Microsoft SQL Server, and other databases.
Quickstart | Demos | Database Support | Connection Strings | Commands | Building | Releases
Quickstart
- Download a release for your platform
- Extract the
.zip(Windows), or.tar.bz2(OS X/Linux) file and place theusqlexecutable somewhere on your%PATH%(Windows), or your$PATH(OS X/Linux) - Connect to a database using
usql driver://user:pass@host/dbname, and execute a SQL query, or command:
$ usql postgres://booktest@localhost/booktest
error: pq: 28P01: password authentication failed for user "booktest"
Password:
You are connected with driver postgres (PostgreSQL 9.6.2)
Type "help" for help.
pg:booktest@localhost/booktest=> select * from books;
book_id | author_id | isbn | booktype | title | year | available | tags
+---------+-----------+------+----------+------------------+------+---------------------------------+-----------------+
1 | 1 | 1 | FICTION | asotenhuastonehu | 2016 | 2017-03-19T02:48:36.27928+07:00 | {}
2 | 1 | 2 | FICTION | asotenhuastonehu | 2016 | 2017-03-19T02:48:36.27928+07:00 | {cool,disastor}
3 | 1 | 3 | FICTION | asotenhuastonehu | 2001 | 2017-03-19T02:48:36.27928+07:00 | {cool}
(3 rows)
pg:booktest@localhost/booktest=> \p
select * from books;
pg:booktest@localhost/booktest=> \g
book_id | author_id | isbn | booktype | title | year | available | tags
+---------+-----------+------+----------+------------------+------+---------------------------------+-----------------+
1 | 1 | 1 | FICTION | asotenhuastonehu | 2016 | 2017-03-19T02:48:36.27928+07:00 | {}
2 | 1 | 2 | FICTION | asotenhuastonehu | 2016 | 2017-03-19T02:48:36.27928+07:00 | {cool,disastor}
3 | 1 | 3 | FICTION | asotenhuastonehu | 2001 | 2017-03-19T02:48:36.27928+07:00 | {cool}
(3 rows)
pg:booktest@localhost/booktest=> \q
Alternatively, if you already have a working Go build environment, you may install directly in the usual Go fashion:
# build and install usql with "most" SQL drivers
$ go get -u -tags most github.com/xo/usql
Interactive Demos
The following recorded interactive demos are available:
The below is a demonstration using usql with xo's booktest
simple test database, showcasing the release version v0.5.0. In the demonstration,
usql connects to a PostgreSQL database, executes some queries, with variable
interpolation, connects to a SQLite3 database file, and does some more queries,
before then connecting to a Microsoft SQL database and ending the session.
A previous demo showcasing usql's general support for, and connecting to
multiple databases is also available for viewing.
Database Support
usql aims to provide support for all Go standard library compatible SQL
drivers -- with an emphasis on supporting the drivers that sister project,
dburl, provides "out-of-the-box" URL support
for.
The databases currently supported by usql (and related build tag name) are
summarized below:
| Drivers | Build Tag | Driver Package |
|---|---|---|
| Microsoft SQL Server | mssql* | github.com/denisenkom/go-mssqldb |
| MySQL | mysql* | github.com/go-sql-driver/mysql |
| PostgreSQL | postgres* | github.com/lib/pq |
| SQLite3 | sqlite3* | github.com/mattn/go-sqlite3 |
| Oracle | oracle | gopkg.in/rana/ora.v4 |
| MySQL | mymysql | github.com/ziutek/mymysql/godrv |
| PostgreSQL | pgx | github.com/jackc/pgx/stdlib |
| Apache Avatica | avatica | github.com/Boostport/avatica |
| ClickHouse | clickhouse | github.com/kshvakov/clickhouse |
| Couchbase | couchbase | github.com/couchbase/go_n1ql |
| Cznic QL | ql | github.com/cznic/ql |
| Firebird SQL | firebird | github.com/nakagami/firebirdsql |
| Microsoft ADODB | adodb | github.com/mattn/go-adodb |
| ODBC | odbc | github.com/alexbrainman/odbc |
| Presto | presto | github.com/prestodb/presto-go-client/presto |
| SAP HANA | hdb | github.com/SAP/go-hdb/driver |
| Sybase SQL Anywhere | sqlany | github.com/a-palchikov/sqlago |
| VoltDB | voltdb | github.com/VoltDB/voltdb-client-go/voltdbclient |
| Google Spanner | spanner | github.com/xo/spanner (not yet public) |
| MOST DRIVERS | most | (all drivers listed above, excluding the drivers for Oracle and ODBC, which require third-party dependencies to build/install) |
| ALL DRIVERS | all | (all drivers listed above) |
*included by default when building
Database Connection Strings
Database connection strings, or "data source name" (aka DSNs), used with usql
have the same parsing rules as a normal URL, and have the following two forms:
protocol+transport://user:pass@host/dbname?opt1=a&opt2=b
protocol:/path/to/file
Where:
| Component | Description |
|---|---|
| protocol | driver name or alias (see below) |
| transport | "tcp", "udp", "unix" or driver name (for ODBC connections) |
| user | username |
| pass | password |
| host | host |
| dbname* | database, instance, or service name/ID to connect to |
| ?opt1=... | additional database driver options (see respective SQL driver for available options) |
* 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).
Additionally, if usql is passed a URL without a leading scheme://, usql will
attempt to locate the path on disk, and if it exists will open it accordingly.
Specifically, if usql finds a Unix Domain Socket, it will attempt to open it
using the mysql driver, or when a directory is found, usql will attempt to
open the path using the postgres driver; last, if it the path is a regular
file, usql will attempt to open the file using the sqlite3 driver.
usql recognizes the same drivers and scheme aliases from the dburl
package. Please see the dburl documentation for more in-depth information on
how DSNs are built from standard URLs. Additionally, all of the above formats
can be used in conjunction with the \c (or \connect) backslash meta command.
Example Connection Strings
The following are example connection strings (DSNs) and some additional ways to
connect to databases with usql:
# connect to a postgres database
$ usql pg://user:pass@localhost/dbname
$ usql pgsql://user:pass@localhost/dbname
$ usql postgres://user:pass@localhost:port/dbname
# connect to a mysql database
$ usql my://user:pass@localhost/dbname
$ usql mysql://user:pass@localhost:port/dbname
$ usql /var/run/mysqld/mysqld.sock
# connect to a mssql (Microsoft SQL) database
$ usql ms://user:pass@localhost/dbname
$ usql mssql://user:pass@localhost:port/dbname
# connect using Windows domain authentication to a mssql (Microsoft SQL)
# database
$ runas /user:ACME\wiley /netonly "usql mssql://host/dbname/"
# connect to a oracle database
$ usql or://user:pass@localhost/dbname
$ usql oracle://user:pass@localhost:port/dbname
# connect to a pre-existing sqlite database
$ usql dbname.sqlite3
# note: when not using a "<scheme>://" or "<scheme>:" prefix, the file must already
# exist; if it doesn't, please prefix with file:, sq:, sqlite3: or any other
# scheme alias recognized by the dburl package for sqlite databases, and sqlite
# will create a new database, like the following:
$ 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\""
Backslash (\) Commands
The following are the currently supported backslash (\ ) meta commands
available to interactive usql sessions or to included (ie, \i) scripts:
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
\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
More coming soon!
Build/Install from Source
You can build or install usql from source in the usual Go fashion:
# install usql (includes support for PosgreSQL, MySQL, SQLite3, and MS SQL)
$ go get -u github.com/xo/usql
Please note that default calls to go get, go build, or go install will
only include drivers for PostgreSQL, MySQL, SQLite3 and Microsoft SQL Server.
If you need additional support for a database driver (or wish to disable a
specific driver), you may use additional build tags with go get, go build,
or go install. Please refer to the table in the Database Support section
above for the names of the various build tags.
Note that for every build tag <name>, there is an additional tag no_<name>,
that disables the respective driver(s). Additionally, there are the build tags
most and all, that include most, and all SQL drivers, respectively.
As such, you can easily (and quickly) recompile usql by combining any
number of build tags to enable/disable specific drivers as needed:
# install all drivers
$ go get -u -tags all github.com/xo/usql
# install with "most" drivers (same as "all" but excludes oracle/odbc or any other CGO-based drivers)
$ 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
# install all drivers but exclude avatica, and couchbase drivers
$ go get -u -tags 'all no_avatica no_couchbase'
For reference, usql releases are
built with the most tag, and with additional SQLite3 specific build tags.
Using usql as a library
Significant effort has gone into making usql's codebase modular, and reusable
by other developers wishing to leverage the existing features of usql. As
such, if you would like to build your own SQL command-line interface (e.g, for
use with a SQL-like project, or otherwise as an "official" client), it is
relatively straight-forward and easy to do so.
Please refer to the main command-line entry point to see how usql
uses its constituent packages to create a interactive command-line
handler/interpreter. Additionally, usql's code is fairly well-documented --
please refer to the GoDoc listing to
see how it's all put together.
Compatibility and TODO
The goal of the usql project is to eventually provide a drop-in replacement
for the amazing PostgreSQL's psql command -- including all bells/whistles --
but with the added benefit of working with practically any database.
This is a continuing, and on-going effort -- and a substantial, good-faith
attempt has been made to provide support for the most frequently used
aspects/features of psql.
Note, however, that usql is not close to a 100% replacement/drop-in, and
not-yet fully compatible with psql. CAVEAT USER.
TODO
Eventually, usql developers hope to leverage the power of Go and have plans
for more features than the base psql command provides. Currently, the list of
planned / in progress work:
- Fix multiline behavior to mimic psql properly
- Title bar support
- Google Spanner
- PAGER
- \qecho + \o support
- fix table output / formatting
- add support for managing multiple database connections simultaneously
(@conn syntax, and a ~/.usqlconnections file, and ~/.usqlconfig) (maybe not
needed, if variable support works "as expected"?)
maybe execute using something like \g @:name or :@name ? or \g -name ?
\c -name pg://user@localhost/dbname
by using a -name syntax, can be the same as passed cli parameters for the provided dsn -- could even be the same form, like:
usql -N myconn pg://booktest@localhostorusql --name myconn pg://then, working with \copy, could do:\copy -N myconn <source> to -N myconn2 <dest>syntax something like:source := <table> | (<select_stmt>) dest := <table> table := <identifier> (<column_list>) - SQL completion (WIP)
- syntax highlighting (WIP)
- \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 ... ?)
- better --help support/output cli, man pages
psql compatibility- the \j* commands (WIP)
- \watch
- \errverbose
- formatting settings (\pset, \a, etc)
- all \d* commands from
psql(WIP, need to finish work extracting introspection code fromxo) - remaining
psqlcli parameters
- correct operation of interweaved -f/-c commands, ie: -f 1 -c 1 -c 2 -f 2 -f 3 -c 3 runs in the specified order
- test suite for databases, doing a minimal set of SELECT, INSERT, UPDATE, DELETE
- Cassandra
- InfluxDB
- CSV via SQLite3 vtable
- Google Sheets via SQLite3 vtable
- Atlassian JIRA JQL (why not? lol)
Need to write scripts for packaging and build binaries for:
- Debian/Ubuntu (.deb)
- MacOS X (.pkg)
- Windows (.msi)
- CentOS/RHEL (.rpm)
Additional:
- Submit upstream to Debian unstable (WIP)
Related Projects
Documentation
¶
There is no documentation for this package.
Source Files
Directories