Applying Migrations

Viewing:

OLAP Migrations

This page covers OLAP migrations. For migrations across the MooseStack, see the Migrate docs.

Overview

Migrations are designed for two complementary goals:

Move fast locally by inferring changes from your code and applying them immediately to your local database.
Be deliberate in production by executing a reviewed, versioned plan that matches your intent and protects data.

How to think about it:

Development mode: You edit code, MooseStack infers the SQL and immediately applies it to local ClickHouse. Great for rapid iteration; not guaranteed to infer intent (e.g., renames).
Production (planned) mode: You generate a plan from the target environment vs your code, review and commit the plan, and MooseStack executes it deterministically during deploy with drift checks.

What you need to do:

In dev: just code. MooseStack handles local diffs automatically.
In prod (OLAP):
- Generate and save a plan:
```
moose generate migration --url https://<remote-env> --token <api-token> --save
```
- Review and edit the plan (plan.yaml) as needed
- Commit the plan to source control
- Deploy to production. MooseStack validates snapshots (current DB vs remote_state.json, desired code vs local_infra_map.json) and executes plan.yaml in order. If drift is detected, the deploy aborts; regenerate the plan and retry.

Development Workflow

Starting the Runtime

Use moose dev to start the MooseStack runtime with automatic migration detection:

moose dev
 
⡏ Starting local infrastructure
  Successfully started containers
     Validated clickhousedb-1 docker container
     Validated redpanda-1 docker container
  Successfully validated red panda cluster
     Validated temporal docker container
  Successfully ran local infrastructure

Hot-Reloaded Migrations

MooseStack continuously monitors your code changes and applies migrations automatically. All changes are applied to your local database only.

app/tables/events.ts

interface Event {
  id: Key<string>;
  name: string;
  createdAt: Date;
  status: string; // New field - will trigger migration
}
 
export const events = new OlapTable<Event>("events")

When you save changes, you’ll see live logs in the terminal showing the diffs being applied to your local database:

⢹ Processing Infrastructure changes from file watcher
             ~ Table events:
                  Column changes:
                    + status: String

Production Workflow

Prefer a reviewed plan?

Use planned migrations to generate, review, and apply OLAP DDL plans deterministically.

Generating Migration Plans

When using planned migrations for OLAP, you need to generate a migration plan from the remote environment. This is done by running the following command:

moose generate migration --url https://<remote-env> --token <api-token> --save

This generates a few files in the migrations directory:

plan.yaml: The migration plan containing an ordered list of operations to apply to the remote database to bring it into alignment with your local code.
remote_state.json: A snapshot of the remote database state at the time the plan was generated
local_infra_map.json: A snapshot of the local database state at the time the plan was generated

Why store the remote and local state?

The remote and local state are used to validate that the plan is still valid at the time of deployment. If there have been schema changes made to your live remote database since the plan was generated, the deployment will abort and you will need to regenerate the plan. This is to prevent you from dropping data unintentionally.

Reviewing and Editing the Plan

You can review and edit the plan as needed. The plan is a YAML file that contains an ordered list of operations to apply to the remote database to bring it into alignment with your local code.

migrations/plan.yaml

Applying the Plan

The plan is applied during deployment. MooseStack will validate that the remote database state matches the snapshot of the database state at the time the plan was generated, and applies plan.yaml in order; it aborts if snapshots don’t match current state.

Migration Types

Adding New Tables or Materialized Views

index.ts

import { newTable, newMaterializedView } from "./path/to/your/newObjects"
 
// Export the new table and materialized view to apply changes
export {
  newTable, // New table
  newMaterializedView // New materialized view
}

The dev mode will automatically detect the new table or materialized view and apply the changes to your local database. You will see a log like this in the terminal:

Terminal

$ moose dev
⠋ Processing Infrastructure changes from file watcher
             +  Table: new_table Version None - id: String, a_column: String, some_other_column: Float64 -  - deduplicate: false
             +  Table: target_table Version None - id: String, a_column: String, some_other_column: Float64 - id - deduplicate: false
             +  SQL Resource: mv_to_target

The generated plan for this operation will look like this:

migrations/plan.yaml

- CreateTable:
    table:
      name: new_table
      columns:
      - name: id
        data_type: String
        required: true
        unique: false
        primary_key: true
        default: null
        annotations: []
      - name: a_column
        data_type: String
        required: true
        unique: false
        primary_key: false
        default: null
        annotations: []
      - name: some_other_column
        data_type: Float64
        required: true
        unique: false
        primary_key: false
        default: null
        annotations: []
      order_by:
      - id
      deduplicate: false
      engine: MergeTree
      version: null
      metadata:
        description: null
      life_cycle: FULLY_MANAGED
