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

# add xo tap
$ brew tap xo/xo

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

# install usql with most drivers
$ yay -S usql

$ git clone https://aur.archlinux.org/usql.git && cd usql
$ makepkg -si
==> Making package: usql 0.12.10-1 (Fri 26 Aug 2022 05:56:09 AM WIB)
==> Checking runtime dependencies...
==> Checking buildtime dependencies...
==> Retrieving sources...
  -> Downloading usql-0.12.10.tar.gz...
...

# install latest usql version with base drivers
$ go install github.com/xo/usql@latest

# alternately, install usql with most drivers (see below for info about build tags)
$ go install -tags most github.com/xo/usql@latest

# run interactive shell and mount the $PWD/data directory as a volume for use
# within the container
$ docker run --rm -it --volume $(pwd)/data:/data docker.io/usql/usql:latest sqlite3://data/test.db
Trying to pull docker.io/usql/usql:latest...
Getting image source signatures
Copying blob af48168d69d8 done   |
Copying blob efc2b5ad9eec skipped: already exists
Copying config 917ceb411d done   |
Writing manifest to image destination
Connected with driver sqlite3 (SQLite3 3.45.1)
Type "help" for help.

sq:data/test.db=> \q

# run postgres locally
$ docker run --detach --rm --name=postgres --publish=5432:5432 --env=POSTGRES_PASSWORD=P4ssw0rd docker.io/usql/postgres

