psql/.gitea/workflows/flyway-migrate.yaml

name: Database Migration and Test
run-name: Flyway Migrate & Test by ${{ gitea.actor }}
on: [push]

jobs:
  migrate-and-test:
    runs-on: ubuntu-latest
    steps:
      - name: Check out repository code
        uses: actions/checkout@v3

      - name: Create test database
        uses: docker://alpine/psql:latest
        with:
          args: >
            sh -c 'export PGPASSWORD=${{ secrets.DB_PASSWORD }} &&
            psql -h ${{ secrets.DB_HOST }} -p ${{ secrets.DB_PORT }} -U ${{ secrets.DB_USER }} -d postgres
            -c "DROP DATABASE IF EXISTS testdb;"
            -c "CREATE DATABASE testdb;"'

      - name: Run Flyway Migrate
        uses: docker://flyway/flyway:10
        with:
          args: >
            -url=jdbc:postgresql://${{ secrets.DB_HOST }}:${{ secrets.DB_PORT }}/testdb
            -user=${{ secrets.DB_USER }}
            -password=${{ secrets.DB_PASSWORD }}
            -locations=filesystem:./sql
            -baselineOnMigrate=true
            migrate

      - name: Run Validation Test (SELECT COUNT)
        uses: docker://alpine/psql:latest
        with:
          args: >
            sh -c 'export PGPASSWORD=${{ secrets.DB_PASSWORD }} &&
            psql -h ${{ secrets.DB_HOST }} -p ${{ secrets.DB_PORT }} -U ${{ secrets.DB_USER }} -d testdb
            -c "SELECT COUNT(*) FROM users;"'

      - name: Drop test database
        if: always()
        uses: docker://alpine/psql:latest
        with:
          args: >
            sh -c 'export PGPASSWORD=${{ secrets.DB_PASSWORD }} &&
            psql -h ${{ secrets.DB_HOST }} -p ${{ secrets.DB_PORT }} -U ${{ secrets.DB_USER }} -d postgres
            -c "DROP DATABASE IF EXISTS testdb;"'