Start With Your ClickHouse Database

Why Use Moose with ClickHouse?

Moose provides a layer on top of your existing ClickHouse database to make it simpler to manage your tables, views, pipelines, and APIs in plain TypeScriptPython code:

Quick Setup

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

https://explorer:@play.clickhouse.com:443/?database=default

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

# Bootstrap from your ClickHouse database
moose init my-project --from-remote <YOUR_CLICKHOUSE_CONNECTION_STRING> --language python
 
# Set up dependencies
cd my-project
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt

# Bootstrap from your ClickHouse database
moose init my-project --from-remote <YOUR_CLICKHOUSE_CONNECTION_STRING> --language typescript
 
# Set up dependencies
cd my-project
npm install

moose dev

Type Mapping

Moose automatically maps ClickHouse data types to appropriate language-specific types:

What’s Next?

Now that you’ve got Moose talking to your ClickHouse database:

1. Get Some Data Locally

# CLI command to seed from remote ClickHouse
moose seed clickhouse --connection-string <YOUR_CLICKHOUSE_CONNECTION_STRING> --limit 100

2. Build Materialized Views That Don’t Break

Moose lets you define materialized views in code with automatic schema management and dependency orchestration:

What you’re probably doing now:

-- Step 1: Create target table (remember the exact schema!)
CREATE TABLE daily_user_stats (
    user_id UInt64,
    date Date,
    event_count UInt64
) ENGINE = MergeTree()
ORDER BY (user_id, date);
 
-- Step 2: Create materialized view (hope you got the dependency order right!)
CREATE MATERIALIZED VIEW daily_user_stats_mv TO daily_user_stats AS
SELECT 
    user_id,
    toDate(timestamp) as date,
    count(*) as event_count
FROM user_events 
GROUP BY user_id, date;
 
-- Step 3: Copy-paste this to staging... then production... 
-- Step 4: Pray you didn't miss anything when the schema changes
-- Step 5: Remember to DROP VIEW before changing SELECT logic

With Moose:

import { userEvents } from "./tables"; // Import existing tables
import { MaterializedView, sql } from "@514labs/moose-lib";
 
// Define your target schema
interface DailyUserStats {
  user_id: number;
  date: Date;
  event_count: number;
}
 
// Define what you want - Moose generates all the DDL
const dailyStats = new MaterializedView<DailyUserStats>({
  // ✅ SQL in actual code, directly reference table names and columns for autocompletion
  selectStatement: sql`
    SELECT 
      ${userEvents.user_id}, 
      toDate(${userEvents.timestamp}) as date, 
      count(*) as event_count
    FROM ${userEvents} 
    GROUP BY ${userEvents.user_id}, date
  `,
  selectTables: [userEvents],
  tableName: "daily_user_stats",
  materializedViewName: "daily_user_stats_mv",
  orderByFields: ["user_id", "date"]
});
 
// Moose automatically:
// ✅ Creates the target table with correct schema
// ✅ Sets up the materialized view 
// ✅ Handles dependency order
// ✅ Manages schema migrations
// ✅ Provides hot-reload in development

from moose_lib import MaterializedView, OlapTable, MaterializedViewOptions
from pydantic import BaseModel
from datetime import date
from app.tables import user_events ## Import existing tables
 
## Define your target schema
class DailyUserStats(BaseModel):
    user_id: int
    date: date  
    event_count: int
 
 
# Define what you want - Moose generates all the DDL
daily_stats = MaterializedView[DailyUserStats](
    MaterializedViewOptions(
        select_statement="""
            SELECT user_id, toDate(timestamp) as date, count(*) as event_count
            FROM user_events GROUP BY user_id, date
        """,
        select_tables=[user_events],
        table_name="daily_user_stats",
        materialized_view_name="daily_user_stats_mv",
        order_by_fields=["user_id", "date"]
    )
)
 
# Moose automatically:
# ✅ Creates the target table with correct schema  
# ✅ Sets up the materialized view
# ✅ Handles dependency order
# ✅ Manages schema migrations
# ✅ Provides hot-reload in development

Ready to dive deeper? → Materialized Views docs

3. Build APIs That Handle the Annoying Stuff

Consumption APIs are a way to write native TypeScriptPython functions that wrap your ClickHouse queries and make them available for your frontend clients via a type-safe REST API.

They are designed to take care of the boilerplate that slows you down like:

• Request validation
• Response type mapping
• SQL injection protection
• Documentation
• JWT validation (if you’re using it)

Ready to dive deeper? → Consumption APIs docs

4. Use AI to Generate Analytics & APIs

Want AI to generate insights and APIs from your data? Try Aurora - our AI analytics agent for Moose projects.

Current Limitations

What we’re working on adding:

• Selective table import: Imports all tables (filtering coming soon)
• Default values: Adding annotations to your models to set default values for your tables
• Advanced ClickHouse types:
  • Map(K, V) types
  • Tuple(T1, T2, ...) types
  • Geo types (Point, Ring, Polygon, etc.)

Known Limitations

Supported Types with Manual Type Adjustments:

LowCardinality(T)

Currently, LowCardinality(T) maps to base type T.

You can override the autogenerated data models by making the following adjustments to fields that are LowCardinality in your generated code:

interface MyModel {
  status: string & LowCardinality; // Optimized for low cardinality
  category: string;                // Regular string
}

from typing import Literal
 
class MyModel(BaseModel):
    status: Literal["active", "inactive", "pending"]  # Automatically 
    category: str                                     # Regular string

JSON types

Enum types

Enum column types that have an empty string as a key will be skipped:

CREATE TABLE my_table (
    my_enum Enum8(""=0, "A"=1, "B"=2)
);

## This does not work:
class my_enum(StringToEnumMixin, IntEnum):
    "" = 0 ## Illegal to have an empty string as a key
    A = 1
    B = 2
 
## Instead what will be generated is:
class my_enum(StringToEnumMixin, IntEnum):
    = 0 ## Will lead to a type error, consider using a different key
    A = 1
    B = 2

CREATE TABLE my_table (
    my_enum Enum8("1"=0, "A"=1, "B"=2)
);

class my_enum(StringToEnumMixin, IntEnum):
    1 = 0 ## Will lead to a type error, consider using a different key
    A = 1
    B = 2