What it is
usql is a universal command-line interface for interacting with any SQL database, providing a consistent experience across PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and more.
Installation
Linux
# Using go (if you have it installed)
go install github.com/xo/usql@latest
# Or download from releases page: https://github.com/xo/usql/releases
# Example for amd64 Linux:
wget https://github.com/xo/usql/releases/download/v0.10.1/usql_0.10.1_linux_amd64.tar.gz
tar -xzf usql_0.10.1_linux_amd64.tar.gz
sudo mv usql /usr/local/bin/
macOS
# Using Homebrew
brew install usql
# Or download from releases page: https://github.com/xo/usql/releases
# Example for amd64 macOS:
curl -LO https://github.com/xo/usql/releases/download/v0.10.1/usql_0.10.1_darwin_amd64.tar.gz
tar -xzf usql_0.10.1_darwin_amd64.tar.gz
sudo mv usql /usr/local/bin/
Windows
Download the appropriate .zip file from the releases page and extract usql.exe to a directory in your system’s PATH.
Core Concepts
Connection Strings
usql uses connection strings to specify which database to connect to. The general format is:
driver://user:password@host:port/database
Common drivers include:
postgres(for PostgreSQL)mysql(for MySQL)sqlite3(for SQLite)sqlserver(for SQL Server)oci(for Oracle)
Drivers
usql supports a wide range of SQL databases through different drivers. You don’t typically install drivers separately; usql bundles common ones. For less common databases, you might need to install a specific driver package (e.g., go get github.com/lib/pq for PostgreSQL if not bundled).
Commands / Usage
Connecting to Databases
Connect to a PostgreSQL database
usql postgres://user:password@localhost:5432/mydatabase
Connects to a PostgreSQL database named mydatabase on localhost.
Connect to a MySQL database
usql mysql://user:password@tcp(127.0.0.1:3306)/mydatabase
Connects to a MySQL database named mydatabase on 127.0.0.1.
Connect to a SQLite database
usql sqlite3:///path/to/your/database.db
Connects to a SQLite database file.
Connect using environment variables
PGPASSWORD=mypassword usql postgresql://myuser@localhost/mydatabase
Uses PGPASSWORD environment variable for the password. usql respects common database-specific environment variables.
Connect to a DSN file
usql -d ~/.my_db_dsn
Connects using a Data Source Name (DSN) stored in a file.
Connect with a specific driver
usql --driver sqlserver "sqlserver://user:password@host:port?database=mydatabase"
Explicitly specifies the driver if the connection string isn’t auto-detected.
Executing SQL Commands
Execute a single SQL statement and exit
usql postgres://user:pass@host/db -c "SELECT COUNT(*) FROM users;"
Runs the given SQL command and prints the result, then exits.
Execute SQL from a file
usql postgres://user:pass@host/db -f /path/to/queries.sql
Executes all SQL statements found in the specified file.
Execute SQL from standard input
echo "SELECT * FROM products LIMIT 5;" | usql postgres://user:pass@host/db
Pipes SQL commands to usql for execution.
Interactive Mode Commands (Prefixed with \)
List all available commands
\?
Shows help for all usql meta-commands.
List tables
\dt
Lists tables in the current database.
List schemas
\dn
Lists schemas in the current database.
Describe a table
\d users
Shows the columns and their types for the users table.
Show the query plan for a statement
EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';
(Note: EXPLAIN is a standard SQL command, not a usql meta-command, but often used interactively).
View usql version
\ver
Displays the usql version and driver information.
Set query timeout
\timing on
Turns on query timing, showing how long each query takes.
Set query timeout (seconds)
\qtimeout 10
Sets a timeout of 10 seconds for queries.
Change database
\c another_db
Connects to a different database on the same server.
Exit usql
\q
Quits the usql interactive session.
Save query output to a file
\o /path/to/output.csv
Redirects subsequent query output to the specified file.
Disable query output redirection
\o
Stops redirecting query output to a file.
Set output format
\pset format csv
Sets the output format for query results (e.g., csv, aligned, html, json, unaligned).
Set field separator
\pset fieldsep '|'
Sets the character used to separate fields in unaligned or csv output.
Set null string representation
\pset null 'NULL'
Sets the string representation for NULL values.
Common Flags
Specify driver
usql --driver mysql -c "SHOW TABLES;" mysql://user:pass@host/db
Forces usql to use the mysql driver, even if it could infer it.
Execute commands from file
usql -f queries.sql postgres://user:pass@host/db
Shorthand for --file.
Execute a single command
usql -c "SELECT 1;" postgres://user:pass@host/db
Shorthand for --command.
Set connection timeout
usql --connect-timeout 5 postgres://user:pass@host/db
Sets a 5-second timeout for establishing the database connection.
Disable auto-commit
usql --no-auto-commit postgres://user:pass@host/db
Turns off the default auto-commit behavior, allowing manual transaction control.
Enable verbose output
usql --verbose postgres://user:pass@host/db
Increases the verbosity of usql’s output.
Set password from stdin
echo "mypassword" | usql --password-stdin postgres://user@host/db
Reads the password from standard input. Useful in scripts.
Set environment
usql --env=production postgres://user:pass@host/db
Sets an environment name, which can be used in connection string placeholders or for conditional logic if supported by a custom driver.
Disable history
usql --no-history postgres://user:pass@host/db
Prevents usql from saving or loading command history.
No headers
usql -H postgres://user:pass@host/db -c "SELECT id, name FROM users;"
Suppresses the header row in query results.
No alignment
usql -A postgres://user:pass@host/db -c "SELECT id, name FROM users;"
Disables column alignment, outputting rows without padding.
Unaligned output
usql -U postgres://user:pass@host/db -c "SELECT id, name FROM users;"
Outputs rows with fields separated by the field separator (default is |).
Common Patterns
Running a query and saving results to CSV
usql postgres://user:pass@host/db -c "SELECT * FROM logs WHERE timestamp > '2023-10-26';" -o logs.csv --csv
Executes a query and saves the output directly to a CSV file. The --csv flag is a shortcut for \pset format csv.
Iterating over a list of IDs and performing an action
for id in 1 2 3 4 5; do
usql postgres://user:pass@host/db -c "UPDATE items SET status = 'processed' WHERE id = $id;"
done
A simple loop to update multiple records.
Using usql within a shell script for database migrations
#!/bin/bash
DB_URL="postgres://user:pass@host/db"
# Apply migrations
usql "$DB_URL" -f ./migrations/001_create_users_table.sql
usql "$DB_URL" -f ./migrations/002_add_posts_table.sql
echo "Migrations applied successfully."
Running SQL migration scripts sequentially.
Fetching a single value and using it in a shell command
USER_COUNT=$(usql postgres://user:pass@host/db -t -A -c "SELECT COUNT(*) FROM users;")
echo "There are $USER_COUNT users."
Uses -t (tuples only) and -A (unaligned) flags to get just the raw value from the query.
Connecting to a database with a complex DSN string
usql "postgresql://myuser:mypassword@mypgserver.example.com:5432/mydatabase?sslmode=require&connect_timeout=10"
Includes SSL mode and connection timeout directly in the connection string.
Running a query with a password provided via stdin (for CI/CD)
echo "$DB_PASSWORD" | usql --password-stdin postgresql://myuser@db.example.com/mydatabase -c "SELECT version();"
Securely provides the password without exposing it in the command history.
Gotchas
Driver Name Ambiguity
If usql cannot automatically determine the correct driver from the connection string, you might need to explicitly use the --driver flag. For example, specifying postgres for PostgreSQL connections.
SSL Mode Configuration
For databases like PostgreSQL and MySQL, SSL/TLS connection parameters (like sslmode=require) need to be correctly specified in the connection string or handled via environment variables (PGSSLMODE, MYSQL_SSL_MODE).
Transaction Handling
By default, usql operates in auto-commit mode. If you need explicit transaction control (BEGIN, COMMIT, ROLLBACK), you must disable auto-commit using the --no-auto-commit flag.
Quoting and Escaping
Shell quoting rules apply to the entire usql command line. SQL statements passed via -c or piped via stdin need to be properly quoted for the shell. Inside SQL, standard SQL quoting rules apply.
usql Meta-Commands vs. SQL Commands
Commands starting with \ are usql meta-commands (e.g., \dt, \q). Standard SQL commands are executed directly. Be careful not to prefix SQL commands with \.
Driver Availability
While usql bundles many drivers, some less common databases might require installing a specific Go driver package separately if usql doesn’t include it by default. Check the usql documentation for supported drivers.