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, and regenerates one external models file. 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 URL

Connection strings

db pull accepts HTTP(S) URLs. Examples:

# HTTPS with credentials in URL
moose db pull --clickhouse-url "https://user:pass@your-instance.clickhouse.cloud:8443/database"
 
# HTTPS with query params
moose db pull --clickhouse-url "https://play.clickhouse.com/?user=explorer&database=default"
 
# Local HTTP
moose db pull --clickhouse-url "http://localhost:8123/?user=default&database=default"

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.
• Leaves the changes unstaged so you can review them with git diff before committing.

Example output

// AUTO-GENERATED FILE. DO NOT EDIT.// This file will be replaced when you run `moose db pull`. // ...typed table definitions matching remote EXTERNALLY_MANAGED tables...

Command

moose db pull [--clickhouse-url <URL>] [--file-path <OUTPUT_PATH>]

• --clickhouse-url: Optional. ClickHouse URL (e.g., https://user:pass@host:8443/database). If not provided, the URL is resolved in this order:
  1. MOOSE_CLICKHOUSE_CONFIG__URL environment variable
  2. Saved URL from moose init --from-remote (stored in keychain)
  3. [dev.remote_clickhouse] config in moose.config.toml (with keychain credentials)
• --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.

moose db pull --clickhouse-url <YOUR_CLICKHOUSE_URL>

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:

[http_server_config]on_first_start_script = "moose db pull"

This runs once when the dev server first starts. The CLI will resolve credentials from:

• MOOSE_CLICKHOUSE_CONFIG__URL environment variable
• Saved URL from moose init --from-remote (stored in keychain)
• [dev.remote_clickhouse] config (if configured) with credentials from keychain

To run after every code reload instead, use on_reload_complete_script:

[http_server_config]on_reload_complete_script = "moose db pull"

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:

moose init my-project --from-remote $REMOTE_CLICKHOUSE_URL --language <typescript|python>

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.

moose db pull --clickhouse-url <YOUR_CLICKHOUSE_URL>

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 for HTTP, 8443 for HTTPS) and credentials.

Related

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