1. MooseStack
  3. Use with Existing ClickHouse

Use Moose with Your Existing ClickHouse

5 min setup
Zero disruption to prod
Code-first, local dev

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

What You Get

Schema management in code

Define ClickHouse tables, materialized views, and schemas as TypeScript/Python code

Auto-generated models

TypeScript/Python classes generated from your existing ClickHouse tables

Local development environment

Local ClickHouse instance that mirrors your production schema

Type-safe APIs

Build REST APIs with full type safety and autocomplete

Automatic migrations

Schema changes are automatically applied to your target database on deployment

Prerequisites

Before you start

Existing ClickHouse Database

Any ClickHouse instance (Cloud, self-hosted, or playground)

Node.js 20+

Required for TypeScript development

Download →

Docker Desktop

For local development environment

Download →

macOS/Linux

Windows works via WSL2


Step 1: Install Moose

Install the Moose CLI globally to your system:

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

After installation, you'll use moose init to create a new project that automatically connects to your ClickHouse and generates all the code you need.

Step 2: Create Your Project

Don't have ClickHouse?

Use the ClickHouse Playground tab to try it out!

# 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

Connection String Format:

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

If you don't provide a connection string, Moose will guide you through an interactive setup process where you'll be prompted to enter:

  • Host and port (e.g., https://your-service-id.region.clickhouse.cloud:8443)
  • Username (usually default)
  • Password (your ClickHouse password)
  • Database name (optional, defaults to default)

This is perfect if you're not sure about your connection details or prefer a guided experience.

What gets generated

Moose will create a complete project structure with:

  • Data models: TypeScript/Python classes for every table in your ClickHouse
  • Type definitions: Full type safety for all your data
  • Development environment: Local ClickHouse instance that mirrors your production schema
  • Build tools: Everything configured and ready to go

cd my-project
npm install

You should see: Successfully generated X models from ClickHouse tables

Explore Your Generated Models

Check what Moose created from your tables in the app/index.ts file:

    • index.ts
Export Pattern
Your generated table models are exported here so Moose can detect them.
Learn more about export pattern: local development / hosted

ClickPipes/PeerDB (CDC) Tables

If your database includes CDC‑managed tables (e.g., PeerDB/ClickPipes tables) MooseStack will mark those as EXTERNALLY_MANAGED and write them into a dedicated external models file. Your root file will be updated to load these models automatically.

Introspection Process

The introspection process will look for the fields _peerdb_synced_at, _peerdb_is_deleted, and _peerdb_version to determine if a table is CDC‑managed.

    • index.ts
    • externalModels.ts

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:

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 schema changes to your local infrastructure as you save code changes
  • Starts a web server for building APIs
Production Safety

Your production ClickHouse remains completely untouched. This is a separate, local development environment.

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

Connection String Format: The connection string must use ClickHouse native protocol:

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

Note: Data transfer uses ClickHouse's native TCP protocol via remoteSecure(). The remote server must have the native TCP port accessible. The command automatically handles table mismatches gracefully.

Notes
  • --limit and --all are mutually exclusive
  • --all can be used to copy the entire table(s), use with caution as it can be very slow and computationally intensive.
  • Large copies are automatically batched to avoid remote limits; you'll see per-batch progress.
  • If you stop with Ctrl+C, the current batch finishes and the command exits gracefully.

Expected Output:

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

Troubleshooting:

  • Tables that don't exist on remote are automatically skipped with warnings
  • Use --table <name> to seed a specific table that exists in both databases
  • Check moose ls table to see your local tables

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.

What you can build
  • REST APIs that expose your ClickHouse data to frontend applications
  • Materialized Views for faster queries and aggregations
  • Streaming pipelines for real-time data processing
  • Full-stack applications with your ClickHouse data as the backend

Add APIs

Build REST APIs on top of your existing tables to expose your data to your user-facing apps. This is a great way to get started with Moose without changing any of your existing pipelines.

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();  });
Building APIs with Moose

Check out the MooseAPI module for more information on building APIs with Moose.

Known Limitations

Early Access Feature

Some advanced ClickHouse features may not be fully supported yet. Join the Moose Slack and let us know if you have any issues, feedback, or requests.

What we're working on:

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

Troubleshooting

What's Next?

Ready to build more? Check out the APIs and OLAP documentation to learn how to build powerful features on top of your ClickHouse data.

On this page

What This Guide DoesHow It WorksPrerequisitesStep 1: Install MooseStep 2: Create Your ProjectExplore Your Generated ModelsClickPipes/PeerDB (CDC) TablesStep 3: Start DevelopmentSeed Your Local Database (Optional)Step 4: Build Your First APIAdd APIsKnown LimitationsTroubleshootingWhat's Next?
FiveonefourFiveonefour
Fiveonefour Docs
MooseStackTemplatesGuides
Release Notes
Source523
  • Overview
Build a New App
  • 5 Minute Quickstart
  • Browse Templates
  • Existing ClickHouse
Add to Existing App
  • Next.js
  • Fastify
Fundamentals
  • Moose Runtime
  • MooseDev MCP
  • Data Modeling
Moose Modules
  • Moose OLAP
  • Moose Streaming
  • Moose Workflows
  • Moose APIs & Web Apps
Deployment & Lifecycle
  • Moose Migrate
  • Moose Deploy
Reference
  • API Reference
  • Data Types
  • Table Engines
  • CLI
  • Configuration
  • Observability Metrics
  • Help
  • Release Notes
Contribution
  • Documentation
  • Framework

Before you start

Existing ClickHouse Database

Any ClickHouse instance (Cloud, self-hosted, or playground)

Node.js 20+

Required for TypeScript development

Download →

Docker Desktop

For local development environment

Download →

macOS/Linux

Windows works via WSL2

# 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
cd my-project
npm install

Check what Moose created from your tables in the app/index.ts file:

    • index.ts
Export Pattern
Your generated table models are exported here so Moose can detect them.
Learn more about export pattern: local development / hosted
    • index.ts
    • externalModels.ts
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();  });