Skip to main content

Database Migrations

NextGenPoll uses Flyway for database schema management. All schema changes must go through versioned migration scripts — never modify the database schema directly in production.

Where Migrations Live​

backend/src/main/resources/db/migration/
├── V1__Create_Initial_Schema.sql
├── V2__Seed_Data.sql
├── V3__Add_Response_Types.sql
└── ...

Naming Convention​

V{version}__{Description}.sql
PartRule
VLiteral uppercase V
{version}Integer — must be higher than all existing versions
__Two underscores
{Description}Words separated by _, no spaces

Examples:

  • V8__Add_Image_Url_To_Questions.sql
  • V9__Create_Ai_Summaries_Table.sql
Never modify existing migration files

Once a migration has been applied to any environment, do not edit it. Flyway computes a checksum of each script — modifying an applied script will cause the next startup to fail with a checksum mismatch error. If you need to correct something, add a new migration.

How Migrations Run​

Local Development​

Flyway runs automatically on backend startup via the Spring Boot Flyway auto-configuration. On first run:

  1. Creates the flyway_schema_history table.
  2. Applies all pending migration scripts in version order.

To run migrations manually without starting the backend:

# Windows
cd backend
mvnw.cmd flyway:migrate

# Mac / Linux
cd backend
./mvnw flyway:migrate

CI (Testing)​

CI uses an H2 in-memory database with the test Spring profile. Flyway is skipped in CI (-Dflyway.skip=true) because H2 uses JPA DDL auto-generation (ddl-auto=create-drop) instead.

# backend-ci.yml
./mvnw -B verify -Dflyway.skip=true

Production (CD)​

The backend-cd.yml workflow runs Flyway against the Azure PostgreSQL database before deploying the new JAR:

./mvnw flyway:repair flyway:migrate -B \
  "-Dflyway.url=${{ secrets.AZURE_DB_URL }}" \
  "-Dflyway.user=${{ secrets.AZURE_DB_USERNAME }}" \
  "-Dflyway.password=${{ secrets.AZURE_DB_PASSWORD }}" \
  -Dflyway.skip=false \
  -Dflyway.baselineOnMigrate=true \
  -Dflyway.outOfOrder=true

flyway:repair is run first to clean up failed or checksum-mismatched migrations before applying new ones.

Adding a New Migration​

  1. Determine the next version number (check the highest V{n} in db/migration/).
  2. Create the file: 
    backend/src/main/resources/db/migration/V{n+1}__{Description}.sql
  3. Write your SQL. Use standard PostgreSQL syntax. Wrap multi-statement migrations in a transaction if applicable: 
    -- V8__Add_Image_Url_To_Questions.sql
    ALTER TABLE questions ADD COLUMN image_url VARCHAR(500);
  4. Restart the backend — Flyway will apply the new migration automatically.
  5. Update the corresponding JPA entity class and repository if needed.

Flyway Troubleshooting​

Checksum Mismatch​

Error: Migration V3 failed… checksum mismatch

Cause: An applied migration file was modified.

Fix:

./mvnw flyway:repair

This removes the failed entry from flyway_schema_history. Then restore the original file content, add a new corrective migration, and rerun.

Out-of-Order Migrations​

Error: Migration V7 is not applied

Cause: A migration with a lower version number was added after higher versions were already applied (common in feature branches).

Fix: The CD pipeline uses -Dflyway.outOfOrder=true. For local dev, add this to application-dev.yml:

spring:
  flyway:
    out-of-order: true

Baseline on Migrate​

If deploying to a database that already has tables but no flyway_schema_history (e.g., a pre-Flyway database), Flyway will fail. The CD pipeline uses -Dflyway.baselineOnMigrate=true to handle this automatically.

Schema Drift​

If spring.jpa.hibernate.ddl-auto=validate is set (used in prod profile), the application will fail to start if the JPA entity definitions do not match the actual database schema. This is intentional — it catches missing migrations early. Fix by adding the missing migration.