Popularity
9.4
Stable
Activity
9.2
Growing
6,788
115
244

Programming language: Go
License: MIT License
Tags: Utilities    
Latest version: v0.7.8

usql alternatives and similar packages

Based on the "Utilities" category.
Alternatively, view usql alternatives based on common mentions on social networks and blogs.

Do you think we are missing an alternative of usql or a related project?

Add another 'Utilities' Package

README

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

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

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.

Unit Tests Go Reference Discord Discussion

Installing

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)

macOS Notes

The recommended installation method on macOS is via brew (see below). If the following or similar error is encountered when attempting to run usql:

$ usql
dyld: Library not loaded: /usr/local/opt/icu4c/lib/libicuuc.68.dylib
  Referenced from: /Users/user/.local/bin/usql
  Reason: image not found
Abort trap: 6

Then the ICU lib needs to be installed. This can be accomplished using brew:

$ brew install icu4c

Installing via Homebrew (macOS and Linux)

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

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

Additional support for ODBC databases can be installed by passing --with-odbc option during install:

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

Installing via Scoop (Windows)

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

Installing via Go

usql can be installed in the usual Go fashion:

# install usql with basic database support
# includes PostgreSQL, Oracle Database, MySQL, MS SQL, and SQLite3 drivers
$ GO111MODULE=on go get github.com/xo/usql

Support for additional databases can be specified with build tags:

# install usql with most drivers (excludes unsupported drivers)
$ GO111MODULE=on go get -tags most github.com/xo/usql

# install usql with all drivers
$ GO111MODULE=on go get -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
$ GO111MODULE=on go get -tags all github.com/xo/usql

# install with most drivers
$ GO111MODULE=on go get -tags most github.com/xo/usql

# install with base drivers and additional support for Oracle Database and ODBC
$ GO111MODULE=on go get -tags 'godror odbc' github.com/xo/usql

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

# install all drivers excluding avatica and couchbase
$ GO111MODULE=on go get -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](build-release.sh) are also specified for releases.

Embedding

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](main.go) to see how usql puts together its packages. usql's code is also well-documented -- please refer to the Go reference for an overview of the various packages and APIs.

Database Support

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:

$ cd $GOPATH/src/github.com/xo/usql
$ export GO111MODULE=on
# build excluding the base drivers, and including cassandra and moderncsqlite
$ go build -tags 'no_postgres no_oracle no_sqlserver no_sqlite3 cassandra moderncsqlite'
# show built driver support
$ ./usql -c '\drivers'
Available Drivers:
  cql [ca, scy, scylla, datastax, cassandra]
  memsql (mysql) [me]
  moderncsqlite [mq, sq, file, sqlite, sqlite3, modernsqlite]
  mysql [my, maria, aurora, mariadb, percona]
  tidb (mysql) [ti]
  vitess (mysql) [vt]

The above shows that usql was built with only the mysql, cassandra (ie, cql), and moderncsqlite drivers. The output above reflects information about the drivers available to usql, specifically the internal driver name, its primary URL scheme, the driver's available scheme aliases (shown in [...]), and the real/underlying driver (shown in (...)) for wire compatible drivers.

Supported Database Schemes and Aliases

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

