USQL(1)

NAME
usql - A universal command-line interface for SQL, NoSQL, and other databases

DESCRIPTION
usql is a universal command-line interface for PostgreSQL, MySQL, MariaDB, Oracle Database, TiDB, SQLite3, Microsoft SQL Server, and many other databases including NoSQL and non-relational databases! Inspired by PostgreSQL's powerful command-line tool psql.

usql provides a simple way to interact with numerous database systems using a consistent interface. It supports most core psql features, such as variables, backticks, and backslash commands, while adding unique capabilities like syntax highlighting, context-based completion, native multi-database support via standard URL connection strings, copying data between different database systems, terminal graphics support, and more.

Database administrators and developers who prefer a psql -like experience for non-PostgreSQL databases will find usql intuitive and powerful. It supports connecting to various database drivers (see the \drivers command within usql), executing SQL commands, running scripts from files, and formatting output in various styles (aligned, unaligned, HTML, JSON, CSV, vertical, etc.).

OPTIONS

-c, --command COMMAND
    Run only single command (SQL or internal) and exit.

-f, --file FILE
    execute commands from file and exit

-w, --no-password
    never prompt for password

-X, --no-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.

CONNECTING TO DATABASES
usql connects to databases using Data Source Name (DSN) URLs, typically in the format:

    driver[+transport]://[user[:pass]@][host][:port][/dbname][?opt1=val1&opt2=val2]

Where:

    driver
        Is the primary scheme or alias for the database driver (e.g., `postgres`, `pg`, `mysql`, `my`, `sqlserver`, `ms`, `oracle`, `or`, `sqlite3`, `sq`, `csvq`). Use the `\drivers` command in usql to see available drivers and aliases.

    transport
        Is optional, usually `tcp`, `udp`, or `unix`. Some drivers like ODBC use it for sub-protocol specification.

    user, pass, host, port
        Are standard connection credentials. Many parts are optional and defaults may apply (e.g., connecting via local Unix sockets).

    dbname
        Represents the database name, service ID, instance, or sometimes a file path (for file-based databases like SQLite3 or DuckDB). For some drivers (e.g., SQL Server, Oracle), this can be `/instance/dbname`.

    ?opt1=val1...
        Are driver-specific connection options passed as URL query parameters.

usql can also connect to local files directly (e.g., `/path/to/db.sqlite3`) or named connections defined in the configuration file.

See the project's README.md for detailed examples and supported drivers.

COMMANDS
usql interprets backslash (\) commands for meta-operations, similar to psql. These provide functionality beyond standard SQL. Use \? within usql for a full list. Key commands include:

\c, \connect DSN | NAME
    Connect to a database using a DSN URL or a pre-defined named connection.

\copy SRC_DSN DST_DSN QUERY TABLE[(COLS)]
    Copy data between databases. Executes QUERY on SRC_DSN and inserts results into TABLE on DST_DSN.

\d[+] [PATTERN]
    List tables, views, sequences. Add '+' for more detail. Many variants exist (\dt, \di, \f, n, etc.) to list specific object types. Use \? options for details.

\g, \go [(OPTIONS)] [FILE]
    Execute the current query buffer, optionally sending results to a FILE or pipe.

\G, \ego [(OPTIONS)] [FILE]
    Execute query and force vertical output mode.

\gx [(OPTIONS)] [FILE]
    Execute query and force expanded output mode.

\i, \include FILE
    Execute commands from FILE.

\p, \print [-raw|-exec]
    Show the contents of the query buffer (or the raw/exec buffer).

\r, \reset
    Clear the query buffer.

\set [NAME [VALUE]]
    Set or show runtime variables. Used for query interpolation.

\pset [NAME [VALUE]]
    Set or show display formatting options (e.g., border, format, tuples_only).

\cset [NAME [DSN]]
    Set or show named connection DSNs.

\timing [on|off]
    Toggle display of command execution time.

\! [COMMAND]
    Execute a command in the system shell.

