Migrations are designed for two complementary goals:
How to think about it:
What you need to do:
moose generate migration --url https://<remote-env> --token <api-token> --saveplan.yaml) as neededremote_state.json, desired code vs ) and executes in order. If drift is detected, the deploy aborts; regenerate the plan and retry.local_infra_map.jsonplan.yamlUse 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 infrastructureMooseStack continuously monitors your code changes and applies migrations automatically. All changes are applied to your local database only.
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: StringWhen 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> --saveThis 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 generatedlocal_infra_map.json: A snapshot of the local database state at the time the plan was generatedThe 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.
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.
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.
import { newTable, newMaterializedView } from "./path/to/your/newObjects" // Export the new table and materialized view to apply changesexport { 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:
$ 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_targetThe generated plan for this operation will look like this:
- 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_targetAdding new fields to your data models:
interface AddedColumn { id: Key<string>; another_column: string; some_column: string;}interface AddedColumn { id: Key<string>; another_column: string; some_column: string; new_column: number; // New field - migration applied}In dev mode, you will see a log like this:
$ moose dev
⢹ Processing Infrastructure changes from file watcher
~ Table events:
Column changes:
+ new_column: Int64The generated plan for this operation will look like this:
- AddTableColumn: table: "events" column: name: "new_column" data_type: "Int64"Removing fields from your data models:
interface RemovedColumn { id: Key<string>; another_column: string; some_column: string; old_column: number;} export const events = new OlapTable<RemovedColumn>("events")interface RemovedColumn { id: Key<string>; another_column: string; some_column: string; // old_column field removed } export const events = new OlapTable<RemovedColumn>("events")In dev mode, you will see a log like this:
$ moose dev
⢹ Processing Infrastructure changes from file watcher
~ Table events:
Column changes:
- old_column: Int64The generated plan for this operation will look like this:
- DropTableColumn: table: "events" column_name: "old_column"interface ChangedType { id: Key<string>; some_column: string; // String type} export const events = new OlapTable<ChangedType>("events")interface ChangedType { id: Key<string>; some_column: string & LowCardinality; // Add LowCardinality for better performance} export const events = new OlapTable<ChangedType>("events")In dev mode, you will see a log like this:
$ 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:
- ChangeTableColumn: table: "events" column_name: "some_column" data_type: "LowCardinality(String)"Modifying the SELECT statement of a materialized view:
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",});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",});The dev mode diff:
$ moose dev
⠋ Processing Infrastructure changes from file watcher
~ Table target_table:
Column changes:
+ avg: Float64
~ SQL Resource: mv_to_targetNotice 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:
created_at: 2025-08-20T05:35:31.668353Zoperations: - 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_targetChanging a materialized view's SELECT statement will recreate the entire view and repopulate all data. This can be time-consuming for large datasets.