<!-- DRIVER DETAILS START --> | Database | Scheme / Tag | Scheme Aliases | Driver Package / Notes | |----------------------|-----------------|-------------------------------------------------|-----------------------------------------------------------------| | Microsoft SQL Server | sqlserver | ms, mssql | github.com/denisenkom/go-mssqldb | | MySQL | mysql | my, maria, aurora, mariadb, percona | github.com/go-sql-driver/mysql | | Oracle Database | oracle | or, ora, oci, oci8, odpi, odpi-c | github.com/sijms/go-ora/v2 | | PostgreSQL | postgres | pg, pgsql, postgresql | github.com/lib/pq | | SQLite3 | sqlite3 | sq, file, sqlite | github.com/mattn/go-sqlite3 | | | | | | | Alibaba MaxCompute | maxcompute | mc | sqlflow.org/gomaxcompute | | Apache Avatica | avatica | av, phoenix | github.com/apache/calcite-avatica-go/v5 | | Apache H2 | h2 | | github.com/jmrobles/h2go | | Apache Ignite | ignite | ig, gridgain | github.com/amsokol/ignite-go-client/sql | | AWS Athena | athena | s3, aws | github.com/uber/athenadriver/go | | Cassandra | cassandra | ca, scy, scylla, datastax, cql | github.com/MichaelS11/go-cql-driver | | ClickHouse | clickhouse | ch | github.com/ClickHouse/clickhouse-go | | Couchbase | couchbase | n1, n1ql | github.com/couchbase/go_n1ql | | CSVQ | csvq | cs, csv, tsv, json | github.com/mithrandie/csvq-driver | | Cznic QL | ql | cznic, cznicql | modernc.org/ql | | Exasol | exasol | ex, exa | github.com/exasol/exasol-driver-go | | Firebird | firebird | fb, firebirdsql | github.com/nakagami/firebirdsql | | Genji | genji | gj | github.com/genjidb/genji/driver | | Google BigQuery | bigquery | bq | gorm.io/driver/bigquery/driver | | Google Spanner | spanner | sp | github.com/cloudspannerecosystem/go-sql-spanner | | Microsoft ADODB | adodb | ad, ado | github.com/mattn/go-adodb | | ModernC SQLite3 | moderncsqlite | mq, modernsqlite | modernc.org/sqlite | | MySQL MyMySQL | mymysql | zm, mymy | github.com/ziutek/mymysql/godrv | | Netezza | netezza | nz, nzgo | github.com/IBM/nzgo | | PostgreSQL PGX | pgx | px | github.com/jackc/pgx/v4/stdlib | | Presto | presto | pr, prs, prestos, prestodb, prestodbs | github.com/prestodb/presto-go-client/presto | | SAP ASE | sapase | ax, ase, tds | github.com/thda/tds | | SAP HANA | saphana | sa, sap, hana, hdb | github.com/SAP/go-hdb/driver | | Trino | trino | tr, trs, trinos | github.com/trinodb/trino-go-client/trino | | Vertica | vertica | ve | github.com/vertica/vertica-sql-go | | VoltDB | voltdb | vo, vdb, volt | github.com/VoltDB/voltdb-client-go/voltdbclient | | | | | | | Apache Hive | hive | hi | sqlflow.org/gohive | | Apache Impala | impala | im | github.com/bippio/go-impala | | Azure CosmosDB | cosmos | cm | github.com/btnguyen2k/gocosmos | | GO DRiver for ORacle | godror | gr | github.com/godror/godror | | ODBC | odbc | od | github.com/alexbrainman/odbc | | Snowflake | snowflake | sf | github.com/snowflakedb/gosnowflake | | | | | | | Amazon Redshift | postgres | rs, redshift | github.com/lib/pq | | CockroachDB | postgres | cr, cdb, crdb, cockroach, cockroachdb | github.com/lib/pq | | OLE ODBC | adodb | oo, ole, oleodbc | github.com/mattn/go-adodb | | SingleStore MemSQL | mysql | me, memsql | github.com/go-sql-driver/mysql | | TiDB | mysql | ti, tidb | github.com/go-sql-driver/mysql | | Vitess Database | mysql | vt, vitess | github.com/go-sql-driver/mysql | | | | | | | NO DRIVERS | no_base | | no base drivers (useful for development) | | MOST DRIVERS | most | | all stable drivers | | ALL DRIVERS | all | | all drivers | | NO <TAG> | no_<tag> | | exclude driver with <tag> |

<!-- DRIVER DETAILS END -->

†Requires CGO ‡Wire compatible (see respective driver)

Any of the protocol schemes/aliases shown above can be used in conjunction when connecting to a database via the command-line or with the \connect command:

# connect to a vitess database:
$ usql vt://user:[email protected]:3306/mydatabase

$ usql
(not connected)=> \c vitess://user:[email protected]:3306/mydatabase

See the section below on connecting to databases for further details building DSNs/URLs for use with usql.

Using

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

# connect to a postgres database
$ usql postgres://[email protected]/booktest

# connect to an oracle database
$ usql oracle://user:[email protected]/oracle.sid

# connect to a postgres database and run the commands contained in 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

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)
  -F, --field-separator=FIELD-SEPARATOR ...
                               field separator for unaligned output (default, "|")
  -R, --record-separator=RECORD-SEPARATOR ...
                               record separator for unaligned output (default, \n)
  -T, --table-attr=TABLE-ATTR ...
                               set HTML table tag attributes (e.g., width, border)
  -A, --no-align               unaligned table output mode
  -H, --html                   HTML table output mode
  -t, --tuples-only            print rows only
  -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
  -G, --vertical               vertical 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:

   driver+transport://user:[email protected]/dbname?opt1=a&opt2=b
   driver:/path/to/file
   /path/to/file

Where the above are:

