test-db-changes.yml

name: Test DB Changes

on:
  push:
    branches:
      - main
      - patch-*
      - prepare-*
  pull_request:
    paths:
      - '**.go'
      - 'server/datastore/mysql/schema.sql'
      - '.github/workflows/test-db-changes.yml'
  workflow_dispatch: # Manual

# This allows a subsequently queued workflow run to interrupt previous runs
concurrency:
  group: ${{ github.workflow }}-${{ github.head_ref || github.run_id}}
  cancel-in-progress: true

defaults:
  run:
    # fail-fast using bash -eo pipefail. See https://docs.github.com/en/actions/using-workflows/workflow-syntax-for-github-actions#exit-codes-and-error-action-preference
    shell: bash

permissions:
  contents: read

jobs:
  test-db-changes:
    runs-on: ubuntu-latest
    steps:
    - name: Harden Runner
      uses: step-security/harden-runner@63c24ba6bd7ba022e95695ff85de572c04a18142 # v2.7.0
      with:
        egress-policy: audit

    - name: Checkout Code
      uses: actions/checkout@629c2de402a417ea7690ca6ce3f33229e27606a5 # v2
      with:
        fetch-depth: 0

    # TODO: This doesn't cover all scenarios since other PRs might
    # be merged into `main` after this check has passed.
    #
    # We should add a Slack notification or something similar for
    # when this check fails on `main`.
    #
    # TODO: This only checks for added files, we should also check for renames,
    # which should be more of an edge case, but they might still happen
    - name: Check migration order
      run: |
        # if the workflow is run during a push event (on merges to main and
        # tags,) use the latest created tag as a reference
        base_ref=origin/${{github.base_ref}}
        if [ "${{github.event_name}}" == "push" ]; then
          base_ref=$(git tag --list "fleet-v*" --sort=-creatordate | head -n 1)
        fi

        all_migrations=($(ls server/datastore/mysql/migrations/tables/20*_*.go | sort -r | grep -v '_test.go'))
        new_migrations=($(git diff --find-renames --name-only --diff-filter=A $base_ref -- server/datastore/mysql/migrations/tables/20\*_\*.go ':(exclude,glob)server/datastore/mysql/migrations/tables/20*_*_test.go' | sort -r))

        index=0
        for migration in "${new_migrations[@]}"; do
          if [ "$migration" != "${all_migrations[$index]}" ]; then
            echo "❌ fail: $migration has an older timestamp than ${all_migrations[$index]}"
            echo "this might cause problems if this change is merged"
            echo "please update the timestamp of $migration"
            exit 1
          fi
          index=$((index+1))
        done

    - name: Prevent hosts foreign keys
      run: |
        # grep exits with an error code if it doesn't find a match, so this condition
        # is only true if it a) finds a matching migrations file in the diff, and b)
        # finds an FK to hosts in one of the migrations files.
        #
        # grep prints the matches, which will help figure out where those references are.
        if git diff --name-only origin/main | grep "migrations/" | xargs grep -i -E 'references\s*hosts\s*\(\s*id\s*\)' ; then
        	echo "❌ fail: hosts foreign keys are not allowed"
        	echo "Ref: https://github.com/fleetdm/fleet/blob/main/handbook/engineering/scaling-fleet.md#foreign-keys-and-locking"
        	exit 1
        fi


    - name: Install Go
      uses: actions/setup-go@93397bea11091df50f3d7e59dc26a7711a8bcfbe # v4.1.0
      with:
        go-version-file: 'go.mod'

    - name: Start Infra Dependencies
      # Use & to background this
      run: docker compose up -d mysql_test &

    - name: Wait for mysql
      run: |
        echo "waiting for mysql..."
        until docker compose exec -T mysql_test sh -c "mysql -uroot -p\"\${MYSQL_ROOT_PASSWORD}\" -e \"SELECT 1=1\" fleet" &> /dev/null; do
            echo "."
            sleep 1
        done
        echo "mysql is ready"

    - name: Verify test schema changes
      run: |
        make dump-test-schema
        if [[ $(git diff server/datastore/mysql/schema.sql) ]]; then
          echo "❌ fail: uncommited changes in schema.sql"
          echo "please run `make dump-test-schema` and commit the changes"
          exit 1
        fi