In this multi part blog post I will try to walk through integration testing from sending HTTP request to executing database queries against real DB. Approach that we gonna take will fit new projects as well ass legacy, where database has some hardcoded values.

All source code can be found at my GitHub, clone repository, and run rebuild.cmd to get started right away.

 │   docker-compose.yml
 │   init.sql
 │   migrate-prod.bat
 │   rebuild.cmd
 │
 └───migrations
     │   Dockerfile
     │   flyway.conf
     │
     └───scripts
             V1_0_0.sql

Lets start from top we have docker-compose.yml file gluing all pieces together.

version: '3.4'
services:
  postgres-shop:
    image: postgres:12.1
    container_name: postgres-shop
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    ports:
      - 6444:5432
  migrations-shop:
    container_name: migrations-shop
    command: migrate
    depends_on:
      - postgres-shop
    build:
      context: ./migrations
      dockerfile: ./Dockerfile

In order to run migrations flyway has to connect to database, we will take advantage of PostgreSQL initialization script and create database with user after database start.

CREATE ROLE admin_user WITH LOGIN PASSWORD 'secretPassword123!';

CREATE DATABASE rental;
GRANT ALL PRIVILEGES ON DATABASE rental TO admin_user;

This is just an example how to run flyway on your machine when you want to apply migrations to your production server. For this to work download flyway and add it to your system path variable

flyway -url=jdbc:postgresql://<ip>:<port>/<database> -configFiles=%~dp0\migrations\flyway.conf -locations=filesystem:%~dp0\migrations\scripts migrate

Flyway folder contains all data required to build custom flyway docker image with custom configs and migration scripts.

FROM flyway/flyway:6.0.2-alpine
COPY scripts/* /flyway/sql/
COPY flyway.conf /flyway/conf/

Truncated version of flyway.conf, with properties that are set. PostgreSQL database and flyway will be running in docker, so we can combine all docker references and newly created database information: server, user, password.

# JDBC url to use to connect to the database
# Examples
# --------
# Most drivers are included out of the box.
# * = JDBC driver must be downloaded and installed in /drivers manually
# ** = TNS_ADMIN environment variable must point to the directory of where tnsnames.ora resides
# PostgreSQL        : jdbc:postgresql://<host>:<port>/<database>?<key1>=<value1>&<key2>=<value2>...
flyway.url=jdbc:postgresql://postgres-shop/rental?user=admin_user&password=secretPassword123!

# Fully qualified classname of the JDBC driver (autodetected by default based on flyway.url)
# flyway.driver=

# User to use to connect to the database. Flyway will prompt you to enter it if not specified.
flyway.user=admin_user

# Password to use to connect to the database. Flyway will prompt you to enter it if not specified.
flyway.password=secretPassword123!

# The maximum number of retries when attempting to connect to the database. After each failed attempt,
# Flyway will wait 1 second before attempting to connect again, up to the maximum number of times specified
# by connectRetries. (default: 0)
flyway.connectRetries=360

# Comma-separated list of schemas managed by Flyway. These schema names are case-sensitive.
# Consequences:
# - Flyway will automatically attempt to create all these schemas, unless the first one already exists.
# - The first schema in the list will be automatically set as the default one during the migration.
# - The first schema in the list will also be the one containing the schema history table.
# - The schemas will be cleaned in the order of this list.
# - If Flyway created them, the schemas themselves will as be dropped when cleaning.
# (default: The default schema for the database connection)
flyway.schemas=public,rental

# Name of Flyway's schema history table (default: flyway_schema_history)
# By default (single-schema mode) the schema history table is placed in the default schema for the connection
# provided by the datasource.
# When the flyway.schemas property is set (multi-schema mode), the schema history table is placed in the first
# schema of the list.
flyway.table=schema_migrations

# The version to tag an existing schema with when executing baseline. (default: 1)
flyway.baselineVersion=1

# The description to tag an existing schema with when executing baseline. (default: << Flyway Baseline >>)
flyway.baselineDescription=Initial version

# Whether to automatically call baseline when migrate is executed against a non-empty schema with no schema history
# table. This schema will then be initialized with the baselineVersion before executing the migrations.
# Only migrations above baselineVersion will then be applied.
# This is useful for initial Flyway production deployments on projects with an existing DB.
# Be careful when enabling this as it removes the safety net that ensures
# Flyway does not migrate the wrong database in case of a configuration mistake! (default: false)
flyway.baselineOnMigrate=true

Finally Scripts folder contains all migration scripts. There are multiple ways to configure script file naming, for this example simple V{major}_{minor}_{patch} naming is sufficient. Scripts will be executed in order, based on versioning.

Now that we have all the pieces ready, lets run docker-compose . To make things easier I wrote small script: stop all containers, clear cache (this is necessary in order to rebuild flyway docker container with new script files), start all containers and apply migrations.

docker-compose down
docker-compose build --no-cache
docker-compose up

Running rebuild.cmd will start PostgreSQL, initialize it by creating our user and database. Flyway will be started as soon as PostgreSQL starts up and will log few warnings. It takes few seconds for the database to become available. Finally, scripts will be executed, results can be viewed in public.schema_migrations table.


Flyway migration table

In this blog post, we looked at a simple approach to start a local database in docker and run migrations. PostgreSQL can be replaced by any other database, flyway supports dozen of them, even flyway can be replaced by other migration running tools. I used this migration approach in work and in my personal projects, we haven't found any drawbacks or limitations. When working with a legacy database initialization can be done from a backup, when working in larger teams I would recommend using different versioning and running scripts out of order.