Component Description
driver driver scheme name or scheme 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

±Some databases, such as Microsoft SQL Server, or Oracle Database support a path component (ie, /dbname) in the form of /instance/dbname, where /instance is the optional service identifier (aka "SID") or database instance

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 sqlserver (formerly known as 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:[email protected]/dbname
$ usql pgsql://user:[email protected]/dbname
$ usql postgres://user:[email protected]:port/dbname
$ usql pg://
$ usql /var/run/postgresql
$ usql pg://user:[email protected]/dbname?sslmode=disable # Connect without SSL

# connect to a mysql database
$ usql my://user:[email protected]/dbname
$ usql mysql://user:[email protected]:port/dbname
$ usql my://
$ usql /var/run/mysqld/mysqld.sock

# connect to a sqlserver database
$ usql sqlserver://user:[email protected]/instancename/dbname
$ usql ms://user:[email protected]/dbname
$ usql ms://user:[email protected]/instancename/dbname
$ usql mssql://user:[email protected]:port/dbname
$ usql ms://

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

# connect to a oracle database
$ usql or://user:[email protected]/sid
$ usql oracle://user:[email protected]:port/sid
$ usql or://

# connect to a cassandra database
$ usql ca://user:[email protected]/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:[email protected]/dbname
$ usql odbc+PostgreSQL+ANSI://user:[email protected]/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);
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://[email protected]
error: pq: 28P01: password authentication failed for user "booktest"
Enter password:
Connected with driver postgres (PostgreSQL 9.6.6)
pg:[email protected]=> select * from authors;
  author_id |      name
+-----------+----------------+
          1 | Unknown Master
          2 | blah
          3 | aoeu
(3 rows)

pg:[email protected]=>

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)=> \?
General
  \q                                   quit usql
  \copyright                           show usql usage and distribution terms
  \drivers                             display information about available database drivers

Query Execute
  \g [(OPTIONS)] [FILE] or ;           execute query (and send results to file or |pipe)
  \crosstabview [(OPTIONS)] [COLUMNS]  execute query and display results in crosstab
  \G [(OPTIONS)] [FILE]                as \g, but forces vertical output mode
  \gexec                               execute query and execute each value of the result
  \gset [PREFIX]                       execute query and store results in usql variables
  \gx [(OPTIONS)] [FILE]               as \g, but forces expanded output mode
  \watch [(OPTIONS)] [DURATION]        execute query every specified interval

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

Help
  \? [commands]                        show help on backslash commands
  \? options                           show help on usql command-line options
  \? variables                         show help on special variables

Input/Output
  \echo [-n] [STRING]                  write string to standard output (-n for no newline)
  \qecho [-n] [STRING]                 write string to \o output stream (-n for no newline)
  \warn [-n] [STRING]                  write string to standard error (-n for no newline)
  \o [FILE]                            send all query results to file or |pipe
  \i FILE                              execute commands from file
  \ir FILE                             as \i, but relative to location of current script

Informational
  \d[S+] [NAME]                        list tables, views, and sequences or describe table, view, sequence, or index
  \da[S+] [PATTERN]                    list aggregates
  \df[S+] [PATTERN]                    list functions
  \di[S+] [PATTERN]                    list indexes
  \dm[S+] [PATTERN]                    list materialized views
  \dn[S+] [PATTERN]                    list schemas
  \ds[S+] [PATTERN]                    list sequences
  \dt[S+] [PATTERN]                    list tables
  \dv[S+] [PATTERN]                    list views
  \l[+]                                list databases

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 [STRING]                          set HTML <table> tag attributes, or unset if none
  \t [on|off]                          show only rows
  \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
  \timing [on|off]                     toggle timing of commands

Variables
  \prompt [-TYPE] <VAR> [PROMPT]       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:[email protected]=> \set FOO bar
pg:[email protected]=> 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:[email protected]=> \set TBLNAME authors
pg:[email protected]=> \set COLNAME name
pg:[email protected]=> \set FOO bar
pg:[email protected]=> select * from :TBLNAME where :"COLNAME" = :'FOO'
pg:[email protected]> \p
select * from authors where "name" = 'bar'
pg:[email protected]> \raw
select * from :TBLNAME where :"COLNAME" = :'FOO'
pg:[email protected]> \g
  author_id | name
+-----------+------+
          7 | bar
(1 rows)

pg:[email protected]=>

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

pg:[email protected]=> select ':FOO';
  ?column?
+----------+
  :FOO
(1 rows)

