xo / usql, Hacker News

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

Installing Building Using Database Support Features and Compatibility Releases

Overview

USQLprovides a simple way to work withSQL and NoSQL databasesvia a command-line inspired by PostgreSQL’sPSQL.USQLsupports most of the corePSQLfeatures, such as (variables) ,Backticks, andcommandsand has additional features thatPSQLdoes not, such assyntax highlighting, context-based completion, andmultiple database support.

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

Installing

USQL (can be installed) via Release,via Homebrew,via Scooporvia Go:

Installing via Release

Download a release for your platform
Extract theUSQLorusql.exefile from the.tar.bz2or.zipfile
Move the extracted executable to somewhere on your$ PATH(Linux / macOS) or% PATH%(Windows)

Installing via Homebrew (macOS)

USQL (is available in theXO / XOtap, and can be installed in the usual way with theBREWcommand:

#  (add tap)  $ brew tap xo / xo#install usql with "most" drivers$ brew install usql

Additional support forOracle and ODBC databasescan 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’sinstantclient-sdkformula. Any otherinstantclient-sdkformulae or older versions of the Oracle Instant Client SDKshould be uninstalledprior 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 )

USQLcan be installed usingScoop:

#install scoop if not already installediex (new-objectnet.webclient) .downloadstring (''https://get.scoop.sh'')  scoop install usql

Installing via Go

USQLcan 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 withbuild 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 theirdatabase driver. Additionally, themostandallbuild 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, there is also the (no _) 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 buildsare built with the`most`build tag. AdditionalSQLite3 build tagsare also specified for releases.

Embedding

An effort has been made to keep (USQL) ‘s packages modular, and reusable by other developers wishing to leverage theUSQLcode 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 coreUSQLsource tree.

Please refer tomain.goto see howUSQLputs together its packages.USQL‘s code is also well-documented – please refer to theGoDoc listingfor an overview of the various packages and APIs.

Database Support

USQLworks with all Go standard library compatible SQL drivers supported bygithub .com / XO / Dburl.

The list of drivers thatUSQLwas built with can be displayed using the driverscommand:

$  (CD)   ($ GOPATH)  / src / github.com / xo / usql $ go build -tags'no_most postgres mysql cql sqlite3''&&./usql Type"help"forhelp.  (not connected)=> drivers Available Drivers:   cockroachdb (postgres) [cr, cdb, crdb, cockroach]   memsql (mysql) [me]   mssql   mysql [my, maria, aurora, mariadb, percona]   postgres   redshift (postgres)   sqlite3   tidb (mysql) [ti]   vitess (mysql) [vt] (not connected)=>

The above shows that (USQL) was built with only thepostgres,mysql, (CQL) , andsqlite3Drivers. The output above reflect information about the drivers availabl e toUSQL, 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 connectcommandwhen connecting to a database.

Supported Database Schemes and Aliases

The following is a table of all drivers, schemes, and aliases thatUSQLsupports:

(Database) scheme / driver) (Protocol Aliases [real driver])

Microsoft SQL Server (mssql)	ms, sqlserver
MySQL (mysql)	my, mariadb, maria, percona, aurora
Oracle Database (goracle)	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
VoltDB (voltdb)	vo, volt, vdb

Go Drivers and Build Tags

The following are theGo SQL drivers (that) USQLsupports, and the associated Go build tag:

Using

Afterinstalling,USQLcan be used similarly to the following:

#connect to a postgres database$ usql postgres: // booktest @ localhost / booktest#connect to an oracle database$ usql oracle: // user: [email protected]/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 interfaceforSQL databases  Usage:   USQL [OPTIONS] ... [DSN]  Arguments:   DSN database url  Options:   -c, --command=COMMAND ... run only singlecommand(SQL or internal) andexit  -f, --file=FILE ... execute commands from file and  (exit)    -w, --no-password never promptforPassword   -X, --no-rcDOnotreadstart 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 ...                                setvariable NAME to VALUE   -P, --pset=VAR [=ARG] ...setprinting option VAR to ARG (see pset command)   -A, --no-align unaligned table output mode   -F, --field-separator=TEXT field separatorforunaligned output (default,"|")   -H, --html HTML table output mode   -R, --record-separator=TEXT record separatorforunaligned output (default, n)   -t, --tuples-only print rows only   -T, --table-attr=TEXTsetHTML table tag attributes (eg, width, border)   -x, --expanded turn on expanded table output   -z, --field-separator-zerosetfield separatorforunaligned output to zero byte   -0, --record-separator-zerosetrecord separatorforunaligned output to zero byte   -J, --json JSON output mode   -C, --csv CSV output mode   -V, --version display version andexit

Connecting to Databases

USQL (opens a database connection byparsing a URLand passing the resulting connection string toa database driver. Database connection strings (aka "data source name" or DSNs) have the same parsing rules as URLs, and can be passed toUSQLvia command-line, or to the connector ccommands.

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,/ dbnamecan be/ instance / dbname, where/ instanceis optional. For Oracle Database,/ dbnameis of the form/ service / dbnamewhere/ serviceis the service name or SID, and/ dbnameis optional. Please see below for examples.

Driver Aliases

USQLsupports the same driver names and aliases from thedburlpackage. Most databases have at least one or more alias – please refer to thedburldocumentationfor 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,PGforpostgres,myformysql, (ms) formssql,or (for) oracle, or (sq) forsqlite3.

relevant database driver’s documentationfor available options.

Paths on Disk

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

If the specified path is a Unix Domain Socket,USQLwill attempt to open it using the MySQL driver. If the path is a directory,USQLwill attempt to open it using the PostgreSQL driver. If the path is a regular file,USQLwill 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.USQLwill 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 forthe database driveryou are connecting with for more information.

Connection Examples

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

#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 ": // "or#": "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.   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

Executing Queries and Commands

The interactive intrepreter reads queries andmeta) commands, sending the query to the connected database:

