Skip to main content
Redhat Developers  Logo
  • Products

    Featured

    • Red Hat Enterprise Linux
      Red Hat Enterprise Linux Icon
    • Red Hat OpenShift AI
      Red Hat OpenShift AI
    • Red Hat Enterprise Linux AI
      Linux icon inside of a brain
    • Image mode for Red Hat Enterprise Linux
      RHEL image mode
    • Red Hat OpenShift
      Openshift icon
    • Red Hat Ansible Automation Platform
      Ansible icon
    • Red Hat Developer Hub
      Developer Hub
    • View All Red Hat Products
    • Linux

      • Red Hat Enterprise Linux
      • Image mode for Red Hat Enterprise Linux
      • Red Hat Universal Base Images (UBI)
    • Java runtimes & frameworks

      • JBoss Enterprise Application Platform
      • Red Hat build of OpenJDK
    • Kubernetes

      • Red Hat OpenShift
      • Microsoft Azure Red Hat OpenShift
      • Red Hat OpenShift Virtualization
      • Red Hat OpenShift Lightspeed
    • Integration & App Connectivity

      • Red Hat Build of Apache Camel
      • Red Hat Service Interconnect
      • Red Hat Connectivity Link
    • AI/ML

      • Red Hat OpenShift AI
      • Red Hat Enterprise Linux AI
    • Automation

      • Red Hat Ansible Automation Platform
      • Red Hat Ansible Lightspeed
    • Developer tools

      • Red Hat Trusted Software Supply Chain
      • Podman Desktop
      • Red Hat OpenShift Dev Spaces
    • Developer Sandbox

      Developer Sandbox
      Try Red Hat products and technologies without setup or configuration fees for 30 days with this shared Openshift and Kubernetes cluster.
    • Try at no cost
  • Technologies

    Featured

    • AI/ML
      AI/ML Icon
    • Linux
      Linux Icon
    • Kubernetes
      Cloud icon
    • Automation
      Automation Icon showing arrows moving in a circle around a gear
    • View All Technologies
    • Programming Languages & Frameworks

      • Java
      • Python
      • JavaScript
    • System Design & Architecture

      • Red Hat architecture and design patterns
      • Microservices
      • Event-Driven Architecture
      • Databases
    • Developer Productivity

      • Developer productivity
      • Developer Tools
      • GitOps
    • Secure Development & Architectures

      • Security
      • Secure coding
    • Platform Engineering

      • DevOps
      • DevSecOps
      • Ansible automation for applications and services
    • Automated Data Processing

      • AI/ML
      • Data Science
      • Apache Kafka on Kubernetes
      • View All Technologies
    • Start exploring in the Developer Sandbox for free

      sandbox graphic
      Try Red Hat's products and technologies without setup or configuration.
    • Try at no cost
  • Learn

    Featured

    • Kubernetes & Cloud Native
      Openshift icon
    • Linux
      Rhel icon
    • Automation
      Ansible cloud icon
    • Java
      Java icon
    • AI/ML
      AI/ML Icon
    • View All Learning Resources

    E-Books

    • GitOps Cookbook
    • Podman in Action
    • Kubernetes Operators
    • The Path to GitOps
    • View All E-books

    Cheat Sheets

    • Linux Commands
    • Bash Commands
    • Git
    • systemd Commands
    • View All Cheat Sheets

    Documentation

    • API Catalog
    • Product Documentation
    • Legacy Documentation
    • Red Hat Learning

      Learning image
      Boost your technical skills to expert-level with the help of interactive lessons offered by various Red Hat Learning programs.
    • Explore Red Hat Learning
  • Developer Sandbox

    Developer Sandbox

    • Access Red Hat’s products and technologies without setup or configuration, and start developing quicker than ever before with our new, no-cost sandbox environments.
    • Explore Developer Sandbox

    Featured Developer Sandbox activities

    • Get started with your Developer Sandbox
    • OpenShift virtualization and application modernization using the Developer Sandbox
    • Explore all Developer Sandbox activities

    Ready to start developing apps?

    • Try at no cost
  • Blog
  • Events
  • Videos

Containerizing SQL DB changes with Flyway, Kubernetes, and OpenShift

January 10, 2018
Elvadas Nono
Related topics:
ContainersKubernetes
Related products:
Red Hat OpenShiftRed Hat OpenShift Container Platform