- CreateTable:
    table:
      name: target_table
      columns:
      - name: id
        data_type: String
        required: true
        unique: false
        primary_key: true
        default: null
        annotations: []
      - name: a_column
        data_type: String
        required: true
        unique: false
        primary_key: false
        default: null
        annotations: []
      - name: some_other_column
        data_type: Float64
        required: true
        unique: false
        primary_key: false
        default: null
        annotations: []
      order_by:
      - id
      deduplicate: false
      engine: MergeTree
      version: null
      metadata:
        description: null
      life_cycle: FULLY_MANAGED
- RawSQL:
    sql: "CREATE MATERIALIZED VIEW mv_to_target TO target_table AS SELECT * FROM source_table"
    description: Running setup SQL for resource mv_to_target

Column Additions

Adding new fields to your data models:

Before

interface AddedColumn {
  id: Key<string>;
  another_column: string;
  some_column: string;
}

After

interface AddedColumn {
  id: Key<string>;
  another_column: string;
  some_column: string;
  new_column: number; // New field - migration applied
}

Before.py

class AddedColumn(BaseModel):
    id: Key[str]
    another_column: str
    some_column: str
 
table = OlapTable[AddedColumn]("events")

After.py

class AddedColumn(BaseModel):
    id: Key[str]
    another_column: str
    some_column: str
    new_column: int  # New field - migration applied
 
table = OlapTable[AddedColumn]("events")

In dev mode, you will see a log like this:

Terminal

$ moose dev
⢹ Processing Infrastructure changes from file watcher
             ~ Table events:
                  Column changes:
                    + new_column: Int64

The generated plan for this operation will look like this:

migrations/plan.yaml

- AddTableColumn:
    table: "events"
    column:
      name: "new_column"
      data_type: "Int64"

Column Removals

Removing fields from your data models:

Before

interface RemovedColumn {
  id: Key<string>;
  another_column: string;
  some_column: string;
  old_column: number;
}
 
export const events = new OlapTable<RemovedColumn>("events")

After

interface RemovedColumn {
  id: Key<string>;
  another_column: string;
  some_column: string;
  // old_column field removed 
}
 
export const events = new OlapTable<RemovedColumn>("events")

Before.py

class RemovedColumn(BaseModel):
    id: Key[str]
    another_column: str
    some_column: str
    old_column: int
 
 
table = OlapTable[RemovedColumn]("events")

After.py

class RemovedColumn(BaseModel):
    id: Key[str]
    another_column: str
    some_column: str
    # old_column field removed
 
table = OlapTable[RemovedColumn]("events")

In dev mode, you will see a log like this:

Terminal

$ moose dev
⢹ Processing Infrastructure changes from file watcher
             ~ Table events:
                  Column changes:
                    - old_column: Int64

The generated plan for this operation will look like this:

migrations/plan.yaml

- DropTableColumn:
    table: "events"
    column_name: "old_column"

Changing Column Data Types (use with caution)

Before

interface ChangedType {
  id: Key<string>;
  some_column: string; // String type
}
 
export const events = new OlapTable<ChangedType>("events")

After

interface ChangedType {
  id: Key<string>;
  some_column: string & LowCardinality; // Add LowCardinality for better performance
}
 
export const events = new OlapTable<ChangedType>("events")

Before.py

class ChangedType(BaseModel):
    id: Key[str]
    some_column: str
 
table = OlapTable[ChangedType]("events")

After.py

class ChangedType(BaseModel):
    id: Key[str]
    some_column: Annotated[str, "LowCardinality"]  # Add LowCardinality for better performance
 
table = OlapTable[ChangedType]("events")

In dev mode, you will see a log like this:

Terminal

$ moose dev
⢹ Processing Infrastructure changes from file watcher
             ~ Table events:
                  Column changes:
                    - some_column: String -> LowCardinality(String)

The generated plan for this operation will look like this:

migrations/plan.yaml

- ChangeTableColumn:
    table: "events"
    column_name: "some_column"
    data_type: "LowCardinality(String)"

Breaking type changes

Some data type changes can be incompatible with existing data. Read the guide to learn more.

Materialized View Changes

Modifying the SELECT statement of a materialized view:

Before.ts

import { sql } from "@514-labs/moose-lib";
 
