Migration

Database Migration Skill

Create a Flyway migration for: $ARGUMENTS

Process

1. Get Next Version Number

   ls src/main/resources/db/migration/ | grep -E '^V[0-9]+' | sort -V | tail -1
   

   Use the NEXT sequential number. NEVER guess.

2. Verify Table/Column Names

   • Check JPA Entity: @Table(name = "...") and @Column(name = "...")
   • If no annotation: JPA uses class/field name (Church → church)
   • NEVER assume plural/singular - verify!

   # Find table name
   grep -r "CREATE TABLE.*tablename" src/main/resources/db/migration/ | head -5
   # Check entity annotation
   grep -A2 "@Table" src/main/java/com/reuben/pastcare_spring/models/EntityName.java
   

3. Write Migration (MySQL Syntax ONLY)

   Create Table:

   CREATE TABLE IF NOT EXISTS table_name (
       id BIGINT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       is_active TINYINT(1) DEFAULT 1,
       amount DECIMAL(19,2),
       created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
       church_id BIGINT NOT NULL,
       CONSTRAINT fk_table_church FOREIGN KEY (church_id) REFERENCES church(id)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
   

   Alter Table:

   ALTER TABLE table_name ADD COLUMN new_column VARCHAR(255);
   ALTER TABLE table_name MODIFY COLUMN existing_column VARCHAR(500);
   ALTER TABLE table_name DROP COLUMN old_column;
   

   Create Index:

   CREATE INDEX idx_table_column ON table_name(column_name);
   -- NEVER use WHERE clause (MySQL doesn't support partial indexes)
   

4. Verify Migration

   ./mvnw compile  # Verify Flyway can parse
   ./mvnw spring-boot:run -Dspring-boot.run.profiles=dev  # Apply migration
   # Look for: "Successfully applied X migration(s)"
   

MySQL Syntax Requirements

Common Errors to Avoid

• Wrong version number (always check last migration)
• PostgreSQL syntax (SERIAL, BOOLEAN, WHERE in CREATE INDEX)
• Missing IF NOT EXISTS for CREATE TABLE
• Missing ENGINE=InnoDB for tables with foreign keys
• Missing DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
• Referencing non-existent columns
• Changing table names (breaks production!)

Known Table Names

FORBIDDEN Operations

• DROP DATABASE
• TRUNCATE TABLE
• Clearing all data
• Deleting migration files
• Any operation causing data loss