pg:[email protected]=> \p
select ':FOO';
pg:[email protected]=>

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:[email protected]=> \set MYVAR `date`
pg:[email protected]=> \set
MYVAR = 'Wed Jun 13 12:17:11 WIB 2018'
pg:[email protected]=> \echo :MYVAR
Wed Jun 13 12:17:11 WIB 2018
pg:[email protected]=>

Passwords

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:[email protected]=>

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
\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://

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:

$ export USQL_SHOW_HOST_INFORMATION=false
$ usql pg://[email protected]
Type "help" for help.

pg:[email protected]=>

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:[email protected]=> \set SHOW_HOST_INFORMATION true
pg:[email protected]=> \connect pg://
Connected with driver postgres (PostgreSQL 9.6.9)
pg:[email protected]=>

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 \pset time <FORMAT>:

$ usql pg://
Connected with driver postgres (PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1))
Type "help" for help.

pg:[email protected]=> \pset
time                     RFC3339Nano
pg:[email protected]=> select now();
             now
-----------------------------
 2021-05-01T22:21:44.710385Z
(1 row)

pg:[email protected]=> \pset time Kitchen
Time display is "Kitchen" ("3:04PM").
pg:[email protected]=> select now();
   now
---------
 10:22PM
(1 row)

pg:[email protected]=>

Any Go supported time format or the standard Go const name (for example, Kitchen, in the above).

Constants
Constant Name Value
ANSIC Mon Jan _2 15:04:05 2006
UnixDate Mon Jan _2 15:04:05 MST 2006
RubyDate Mon Jan 02 15:04:05 -0700 2006
RFC822 02 Jan 06 15:04 MST
RFC822Z 02 Jan 06 15:04 -0700
RFC850 Monday, 02-Jan-06 15:04:05 MST
RFC1123 Mon, 02 Jan 2006 15:04:05 MST
RFC1123Z Mon, 02 Jan 2006 15:04:05 -0700
RFC3339 2006-01-02T15:04:05Z07:00
RFC3339Nano 2006-01-02T15:04:05.999999999Z07:00
Kitchen 3:04PM
Stamp Jan _2 15:04:05
StampMilli Jan _2 15:04:05.000
StampMicro Jan _2 15:04:05.000000
StampNano Jan _2 15:04:05.000000000

Copy

usql implements the \copy command that reads data from a database connection and writes it into another one. It requires 4 parameters:

  • source connection string
  • destination connection string
  • source query
  • destination table name, optionally with columns

Connection strings support same syntax as in \connect. Source query needs to be quoted. Source query must select same number of columns and in same order as they're defined in the destination table, unless they're specified for the destination, as table_name(column1, column2, ...). Quote the whole expression, if it contains spaces. \copy does not attempt to perform any data type conversion. Use CAST in the source query to ensure data types compatible with destination table. Some drivers may have limited data type support, and they might not work at all when combined with other limited drivers.

Unlike psql, \copy in usql cannot read data directly from files. Drivers like csvq can help with this, since they support reading CSV and JSON files.

$ cat books.csv
book_id,author_id,isbn,title,year,available,tags
3,1,3,one,2018,"2018-06-01 00:00:00",{}
4,2,4,two,2019,"2019-06-01 00:00:00",{}

$ usql -c "\copy csvq://. sqlite3://test.db 'select * from books' 'books'"
Copied 2 rows

Note that it might be a better idea to use tools dedicated to the destination database to load data in a robust way.

\copy reads data from plain SELECT queries. Most drivers that have \copy enabled use INSERT statements, except for PostgreSQL ones, which use COPY TO. Because data needs to be downloaded from one database and uploaded into another, don't expect same performance as in psql. For loading large amount of data efficiently, use tools native to the destination database.

You can use \copy with variables. Better yet, put those \set commands in your runtime configuration file at $HOME/.usqlrc and passwords at $HOME/.usqlpass.

$ usql
Type "help" for help.

(not connected)=> \set pglocal postgres://[email protected]:49153?sslmode=disable
(not connected)=> \set oralocal godror://[email protected]:1521/orasid
(not connected)=> \copy :pglocal :oralocal 'select staff_id, first_name from staff' 'staff(staff_id, first_name)'

Contributing

usql is currently a WIP, and is aiming towards a 1.0 release soon. Well-written PRs are always welcome -- and there is a clear backlog of issues marked help wanted on the GitHub issue tracker!

Please pick up an issue today, and submit a PR tomorrow!

For more technical details, see CONTRIBUTING.md.

Related Projects

  • 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