Skip to content

Database Migrations

Before You Read

This guide assumes you have Bitbucket access to the oro-database-migrations repository and understand the basic Database Access Patterns. Migrations run through Bitbucket Pipelines — no local Flyway installation is needed for normal workflows.

How It Works

All Cloud SQL schema migrations live in the oro-database-migrations repository. The system uses Flyway for version-controlled migrations with a three-stage validation pipeline before anything touches a real database.

Developer pushes branch → PR opened → Pipeline validates (3 stages) → PR merged to main → Auto-applies to dev
                                                                                         ↓
                                                                          Manual trigger → Staging
                                                                          Manual trigger → Production

Repository Layout

oro-database-migrations/
├── config/                         # Per-service database connection configs
│   ├── microservice-monolith/
│   │   └── dev.conf
│   ├── microservice-identity/
│   │   └── dev.conf
│   ├── microservice-communication/
│   │   └── dev.conf
│   └── microservice-analytics/
│       └── dev.conf
│
├── migrations/                     # SQL files organized by service and environment
│   ├── microservice-monolith/
│   │   ├── dev/                    # V*.sql and U*.sql files
│   │   └── staging/                # Auto-promoted from dev (generated)
│   ├── microservice-identity/
│   ├── microservice-communication/
│   └── microservice-analytics/
│
├── scripts/                        # Bash helpers used by pipelines
│   ├── apply-migration.sh
│   ├── validate-migration.sh
│   ├── clone-dryrun-migration.sh
│   ├── rollback-migration.sh
│   ├── repair-migration.sh
│   ├── extract-pr-metadata.sh
│   ├── setup-ssh-tunnel.sh
│   ├── cleanup-ssh-tunnel.sh
│   └── oro-deployment-gate/        # Ad-hoc single-file migration scripts
│
├── MIGRATION_TEMPLATE.sql          # Template for new forward migrations
└── ROLLBACK_TEMPLATE.sql           # Template for rollback scripts

Supported Services

Service Database Dev Host Staging Host
microservice-monolith db_microservice_monolith db-int.dev.orofi.xyz db-int.stage.orofi.xyz
microservice-identity db_microservice_identity db-int.dev.orofi.xyz db-int.stage.orofi.xyz
microservice-communication db_microservice_communication db-int.dev.orofi.xyz db-int.stage.orofi.xyz
microservice-analytics db_microservice_analytics db-int.dev.orofi.xyz db-int.stage.orofi.xyz

All migrations run as the flyway_admin database user (password stored in Bitbucket as FLYWAY_PASSWORD / FLYWAY_STAGING_PASSWORD). The database host is reached through an SSH tunnel via bastion (35.226.57.140) — you never connect directly.

Creating a Migration

1. Name your files correctly

Every migration requires two files: a forward migration (V) and a rollback migration (U). They must have a matching version number and hash suffix.

Forward migration: 

V{VERSION}__{DESCRIPTION}__migration_{HASH}.sql

Rollback migration: 

U{VERSION}__{DESCRIPTION}__migration_{HASH}.sql

Example pair: 

V3__AddLenderTable__migration_a1b2c3d4.sql
U3__AddLenderTable__migration_a1b2c3d4.sql

Rules: - Version numbers must be sequential integers (no gaps) - Use __ (double underscore) as the separator - The 8-character hash must match between V and U files - Place files under migrations/{SERVICE_NAME}/{ENVIRONMENT}/

2. Write the forward migration

Use MIGRATION_TEMPLATE.sql as your starting point:

-- Migration: V3__AddLenderTable
-- Date: 2026-04-02
-- Author: Your Name
-- Service: microservice-monolith
-- Environment: dev

START TRANSACTION;

CREATE TABLE lenders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

COMMIT;

3. Write the rollback migration

Use ROLLBACK_TEMPLATE.sql. Rollbacks use stored procedures to make them idempotent — safe to run even if partially applied or already rolled back.

-- Rollback Migration: U3__AddLenderTable

START TRANSACTION;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `migration_id` = 'AddLenderTable') THEN
        DROP TABLE IF EXISTS lenders;
    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

COMMIT;

Idempotency is required

The pipeline validates that rollback (U*) scripts use IF EXISTS clauses. A rollback without IF EXISTS on DROP statements will fail validation.

