hsql

hsql is your agent's favorite SQL client. It the headless CLI for Harlequin, and shares the same config and query engine, with an interface optimized for agents, scripts, and automations.

[!TIP] This README contains a small subset of the docs available at harlequin.sh.

If you already use Harlequin and know about adapters and config files, jump ahead to Running hsql.

Installing hsql

hsql is packaged with Harlequin, so if you already use Harlequin, hsql is already installed.

Otherwise, you can install hsql directly. hsql is a Python program, and there are many ways to install and run it. We strongly recommend using uv:

1. Install uv. From a POSIX shell, run:

   curl -LsSf https://astral.sh/uv/install.sh | sh
   

   Or using Windows Powershell:

   powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"
   

2. Install hsql as a tool using uv:

   uv tool install hsql
   

   This command will install hsql into an isolated environment and add it to your PATH so you can easily run the executable.

Installing Database Adapters

hsql can connect to dozens of databases using adapter plug-ins. Adapters are distributed as their own Python packages that need to be installed into the same environment as hsql and harlequin.

For a list of known adapters provided either by the Harlequin maintainers or the broader community, see the adapters page.

The adapter docs also include installation instructions (installing an adapter with Harlequin also installs it for hsql). Some adapters can be installed as Harlequin extras, like postgres. If you used uv to install hsql directly, you can add adapter packages using --with:

uv tool install hsql --with harlequin-postgres

You can install multiple extras:

uv tool install hsql --with harlequin-postgres --with harlequin-mysql

Running hsql

Once hsql is installed, you run it from the command line. If you have used psql or the duckdb CLI, hsql will feel familiar, but hsql has the major advantage that is works with most databases and provides the same interface and produces the same output, regardless of the connected database. This means you (and your agent) can learn one tool, instead of several. In your shell, all hsql commands take the same form:

hsql [OPTIONS] [CONN_STR]

where [OPTIONS] is 0 or more pairs of the form --[option-name] [option-value], and [CONN_STR] is 0 or more connection strings. [OPTIONS] are composed of both hsql options and adapter options. For a full list of options, run hsql with the --help option:

hsql --help

Using hsql with DuckDB

hsql defaults to using its DuckDB database adapter, which ships with hsql and includes the full DuckDB in-process database.

Run a query against an in-memory DuckDB session, run hsql and pass in a query with the -c option:

$ hsql -c "select 1"
 1
---
 1
(1 row)

To query one or more DuckDB database files, pass in relative or absolute paths as connection strings (hsql will create DuckDB databases if they do not exist):

$ hsql "path/to/duck.db" -tAc "select count(*) from orders"
42

Using hsql with SQLite and Other Adapters

hsql also ships with a SQLite3 adapter. To use that adapter, you specify the --adapter sqlite option. Like DuckDB, you can open an in-memory SQLite database by omitting the connection string:

$ hsql --adapter sqlite -c "select 'Ted' as author"
 author
--------
 Ted
(1 row)

You can query one or more SQLite database files by passing in their paths as connection strings; note that the --adapter option has a short alias, -a:

$ hsql -a sqlite "path/to/sqlite.db" -c "select * from users"
 id | name
----+---------
 1  | Ted
 2  | Patrick
(2 rows)

Other adapters work the same way; for example, Postgres:

$ hsql -a postgres "postgresql://example.com/postgres:5432" -c "select * from invoices"

[!TIP] You should use Profiles to keep credentials out of your shell history. For more information, keep reading or see the docs on config files.

Configuring hsql and Using Profiles

hsql supports a number of options for setting the query limit, configuring output formats, and defining connection parameters. Options can be passed as command-line flags, or read from config files. Config files store configurations under separate profiles, so you can easily switch between databases by reading from different profiles with the -P option:

$ hsql -P prod -c "select count(*) from orders" --csv
$ hsql -P dev -c "select * from users" --vertical --limit 5
$ hsql -P warehouse -c "..." --parquet -o invoices.pq

Data Layouts and File Formats

hsql supports all of the following formats for displaying and writing data:

• table
• markdown (alias: md)
• vertical
• csv
• tsv
• json
• jsonl (alias: ndjson)
• parquet
• orc
• feather (alias: arrow)
• none (suppresses output)

You can select a format with the --format <name> or using the shorthand --<name>, so these are equivalent: --format csv, --csv.

Some layouts can present the results from multiple queries. Others will raise an error and exit with code 2 if multiple queries are executed.

Additionally, for any layout, pass --stats to print summary info as JSON to stderr:

$ hsql -c "select 1" --format none  --stats
{"status":"ok","statements":1,"rows":1,"truncated":false,"limit":500,"elapsed_ms":1,"columns":[{"name":"1","type":"#"}]}

Scripting with hsql

[!WARNING] To make hsql safe and efficient for agents, by default hsql applies a 500-row limit to all queries. To remove this limit, use --limit -1 or set limit = -1 in your profile. If limits truncate data, hsql will print a warning on stderr; we recommend that you do NOT suppress or redirect that message so do NOT use hsql with 2>/dev/null.

hsql can write data to files, either with the -o option or by piping output (hsql only writes data to stdout; other messages go to stderr):

$ hsql -P prod --limit -1 -c "select * from users" --parquet -o "users.pq"
$ hsql -P prod --limit -1 -c "select * from users" --csv > users.csv

hsql can execute multiple statements in one invocation, and supports several methods for doing so:

• Pass -c multiple times
• Include multiple queries, separated by ;, in one -c option
• Pass one or more .sql files with -f, with multiple statements in each
• Use --results to define which queries output data to stdout
• Use --on-error to either stop or continue if one or more queries produces an error.

In other words, this works:

$ hsql -P prod --limit -1 --format md --results all --on-error stop \
    -f ./setup.sql \
    -c "select count(*) from raw_table" \
    -f ./build-models.sql \
    -c "select count(*) from modeled_table" 

hsql's exit codes are meaningful and stable:

• 0: Success
• 1: Query error
• 2: Usage/config error
• 3: Connection error
• 4: Timeout
• 130: Interrupted

You can also use --stats and jq together to error on a truncated query:

hsql --limit -1 -c "select 1" --csv -o data.csv --stats 2>&1 | jq -e '.truncated | not' > /dev/null

Keep Reading at harlequin.sh

Visit harlequin.sh for an overview of features and full documentation.

Getting Help

To view all command-line options for Harlequin and all installed adapters, after installation, simply type:

hsql --help

GitHub Discussions are a good place to ask questions, request features, and say hello.

GitHub Issues are the best place to report bugs.

Sponsoring Harlequin and hsql

Please consider sponsoring Harlequin's author, so he can continue to dedicate time to hsql.

Contributing

Thanks for your interest in Harlequin! Harlequin and hsql are primarily maintained by Ted Conbeer, but he welcomes all contributions!

Please see CONTRIBUTING.md for more information.