Universal command-line interface for SQL databases

Overview

usql Build Status

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

Overview

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.

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 drivers requiring CGO)
$ GO111MODULE=on go get -tags most github.com/xo/usql

# install usql with all drivers (includes drivers requiring CGO, namely Oracle and ODBC 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 (same as all but excludes Oracle/ODBC)
$ GO111MODULE=on go get -tags most github.com/xo/usql

# install with base drivers and Oracle Database (OCI)/ODBC support
$ GO111MODULE=on go get -tags 'godror 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
$ 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 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 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.

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
$ go build -tags 'no_most postgres mysql cql sqlite3' && ./usql
Type "help" for help.

(not connected)=> \drivers
Available Drivers:
  cockroachdb (postgres) [cr, cdb, crdb, cockroach]
  memsql (mysql) [me]
  sqlserver [ms, mssql]
  mysql [my, maria, aurora, mariadb, percona]
  postgres [pg, pgsql, postgresql]
  redshift (postgres) [rs]
  sqlite3 [sq, file, sqlite]
  tidb (mysql) [ti]
  vitess (mysql) [vt]
(not connected)=>

The above shows that usql was built with only the postgres, mysql, cql, and sqlite3 drivers. The output above reflects information about the drivers available to usql, 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 \connect command when connecting to a database.

Supported Database Schemes and Aliases

The following is a table of all drivers, schemes, and aliases that usql supports:

Database (scheme/driver) Protocol Aliases [real driver]
MySQL (mysql) my, mariadb, maria, percona, aurora
Oracle Database (oracle) or, ora, oracle, oci, oci8, odpi, odpi-c
PostgreSQL (postgres) pg, postgresql, pgsql
SQLite3 (sqlite3) sq, sqlite, file
Microsoft SQL Server (sqlserver ) ms, mssql
Amazon Redshift (redshift) rs [postgres]
CockroachDB (cockroachdb) cr, cockroach, crdb, cdb [postgres]
MemSQL (memsql) me [mysql]
TiDB (tidb) ti [mysql]
Vitess (vitess) vt [mysql]
MySQL (mymysql) zm, mymy
Oracle Database (godror) gr
PostgreSQL (pgx) px
Alibaba MaxCompute (maxcompute) mc
Apache Avatica (avatica) av, phoenix
Apache H2 (h2) h2
Apache Hive (hive) hi
Apache Ignite (ignite) ig, gridgain
Apache Impala (impala) im
AWS Athena (athena) s3
Azure Cosmos (cosmos) cm
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
Genji (genji) gj
Google BigQuery (bigquery) bq
Google Spanner (spanner) sp
Microsoft ADODB (adodb) ad, ado
ModernC SQLite (moderncsqlite) mq, modernsqlite
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
Trino (trino) tr, trino, trinos, trs
Vertica (vertica) ve
VoltDB (voltdb) vo, volt, vdb

Go Drivers and Build Tags

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

