External Tables

Overview

External tables allow you to connect Moose to database tables that are managed outside of your application. This is essential when working with:

• CDC (Change Data Capture) services like ClickPipes, Debezium, or AWS DMS
• Legacy database tables managed by other teams
• Third-party data sources with controlled schema evolution

When to Use External Tables

Configuration

import { OlapTable, LifeCycle } from "@514labs/moose-lib";
 
interface CdcUserData {
  id: string;
  name: string;
  email: string;
  updated_at: Date;
}
 
// Connect to CDC-managed table
const cdcUserTable = new OlapTable<CdcUserData>("cdc_users", {
  lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});

from moose_lib import OlapTable, OlapConfig, LifeCycle
from pydantic import BaseModel
from datetime import datetime
 
class CdcUserData(BaseModel):
    id: str
    name: str
    email: str
    updated_at: datetime
 
# Connect to CDC-managed table
cdc_user_table = OlapTable[CdcUserData]("cdc_users", OlapConfig(
    life_cycle=LifeCycle.EXTERNALLY_MANAGED
))

Getting Models for External Tables

New project: initialize from your existing ClickHouse

If you don’t yet have a Moose project, use init-from-remote to bootstrap models from your existing ClickHouse:

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

What happens:

• Moose introspects your database and generates table models in your project.
• If Moose detects ClickPipes (or other CDC-managed) tables, it marks those as EXTERNALLY_MANAGED and writes them into a dedicated external models file:
  • TypeScript: app/externalModels.ts
  • Python: app/external_models.py
• This is a best-effort detection to separate CDC-managed tables from those you may want Moose to manage in code.

How detection works (ClickPipes/PeerDB example):

• Moose looks for PeerDB-specific fields that indicate CDC ownership and versions, such as _peerdb_synced_at, _peerdb_is_deleted, _peerdb_version, and related metadata columns.
• When these are present, the table will be marked EXTERNALLY_MANAGED and emitted into the external models file automatically.

import { OlapTable, LifeCycle, ClickHouseEngines } from "@514labs/moose-lib";
import type { ClickHouseInt, ClickHouseDecimal, ClickHousePrecision, ClickHouseDefault } from "@514labs/moose-lib";
import typia from "typia";
 
export interface foo {
  id: string & typia.tags.Format<"uuid">;
  name: string;
  description: string | undefined;
  status: string;
  priority: number & ClickHouseInt<"int32">;
  is_active: boolean;
  metadata: string | undefined;
  tags: string[];
  score: (string & ClickHouseDecimal<10, 2>) | undefined;
  large_text: string | undefined;
  created_at: string & typia.tags.Format<"date-time"> & ClickHousePrecision<6>;
  updated_at: string & typia.tags.Format<"date-time"> & ClickHousePrecision<6>;
  _peerdb_synced_at: string & typia.tags.Format<"date-time"> & ClickHousePrecision<9> & ClickHouseDefault<"now64()">;
  _peerdb_is_deleted: number & ClickHouseInt<"int8">;
  _peerdb_version: number & ClickHouseInt<"int64">;
}
 
export const FooTable = new OlapTable<foo>("foo", {
  orderByFields: ["id"],
  engine: ClickHouseEngines.ReplacingMergeTree,
  ver: "_peerdb_version",
  settings: { index_granularity: "8192" },
  lifeCycle: LifeCycle.EXTERNALLY_MANAGED,
});

Existing project: mark additional external tables

If there are other tables in your DB that are not CDC-managed but you want Moose to treat as external (not managed by code):

1. Mark them as external in code

// In a file you control (not the external file yet)
const table = new OlapTable<MySchema>("my_table", {
  lifeCycle: LifeCycle.EXTERNALLY_MANAGED
});

table = OlapTable[MySchema](
  "my_table",
  OlapConfig(
    life_cycle=LifeCycle.EXTERNALLY_MANAGED
  )
)

2. Move them into the external models file

• Move the model definitions to your external file (app/externalModels.ts or app/external_models.py).
• Ensure your root file still loads only the external models via a single import:
  • Add import "./externalModels";from external_models import * in your app/index.tsapp/main.py file.

This keeps truly external tables out of your managed code path, while still making them available locally (and in tooling) without generating production DDL.

Important Considerations

Staying in sync with remote schema

For EXTERNALLY_MANAGED tables, keep your code in sync with the live database by running DB Pull. You can do it manually or automate it in dev.

moose db pull --connection-string <YOUR_CLICKHOUSE_URL>