Running Safely

Three options bound a run: how many rows it fetches, whether it can write, and how long it can take. hsql refuses to connect when the adapter cannot enforce the last two, rather than running unbounded.

--limit

hsql fetches 500 rows per result set by default, and the database applies the limit.

$ hsql -P prod --limit 100 -c "select * from orders"
hsql -P prod --limit -1 -c "select * from orders" --format parquet -o orders.parquet

--limit -1 runs the query as written, with no limit applied.

Warning

A truncated result looks like a complete one. When a limit cuts a result set short, hsql says so on stderr and --stats reports "truncated": true.

--display-rows is the softer knob: it caps what a text layout prints without changing what was fetched.

--read-only

-r (or --read-only) instructs the database to prohibit writes:

$ hsql -r "path/to/duck.db" -c "insert into orders values (1)"

hsql: error: Invalid Input Error: Cannot execute statement of type "INSERT" on database "duck" which is attached in read-only mode!

An adapter that cannot connect read-only makes hsql exit 2 instead of connecting writable.

--timeout

--timeout SECONDS bounds executing and fetching together, and exits 4 when it runs out:

$ hsql --timeout 0.5 -c "select count(*) from range(100000000000) t(i)"

hsql: error: timed out after 0.5s

As with --read-only, hsql refuses to start when the adapter cannot cancel a query.

Adapter Capabilities

hsql --info reports what each installed adapter declares it supports, and connects to nothing. -a NAME narrows it to one adapter, which is faster than importing them all:

$ hsql --info -a sqlite | jq '.adapters.sqlite'

{
  "distribution": "harlequin",
  "version": "2.10.0",
  "capabilities": {
    "implements_cancel": true,
    "implements_catalog_search": true,
    "implements_read_only": true,
    "implements_validate_sql": false
  },
  "error": null
}

Note

An adapter that is installed but will not import is reported with its capabilities unknown and the import error beside it. That is a broken installation rather than a broken config; see Troubleshooting.

A Profile for Automation

read_only, timeout and limit are profile keys, so no invocation has to remember them:

[profiles.agent]
adapter = "postgres"
host = "${PGHOST}"
password = "${PGPASSWORD}"
read_only = true
timeout = 30
limit = -1

$ hsql -P agent -tAc "select count(*) from orders"