Flyway Database Migration (Local Dev)

This flow will spin up a Postgresql database and apply migrations to it using raw SQL commands, this is simliar to my Docker Compose Demo but the raw SQL resides with the code and the baseline command is auto-magic💕 This is useful for applying migrations locally.

The code I use in this post can be found at https://github.com/carlpaton/flyway-migration-demo/tree/main

Disclaimer: I personally think a Model First approach like EF Core is easier but there is merit in knowing more than one way to do db migrations.

Define Raw SQL

Migrations

Flyway has a naming pattern that we need to follow, for simplicity I just used an existing example in my public github

Initialization Scripts

This is useful to create users, schemas, set roles ect and is auto-magically run though a docker volumn mapped to docker-entrypoint-initdb.d, this script will only run the first time the container started as it checks the data directory. Also see hub.docker.com - postgres

I called my file initialize-database.sql, its sensible to have users with different access level, the application user should not have elevated access like this, it should only be allowed to do what the application does, normally just CRUD stuff.

1
2
3
4
5
6
7
CREATE USER demo_schema_manager WITH PASSWORD 'password';
ALTER USER demo_schema_manager WITH CREATEROLE;

CREATE SCHEMA demo;
GRANT USAGE ON SCHEMA demo TO demo_schema_manager;
GRANT CREATE ON SCHEMA demo TO demo_schema_manager;
ALTER SCHEMA demo OWNER TO demo_schema_manager;

Define Compose File

This needs to run services demo-db, demo-db-migration and demo-pgadmin

demo-db

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
demo-db:
  image: postgres:17.2
  ports:
    - 5432:5432
  environment:
    POSTGRES_USER: postgres
    POSTGRES_PASSWORD: postgres
    POSTGRES_DB: demo_db
  volumes:
    - ./initialize-database.sql:/docker-entrypoint-initdb.d/initialize-database.sql
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U postgres"]
    interval: 5s
    timeout: 5s
    retries: 5

demo-db-migration

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
demo-db-migration:
  image: flyway/flyway:11
  depends_on:
    demo-db:
      condition: service_healthy
  environment:
    FLYWAY_URL: jdbc:postgresql://demo-db:5432/demo_db
    FLYWAY_USER: demo_schema_manager
    FLYWAY_PASSWORD: password
    FLYWAY_LOCATIONS: filesystem:/flyway/migration_scripts
    FLYWAY_DEFAULT_SCHEMA: demo
    FLYWAY_VALIDATE_MIGRATION_NAMING: true
  command: migrate -connectRetries=5
  volumes:
    - ./migrations:/flyway/migration_scripts

demo-pgadmin

pgadmin4-server-file.json just imports my server configuration, you can take out the env PGADMIN_SERVER_JSON_FILE if you just want to use the UI to define your own.

1
2
3
4
5
6
7
8
9
10
11
12
13
demo-pgadmin:
  image: dpage/pgadmin4:8.14
  ports:
    - 5050:80    
  depends_on:
    demo-db:
      condition: service_healthy
  environment:
    PGADMIN_DEFAULT_EMAIL: user@domain.com
    PGADMIN_DEFAULT_PASSWORD: postgres
    PGADMIN_SERVER_JSON_FILE: /pgadmin4-server-file.json
  volumes:
    - ./pgadmin4-server-file.json:/pgadmin4-server-file.json

Run The Migrations Locally

You can just clone flyway-migration-demo or copy the examples above but I encourage you to type it out, think about what you actually typing, you will learn more this way.

  1. Save the files as
    • migrations in C:\dev\flyway-migration-demo\migrations
    • initialize file in the root C:\dev\flyway-migration-demo\initialize-database.sql-
    • compose file in the root C:\dev\flyway-migration-demo\docker-compose.yml
  2. Run docker compose up --wait (docker desktop may ask for permissions to share the files to the volumns)
  3. Wait for compose to confirm the services are healthy

