Introspect without a connection

Schema author guide

When the machine that runs askdb can’t reach your Postgres database, or shouldn’t hold its credentials, someone who can runs AskDB’s catalog queries and hands you the results. AskDB builds the same schema artifact from them as from a live connection.

This works for Postgres only. The other engines need a live connection.

What you hand over

The export bundle, the folder of query results you hand over, holds catalog metadata and nothing else:

The names of schemas, tables, views, columns, constraints, indexes and sequences.
Column types, nullability and default expressions; primary, foreign and unique keys; and whether row-level security is on.
The SQL text of check constraints, index expressions and view definitions; enum labels; sequence settings; and comments on tables, views and columns.

It holds no table rows, no row counts, and no credentials: the connection string never goes into the files. A definition is SQL text as written, though, so a literal value written into a check constraint, a column default, a view or a comment travels with it.

Before you start

AskDB set up in your project: npx askdb init writes the config askdb introspect reads. You don’t need the pg driver for this.
Someone with psql and a Postgres role that can read the catalog. A read-only role is enough.

1. Print the queries

npx askdb introspect templates --engine postgres > askdb-templates.sql

The file holds 12 queries, each headed by a -- <name> comment, such as -- tables and -- columns.

2. Fill in the schema filters

Each query takes two parameters: $1, the schemas to include, and $2, the schemas to skip. psql can’t run the queries until you replace them. NULL means no filter, so to export every schema:

sed -e 's/\$1::text\[\]/NULL::text[]/g' -e 's/\$2::text\[\]/NULL::text[]/g' \
  askdb-templates.sql > askdb-queries.sql

To export only some schemas, put a list in place of every $1 instead. Each query uses $1 and $2 twice, and the g at the end of each sed expression replaces them all:

sed -e "s/\$1::text\[\]/ARRAY['public','sales']::text[]/g" -e 's/\$2::text\[\]/NULL::text[]/g' \
  askdb-templates.sql > askdb-queries.sql

You can also export everything and filter in step 4.

3. Run each query and save it as CSV

The person with database access runs this, with their connection string in DATABASE_URL:

mkdir askdb-queries askdb-export
awk '/^-- /{name=$2; next} {print > ("askdb-queries/" name ".sql")}' askdb-queries.sql
for f in askdb-queries/*.sql; do
  psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 --csv -f "$f" > "askdb-export/$(basename "$f" .sql).csv"
done
echo '{ "engine": "postgres", "version": 1 }' > askdb-export/manifest.json

The first command splits the file into one query per file in askdb-queries/. The loop runs each one and saves its result in askdb-export/.

The export bundle, askdb-export/, now holds manifest.json and one CSV file for each query, such as tables.csv and columns.csv. psql --csv writes each file the way AskDB reads it: a header row with the query’s column names, t and f for booleans, and an empty field for null. Hand over askdb-export/.

4. Introspect from the export bundle

npx askdb introspect --engine postgres --from-export ./askdb-export

It writes the schema artifact to introspection.outputDir from your config, or to --out <dir>. --engine postgres matters when your config names another engine. --schemas, --exclude-schemas and --tables filter the export the same way they filter a live database.

5. Refresh it when the database changes

When the database changes, repeat steps 3 and 4. Introspecting again into the same output folder (introspection.outputDir or --out) keeps your tables/*.md, concepts.md and tenant-policy.md, and the sensitive flags in schema.json. Enrich your schema has the steps after a table or column is renamed.

If it fails

Error	Fix
`ERROR: there is no parameter $1` (from `psql`)	The queries still hold `$1` and `$2`. Do step 2.
`export bundle is missing manifest.json at <path>`	Write `manifest.json` into the export bundle (step 3).
`export bundle is missing views.csv or views.json`	A query’s result is missing. Run that query again and save its CSV.
`<file> header mismatch for template '<name>' (expected [...], got [...])`	The CSV’s header row doesn’t match the query’s columns. Save the result again with `psql --csv`, which writes the header as the query names it.
`unsupported export bundle version '2' (expected 1)`	`manifest.json` names another version. Write `"version": 1`, or print the queries again with the `askdb` you’re introspecting with.
`--from-export is currently supported only for --engine postgres (got <engine>)`	Your config names another engine. Pass `--engine postgres`.

The Introspection API reference describes the export bundle’s format in full, including JSON files instead of CSV.