Flyway Backend Docker Skill

This skill configures a backend project to use Flyway for SQL schema management. It modifies an existing Dockerfile (created by the backend-docker skill) to add tini as init system and Flyway migrations that run before the application starts.

Prerequisites

• backend-docker skill must be applied first - A Dockerfile must already exist
• Docker installed and running
• MySQL or compatible database will be used

When to Invoke This Skill

Invoke this skill when ANY of these conditions are true:

1. User asks for Flyway setup: "add flyway", "setup flyway migrations", "database migrations with flyway"
2. User needs SQL schema management: "manage database schema", "SQL migrations"
3. User wants tini + flyway in Docker: "run migrations before app starts"
4. Backend needs MySQL with migrations: "setup mysql with migrations"

Workflow

Step 1: Verify Dockerfile Exists

# Check for existing Dockerfile (from backend-docker skill)
ls ${BACKEND_PATH}/Dockerfile

If Dockerfile does not exist, invoke the backend-docker skill first.

Step 2: Create SQL Directory Structure

Create the ./sql directory with the AGENTS.md file:

mkdir -p ${BACKEND_PATH}/sql

Create ${BACKEND_PATH}/sql/AGENTS.md:

# SQL Migrations Directory

This folder contains Flyway SQL migration files.

## IMPORTANT: Agent Rules

**DO NOT** modify or delete any existing files in this folder.

**ONLY** add new migration files following the Flyway naming convention:
- `V{version}__{description}.sql` for versioned migrations
- `U{version}__{description}.sql` for undo migrations (optional)
- `R__{description}.sql` for repeatable migrations

### Naming Convention Examples

- `V1__create_users_table.sql`
- `V2__add_email_to_users.sql`
- `V3__create_orders_table.sql`
- `R__refresh_views.sql`

### Why This Restriction?

Flyway tracks applied migrations by checksum. Modifying an already-applied migration will cause:
- Migration failures
- Checksum mismatch errors
- Database inconsistency

If you need to change existing schema, create a NEW migration file with the next version number.

Step 3: Create Hello World Migration

Create ${BACKEND_PATH}/sql/V1__hello_world.sql:

-- Flyway Hello World Migration
-- This validates that Flyway is correctly configured

