usql

https://github.com/xo/usql
Admin
usql is a universal command-line interface for SQL and NoSQL databases, providing a unified CLI...
Tokens:57,965
Snippets:407
Trust Score:7.1
License:MIT
Update:3 weeks ago
Show doc for...
Tokens:
Raw
### Install usql with Go

Source: https://github.com/xo/usql/blob/main/README.md

Installs the latest usql version with base drivers using go install. Use the -tags most flag to include most drivers; see the building section for more build tag information.

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

--------------------------------

### Install usql with base drivers

Source: https://github.com/xo/usql/blob/main/README.md

Installs usql with the default base drivers (PostgreSQL, MySQL, SQLite3, Microsoft SQL Server, Oracle, CSVQ) using 'go install'.

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

--------------------------------

### Basic usql usage examples

Source: https://github.com/xo/usql/blob/main/README.md

Shows how to connect to different databases (PostgreSQL, Oracle) and execute a script file. The examples use the usql command with a DSN and the -f flag to run commands from a file.

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

--------------------------------

### Install usql with additional drivers via build tags

Source: https://github.com/xo/usql/blob/main/README.md

Installs usql with base drivers plus Avatica and ODBC drivers by specifying the 'avatica' and 'odbc' build tags.

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

--------------------------------

### Install usql with Homebrew

Source: https://github.com/xo/usql/blob/main/README.md

Installs usql with most drivers using the Homebrew tap xo/xo. Use the --with-odbc flag to enable ODBC database support.

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

