Inspect

sq inspect inspects metadata (schema/structure, tables, columns) for a source, or for an individual table. When used with --json, the output of sq inspect can be fed into other tools such as jq to enable complex data pipelines.

Let’s start off with a single source, a Postgres Sakila database:

# Start the Postgres container
$ docker run -d -p 5432:5432 sakiladb/postgres:12

# Add the source
$ sq add postgres://sakila:p_ssW0rd@localhost/sakila --handle @sakila_pg
@sakila_pg  postgres  sakila@localhost/sakila

Inspect source #

Use sq inspect @sakila_pg to inspect the source.

$ cat actor.csv | sq inspect

This output includes the source metadata, and the schema structure (tables, columns, etc.).

Output formats #

text #

$ sq inspect @sakila_pg

To see more detail, use the --verbose (-v) flag with the text format.

yaml #

The yaml format (--yaml / -y) renders the full output, and is reasonably human-readable.

json #

The json format (--json / -j) renders the same content as yaml, but is more suited for use with other tools, such as jq .

Here’s an example of using sq with jq to list all table names:

$ sq inspect -j | jq -r '.tables[] | .name'

See more examples in the cookbook .

markdown #

The markdown format (via --markdown or -f markdown) renders a schema document suited for embedding in project docs or a pull request: a source overview, per-table column / key / constraint / index detail, and a Mermaid entity-relationship diagram. The diagram renders inline on GitHub, GitLab, and most Markdown viewers, showing every table and its foreign-key relationships.

# Redirect output to sakila.md
$ sq inspect @sakila_pg --markdown > sakila.md

# Or just use the -o/--output flag
$ sq inspect @sakila_pg --markdown -o sakila.md

# Output just for the actor table.
$ sq inspect @sakila_pg.actor --markdown -o actor.md

html #

The html format (via --html or -f html) renders the same schema document as the markdown format — a source overview, per-table column / key / constraint / index detail, and a Mermaid entity-relationship diagram — but as a standalone HTML page. The diagrams are rendered client-side by Mermaid.js, so the page can be opened directly in a browser.

$ sq inspect @sakila_pg --html

# Equivalent, using the generic format flag:
$ sq inspect @sakila_pg -f html

# Write the document to a file with the --output (-o) flag:
$ sq inspect @sakila_pg --html -o sakila.html

# Or to open directly in a browser (macOS):
$ sq inspect @sakila_pg --html > sakila.html && open sakila.html

By default, the page loads Mermaid.js from a CDN, producing a small file that requires internet access to render the diagram. To produce a fully self-contained document that renders offline, inline the Mermaid.js library via the format.html.embed-assets option. Set it persistently with sq config set, or per invocation with the matching --format.html.embed-assets flag:

# Persistently, for all HTML output.
$ sq config set format.html.embed-assets true

# Or per invocation (overrides config for this run).
$ sq inspect @sakila_sl3 --html --format.html.embed-assets

As with markdown, the --overview (-O) mode omits the schema and diagram, and inspecting a single table (sq inspect @sakila_sl3.film_actor --html) renders just that table’s section.

mermaid-erd #

The mermaid-erd format emits just the bare Mermaid entity-relationship diagram source — the same diagram embedded in the markdown and html schema documents, but with nothing wrapped around it (no code fence, no HTML page). It’s handy for pasting into a Markdown file, the Mermaid live editor , or a docs pipeline. There’s no dedicated flag for it; select it via the generic --format (-f) flag:

# Whole-source ERD.
$ sq inspect @sakila_pg --format=mermaid-erd

# Just the film_actor table (and its related tables).
$ sq inspect @sakila_pg.film_actor -f mermaid-erd

# Write the diagram to a file.
$ sq inspect @sakila_pg -f mermaid-erd -o sakila.mmd

