Migration planning is a new way to have more fine-grained control over HOW database schema changes are applied to your database when you deploy your code into production.
Most database migrations are designed under the assumption that your code is the sole owner of the database schema. In OLAP databases, we have to be more careful and assume that schema changes can happen at any time:
This is why the plan is generated from the remote environment, and validated against the live state of the database at the time of deployment. If it detects a drift, it will abort the deployment and require you to regenerate the plan, to make sure you are not dropping data unintentionally.
Planned migrations apply only to OLAP (ClickHouse) schema changes. Streaming, APIs, and processes are unaffected by this flow.
./migrations/plan.yaml./migrations/remote_state.json (state when plan was created)./migrations/local_infra_map.json (desired state from your local code)[features]olap = trueddl_plan = trueOnce done editing your code in your feature branch, you can generate a plan that diffs your local code against your live remote database:
For Moose server deployments:
moose generate migration --url https://<remote-env> --token <api-token> --saveFor serverless deployments:
moose generate migration --clickhouse-url clickhouse://user:pass@host:port/db --saveOutputs:
./migrations/plan.yaml./migrations/remote_state.json./migrations/local_infra_map.jsonWhat each file contains:
remote_state.json: The state of the remote database when the plan was generated.local_infra_map.json: The state of the local code when the plan was generated.plan.yaml: The plan to apply to the remote database based on the diff between the two states.You will commit the entire migrations/ directory to version control, and Moose will automatically apply the plan when you deploy the code to production.
Moose makes some assumptions about your schema changes, such as renaming a column instead of dropping and adding. You can modify the plan to override these assumptions.
Open plan.yaml in your PR. Operations are ordered (teardown first, then setup) to avoid dependency issues. Review like regular code. You can also edit the plan to override the default assumptions Moose makes.
# Drop a deprecated column- DropTableColumn: table: "events" column_name: "deprecated_field" # Rename a column to match code- RenameTableColumn: table: "events" before_column_name: "createdAt" after_column_name: "created_at" # Add a new nullable column after created_at- AddTableColumn: table: "events" column: name: "status" data_type: "String" required: false unique: false primary_key: false default: null annotations: [] comment: null after_column: "created_at" # Change a column type to Nullable(Float64)- ModifyTableColumn: table: "events" before_column: name: "value" data_type: "Float64" required: false unique: false primary_key: false default: null annotations: [] comment: null after_column: name: "value" data_type: Nullable: nullable: "Float64" required: false unique: false primary_key: false default: null annotations: [] comment: null # Create a simple view via raw SQL- RawSql: sql: - "CREATE VIEW IF NOT EXISTS `events_by_user` AS SELECT user_id, count() AS c FROM events GROUP BY user_id" description: "Creating view events_by_user"You can edit the plan to override the default assumptions Moose makes.
There are two main reasons to edit the plan:
When you rename a column, Moose will default to dropping and adding the column. However, you can override this by using the RenameTableColumn operation:
import { OlapTable } from "@514-labs/moose-lib"; interface SourceSchema { created_at: Date; color: string;} interface TargetSchema { createdAt: Date; color: string;} export const sourceTable = new OlapTable<SourceSchema>("source_table"); export const targetTable = new OlapTable<TargetSchema>("target_table");- DropTableColumn: table: source_table column_name: created_at- AddTableColumn: table: source_table column: name: createdAt data_type: DateTime required: true unique: false primary_key: false default: null annotations: [] after_column: colorIn the plan, you can override this by using the RenameTableColumn operation:
created_at: 2025-08-20T05:35:31.668353Z - RenameTableColumn: table: source_table before_column_name: created_at after_column_name: createdAtWhen you add a new column, Moose will default to backfilling the column based on the value in the default field.
If your field is a DateTime, you can edit the plan to set the default value to the current timestamp:
- AddTableColumn: table: "source_table" column: name: "created_at" data_type: "DateTime" required: false unique: false default: NOW ## Specify the default value to the current timestampYou can also override the the default behavior by using the RawSql operation to define your own custom backfill logic:
- AddTableColumn: table: "source_table" column: name: "created_at" data_type: "DateTime" required: false unique: false default: null- RawSql: sql: - "UPDATE events SET created_at = toDateTime(created_at_ms / 1000) WHERE created_at IS NULL" description: "Backfill created_at from created_at_ms"For Moose server deployments (with moose prod running), migrations are applied automatically on startup. Generate plans using:
moose generate migration --url https://<remote-env> --token <api-token> --saveWhen you deploy, Moose validates the plan and executes it automatically.
For serverless deployments (no Moose server), you manage migrations manually using the ClickHouse connection directly:
[state_config]storage = "clickhouse" [features]olap = truedata_model_v2 = trueWorkflow:
moose generate migration --clickhouse-url <url> --saveReview the generated ./migrations/ files in your PR
Execute the plan against your ClickHouse with CI/CD or manually:
moose migrate --clickhouse-url <url>Before applying the plan, Moose will first validate that the snapshot of your database that was taken when you generated the plan is still the same as the current database state. If it is not, Moose will abort the deployment. If it is, Moose will execute the plan in plan.yaml against your production database.
Execution rules:
remote_state.json, Moose aborts (remote drift since planning).local_infra_map.json, Moose aborts (code changed since planning).plan.yaml operations are executed in order against ClickHouse.ddl_plan = true and ./migrations/plan.yaml exists.[features].olap = true.