flyway-migrations

name	flyway-migrations
description	Flyway SQL migration patterns for Xeres including naming conventions, H2 database patterns, enum types, foreign keys, and best practices.

Flyway Migration Patterns for Xeres

Migration Location

app/src/main/resources/db/migration/

Naming Convention

V<major>_<minor>_<timestamp>__<description>.sql

Examples:

• V00_0_1_202001232214__InitDb.sql
• V00_0_32_202603092327__AddIndices.sql

Table Creation Pattern

CREATE TABLE profile (
    id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    pgp_id      BIGINT,
    created     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT profile_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_profile_name ON profile(name);

Enum Types

CREATE TYPE trust_level AS ENUM ('UNKNOWN', 'NEVER', 'MARGINAL', 'FULLY', 'ULTIMATE');

ALTER TABLE profile ADD COLUMN trust trust_level NOT NULL DEFAULT 'UNKNOWN';

Foreign Keys

ALTER TABLE contact
    ADD CONSTRAINT fk_contact_profile
    FOREIGN KEY (profile_id) REFERENCES profile(id)
    ON DELETE CASCADE;

Best Practices

1. Separate index creation from table creation
2. Name all constraints explicitly for easier debugging
3. Use BIGINT for IDs with GENERATED BY DEFAULT AS IDENTITY
4. Include NOT NULL constraints where appropriate
5. Default values for optional columns
6. One concern per migration when possible
7. Timestamp precision must always be of TIMESTAMP(9)

Rolling Back

Add downward migrations with V<version>__<name>.sql (no timestamp):

-- V00_0_33__AddLocations.sql
ALTER TABLE location DROP COLUMN IF EXISTS last_seen;

Testing Migrations

Flyway runs automatically on application startup with H2 database.