%% sq inspect @sakila_pg.actor -f mermaid-erd
erDiagram
    actor {
        int actor_id PK
        text first_name
        text last_name
        datetime last_update
    }
    actor ||--o{ film_actor : "film_actor_actor_id_fkey"

The format covers only source and single-table schema inspection. Operations with no diagram — such as --overview (-O), --catalogs, or --dbprops — return an error rather than empty output.

Source overview #

Sometimes you don’t need the full schema, but still want to view the source metadata. Use the --overview (-O) mode to see just the top-level metadata. This excludes the schema structure, and is also much faster to complete.

Well, that’s not a lot of detail. The --yaml output is more useful:

The --json format produces similar output.

Database properties #

The --dbprops mode displays the underlying database’s properties, server config, and the like.

$ sq inspect @sakila_pg --dbprops

Use --dbprops with --yaml or --json to get the properties in machine-readable format. Note that while the returned structure is generally a set of key-value pairs, the specifics can vary significantly from one driver type to another. Here’s --dbprops from a SQLite database (in --yaml format):

Catalogs #

The --catalogs mode lists the catalogs (databases) available in the source.

Schemata #

Like --catalogs, the --schemata mode lists the schemas available in the source.

To list the schemas in a specific catalog, supply CATALOG. to the --src.schema flag:

# List the schemas in the "inventory" catalog.
$ sq inspect @sakila/pg12 --schemata --src.schema inventory.

Inspect table #

In additional to inspecting a source, you can drill down on a specific table.

$ sq inspect @sakila_pg.actor

Use --verbose mode for more detail:

And, as you might expect, you can also see the output in --json and --yaml formats.

Foreign-key relationships #

sq inspect reports foreign-key constraints for any SQL source that supports them (SQLite, Postgres, MySQL, SQL Server, Oracle, DuckDB). The relationships appear under each table’s fk object — the same FK shows up once under its owning table’s fk.outgoing and once under the referenced table’s fk.incoming:

• tables[].fk.outgoing — constraints declared on this table (its outgoing edges). Tells you what rows in this table depend on.
• tables[].fk.incoming — constraints declared on other tables whose referenced side is this table (its incoming edges). Tells you what depends on rows in this table. Useful for “blast radius” questions (“if I delete this row, what else breaks?”) and for visualization tools that want to render the schema as a directed graph without walking every table to discover incoming edges.

Composite foreign keys are supported by every driver. Cross-schema and cross-catalog references are reported by Postgres, MySQL, and SQL Server. Oracle reports cross-schema references via ref_schema for outgoing FKs only — fk.incoming is scoped to the current user’s schema, so FKs from tables in other schemas are not surfaced. DuckDB’s duckdb_constraints() view does not expose the referenced table’s schema, so a DuckDB FK that crosses schemas is reported as same-schema.

The on_delete and on_update referential actions are surfaced where the driver reports them (Oracle exposes on_delete only; DuckDB’s duckdb_constraints() view doesn’t expose either action, so both fields are left empty for DuckDB sources).

For example, to list every parent → child relationship in the Sakila schema:

$ sq inspect -j @sakila_pg | jq -r '
  .tables[]
  | .name as $child
  | .fk.outgoing[]?
  | "\($child).\(.columns | join(",")) -> \(.ref_table).\(.ref_columns | join(","))"'
film.original_language_id -> language.language_id
film.language_id -> language.language_id
film_actor.actor_id -> actor.actor_id
film_actor.film_id -> film.film_id
...

Filtering composites #

Composite constraints (FK, unique, or index) are represented as a single entry whose columns slice carries every participating column in declaration order — single-column constraints are just the one-element case. There’s no separate composite flag because the arity is the slice length, so (.columns | length) > 1 is the idiomatic filter:

# Composite foreign keys only
$ sq inspect -j @sakila_pg | jq -r '
  .tables[] | .fk.outgoing[]?
  | select((.columns | length) > 1)
  | "\(.table)(\(.columns | join(","))) -> \(.ref_table)(\(.ref_columns | join(",")))"'

The same pattern works for unique_constraints[] and indexes[].

The --verbose text output also gains an FK column listing the referenced table and columns for each FK column.

Unique constraints and indexes #

In addition to foreign keys, each table reports its UNIQUE constraints and the physical indexes that back it:

• tables[].unique_constraints — UNIQUE declarations (inline or via ALTER TABLE ADD CONSTRAINT). Primary keys are reported separately via columns[].primary_key and are not repeated here. Composite members appear in declaration order.
• tables[].indexes — physical indexes, including the implicit PK-backing index, unique-constraint-backing indexes, and any user-declared CREATE INDEX entries. Each entry carries unique, primary, and a driver-specific type (e.g. BTREE, HASH, NONCLUSTERED). DuckDB is the exception: its duckdb_indexes() catalog only lists explicit CREATE INDEX definitions, so PK-backing and UNIQUE-backing indexes don’t appear there. The PK and UNIQUE information is still available via columns[].primary_key and unique_constraints.

For example, list non-unique indexes per table:

$ sq inspect -j @sakila_pg | jq -r '
  .tables[]
  | .name as $t
  | .indexes[]?
  | select(.unique == false)
  | "\($t).\(.name) (\(.columns | join(\",\"))) [\(.type)]"'

How verbose text marks redundant index entries #

The INDEXES column of sq inspect --verbose text output applies two display rules so the column reads cleanly without losing information:

• PK-backing indexes are dropped entirely. The PK column already marks the participating columns; repeating the implicit index name adds nothing.
• UNIQUE-constraint-backing indexes are shown muted, with the index name wrapped in parentheses and rendered in a subdued style (italic + faint, on color terminals). The UC name also appears under UNIQUE CONSTRAINTS, so the parens signal “this is the implicit backing index” while keeping the name visible. The match is by column-set, not name, so SQLite’s auto-generated sqlite_autoindex_* entries pair up with the right UC.

NAME     TYPE   ROWS  COLS  NAME        TYPE     PK  FK  INDEXES                                 UNIQUE CONSTRAINTS
demo_uc  table  0     5     id          int4     pk
                            email       varchar          (demo_uc_email_key), idx_solo_unique    demo_uc_email_key
                            first_name  varchar          (uniq_full_name)                        uniq_full_name
                            last_name   varchar          (uniq_full_name)                        uniq_full_name
                            nickname    varchar          idx_demo_nickname

A user-declared CREATE UNIQUE INDEX that doesn’t back any formal constraint (e.g. idx_solo_unique above) renders unmuted, alongside the parenthesized constraint-backing entry.

These display rules apply only to the verbose text renderer. The JSON and YAML formats always emit the full tables[].indexes slice — every physical index, including PK- and UC-backing ones — so tooling consuming the machine-readable forms sees the complete picture.

Override active schema #

By default, sq inspect uses the active schema for the source. You can override the active schema (and catalog) using the --src.schema flag. See the sources section for a fuller explanation of --src.schema, but here’s a quick example of inspecting Postgres’s information_schema schema:

$ sq inspect @sakila/pg12 --src.schema sakila.information_schema
SOURCE        DRIVER    NAME    FQ NAME                    SIZE    TABLES  VIEWS  LOCATION
@sakila/pg12  postgres  sakila  sakila.information_schema  16.6MB  7       61     postgres://sakila:xxxxx@192.168.50.132/sakila

NAME                                   TYPE   ROWS   COLS
sql_features                           table  716    feature_id, feature_name, sub_feature_id, sub_feature_name, is_supported, is_verified_by, comments
sql_implementation_info                table  12     implementation_info_id, implementation_info_name, integer_value, character_value, comments
sql_languages                          table  4      sql_language_source, sql_language_year, sql_language_conformance, sql_language_integrity, sql_language_implementation, sql_language_binding_style, sql_language_programming_language
sql_packages                           table  10     feature_id, feature_name, is_supported, is_verified_by, comments
sql_parts                              table  9      feature_id, feature_name, is_supported, is_verified_by, comments
sql_sizing                             table  23     sizing_id, sizing_name, supported_value, comments
sql_sizing_profiles                    table  0      sizing_id, sizing_name, profile_id, required_value, comments
_pg_foreign_data_wrappers              view   0      oid, fdwowner, fdwoptions, foreign_data_wrapper_catalog, foreign_da