Skip to main content

​
MySQL Migrations

Strategi migration database untuk MStore Backend menggunakan Atlas (HCL-based) dan Flyway (SQL-based).

​
🏗️ Migration Tools

​
Primary: Atlas (HCL)

  • Format: HCL (HashiCorp Configuration Language)
  • Version Control: Git-friendly, schema as code
  • Location: third_party/migrations/atlas/schema/
  • Benefit: Declarative, easy to review, supports dry-run

​
Fallback: Flyway (SQL)

  • Format: SQL scripts
  • Sequential: Numbered files (V001, V002, etc.)
  • Location: third_party/migrations/flyway/
  • Benefit: Simple, direct SQL control

​
📋 Migration Naming Convention

{sequence}_{domain}_{action}.{ext}
Examples: 
01_schema.my.hcl                          # Base schema definition
02_lookup.my.hcl                          # Lookup/enum tables
03_registration_preseed_master.my.hcl     # Merchant & branch setup
04_master_data_import.my.hcl              # Master data tables
05_transactional.my.hcl                   # Transaction tables
06_approval_flow.my.hcl                   # Approval workflow
07_inventory_flow.my.hcl                  # Inventory management
08_promotions.my.hcl                      # Promotions & discounts
09_offline_first.my.hcl                   # Offline sync tables
97_accounting_flow.my.hcl                 # Accounting & GL
98_ai_integration.my.hcl                  # AI features
99_archive_data.my.hcl                    # Archive & history

​
🔄 Migration Lifecycle

​
1. Create Migration File

Atlas HCL Example: 
// 10_new_feature.my.hcl
table "feature_configs" {
  schema = schema."mstore-monolith"
  
  column "id" {
    null           = false
    type           = bigint
    unsigned       = true
    auto_increment = true
  }
  
  column "merchant_id" {
    null     = false
    type     = bigint
    unsigned = true
  }
  
  column "config_key" {
    null = false
    type = varchar(128)
  }
  
  column "config_value" {
    null = false
    type = text
  }
  
  column "created_at" {
    null    = true
    type    = timestamp
    default = sql("CURRENT_TIMESTAMP")
  }
  
  primary_key {
    columns = [column.id]
  }
  
  index "merchant_config_key" {
    unique  = true
    columns = [column.merchant_id, column.config_key]
  }
  
  foreign_key "merchant_fk" {
    columns     = [column.merchant_id]
    ref_columns = [table.merchants.column.id]
    on_delete   = CASCADE
  }
}

​
2. Apply Migration

Using Atlas: 
# Dry run (preview changes)
atlas migrate diff --env local

# Apply migration
atlas migrate apply --env local

# Apply to specific environment
atlas migrate apply --env staging
Using Flyway: 
flyway migrate

​
3. Verify Migration

-- Check table structure
DESCRIBE feature_configs;

-- Check indexes
SHOW INDEXES FROM feature_configs;

-- Check constraints
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'feature_configs';

​
⬇️ Rollback Strategy

​
Option A: Migration Rollback (Preferred)

# Revert last migration
atlas migrate down --env local

# Revert to specific version
atlas migrate down --env local --target-version {version}

​
Option B: Manual Rollback (Emergency)

  1. Stop application
  2. Restore from backup: 
    mysql -u root -p mstore-monolith < backup_2025_12_17.sql
  3. Re-apply migrations to stable version
  4. Verify data integrity
  5. Restart application

​
Option C: Point-in-Time Recovery

-- Restore to specific timestamp
RESTORE DATABASE mstore-monolith 
  FROM BACKUP 'backup_2025_12_17' 
  TO POINT IN TIME '2025-12-17 14:30:00';

​
✅ Pre-Migration Checklist

  • Backup database
  • Test migration on staging
  • Verify rollback procedure
  • Notify team
  • Schedule maintenance window
  • Review migration script for syntax errors
  • Check for data type compatibility

​
✅ Post-Migration Checklist

  • Verify schema changes: DESCRIBE table_name;
  • Check index creation: SHOW INDEXES FROM table_name;
  • Validate constraints: SHOW CREATE TABLE table_name;
  • Run smoke tests
  • Monitor performance
  • Check application logs
  • Verify data integrity

​
🚀 Best Practices

​
DO ✅

  • Use version control for all migrations
  • Test migrations on staging first
  • Keep migrations small & focused
  • Document complex migrations
  • Backup before major migrations
  • Use transactions for data migrations
  • Index foreign keys
  • Use soft delete for audit trails

​
DON’T ❌

  • Modify production data directly
  • Skip testing migrations
  • Use large batch updates without pagination
  • Drop tables without backup
  • Ignore migration errors
  • Mix schema & data migrations
  • Use reserved SQL keywords as column names

Data Seeding

Seed data strategy

MySQL Schema

Complete schema reference

Backup & Restore

Backup procedures

ERD Diagram

Entity relationship diagram