test_migrations.yaml

name: Test Skyportal Migrations

on:
  # Run only if potential changes in database schema
  pull_request:
    paths:
      - "baselayer"
      - "skyportal/models.py"
      - "alembic/versions/**"

jobs:
  test:
    name: Test SkyPortal migrations
    runs-on: ubuntu-latest
    timeout-minutes: 90
    env:
      # We only check migrations from this point onward, since we know
      # this specific commit to have migration integrity.
      #
      # This commit can be updated from time to time, if necessary
      # (e.g., to get access to a newer data loader or dependencies).
      # Pick any commit known to have passed the migration tests on
      # CI, or which has deployed successfully.
      MIGRATION_REFERENCE: 880cd56716015e33df22af1d256717ce98d7eb84

    services:
      postgres:
        image: postgres
        env:
          POSTGRES_USER: skyportal
          POSTGRES_PASSWORD: anything
        ports:
          - 5432:5432
        # needed because the postgres container does not provide a
        # healthcheck
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/setup-python@v1
        with:
          python-version: "3.8"

      - name: Checkout master
        uses: actions/checkout@v2
        with:
          fetch-depth: 0
          ref: master
          submodules: true

      - uses: actions/cache@v2
        with:
          path: |
            ~/.npm
          key: ${{ runner.os }}-npm-${{ hashFiles('package.json') }}

      - uses: actions/cache@v2
        with:
          path: |
            ~/.cache/pip
          key: ${{ runner.os }}-pip-${{ hashFiles('**/requirements*.txt') }}

      - name: Install system dependencies
        run: |
          curl -sL https://deb.nodesource.com/setup_14.x | sudo -E bash -

          sudo apt install -y wget nodejs unzip firefox nginx

          pip install --upgrade pip
          pip install wheel numpy

          export NPM_PACKAGES="${HOME}/.npm-packages"
          export PATH=${NPM_PACKAGES}/bin:$PATH
          export NODE_PATH="$NPM_PACKAGES/lib/node_modules:$NODE_PATH"

          sudo npm -g install npm@7.24.0

          which python; python --version
          echo npm $(npm --version)
          echo node $(node --version)
          nginx -v
          firefox --version

      - name: Install SkyPortal dependencies
        run: |
          export PYTHONPATH=$PYTHONPATH:$(pwd)
          make dependencies

          pip install black

      - name: Save current Alembic head on master
        run: |
          # Get the head line from Alembic history output
          head=$( PYTHONPATH=. alembic history | grep '(head)' )

          # Split by space; index 2 should be latest revision
          tokens=($head)
          current_head=${tokens[2]}
          echo ${current_head}
          echo "CURRENT_HEAD=${current_head}" >> $GITHUB_ENV

      - name: Checkout migration reference commit
        uses: actions/checkout@v2
        with:
          fetch-depth: 0
          ref: ${{ env.MIGRATION_REFERENCE }}
          submodules: true

      - name: Write SkyPortal configs
        run: |
          cat << EOF > config.yaml
            database:
              database: skyportal
              host: localhost
              port: 5432
              user: skyportal
              password: anything
          EOF

      - name: Initialize SkyPortal
        run: |
          # Usually, we create databases on the local machine, so
          # `createdb` just works out of the box.  However, when doing
          # this on a server, as in our case, we need to specify extra
          # options.
          #
          # db_init should not complain if the databases exist already
          #
          echo "localhost:5432:*:skyportal:anything" > ~/.pgpass
          chmod 600 ~/.pgpass

          createdb -h localhost -U skyportal skyportal_test
          psql -U skyportal -h localhost -c "GRANT ALL PRIVILEGES ON DATABASE skyportal_test TO skyportal;" skyportal_test

          make db_init

          pip list --format=columns
          npm ls --depth 0

          # Stamp Alembic HEAD
          PYTHONPATH=. alembic -x config=config.yaml stamp head

      - name: Test loading demo data
        run: |
          make run &
          sleep 10 && make load_demo_data
          kill %1

      - name: Checkout PR branch
        uses: actions/checkout@v2
        with:
          fetch-depth: 0
          submodules: true
          clean: false

      - name: Check for migration branching
        run: |
          # Make sure latest revision on master is included in Alembic history
          n_current_head=$( PYTHONPATH=. alembic history | grep -c $CURRENT_HEAD )
          if [[ $n_current_head -eq 0 ]]
          then
            cat << EOF
              The current head Alembic revision on master is not present in this branch's
              Alembic history. Please merge in the latest commit from master into this PR.
          EOF
            exit 1
          else
            echo "Alembic history includes the latest on master."
          fi

          # Make sure there are not multiple heads to the Alembic history
          num_heads=$( PYTHONPATH=. alembic history | grep -c '(head)' )
          if [[ $num_heads -ne 1 ]]
          then
            cat << EOF
              There is more than one head revision in this branch's Alembic history.
              Please resolve the branching revisions.
          EOF
            exit 1
          else
            echo "Alembic history has no conflicts in branch."
          fi

      - name: Run migration
        run: |
          # Update to latest dependencies
          make dependencies
          PYTHONPATH=. alembic -x config=config.yaml upgrade head
          PYTHONPATH=. alembic current

      - name: Check migration completeness with Alembic
        run: |
          # Try generating a new migration
          PYTHONPATH=. alembic -x config=config.yaml revision --autogenerate -m "Test migration"

          # The generated migration should be empty if the migration script provided in the PR
          # completely encompasses the changes in the database.
          pass_lines=$( cat alembic/versions/*test_migration.py | grep -c pass ) || true
          if [[ $pass_lines -ne 2 ]]
          then
            echo "Additional migrations needed: see the alembic-difference artifact."
            exit 1
          else
            echo "All migrations included in script."
          fi

      - name: Create a fresh database
        run: |
          # Make new configs
          cat << EOF > config.yaml
            database:
              database: skyportal2
              host: localhost
              port: 5432
              user: skyportal
              password: anything
          EOF

          createdb -h localhost -U skyportal skyportal2
          psql -U skyportal -h localhost -c "GRANT ALL PRIVILEGES ON DATABASE skyportal2 TO skyportal;" skyportal2

          make db_init

          make run &
          sleep 10 && make load_demo_data
          kill %1
          PYTHONPATH=. alembic -x config=config.yaml stamp head

      - name: Compare databases with migra
        run: |
          pip install migra

          # SQL needed to go from migrated database to fresh database
          migra --unsafe postgresql://skyportal:anything@localhost:5432/skyportal postgresql://skyportal:anything@localhost:5432/skyportal2 > migra.sql || true
          if [[ $(cat migra.sql | wc -l) -ne 0 ]]
          then
            echo "Differences found between migrated database and fresh database. See the migra-difference artifact."
            exit 1
          else
            echo "Migrated database and fresh database match."
          fi

      - name: Upload the Alembic diff
        uses: actions/upload-artifact@v2
        if: ${{ always() }}
        with:
          name: alembic-difference
          path: alembic/versions/**test_migration.py

      - name: Upload migra diff
        uses: actions/upload-artifact@v2
        if: ${{ always() }}
        with:
          name: migra-difference
          path: migra.sql