$ usql sqlite: //example.sqlite3 Connected with driver sqlite3 (SQLite3 3. 17 .0) Type"help"forhelp.  sq: example.sqlite3=>create tabletest(test_id int, name string);CREATE TABLE sq: example.sqlite3=>insert intotest(test_id, name) values (1,''hello');INSERT 1 sq: example.sqlite3=>select*fromtest;  test_id|name   ---------   -------           1|hello (1 rows)  sq: example.sqlite3=>select*fromtestsq: example.sqlite3 -> pselect*fromtestsq: example.sqlite3 -> g  test_id|name   ---------   -------           1|hello (1 rows)  sq: example.sqlite3=> cpostgres: // booktest @ localhost error: pq:  (P) : password authentication failedforuser""  (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 bebacktick’d.

Backslash Commands

Currently available commands:

$ usql Type"help"forhelp.  (not connected)=>?General    qquit 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 resultsinusql variables  Help   ?[commands] showhelpon backslash commands   ?options showhelpon usql command-line options   ?variables showhelpon special variables  Query Buffer    e[FILE] [LINE] edit the query buffer (or file) with external editor    pshow the contents of the query buffer    raw show the raw (non-interpolated) contents of the query buffer    rreset (clear) the query buffer    wFILE write query buffer to file  Input / Output    echo [STRING] write string to standard output    iFILE execute commands from file    ir FILE as i, but relative to location of current script  Formatting    pset [NAME [VALUE]]settable output option    atoggle between unaligned and aligned output mode    C[STRING]settable title, or  (unset)   (if)  none    f[STRING] show orsetfield separatorforunaligned query output    Htoggle HTML output mode    t[on|off] show only rows    T[STRING]setHTMLtable>tag attributes, orunsetifnone    x[on|off|auto] toggle expanded output  Transaction    begin begin a transaction    commit commit current transaction    rollback rollback (abort) current transaction  Connection    cURL connect to database with url    cDRIVER PARAMS ... connect to database with SQL driver and parameters    Zclose database connection    password [USERNAME] change the passwordfora user    conninfo display information about the current database connection  Operating System    cd [DIR] change the current working directory    sEtenv NAME [VALUE]setor  (unset)  environment variable   ![COMMAND] executecommandinshell or start interactive shell  Variables    prompt [-TYPE] [PROMPT]prompt user tosetvariable    set [NAME [VALUE]]set  (internal variable, or list all  (if)  No parameters    unset NAMEunset(delete) internal variable

Features and Compatibility

The (USQL) project’s goal is to support all standardPSQLcommands and features. Pull Requests are always appreciated!

Variables and Interpolation

USQLsupports client-side interpolation of variables that can be setand 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 setvariable,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 -> pselect*from authors where""  (name)  "=' (bar)  'pg: booktest @ localhost -> rawselect*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 willNOTbe Interpolated:

pg: booktest @ localhost=>select': FOO';  ?column? ----------     : FOO (1 rows)  pg: booktest @ localhost=> pselect': FOO';pg: booktest @ localhost=>

Backtick’d parameters

(Meta)) commandssupport 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’sSHELL, exactly as written, and can be combined with set:

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

Passwords

USQLsupports reading passwords for databases from a.usqlpassfile contained in the user’s (HOME) directory at startup:

$ cat$ HOME/ .usqlpass#format is :#protocol: host: port: dbname: user: passPostgres:*:*:*: booktest: booktest $ usql pg: // Connected with driver postgres (PostgreSQL 9.6.9) Type"help"forhelp.  pg: booktest @=>

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

Runtime Configuration (RC) File

USQLsupports executing a.usqlrccontained 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:  (WIB)  Type"help"forhelp.  (not connected)=> set SYNTAX_HL_STYLE='paraiso-dark''(not connected)=>

The.USQLCfile is read byUSQLat 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- Xor- no-rcon the command-line:

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

$exportUSQL_SHOW_HOST_INFORMATION=false $ usql pg: // booktest @ localhost Type"help"forhelp.  pg: booktest @=>

SHOW_HOST_INFORMATION (is a standard)USQL (variable) , and can be setor unset. Additionally, it can be passed via the command-line using- Vor- set:

$ usql --set SHOW_HOST_INFORMATION=false pg: // Type"help"forhelp.  pg: booktest @=> set SHOW_HOST_INFORMATION  (true)  pg: booktest @=> connect pg: // Connected with driver postgres (PostgreSQL 9.6.9) pg: booktest @=>

Syntax Highlighting

Interactive queries will be syntax highlighted by default, using Chroma. There are a number ofvariablesthat 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 thatsupport formatting. By default,USQLformats time / date columns as (RFC) Nano, and can be set using the(TIME_FORMAT) ****************************************************** (variable) :

$ usql pg: // Connected with driver postgres (PostgreSQL 9.6.9) Type"help"forhelp.  pg: booktest @=> set TIME_FORMAT=' (RFC) *********************************************************************************************************************************************************************** (Nano)  ''pg: booktest @=>select(now ();                now   ----------------------------------     2018 - 06 -  (T) : 24: 12. 481923   07: 00 (1 rows)  pg: booktest @=> set TIME_FORMAT Kitchen pg: booktest @=> g   now   --------     3: 24 AM (1 rows)

AnyGo supported time formator const name (for example,Kitchen, in the above) can be used for (TIME_FORMAT) .

TODO

USQLaims 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) withPSQL, takes general development priority.