# Flyway - Database Migration Testing

Comprehensive guide to database migration testing with Flyway for version-controlled schema management.

## Overview

**Container**: `flyway/flyway:latest`
**Category**: Database Testing
**Port**: N/A (CLI tool)

Flyway is a database migration tool that enables version control for database schemas, tracking and applying incremental changes across environments.

## Quick Start

```bash
# Check Flyway version
docker exec flyway flyway --version

# Show migration info
docker exec flyway flyway info

# Run migrations
docker exec flyway flyway migrate

# Validate migrations
docker exec flyway flyway validate
```

## Configuration

```properties
# flyway/conf/flyway.conf
flyway.url=jdbc:postgresql://db:5432/myapp
flyway.user=postgres
flyway.password=postgres
flyway.schemas=public
flyway.locations=filesystem:/flyway/sql
flyway.validateOnMigrate=true
flyway.baselineOnMigrate=false
flyway.cleanDisabled=true
```

### Environment Variables

```bash
# Can also configure via environment
export FLYWAY_URL=jdbc:postgresql://db:5432/myapp
export FLYWAY_USER=postgres
export FLYWAY_PASSWORD=postgres
export FLYWAY_SCHEMAS=public
export FLYWAY_LOCATIONS=filesystem:/flyway/sql
```

## Migration File Naming

| Prefix | Type | Example | Description |
|--------|------|---------|-------------|
| V | Versioned | V1__Create_users.sql | Applied once, in order |
| U | Undo | U1__Create_users.sql | Reverses versioned migration |
| R | Repeatable | R__Create_views.sql | Re-run when checksum changes |

### Naming Convention

```
V{version}__{description}.sql
```

- Version: Numbers separated by dots/underscores (1, 1.1, 1_1_1)
- Double underscore separates version from description
- Description uses underscores for spaces

## Migration Examples

### Versioned Migration

```sql
-- V1__Create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
```

```sql
-- V2__Add_user_roles.sql
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users
ADD COLUMN role_id INTEGER REFERENCES roles(id);

INSERT INTO roles (name, description) VALUES
    ('admin', 'System administrator'),
    ('user', 'Regular user'),
    ('guest', 'Guest user');
```

```sql
-- V3__Add_user_profile.sql
ALTER TABLE users
ADD COLUMN first_name VARCHAR(100),
ADD COLUMN last_name VARCHAR(100),
ADD COLUMN avatar_url TEXT,
ADD COLUMN bio TEXT;
```

### Repeatable Migration

```sql
-- R__Create_reporting_views.sql
-- This runs every time the checksum changes

DROP VIEW IF EXISTS user_statistics;
CREATE VIEW user_statistics AS
SELECT
    date_trunc('day', created_at) as date,
    COUNT(*) as new_users,
    COUNT(CASE WHEN role_id = 1 THEN 1 END) as new_admins
FROM users
GROUP BY 1
ORDER BY 1 DESC;

DROP VIEW IF EXISTS active_users;
CREATE VIEW active_users AS
SELECT u.*, r.name as role_name
FROM users u
LEFT JOIN roles r ON u.role_id = r.id
WHERE u.created_at > NOW() - INTERVAL '30 days';
```

### Undo Migration

```sql
-- U2__Add_user_roles.sql
ALTER TABLE users DROP COLUMN role_id;
DROP TABLE IF EXISTS roles;
```

## CLI Commands

```bash
# Show current migration status
docker exec flyway flyway info

# Apply pending migrations
docker exec flyway flyway migrate

# Validate applied migrations
docker exec flyway flyway validate

# Repair migration checksums
docker exec flyway flyway repair

# Undo last migration (requires Flyway Teams)
docker exec flyway flyway undo

# Clean database (DANGEROUS - drops all objects)
docker exec flyway flyway clean

# Create baseline from existing database
docker exec flyway flyway baseline -baselineVersion=1

# Dry run (see what would execute)
docker exec flyway flyway migrate -dryRun
```

### Command Options