```sh
# add xo tap
$ brew tap xo/xo

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

--------------------------------

### Install usql from AUR with yay

Source: https://github.com/xo/usql/blob/main/README.md

Installs the pre-built usql binary using yay -S usql-bin, or builds from source with yay -S usql (takes longer). Alternatively, clone the AUR package and run makepkg -si.

```sh
# install usql with most drivers
$ yay -S usql-bin
```

```sh
# build usql with most drivers from source
$ yay -S usql
```

```sh
$ git clone https://aur.archlinux.org/usql.git && cd usql
$ makepkg -si
```

--------------------------------

### Install usql with most or all drivers

Source: https://github.com/xo/usql/blob/main/README.md

Shows two commands: one to build with most drivers (excluding CGO and problematic drivers) and one to build with all drivers (including CGO and problematic drivers).

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

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

--------------------------------

### Start podman socket and set DOCKER_HOST

Source: https://github.com/xo/usql/blob/main/CONTRIBUTING.md

With podman, point the tests at the podman socket first. dockertest speaks the Docker API, and podman serves it.

```bash
systemctl --user start podman.socket
```

```bash
export DOCKER_HOST=unix:///run/user/1000/podman/podman.sock
```

--------------------------------

### Time formatting with \pset time

Source: https://github.com/xo/usql/blob/main/README.md

Shows how to change the time format using \pset time, with examples of RFC3339Nano and Kitchen formats and their output.

```sh
$ usql pg://
Connected with driver postgres (PostgreSQL 13.2 (Debian 13.2-1.pgdg110+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@=>
```

--------------------------------

### Install unixODBC

Source: https://github.com/xo/usql/blob/main/contrib/db2/README.md

Install unixODBC on Debian/Ubuntu using aptitude or on Arch using yay. Required before installing the Db2 driver.

```sh
$ sudo aptitude install unixodbc unixodbc-bin unixodbc-dev
$ yay -S unixodbc

```

--------------------------------

### Create and use a .usqlrc file

Source: https://github.com/xo/usql/blob/main/README.md

Shows how to create a .usqlrc file in the user's HOME directory and then start usql. The file sets a syntax highlighting style, a color prompt, and prints a welcome message. The output shows the welcome message and the result of the \set command.

```sh
$ 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)=>
```

--------------------------------

### Install icu4c with brew

Source: https://github.com/xo/usql/blob/main/README.md

Installs the missing icu4c library using Homebrew. The output shows the installation process and then a successful usql startup.

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

$ usql
(not connected)=>
```

--------------------------------

### Verify live connection with usql

Source: https://github.com/xo/usql/blob/main/docs/DRIVER.md

Start the database, then run these commands and keep the output. The second command reports that describe commands are not supported because a new driver has no metadata reader.

```shell
usql <dsn> -c 'select 1'
```

```shell
usql <dsn> -c '\dt'
```

--------------------------------

### Start a postgres-18 container with dbrun

Source: https://github.com/xo/usql/blob/main/CONTRIBUTING.md

Starts a database container using dbmeta's dbrun command. It runs from a dbmeta checkout because the test module there has a replace directive. A bare product name starts the newest release, and postgres-12 names one release.

```bash
git clone https://github.com/xo/dbmeta
```

```bash
cd dbmeta/test
```

```bash
go run ./cmd/dbrun start postgres-18
```

--------------------------------

### Download and install dsdriver

Source: https://github.com/xo/usql/blob/main/contrib/db2/README.md

Verify the downloaded Db2 driver package and run the installer script. The output shows the expected path to the tarball.

```sh
$ ls ~/Downloads/ibm_data_server_driver_package_linuxx64_v11.5.tar.gz
/home/ken/Downloads/ibm_data_server_driver_package_linuxx64_v11.5.tar.gz
$ sudo ./install-dsdriver.sh

```

--------------------------------

### Install usql excluding specific drivers

Source: https://github.com/xo/usql/blob/main/README.md

Installs usql with most drivers but excludes Avatica, Couchbase, and PostgreSQL using the 'no_' build tags.

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

--------------------------------

### Connect to PostgreSQL, MySQL, SQL Server, Oracle, Cassandra, MaxCompute, SQLite, and ODBC databases

Source: https://github.com/xo/usql/blob/main/README.md

Shows example usql connection strings for various database drivers, including optional parameters like sslmode, and notes about SQLite file creation and ODBC configuration. The MaxCompute project option is required; unrecognized options are sent as SQL hints.

```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 cql://host/
$ usql ca://

# connect to an alibaba maxcompute project over https, or over http with mc+http:
$ usql "mc://accessid:accesskey@service.cn-hangzhou.maxcompute.aliyun.com/api?project=myproject"
$ usql "mc+http://accessid:accesskey@host/api?project=myproject"

# 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 (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
```

--------------------------------

### Run full test suite with -p 1

Source: https://github.com/xo/usql/blob/main/CONTRIBUTING.md

Runs the whole suite one package at a time. Use -p 1 because Go runs packages in parallel by default, and three packages each start a SQL Server container, which can exhaust memory on a normal workstation and cause timeouts.

```bash
go test -p 1 -tags "$(./build.sh -T)" ./...
```

--------------------------------

### Verify driver module hygiene with a real install

Source: https://github.com/xo/usql/blob/main/CONTRIBUTING.md

Confirm module hygiene by performing a real install in a temporary directory. This catches issues like an exclude or replace directive in the driver's own go.mod, a missing go.mod, or a +incompatible version that will not resolve.

```shell
cd $(mktemp -d) && go install github.com/xo/usql@latest
```

--------------------------------

### Minimal usql driver package with drivers.Register

Source: https://github.com/xo/usql/blob/main/docs/DRIVER.md

Minimal driver package for usql. The init() function calls drivers.Register with a drivers.Driver struct. The // DRIVER comment on the driver import is required for other tools to find the driver package.

```go
package mydb

import (
    _ "github.com/example/mydb-go-driver" // DRIVER
    "github.com/xo/usql/drivers"
)

func init() {
    drivers.Register("mydb", drivers.Driver{
        AllowMultilineComments: true,
        Process:                drivers.StripTrailingSemicolon,
    })
}
```

--------------------------------

### Install matching duckdb toolchain with winget

Source: https://github.com/xo/usql/blob/main/CONTRIBUTING.md

Installs the MinGW-Builds GCC 14.2.0 toolchain (x86_64, UCRT, posix-seh) required to link the duckdb driver's prebuilt static archives. The package lands under %LOCALAPPDATA%\Microsoft\WinGet\Packages\ in a mingw64\bin directory, and go build finds gcc there automatically.

```shell
winget install --id BrechtSanders.WinLibs.POSIX.UCRT.LLVM --exact --silent
```

--------------------------------

### Read passwords from .usqlpass file

Source: https://github.com/xo/usql/blob/main/README.md

usql reads passwords from ~/.usqlpass at startup. The example shows the file contents and the resulting connection output.

```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@=>
```

--------------------------------

### usql --help output

Source: https://github.com/xo/usql/blob/main/README.md

Displays the full help text for the usql command, including usage, arguments, and all supported flags. This is useful for discovering available options and their descriptions.

```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 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
```
Tokens:
Raw