4. Place files and create your branch

migrations/
└── microservice-monolith/
    └── dev/
        ├── V3__AddLenderTable__migration_a1b2c3d4.sql   ← new
        └── U3__AddLenderTable__migration_a1b2c3d4.sql   ← new

Create your git branch following exactly this naming pattern — the pipeline parses it to determine the service and environment:

migration/{SERVICE_NAME}/{ENVIRONMENT}/{description}

# Examples:
migration/microservice-monolith/dev/add-lender-table
migration/microservice-identity/dev/add-email-index
migration/microservice-analytics/staging/add-report-table

5. Open a Pull Request

Push the branch and open a PR. The pipeline triggers automatically.

CI/CD Pipeline

PR Validation (Automatic)

Every PR on a migration/* branch runs a three-stage validation pipeline. All three must pass before the PR can be merged.

graph LR
    A[PR Opened] --> B[Extract Metadata]
    B --> C[SSH Tunnel Setup]
    C --> D[Stage 1: Quick Validation]
    D --> E[Stage 2: Dry-Run on Clone]
    E --> F[SSH Tunnel Cleanup]
    F --> G{All Passed?}
    G -- Yes --> H[PR can be merged]
    G -- No --> I[Pipeline fails — fix and push]

Stage 1 — Quick Validation (validate-migration.sh)

Checks run without modifying any database:

Check What it looks for
Duplicate operations Multiple CREATE INDEX statements for the same index name
Idempotency Rollback (U*) scripts missing IF EXISTS clauses
Schema conflicts Indexes that already exist, or target tables that don't exist
Version sequence Version numbers that skip a number

Exit codes: 0 = pass, 1 = fail (blocks merge), 2 = pass with warnings.

Stage 2 — True Dry-Run (clone-dryrun-migration.sh)

This is real SQL execution, not just syntax checking:

  1. Creates a temporary clone database: {DB_NAME}_dryrun_{TIMESTAMP}
  2. Dumps the current schema (structure only, no data) using mysqldump --no-data
  3. Loads schema into the clone, drops flyway_schema_history
  4. Sets Flyway baseline to the second-highest applied version
  5. Runs flyway migrate — only the new migration runs
  6. Drops the clone database on exit (success or failure)

If the dry-run clone doesn't exist yet (first migration), Flyway runs against an empty database.

Dev Auto-Deploy (On Merge to Main)

When a migration PR merges to main and the target environment is dev, the pipeline automatically applies the migration to the dev database. The same three-stage validation runs again before applying.

Dev only

Only dev environment migrations auto-apply on merge. Staging and production always require a manual pipeline trigger.

Staging Deployment (Manual)

Trigger the apply-migration custom pipeline in Bitbucket with:

Variable Value
SERVICE_NAME e.g. microservice-monolith
ENVIRONMENT staging

Staging promotion is automatic. If config/{SERVICE_NAME}/staging.conf does not exist, the pipeline:

  1. Copies all V*.sql and U*.sql from migrations/{SERVICE}/dev/ to migrations/{SERVICE}/staging/
  2. Generates config/{SERVICE}/staging.conf by transforming the dev config:
  3. Replaces db-int.dev.orofi.xyz → db-int.stage.orofi.xyz
  4. Uses the staging Flyway password (FLYWAY_STAGING_PASSWORD)
  5. Runs three-stage validation on staging
  6. Applies the migration

Once staging.conf exists, subsequent staging runs skip the promotion step.

Production Deployment (Manual)

Trigger the apply-migration custom pipeline with ENVIRONMENT=prod. Production uses the same pipeline as staging but with production credentials.

Production deployments

Always test on staging before applying to production. There is no automatic rollback — see Rolling Back below.

SSH Tunnel

The pipeline databases are on a private VPC. The pipeline establishes an SSH tunnel through the bastion host before any database operation, then cleans it up afterward.

Bitbucket Pipeline → SSH → Bastion (35.226.57.140) → Port-forward → DB Host:3306
                                                       localhost:13306

The SSH_PRIVATE_KEY Bitbucket repository secret contains a base64-encoded SSH private key. The pipeline decodes it, converts from OPENSSH to PEM format if needed, and tears it down after the operation.

You do not need to manage the tunnel manually — it is fully automated.

Rolling Back

Automatic Rollback (After Failed Apply)

If apply-migration.sh detects an error, the pipeline automatically attempts to run the corresponding U{VERSION} rollback script. The rollback:

  1. Executes the U*.sql file using the mysql client
  2. Verifies the rollback succeeded (checks logs for errors)
  3. Deletes the failed entry from flyway_schema_history
  4. Verifies the schema version decreased

To trigger rollback manually via the custom pipeline, run rollback-migration. Provide SERVICE_NAME, ENVIRONMENT, and optionally MIGRATION_VERSION (if omitted, the pipeline detects the latest failed migration).

Writing a New Forward Migration to Undo

The simplest approach when a migration has already been applied to multiple environments is to write a new V{N+1} migration that reverses the schema change. This is the recommended path for changes that have reached staging or production.

Repair (Without Rollback)

If a migration partially applied and you want to retry after fixing the SQL — without undoing what already ran — use the repair-migration custom pipeline. This removes the failed entry from flyway_schema_history so Flyway can re-attempt the migration.

Use repair carefully

Repair does not undo any database changes. Only use it when the failed migration left the schema in a clean state (e.g., the migration failed before touching any tables).

Ad-Hoc Single-File Migrations

For migrations that don't go through the standard repo workflow — for example, emergency fixes or operations targeting a specific database by URL — use the deploy-single-migration custom pipeline. This pipeline:

  1. Downloads a single V*.sql file from a GCS bucket (orofi-{env}-migrations)
  2. Validates, dry-runs, and applies it to the target database

Variables required: ENVIRONMENT, DATABASE_URL, FLYWAY_SCHEMAS, MIGRATION_FILE_PATH (GCS path).

Safe Migration Checklist

Before merging a migration PR:

  • [ ] Forward and rollback files exist and have matching version numbers and hashes
  • [ ] Rollback uses IF EXISTS (idempotent) — pipeline validates this, but verify manually
  • [ ] Migration has been tested on dev and the dry-run clone passed
  • [ ] Large ALTER TABLE operations use ALGORITHM=INPLACE to avoid table locks
  • [ ] Indexes use CREATE INDEX ... ALGORITHM=INPLACE to avoid table-level locks
  • [ ] Any ALTER TABLE on a table with >1M rows has been tested for lock duration
  • [ ] Migration is backwards-compatible with the currently deployed app version (so you can roll back the app without rolling back the DB schema)
  • [ ] For staging/prod: dev has been applied and verified first

Flyway Configuration Reference

The pipeline generates a flyway.conf at runtime from the service's .conf file. Key settings:

Setting Value Why
baselineOnMigrate true Allows applying migrations to a pre-existing database
baselineVersion 0 All existing schema is treated as baseline
ignoreMigrationPatterns *:missing Handles gaps from deleted or out-of-order files
outOfOrder true Allows non-sequential migration application
mixed true Allows V (versioned) and U (undo) files together
validateMigrationNaming false Disables validation that rejects U* file names
validateOnMigrate true Ensures migration files haven't been modified after application
placeholderReplacement false Prevents ${} in SQL from being treated as Flyway placeholders

Troubleshooting

Pipeline fails at "Extract Metadata"

The branch name doesn't match migration/{service}/{environment}/*. Rename your branch to the correct pattern.

Stage 1 fails: "Duplicate operation found"

Two migration files in the same directory create the same index. Rename or consolidate them. The pipeline checks for duplicate CREATE INDEX statements.

Stage 1 fails: "Undo script not idempotent"

Your U*.sql file has DROP TABLE foo without IF EXISTS. Change to DROP TABLE IF EXISTS foo.

Stage 2 (dry-run) fails

The SQL in your migration has a real error — syntax error, wrong column name, or object that doesn't exist yet. Read the dryrun log artifact in the pipeline and fix the SQL.

Migration shows as "failed" in Flyway history

Run the repair-migration pipeline to clear the failed entry, fix the migration file, and push a new commit to re-trigger the pipeline.

"Version sequence gap" warning

You added V5 but V4 doesn't exist in the directory. Either the version numbering is wrong or a file is missing. With outOfOrder=true, this is a warning, not a hard failure — but you should investigate.

See Also