```bash
# Specify target version
docker exec flyway flyway migrate -target=3

# Skip validation
docker exec flyway flyway migrate -validateOnMigrate=false

# Out of order migrations
docker exec flyway flyway migrate -outOfOrder=true

# Show SQL without executing
docker exec flyway flyway migrate -dryRun

# Use specific config
docker exec flyway flyway -configFiles=/conf/flyway-prod.conf migrate
```

## Environment-Specific Configuration

```properties
# flyway-dev.conf
flyway.url=jdbc:postgresql://localhost:5432/myapp_dev
flyway.user=dev_user
flyway.password=dev_pass
flyway.placeholders.environment=development
flyway.cleanDisabled=false

# flyway-prod.conf
flyway.url=jdbc:postgresql://prod-db:5432/myapp
flyway.user=prod_user
flyway.password=${PROD_DB_PASSWORD}
flyway.placeholders.environment=production
flyway.cleanDisabled=true
flyway.validateOnMigrate=true
```

### Placeholder Usage

```sql
-- V4__Add_test_data.sql
-- Only inserts data in development environment
INSERT INTO users (email, password_hash, role_id)
SELECT 'test@example.com', 'hash', 2
WHERE '${environment}' = 'development';
```

## Migration Testing Patterns

### Pattern 1: Fresh Database Test

```bash
#!/bin/bash
# test-fresh-db.sh

# Start fresh database
docker-compose up -d db
sleep 10

# Apply all migrations from scratch
docker exec flyway flyway clean  # Only in test!
docker exec flyway flyway migrate

# Validate migrations
docker exec flyway flyway validate
if [ $? -ne 0 ]; then
    echo "Migration validation failed!"
    exit 1
fi

# Run application tests
npm run test:integration

# Check migration info
docker exec flyway flyway info
```

### Pattern 2: Incremental Migration Test

```bash
#!/bin/bash
# test-incremental.sh

# Baseline from production snapshot
docker exec flyway flyway baseline -baselineVersion=10

# Apply new migrations
docker exec flyway flyway migrate
if [ $? -ne 0 ]; then
    echo "Migration failed!"
    exit 1
fi

# Test new migration
npm run test:migration

# Validate final state
docker exec flyway flyway validate
```

### Pattern 3: Rollback Test

```bash
#!/bin/bash
# test-rollback.sh

# Apply migrations
docker exec flyway flyway migrate

# Test current state
npm run test:integration

# Test undo (Flyway Teams)
docker exec flyway flyway undo

# Verify rollback
docker exec flyway flyway validate
```

## CI/CD Integration

### GitHub Actions

```yaml
name: Database Migration Test
on: [push, pull_request]

jobs:
  migration-test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v3

      - name: Run Flyway Info
        run: |
          docker run --rm --network host \
            -v $(pwd)/flyway/sql:/flyway/sql \
            -v $(pwd)/flyway/conf:/flyway/conf \
            flyway/flyway info

      - name: Run Flyway Migrations
        run: |
          docker run --rm --network host \
            -v $(pwd)/flyway/sql:/flyway/sql \
            -v $(pwd)/flyway/conf:/flyway/conf \
            flyway/flyway migrate

      - name: Validate Migrations
        run: |
          docker run --rm --network host \
            -v $(pwd)/flyway/sql:/flyway/sql \
            -v $(pwd)/flyway/conf:/flyway/conf \
            flyway/flyway validate

      - name: Run Integration Tests
        run: npm run test:integration
```

### Pipeline Script

```bash
#!/bin/bash
# ci-migration-test.sh

set -e

echo "=== Database Migration CI Test ==="

# 1. Start fresh database
docker-compose -f docker-compose.test.yml up -d db
echo "Waiting for database..."
sleep 15

# 2. Show current state
echo "=== Migration Info (Before) ==="
docker exec flyway flyway info

# 3. Apply migrations
echo "=== Applying Migrations ==="
docker exec flyway flyway migrate

# 4. Validate
echo "=== Validating Migrations ==="
docker exec flyway flyway validate

# 5. Show final state
echo "=== Migration Info (After) ==="
docker exec flyway flyway info

# 6. Run schema tests
echo "=== Running Schema Tests ==="
npm run test:schema

echo "=== Migration Test Complete ==="
```

## Schema Validation

### Compare Schemas

