test.yml

name: Test Database

on:
  push:
    branches:
      - develop
  pull_request:
  workflow_dispatch:

jobs:
  prepare:
    runs-on: ubuntu-latest
    outputs:
      postgres_versions: ${{ steps.set-versions.outputs.postgres_versions }}
    steps:
      - name: Checkout Repo
        uses: actions/checkout@v4
      
      - uses: DeterminateSystems/nix-installer-action@main

      - name: Set PostgreSQL versions
        id: set-versions
        run: |
          VERSIONS=$(nix run nixpkgs#yq --  '.postgres_major[]' ansible/vars.yml | nix run nixpkgs#jq -- -R -s -c 'split("\n")[:-1]')
          echo "postgres_versions=$VERSIONS" >> $GITHUB_OUTPUT

  build:
    needs: prepare
    strategy:
      matrix:
        postgres_version: ${{ fromJson(needs.prepare.outputs.postgres_versions) }}
        include:
          - runner: [self-hosted, X64]
            arch: amd64
          - runner: arm-runner
            arch: arm64
    runs-on: ${{ matrix.runner }}
    timeout-minutes: 180
    env:
      POSTGRES_PORT: 5478
      POSTGRES_PASSWORD: password
    steps:
      - uses: actions/checkout@v3

      - uses: DeterminateSystems/nix-installer-action@main

      - name: Set PostgreSQL version environment variable
        run: echo "POSTGRES_MAJOR_VERSION=${{ matrix.postgres_version }}" >> $GITHUB_ENV

      - name: Strip quotes from pg major and set env var
        run: |
          stripped_version=$(echo ${{ matrix.postgres_version }} | sed 's/^"\(.*\)"$/\1/')
          echo "PGMAJOR=$stripped_version" >> $GITHUB_ENV

      - name: Generate common-nix.vars.pkr.hcl
        run: |
          PG_VERSION=$(sudo nix run nixpkgs#yq -- '.postgres_release["postgres'${{ matrix.postgres_version }}'"]' ansible/vars.yml)
          PG_VERSION=$(echo $PG_VERSION | tr -d '"')  # Remove any surrounding quotes
          echo 'postgres-version = "'$PG_VERSION'"' > common-nix.vars.pkr.hcl
          # Ensure there's a newline at the end of the file
          echo "" >> common-nix.vars.pkr.hcl

      - id: settings
        # Remove spaces and quotes to get the raw version string
        run: sed -r 's/(\s|\")+//g' common-nix.vars.pkr.hcl >> $GITHUB_OUTPUT

      - name: Generate args
        id: args
        run: |
          ARGS=$(sudo nix run nixpkgs#yq -- 'to_entries | map(select(.value|type == "!!str")) | map(.key + "=" + .value) | join("\n")' ansible/vars.yml)
          echo "result<<EOF" >> $GITHUB_OUTPUT
          echo "$ARGS" >> $GITHUB_OUTPUT
          echo "EOF" >> $GITHUB_OUTPUT

      - run: docker context create builders
      - uses: docker/setup-buildx-action@v3
        with:
          endpoint: builders
      - uses: docker/build-push-action@v5
        with:
          load: true
          context: .
          file: Dockerfile-${{ env.PGMAJOR }}
          target: production
          build-args: |
            ${{ steps.args.outputs.result }}
          tags: supabase/postgres:${{ steps.settings.outputs.postgres-version }},supabase_postgres
          cache-from: |
            type=gha,scope=${{ github.ref_name }}-${{ steps.settings.outputs.postgres-version }}-${{ matrix.arch }}
            type=gha,scope=${{ github.base_ref }}-${{ steps.settings.outputs.postgres-version }}-${{ matrix.arch }}
          cache-to: type=gha,mode=max,scope=${{ github.ref_name }}-${{ steps.settings.outputs.postgres-version }}-${{ matrix.arch }}

      - name: Start Postgres
        run: |
          docker run --rm --pull=never \
          -e POSTGRES_PASSWORD=${{ env.POSTGRES_PASSWORD }} \
          -p ${{ env.POSTGRES_PORT }}:5432 \
          --name supabase_postgres \
          -d supabase/postgres:${{ steps.settings.outputs.postgres-version }}

      - name: Install psql
        run: |
          sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
          wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
          sudo apt update
          sudo apt install -y --no-install-recommends postgresql-client-${{ env.PGMAJOR }}

      - name: Install pg_prove
        run: sudo cpan -T TAP::Parser::SourceHandler::pgTAP
        env:
          SHELL: /bin/bash

      - name: Wait for healthy database
        run: |
          count=0
          until [ "$(docker inspect -f '{{.State.Health.Status}}' "$container")" == "healthy" ]; do
              exit=$?
              count=$((count + 1))
              if [ $count -ge "$retries" ]; then
                  echo "Retry $count/$retries exited $exit, no more retries left."
                  docker stop -t 2 "$container"
                  return $exit
              fi
              sleep 1;
          done;
          echo "$container container is healthy"
        env:
          retries: 20
          container: supabase_postgres

      - name: Run tests
        run: pg_prove migrations/tests/test.sql
        env:
          PGHOST: localhost
          PGPORT: ${{ env.POSTGRES_PORT }}
          PGDATABASE: postgres
          PGUSER: supabase_admin
          PGPASSWORD: ${{ env.POSTGRES_PASSWORD }}

      - name: Check migrations are idempotent
        run: |
          for sql in ./migrations/db/migrations/*.sql; do
            echo "$0: running $sql"
            psql -v ON_ERROR_STOP=1 --no-password --no-psqlrc -f "$sql"
          done
        env:
          PGHOST: localhost
          PGPORT: ${{ env.POSTGRES_PORT }}
          PGDATABASE: postgres
          PGUSER: supabase_admin
          PGPASSWORD: ${{ env.POSTGRES_PASSWORD }}

      - name: Update Dockerfile.dbmate version
        run: |
          sed -i 's/%VERSION%/${{ env.PGMAJOR }}/g' migrations/Dockerfile.dbmate

      - name: verify schema.sql is committed
        run: |
          docker compose -f migrations/docker-compose.yaml up db dbmate --abort-on-container-exit
          if ! git diff --exit-code --quiet migrations/schema-${{ env.PGMAJOR }}.sql; then
            echo "Detected changes in schema.sql:"
            git diff migrations/schema-${{ env.PGMAJOR }}.sql
            exit 1
          fi