Share:

    In DevOps projects, you are sometimes haunted by the practices inherited from the monolithic world. In a previous project, we were checking how to simply apply SQL updates and changes to a relational database management system (RDBMS) database in an OpenShift Cluster.

    Micro database schema evolution patterns are perfectly described by Edson Yanaga in his brilliant free book: Migrating to Microservice Databases: From Relational Monolith to Distributed Data.  A video presentation of these patterns is also available on youtube.

    In this blog post series we will show a simple approach to implement the described patterns in your Continuous Integration and Continuous Delivery (CI/CD) pipelines on OpenShift. The series is split in two parts:

    • This post shows how to handle SQL update automation using Flyway, Dockerfiles, and Kubernetes on OpenShift.
    • A future post will showcase application migration patterns, including database migration stages using OpenShift Jenkins2 pipelines.

    The approach uses docker containers, Flyway and Kubernetes Objects to automate SQL updates/patches on a micro database running on OpenShift.

    Create your Micro Database

    To keep it simple We will rely on a docker image that provides a simple Postgres database with a custom prebuilt data set, but you can build a custom database service to follow this demo.

    The database is hosted on Openshift, and we assume you have a basic knowledge of Openshift, Kubernetes, and docker containers. You can install a simple Minishift/CDK Cluster using these instructions:

    Once you have your OpenShift/minishift installation running, connect as admin using the oc CLI command:

    $ oc login https://192.168.99.100:8443 -u developer -p developer
    $ oc new-project ocp-flyway-db-migration
    
    

    Grant anyuid scc to the default service account in order to run docker images

    
    $ oc adm policy add-scc-to-user anyuid -z default 
    $ oc new-app --docker-image=jbossdevguidebook/beosbank_posgres_db_europa:latest --name=beosbank-posgres-db-europa

    Openshift view: Beosbank pod

    OpenShift view: Beosbank pod">

    Determine the database pod name, and connect to the database.. Then you can explore the database content:

    $ oc get pods
     NAME                                 READY     STATUS    RESTARTS   AGE
     beosbank-posgres-db-europa-1-p16bx   1/1       Running   1          22h
    
    $ oc rsh beosbank-posgres-db-europa-1-p16bx
    # psql -U postgres
    

    Now that the RDBMS is up and running, we may ask how to perform automatic SQL updates on the database.

    From monolithic processes, we have various options to do it, including SQL batches with Flyway runtimes. In the next section we will see how to containerize a Flyway update first, and then automate it with Kubernetes.

     

    Containerizing SQL updates with Flyway runtimes

    The purpose behind the Flyway process containerization is to provide on-the-fly a container that can connect to the database container using Java Database Connectivity (JDBC) protocol in order to perform SQL updates.

    From DockerHub you can find a lot of custom images for Flyway. The following Dockerfile can be used to procure a more suitable one in the OpenShift context:

    FROM alpine
    MAINTAINER "Nono Elvadas"
    
    
    ENV FLYWAY_VERSION=4.2.0
    
    ENV FLYWAY_HOME=/opt/flyway/$FLYWAY_VERSION \
     FLYWAY_PKGS="https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/${FLYWAY_VERSION}/flyway-commandline-${FLYWAY_VERSION}.tar.gz"
    
    
    LABEL com.redhat.component="flyway" \
     io.k8s.description="Platform for upgrading database using flyway" \
     io.k8s.display-name="DB Migration with flyway " \
     io.openshift.tags="builder,sql-upgrades,flyway,db,migration"
    
    
    RUN apk add --update \
     openjdk8-jre \
     wget \
     bash
    
    #Download flyway
    RUN wget --no-check-certificate $FLYWAY_PKGS &&\
     mkdir -p $FLYWAY_HOME && \
     mkdir -p /var/flyway/data && \
     tar -xzf flyway-commandline-$FLYWAY_VERSION.tar.gz -C $FLYWAY_HOME --strip-components=1
    
    VOLUME /var/flyway/data
    
    ENTRYPOINT cp -f /var/flyway/data/*.sql $FLYWAY_HOME/sql/ && \
     $FLYWAY_HOME/flyway baseline migrate info -user=${DB_USER} -password=${DB_PASSWORD} -url=${DB_URL}

    The Dockerfile installs wget, bash and a Java runtime environment, then downloads a specific version of Flyway binaries. Flyway binaries are installed. A volume is created  on /var/flyway/data to hold SQL files we want to be executed on the database.

    By default, Flyway will check the SQL file in the $FLYWAY_HOME/sql/ folder.

    We first copy all of the provided SQL files from the data volume to $FLYWAY_HOME/sql/ and start a migration script. Database url and credentials should be provided as environment variables.

    Note: Originally the idea was to tell Flyway to read SQL files from the volume without copying or moving them to the container home directory. However, we faced an issue with this configuration:
    (See flyway issue 1807 on github). Indeed the Flyway engine will recursively read the volume, including the hidden subfolder. There is a Request for Enhancement to customize this behavior and prevent Flyway from reading meta data files in the volume mount folder.

    Build the image using the command:

    $ docker build -t --no-cache jbossdevguidebook/flyway:v1.0.4-rhdblog .
    
     ... 
    
    2018-01-07 13:48:43 (298 KB/s) - 'flyway-commandline-4.2.0.tar.gz' saved [13583481/13583481] 
    ---> 095befbd2450 
    Removing intermediate container 8496d11bf4ae 
    Step 8/9 : VOLUME /var/flyway/data
     ---> Running in d0e012ece342
     ---> 4b81dfff398b
    Removing intermediate container d0e012ece342
    Step 9/9 : ENTRYPOINT cp -f /var/flyway/data/\*.sql $FLYWAY_HOME/sql/ && $FLYWAY_HOME/flyway baseline migrate info -user=${DB_USER} -password=${DB_PASSWORD} -url=${DB_URL}
     ---> Running in ff2431eb1c26
     ---> 0a3721ff4863
    Removing intermediate container ff2431eb1c26
    Successfully built 0a3721ff4863
    Successfully tagged jbossdevguidebook/flyway:v1.0.4-rhdblog

    The database client is now available as a docker image. In the next section, we will see how to combine Kubernetes objects in OpenShift to automate SQL updates for this database.

    Kubernetes in action

    Kubernetes provides various deployment objects and patterns we can rely on to apply live SQL updates from containers created on top of the "jbossdevguidebook/flyway:v1.0.4-rhdblog" image:

    • Deployment Config
    • Job
    • CronJob/ScheduledJob
    • InitContainer; Sidecar

    In the following section we will illustrate how a single Kubernetes job object can be used to perform live SQL updates. SQL files will be provided to the container through a volume and a configMap.

    Create a configMap from provided SQL files

    $ cd ocp-flyway-db-migration/sql
    $ oc create cm sql-configmap --from-file=.
    configmap "sql-configmap" created

    Create a Job to update the DB.

    The job spec is provided. To keep it simple we are not going in deep details of the customization.  Files are available on my github repo.

    • Include secrets to keep db user and password credentials
    • Manage job history limits; restart policy according to the desired policy
    $ oc create -f https://raw.githubusercontent.com/nelvadas/ocp-flyway-db-migration/master/beosbank-flyway-job.yaml

    Check that the job was created in OpenShift:

    $ oc get jobs
    NAME DESIRED SUCCESSFUL AGE
    beosbank-dbupdater-job 1 1 2d
    

    Check the pods. Once the job is created, it generates a job instance that is executed by a new pod.

    $ oc get pods
    NAME READY STATUS RESTARTS AGE
    beosbank-dbupdater-job-wzk9q 0/1 Completed 0 2d
    beosbank-posgres-db-europa-1-p16bx 1/1 Running 2 6d

    The job instance completed successfully according to the log, and the migration steps have been applied.

    $ oc logs beosbank-dbupdater-job-wzk9q
    Flyway 4.2.0 by Boxfuse
    Database: jdbc:postgresql://beosbank-posgres-db-europa/beosbank-europa (PostgreSQL 9.6)
    Creating Metadata table: "public"."schema_version"
    Successfully baselined schema with version: 1
    Successfully validated 5 migrations (execution time 00:00.014s)
    Current version of schema "public": 1
    Migrating schema "public" to version 1.1 - UpdateCountry
    Migrating schema "public" to version 2.2 - UpdateCountry2
    Migrating schema "public" to version 2.3 - UpdateZip
    Migrating schema "public" to version 3.0 - UpdateStreet
    Successfully applied 4 migrations to schema "public" (execution time 00:00.046s).
    +---------+-----------------------+---------------------+---------+
    | Version | Description | Installed on | State |
    +---------+-----------------------+---------------------+---------+
    | 1 | << Flyway Baseline >> | 2018-01-05 04:35:16 | Baselin |
    | 1.1 | UpdateCountry  | 2018-01-05 04:35:16 | Success |
    | 2.2 | UpdateCountry2 | 2018-01-05 04:35:16 | Success |
    | 2.3 | UpdateZip      | 2018-01-05 04:35:16 | Success |
    | 3.0 | UpdateStreet   | 2018-01-05 04:35:16 | Success |
    +---------+-----------------------+---------------------+---------+

    Check the updated DB

    $ oc rsh beosbank-posgres-db-europa-1-p16bx
    # psql -U postgres
    psql (9.6.2)
    Type "help" for help.
    
    postgres=# \connect beosbank-europa
    beosbank-europa=# select * from eu_customer;
     id | city      | country | street             | zip | birthdate |firstname | lastname
    ----+-------------+------------------+-------------------+--------+------------+
     1 | Berlin     | Germany  | brand burgStrasse | 10115 | 1985-06-20 |Yanick | Modjo
     2 | Bologna    | Italy    | place Venice      | 40100 | 1984-11-21 |Mirabeau | Luc
     3 | Paris      | France   | Bld DeGaule       | 75001 | 2000-02-07 |Noe | Nono
     4 | Chatillon  | France   | Avenue JFK        | 55 | 1984-02-19 |Landry | Kouam
     5 | Douala     | Cameroon | bld Liberte       | 1020 | 1996-04-21 |Ghislain | Kamga
     6 | Yaounde    | Cameroon | Hypodrome         | 1400 | 1983-11-18 |Nathan | Brice
     7 | Bruxelles  | Belgium  | rue Van Gogh      | 1000 | 1980-09-06 |Yohan | Pieter
     9 | Bamako     | Mali     | Rue Modibo Keita  | 30 | 1979-05-17 |Mohamed | Diallo
     10 | Cracovie  | Pologne  | Avenue Vienne     | 434 | 1983-05-17 |Souleymann | Njifenjou
     11 | Chennai   | Red Hat Training | Gandhi street | 600001 | 1990-02-13 |Anusha | Mandalapu
     12 | Sao Polo  | Open Source | samba bld | 75020 | 1994-02-13 |Adriana | Pinto
     8 | Farnborough | UK | 200 Fowler Avenue | 208 | 1990-01-01 |John | Doe
    (12 rows)
    
    beosbank-europa=#

    If the batch is rerun with same migration scripts, as the database is already aware of the modifications, a warning is displayed in your log and no update is performed:

    Current version of schema "public": 3.0
    Schema "public" is up to date. No migration necessary.

    This concludes the article. Hope you learn something that will help you during your container journey.

    The full source is available from my Github repository:
    https://github.com/nelvadas/ocp-flyway-db-migration

    Last updated: January 5, 2022

    Recent Posts

    • Storage considerations for OpenShift Virtualization

    • Upgrade from OpenShift Service Mesh 2.6 to 3.0 with Kiali

    • EE Builder with Ansible Automation Platform on OpenShift

    • How to debug confidential containers securely

    • Announcing self-service access to Red Hat Enterprise Linux for Business Developers

    Red Hat Developers logo LinkedIn YouTube Twitter Facebook

    Products

    • Red Hat Enterprise Linux
    • Red Hat OpenShift
    • Red Hat Ansible Automation Platform

    Build

    • Developer Sandbox
    • Developer Tools
    • Interactive Tutorials
    • API Catalog

    Quicklinks

    • Learning Resources
    • E-books
    • Cheat Sheets
    • Blog
    • Events
    • Newsletter

    Communicate

    • About us
    • Contact sales
    • Find a partner
    • Report a website issue
    • Site Status Dashboard
    • Report a security problem

    RED HAT DEVELOPER

    Build here. Go anywhere.

    We serve the builders. The problem solvers who create careers with code.

    Join us if you’re a developer, software engineer, web designer, front-end designer, UX designer, computer scientist, architect, tester, product manager, project manager or team lead.

    Sign me up

    Red Hat legal and privacy links

    • About Red Hat
    • Jobs
    • Events
    • Locations
    • Contact Red Hat
    • Red Hat Blog
    • Inclusion at Red Hat
    • Cool Stuff Store
    • Red Hat Summit
    © 2025 Red Hat

    Red Hat legal and privacy links

    • Privacy statement
    • Terms of use
    • All policies and guidelines
    • Digital accessibility

    Report a website issue