Simplifying Database Migrations with Docker, Flyway, and PostgreSQL

Introduction

In modern software development, managing database migrations can be a complex task. Ensuring that your database schema is synchronized across different environments, such as development, testing, and production, is crucial for the stability and reliability of your application. One effective way to handle this challenge is by using Flyway, a robust database migration tool, in combination with Docker and PostgreSQL. In this blog post, I’ll will walk through setting up a Dockerized PostgreSQL database with Flyway for managing database migrations, demonstrating how to streamline your development workflow and maintain consistency across environments.

Thank me by sharing on Twitter 🙏

AI generated podcast episode based off this post.

Setting Up the Project Structure

Organizing Your Project

A well-organized project structure is the foundation of any successful development project. For our setup, create an infrastructure directory that contains a db subdirectory. This subdirectory will hold all necessary configurations and migration scripts.

Here’s how your project structure should look:

Plaintext

my_project/
│
├── src/
│   └── ...
│
├── infrastructure/
│   ├── db/
│   │   ├── .env
│   │   └── migrations/
│   │       └── V1__Create_country_codes_table.sql
│   └── docker-compose.yml
│
└── README.md

This structure keeps all infrastructure-related configuration files organized and separate from your application code, making it easier to manage and maintain.

Creating the .env File

The .env file stores environment variables used by Docker Compose to configure the PostgreSQL and Flyway services. Create the .env file inside the db directory with the following content:

Plaintext

POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres
POSTGRES_DB=mydatabase
FLYWAY_URL=jdbc:postgresql://db:5432/mydatabase
FLYWAY_USER=postgres
FLYWAY_PASSWORD=postgres
FLYWAY_SCHEMAS=public

These variables will be used to configure the PostgreSQL database and Flyway migration tool, ensuring that your database credentials and connection details are easily manageable and secure.

Writing the Migration Script

Creating the Migration Script

Our first migration script will create a table named country_codes with the following columns:

id: An auto-incrementing integer primary key.
code: A string (VARCHAR) field that cannot be null.
created_at: A timestamp field that defaults to the current timestamp when a record is created.
updated_at: A timestamp field that defaults to the current timestamp when a record is created and updates automatically with the current timestamp whenever the record is updated.

Create a new file named V1__Create_country_codes_table.sql inside the migrations directory with the following content:

SQL

-- V1__Create_country_codes_table.sql
CREATE TABLE country_codes (
    id SERIAL PRIMARY KEY,
    code VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create a function to update the updated_at field
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger to update the updated_at field before any update
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON country_codes
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

This script sets up the country_codes table and ensures that the updated_at field is automatically updated whenever a record is modified.

Configuring Docker Compose

Writing the Docker Compose File

Docker Compose allows you to define and manage multi-container Docker applications. Our Docker Compose configuration will define two services: db for PostgreSQL and flyway for managing database migrations.

Create the docker-compose.yml file inside the infrastructure directory with the following content:

YAML

services:
  db:
    # Use the official PostgreSQL image from Docker Hub
    image: postgres:latest
    # Name the container
    container_name: postgres
    # Load environment variables from the .env file
    env_file:
      - ./db/.env
    # Persist PostgreSQL data
    volumes:
      - postgres_data:/var/lib/postgresql/data
    # Expose PostgreSQL port
    ports:
      - "5432:5432"
    # Health check to ensure PostgreSQL is ready
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

  flyway:
    # Use the official Flyway image from Docker Hub
    image: flyway/flyway:latest
    # Name the container
    container_name: flyway
    # Ensure the db service is healthy before starting Flyway
    depends_on:
      db:
        condition: service_healthy
    # Load environment variables from the .env file
    env_file:
      - ./db/.env
    # Mount the migrations directory
    volumes:
      - ./db/migrations:/flyway/sql
    # Run Flyway migrations
    entrypoint: ["flyway", "migrate"]

# Define named volumes
volumes:
  postgres_data:

Running Docker Compose

With the docker-compose.yml file in place, you can now start the services by running:

ShellScript

docker-compose up

This command will start the PostgreSQL and Flyway services, apply the migrations, and ensure your database schema is up to date.

Conclusion

Setting up a robust and consistent database migration process is crucial for any modern application. By leveraging Docker, Flyway, and PostgreSQL, you can streamline your development workflow, ensuring that your database schema remains consistent across different environments. This setup not only simplifies the migration process but also enhances the maintainability and scalability of your application.

I hope this guide has helped you understand how to set up and manage database migrations using Docker, Flyway, and PostgreSQL. If you have any questions or feedback, please leave a comment below.