CREATE TABLE IF NOT EXISTS flyway_hello_world (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO flyway_hello_world (message) VALUES ('Hello from Flyway!');

Step 4: Create Entrypoint Script

Create ${BACKEND_PATH}/docker-entrypoint.sh:

#!/bin/sh
set -e

echo "=== Running Flyway migrations ==="

# Wait for database to be ready
until nc -z -v -w30 ${DB_HOST:-db} ${DB_PORT:-3306}
do
  echo "Waiting for database connection at ${DB_HOST:-db}:${DB_PORT:-3306}..."
  sleep 2
done

echo "Database is up - running migrations"

# Run Flyway migrations
flyway -url="jdbc:mysql://${DB_HOST:-db}:${DB_PORT:-3306}/${DB_NAME:-app}?allowPublicKeyRetrieval=true&useSSL=false" \
       -user=${DB_USER:-root} \
       -password=${DB_PASSWORD:-root} \
       -locations=filesystem:/app/sql \
       -connectRetries=5 \
       migrate

echo "=== Flyway migrations complete ==="
echo "=== Starting application ==="

# Execute the main command (passed as arguments)
exec "$@"

Step 5: Modify Existing Dockerfile

Read the existing Dockerfile and apply these modifications:

5a: Add Flyway Dependencies

For Debian/Ubuntu-based images (python:*-slim, etc.), add after existing apt-get install:

# Install tini, flyway dependencies, and netcat for health checks
RUN apt-get update && apt-get install -y --no-install-recommends \
    tini \
    curl \
    netcat-openbsd \
    default-jre-headless \
    && rm -rf /var/lib/apt/lists/*

For Alpine-based images (node:-alpine, golang:-alpine, etc.), add:

# Install tini and dependencies for flyway
RUN apk add --no-cache tini openjdk17-jre-headless curl bash netcat-openbsd

5b: Add Flyway Installation

Add after the dependency installation:

# Install Flyway
ENV FLYWAY_VERSION=10.22.0
RUN mkdir -p /tmp/flyway && \
    curl -fSL --retry 3 --retry-delay 5 \
        "https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${FLYWAY_VERSION}/flyway-commandline-${FLYWAY_VERSION}-linux-x64.tar.gz" \
        -o /tmp/flyway/flyway.tar.gz && \
    tar -xzf /tmp/flyway/flyway.tar.gz -C /opt && \
    ln -s /opt/flyway-${FLYWAY_VERSION}/flyway /usr/local/bin/flyway && \
    rm -rf /tmp/flyway

Note: For Alpine images, use linux-alpine-x64.tar.gz instead of linux-x64.tar.gz.

5c: Add SQL and Entrypoint Copies

Add before COPY . .:

# Copy SQL migrations
COPY sql/ /app/sql/

# Copy entrypoint
COPY docker-entrypoint.sh /docker-entrypoint.sh
RUN chmod +x /docker-entrypoint.sh

5d: Modify ENTRYPOINT and CMD

Replace the existing CMD with ENTRYPOINT + CMD pattern:

For Debian-based images:

ENTRYPOINT ["/usr/bin/tini", "--", "/docker-entrypoint.sh"]
CMD ["<original-cmd>"]

For Alpine-based images:

ENTRYPOINT ["/sbin/tini", "--", "/docker-entrypoint.sh"]
CMD ["<original-cmd>"]

Where <original-cmd> is the original CMD from the Dockerfile (e.g., python -m uvicorn app.main:app --host 0.0.0.0 --port 8000).

Step 6: Create docker-compose.yml for Testing

Create ${BACKEND_PATH}/docker-compose.yml:

services:
  db:
    image: mysql:8.0
    container_name: flyway-test-db
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: app
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-proot"]
      interval: 5s
      timeout: 5s
      retries: 10
      start_period: 30s

  app:
    build: .
    container_name: flyway-test-app
    environment:
      DB_HOST: db
      DB_PORT: 3306
      DB_NAME: app
      DB_USER: root
      DB_PASSWORD: root
    ports:
      - "8080:8080"
    depends_on:
      db:
        condition: service_healthy

volumes:
  mysql_data:

Step 7: Create Integration Test Script

Create ${BACKEND_PATH}/test-flyway-integration.sh:

#!/bin/bash
set -e

echo "=== Flyway Integration Test ==="

# Cleanup any existing containers
docker compose down -v 2>/dev/null || true

# Start MySQL
echo "Starting MySQL container..."
docker compose up -d db

# Wait for MySQL to be healthy
echo "Waiting for MySQL to be ready..."
timeout=90
while [ $timeout -gt 0 ]; do
    if docker compose exec -T db mysqladmin ping -h localhost -u root -proot 2>/dev/null; then
        echo "MySQL is ready!"
        break
    fi
    echo "Waiting... ($timeout seconds remaining)"
    sleep 2
    timeout=$((timeout - 2))
done

if [ $timeout -le 0 ]; then
    echo "ERROR: MySQL failed to start within timeout"
    docker compose logs db
    docker compose down -v
    exit 1
fi

# Build and start app (which runs Flyway migrations)
echo "Building and starting app container..."
docker compose up -d --build app

# Wait for app to start and run migrations
echo "Waiting for app to start..."
sleep 15

# Check if app is running
if ! docker compose ps app | grep -q "Up"; then
    echo "ERROR: App container failed to start"
    docker compose logs app
    docker compose down -v
    exit 1
fi

# Verify Flyway migration was applied
echo "Verifying Flyway migration..."
RESULT=$(docker compose exec -T db mysql -u root -proot -D app -e "SELECT message FROM flyway_hello_world LIMIT 1;" 2>/dev/null)

if echo "$RESULT" | grep -q "Hello from Flyway!"; then
    echo "SUCCESS: Flyway migration applied correctly!"
    echo "Found message: Hello from Flyway!"
else
    echo "ERROR: Flyway migration verification failed"
    echo "Query result: $RESULT"
    docker compose logs app
    docker compose down -v
    exit 1
fi

# Cleanup
echo "Cleaning up..."
docker compose down -v

echo "=== Integration Test PASSED ==="

Step 8: Validate Setup

Run the integration test:

cd ${BACKEND_PATH}
chmod +x test-flyway-integration.sh docker-entrypoint.sh
./test-flyway-integration.sh

Expected output:

• MySQL container starts successfully
• App container builds and starts
• Flyway migrations run without errors
• flyway_hello_world table contains "Hello from Flyway!"

Environment Variables

Adding New Migrations

To add a new migration:

# Create new migration file
touch sql/V2__create_users_table.sql

IMPORTANT: Never modify existing migration files. Always create new versioned migrations.

Error Handling

Flyway Migration Fails

1. Check database connectivity: Verify DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD
2. Check SQL syntax: Review migration files for errors
3. Check Flyway logs: docker compose logs app

Tini Not Found

1. Verify tini is installed in Dockerfile
2. Check path: Alpine uses /sbin/tini, Debian uses /usr/bin/tini

Database Connection Timeout

1. Increase wait time in docker-entrypoint.sh
2. Check MySQL healthcheck in docker-compose.yml

Do NOT Invoke When

• Project doesn't need SQL schema management
• Project uses a different migration tool (Liquibase, Alembic, etc.)
• Project doesn't use a relational database
• Dockerfile doesn't exist (invoke backend-docker first)