services are healthy

  1. Wait a few seconds for pgAdmin to load the servers and then navigate to http://localhost:5050/browser/
    • The login is user@domain.com and password is postgres
    • The server will need a password, its postgres because we are using the administrator user

You could also install pgAdmin locally if you dont want to use the container.

  1. Check the migrations have been applied

migrations applied 102

  1. (OPTIONAL) To tear down the containers run docker compose down

Future Migrations

Tear Down Approach

  1. Locally add a new migration, example migrations-new/V1.0.3__fizzbuzz_data2.sql
    • This needs to be added to the migrations folder with the v101 and v102 files
  2. Tear down the stack with docker compose down
  3. Run docker compose up --wait and follow the steps above to see the applied migrations

Rolling Forward

You may care about your local test data that was not auto-magically seeded or you want to test the new migration the same way it would be applied in Production you can use most of the above and run the info command in another short lived container.

  1. Use a new container to connect to the db and run the flyway info command. It would be sensible to add this command to a make file, alternatively you can use the Flyway CLI locally and store the configuration as TOML.
1
docker run --rm --name flyway-manual-commands flyway/flyway:11 -user=demo_schema_manager -password=password -url=jdbc:postgresql://host.docker.internal:5432/demo_db -defaultSchema=demo info

Expected output below, as we only checking flyways info command the WARNING doesnt matter.

1
2
3
4
5
6
7
8
9
10
11
12
13
WARNING: No locations configured and default location 'sql' not found.
Flyway OSS Edition 11.1.0 by Redgate

See release notes here: https://rd.gt/416ObMi
Database: jdbc:postgresql://host.docker.internal:5432/demo_db (PostgreSQL 17.2)
Schema version: 1.0.2

+-----------+---------+----------------+------+---------------------+--------+----------+
| Category  | Version | Description    | Type | Installed On        | State  | Undoable |
+-----------+---------+----------------+------+---------------------+--------+----------+
| Versioned | 1.0.1   | fizzbuzz event | SQL  | 2024-12-19 03:36:47 | Future | No       |
| Versioned | 1.0.2   | fizzbuzz data  | SQL  | 2024-12-19 03:36:47 | Future | No       |
+-----------+---------+----------------+------+---------------------+--------+----------+

This is the same data as checking demo.flyway_schema_history but I think the info command is cool and helps understand the possible CLI commands.

  1. Copy the new migration into the migrations folder, same example as migrations-new/V1.0.3__fizzbuzz_data2.sql

  2. Run the MIGRATE command using the existing compose container

1
docker compose up demo-db-migration

Expected output

1
2
3
4
5
6
7
8
9
10
11
12
13
[+] Running 2/0
 ✔ Container flyway-migration-demo-demo-db-1            Running                                                    0.0s
 ✔ Container flyway-migration-demo-demo-db-migration-1  C...                                                       0.0s
Attaching to demo-db-migration-1
demo-db-migration-1  | Flyway OSS Edition 11.1.0 by Redgate
demo-db-migration-1  |
demo-db-migration-1  | See release notes here: https://rd.gt/416ObMi
demo-db-migration-1  | Database: jdbc:postgresql://demo-db:5432/demo_db (PostgreSQL 17.2)
demo-db-migration-1  | Successfully validated 3 migrations (execution time 00:00.053s)
demo-db-migration-1  | Current version of schema "demo": 1.0.2
demo-db-migration-1  | Migrating schema "demo" to version "1.0.3 - fizzbuzz data2"
demo-db-migration-1  | Successfully applied 1 migration to schema "demo", now at version v1.0.3 (execution time 00:00.021s)
demo-db-migration-1 exited with code 0

  1. Running the INFO command again should then show version 1.0.3.

  2. Check the migrations have been applied, feels repetitive … but I do what I want ( ͡° ͜ʖ ͡°)

migrations applied 103

Next steps would be to apply the migration in a CICD pipeline, Github Actions is the cool kid on the block right?

References