# connect to local postgres instance
$ docker run --rm --network host -it docker.io/usql/usql:latest postgres://postgres:P4ssw0rd@localhost
Connected with driver postgres (PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.

pg:postgres@localhost=> \q

# run specific usql version
$ docker run --rm -it docker.io/usql/usql:0.19.3

# build/install with base drivers (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server,
# Oracle, CSVQ)
$ go install github.com/xo/usql@master

# build/install with base, Avatica, and ODBC drivers
$ go install -tags 'avatica odbc' github.com/xo/usql@master

# build/install most drivers, excluding Avatica, Couchbase, and PostgreSQL
$ go install -tags 'most no_avatica no_couchbase no_postgres' github.com/xo/usql@master

# build/install with most drivers (excludes CGO drivers and problematic drivers)
$ go install -tags most github.com/xo/usql@master

# build/install all drivers (includes CGO drivers and problematic drivers)
$ go install -tags all github.com/xo/usql@master

$ cd $GOPATH/src/github.com/xo/usql

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

# connect to a vitess database:
$ usql vt://user:pass@host:3306/mydatabase

$ usql
(not connected)=> \c vitess://user:pass@host:3306/mydatabase

$ usql
(not connected)=> \copy csvq://. pg://localhost/ 'select * ....' 'myTable'

# connect to a postgres database
$ usql postgres://booktest@localhost/booktest

# connect to an oracle database
$ usql oracle://user:pass@host/oracle.sid

# connect to a postgres database and run the commands contained in script.sql
$ usql pg://localhost/ -f script.sql

$ usql --help
usql, the universal command-line interface for SQL databases

Usage:
  usql [flags]... [DSN]

Arguments:
  DSN   database url or connection name

Flags:
  -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-init                             do not execute initialization scripts (aliases: --no-rc --no-psqlrc --no-usqlrc)
  -o, --out FILE                            output file
  -W, --password                            force password prompt (should happen automatically)
  -1, --single-transaction                  execute as a single transaction (if non-interactive)
  -v, --set NAME=VALUE                      set variable NAME to VALUE (see \set command, aliases: --var --variable)
  -N, --cset NAME=DSN                       set named connection NAME to DSN (see \cset command)
  -P, --pset VAR=ARG                        set printing option VAR to ARG (see \pset command)
  -F, --field-separator FIELD-SEPARATOR     field separator for unaligned and CSV output (default "|" and ",")
  -R, --record-separator RECORD-SEPARATOR   record separator for unaligned and CSV 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 and CSV output to zero byte
  -0, --record-separator-zero               set record separator for unaligned and CSV output to zero byte
  -J, --json                                JSON output mode
  -C, --csv                                 CSV output mode
  -G, --vertical                            vertical output mode
  -q, --quiet                               run quietly (no messages, only query output)
      --config string                       config file
  -V, --version                             output version information, then exit
  -?, --help                                show this help, then exit

# connect to postgres using the local $USER and the unix domain socket in /var/run/postgresql
$ usql pg://

# 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 sqlserver database
$ usql sqlserver://user:pass@host/instancename/dbname
$ 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 sqlserver database using Windows domain authentication
$ runas /user:ACME\wiley /netonly "usql mssql://host/dbname/"

# connect to a oracle database
$ usql or://user:pass@host/sid
$ usql oracle://user:pass@host:port/sid
$ usql or://

# connect to a cassandra database
$ usql ca://user:pass@host/keyspace
$ usql cassandra://host/keyspace
$ usql cql://host/
$ usql ca://

# connect to a sqlite database that exists on disk
$ usql dbname.sqlite3

# Note: when connecting to a SQLite database, if the "driver://" or
# "driver:" scheme/alias is omitted, the file must already exist on disk.
#
# if the file does not yet exist, the URL must incorporate file:, sq:, sqlite3:,
# or any other recognized sqlite3 driver alias to force usql to create a new,
# empty database at the specified path:
$ usql sq://path/to/dbname.sqlite3
$ usql sqlite3://path/to/dbname.sqlite3
$ usql file:/path/to/dbname.sqlite3

# connect to a adodb ole resource (windows only)
$ usql adodb://Microsoft.Jet.OLEDB.4.0/myfile.mdb
$ usql "adodb://Microsoft.ACE.OLEDB.12.0/?Extended+Properties=\"Text;HDR=NO;FMT=Delimited\""

# connect to a named connection in $HOME/.config/usql/config.yaml
$ cat $HOME/.config/usql/config.yaml
connections:
  my_named_connection: sqlserver://user:pass@localhost/
$ usql my_named_connection

# 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 config above
$ usql odbc+DB2://user:pass@localhost/dbname
$ usql odbc+PostgreSQL+ANSI://user:pass@localhost/dbname?TraceFile=/path/to/trace.log

$ usql sqlite://example.sqlite3
Connected with driver sqlite3 (SQLite3 3.17.0)
Type "help" for help.

sq:example.sqlite3=> create table test (test_id int, name string);
CREATE TABLE
sq:example.sqlite3=> insert into test (test_id, name) values (1, 'hello');
INSERT 1
sq:example.sqlite3=> select * from test;
  test_id | name
+---------+-------+
        1 | hello
(1 rows)

sq:example.sqlite3=> select * from test
sq:example.sqlite3-> \p
select * from test
sq:example.sqlite3-> \g
  test_id | name
+---------+-------+
        1 | hello
(1 rows)

sq:example.sqlite3=> \c postgres://booktest@localhost
error: pq: 28P01: password authentication failed for user "booktest"
Enter password:
Connected with driver postgres (PostgreSQL 9.6.6)
pg:booktest@localhost=> select * from authors;
  author_id |      name
+-----------+----------------+
          1 | Unknown Master
          2 | blah
          3 | foobar
(3 rows)

pg:booktest@localhost=>

(not connected)=> \c postgres://user:pass@localhost
pg:user@localhost=> select * from my_table \G

$ usql
Type "help" for help.

(not connected)=> \?
General
  \q                                quit usql
  \quit                             alias for \q
  \copyright                        show usage and distribution terms for usql
  \drivers                          show database drivers available to usql

Help
  \? [commands]                     show help on usql's meta (backslash) commands
  \? options                        show help on usql command-line options
  \? variables                      show help on special usql variables

Connection
  \c DSN or \c NAME                 connect to dsn or named database connection
  \c DRIVER PARAMS...               connect to database with driver and parameters
  \connect                          alias for \c
  \Z                                close (disconnect) database connection
  \disconnect                       alias for \Z
  \password [USER]                  change password for user
  \passwd                           alias for \password
  \conninfo                         display information about the current database connection

Query Execute
  \g [(OPTIONS)] [FILE] or ;        execute query (and send results to file or |pipe)
  \go                               alias for \g
  \G [(OPTIONS)] [FILE]             as \g, but forces vertical output mode
  \ego                              alias for \G
  \gx [(OPTIONS)] [FILE]            as \g, but forces expanded output mode
  \gexec                            execute query and execute each value of the result
  \gset [PREFIX]                    execute query and store results in usql variables
  \bind [PARAM]...                  set query parameters
  \timing [on|off]                  toggle timing of commands

Query View
  \crosstab [(OPTIONS)] [COLUMNS]   execute query and display results in crosstab
  \crosstabview                     alias for \crosstab
  \xtab                             alias for \crosstab
  \chart CHART [(OPTIONS)]          execute query and display results as a chart
  \watch [(OPTIONS)] [INTERVAL]     execute query every specified interval

Query Buffer
  \e [-raw|-exec] [FILE] [LINE]     edit the query buffer, raw (non-interpolated) buffer, the
                                    exec buffer, or a file with external editor
  \edit                             alias for \e
  \p [-raw|-exec]                   show the contents of the query buffer, the raw
                                    (non-interpolated) buffer or the exec buffer
  \print                            alias for \p
  \raw                              alias for \p
  \exec                             alias for \p
  \w [-raw|-exec] FILE              write the contents of the query buffer, raw
                                    (non-interpolated) buffer, or exec buffer to file
  \write                            alias for \w
  \r                                reset (clear) the query buffer
  \reset                            alias for \r

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
  \dp[S] [PATTERN]                  list table, view, and sequence access privileges
  \ds[S+] [PATTERN]                 list sequences
  \dt[S+] [PATTERN]                 list tables
  \dv[S+] [PATTERN]                 list views
  \l[+]                             list databases
  \ss[+] [TABLE|QUERY] [k]          show stats for a table or a query

Variables
  \set [NAME [VALUE]]               set usql application variable, or show all usql application
                                    variables if no parameters
  \unset NAME                       unset (delete) usql application variable
  \pset [NAME [VALUE]]              set table print formatting option, or show all print
                                    formatting options if no parameters
  \a                                toggle between unaligned and aligned output mode
  \C [TITLE]                        set table title, or unset if none
  \f [SEPARATOR]                    show or set field separator for unaligned query output
  \H                                toggle HTML output mode
  \T [ATTRIBUTES]                   set HTML <table> tag attributes, or unset if none
  \t [on|off]                       show only rows
  \x [on|off|auto]                  toggle expanded output
  \cset [NAME [URL]]                set named connection, or show all named connections if no
                                    parameters
  \cset NAME DRIVER PARAMS...       set named connection for driver and parameters
  \prompt [-TYPE] VAR [PROMPT]      prompt user to set application variable

Input/Output
  \echo [-n] [MESSAGE]...           write message to standard output (-n for no newline)
  \qecho [-n] [MESSAGE]...          write message to \o output stream (-n for no newline)
  \warn [-n] [MESSAGE]...           write message to standard error (-n for no newline)
  \o [FILE]                         send all query results to file or |pipe
  \out                              alias for \o
  \copy SRC DST QUERY TABLE         copy results of query from source database into table on
                                    destination database
  \copy SRC DST QUERY TABLE(A,...)  copy results of query from source database into table's
                                    columns on destination database

Control/Conditional
  \i FILE                           execute commands from file
  \include                          alias for \i
  \ir FILE                          as \i, but relative to location of current script
  \include_relative                 alias for \ir
  \if EXPR                          begin conditional block
  \elif EXPR                        alternative within current conditional block
  \else                             final alternative within current conditional block
  \endif                            end conditional block

Transaction
  \begin [-read-only [ISOLATION]]   begin transaction, with optional isolation level
  \commit                           commit current transaction
  \rollback                         rollback (abort) current transaction
  \abort                            alias for \rollback

Operating System/Environment
  \! [COMMAND]                      execute command in shell or start interactive shell
  \cd [DIR]                         change the current working directory
  \getenv VARNAME ENVVAR            fetch environment variable
  \setenv NAME [VALUE]              set or unset environment variable

$ usql my_godror_conn
Connected with driver godror (Oracle Database 23.0.0.0.0)
Type "help" for help.

gr:system@localhost/free=>

(not connected)=> \unset FOO
(not connected)=> \set FOO bar

(not connected)=> \set
FOO = 'bar'

pg:booktest@localhost=> \set FOO bar
pg:booktest@localhost=> select * from authors where name = :'FOO';
  author_id | name
+-----------+------+
          7 | bar
(1 rows)

pg:booktest@localhost=> \set TBLNAME authors
pg:booktest@localhost=> \set COLNAME name
pg:booktest@localhost=> \set FOO bar
pg:booktest@localhost=> select * from :TBLNAME where :"COLNAME" = :'FOO'

pg:booktest@localhost-> \p
select * from authors where "name" = 'bar'
pg:booktest@localhost-> \raw
select * from :TBLNAME where :"COLNAME" = :'FOO'

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

pg:booktest@localhost=> \p
select ':FOO';

(not connected)=> \cset my_conn postgres://user:pass@localhost
(not connected)=> \c my_conn
Connected with driver postgres (PostgreSQL 16.2 (Debian 16.2-1.pgdg120+2))
pg:postgres@localhost=>

(not connected)=> \cset
my_conn = 'postgres://user:pass@localhost'

(not connected)=> \pset time Kitchen
Time display is "Kitchen" ("3:04PM").
(not connected)=> \a
Output format is unaligned.

(not connected)=> \pset
time                     Kitchen

(not connected)=> \? variables

(not connected)=> \echo Welcome `echo $USER` -- 'currently:' "(" `date` ")"
Welcome ken -- currently: ( Wed Jun 13 12:10:27 WIB 2018 )
(not connected)=>

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

(not connected)=> \cset PGDSN postgres://user:pass@localhost
(not connected)=> \cset MYDSN mysql://user:pass@localhost
(not connected)=> \copy PGDSN MYDSN 'select book_id, author_id from books' 'books(id, author_id)'

(not connected)=> \cset MYDSN mysql://user:pass@localhost
(not connected)=> \copy postgres://user:pass@localhost MYDSN 'select book_id, author_id from books' 'books(id, author_id)'

$ usql
(not connected)=> echo :SOURCE_DSN :DESTINATION_DSN
pg://postgres:P4ssw0rd@localhost/ mysql://localhost
(not connected)=> \copy :SOURCE_DSN :DESTINATION_DSN 'select * from mySourceTable' 'myDestination(colA, colB)'
COPY 2

$ usql
(not connected)=> \copy csvq:. sq:test.db 'select * from authors' authors
error: failed to prepare insert query: 2 values for 1 columns
(not connected)=> \copy csvq:. sq:test.db 'select name from authors' authors(name)
COPY 2

$ usql
(not connected)=> \copy postgres://user:pass@localhost mysql://user:pass@localhost 'SELECT uuid_column::TEXT FROM myPgTable' myMyTable
COPY 1

$ cat authors.csv
author_id,name
1,Isaac Asimov
2,Stephen King
$ cat books.csv
book_id,author_id,title
1,1,I Robot
2,2,Carrie
3,2,Cujo
$ usql
(not connected)=> -- setting variables to make connections easier
(not connected)=> \set SOURCE_DSN csvq://.
(not connected)=> \set DESTINATION_DSN sqlite3:booktest.db
(not connected)=> -- connecting to the destination and creating the schema
(not connected)=> \c :DESTINATION_DSN
Connected with driver sqlite3 (SQLite3 3.38.5)
(sq:booktest.db)=> create table authors (author_id integer, name text);
CREATE TABLE
(sq:booktest.db)=> create table books (book_id integer not null primary key autoincrement, author_id integer, title text);
CREATE TABLE
(sq:booktest.db)=> -- adding an extra row to books prior to copying
(sq:booktest.db)=> insert into books (author_id, title) values (1, 'Foundation');
INSERT 1
(sq:booktest.db)=> -- disconnecting to demonstrate that \copy opens new database connections
(sq:booktest.db)=> \disconnect
(not connected)=> -- copying data from SOURCE -> DESTINATION
(not connected)=> \copy :SOURCE_DSN :DESTINATION_DSN 'select * from authors' authors
COPY 2
(not connected)=> \copy :SOURCE_DSN :DESTINATION_DSN 'select author_id, title from books' 'books(author_id, title)'
COPY 3
(not connected)=> \c :DESTINATION_DSN
Connected with driver sqlite3 (SQLite3 3.38.5)
(sq:booktest.db)=> select * from authors;
 author_id |     name
-----------+--------------
         1 | Isaac Asimov
         2 | Stephen King
(2 rows)

sq:booktest.db=> select * from books;
 book_id | author_id |   title
---------+-----------+------------
       1 |         1 | Foundation
       2 |         1 | I Robot
       3 |         2 | Carrie
       4 |         2 | Cujo
(4 rows)

$ cat $HOME/.usqlpass
postgres:*:*:*:postgres:P4ssw0rd
godror:*:*:*:system:P4ssw0rd
$ usql
Type "help" for help.

(not connected)=> \set pglocal postgres://postgres@localhost:49153?sslmode=disable
(not connected)=> \set orlocal godror://system@localhost:1521/orasid
(not connected)=> \copy :pglocal :orlocal 'select staff_id, first_name from staff' 'staff(staff_id, first_name)'
COPY 18

$ usql
(not connected)=> \set SYNTAX_HL_STYLE dracula
(not connected)=> \unset SYNTAX_HL_OVERRIDE_BG

$ usql
Connected with driver postgres (PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.

pg:postgres@=> select * f<Tab>
fetch            from             full outer join

$ usql my://
Connected with driver mysql (10.8.3-MariaDB-1:10.8.3+maria~jammy)
Type "help" for help.

my:root@=> \g<Tab>
\g     \gexec \gset  \gx

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

pg:postgres@=> \pset
time                     RFC3339Nano
pg:postgres@=> select now();
             now
-----------------------------
 2021-05-01T22:21:44.710385Z
(1 row)

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

pg:postgres@=>

$ export USQL_SHOW_HOST_INFORMATION=false
$ usql pg://booktest@localhost
Type "help" for help.

pg:booktest@=>

$ usql --set SHOW_HOST_INFORMATION=false pg://
Type "help" for help.

pg:booktest@=> \set SHOW_HOST_INFORMATION true
pg:booktest@=> \connect pg://
Connected with driver postgres (PostgreSQL 9.6.9)
pg:booktest@=>

# disable
$ USQL_TERM_GRAPHICS=none usql

# force iterm graphics
$ TERM_GRAPHICS=iterm usql

$ 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:booktest@=>

chmod 0600 ~/.usqlpass

$ cat $HOME/.usqlrc
\echo WELCOME TO THE JUNGLE `date`
\set SYNTAX_HL_STYLE paraiso-dark

-- set color prompt (default is prompt is "%S%m%/%R%#" )
\set PROMPT1 "\033[32m%S%m%/%R%#\033[0m"
$ 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)=>

$ usql --no-init pg://

$ 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

$ brew install icu4c
Running `brew update --auto-update`...
==> Downloading ...
...

$ usql
(not connected)=>