`_ |
[d-arangodb]: https://github.com/xo/dbimp
[d-athena]: https://github.com/xo/dbimp
[d-avatica]: https://github.com/xo/dbimp
[d-bigquery]: https://github.com/xo/dbimp
[d-cassandra]: https://github.com/xo/cassandra
[d-chai]: https://github.com/chaisql/chai
[d-clickhouse]: https://github.com/xo/dbimp
[d-cockroachdb]: https://github.com/jackc/pgx
[d-cosmos]: https://github.com/xo/dbimp
[d-couchbase]: https://github.com/xo/dbimp
[d-cratedb]: https://github.com/jackc/pgx
[d-csvq]: https://github.com/mithrandie/csvq-driver
[d-databend]: https://github.com/xo/dbimp
[d-databricks]: https://github.com/xo/dbimp
[d-drill]: https://github.com/xo/dbimp
[d-druid]: https://github.com/xo/dbimp
[d-duckdb]: https://github.com/duckdb/duckdb-go
[d-dynamodb]: https://github.com/xo/dbimp
[d-elasticsearch]: https://github.com/xo/dbimp
[d-exasol]: https://github.com/xo/dbimp
[d-firebird]: https://github.com/nakagami/firebirdsql
[d-flightsql]: https://github.com/apache/arrow-go/tree/main/arrow/flight/flightsql/driver
[d-gizmosql]: https://github.com/apache/arrow-go/tree/main/arrow/flight/flightsql/driver
[d-h2]: https://github.com/jmrobles/h2go
[d-hive]: https://github.com/beltran/gohive
[d-impala]: https://github.com/sclgo/impala-go
[d-influxdb]: https://github.com/xo/dbimp
[d-influxql]: https://github.com/xo/dbimp
[d-libpq]: https://github.com/lib/pq
[d-libsql]: https://github.com/xo/dbimp
[d-maxcompute]: https://github.com/aliyun/aliyun-odps-go-sdk
[d-memsql]: https://github.com/go-sql-driver/mysql
[d-moderncsqlite]: https://gitlab.com/cznic/sqlite
[d-mysql]: https://github.com/go-sql-driver/mysql
[d-neo4j]: https://github.com/xo/dbimp
[d-odbc]: https://github.com/xo/odbc
[d-opensearch]: https://github.com/xo/dbimp
[d-oracle]: https://github.com/sijms/go-ora
[d-ots]: https://github.com/aliyun/aliyun-tablestore-go-sql-driver
[d-pgx]: https://github.com/jackc/pgx
[d-pinot]: https://github.com/xo/dbimp
[d-postgres]: https://github.com/jackc/pgx
[d-presto]: https://github.com/xo/dbimp
[d-questdb]: https://github.com/jackc/pgx
[d-redshift]: https://github.com/jackc/pgx
[d-rqlite]: https://github.com/xo/dbimp
[d-saphana]: https://github.com/SAP/go-hdb
[d-snowflake]: https://github.com/xo/dbimp
[d-solr]: https://github.com/xo/dbimp
[d-spanner]: https://github.com/xo/dbimp
[d-sqlite3]: https://github.com/mattn/go-sqlite3
[d-sqlserver]: https://github.com/microsoft/go-mssqldb
[d-surrealdb]: https://github.com/xo/dbimp
[d-tidb]: https://github.com/go-sql-driver/mysql
[d-trino]: https://github.com/xo/dbimp
[d-vertica]: https://github.com/vertica/vertica-sql-go
[d-vitess]: https://github.com/go-sql-driver/mysql
[d-voltdb]: https://github.com/VoltDB/voltdb-client-go
[d-ydb]: https://github.com/ydb-platform/ydb-go-sdk
[f-cgo]: #f-cgo "Requires CGO"
[f-embedded]: #f-embedded "Embedded"
[f-hosted]: #f-hosted "Hosted service"
† Requires CGO
§ Embedded, with no server to run
¶ Hosted service, with no server you can run
Any of the protocol schemes/aliases above can be used in conjunction when
connecting to a database via the command-line or with the [`\connect` and
`\copy` commands][commands]:
```sh
# 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'
```
See [the section below on connecting to databases][connecting] for further
details building DSNs/URLs for use with `usql`.
## Using
After [installing][], `usql` can be used similarly to the following:
```sh
# 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
```
### Command-line Options
Supported command-line options:
```sh
$ 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 script variable NAME to VALUE (see \set command, aliases: --var --variable)
--uset NAME=VALUE set setting NAME of usql to VALUE (see \uset command)
-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
```
### Connecting to Databases
`usql` opens a database connection by [parsing a URL][dburl] and passing the
resulting connection string to [a database driver][databases]. 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`,
`\c`, and `\copy` commands][commands].
Database connections can be defined with [the `\cset` command][connection-vars]
or in [the `config.yaml` configuration file][config].
#### Database Connection Strings
Database connection strings look like the following:
```txt
driver+transport://user:pass@host/dbname?opt1=a&opt2=b
driver:/path/to/file
/path/to/file
name
```
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` [±][f-path] | 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 |
| `name` | a connection name set by [`\cset`][connection-vars] or in [`config.yaml`][config] |
[f-path]: #f-path "URL Paths for Databases"
± 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 as [the `dburl`
package][dburl]. Databases have at least one or more aliases. See [`dburl`'s
scheme documentation][dburl-schemes] for a list of 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`, `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&opt2=b`. Refer to the [relevant database driver's documentation][databases]
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.
When the path is a Unix Domain Socket, `usql` will attempt to open it with the
MySQL driver. When the path is a directory, `usql` will attempt to open it
using the PostgreSQL driver. And, lastly, when the path is a regular file,
`usql` will attempt to open the file using the SQLite3 or DuckDB drivers.
#### 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:
```sh
# connect to postgres using the local $USER and the unix domain socket in /var/run/postgresql
$ usql pg://
```
See the relevant documentation [on database drivers][databases] for more
information.
### Connection Examples
The following are example connection strings and additional ways to connect to
databases using `usql`:
```sh
# 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 cassandra://host/
$ usql ca://
# connect to an alibaba maxcompute project over https, or over http with tls=false:
$ usql "mc://accessid:accesskey@service.cn-hangzhou.maxcompute.aliyun.com/api?project=myproject"
$ usql "mc://accessid:accesskey@host/api?project=myproject&tls=false"
# Note: the project option is required. The curr_project and scheme options of
# the previous maxcompute driver no longer work, and any option the driver does
# not recognize is sent to the server as an SQL hint.
# 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 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 (needs the ODBC driver manager of the system)
$ cat /etc/odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1
# connect to a postgres database using the odbc config above
$ usql odbc+PostgreSQL+ANSI://user:pass@localhost/dbname?TraceFile=/path/to/trace.log
```
See the [section on connection variables][connection-vars] for information on
defining connection names.
### Executing Queries and Commands
The interactive interpreter reads queries and [backslash meta (`\`) commands][commands],
sending the query to the connected database:
```sh
$ 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=>
```
A command takes one or more parameters. A parameter can be quoted with either
`'` or `"`, and [can also be backticked][backticks].
### Backslash Commands
`usql` supports interleaved backslash (`\`) meta commands to modify or alter
the way that `usql` interprets queries, formats its output, and changes the
resulting interactive flow.
```sh
(not connected)=> \c postgres://user:pass@localhost
pg:user@localhost=> select * from my_table \G
```
Available backslash meta commands can be displayed with `\?`:
```sh
$ 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
```
> The README is long; the above is its first 40000 characters. Full content: https://github.com/xo/usql
