Skip to main content
Version: v0.1.0

Database Migrations

Guide to managing database schema changes using Flyway.

Overview​

Ciyex EHR uses Flyway for database version control. All schema changes (DDL) and data migrations (DML) are versioned and applied automatically on application startup.

Migration File Structure​

Migrations are located in src/main/resources/db/migration. Naming convention: V{Version}__{Description}.sql

Examples:

  • V1__init_schema.sql
  • V1.1__add_patient_email.sql
  • V2.0__create_audit_tables.sql

Creating a Migration​

  1. Create the file: src/main/resources/db/migration/V1.2__add_phone_to_users.sql
  2. Write SQL: 
    ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
    COMMENT ON COLUMN users.phone_number IS 'User mobile contact';
  3. Run Application: Spring Boot will detect the new file and apply it on startup.

Managing Conflicts​

If two developers create V1.2 locally:

  • Flyway will fail with a checksum mismatch or version conflict.
  • Solution: Rename your migration to the next available version (e.g., V1.3) before merging.

Rollbacks​

Flyway Community Edition does not support automatic "undo" migrations. Strategy: Create a new forward migration that reverses the change.

Example V1.3__undo_add_phone.sql:

ALTER TABLE users DROP COLUMN phone_number;

Repairing History​

If a migration fails (e.g., due to syntax error) and leaves the schema history in a failed state:

  1. Fix the SQL file.
  2. Run Flyway repair: 
    ./gradlew flywayRepair
    Or manually update the flyway_schema_history table (caution advised).

Best Practices​

  • Never modify an existing migration after it has been applied to any environment. Always create a new version.
  • Test migrations locally against a clean database.
  • Use transactional DDL: PostgreSQL supports DDL in transactions. Flyway wraps migrations in transactions by default, so if part fails, it all rolls back.