Spring Boot + PostgreSQL + Flyway: Database Migration Workflow on DomainIndia VPS

# Spring Boot + PostgreSQL + Flyway: Database Migration Workflow on DomainIndia VPS ## Why you need migrations Without a migration tool, schema changes are manual. Symptoms: - "I forgot to run that ALTER TABLE on staging" - Dev DB has columns production doesn't (or vice versa) - Rolling back a release doesn't roll back the DB - New team member's local DB is half-set-up With Flyway: - Every schema change is an SQL file committed to git - `flyway migrate` replays missing changes in order - `flyway info` shows which migrations ran on each DB - DB schema versioned alongside app code ## Flyway vs Liquibase Pick **Flyway** for most Spring Boot apps. Plain SQL is readable, reviewable, standard. ## Step 1 — Add to Spring Boot `pom.xml`: ```xml org.flywaydb flyway-core org.flywaydb flyway-database-postgresql ``` Spring Boot auto-configures Flyway — it runs migrations on startup. `application.yml`: ```yaml spring: datasource: url: jdbc:postgresql://localhost/myapp username: spring password: ${DB_PASSWORD} flyway: enabled: true locations: classpath:db/migration baseline-on-migrate: true validate-on-migrate: true ``` ## Step 2 — Migration file structure Create `src/main/resources/db/migration/`: ``` V1__create_users.sql V2__create_orders.sql V3__add_user_phone.sql V4__seed_default_roles.sql V5__index_orders_created_at.sql ``` Naming: - `V` (uppercase) prefix - Version number (integer or dotted: `V1`, `V1.1`, `V2`) - `__` (two underscores) - Description (letters, numbers, underscores) - `.sql` extension **Never edit a migration after it's run.** Always add a new migration. Flyway tracks applied migrations + their checksums. ## Step 3 — Example migrations `V1__create_users.sql`: ```sql CREATE TABLE users ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), email text UNIQUE NOT NULL, password_hash text NOT NULL, name text NOT NULL, created_at timestamptz DEFAULT now(), updated_at timestamptz DEFAULT now() ); CREATE INDEX idx_users_email ON users(email); ``` `V3__add_user_phone.sql`: ```sql ALTER TABLE users ADD COLUMN phone text; CREATE INDEX idx_users_phone ON users(phone) WHERE phone IS NOT NULL; ``` ## Step 4 — Running migrations **In dev:** Spring Boot runs them on app startup automatically. **In production:** disable auto-run in app (`spring.flyway.enabled: false`) and run as a separate step in your deploy: ```bash ./mvnw flyway:migrate -Dflyway.url=$DATABASE_URL -Dflyway.user=$DB_USER -Dflyway.password=$DB_PASSWORD ``` Or via the Flyway CLI: ```bash flyway -url=$DATABASE_URL -user=$DB_USER -password=$DB_PASSWORD migrate ``` **Why separate?** Web servers restart unexpectedly. Running migrations on startup means a slow migration blocks health checks → load balancer kills the app → migration restarts partway through → broken DB. ## Step 5 — systemd migration unit Create a one-shot systemd service that runs migrations once, before the app starts: `/etc/systemd/system/spring-app-migrate.service`: ```ini [Unit] Description=Flyway migrations Before=spring-app.service Requires=postgresql.service [Service] Type=oneshot User=spring WorkingDirectory=/opt/spring-app ExecStart=/usr/bin/java -jar /opt/spring-app/app.jar --spring.flyway.enabled=true --spring.flyway.repair-on-migrate=false EnvironmentFile=/opt/spring-app/.env ``` In `spring-app.service`: ```ini [Unit] After=network.target postgresql.service spring-app-migrate.service Requires=spring-app-migrate.service ``` Now restart: `sudo systemctl restart spring-app` — migrations run first, then the app starts. ## Step 6 — Rollback strategy Flyway's `undo` feature is Teams-tier only. For OSS, roll back via **forward migrations**: ```sql -- V10__drop_user_phone.sql (forward-only rollback of V3) ALTER TABLE users DROP COLUMN phone; ``` Better pattern: design migrations to be **backward-compatible**: - Adding a column — always compatible. Old code ignores the new column. - Removing a column — do it in 2 releases: 1. Release N: stop writing to the column in code 2. Release N+1 (after verifying): drop the column via migration - Renaming a column — do it in 3 steps: 1. Add new column, dual-write 2. Backfill old data to new column 3. Remove old column This way, rolling back a bad code deploy doesn't require a DB rollback. ## Step 7 — Seed data For development fixtures: `V4__seed_default_roles.sql`: ```sql INSERT INTO roles (name, permissions) VALUES ('admin', '{"*": true}'), ('user', '{"read": true}'), ('guest', '{}') ON CONFLICT (name) DO NOTHING; ``` Use `ON CONFLICT` so re-running doesn't fail. Idempotent. For test-only data, prefer Spring's `@Sql` test annotation or `data.sql`, not Flyway — test data shouldn't land in prod. ## Step 8 — Repeatable migrations For views/functions/triggers that you edit frequently, use `R__` prefix: `R__update_search_view.sql`: ```sql CREATE OR REPLACE VIEW search_items AS SELECT id, name, description, created_at FROM products UNION ALL SELECT id, title, body, published_at FROM articles; ``` Flyway detects changes via checksum and re-runs. Useful for views and stored procedures. ## Step 9 — CI/CD integration GitHub Actions: ```yaml - name: Validate migrations run: ./mvnw flyway:validate -Dflyway.url=${{ secrets.STAGING_DB_URL }} - name: Dry run run: ./mvnw flyway:info -Dflyway.url=${{ secrets.STAGING_DB_URL }} - name: Run on staging if: github.ref == 'refs/heads/develop' run: ./mvnw flyway:migrate -Dflyway.url=${{ secrets.STAGING_DB_URL }} - name: Run on production (manual approval) if: github.ref == 'refs/heads/main' environment: production # requires approver in GH run: ./mvnw flyway:migrate -Dflyway.url=${{ secrets.PROD_DB_URL }} ``` ## Monitoring migrations Check state: ```bash ./mvnw flyway:info -Dflyway.url=$DATABASE_URL ``` Output: ``` +------------+---------+---------------+---------+---------------------+----------+ | Category | Version | Description | Type | Installed On | State | +------------+---------+---------------+---------+---------------------+----------+ | Versioned | 1 | create users | SQL | 2026-01-15 10:00:00 | Success | | Versioned | 2 | create orders | SQL | 2026-01-16 11:00:00 | Success | | Versioned | 3 | add user phone| SQL | 2026-02-01 14:00:00 | Success | | Versioned | 4 | seed roles | SQL | | Pending | +------------+---------+---------------+---------+---------------------+----------+ ``` ## Common pitfalls ## FAQ