# Moose / Olap / Apply Migrations Documentation – TypeScript ## Included Files 1. moose/olap/apply-migrations/apply-migrations.mdx ## Applying Migrations Source: moose/olap/apply-migrations/apply-migrations.mdx How to apply migrations to your database # Applying 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: ```bash moose generate migration --url https:// --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: ```bash 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**. ```ts filename="app/tables/events.ts" copy interface Event { id: Key; name: string; createdAt: Date; status: string; // New field - will trigger migration } /planned-migrations`} ctaLabel="Planned Migrations (OLAP)" compact> 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: ```bash moose generate migration --url https:// --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 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. ```yaml filename="migrations/plan.yaml" copy ``` ### 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 ```typescript filename="index.ts" {4-7} copy // 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: ```bash filename="Terminal" copy $ 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: ```yaml filename="migrations/plan.yaml" copy - 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: ```ts filename="Before" copy interface AddedColumn { id: Key; another_column: string; some_column: string; } ``` ```ts filename="After" copy interface AddedColumn { id: Key; another_column: string; some_column: string; new_column: number; // New field - migration applied } ``` In dev mode, you will see a log like this: ```bash filename="Terminal" copy $ 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: ```yaml filename="migrations/plan.yaml" copy - AddTableColumn: table: "events" column: name: "new_column" data_type: "Int64" ``` ### Column Removals Removing fields from your data models: ```ts filename="Before" copy interface RemovedColumn { id: Key; another_column: string; some_column: string; old_column: 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({ selectStatement, selectTables: [sourceTable], targetTable: { name: "target_table", engine: ClickHouseEngines.MergeTree, orderByFields: ["day"], }, materializedViewName: "mv_table_to_target, }); ``` ```ts filename="After.ts" copy 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 `;