Skip to content

ADR-0009: Database Migration Strategy

Status

PROPOSED

Date: 2025-07-06

Changelog

Date Revision Description Author
2025-07-06 1.0 Initial version Project Team

Context

As we develop a system with multiple services, each managing its own data persistence, we need a clear strategy for handling database schema migrations. This is critical for several reasons:

  • Each microservice will maintain its own independent database as part of our service boundary strategy
  • We need to ensure data integrity during schema changes
  • Database changes need to be versioned and synchronized with application code deployments
  • We need reliable mechanisms for disaster recovery and environment reproduction
  • Our system operates on an eventual consistency model, which impacts how we plan and execute migrations
  • As the product matures, our migration strategy will need to evolve

Decision

We will implement a database migration strategy with the following characteristics:

For Release 0.1 (Current Phase)

  1. Service-Specific Migrations: Each service will manage its own database migrations independently, aligned with our microservice architecture.

  2. Forward-Only Migrations: We will initially implement forward-only migrations without explicit downgrade support.

  3. Migration Scripts: Each service will maintain a sequential set of migration scripts in a standardized migrations folder within its codebase. Scripts will include metadata comments with description, author, and date information, and will be executed in order based on their version number.

  4. Backup-Based Recovery: Instead of implementing downgrade migrations, we will rely on comprehensive backup and restore mechanisms to recover from failed migrations.

  5. Centralized Backup System: We will implement a centralized backup system that captures database states before migrations are applied.

  6. Migration Execution Process:

    • Automated pre-migration backups
    • Migration execution in a transactional manner when possible
    • Validation steps to confirm migration success
    • Fallback procedures using backups if issues occur

  7. Migration Tooling: We will use SQL scripts located in a migrations folder for each service. Migration scripts will follow the naming convention vNNN-migration-script.sql where NNN is the sequential number of the migration script (e.g., v001, v002).

  8. Idempotent Migrations: Migrations should be designed to be idempotent when possible to support retry scenarios.

Planned Evolution for Release 1.0

  1. Bidirectional Migrations: We will rework our migration tooling to support explicit downgrade paths for each migration.

  2. Advanced Testing: We will implement comprehensive testing of both upgrade and downgrade paths.

  3. Migration Coordination: Implement tools for coordinating migrations across services when needed.

Consequences

Positive

  • Simpler initial implementation without the complexity of downgrade scripts
  • Clear separation of database concerns aligned with service boundaries
  • Reliable recovery mechanism through systematic backups
  • Clear path for evolution as the product matures
  • Easier to implement in the early stages of the project

Negative

  • Recovery from failed migrations will require full database restoration, which may increase downtime
  • Limited flexibility for partial rollbacks
  • More operational overhead in managing and coordinating backups
  • Migration testing will initially be less comprehensive without downgrade paths

Neutral

  • Teams will need to be disciplined about creating backups before migrations
  • Will require careful coordination with the deployment process
  • Eventual consistency model requires careful planning of migration deployment timing

Alternatives Considered

  1. Full Bidirectional Migrations from the Start

    • Description: Implement both upgrade and downgrade scripts from initial release
    • Pros: More flexible recovery options, granular rollback capability
    • Cons: Higher development overhead, more complex testing requirements
    • Why not chosen: Excessive complexity for the current project phase; better to focus on core functionality

  2. Schema-as-Code Approach

    • Description: Define database schema in code and generate migrations automatically
    • Pros: Reduced manual effort, potentially fewer migration errors
    • Cons: Less control over migration steps, potential issues with complex migrations
    • Why not chosen: Insufficient control for our specific use cases, especially with eventual consistency

  3. Centralized Database Management

    • Description: Manage all database migrations through a central service
    • Pros: Easier coordination, consistent tooling
    • Cons: Creates dependencies between services, potential bottleneck
    • Why not chosen: Conflicts with our microservice architecture principles

References