Database migration patterns using Flyway with versioned SQL scripts
This skill provides patterns for managing database schema changes with Flyway.
db/migration/V{version}__{description}.sql
Examples:
V1__create_users_table.sqlV2__add_email_to_users.sqlV3__create_payments_table.sqlV1.1__add_phone_to_users.sql-- V1__create_users_table.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users(email);
-- Automatic updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
-- V2__add_phone_to_users.sql
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone_number);
-- V3__add_user_indexes.sql
CREATE INDEX CONCURRENTLY idx_users_created_at ON users(created_at DESC);
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);
-- V4__create_orders_table.sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
status VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
-- V5__set_default_status.sql
UPDATE users
SET status = 'active'
WHERE status IS NULL;
ALTER TABLE users
ALTER COLUMN status SET NOT NULL;
import org.flywaydb.core.Flyway
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
fun createDataSource(jdbcUrl: String): HikariDataSource {
val config = HikariConfig().apply {
this.jdbcUrl = jdbcUrl
username = System.getenv("DATABASE_USERNAME")
password = System.getenv("DATABASE_PASSWORD")
maximumPoolSize = 5
minimumIdle = 1
idleTimeout = 60000
maxLifetime = 600000
}
return HikariDataSource(config)
}
fun runMigrations(dataSource: HikariDataSource) {
Flyway.configure()
.dataSource(dataSource)
.locations("classpath:db/migration")
.load()
.migrate()
}
// In main()
fun main() {
val dataSource = createDataSource(env.databaseUrl)
runMigrations(dataSource)
logger.info("Database migrations completed")
}