Automate Database Migrations with Flyway

1. 1
   Configure Flyway Connection

   The easiest way to start is with the official Docker image. You configure Flyway by passing connection details as command-line arguments. We will use a temporary in-memory H2 database for this example.
2. 2
   Create Your First Migration Script

   Create a directory named 'sql'. Inside it, create a file named `V1__Create_users_table.sql`. Flyway discovers and runs migrations based on this `V<VERSION>__Description.sql` naming pattern.
3. 3
   Run the Migration

   Execute the `migrate` command. Flyway connects to the database, creates a `flyway_schema_history` table to track state, and applies any pending migration scripts in version order.
4. 4
   Add a Second Migration

   To evolve the schema, add a new file like `V2__Add_email_to_users.sql`. When you run `migrate` again, Flyway detects the new script and applies only that change.
5. 5
   Check Migration Status

   Run the `info` command at any time to see the status of all migrations. It shows which migrations have been applied, when they were applied, and if they were successful.

#!/bin/bash

# This script demonstrates a full Flyway migration cycle using Docker.

# Ensure you are in a clean directory
rm -rf ./flyway_project
mkdir -p ./flyway_project/sql
cd ./flyway_project

echo "--- Step 1: Creating migration scripts ---"

# First migration: Create the initial 'users' table.
# The naming convention V<VERSION>__<DESCRIPTION>.sql is crucial.
echo "CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);" > ./sql/V1__Create_users_table.sql
echo "Created V1__Create_users_table.sql"

# Second migration: Add an 'email' column to the 'users' table.
echo "ALTER TABLE users ADD email VARCHAR(100);" > ./sql/V2__Add_email_to_users.sql
echo "Created V2__Add_email_to_users.sql"

echo "\n--- Step 2: Running Flyway migrations ---"
# We use an H2 in-memory database for this example, so no external DB is needed.
# The -v flag mounts our local ./sql directory into the container's /flyway/sql directory.
docker run --rm \
    -v $(pwd)/sql:/flyway/sql \
    flyway/flyway:latest \
    -url=jdbc:h2:mem:flyway_db \
    -user=sa \
    -password= \
    migrate

echo "\n--- Step 3: Checking Flyway migration status ---"
docker run --rm \
    -v $(pwd)/sql:/flyway/sql \
    flyway/flyway:latest \
    -url=jdbc:h2:mem:flyway_db \
    -user=sa \
    -password= \
    info

# Go back to the parent directory
cd ..
echo "\nScript finished. To clean up, run: rm -rf ./flyway_project"