# Moose / Olap / Planned Migrations Documentation – Python ## Included Files 1. moose/olap/planned-migrations/planned-migrations.mdx ## Planned Migrations (OLAP) Source: moose/olap/planned-migrations/planned-migrations.mdx Generate, review, and safely execute ClickHouse DDL plans # Planned Migrations 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. ## Why planned migrations? 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: - The database schema is shared with other services (e.g. Change Data Capture services like ClickPipes) - Other users (e.g. analysts) of the database may have credentials that let them change the schema 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. ## What this does - Generates an ordered set of ClickHouse operations and writes them to `./migrations/plan.yaml` - Saves two validation snapshots for drift detection: - `./migrations/remote_state.json` (state when plan was created) - `./migrations/local_infra_map.json` (desired state from your local code) - When enabled, validates state and executes the exact reviewed operations ## Prerequisites ```toml file="moose.config.toml" [features] olap = true ddl_plan = true ``` ## Generating a Plan Once 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:** ```bash filename="Terminal" copy moose generate migration --url https:// --token --save ``` **For serverless deployments:** ```bash filename="Terminal" copy moose generate migration --clickhouse-url clickhouse://user:pass@host:port/db --save ``` Outputs: ```text ./migrations/plan.yaml ./migrations/remote_state.json ./migrations/local_infra_map.json ``` What 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. ## Review and edit the plan 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. ```yaml filename="migrations/plan.yaml" copy # 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. ### When to edit the plan There are two main reasons to edit the plan: 1. To "override" the default assumptions Moose makes when it cannot infer the intent of your schema changes, such as renaming a column instead of dropping and adding. 2. To add new operations that are not covered by the default assumptions, such as adding a backfill operation to a new column. #### Rename a column instead of drop/add When you rename a column, Moose will default to dropping and adding the column. However, you can override this by using the `RenameTableColumn` operation: ```yaml filename="migrations/plan.yaml" copy - 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: color ``` In the plan, you can override this by using the `RenameTableColumn` operation: ```yaml filename="migrations/plan.yaml" copy created_at: 2025-08-20T05:35:31.668353Z - RenameTableColumn: table: source_table before_column_name: created_at after_column_name: createdAt ``` #### Add a backfill operation to a new column When 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: ```yaml filename="migrations/plan.yaml" copy - AddTableColumn: table: "source_table" column: name: "created_at" data_type: "DateTime" required: false unique: false default: NOW ## Specify the default value to the current timestamp ``` You can also override the the default behavior by using the `RawSql` operation to define your own custom backfill logic: ```yaml filename="migrations/plan.yaml" copy - 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" ``` ## Deployment Flows ### Moose Server Deployments For Moose server deployments (with `moose prod` running), migrations are applied automatically on startup. Generate plans using: ```bash filename="Terminal" copy moose generate migration --url https:// --token --save ``` When you deploy, Moose validates the plan and executes it automatically. ### Serverless Deployments For serverless deployments (no Moose server), you manage migrations manually using the ClickHouse connection directly: ```toml file="moose.config.toml" [state_config] storage = "clickhouse" [features] olap = true data_model_v2 = true ``` **Workflow:** 1. **Generate the plan** from your ClickHouse database: ```bash filename="Terminal" copy moose generate migration --clickhouse-url --save ``` 2. **Review** the generated `./migrations/` files in your PR 3. **Execute the plan** against your ClickHouse with CI/CD or manually: ```bash filename="Terminal" copy moose migrate --clickhouse-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: - If current tables in your live production database differ from `remote_state.json`, Moose aborts (remote drift since planning). - If desired tables in your local code differ from `local_infra_map.json`, Moose aborts (code changed since planning). - If both match, `plan.yaml` operations are executed in order against ClickHouse. ## Troubleshooting - Failure to connect to remote database? Make sure you have [your admin API key setup correctly](./apis/auth#admin-endpoints) - Plan rejected due to drift: Re-generate a plan against the current remote, review, and retry. - No execution in moose server deployments: Ensure `ddl_plan = true` and `./migrations/plan.yaml` exists. - OLAP disabled: Ensure `[features].olap = true`.