Importing Database Catalogs

wvlet catalog import scans your database once and saves each table's schema as Wvlet table declarations in the catalog/ folder of your project. With those files committed next to your queries, the compiler resolves table references and their column types without connecting to the database — in your editor, in CI, or when working offline.

# Import from a profile defined in ~/.wvlet/profiles.json
wvlet catalog import --profile production

# Limit the import to one schema of a large warehouse
wvlet catalog import --profile production --schema sales

The generated files are ordinary Wvlet source files, one per schema:

catalog/mydb/sales.wv

-- Generated by `wvlet catalog import`. DO NOT EDIT.
-- Table declarations of mydb.sales, bound for offline query validation.
-- Re-run `wvlet catalog import` to refresh; add hand-written declarations in your own files.

table customers in mydb.sales = {
  customer_id: long
  name: string
}

table orders in mydb.sales = {
  order_id: long
  status: string
  price: decimal[10,2]
}

Each declaration is bound to its table location with in <catalog>.<schema> (see Data Models), so queries referencing those tables type-check against the saved schema and compile to correctly qualified SQL:

from sales.orders
where status = 'completed'
select order_id, price

Engine functions

The import also saves the functions of your database engine as native Wvlet function definitions in catalog/<name>/functions.wv, so engine-specific functions — not just tables — work offline:

catalog/mydb/functions.wv

-- Generated by `wvlet catalog import`. DO NOT EDIT.
-- Functions of the duckdb engine, bound for offline query validation.
-- Re-run `wvlet catalog import` to refresh; add hand-written defs in your own files.
def date_trunc(a1: string, a2: timestamp) in duckdb: timestamp = native
def regexp_extract(a1: string, a2: string) in duckdb: string = native

Calls to these functions compile to plain SQL calls of the target engine, exactly as if the definition did not exist — but with the function available in editor completion (with its signature) and its return type known to the compiler:

from sales.orders
select regexp_extract(status, '[a-z]+') as status_word

A few notes on what gets imported:

• Only scalar, aggregate, and window functions are imported. Table functions, pragmas, and macros use a different call syntax and are skipped.
• Functions whose names collide with Wvlet keywords, builtin functions the compiler already types (count, upper, ...), or standard-library definitions are skipped, so an import can never change how an existing query compiles.
• An overloaded function is collapsed into a single definition with any argument types, so the compiler accepts every call the engine would.
• Pass --no-functions to skip the functions import (a later full import without the flag refreshes the file; with the flag it is removed again).
• If one of your own files defines a function with the same name, the compiler reports a duplicate-definition warning and deterministically uses the definition from the first file in source-file name order. Rename one of them to remove the ambiguity.

Duplicate table names across schemas

When two schemas define a table with the same name (e.g. mydb.sales.orders and mydb.marketing.orders), the import writes a table orders in ... declaration into both schema files. The compiler can only resolve the name through one of them, so it reports a warning:

[warning] Duplicate type definition 'orders' (bound to mydb.marketing): also defined at
catalog/mydb/sales.wv:1:1 (bound to mydb.sales). Only the definition in catalog/mydb/sales.wv
is used for name lookup.

Queries that reference the winning definition keep working; references to the other schema's table may not resolve to the schema you expect. To fix the warning, keep only the schema you query (wvlet catalog import --schema sales), or remove one of the definitions — for hand-written declarations, rename one of them. The warning never fails the compilation, even with wvlet compile --strict.

Keeping catalogs up to date

Re-running wvlet catalog import overwrites the generated files. The output is deterministic — tables sorted by name, columns in table order — so git diff after an import shows exactly what changed in the database:

wvlet catalog import --profile production
git diff catalog/          # review schema drift
git add catalog/ && git commit -m "Update catalog snapshot"

Committed catalog files behave like a lockfile: the compile-time schema stays deterministic even while the database evolves, and queries still execute against the real tables. Schedule the import in CI or cron to keep the snapshot fresh.

Detecting schema drift

wvlet catalog diff compares every table declaration of your project — imported catalog files and hand-written declarations alike — against the connected database, and reports where the two have drifted apart. For each drifted table it prints a ready-to-run reshape migration block:

$ wvlet catalog diff --profile production
table users has drifted from its declaration. To migrate, run:
  reshape users {
    add created_at: timestamp
    exclude legacy_flag
    cast user_id as long -- the catalog has int
  }

• reshape operations have ensure semantics (add is a no-op when the column already exists; exclude when it is already gone; cast when the column already has the target type), so a generated migration is safe to re-run.
• A rename cannot be inferred from a diff — it shows up as an exclude/add pair, which would drop the column's data. When the output contains such a pair, the block includes a hint: replace the pair with a hand-written rename <old> as <new> to preserve the data.
• Column type drift becomes a cast <column> as <declared type> operation, with the current catalog type noted in a trailing comment.
• Declared tables that do not exist in the database are not drift: a declared table materializes automatically on the first write to it.

The command exits with a non-zero status when any drift is found, so it can gate CI:

# GitHub Actions example
- name: Check schema drift
  run: wvlet catalog diff --profile production

Applying the migrations

Pass --apply to run the generated reshape blocks against the database (sqldef / db:migrate style). The migrations execute through the regular compile-and-run path — exactly what pasting the blocks into a script would do — and the command re-diffs afterwards, exiting zero only when the catalog now matches the declarations:

$ wvlet catalog diff --profile production --apply
table users has drifted from its declaration. To migrate, run:
  reshape users {
    add created_at: timestamp
    exclude legacy_flag
  }
[info] Applied 1 migration(s); the catalog matches the declarations

Review the printed blocks before reaching for --apply on a production database: exclude drops the column and its data, and a rename always diffs as an exclude/add pair — apply a hand-written rename <old> as <new> first when that is what actually happened. A typical workflow gates CI with the plain check and runs --apply as an explicit deploy step.

Validating queries in CI

Because the catalog is plain source, query compilation needs no database credentials. Compile each query to catch syntax errors and — with --strict — the typing errors the compiler detects:

# GitHub Actions example
- name: Validate queries
  run: |
    for query in queries/*.wv; do
      wvlet compile --strict -f "$query"
    done

Editor support

The VS Code extension loads the catalog/ folder of your workspace automatically: after an import, table references resolve in the editor — diagnostics, column completion, and hover work offline against the imported schemas, and imported engine functions appear in name completion with their signatures.

Destructive statements are checked against the snapshot too: a drop table, reshape, or truncate targeting a table the imported catalog does not know gets an editor warning (not an error — the snapshot may be stale; re-run wvlet catalog import if the table was created after the import). Adding if exists to the statement acknowledges the table may be missing and silences the warning.

Notes

• Table and column names that are not plain identifiers are written as backquoted identifiers (e.g. `weird-table`).
• Column types that cannot be expressed in Wvlet's type syntax degrade to any, keeping the original type in a comment.
• System schemas (information_schema, pg_catalog, system) are skipped.
• The catalog/ folder is owned by the importer. Add your own type and model definitions in separate .wv files so a re-import never overwrites them.