Setup Database CI/CD for PostgreSQL using Flyway and GitHub Actions

Continuous Integration/Continuous Deployment (CI/CD) ♾️ is a widely adopted practice in software development that helps ensure reliable and efficient delivery of applications. When it comes to databases, managing changes to a database can be complex and error-prone 🚨. Flyway is a powerful 🔥 open-source tool that simplifies database migrations, allowing you to version your database schema and apply changes in a controlled manner. In combination with GitHub Actions 🛠️, you can automate⚙️ the CI/CD pipeline for your PostgreSQL database, ensuring that changes to your database schema are deployed reliably and consistently 📈.

In this blog post, we will walk through the process of setting up PostgreSQL database CI/CD with Flyway and GitHub Actions, step by step 🧗.

Setting up a GitHub repo:

Let's set up a GitHub repository that will contain your database migration scripts.

For this demo, I'll create a repository named postgresql-flyway-demo and a blank folder inside it named migrations to keep the migration scripts 📜. These are the SQL files that define the changes you want to make to your database schema and data. Flyway will execute these scripts in order and keep track of the applied migrations.

You can use any naming convention for your scripts, but I recommend using the default Flyway convention. For example, V1.00__createtable.sql or V1.01__addcolumn.sql. The version number should be incremental and unique for each script. More details about the naming convention can be found 👉 here.

Create a GitHub workflow file:

A GitHub workflow file defines the steps to run Flyway and apply the migrations to your database. This file will be stored in your repository under the .github/workflows directory. Refer 👉 here for more details.

You can use the official Flyway action 🛠️ from the GitHub Marketplace, which simplifies the configuration and execution of Flyway.

For the demo, I am using this 👉 GitHub Action, available at GitHub Marketplace.

Let's take a look at the following 👇 workflow file postgresql-flyway-migrations.yml created for this demo.

name: postgresql-flyway-migrations
on:
  - push
jobs:
  test:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres
        env:
          POSTGRES_DB: ${{ secrets.DB_TEST }}
          POSTGRES_USER: ${{ secrets.USER_TEST }}
          POSTGRES_PASSWORD: ${{ secrets.PASSWORD_TEST }}
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v2
      - uses: joshuaavalon/flyway-action@v3.0.0
        with:
          url: ${{ secrets.URL_TEST }}
          user: ${{ secrets.USER_TEST }}
          password: ${{ secrets.PASSWORD_TEST }}
          locations: filesystem:./migrations
      - run: echo 'testing'


  deploy-to-prod:
    needs: test
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - uses: joshuaavalon/flyway-action@v3.0.0
        with:
          url: ${{ secrets.URL_PROD }}
          user: ${{ secrets.USER_PROD }}
          password: ${{ secrets.PASSWORD_PROD }}
          locations: filesystem:./migrations
      - run: echo 'deploying to prod'

The postgresql-flyway-migrations workflow consists of two jobs test and deploy-to-prod and will be triggered on push events, whenever you push a script to the migrations folder.

⚙️ ▶️The test job runs on an Ubuntu environment and uses the Postgres Docker image as a service to set up a PostgreSQL database as a Test Environment.

You can skip setting up the Test environment using postgres docker image and pass your DEV database URL instead if you have one.

It performs the following steps:

• Checkout the code from the repository.

• Build the Flyway Docker image 💿 with the migration scripts.

• Run the Flyway migrations on the Test database with the connection settings and credentials from your GitHub Secrets and verify 👨‍💻 that the migrations are successful.

🧐 Notice that the database URL, user and password are provided as GitHub Actions secrets.

It is generally not recommended to include sensitive credentials, such as usernames and passwords, in your Git repository. Storing credentials in a public repository can pose security risks, as it makes them accessible to anyone who has access to the repository, including potential malicious actors 🚨.

Secrets are encrypted values that you can store in your repository settings and use in your workflows. Refer 👉 here to know more about GitHub encrypted secrets.

To create secrets for your database credentials, follow these steps:

• Go to the Settings tab in your repository.

• Navigate to the Security section in the sidebar.

• Select Secrets and variables.

• Choose Actions and click on New repository secret.

⚙️ ▶️ The deploy-to-prod job triggers on successful completion of the test job. This ensures that only successfully tested changes are deployed to the Production database.

The steps are similar to the test job. However, the URL and credentials are of the Production database.

You can customize 👨‍💻 the workflow to suit your needs, such as adding more steps, environments, conditions, etc.

Test and deploy your database migrations:

The workflow will run whenever there is a push request to the main branch of your repository. The migrate command will apply any pending migrations to your database in order.

Let's perform the following migrations and see 😎 if it's working.

• Create a new table

• Add a column to the table

⚙️ ▶️ Create a new table - V1.00__createtable.sql

CREATE TABLE customers (
    id INT NOT NULL PRIMARY KEY
);

Pushed the V1.00__createtable.sql script to migrations folder in main branch.

Let's monitor 💻 the progress and status of the workflow by going to Actions tab and clicking on the postgresql-flyway-migrations workflow. See the workflow ran and both the steps - test & deploy-to-prod succeeded ✅.

Let's verify if the table gets created inside the database. And we see 🤩 the table customers got created with id column.

I am using the default postgres database for demo purposes. You should use your respective application databases to configure the workflow.

⚙️ ▶️ Add a column to the table - V1.01__addcolumn.sql

ALTER TABLE customers
  ADD COLUMN name VARCHAR(10) NOT NULL;

Next, push the 👆 code to add a column name to the customers table.

The workflow triggered automatically and both steps succeeded ✅.

And, we see the name column added 👇 to the customers table.

Additionally, Flyway creates and maintains a flyway_schema_history table in the database to keep track of the applied migrations. The schema history table stores metadata 📋 about each applied migration, such as the version, description, type, and execution time of the migration.

Please refer to these 👇 resources for more information:

Flyway 👉 https://flywaydb.org/documentation/

GitHub Actions 👉 https://docs.github.com/en/actions

The code 📜 can also be found at GitHub 👉 https://github.com/arinpro/postgresql-flyway-demo

That's it! 🚀 You have successfully set up a database CI/CD pipeline ♾️ using Flyway and GitHub Actions. You can now enjoy 🤩 the benefits of automated database deployments and migrations across different environments.

Happy Learning 🙌