\q, \quit
    Quit usql.

VARIABLES
usql utilizes variables for configuration and dynamic query generation:

Runtime Variables (\set)
    Set with `\set NAME VALUE`. Interpolated into queries using `:NAME` (direct substitution), `:'NAME'` (quoted as string literal), or `:"NAME"` (quoted as identifier). View all with `\set`. Unset with `\unset NAME`.

Connection Variables (\cset)
    Set with `\cset NAME DSN`. Provide aliases for database connection URLs used with `\c` or `\copy`. View all with `\cset`. Not used for query interpolation.

Display Formatting Variables (\pset)
    Set with `\pset NAME VALUE` or toggle commands like `\a`, `\H`, `\x`, `\t`. Control table borders, output formats (aligned, csv, html, json, etc.), titles, timing display, etc. View all with `\pset`.

Special variables (set via `\set`) control behavior like syntax highlighting (`SYNTAX_HL`, `SYNTAX_HL_STYLE`), host info display (`SHOW_HOST_INFORMATION`), prompts (`PROMPT1`, `PROMPT2`, `PROMPT3`), etc. Use `\? variables` in usql for a comprehensive list.

FILES

$HOME/.config/usql/config.yaml
    Primary configuration file (path varies by OS: `%AppData%/usql/config.yaml` on Windows, `$HOME/Library/Application Support/usql/config.yaml` on macOS). Defines named connections (`connections:`), startup commands (`init:`), default settings, etc. Uses YAML format.

$HOME/.usqlrc
    Legacy startup script executed when `usql` starts interactively, unless `-X` is given. Useful for setting variables or running initial commands. The `init:` section in `config.yaml` is now preferred.

$HOME/.usqlpass
    Legacy file for storing database passwords (permissions must be `0600`). Format: `protocol:host:port:dbname:user:pass`. Using named connections in `config.yaml` is generally safer and more flexible.

ENVIRONMENT

EDITOR
    Specifies the editor used by the `\e` command.

PAGER
    Specifies the pager program used for displaying help (`\?`) and other long output.

SHELL
    Specifies the shell used by the `\!` command.

USQL_CONFIG_DIR
    Overrides the default directory for configuration files (`config.yaml`, `.usqlrc`, `.usqlpass`).

USQL_SHOW_HOST_INFORMATION
    Set to `false` to prevent displaying database host/version information upon connection. Can also be controlled by the `SHOW_HOST_INFORMATION` variable via `\set`.

USQL_TERM_GRAPHICS
    Force enable or disable terminal graphics support. Values: `kitty`, `iterm`, `sixel`, `none`. Overrides automatic detection.

TERM_GRAPHICS
    Alternative to `USQL_TERM_GRAPHICS`.

EXAMPLES
Connect to a PostgreSQL database interactively:

    usql pg://user:pass@localhost:5432/mydatabase

Connect to a local SQLite3 file (will create if not exists if scheme is present):

    usql sqlite3://./data.db

Run a single SQL command against a MySQL database and exit:

    usql -c SELECT user, host FROM mysql.user; my://root@localhost/

Execute commands from a script file against SQL Server:

    usql -f setup.sql ms://sa:Password123@server.example.com/master

Connect using a named connection 'prod_db' defined in config.yaml:

    usql prod_db

Set a variable on the command line and use it in a query:

    usql -v SCHEMA=public -v TBL=users pg://... -c 'SELECT * FROM :"SCHEMA".:"TBL" LIMIT 10;'

Copy all data from a PostgreSQL table 'source_tbl' to an Oracle table 'TARGET_TBL':

    usql -c \ecopy pg://... or://... 'SELECT * FROM source_tbl' 'TARGET_TBL'

Connect to a CSV file in the current directory and query it:

    usql csvq://. -c SELECT column_a, column_b FROM 'data.csv' WHERE column_a > 10;

SEE ALSO
The full documentation, source code, and issue tracker can be found on GitHub:

    https://github.com/xo/usql