Exploring the Catalog

View as Markdown

--catalog lists the objects one level below --path, and exits without running SQL. --catalog-search finds objects by name at every level at once. Neither runs a query, and both produce ordinary result sets.

$ hsql "path/to/duck.db" --catalog

 path | name | query_name | type     | type_label
------+------+------------+----------+------------
 duck | duck | "duck"     | database | db
(1 row)

Every row has five columns:

Column	What it is
`path`	What to pass to `--path` to list this object’s children.
`name`	The object’s name, unquoted.
`query_name`	The name already quoted for this database. Paste this into SQL.
`type`	The database’s own word for it: `database`, `schema`, `VIEW`, `BIGINT`.
`type_label`	The short label Harlequin shows in its data catalog.

Navigating the Catalog

Pass a row’s path back to --path:

$ hsql "path/to/duck.db" --catalog --path duck

 path           | name      | query_name         | type   | type_label
----------------+-----------+--------------------+--------+------------
 duck.analytics | analytics | "duck"."analytics" | schema | sch
 duck.main      | main      | "duck"."main"      | schema | sch
(2 rows)

$ hsql "path/to/duck.db" --catalog --path duck.analytics

 path                        | name         | query_name                 | type       | type_label
-----------------------------+--------------+----------------------------+------------+------------
 duck.analytics.customers    | customers    | "analytics"."customers"    | BASE TABLE | t
 duck.analytics.order_totals | order_totals | "analytics"."order_totals" | VIEW       | v
 duck.analytics.orders       | orders       | "analytics"."orders"       | BASE TABLE | t
(3 rows)

One level further down is the columns, with their types:

$ hsql "path/to/duck.db" --catalog --path duck.analytics.orders

 path                              | name        | query_name    | type          | type_label
-----------------------------------+-------------+---------------+---------------+------------
 duck.analytics.orders.customer_id | customer_id | "customer_id" | BIGINT        | ##
 duck.analytics.orders.id          | id          | "id"          | BIGINT        | ##
 duck.analytics.orders.placed_at   | placed_at   | "placed_at"   | TIMESTAMP     | ts
 duck.analytics.orders.total       | total       | "total"       | DECIMAL(18,2) | #.#
(4 rows)

The adapter names the segments, so how deep the catalog goes and what each level is called varies by database.

A trailing * filters a listing. Quote it, or the shell expands it against the working directory:

$ hsql "path/to/duck.db" --catalog --path 'duck.analytics.ord*'

Searching

--catalog-search TERM searches every level at once, for objects whose name contains TERM:

$ hsql "path/to/duck.db" --catalog-search customer_id

 path                                    | name        | query_name    | type   | type_label
-----------------------------------------+-------------+---------------+--------+------------
 duck.analytics.order_totals.customer_id | customer_id | "customer_id" | BIGINT | ##
 duck.analytics.orders.customer_id       | customer_id | "customer_id" | BIGINT | ##
 duck.main.staging_events.customer_id    | customer_id | "customer_id" | BIGINT | ##
(3 rows)

--path narrows a search to one subtree:

$ hsql "path/to/duck.db" --catalog-search order --path duck.analytics -tA

duck.analytics.order_totals|order_totals|"analytics"."order_totals"|VIEW|v
duck.analytics.orders|orders|"analytics"."orders"|BASE TABLE|t

Note

Not every adapter can search. hsql --info -a NAME reports implements_catalog_search; see Running Safely.

Formats and Files

A listing is a result set, so every format and output option applies:

$ hsql "path/to/duck.db" --catalog --path duck.analytics -tA --csv

duck.analytics.customers,customers,"""analytics"".""customers""",BASE TABLE,t
duck.analytics.order_totals,order_totals,"""analytics"".""order_totals""",VIEW,v
duck.analytics.orders,orders,"""analytics"".""orders""",BASE TABLE,t

$ hsql -P prod --catalog --path prod.public --json -o ./schema.json

They are modes rather than options: hsql either reads the catalog or runs SQL. Passing -c or -f beside --catalog exits 2. Use two invocations.

For the same catalog as a tree you can click through, use the Harlequin IDE.