db-changes-var1.yaml

name: DB Change Check var1 - K

on:
  pull_request:
    branches:
      - main

jobs:
  check-sql-schema-changes:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout Branch
        uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Check For Changes
        id: changed-db-files
        uses: tj-actions/changed-files@v44
        with:
          files: "app/**"

      - name: Check Conditions
        id: check-conditions
        run: |
          echo "DB_CHANGE=${{ steps.changed-db-files.outputs.any_changed }}" >> $GITHUB_ENV

  run-alembic:
    needs: check-sql-schema-changes
    # if: ${{ needs.check-sql-schema-changes.DB_CHANGE == 'true' }}
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgis/postgis:14-3.4
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_USER: postgres
          POSTGRES_HOST: localhost
          POSTGRES_DRIVERNAME: postgresql
        options: >-
          --health-cmd pg_isready
          --health-interval 5s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - name: Drop unwanted PostGIS extensions
        run: |
          psql -h localhost -U postgres -d postgres -c "DROP EXTENSION IF EXISTS postgis_tiger_geocoder CASCADE;"
          psql -h localhost -U postgres -d postgres -c "DROP EXTENSION IF EXISTS postgis_topology CASCADE;"
          psql -h localhost -U postgres -d postgres -c "DROP EXTENSION IF EXISTS fuzzystrmatch CASCADE;"

        env:
          PGPASSWORD: postgres

      - name: Checkout Main Branch
        uses: actions/checkout@v4
        with:
          fetch-depth: 0
          ref: 'main'

      - name: Set up Python
        id: setup-python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Generate envs & set path
        id: generate-envs
        run: |
          DATABASE_URL="postgresql://postgres:postgres@localhost:5432"
          echo "DATABASE_URL=$DATABASE_URL" >> $GITHUB_ENV
          echo "PYTHONPATH=$PYTHONPATH:$(pwd)" >> $GITHUB_ENV

      - name: Install dependencies
        id: install-dependencies
        run: |
          python -m pip install --upgrade pip
          pip install -r app/requirements.txt

      - name: Run alembic script autogen for Initial DB Setup
        id: run-alembic-script-autogen
        env:
          DATABASE_URL: ${{ env.DATABASE_URL }}
        run: |
          alembic -c alembic.ini revision --autogenerate -m "test_temp_db_001"

      - name: Alembic Upgrade Head
        id: run-alembic-upgrade-initial
        env:
          DATABASE_URL: ${{ env.DATABASE_URL }}
        run: |
          alembic -c alembic.ini upgrade head

      - name: Preserve migration script for new branch checkout
        id: git-stash-migration-script
          git add .
          git stash # add and stash the last revision script before switching branches

      - name: Checkout New Branch
        uses: actions/checkout@v4
        with:
          fetch-depth: 0
          ref: 'add-gha-auto-db-migration'

      - name: Generate alembic script for new branch
        id: run-alembic-script-autogen-round-2
        env:
          DATABASE_URL: ${{ env.DATABASE_URL }}
        run: |
          git stash pop # get the last revision script
          alembic -c alembic.ini revision --autogenerate -m "test_new_changes_002"
          cat migrations/versions/test_new_changes_002.py

      - name: Upgrade alembic head with new revision
        id: run-alembic-upgrade-new
        env:
          DATABASE_URL: ${{ env.DATABASE_URL }}
        run: |
          alembic -c alembic.ini upgrade head

      - name: Test DB is set up correctly
        id: test-db-set-up
        run: |
          set -e # catch errors
          python migrations/test_migration.py

      # - name: Checkout new branch
      #   id: checkout-new-branch
      #   run: |
      #     git config --global user.email "github-actions[bot]@users.noreply.github.com"
      #     git config --global user.name "github-actions[bot]"
      #     git checkout -b ${{ env.BRANCH_NAME }}
      #     git push origin ${{ env.BRANCH_NAME }}

      # - name: Add alembic script to new branch
      #   id: add-alembic-script-to-new-branch
      #   run: |
      #     git add .
      #     git commit -m "Add new Alembic migration"
      #     git push origin HEAD:${{ github.ref }}

      # - name: Create PR
      #   id: create-pull-request
      #   uses: peter-evans/create-pull-request@v5
      #   with:
      #     branch: ${{ env.BRANCH_NAME }}
      #     base: main
      #     title: "BotPR: {{ env.BRANCH_NAME }}"
      #     author: "github-actions[bot] <github-actions[bot]@users.noreply.github.com>"