Syncing External Tables

What this is

Use moose db pull to refresh the definitions of tables you marked as EXTERNALLY_MANAGED from a live ClickHouse instance. It reads your code to find external tables, fetches their remote schemas, regenerates one external models file, and creates a small git commit if anything changed. If new external tables were added remotely (e.g., new CDC streams), they are added to the external models file as part of the same run.

When to use it

• External tables changed remotely: a DBA, CDC, or ETL pipeline updated schema.
• Keep types in sync: update generated models without touching fully-managed tables.
• Safe by design: does not modify the database or your managed models.

Requirements

• Tables are defined with lifeCycle: EXTERNALLY_MANAGED (TypeScript) or life_cycle=LifeCycle.EXTERNALLY_MANAGED (Python)
• A ClickHouse connection string (native or HTTP/S)

Connection strings

db pull accepts both native and HTTP(S) URLs. Native strings are automatically converted to HTTP(S) with the appropriate ports.

Examples:

What gets written

db pull treats this file as the single source of truth for EXTERNALLY_MANAGED tables. It introspects the remote schema, updates existing external tables, and adds any newly detected external tables here. It does not modify models elsewhere in your codebase.

Important:

• The file is overwritten on every run (or at the path passed via --file-path).
• If you customize the path, ensure your root file imports it so Moose loads your external models.

How it works

When you run db pull the CLI does the following:

• Loads your project's infrastructure map and identifies tables marked as EXTERNALLY_MANAGED.
• Connects to the remote ClickHouse specified by --clickhouse-url and introspects the live schemas for those tables.
• Regenerates a single external models file that mirrors the remote schema.
• Adds any newly detected external tables from the remote database to the generated file so your code stays in sync as sources evolve.
• Does not change any fully managed tables, your app/index.ts (TypeScript) or app/main.py (Python), or the database itself.
• Creates a small git commit if the generated file changed, so you can review and share the update.

Example output

Command

• --clickhouse-url: ClickHouse URL (e.g., clickhouse://user:pass@host:port/database or https://user:pass@host:port/database). Can also be set via CLICKHOUSE_URL environment variable.
• --file-path: Optional. Override the default output file. The file at this path will be regenerated (overwritten) on each run.

Typical Use Cases

Remote schema changed; update local types

Your DBA, CDC pipeline (e.g., ClickPipes), or ETL job updated a table's schema. To keep your code accurate and type-safe, refresh your external models so queries, APIs, and materialized views reference the correct columns and types.

This updates only EXTERNALLY_MANAGED models and leaves managed code untouched.

Automatically run on dev startup (keep local fresh)

In active development, schemas can drift faster than you commit updates. Running db pull on dev startup helps ensure your local code matches the live schema you depend on.

Add to moose.config.toml:

This runs once when the dev server first starts. The CLI will read the URL from the CLICKHOUSE_URL environment variable. To run after code reloads, use on_reload_complete_script.

New project from an existing DB

If you're starting with an existing ClickHouse database, bootstrap code with init --from-remote, then use db pull over time to keep external models fresh:

A new CDC/external table appeared; add it to code

Your CDC pipeline created a new table (or exposed a new stream). Pull to add the new table to your external models file automatically.

The regenerated external models file will now include the newly discovered external table.

Troubleshooting

• No changes written: Ensure tables are actually marked as EXTERNALLY_MANAGED and names match remote.
• Unsupported types: The CLI will list tables with unsupported types; they're skipped in the generated file.
• Auth/TLS errors: Verify scheme/ports (8123 or 8443) and credentials; try HTTPS if native URL fails.
• Git commit issues: The command attempts a lightweight commit; commit manually if your working tree is dirty.

Related

• External Tables: concepts and configuration
• Initialize from ClickHouse: bootstrap projects from an existing DB
• Data Types: mapping and constraints