Driver Build Tag Driver Used
MySQL mysql github.com/go-sql-driver/mysql
Oracle Database oracle github.com/sijms/go-ora
PostgreSQL postgres github.com/lib/pq
SQLite3 sqlite3 github.com/mattn/go-sqlite3
Microsoft SQL Server sqlserver github.com/denisenkom/go-mssqldb
MySQL mymysql github.com/ziutek/mymysql/godrv
Oracle Database godror github.com/godror/godror
PostgreSQL pgx github.com/jackc/pgx/stdlib
Alibaba MaxCompute maxcompute sqlflow.org/gomaxcompute
Apache Avatica avatica github.com/Boostport/avatica
Apache H2 h2 github.com/jmrobles/h2go
Apache Hive hive sqlflow.org/gohive
Apache Ignite ignite github.com/amsokol/ignite-go-client
Apache Impala impala github.com/bippio/go-impala
AWS Athena athena github.com/uber/athenadriver/go
Azure Cosmos cosmos github.com/btnguyen2k/gocosmos
Cassandra cassandra github.com/MichaelS11/go-cql-driver
ClickHouse clickhouse github.com/kshvakov/clickhouse
Couchbase couchbase github.com/couchbase/go_n1ql
Cznic QL ql modernc.org/ql
Firebird SQL firebird github.com/nakagami/firebirdsql
Genji genji github.com/genjidb/genji/sql/driver
Google BigQuery bigquery gorm.io/driver/bigquery/driver
Google Spanner spanner github.com/rakyll/go-sql-driver-spanner
Microsoft ADODB adodb github.com/mattn/go-adodb
ModernC SQLite moderncsqlite modernc.org/sqlite
ODBC odbc github.com/alexbrainman/odbc
Presto presto github.com/prestodb/presto-go-client/presto
SAP ASE tds github.com/thda/tds
SAP HANA hdb github.com/SAP/go-hdb/driver
Snowflake snowflake github.com/snowflakedb/gosnowflake
Trino trino github.com/trinodb/trino-go-client/trino
Vertica vertica github.com/vertica/vertica-sql-go
VoltDB voltdb github.com/VoltDB/voltdb-client-go/voltdbclient
MOST DRIVERS most all drivers excluding ODBC (requires CGO and additional dependencies)
ALL DRIVERS all all drivers

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

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

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
  \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
  \gx                             as \g, but forces expanded output mode

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

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] [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

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 the TIME_FORMAT variable:

$ usql pg://
Connected with driver postgres (PostgreSQL 9.6.9)
Type "help" for help.

pg:[email protected]=> \set
TIME_FORMAT = 'RFC3339Nano'
pg:[email protected]=> select now();
                now
+----------------------------------+
  2018-06-14T03:24:12.481923+07:00
(1 rows)

pg:[email protected]=> \set TIME_FORMAT Kitchen
pg:[email protected]=> \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.

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!

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
Comments
  • typed text isn't visible (need a way to select dark-background or light-background palettes)

    typed text isn't visible (need a way to select dark-background or light-background palettes)

    The typed text "help" below wasn't visible in the terminal.

    $ usql sqlite:/var/db/pkg/local.sqlite
    Connected with driver sqlite3 (SQLite3 3.21.0)
    Type "help" for help.
    
    sq:/var/db/pkg/local.sqlite=> help
    You are using usql, the universal command-line interface for SQL databases.
    Type:  \copyright for distribution terms
           \? for help with usql commands
           \g or terminate with semicolon to execute query
           \q to quit
    sq:/var/db/pkg/local.sqlite=>  
    
    opened by yurivict 17
  • libicu dependency issue again

    libicu dependency issue again

    Installed usql binary release 0.7.8 on Ubuntu 20.04 (Focal). Upon executing, I got an error:

    $ usql --version
    usql: error while loading shared libraries: libicuuc.so.63: cannot open shared object file: No such file or directory
    

    This version of Ubuntu installs libicu65.

    This libicu dependency has caused issues in the past (#80, #82, #90).

    I'm wondering if it's possible to relax the usql dependency to libicuuc.so rather than a specific version (e.g. libicuuc.so.63) so that if the host system is a couple versions ahead or behind, chances are very good that usql will function just fine.

    Here's what /usr/lib/x86_64-linux-gnu looks like on Ubuntu 20.04:

    libicudata.a
    libicudata.so -> libicudata.so.65.1
    libicudata.so.65 -> libicudata.so.65.1
    libicudata.so.65.1
    libicui18n.a
    libicui18n.so -> libicui18n.so.65.1
    libicui18n.so.65 -> libicui18n.so.65.1
    libicui18n.so.65.1
    libicuio.a
    libicuio.so -> libicuio.so.65.1
    libicuio.so.65 -> libicuio.so.65.1
    libicuio.so.65.1
    libicutest.a
    libicutest.so -> libicutest.so.65.1
    libicutest.so.65 -> libicutest.so.65.1
    libicutest.so.65.1
    libicutu.a
    libicutu.so -> libicutu.so.65.1
    libicutu.so.65 -> libicutu.so.65.1
    libicutu.so.65.1
    libicuuc.a
    libicuuc.so -> libicuuc.so.65.1
    libicuuc.so.65 -> libicuuc.so.65.1
    libicuuc.so.65.1
    

    If I manually create links for libicuuc.so.63 -> libicuuc.so.65.1, usql works again:

    $ for lib in libicu*.so.65.1
    do
       sudo ln -s $lib ${lib/65.1/63};
    done;
    
    $ ls -l libicu* | sed 's/.*:[0-9][0-9] //;'
    libicudata.a
    libicudata.so -> libicudata.so.65.1
    libicudata.so.63 -> libicudata.so.65.1    <-- new link
    libicudata.so.65 -> libicudata.so.65.1
    libicudata.so.65.1
    libicui18n.a
    libicui18n.so -> libicui18n.so.65.1
    libicui18n.so.63 -> libicui18n.so.65.1    <-- new link
    libicui18n.so.65 -> libicui18n.so.65.1
    libicui18n.so.65.1
    libicuio.a
    libicuio.so -> libicuio.so.65.1
    libicuio.so.63 -> libicuio.so.65.1    <-- new link
    libicuio.so.65 -> libicuio.so.65.1
    libicuio.so.65.1
    libicutest.a
    libicutest.so -> libicutest.so.65.1
    libicutest.so.63 -> libicutest.so.65.1    <-- new link
    libicutest.so.65 -> libicutest.so.65.1
    libicutest.so.65.1
    libicutu.a
    libicutu.so -> libicutu.so.65.1
    libicutu.so.63 -> libicutu.so.65.1    <-- new link
    libicutu.so.65 -> libicutu.so.65.1
    libicutu.so.65.1
    libicuuc.a
    libicuuc.so -> libicuuc.so.65.1
    libicuuc.so.63 -> libicuuc.so.65.1    <-- new link
    libicuuc.so.65 -> libicuuc.so.65.1
    libicuuc.so.65.1
    $ usql --version
    usql 0.7.8
    $
    
    opened by jetzerb 16
  • Problems with building oracle version on Windows

    Problems with building oracle version on Windows

    Here is the complete setup I used to build u-sql exe on Windows with oracle support:

    Environment

    • Windows 10
    • Powershell 5

    Build

    1. Installed golang and gcc stuff with chocolatey:
    #iwr https://chocolatey.org/install.ps1 -UseBasicParsing | iex
    choco install golang mingw pkgconfiglite
    refreshenv
    
    1. Downloaded Instant Client Downloads for Microsoft Windows (x64). All zip files extracted into the same directory c:\oracle\instantclient_12_1

    2. Created file c:\oracle\oci8.pc file with the following content:

    prefix=/devel/target/1.0
    exec_prefix=${prefix}
    libdir=C:/oracle/instantclient_12_1/sdk/lib/msvc
    includedir=C:/oracle/instantclient_12_1/sdk/include
    oralib=C:/oracle/instantclient_12_1/sdk/lib/msvc
    orainclude=C:/oracle/instantclient_12_1/sdk/include
    gcclib=c:/tools/mingw64/lib
    gccinclude=c:/tools/mingw64/lib
    glib_genmarshal=glib-genmarshal
    gobject_query=gobject-query
    glib_mkenums=glib-mkenums
    Name: oci8
    Version: 12.1
    Description: oci8 library
    Libs: -L${oralib} -L${gcclib} -loci
    Libs.private:
    Cflags: -I${orainclude} -I${gccinclude}
    
    1. Run the following build command:
    $Env:PKG_CONFIG_PATH="c:\oracle"  #location of oci8.pc
    go get -u -tags oracle github.com/knq/usql
    

    It builds without problems and produces ~ 13MB file in $HOME\go\bin\usql.exe When I run it nothing happens - no error, no output, no nothing. When I build non oracle binary everything works as expected.

    Using dependency walker I found out that many of the DLLs are missing:

           Module                                                        File Time Stamp   Link Time Stamp   File Size   Attr.  Link Checksum  Real Checksum  CPU  Subsystem  Symbols     Preferred Base      Actual Base  Virtual Size  Load Order  File Ver        Product Ver     Image Ver    Linker Ver  OS Ver  Subsystem Ver
    -----  ------------------------------------------------------------  ----------------  ----------------  ----------  -----  -------------  -------------  ---  ---------  ----------  ------------------  -----------  ------------  ----------  --------------  --------------  -----------  ----------  ------  -------------
    [ ? ]  API-MS-WIN-CORE-APIQUERY-L1-1-0.DLL                           Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-APPCOMPAT-L1-1-1.DLL                          Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-APPINIT-L1-1-0.DLL                            Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-ATOMS-L1-1-0.DLL                              Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-CRT-L1-1-0.DLL                                Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-CRT-L2-1-0.DLL                                Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-DATETIME-L1-1-2.DLL                           Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-DELAYLOAD-L1-1-1.DLL                          Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-ENCLAVE-L1-1-0.DLL                            Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-ERRORHANDLING-L1-1-3.DLL                      Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-FIBERS-L2-1-1.DLL                             Error opening file. The system cannot find the file specified (2).
    [ ? ]  API-MS-WIN-CORE-FILE-L1-2-2.DLL                               Error opening file. The system cannot find the file
    ...
    

    Looks like vcredist but I have all installed: ...

    choco list -l vcredist Chocolatey v0.10.3 vcredist140 14.10.25008.0 vcredist2008 9.0.30729.6161 vcredist2010 10.0.40219.2 vcredist2012 11.0.61031 vcredist2015 14.0.24215.20170201 5 packages installed. ...

    I would appreciate any help.

    opened by majkinetor 15
  • Does usql support authenticating via domain to MS Sql?

    Does usql support authenticating via domain to MS Sql?

    One thing I hate is trying to SSMS to come up correctly when I'm connecting with Domain Auth to a domain that I don't have a trust with. Does Usql have the capability to auth via domain to a domain with no trust?

    opened by sorakiu 13
  • usql hanging and spinning cpu when NCLOB data type selected, ORACLE, GODROR

    usql hanging and spinning cpu when NCLOB data type selected, ORACLE, GODROR

    usql 0.9.2 using Oracle Go driver (godror) downloaded about a week ago.

    If my query includes the select of a column with data type NCLOB , usql hangs and sends the CPU on my client spinning up to about 180% and it will never terminate. Dropping this column from the SELECT, the query runs fine and terminates quickly with proper results.

    I typically run by putting my query in a local file and then execute as ...

    usql (connection url) < myquery.sql > myquery.txt

    if the query doesn't select an NCLOB, it works fine.

    i've also tried just starting usql interactively and pasting the query in, but same result, when there is an NCLOB column, it hangs.

    I had hypothesized that usql is trying to see all the data to determine the column widths in the output and NCLOBs may be causing an issue there. But i also tried running with --csv option and i get the same behavior.

    Also, when usql is hanging like this, I can't force terminate the process via ctrl-c or even suspend it with ctrl-z. I have to get the PID and issue a kill command in a separate terminal.

    opened by mckennagene 11
  • Cannot build usql

    Cannot build usql

    I'm getting an error while trying to build usql 0.9.0 $ go get github.com/xo/usql go get: github.com/xo/[email protected] updating to github.com/xo/[email protected] requires sqlflow.org/[email protected] requires github.com/myesui/[email protected]: reading github.com/myesui/uuid/go.mod at revision v1.0.0: unknown revision v1.0.0

    can you fix it please? Thanks.

    opened by yshadow 11
  • Any suggestions on how to connect to DB2/Tibero instances?

    Any suggestions on how to connect to DB2/Tibero instances?

    Hi @kenshaw , In Linux, I have used odbc+DB2 to connect to the DB2 instance. Thanks for the going an extra mile and providing a Dockerfile for DB2 instance. Can I use a similar approach to connect to Tibero? Apart from that, Can you elaborate on how to use adodb to connect to DB2 in windows? And can we apply this adodb process on Tibero too?

    Thanks, Chakri

    opened by chakri-stark 11
  • What is necessary to be able to implement completion?

    What is necessary to be able to implement completion?

    Hello.

    I just recently came to this project, and I found it to be very interesting.

    I'm looking forward to the Autocomplete feature, and I intend contributing to the project to implement it.

    What would be the general guidelines to have something like this implemented?

    opened by thalesmello 11
  • Error when trying to login to Oracle

    Error when trying to login to Oracle

    $ ./bin/usql or://user:[email protected]
    error: ora: ORA-00942: table or view does not exist
    

    My guess is it fails at this query:

    SELECT version FROM V$INSTANCE;
    

    Any ideas?

    opened by sawan 11
  • Common Table Expressions

    Common Table Expressions

    I could be wrong, but I don't think usql will work with https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx since those statements do not start with an INSERT or SELECT. Am I wrong?

    opened by otoolep 11
  • mariadb password

    mariadb password

    Given

    • usqlpass have maria:host:port:dbname:user:pass

    Reproduce

    • version 0.11.0 usql maria://host:port/dbname

    Actual

    • db server side [Warning] Access denied for user ''@'xxx' (using password: NO)
    opened by loynoir 10
  • awsathena driver does not support `\d`

    awsathena driver does not support `\d`

    I can connect to Athena using:

    $ usql 's3://__S3_STAGING_DIR_NOT_URL_ENCODE_WITH_TRAILING_FORWARDSLASH_ALSO_NOT_ENCODED?WGRemoteCreation=true&accessID=__THE_ACCESS_KEY__&db=__DATABASE_NAME_PROBABLY_URLENCODED_&missingAsEmptyString=true&region=eu-west-2&secretAccessKey=__THE_SECRET_URLENCODED__'
    

    Which does work / can query

    However \d does not (not supported by awsathena driver).

    s3:__S3_STAGING_DIR_NOT_URL_ENCODE_WITH_TRAILING_FORWARDSLASH_ALSO_NOT_ENCODED=> \d
    error: describe commands not supported by awsathena driver
    
    $ usql --version
    usql 0.12.13
    
    opened by forbesmyester 1
  • High memory usage

    High memory usage

    I run simple query select * from <tablen_name> where date_column >= '<date>';. The result for this query is 1854211 rows, after I got the first page of results the usql process consumed 6GB of the memory.

    obraz

    opened by kozikowskik 7
  • `\ss` says a relation is missing when a table is empty and doesn't have any statistics

    `\ss` says a relation is missing when a table is empty and doesn't have any statistics

    pg:[email protected]:5432/benchto=> \ss benchmark_runs
    Did not find any relation named "benchmark_runs".
    pg:[email protected]:5432/benchto=> select count(*) from benchmark_runs;
    ┌───────┐
    │ count │
    ├───────┤
    │     0 │
    └───────┘
    

    It would be more valuable if it printed a summary row with zeros.

    bug help wanted 
    opened by nineinchnick 0
  • `\ss` without arguments lists columns for all tables

    `\ss` without arguments lists columns for all tables

    \ss without arguments lists columns for all tables, including system catalogs. It would be more useful if it only listed the summary row for every table.

    enhancement help wanted 
    opened by nineinchnick 0
  • Fix newlines in \d output

    Fix newlines in \d output

    TestWriter now succeeds for pgsql descTable go test ./drivers/ --dbs pgsql --cleanup=false

    I restricted the postgres implementation of Tables() to only return tables, views, materialized views and sequences. Technically this would not be necessary since all calls to Tables() now specifically request the types of database objects they require. But still I think it is cleaner since other implementations (mostly information_schema) behave like this as well.

    opened by henlue 0
Releases(v0.12.13)
Owner
XO
Various Cross-Platform language, database, and platform tools
XO
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 4.1k Sep 25, 2022
SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call.

SQL API SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call. The request contains the DB credenti

Çiçeksepeti Tech 24 Jun 21, 2022
Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.

Scaleway Plugin for Steampipe Use SQL to query infrastructure servers, networks, databases and more from your Scaleway project. Get started → Document

Turbot 6 Jul 16, 2022
Manage SQL databases, users and grant using kubernetes manifests

SqlOperator Operate sql databases, users and grants. This is a WIP project and should not at all be used in production at this time. Feel free to vali

Stenic 1 Nov 28, 2021
A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

ploto A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management It's not an ORM. wo

solar 2 Mar 16, 2022
SQLite extension for accessing other SQL databases

dblite SQLite extension for accessing other SQL databases, in SQLite. Similar to how Postgres Foreign Data Wrappers enable access to other databases i

MergeStat 9 May 20, 2022
Use SQL to query databases, logs and more from PlanetScale

Use SQL to instantly query PlanetScale databases, branches and more. Open source CLI. No DB required.

Turbot 1 Feb 13, 2022
This is the code example how to use SQL to query data from any relational databases in Go programming language.

Go with SQL example This is the code example how to use SQL to query data from any relational databases in Go programming language. To start, please m

Muhammad Uzair Mohd Faizul 1 Mar 12, 2022
A simple auditor of SQL databases.

DBAuditor SQL数据库审计系统,目前支持SQL注入攻击审计 环境配置 sudo apt install golang 运行方式 将待审计语句填入test.txt中,然后运行主程序: 直接运行: go run main.go 编译运行: go build main.go ./main 主要目

Yunjie Xiao 4 May 13, 2022
Mergestat - a command-line tool for running SQL queries on git repositories and related data sources

Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. ?? ??

MergeStat 3.2k Sep 28, 2022
write APIs using direct SQL queries with no hassle, let's rethink about SQL

SQLer SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define val

Mohammed Al Ashaal 2k Sep 13, 2022
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

null 0 Oct 20, 2021
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

null 0 Jan 25, 2022
🏋️ dbbench is a simple database benchmarking tool which supports several databases and own scripts

dbbench Table of Contents Description Example Installation Supported Databases Usage Custom Scripts Troubeshooting Development Acknowledgements Descri

Simon Jürgensmeyer 74 Sep 26, 2022
Cross-platform client for PostgreSQL databases

pgweb Web-based PostgreSQL database browser written in Go. Overview Pgweb is a web-based database browser for PostgreSQL, written in Go and works on O

Dan Sosedoff 7.5k Sep 21, 2022
Redis-shake is a tool for synchronizing data between two redis databases. Redis-shake是一个用于在两个redis之间同步数据的工具,满足用户非常灵活的同步、迁移需求。

RedisShake is mainly used to synchronize data from one redis to another. Thanks to the Douyu's WSD team for the support. 中文文档 English tutorial 中文使用文档

Alibaba 2.6k Sep 20, 2022
Go sqlite3 http vfs: query sqlite databases over http with range headers

sqlite3vfshttp: a Go sqlite VFS for querying databases over http(s) sqlite3vfshttp is a sqlite3 VFS for querying remote databases over http(s). This a

Peter Sanford 71 Sep 22, 2022
The open-source collaborative IDE for your databases.

The open-source collaborative IDE for your databases in your browser. About Slashbase is an open-source collaborative IDE for your databases in your b

Slashbase 325 Sep 4, 2022
Cross-platform client for PostgreSQL databases

pgweb Web-based PostgreSQL database browser written in Go. Overview Pgweb is a web-based database browser for PostgreSQL, written in Go and works on O

Dan Sosedoff 7.5k Sep 24, 2022