```bash
# Compare schemas between environments
pg_dump -s dev_db > dev_schema.sql
pg_dump -s prod_db > prod_schema.sql
diff dev_schema.sql prod_schema.sql
```

### Schema Test

```javascript
// schema.test.js
const { Client } = require('pg');

describe('Database Schema', () => {
  let client;

  beforeAll(async () => {
    client = new Client({ connectionString: process.env.DATABASE_URL });
    await client.connect();
  });

  afterAll(async () => {
    await client.end();
  });

  test('users table has required columns', async () => {
    const result = await client.query(`
      SELECT column_name, data_type
      FROM information_schema.columns
      WHERE table_name = 'users'
    `);

    const columns = result.rows.map(r => r.column_name);
    expect(columns).toContain('id');
    expect(columns).toContain('email');
    expect(columns).toContain('password_hash');
    expect(columns).toContain('created_at');
  });

  test('users table has email index', async () => {
    const result = await client.query(`
      SELECT indexname
      FROM pg_indexes
      WHERE tablename = 'users' AND indexname = 'idx_users_email'
    `);

    expect(result.rows.length).toBe(1);
  });

  test('foreign key constraints exist', async () => {
    const result = await client.query(`
      SELECT constraint_name
      FROM information_schema.table_constraints
      WHERE table_name = 'users' AND constraint_type = 'FOREIGN KEY'
    `);

    expect(result.rows.length).toBeGreaterThan(0);
  });
});
```

## Best Practices

### DO

1. **Use descriptive migration names** - V1__Create_users_table.sql
2. **Keep migrations small and focused** - One logical change per migration
3. **Include rollback logic** - Create undo migrations when possible
4. **Test against production-like data** - Use realistic test datasets
5. **Version control all migrations** - Never modify applied migrations
6. **Validate in CI/CD** - Run migration tests before deployment
7. **Use transactions** - Wrap DDL in transactions where supported

### DON'T

1. **Delete or rename applied migrations** - Breaks checksums
2. **Use flyway clean in production** - Drops all data!
3. **Skip versions** - Keep sequential numbering
4. **Make non-idempotent repeatable migrations** - Check before creating
5. **Include application data in schema migrations** - Separate concerns
6. **Modify applied migrations** - Create new migrations instead

## Database-Specific Notes

### PostgreSQL

```sql
-- V1__Create_schema.sql
CREATE SCHEMA IF NOT EXISTS app;
SET search_path TO app;

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### MySQL

```sql
-- V1__Create_users.sql
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

### SQL Server

```sql
-- V1__Create_users.sql
CREATE TABLE users (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    email NVARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME2 DEFAULT GETUTCDATE()
);
```

## Info Output Example

```
+-----------+---------+---------------------+------+---------------------+---------+
| Category  | Version | Description         | Type | Installed On        | State   |
+-----------+---------+---------------------+------+---------------------+---------+
| Versioned | 1       | Create users table  | SQL  | 2024-01-15 10:00:00 | Success |
| Versioned | 2       | Add user roles      | SQL  | 2024-01-15 10:01:00 | Success |
| Versioned | 3       | Add user profile    | SQL  | 2024-01-15 10:02:00 | Success |
| Versioned | 4       | Add preferences     | SQL  |                     | Pending |
+-----------+---------+---------------------+------+---------------------+---------+
```

## Integration with Stack

- Run migrations before E2E tests (Playwright)
- Validate migrations in CI before deployment
- Test rollbacks in staging environment
- Pair with application integration tests
- Track migration issues in DefectDojo
- Report migration status in Allure

## Troubleshooting

### Common Issues

**Issue**: Checksum mismatch
```bash
# Repair checksums (after verifying changes are safe)
docker exec flyway flyway repair
```

**Issue**: Migration failed mid-way
```bash
# Check state
docker exec flyway flyway info

# Repair if needed
docker exec flyway flyway repair

# Re-run migration
docker exec flyway flyway migrate
```

**Issue**: Out of order migrations
```bash
# Enable out of order
docker exec flyway flyway migrate -outOfOrder=true
```

**Issue**: Schema already exists
```bash
# Baseline existing database
docker exec flyway flyway baseline -baselineVersion=1 -baselineDescription="Initial baseline"
```
