Agent skill

spring-boot-database-migrations

Guide for creating and managing Flyway database migrations. Use this when adding new tables, columns, or modifying database schema.

View SKILL.md on GitHub Repository
Stars 163
Forks 31

Install this agent skill to your Project

npx add-skill https://github.com/majiayu000/claude-skill-registry/tree/main/skills/data/spring-boot-database-migrations

SKILL.md

Database Migrations with Flyway

Follow these practices when making database schema changes.

Migration File Location

src/main/resources/db/migration/
├── V1__Initial_schema.sql
├── V2__Add_customers_table.sql
├── V3__Add_appointments_table.sql
├── V4__Add_employee_columns.sql
└── V[N]__description.sql

Naming Convention

REQUIRED FORMAT: V[version]__[description].sql

  • Version: Sequential integer (V1, V2, V3, ...)
  • Description: Snake_case describing the change
  • Double underscore between version and description

Examples:

V1__Initial_schema.sql
V2__Create_customers_table.sql
V3__Add_email_to_customers.sql
V4__Create_appointments_table.sql
V5__Add_foreign_keys.sql
V6__Add_guest_column_to_customers.sql

Creating New Migrations

For New Tables

sql
-- V[N]__Create_service_types_table.sql
CREATE TABLE service_types (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    duration_minutes INTEGER NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    active BOOLEAN DEFAULT TRUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add index for commonly queried columns
CREATE INDEX idx_service_types_active ON service_types(active);

For Adding Columns

sql
-- V[N]__Add_guest_column_to_customers.sql
ALTER TABLE customers 
ADD COLUMN guest BOOLEAN DEFAULT FALSE NOT NULL;

-- For nullable columns
ALTER TABLE customers 
ALTER COLUMN email DROP NOT NULL;

For Adding Foreign Keys

sql
-- V[N]__Add_foreign_keys_to_appointments.sql
ALTER TABLE appointments 
ADD CONSTRAINT fk_appointments_customer 
FOREIGN KEY (customer_id) REFERENCES customers(id);

ALTER TABLE appointments 
ADD CONSTRAINT fk_appointments_employee 
FOREIGN KEY (employee_id) REFERENCES employees(id);

PostgreSQL vs H2 Compatibility

For development with H2 and production with PostgreSQL, use compatible syntax:

sql
-- Use BIGSERIAL for PostgreSQL auto-increment
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,  -- PostgreSQL
    -- id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- H2 only
    name VARCHAR(255) NOT NULL
);

-- For H2 compatibility, use application-h2.yml with ddl-auto

Migration Rules

  1. NEVER modify applied migrations - Create new migration files instead
  2. NEVER delete migration files - Keep full history
  3. Test migrations locally before committing
  4. Use transactions for complex migrations (PostgreSQL)
  5. Add rollback comments for complex changes

Handling Migration Errors

Checksum Mismatch Error

FlywayValidateException: Migration checksum mismatch for migration version X

Solution (Development Only):

powershell
# Complete database reset
docker-compose down -v
docker system prune -f
.\gradlew.bat bootJar
docker-compose up --build

Failed Migration Recovery

FlywayValidateException: Detected failed migration to version X

Solution:

  1. Fix the migration SQL syntax
  2. Reset the database (development)
  3. Or run Flyway repair (with caution)

Test Fixtures Updates

When adding new migrations, update test fixtures:

  1. Create/update database defaults in src/testFixtures/java/com/salonhub/api/[domain]/
  2. Update ServerSetupExtension if schema changes affect test data
  3. Ensure integration tests use the new schema

Example Database Default:

java
public class ServiceTypeDatabaseDefault {
    public static final Long HAIRCUT_ID = 1L;
    public static final String HAIRCUT_NAME = "Haircut";
    
    public static final String INSERT_HAIRCUT = 
        "INSERT INTO service_types (id, name, duration_minutes, price, active) VALUES " +
        "(1, 'Haircut', 30, 25.00, true)";
}

Checklist for Schema Changes

  • Create new migration file with correct naming
  • Test migration locally with H2 profile
  • Test migration with Docker PostgreSQL
  • Update JPA entities to match schema
  • Update test fixtures/database defaults
  • Run full test suite: .\gradlew.bat check
  • Verify integration tests pass

Recommended Agent Skills

Expand your agent's capabilities with these related and highly-rated skills.

majiayu000/claude-skill-registry

agent-ops-spec

Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-state

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-spec

Manage specification documents in .agent/specs/. Use when user provides requirements, acceptance criteria, or feature descriptions that need to be tracked and validated against implementation.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-testing

Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-testing

Test strategy, execution, and coverage analysis. Use when designing tests, running test suites, or analyzing test results beyond baseline checks.

163 31
Explore
majiayu000/claude-skill-registry

agent-ops-state

Maintain .agent state files. Use at session start, after meaningful steps, and before concluding: read/update constitution/memory/focus/issues/baseline consistently.

163 31
Explore

Didn't find tool you were looking for?

Be as detailed as possible for better results