Use Moose with Your Existing ClickHouse

What This Guide Does

This guide sets up a local ClickHouse development environment that mirrors your production database and enables code-first schema management:

1. Introspect your remote ClickHouse tables and generate TypeScript/Python data models
2. Create a local ClickHouse instance with your exact table schemas
3. Seed your local database with production data (optional)
4. Build APIs and pipelines on top of your ClickHouse data in code

How It Works

Local Development:

• Your production ClickHouse remains untouched
• You get a local ClickHouse instance that copies your remote table schemas
• All development happens locally with hot-reload

Production Deployment:

• When you deploy your code, it connects to your remote ClickHouse
• Any new tables, materialized views, or schema changes you create in code are automatically migrated to your target database
• Your existing data and tables remain intact

Prerequisites

Step 1: Install Moose

Install the Moose CLI globally to your system:

bash -i <(curl -fsSL https://fiveonefour.com/install.sh) moose

Step 2: Create Your Project

# Option 1: Provide connection string directly
moose init my-project --from-remote <YOUR_CLICKHOUSE_CONNECTION_STRING> --language typescript
 
# Option 2: Run without connection string for interactive setup
moose init my-project --from-remote --language typescript

# Option 1: Provide connection string directly
moose init my-project --from-remote <YOUR_CLICKHOUSE_CONNECTION_STRING> --language python
 
# Option 2: Run without connection string for interactive setup
moose init my-project --from-remote --language python

https://username:password@host:port/?database=database_name

moose init my-project --from-remote https://explorer:@play.clickhouse.com:443/?database=default --language typescript

# Generate code models from your existing ClickHouse tables
moose init my-project --from-remote https://explorer:@play.clickhouse.com:443/?database=default --language python

cd my-project
npm install

cd my-project
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

You should see: Successfully generated X models from ClickHouse tables

Explore Your Generated Models

If your database includes ClickPipes/PeerDB (CDC) tables

As noted above, when you use moose init --from-remote, Moose introspects your database. If it detects CDC‑managed tables (e.g., PeerDB/ClickPipes with fields like _peerdb_synced_at, _peerdb_is_deleted, _peerdb_version), it marks those as EXTERNALLY_MANAGED and writes them into a dedicated external models file. Your root file is updated to load these models automatically.

This separation is a best‑effort by the CLI to keep clearly CDC‑owned tables external. For other tables you don’t want Moose to manage, set the lifecycle to external and move them into the external file. See:

• External Tables documentation for more information on how external tables work.
• DB Pull for keeping models in sync with the remote schema.

Step 3: Start Development

Start your development server. This spins up a local ClickHouse instance that perfectly mirrors your production schema:

moose dev

What happens when you run moose dev:

• 🏗️ Creates a local ClickHouse instance with your exact table schemas in your project code
• 🔄 Hot-reloads migrations to your local infrastructure as you save code changes
• 🚀 Starts a web server for building APIs

Seed Your Local Database (Optional)

Copy real data from your production ClickHouse to your local development environment. This gives you realistic data to work with during development.

Why seed? Your local database starts empty. Seeding copies real data so you can:

• Test with realistic data volumes
• Debug with actual production data patterns
• Develop features that work with real data structures

moose seed clickhouse --connection-string <YOUR_CLICKHOUSE_CONNECTION_STRING> --limit 100000

# ClickHouse native protocol (secure connection)
clickhouse://username:password@host:9440/database

moose seed clickhouse --connection-string clickhouse://explorer:@play.clickhouse.com:9440/default --limit 100000

# You can omit --connection-string by setting an env var
export MOOSE_SEED_CLICKHOUSE_URL='clickhouse://username:password@host:9440/database'
 
# copy a limited number of rows (batched under the hood)
moose seed clickhouse --limit 100000

✓ Database seeding completed
Seeded 'local_db' from 'remote_db'
 
✓ table1: copied from remote
⚠️  table2: skipped (not found on remote)
✓ table3: copied from remote

Step 4: Build Your First API

Now that you have your data models, let’s build something useful! You can create APIs, materialized views, and applications with full type safety.

import { TargetSchema, materializedView } from "./myMaterializedView";
 
 
export interface QueryParams {
  date: Date;
  limit?: number;
}
 
const api = new Api<QueryParams, TargetSchema>("get-aggregated-data",
  async ({ date, limit = 10 }: QueryParams, { client, sql }) => {
    const query = sql`
        SELECT * FROM ${materializedView.targetTable} 
        WHERE date = ${date} 
        LIMIT ${limit}`;
 
    const result = await client.query.execute<TargetSchema>(query); // Type-safe query execution
    return result.json();
  }
);

// Data Model for your Materialized View- use type throughout your codebase
export interface TargetSchema {
  date: Date;
  count: number;
}
// Creates both the target table and the materialized view
export const materializedView = new MaterializedView<TargetSchema>({
  // Raw SQL with type-safe table references
  selectStatement: sql`SELECT count(*) as count, date FROM ${myTable} GROUP BY date`,
  selectTables: [myTable], // Automatic dependency resolution for cascading materialized views
  engine: ClickHouseEngines.MergeTree, // Can use which ever engine you want
  orderByFields: ["date"],
  tableName: "myAggregation",
  materializedViewName: "myAggregation_MV",
});

Known Limitations

What we’re working on:

• Selective table import (currently imports all tables)
• Default value annotations

Troubleshooting

Error: Failed to connect to ClickHouse

This guide shows exactly where to find your host, port, username, and password, and how to construct a valid HTTPS connection string.

ClickHouse CloudSelf-Hosted or Docker

1. Log into your ClickHouse Cloud console
2. Open your service details page
3. Click “Connect” in the sidebar

4. Select the HTTPS tab and copy the values shown
   • Host: e.g. your-service-id.region.clickhouse.cloud
   • Port: usually 8443
   • Username: usually default
   • Password: the password you configured

5. Build your connection string:

https://USERNAME:PASSWORD@HOST:PORT/?database=DATABASE_NAME

6. Example (with placeholders):

https://default:your_password@your-service-id.region.clickhouse.cloud:8443/?database=default

7. Optional: Test with curl

curl --user "USERNAME:PASSWORD" --data-binary "SELECT 1" https://HOST:PORT

moose dev fails to start

Double check Docker is running and you do not have any port conflicts.

• ClickHouse local runs on port 18123
• Your local webserver runs on port 4000
• Your local management API runs on port 5001

What’s Next?