interface TargetSchema {
  day: Date;
  count: number;
  sum: number;
}
 
const selectStatement = sql`
  SELECT 
    toStartOfDay(${sourceTable.columns.a_date}) as day, 
    uniq(${sourceTable.columns.id}) as count, 
    SUM(${sourceTable.columns.a_number}) as sum 
    FROM ${sourceTable} 
    GROUP BY day
`;
 
const mv = new MaterializedView<Target>({
  selectStatement,
  selectTables: [sourceTable],
  targetTable: {
    name: "target_table",
    engine: ClickHouseEngines.MergeTree,
    orderByFields: ["day"],
  },
  materializedViewName: "mv_table_to_target,
});

After.ts

import { sql } from "@514-labs/moose-lib";
 
interface TargetSchema {
  day: Date;
  count: number;
  sum: number;
  avg: number; // New column - migration applied
}
 
const selectStatement = sql`
  SELECT 
    toStartOfDay(${sourceTable.columns.a_date}) as day, 
    uniq(${sourceTable.columns.id}) as count, 
    sum(${sourceTable.columns.a_number}) as sum,
    avg(${sourceTable.columns.a_number}) as avg 
    FROM ${sourceTable} 
    GROUP BY day
`;
 
export const userStats = new MaterializedView<TargetSchema>({
  selectStatement,
  selectTables: [sourceTable],
  targetTable: {
    name: "target_table",
    engine: ClickHouseEngines.MergeTree,
    orderByFields: ["day"],
  },
  materializedViewName: "mv_table_to_target,
});

Before.py

from pydantic import BaseModel
 
class TargetSchema(BaseModel):
  day: Date;
  count: number;
  sum: number;
 
mv = MaterializedView[TargetSchema](MaterializedViewConfig(
    select_statement="""
    SELECT 
        toStartOfDay(a_date) as day,
        uniq(id) as count,
        sum(a_number) as sum
    FROM table
    GROUP BY day
    """
    target_table=OlapConfig(
        name="target_table",
        engine=ClickHouseEngines.MergeTree,
        order_by_fields=["day"],
    ),
    materialized_view_name="mv_table_to_target",
))

After.py

class TargetSchema(BaseModel):
  day: Date;
  count: number;
  sum: number;
  avg: number;
 
mv = MaterializedView[TargetSchema](MaterializedViewConfig(
    select_statement="""
    SELECT 
        toStartOfDay(a_date) as day,
        uniq(id) as count,
        sum(a_number) as sum,
        avg(a_number) as avg
    FROM table
    GROUP BY day
    """
    target_table=OlapConfig(
        name="target_table",
        engine=ClickHouseEngines.MergeTree,
        order_by_fields=["day"],
    ),
    materialized_view_name="mv_table_to_target",
))

The dev mode diff:

Terminal

$ moose dev
⠋ Processing Infrastructure changes from file watcher
             ~  Table target_table:
                  Column changes:
                    + avg: Float64
             ~  SQL Resource: mv_to_target

What is a SQL Resource?

Notice that the materialized view generates both a target table and a SQL resource. The target table creates a new table in the database to store the results of the materialized view SELECT statement. The SQL Resource is the SQL statement that is used to create the target table.

The generated plan for this operation will look like this:

migrations/plan.yaml

created_at: 2025-08-20T05:35:31.668353Z
operations:
  - RawSql:
      sql:
        - DROP VIEW IF EXISTS mv_table_to_target
      description: Running teardown SQL for resource mv_table_to_target
  - AddTableColumn:
      table: target_table
      column:
        name: "avg"
        data_type: "Float64"
  - RawSql:
      sql:
        - "CREATE MATERIALIZED VIEW IF NOT EXISTS mv_table_to_target \n        TO target_table\n        AS \n  SELECT \n    toStartOfDay(`a_date`) as day, \n    uniq(`id`) as count, \n    sum(`a_number`) as sum, \n    avg(`a_number`) as avg\n  FROM `source_table` \n  GROUP BY day"
        - "INSERT INTO target_table\n          \n  SELECT \n    toStartOfDay(`a_date`) as day, \n    uniq(`id`) as count, \n    sum(`a_number`) as sum, \n    avg(`a_number`) as avg\n  FROM `source_table` \n  GROUP BY day"
      description: Running setup SQL for resource mv_table_to_target

Note

Changing a materialized view’s SELECT statement will recreate the entire view and repopulate all data. This can be time-consuming for large datasets.

Syncing External Tables

Generating Migrations