Flyway SQL migration patterns for Xeres including naming conventions, H2 database patterns, enum types, foreign keys, and best practices.
app/src/main/resources/db/migration/
V<major>_<minor>_<timestamp>__<description>.sql
Examples:
V00_0_1_202001232214__InitDb.sqlV00_0_32_202603092327__AddIndices.sqlCREATE 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);
CREATE TYPE trust_level AS ENUM ('UNKNOWN', 'NEVER', 'MARGINAL', 'FULLY', 'ULTIMATE');
ALTER TABLE profile ADD COLUMN trust trust_level NOT NULL DEFAULT 'UNKNOWN';
ALTER TABLE contact
ADD CONSTRAINT fk_contact_profile
FOREIGN KEY (profile_id) REFERENCES profile(id)
ON DELETE CASCADE;
BIGINT for IDs with GENERATED BY DEFAULT AS IDENTITYNOT NULL constraints where appropriateAdd downward migrations with V<version>__<name>.sql (no timestamp):
-- V00_0_33__AddLocations.sql
ALTER TABLE location DROP COLUMN IF EXISTS last_seen;
Flyway runs automatically on application startup with H2 database.