discover · compare · collections · requirements · contribute · feed · watching · stats · help with your tokens
xo/usql · source
Snapshot 0 · 2026-11-04T12:47:24Z · Go · MIT
Content fingerprint
b530d28ac0cd8406958b79c913e4d39c37dc11ffeafb61236f1a8c381da9fcb8GitHub description at capture
Universal command-line interface for SQL databases
README at capture
<div align="center">
<img src="https://raw.githubusercontent.com/xo/usql-logo/main/usql.png" height="120">
</div>
<div align="center">
<a href="#installing" title="Installing">Installing</a> |
<a href="#building" title="Building">Building</a> |
<a href="#database-support" title="Database Support">Database Support</a> |
<a href="#using" title="Using">Using</a> |
<a href="#features-and-compatibility" title="Features and Compatibility">Features and Compatibility</a> |
<a href="https://github.com/xo/usql/releases" title="Releases">Releases</a> |
<a href="#contributing" title="Contributing">Contributing</a>
</div>
<br/>
`usql` is a universal command-line interface for PostgreSQL, MySQL, Oracle
Database, SQLite3, Microsoft SQL Server, [and many other databases][databases]
including NoSQL and non-relational databases!
`usql` works with [SQL and NoSQL databases][databases] from a command line
modelled on PostgreSQL's `psql`. `usql` supports most of the
core `psql` features, such as [variables][variables], [backticks][backticks],
[backslash commands][commands] and has additional features that `psql` does
not, such as [multiple database support][databases], [copying between databases][copying],
[context-based completion][completion],
and [terminal graphics][termgraphics].
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][usql-ci-status]][usql-ci]
[![Go Reference][goref-usql-status]][goref-usql]
[![Releases][release-status]][Releases]
[![Discord Discussion][discord-status]][discord]
[usql-ci]: https://github.com/xo/usql/actions/workflows/test.yml "Test CI"
[usql-ci-status]: https://github.com/xo/usql/actions/workflows/test.yml/badge.svg "Test CI"
[goref-usql]: https://pkg.go.dev/github.com/xo/usql "Go Reference"
[goref-usql-status]: https://pkg.go.dev/badge/github.com/xo/usql.svg "Go Reference"
[release-status]: https://img.shields.io/github/v/release/xo/usql?display_name=tag "Latest Release"
[discord]: https://discord.gg/WDWAgXwJqN "Discord Discussion"
[discord-status]: https://img.shields.io/discord/829150509658013727.svg?label=Discord&logo=Discord&colorB=7289da&style=flat-square "Discord Discussion"
[installing]: #installing "Installing"
[databases]: #database-support "Database Support"
[releases]: https://github.com/xo/usql/releases "Releases"
## Installing
`usql` can be installed [via Release][], [via Homebrew][], [via AUR][], [via
Go][] or [via Docker][]:
[via Release]: #installing-via-release
[via Homebrew]: #installing-via-homebrew-macos-and-linux
[via AUR]: #installing-via-aur-arch-linux
[via Go]: #installing-via-go
[via Docker]: #installing-via-docker
### Installing via Release
1. [Download a release for your platform][releases]
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)
### Installing via Homebrew (macOS and Linux)
Install `usql` from the [`xo/xo` tap][xo-tap] in the usual way with the [`brew`
command][homebrew]:
```sh
# install usql with most drivers
$ brew install xo/xo/usql
```
Support for [ODBC databases][databases] is available through the `--with-odbc`
install flag:
```sh
# add xo tap
$ brew tap xo/xo
# install usql with odbc support
$ brew install --with-odbc usql
```
### Installing via AUR (Arch Linux)
Install `usql` from the [Arch Linux AUR][aur-bin] in the usual way with the
[`yay` command][yay]:
```sh
# install usql with most drivers
$ yay -S usql-bin
```
`usql-bin` installs the released binary. To build from source instead, use the
[`usql`][aur] package, which takes considerably longer because it compiles
every driver:
```sh
# build usql with most drivers from source
$ yay -S usql
```
Alternately, build and [install using `makepkg`][arch-makepkg]:
```sh
$ git clone https://aur.archlinux.org/usql.git && cd usql
$ makepkg -si
```
### Installing via Go
Install `usql` in the usual Go fashion:
```sh
# 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
```
See [below for information](#building) on `usql` build tags.
### Installing via Docker
An [official container image (`docker.io/usql/usql`)][docker-hub] is maintained
by the `usql` team, and can be used with Docker, Podman, or other container
runtime.
[docker-hub]: https://hub.docker.com/r/usql/usql
Install `usql` with Docker, Podman, or other container runtime:
```sh
# 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.21.5
```
## Building
When building `usql` out-of-the-box with `go build` or `go install`, only the
[`base` drivers][databases] for PostgreSQL, MySQL, SQLite3, Microsoft SQL
Server, Oracle, CSVQ will be included in the build:
```sh
# build/install with base drivers (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server,
# Oracle, CSVQ)
$ go install github.com/xo/usql@main
```
Other databases can be enabled by specifying the [build tag for their database
driver][databases].
```sh
# build/install with base, Avatica, and ODBC drivers
$ go install -tags 'avatica odbc' github.com/xo/usql@main
```
For every build tag `<driver>`, there is also a `no_<driver>` build tag
that will disable the driver:
```sh
# build/install most drivers, excluding Avatica, Couchbase, and PostgreSQL
$ go install -tags 'most no_avatica no_couchbase no_postgres' github.com/xo/usql@main
```
By specifying the build tags `most` or `all`, the build will include most, and
all SQL drivers, respectively:
```sh
# build/install with most drivers (excludes CGO drivers and problematic drivers)
$ go install -tags most github.com/xo/usql@main
# build/install all drivers (includes CGO drivers and problematic drivers)
$ go install -tags all github.com/xo/usql@main
```
## Database Support
`usql` works with all Go standard library compatible SQL drivers supported by
[`github.com/xo/dburl`][dburl].
The list of drivers that `usql` was built with can be displayed with the
[`\drivers` command][commands]:
<!-- EXAMPLE drivers -->
```sh
$ cd $GOPATH/src/github.com/xo/usql
# build excluding the base drivers, and including cassandra, moderncsqlite and mysql
$ go build -tags 'no_base cassandra moderncsqlite mysql'
# show built driver support
$ ./usql -c '\drivers'
Available Drivers:
cassandra [ca, cql, scy, cass, scylla, datastax]
moderncsqlite [sq, mq, sqlite, sqlite3, modernsqlite]
mysql [my, maria, aurora, mariadb, percona]
```
The above shows that `usql` was built with only the `cassandra`,
`moderncsqlite` and `mysql` drivers. Each line names the scheme of a driver,
and its aliases in `[...]`. Without `sqlite3`, `moderncsqlite` also answers to
the `sqlite3` scheme and its aliases.
### Supported Database Schemes and Aliases
The following are the [Go SQL drivers][go-sql] that `usql` supports, the
associated database, scheme / build tag, and scheme aliases:
<!-- DRIVER DETAILS START -->
| Database | Scheme / Tag | Scheme Aliases | Driver Package / Notes |
| -------------------- | --------------- | ------------------------------------------------ | ------------------------------------------------------------------------------------------- |
| PostgreSQL | `pgx` | `postgres`, `pg`, `pgsql`, `postgresql`, `px` | [github.com/jackc/pgx/v5/stdlib][d-pgx] |
| MySQL | `mysql` | `my`, `maria`, `aurora`, `mariadb`, `percona` | [github.com/go-sql-driver/mysql][d-mysql] |
| Microsoft SQL Server | `sqlserver` | `ms`, `mssql`, `azuresql` | [github.com/microsoft/go-mssqldb][d-sqlserver] |
| Oracle Database | `oracle` | `or`, `ora`, `oci`, `oci8`, `odpi`, `odpi-c` | [github.com/xo/oracle][d-oracle] |
| SQLite3 | `sqlite3` | `sq`, `sqlite`, `file` | [github.com/mattn/go-sqlite3][d-sqlite3] <sup>[†][f-cgo]</sup> <sup>[§][f-embedded]</sup> |
| DuckDB | `duckdb` | `dk`, `ddb`, `duck`, `file` | [github.com/duckdb/duckdb-go/v2][d-duckdb] <sup>[†][f-cgo]</sup> <sup>[§][f-embedded]</sup> |
| ClickHouse | `clickhouse` | `ch` | [github.com/xo/dbimp/clickhouse][d-clickhouse] |
| CockroachDB | `cockroachdb` | `cr`, `cdb`, `crdb`, `cockroach` | [github.com/jackc/pgx/v5/stdlib][d-cockroachdb] |
| CrateDB | `cratedb` | `ct`, `crate` | [github.com/jackc/pgx/v5/stdlib][d-cratedb] |
| SingleStore MemSQL | `memsql` | `me` | [github.com/go-sql-driver/mysql][d-memsql] |
| Presto | `presto` | `pr`, `prestodb` | [github.com/xo/dbimp/trino][d-presto] |
| Amazon Redshift | `redshift` | `rs` | [github.com/jackc/pgx/v5/stdlib][d-redshift] <sup>[¶][f-hosted]</sup> |
| TiDB | `tidb` | `ti` | [github.com/go-sql-driver/mysql][d-tidb] |
| Trino | `trino` | `tr` | [github.com/xo/dbimp/trino][d-trino] |
| Vitess Database | `vitess` | `vt` | [github.com/go-sql-driver/mysql][d-vitess] |
| | | | |
| Alibaba MaxCompute | `maxcompute` | `mc` | [github.com/xo/dbimp/maxcompute][d-maxcompute] <sup>[¶][f-hosted]</sup> |
| Alibaba Tablestore | `tablestore` | `ot`, `ots` | [github.com/xo/dbimp/tablestore][d-tablestore] <sup>[¶][f-hosted]</sup> |
| Amazon Athena | `athena` | `s3`, `aws`, `awsathena` | [github.com/xo/dbimp/athena][d-athena] <sup>[¶][f-hosted]</sup> |
| Apache Avatica | `avatica` | `av`, `phoenix` | [github.com/xo/dbimp/avatica][d-avatica] |
| Apache Drill | `drill` | `dl` | [github.com/xo/dbimp/drill][d-drill] |
| Apache Druid | `druid` | `dr` | [github.com/xo/dbimp/druid][d-druid] |
| Apache Hive | `hive` | `hi`, `hive2` | [github.com/beltran/gohive/v2][d-hive] |
| Apache Impala | `impala` | `im` | [github.com/sclgo/impala-go][d-impala] |
| Apache Pinot | `pinot` | `pi` | [github.com/xo/dbimp/pinot][d-pinot] |
| Apache Solr | `solr` | `so` | [github.com/xo/dbimp/solr][d-solr] |
| ArangoDB | `arangodb` | `ar`, `arango` | [github.com/xo/dbimp/arangodb][d-arangodb] |
| Azure Cosmos DB | `cosmos` | `cm` | [github.com/xo/dbimp/cosmos][d-cosmos] <sup>[¶][f-hosted]</sup> |
| Cassandra | `cassandra` | `ca`, `cql`, `scy`, `cass`, `scylla`, `datastax` | [github.com/xo/cassandra][d-cassandra] |
| ChaiSQL | `chai` | `ci`, `genji`, `chaisql` | [github.com/chaisql/chai][d-chai] <sup>[§][f-embedded]</sup> |
| Couchbase | `couchbase` | `n1`, `n1ql` | [github.com/xo/dbimp/couchbase][d-couchbase] |
| CSVQ | `csvq` | `cs`, `csv`, `tsv`, `json` | [github.com/mithrandie/csvq-driver][d-csvq] <sup>[§][f-embedded]</sup> |
| Databend | `databend` | `dd`, `bend` | [github.com/xo/dbimp/databend][d-databend] |
| Databricks | `databricks` | `br`, `brick`, `bricks`, `databrick` | [github.com/xo/dbimp/databricks][d-databricks] <sup>[¶][f-hosted]</sup> |
| DynamoDB | `dynamodb` | `dy`, `dyn`, `dynamo` | [github.com/xo/dbimp/dynamodb][d-dynamodb] |
| Elasticsearch | `elasticsearch` | `es`, `elastic` | [github.com/xo/dbimp/elasticsearch][d-elasticsearch] |
| Exasol | `exasol` | `ex`, `exa` | [github.com/xo/dbimp/exasol][d-exasol] |
| Firebird | `firebird` | `fb`, `firebirdsql` | [github.com/nakagami/firebirdsql][d-firebird] |
| FlightSQL | `flightsql` | `fl`, `flight` | [github.com/apache/arrow-go/v18/arrow/flight/flightsql/driver][d-flightsql] |
| Google BigQuery | `bigquery` | `bq` | [github.com/xo/dbimp/bigquery][d-bigquery] <sup>[¶][f-hosted]</sup> |
| Google Spanner | `spanner` | `sp` | [github.com/xo/dbimp/spanner][d-spanner] <sup>[¶][f-hosted]</sup> |
| InfluxDB | `influxdb` | `in`, `influx` | [github.com/xo/dbimp/influxdb][d-influxdb] |
| InfluxDB InfluxQL | `influxql` | `iq` | [github.com/xo/dbimp/influxdb][d-influxql] |
| libSQL | `libsql` | `ls`, `turso` | [github.com/xo/dbimp/libsql][d-libsql] |
| ModernC SQLite3 | `moderncsqlite` | `mq`, `modernsqlite` | [modernc.org/sqlite][d-moderncsqlite] <sup>[§][f-embedded]</sup> |
| Neo4j | `neo4j` | `nj`, `neo`, `n4j` | [github.com/xo/dbimp/neo4j][d-neo4j] |
| ODBC | `odbc` | `od` | [github.com/xo/odbc][d-odbc] |
| OpenSearch | `opensearch` | `os`, `open` | [github.com/xo/dbimp/opensearch][d-opensearch] |
| PostgreSQL lib/pq | `libpq` | `pq` | [github.com/lib/pq][d-libpq] |
| QuestDB | `questdb` | `qs` | [github.com/jackc/pgx/v5/stdlib][d-questdb] |
| rqlite | `rqlite` | `rq` | [github.com/xo/dbimp/rqlite][d-rqlite] |
| SAP HANA | `saphana` | `sa`, `sap`, `hana`, `hdb` | [github.com/SAP/go-hdb/driver][d-saphana] |
| Snowflake | `snowflake` | `sf` | [github.com/xo/dbimp/snowflake][d-snowflake] <sup>[¶][f-hosted]</sup> |
| SurrealDB | `surrealdb` | `sr`, `sur`, `surreal` | [github.com/xo/dbimp/surrealdb][d-surrealdb] |
| Vertica | `vertica` | `ve` | [github.com/vertica/vertica-sql-go][d-vertica] |
| VoltDB | `voltdb` | `vo`, `vdb`, `volt` | [github.com/VoltDB/voltdb-client-go/voltdbclient][d-voltdb] |
| YDB | `ydb` | `yd` | [github.com/ydb-platform/ydb-go-sdk/v3][d-ydb] |
| | | | |
| | | | |
| | | | |
| Apache H2 | `h2` | | [github.com/jmrobles/h2go][d-h2] |
| GizmoSQL | `gizmosql` | `gz`, `gizmo` | [github.com/apache/arrow-go/v18/arrow/flight/flightsql/driver][d-gizmosql] |
| | | | |
| **NO DRIVERS** | `no_base` | | _no base drivers (useful for development)_ |
| **MOST DRIVERS** | `most` | | _all stable drivers_ |
| **ALL DRIVERS** | `all` | | _all drivers, excluding bad drivers_ |
| **BAD DRIVERS** | `bad` | | _bad drivers (broken/non-working drivers)_ |
| **NO <TAG>** | `no_<tag>` | | _exclude driver with `<tag>`_ |
[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/xo/dbimp
[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/xo/oracle
[d-pgx]: https://github.com/jackc/pgx
[d-pinot]: https://github.com/xo/dbimp
[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-tablestore]: 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
<!-- DRIVER DETAILS END -->
[f-cgo]: #f-cgo "Requires CGO"
[f-embedded]: #f-embedded "Embedded"
[f-hosted]: #f-hosted "Hosted service"
<p>
<i>
<a id="f-cgo"><sup>†</sup> Requires CGO</a><br>
<a id="f-embedded"><sup>§</sup> Embedded, with no server to run</a><br>
<a id="f-hosted"><sup>¶</sup> Hosted service, with no server you can run</a>
</i>
</p>
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:
<!-- EXAMPLE help -->
```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)
-U, --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
-M, --markdown markdown 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 <i>(for ODBC and ADODB)</i> |
| `user` | username |
| `pass` | password |
| `host` | hostname |
| `dbname` <sup>[±][f-path]</sup> | 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"
<p>
<i>
<a id="f-path">
<sup>±</sup> Some databases, such as Microsoft SQL Server, or Oracle
Database support a path component (ie, <code>/dbname</code>) in the form
of <code>/instance/dbname</code>, where <code>/instance</code> is the
optional service identifier (aka "SID") or database instance
</a>
</i>
</p>
#### 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/myproject"
$ usql "mc://accessid:accesskey@host/myproject?region=cn-hangzhou&tls=false"
# connect to an alibaba tablestore instance, where the host
# myinstance.cn-hangzhou becomes myinstance.cn-hangzhou.ots.aliyuncs.com:
$ usql "tablestore://accessid:accesssecret@myinstance.cn-hangzhou/myinstance"
# 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 config.yaml (see Configuration below for
# where it is on each system)
$ 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:
<!-- EXAMPLE queries -->
```sh
$ usql sqlserver://sa:pass@localhost
Connected with driver sqlserver (Microsoft SQL Server 2025 17.0.5005.3, RTM-CU9, Enterprise Developer Edition (64-bit))
Type "help" for help.
ms:sa@localhost=> select * from authors;
author_id | name
-----------+--------------
1 | Isaac Asimov
2 | Stephen King
(2 rows)
ms:sa@localhost=> select title from books
ms:sa@localhost-> where author_id = 2
ms:sa@localhost-> \p
select title from books
where author_id = 2
ms:sa@localhost-> \g
title
--------
Carrie
Cujo
(2 rows)
ms:sa@localhost=>
```
A command takes one or more parameters. A parameter can be quoted with either
`'` or `"`, and [can also be backticked][backticks].
#### How input becomes statements
`usql` reads input as `psql` does, on every database:
- A statement ends at a `;` that is outside a string, a quoted name and a
comment, or at a meta command such as `\g`.
- Write `\;` for a `;` that must not end the statement. `usql` sends it as `;`.
- The body of a stored program between `BEGIN` and `END`, and on Oracle an
anonymous block, stays in one statement, so its `;` need no escape. Inside a
PL/SQL package, or in the declarations of a unit before its `BEGIN`, write
each `;` as `\;`.
- A line with only `/` and SQL Server's `GO` are not separators. They are
text of the statement, as in `psql`.
- Input that is not typed, from `-c`, `-f`, a pipe or `\i`, runs a last
statement that has no `;`.
#### Line editing
The interactive interpreter edits lines with [`rline`][rline], which reads
`~/.inputrc` (or the file that `INPUTRC` names) as GNU Readline does. For
example, `set editing-mode vi` turns on vi mode, and
`"\e[A": history-search-backward` makes Up search the history for entries
that start with what is typed. Settings inside `$if usql` ... `$endif` apply
only to `usql`.
### 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 `\?`:
<!-- EXAMPLE commands -->
```sh
$ usql
Type "help" for help.
(not connected)=> \?
General
\q quit usql
\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
\Z close (disconnect) database connection
\password [USER] change password for user
\conninfo [-password] display information about the current database connection,
with the password when -password is given
Query Execute
\g [(OPTIONS)] [FILE] or ; execute query (and send results to file or |pipe)
\G [(OPTIONS)] [FILE] as \g, but forces vertical output mode
\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
\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
\p [-raw|-exec] show the contents of the query buffer, the raw
(non-interpolated) buffer or the exec buffer
\w [-raw|-exec] FILE write the contents of the query buffer, raw
(non-interpolated) buffer, or exec buffer to file
\r reset (clear) the query buffer
\ef [FUNCNAME [LINE]] edit function definition with external editor
\ev [VIEWNAME [LINE]] edit view definition with external editor
Informational
\d[S+] [NAME] list tables, views, and sequences or describe table, view,
sequence, or index
\da[S+] [PATTERN] list aggregates
\dA[+] [PATTERN] list access methods
\dAc[+] [AMPTRN [PATTERN]] list operator classes
\dAf[+] [AMPTRN [PATTERN]] list operator families
\dAo[+] [AMPTRN [PATTERN]] list operators of operator families
\dAp[+] [AMPTRN [PATTERN]] list support functions of operator families
\db[+] [PATTERN] list tablespaces
\dc[S+] [PATTERN] list conversions
\dconfig[+] [PATTERN] list configuration parameters
\dC[+] [PATTERN] list casts
\dd[S] [PATTERN] show object descriptions not displayed elsewhere
\dD[S+] [PATTERN] list domains
\ddp [PATTERN] list default privileges
\dE[S+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\det[+] [PATTERN] list foreign tables and their servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[anptw][S+] [PATTERN] list [only agg/normal/procedure/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[S+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl[+] list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dm[S+] [PATTERN] list materialized views
\dn[S+] [PATTERN] list schemas
\do[S+] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp[S] [PATTERN] list table, view, and sequence access privileges
\dP[itn+] [PATTERN] list [only index/table] partitioned relations [n=nested]
\drds [ROLEPTRN [DBPTRN]] list per-database role settings
\drg[S] [PATTERN] list role grants
\dRp[+] [PATTERN] list replication publications
\dRs[+] [PATTERN] list replication subscriptions
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[S+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dx[+] [PATTERN] list extensions
\dX [PATTERN] list extended statistics
\dy[+] [PATTERN] list event triggers
\l[+] [PATTERN] list databases
\z[S] [PATTERN] same as \dp
\sf[+] FUNCNAME show a function's definition
\sv[+] VIEWNAME show a view's definition
\ss[+] TABLE [k] show stats for the columns of a table
Variables
\set [NAME [VALUE]] set script variable, or show all script variables if no
parameters
\uset [NAME [VALUE]] set a setting of usql, show it if there is no value, or
show all settings if no parameters
\unset NAME unset (delete) script variable
\pset [NAME [VALUE]] set table print formatting option, or show all print
formatting options if no parameters
\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
\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
\ir FILE as \i, but relative to location of current script
\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
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
(not connected)=>
```
Parameters passed to commands [can be backticked][backticks].
## Features and Compatibility
An overview of `usql`'s features, functionality, and compatibility with `psql`:
- [Configuration][config]
- [Variables][variables]
- [Backticks][backticks]
- [Copying Between Databases][copying]
- [Syntax Highlighting][highlighting]
- [Time Formatting][timefmt]
- [Context Completion][completion]
- [Host Connection Information](#host-connection-information)
- [Passwords][usqlpass]
- [Runtime Configuration (RC) File][usqlrc]
The `usql` project's goal is to support as much of `psql`'s core features and
functionality, and aims to be as compatible as possible - [contributions are
always appreciated][contributing]!
#### Configuration
During its initialization phase, `usql` reads a standard [YAML configuration][yaml]
file [`config.yaml`](_samples/config.yaml). It reads the first of these:
1. The file that `--config` names.
2. The file that the `USQL_CONFIG` environment variable names.
3. `usql/config.yaml` in the configuration directory of the system:
`%AppData%/usql/config.yaml` on Windows,
`$HOME/Library/Application Support/usql/config.yaml` on macOS, and
`$XDG_CONFIG_HOME/usql/config.yaml` on Linux and other Unix systems, which
is `$HOME/.config/usql/config.yaml` when `XDG_CONFIG_HOME` is not set.
macOS does not read `XDG_CONFIG_HOME`. Set `USQL_CONFIG` to use another
file there.
##### `connections:`
[Named connection DSNs][connecting] can be defined under `connections:` as a string
or as a map:
```yaml
connections:
my_couchbase_conn: couchbase://Administrator:P4ssw0rd@localhost
my_clickhouse_conn: clickhouse://clickhouse:P4ssw0rd@localhost
my_oracle_conn:
protocol: oracle
username: system
password: P4ssw0rd
hostname: localhost
port: 1521
database: free
```
Defined `connections:` can be used on the command-line with `\connect`, `\c`,
`\copy`, and [other commands][commands]:
```sh
$ usql my_oracle_conn
Connected with driver oracle (Oracle Database 23.0.0.0.0)
Type "help" for help.
or:system@localhost/free=>
```
##### `init:`
An initialization script can be defined as `init:` as a string:
```yaml
init: |
\echo welcome to the jungle `date`
\uset syntax_hl_style paraiso-dark
\uset prompt1 '\033[32m%S%M%/%R%#\033[0m '
```
The `init:` script is commonly used to set [environment variables][variables]
or other configuration, and can be disabled on the command-line using the
`--no-init` / `-X` flag. The script will be executed prior to any `-c` /
`--command` / `-f` / `--file` flag and before starting the interactive
interpreter.
##### Other Options
Please see [`_samples/config.yaml`](_samples/config.yaml) for an overview of
available configuration options.
#### Variables
`usql` has [script][runtime-vars] variables, [connection][connection-vars]
variables, [display formatting][print-vars] variables, and [settings][settings]
of its own. They are set with `\set`, `\cset`, `\pset` and `\uset`
respectively, and `\set` is only for scripts: a setting such as `pager` is not
a variable, and `\set pager less` is an error that says to use `\uset`.
##### Runtime Variables
Script variables are managed with the `\set` and `\unset` [commands][commands]:
<!-- EXAMPLE set-unset -->
```sh
(not connected)=> \unset FOO
(not connected)=> \set FOO bar
(not connected)=>
```
Script variables can be displayed with `\set`:
<!-- EXAMPLE set-show -->
```sh
(not connected)=> \set
FOO = 'bar'
(not connected)=>
```
###### Variable Interpolation
When a script variable `NAME` has been `\set`, then `:NAME`, `:'NAME'`, and
`:"NAME"` will be interpolated into the query buffer:
<!-- EXAMPLE interpolate -->
```sh
ms:sa@localhost=> \set FOO 'Stephen King'
ms:sa@localhost=> select * from authors where name = :'FOO';
author_id | name
-----------+--------------
2 | Stephen King
(1 row)
ms:sa@localhost=>
```
Where a runtime variable is used as `:'NAME'` or `:"NAME"` the interpolated
value will be quoted using `'` or `"` respectively:
<!-- EXAMPLE interpolate-quoted -->
```sh
ms:sa@localhost=> \set TBLNAME authors
ms:sa@localhost=> \set COLNAME name
ms:sa@localhost=> \set FOO 'Isaac Asimov'
ms:sa@localhost=> select * from :TBLNAME where :"COLNAME" = :'FOO'
ms:sa@localhost->
```
The query buffer and interpolated values can be displayed with `\p` and
`\print`, or the raw query buffer can be displayed with `\raw`:
<!-- EXAMPLE interpolate-print -->
```sh
ms:sa@localhost=> select * from :TBLNAME where :"COLNAME" = :'FOO'
ms:sa@localhost-> \p
select * from authors where "name" = 'Isaac Asimov'
ms:sa@localhost-> \raw
select * from :TBLNAME where :"COLNAME" = :'FOO'
ms:sa@localhost->
```
<hr/>
> **Note**
>
> Variables contained within other strings <b><u>will not</b></u> be interpolated:
<!-- EXAMPLE not-interpolated -->
```sh
ms:sa@localhost=> select ':FOO' as foo;
foo
------
:FOO
(1 row)
ms:sa@localhost=> \p
select ':FOO' as foo;
ms:sa@localhost=>
```
<hr/>
##### Connection Variables
Connection variables work similarly to runtime variables, and are managed with
`\cset`. Connection variables can be used with the `\c`, `\connect`, `\copy`,
or [other commands][commands]:
<!-- EXAMPLE cset-connect -->
```sh
(not connected)=> \cset my_conn sqlserver://sa:pass@localhost
(not connected)=> \c my_conn
Connected with driver sqlserver (Microsoft SQL Server 2025 17.0.5005.3, RTM-CU9, Enterprise Developer Edition (64-bit))
ms:sa@localhost=>
```
Connection variables are not interpolated into queries. See the [configuration
section for information on defining persistent connection variables][config].
Connection variables can be displayed with `\cset`:
<!-- EXAMPLE cset-show -->
```sh
(not connected)=> \cset
my_conn = 'postgres://user:pass@localhost'
(not connected)=>
```
##### Display Formatting (Print) Variables
Display formatting variables can be set using `\pset` and [other
commands][commands]:
<!-- EXAMPLE pset-set -->
```sh
(not connected)=> \pset time Kitchen
Time display is "Kitchen" ("3:04PM").
(not connected)=> \a
Output format is unaligned.
(not connected)=>
```
Display formatting variables can be displayed with `\pset`:
<!-- EXAMPLE pset-show -->
```sh
(not connected)=> \pset
border 1
columns 0
csv_fieldsep ","
expanded off
fieldsep "|"
fieldsep_zero off
footer on
format aligned
header_align center
json_indent 0
linestyle ascii
locale C
null ""
numericlocale off
pager off
pager_min_lines 0
recordsep "\n"
recordsep_zero off
tableattr
time Kitchen
timezone
title
tuples_only off
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
(not connected)=>
```
##### Settings
The settings of `usql`, such as the pager, the editor, the prompts and
`on_error_stop`, are managed with the `\uset` [command][commands], which works as
`\pset` does. Without arguments it shows every setting, with a name it shows
that setting, and with a name and a value it sets it. The value is read as the
other meta commands read an argument: it can be quoted, `:name` is interpolated
and a backtick runs a command.
<!-- EXAMPLE uset -->
```sh
(not connected)=> \uset pager "less -R"
pager is "less -R"
(not connected)=> \uset on_error_stop on
on_error_stop is on
(not connected)=> \uset
echo_hidden off
editor ""
fetch_count 0
on_error_stop on
pager "less -R"
prompt1 "%S%N%m%/%R%# "
prompt2 "%S%N%m%/%R%# "
quiet off
show_host_information true
sslmode retry
syntax_hl true
syntax_hl_format terminal256
syntax_hl_override_bg true
syntax_hl_style monokai
term_graphics
(not connected)=>
```
A setting can also be given on the command line with `--uset NAME=VALUE`, or
`-U NAME=VALUE`, and `-q` is `--uset quiet=on`. The environment variables
`USQL_PAGER`, `PAGER`, `USQL_EDITOR`, `EDITOR`, `VISUAL`, `USQL_SSLMODE`,
`USQL_SHOW_HOST_INFORMATION` and `USQL_TERM_GRAPHICS` give the first value of
their settings.
Use the `\? variables` [command][commands] to display help information and to
list the variables and the settings recognized by `usql`:
```sh
(not connected)=> \? variables
```
#### Backticks
[Backslash (`\`) meta commands][commands] support backticks on parameters:
<!-- EXAMPLE backticks-echo -->
```sh
(not connected)=> \echo Welcome `echo $USER` -- 'running on:' "(" `uname -s` ")"
Welcome user -- running on: ( Linux )
(not connected)=>
```
Backticked parameters will be passed to the user's `SHELL`, exactly as written,
and can be combined with `\set`:
<!-- EXAMPLE backticks-set -->
```sh
(not connected)=> \set MYVAR `uname -s`
(not connected)=> \set
MYVAR = 'Linux'
(not connected)=> \echo :MYVAR
Linux
(not connected)=>
```
#### Copying Between Databases
`usql` provides a `\copy` command that reads data from a source database DSN
and writes to a destination database DSN:
```sh
(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)'
```
As demonstrated above, the `\copy` command does not require being connected to
a database, and will not modify or change the current open database connection
or state.
Any valid URL or DSN name maybe used for the source and destination database:
```sh
(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)'
```
<hr/>
> **Note**
>
> `usql`'s `\copy` is distinct from and <b><u>does not</u></b> function like
> `psql`'s `\copy`.
<hr/>
##### Copy Parameters
The `\copy` command has two parameter forms:
```txt
\copy SRC DST QUERY TABLE
\copy SRC DST QUERY TABLE(COL1, COL2, ..., COLN)
```
Where:
- `SRC` - is the [source database URL][connecting] to connect to, and where the
`QUERY` will be executed
- `DST` - is the [destination database URL][connecting] to connect to, and where
the destination `TABLE` resides
- `QUERY` - is the query to execute on the `SRC` connection, the results of which
will be copied to `TABLE`
- `TABLE` - is the destination table name, followed by an optional SQL-like column
list of the form `(COL1, COL2, ..., COLN)`
- `(COL1, COL2, ..., COLN)` - a list of the destination column names, 1-to-N
The usual rules for [variables, interpolation, and quoting][variables] apply to
`\copy`'s parameters.
###### Quoting
`QUERY` and `TABLE` **_must_** be quoted when containing spaces:
```sh
$ 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
```
###### Column Counts
The `QUERY` **_must_** return the same number of columns as defined by
the `TABLE` expression:
```sh
$ 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
```
###### Datatype Compatibility and Casting
The `\copy` command does not attempt to perform any kind of datatype
conversion.
If a `QUERY` returns columns with different datatypes than expected by the
`TABLE`'s column, the `QUERY` can use the source database's conversion/casting
functionality to cast columns to a datatype that will work for `TABLE`'s
columns:
```sh
$ usql
(not connected)=> \copy postgres://user:pass@localhost mysql://user:pass@localhost 'SELECT uuid_column::TEXT FROM myPgTable' myMyTable
COPY 1
```
###### Importing Data from CSV
The `\copy` command is capable of importing data from CSV's (or any other
database!) using the `csvq` driver:
<!-- EXAMPLE copy-csv -->
```sh
$ 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
Type "help" for help.
(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 (SQLite 3.53.4)
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 (SQLite 3.53.4)
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)
sq:booktest.db=>
```
<hr/>
> **Note**
>
> When importing large datasets (> 1GiB) from one database to another, it is
> better to use a database's native clients and tools.
<hr/>
###### Reusing Connections with Copy
The `\copy` command (and all `usql` commands) [works with variables][variables].
When scripting, or when needing to perform multiple `\copy` operations from/to
multiple sources/destinations, the best practice is to `\set` connection
variables either in a script or in [the `$HOME/.usqlrc` RC script][usqlrc].
Similarly, passwords can be kept out of scripts by defining [named
connections][connection-vars] in [the `config.yaml` file][config]. The
[`$HOME/.usqlpass` password file][usqlpass] also works, but it is deprecated.
For example:
```sh
$ cat $HOME/.usqlpass
postgres:*:*:*:postgres:P4ssw0rd
oracle:*:*:*:system:P4ssw0rd
$ usql
Type "help" for help.
(not connected)=> \set pglocal postgres://postgres@localhost:49153?sslmode=disable
(not connected)=> \set orlocal oracle://system@localhost:1521/orasid
(not connected)=> \copy :pglocal :orlocal 'select staff_id, first_name from staff' 'staff(staff_id, first_name)'
COPY 18
```
#### Syntax Highlighting
Interactive input is highlighted as you type it. `usql` parses the input with
the grammar of [transit][transit], and the SQL of each statement with the grammar
of the dialect of the connection. The colors come from a [style][styles] of
transit, which has the styles of Chroma. The [settings][settings] that control
highlighting are:
| Setting | Default | Values | Description |
| ----------------------- | ------------------------------- | ------------------------------------------------ | -------------------------------------------------------- |
| `syntax_hl` | `true` | `true` or `false` | turns highlighting on and off |
| `syntax_hl_format` | _dependent on terminal support_ | `noop`, `terminal`, `terminal256`, `terminal16m` | the number of colors: none, 16, 256 or 24 bits |
| `syntax_hl_override_bg` | `true` | `true` or `false` | keeps the background of the terminal, and not the style's |
| `syntax_hl_style` | `monokai` | style name | the [name of a style][styles] |
They are set with `\uset`. A terminal with no color, or a `NO_COLOR` variable,
turns highlighting off by default:
<!-- EXAMPLE syntax-highlighting -->
```sh
$ usql
Type "help" for help.
(not connected)=> \uset syntax_hl_style dracula
syntax_hl_style is dracula
(not connected)=> \uset syntax_hl off
syntax_hl is false
(not connected)=>
```
#### Context Completion
When using the interactive shell, context completion is available in `usql` by
hitting the `<Tab>` key. For example, hitting `<Tab>` can complete some parts
of `SELECT` queries on a SQL Server database:
<!-- EXAMPLE completion-select -->
```sh
$ usql sqlserver://sa:pass@localhost
Connected with driver sqlserver (Microsoft SQL Server 2025 17.0.5005.3, RTM-CU9, Enterprise Developer Edition (64-bit))
Type "help" for help.
ms:sa@localhost=> select * f
1 from
2 fetch
3 full outer join
```
Or, for example completing [backslash commands][commands] while connected to a
database:
<!-- EXAMPLE completion-meta -->
```sh
$ usql sqlserver://sa:pass@localhost
Connected with driver sqlserver (Microsoft SQL Server 2025 17.0.5005.3, RTM-CU9, Enterprise Developer Edition (64-bit))
Type "help" for help.
ms:sa@localhost=> \g
1 \g 4 \gset
2 \go 5 \gexec
3 \gx 6 \getenv
```
Not all commands, contexts, or databases support completion. To help improve
it, see [the section below on contributing][contributing].
Command completion can be canceled with `<Control-C>`.
#### Time Formatting
Some databases support time/date columns that [support formatting][go-time]. By
default, `usql` formats time/date columns as [RFC3339Nano][go-time], and can be
set using `\pset time FORMAT`:
<!-- EXAMPLE time -->
```sh
$ usql sqlserver://sa:pass@localhost
Connected with driver sqlserver (Microsoft SQL Server 2025 17.0.5005.3, RTM-CU9, Enterprise Developer Edition (64-bit))
Type "help" for help.
ms:sa@localhost=> \pset time
Time display is "RFC3339Nano" ("2006-01-02T15:04:05.999999999Z07:00").
ms:sa@localhost=> select cast('2026-10-31 09:41:27.123456' as datetime2) as stamp;
stamp
-----------------------------
2026-10-31T09:41:27.123456Z
(1 row)
ms:sa@localhost=> \pset time Kitchen
Time display is "Kitchen" ("3:04PM").
ms:sa@localhost=> select cast('2026-10-31 09:41:27.123456' as datetime2) as stamp;
stamp
--------
9:41AM
(1 row)
ms:sa@localhost=>
```
`usql`'s time format supports any [Go supported time format][go-time], or can
be any standard Go const name, such as `Kitchen` above. See below for an
overview of the [available time constants](#time-constants).
##### Time Constants
The following are the time constant names available in `usql`, corresponding
time format value, and example display output:
| Constant | Format | Display <sup>[↓][f-ts]</sup> |
| ----------- | ------------------------------------: | ----------------------------------: |
| ANSIC | `Mon Jan _2 15:04:05 2006` | `Wed Aug 3 20:12:48 2022` |
| UnixDate | `Mon Jan _2 15:04:05 MST 2006` | `Wed Aug 3 20:12:48 UTC 2022` |
| RubyDate | `Mon Jan 02 15:04:05 -0700 2006` | `Wed Aug 03 20:12:48 +0000 2022` |
| RFC822 | `02 Jan 06 15:04 MST` | `03 Aug 22 20:12 UTC` |
| RFC822Z | `02 Jan 06 15:04 -0700` | `03 Aug 22 20:12 +0000` |
| RFC850 | `Monday, 02-Jan-06 15:04:05 MST` | `Wednesday, 03-Aug-22 20:12:48 UTC` |
| RFC1123 | `Mon, 02 Jan 2006 15:04:05 MST` | `Wed, 03 Aug 2022 20:12:48 UTC` |
| RFC1123Z | `Mon, 02 Jan 2006 15:04:05 -0700` | `Wed, 03 Aug 2022 20:12:48 +0000` |
| RFC3339 | `2006-01-02T15:04:05Z07:00` | `2022-08-03T20:12:48Z` |
| RFC3339Nano | `2006-01-02T15:04:05.999999999Z07:00` | `2022-08-03T20:12:48.693257Z` |
| Kitchen | `3:04PM` | `8:12PM` |
| Stamp | `Jan _2 15:04:05` | `Aug 3 20:12:48` |
| StampMilli | `Jan _2 15:04:05.000` | `Aug 3 20:12:48.693` |
| StampMicro | `Jan _2 15:04:05.000000` | `Aug 3 20:12:48.693257` |
| StampNano | `Jan _2 15:04:05.000000000` | `Aug 3 20:12:48.693257000` |
[f-ts]: #f-ts "Timestamp Value"
<p>
<i>
<a id="f-ts"><sup>↓</sup> Generated using timestamp <code>2022-08-03T20:12:48.693257Z</code></a><br>
</i>
</p>
#### Host Connection Information
By default, `usql` displays connection information when it connects to a
database. Some databases and connections do not work with this. Set the
environment variable `USQL_SHOW_HOST_INFORMATION` to `false` to turn it off:
<!-- EXAMPLE host-information-env -->
```sh
$ export USQL_SHOW_HOST_INFORMATION=false
$ usql sqlserver://sa:pass@localhost
Type "help" for help.
ms:sa@localhost=>
```
`show_host_information` is a [setting][settings] of `usql`, and is set with
`\uset`. Additionally, it can be passed via the command-line using `--uset`:
<!-- EXAMPLE host-information-uset -->
```sh
$ usql --uset show_host_information=false sqlserver://sa:pass@localhost
Type "help" for help.
ms:sa@localhost=> \uset show_host_information true
show_host_information is true
ms:sa@localhost=> \connect sqlserver://sa:pass@localhost
Connected with driver sqlserver (Microsoft SQL Server 2025 17.0.5005.3, RTM-CU9, Enterprise Developer Edition (64-bit))
ms:sa@localhost=>
```
#### Terminal Graphics
`usql` supports terminal graphics for [Kitty][kitty-graphics], [iTerm][iterm-graphics],
and [Sixel][sixel-graphics] enabled terminals using the [`github.com/kenshaw/rasterm` package][rasterm].
Terminal graphics are only available when using the interactive shell.
##### Detection and Support
`usql` will attempt to detect when terminal graphics support is available using
the `USQL_TERM_GRAPHICS`, `TERM_GRAPHICS` and other environment variables
unique to various terminals.
When support is available, the logo will be displayed at the start of an
interactive session:
<div style="padding-left: 20px;">
<img src="https://raw.githubusercontent.com/xo/usql-logo/main/usql-interactive.png" height="120">
</div>
##### Charts and Graphs
The [`\chart` command][chart-command] can be used to display a chart
directly in the terminal:
<div style="padding-left: 20px;">
<img src="https://raw.githubusercontent.com/xo/usql-logo/main/chart-example.png" height="120">
</div>
See [the section on the `\chart` meta command][chart-command] for details.
Charts are not part of a default build. The renderer runs Apache ECharts in a
JavaScript engine and rasterizes the result with an SVG renderer, which together
add about 12 MiB to the binary, so it is behind the `charts` build tag. Build
with `-tags charts`, or with `-tags all`, to include it. A build without it
reports that it has no chart renderer.
##### Enabling/Disabling Terminal Graphics
Terminal graphics can be forced enabled or disabled by setting the
`USQL_TERM_GRAPHICS` or the `TERM_GRAPHICS` environment variable:
```sh
# disable
$ USQL_TERM_GRAPHICS=none usql
# force iterm graphics
$ TERM_GRAPHICS=iterm usql
```
| Variable | Default | Values | Description |
| --------------- | ------- | ------------------------------------- | ------------------------------ |
| `TERM_GRAPHICS` | `` | ``, `kitty`, `iterm`, `sixel`, `none` | enables/disables term graphics |
##### Terminals with Graphics Support
The following terminals have been tested with `usql`:
- [WezTerm][wezterm] is a cross-platform terminal for Windows, macOS, Linux, and
many other platforms that supports [iTerm][iterm-graphics] graphics
- [iTerm2][iterm2] is a macOS terminal that supports [iTerm][iterm-graphics]
graphics
- [kitty][kitty] is a terminal for Linux, macOS, and various BSDs that supports
[Kitty][kitty-graphics] graphics
- [foot][foot] is a Wayland terminal for Linux (and other Wayland hosts) that
supports [Sixel][sixel-graphics] graphics
Additional terminals that support [Sixel][sixel-graphics] graphics are
catalogued on the [Are We Sixel Yet?][arewesixelyet] website.
#### Passwords
`usql` supports reading passwords for databases from a `.usqlpass` file
contained in the user's `HOME` directory at startup:
```sh
$ 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@=>
```
See [`_samples/usqlpass`](_samples/usqlpass) for a sample.
An entry applies to a URL when each of its fields is `*` or equal to the
URL's. An entry never replaces a user that the URL names, and an entry for one
database applies only to that database. The protocol matches every scheme of
the same database, so a `postgres:` entry also covers `pg://`, `pgx://` and
`pq://`. A database that only speaks another's protocol is a database of its
own, so CockroachDB, CrateDB and Redshift each need their own entry, and so do
TiDB, SingleStore and Vitess. A `mysql:` entry does not cover `tidb://`.
The `.usqlpass` file is deprecated, and `usql` prints a warning when it reads a
password from it. [Define named connections][connection-vars] in [the
`config.yaml` file][config] instead.
<hr/>
> **Note**
>
> The `.usqlpass` file must not be readable by other users. Set its permissions
> to `0600`:
```sh
chmod 0600 ~/.usqlpass
```
<hr/>
#### Runtime Configuration (RC) File
`usql` supports executing a `.usqlrc` runtime configuration (RC) file contained
in the user's `HOME` directory:
```sh
$ cat $HOME/.usqlrc
\echo WELCOME TO THE JUNGLE `date`
\uset syntax_hl_style paraiso-dark
-- set color prompt (default is prompt is "%S%m%/%R%#" )
\uset 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)=> \uset syntax_hl_style
syntax_hl_style is "paraiso-dark"
(not connected)=>
```
See [`_samples/usqlrc`](_samples/usqlrc) for a sample.
The `.usqlrc` file is read 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.
RC-file execution can be temporarily disabled at startup by passing `-X` or
`--no-init` on the command-line:
```sh
$ usql --no-init pg://
```
While the `.usqlrc` functionality will not be removed, it is recommended to set
an `init` script in [the `config.yaml` file][config].
## Additional Notes
The following are additional notes and miscellania related to `usql`:
### Release Builds
[Release builds][releases] are built with the `most` build tag and with
additional [SQLite3 build tags (see: `build.sh`)](build.sh).
### macOS
The recommended installation method on macOS is [via `brew`][via Homebrew] due
to the way library dependencies for the `sqlite3` driver are done on macOS. If
the following (or similar) error is encountered when attempting to run `usql`:
```sh
$ 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 missing library dependency can be fixed by installing
[`icu4c`](http://site.icu-project.org) using `brew`:
```sh
$ brew install icu4c
Running `brew update --auto-update`...
==> Downloading ...
...
$ usql
(not connected)=>
```
### Couchbase FAQ
#### Why does a transaction fail to commit with error 17007?
The server could not meet the durability that the transaction asked for. By
default a transaction asks for `majority`, which a single-node server cannot
meet. `usql` does not change the durability unless you ask it to. Set `durability_level=none` in the URL for a single-node server,
such as a local development server:
```sh
$ usql "couchbase://user:pass@localhost/?durability_level=none"
```
The other values are `majority`, `majorityAndPersistActive` and
`persistToMajority`.
#### Why does a transaction stay open longer than the server's default?
The server ends a transaction after 15 seconds by default, which is too short
for a person typing into one. `usql` sets `txtimeout=30m` when the URL does not
set `txtimeout`. Set it in the URL to choose another value:
```sh
$ usql "couchbase://user:pass@localhost/?txtimeout=2m"
```
## Contributing
`usql` is currently a WIP, and is aiming towards a 1.0 release soon. Report a
bug, ask a question, or propose a feature in [GitHub
Discussions][discussions]. The repository takes no issues and no pull
requests. For [technical details, see CONTRIBUTING.md](CONTRIBUTING.md).
<br/>
<div align="center">
<a href="https://github.com/xo/usql" title="A command line client for many databases, this project">usql</a> |
<a href="https://github.com/xo/dburl" title="Database connection URLs">dburl</a> |
<a href="https://github.com/xo/dbmeta" title="Database metadata">dbmeta</a> |
<a href="https://github.com/xo/dbimp" title="Database drivers in pure Go">dbimp</a> |
<a href="https://github.com/xo/cassandra" title="A database/sql driver for Cassandra">cassandra</a> |
<a href="https://github.com/xo/dbtpl" title="Go code generated from a database">dbtpl</a> |
<a href="https://github.com/xo/tblfmt" title="Tables of database results">tblfmt</a> |
<a href="https://github.com/xo/rline" title="The line editor of usql">rline</a> |
<a href="https://github.com/xo/transit" title="tree-sitter in pure Go">transit</a>
</div>
[dburl]: https://github.com/xo/dburl
[dburl-schemes]: https://github.com/xo/dburl#protocol-schemes-and-aliases
[go-time]: https://pkg.go.dev/time#pkg-constants
[go-sql]: https://pkg.go.dev/database/sql
[homebrew]: https://brew.sh/
[xo-tap]: https://github.com/xo/homebrew-xo
[discussions]: https://github.com/xo/usql/discussions
[aur]: https://aur.archlinux.org/packages/usql
[aur-bin]: https://aur.archlinux.org/packages/usql-bin
[yay]: https://github.com/Jguer/yay
[arch-makepkg]: https://wiki.archlinux.org/title/makepkg
[backticks]: #backticks "Backticks"
[config]: #configuration "Configuration"
[commands]: #backslash-commands "Backslash Commands"
[completion]: #context-completion "Context Completion"
[connecting]: #connecting-to-databases "Connecting to Databases"
[contributing]: #contributing "Contributing"
[copying]: #copying-between-databases "Copying Between Databases"
[highlighting]: #syntax-highlighting "Syntax Highlighting"
[transit]: https://github.com/xo/transit "transit"
[styles]: https://github.com/xo/transit/tree/master/styles/chroma "Styles of transit"
[termgraphics]: #terminal-graphics "Terminal Graphics"
[timefmt]: #time-formatting "Time Formatting"
[usqlpass]: #passwords "Passwords"
[usqlrc]: #runtime-configuration-rc-file "Runtime Configuration File"
[variables]: #variables "Variables"
[settings]: #settings "Settings"
[runtime-vars]: #runtime-variables "Runtime Variables"
[connection-vars]: #connection-variables "Connection Variables"
[print-vars]: #display-formatting-(print)-variables "Display Formatting (print) Variables"
[kitty-graphics]: https://sw.kovidgoyal.net/kitty/graphics-protocol.html
[iterm-graphics]: https://iterm2.com/documentation-images.html
[sixel-graphics]: https://saitoha.github.io/libsixel/
[rasterm]: https://github.com/kenshaw/rasterm
[wezterm]: https://wezfurlong.org/wezterm/
[iterm2]: https://iterm2.com
[foot]: https://codeberg.org/dnkl/foot
[kitty]: https://sw.kovidgoyal.net/kitty/
[arewesixelyet]: https://www.arewesixelyet.com
[chart-command]: #chart-command "\\chart meta command"
[yaml]: https://yaml.org
[rline]: https://github.com/xo/rline