dbmigrateRecipe 1: Refresh Staging with Selected, Masked Data
Start with a concrete test case, include the relations it needs, and review the final values before copying.
1. Choose the connection pair
Select same-engine source and target databases. Set appropriate connection protection levels and use database permissions that match the task.
2. Select the useful subset
Use a WHERE condition, primary-key row selection, or a row limit. Include the required parent and child rows; a limit alone is not a representative random sample.
3. Review masking and overrides
Assign approved Faker, salted-hash, regex, fixed-string, or NULL rules. Check deterministic behavior for related values, and review column overrides because they run after masking.
4. Review the plan and result
Inspect selections, warnings, and target values. Confirm the copy, retain the local run record, and validate that the resulting dataset supports your test case.
Recipe 2: Plan a PostgreSQL NOT NULL Change
Use staged changes to reduce long scans during the final constraint change. ALTER TABLE still requires locks; test the sequence against your workload.
1. Add a nullable column
Review `ALTER TABLE orders ADD COLUMN status_code varchar(32);`. Adding a column still needs a table lock, so set an appropriate lock timeout and plan around active transactions.
2. Backfill with application-aware batches
Use your approved SQL or migration tooling to backfill existing rows in bounded batches. Handle concurrent writes and confirm there are no remaining NULL values.
3. Add and validate a check constraint
Add `CHECK (status_code IS NOT NULL) NOT VALID`, then validate it separately. NOT VALID skips the initial existing-row scan; it does not eliminate lock acquisition.
4. Apply the final constraint
Review `ALTER TABLE orders ALTER COLUMN status_code SET NOT NULL;`. PostgreSQL can avoid another table scan when a validated check proves the column contains no NULL values. Retain or remove the helper constraint in a separately reviewed change.
Recipe 3: Plan for MySQL DDL Implicit Commits
A successful rehearsal helps catch plan errors. It does not make a multi-statement MySQL schema change transactional.
1. Understand the commit boundary
Statements such as ALTER TABLE can implicitly commit. A later failure does not undo earlier successful DDL; atomic DDL is statement-level protection, not a multi-statement rollback.
2. Rehearse the schema plan
Use a shadow dry run to inspect the DDL sequence before applying it. Review production permissions, locks, data volume, and workload conditions separately.
3. Review recovery before applying
Inspect generated recovery SQL and preserve an appropriate backup or export for data recovery. Schema rollback scripts do not restore copied or removed row values.
Recipe 4: Define a Useful CI Schema Drift Check
Make the expected schema explicit so a difference has a clear meaning and a clear owner.
1. Prepare the runner
Use an approved dbmt installation on a supported runner with access to the intended databases. Review the installed version’s command help before adapting pipeline examples.
2. Build the baseline
Apply your reviewed repository migrations to a fresh expected-state database using your existing migration tooling.
3. Compare and review
Configure a same-engine source/target profile and run `dbmt compare`. Preserve the comparison output and define which outcomes should block your pipeline.
4. Resolve intentional differences
Document environment-specific objects and expected release changes. Reconcile unexpected drift through the normal review process instead of automatically overwriting the target.
Need tailored advice for your database cluster?Explore the CLI reference or compare dbmigrate with legacy tools like Flyway, pgAdmin, and MySQL Workbench.