bitwarden-server/.github/workflows/test-database.yml

name: Database testing

on:
  workflow_dispatch:
  push:
    branches:
      - "main"
      - "rc"
      - "hotfix-rc"
    paths:
      - ".github/workflows/test-database.yml" # This file
      - "src/Sql/**" # SQL Server Database Changes
      - "util/Migrator/**" # New SQL Server Migrations
      - "util/MySqlMigrations/**" # Changes to MySQL
      - "util/PostgresMigrations/**" # Changes to Postgres
      - "util/SqliteMigrations/**" # Changes to Sqlite
      - "src/Infrastructure.Dapper/**" # Changes to SQL Server Dapper Repository Layer
      - "src/Infrastructure.EntityFramework/**" # Changes to Entity Framework Repository Layer
      - "test/Infrastructure.IntegrationTest/**" # Any changes to the tests
  pull_request:
    paths:
      - ".github/workflows/test-database.yml" # This file
      - "src/Sql/**" # SQL Server Database Changes
      - "util/Migrator/**" # New SQL Server Migrations
      - "util/MySqlMigrations/**" # Changes to MySQL
      - "util/PostgresMigrations/**" # Changes to Postgres
      - "util/SqliteMigrations/**" # Changes to Sqlite
      - "src/Infrastructure.Dapper/**" # Changes to SQL Server Dapper Repository Layer
      - "src/Infrastructure.EntityFramework/**" # Changes to Entity Framework Repository Layer
      - "test/Infrastructure.IntegrationTest/**" # Any changes to the tests

jobs:
  check-test-secrets:
    name: Check for test secrets
    runs-on: ubuntu-22.04
    outputs:
      available: ${{ steps.check-test-secrets.outputs.available }}
    permissions:
      contents: read

    steps:
      - name: Check
        id: check-test-secrets
        run: |
          if [ "${{ secrets.CODECOV_TOKEN }}" != '' ]; then
            echo "available=true" >> $GITHUB_OUTPUT;
          else
            echo "available=false" >> $GITHUB_OUTPUT;
          fi

  test:
    name: Run tests
    runs-on: ubuntu-22.04
    needs: check-test-secrets
    steps:
      - name: Check out repo
        uses: actions/checkout@11bd71901bbe5b1630ceea73d27597364c9af683 # v4.2.2

      - name: Set up .NET
        uses: actions/setup-dotnet@87b7050bc53ea08284295505d98d2aa94301e852 # v4.2.0

      - name: Restore tools
        run: dotnet tool restore

      - name: Docker Compose databases
        working-directory: "dev"
        # We could think about not using profiles and pulling images directly to cover multiple versions
        run: |
          cp .env.example .env
          docker compose --profile mssql --profile postgres --profile mysql up -d
        shell: pwsh

      - name: Add MariaDB for unified
        # Use a different port than MySQL
        run: |
          docker run --detach --name mariadb --env MARIADB_ROOT_PASSWORD=mariadb-password -p 4306:3306 mariadb:10

      # I've seen the SQL Server container not be ready for commands right after starting up and just needing a bit longer to be ready
      - name: Sleep
        run: sleep 15s

      - name: Checking pending model changes (MySQL)
        working-directory: "util/MySqlMigrations"
        run: 'dotnet ef migrations has-pending-model-changes -- --GlobalSettings:MySql:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "server=localhost;uid=root;pwd=SET_A_PASSWORD_HERE_123;database=vault_dev;Allow User Variables=true"

      - name: Checking pending model changes (Postgres)
        working-directory: "util/PostgresMigrations"
        run: 'dotnet ef migrations has-pending-model-changes -- --GlobalSettings:PostgreSql:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "Host=localhost;Username=postgres;Password=SET_A_PASSWORD_HERE_123;Database=vault_dev"

      - name: Checking pending model changes (SQLite)
        working-directory: "util/SqliteMigrations"
        run: 'dotnet ef migrations has-pending-model-changes -- --GlobalSettings:Sqlite:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "Data Source=${{ runner.temp }}/test.db"

      - name: Migrate SQL Server
        run: 'dotnet run --project util/MsSqlMigratorUtility/ "$CONN_STR"'
        env:
          CONN_STR: "Server=localhost;Database=vault_dev;User Id=SA;Password=SET_A_PASSWORD_HERE_123;Encrypt=True;TrustServerCertificate=True;"

      - name: Migrate MySQL
        working-directory: "util/MySqlMigrations"
        run: 'dotnet ef database update --connection "$CONN_STR" -- --GlobalSettings:MySql:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "server=localhost;uid=root;pwd=SET_A_PASSWORD_HERE_123;database=vault_dev;Allow User Variables=true"

      - name: Migrate MariaDB
        working-directory: "util/MySqlMigrations"
        run: 'dotnet ef database update --connection "$CONN_STR" -- --GlobalSettings:MySql:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "server=localhost;port=4306;uid=root;pwd=mariadb-password;database=vault_dev;Allow User Variables=true"

      - name: Migrate Postgres
        working-directory: "util/PostgresMigrations"
        run: 'dotnet ef database update --connection "$CONN_STR" -- --GlobalSettings:PostgreSql:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "Host=localhost;Username=postgres;Password=SET_A_PASSWORD_HERE_123;Database=vault_dev"

      - name: Migrate SQLite
        working-directory: "util/SqliteMigrations"
        run: 'dotnet ef database update --connection "$CONN_STR" -- --GlobalSettings:Sqlite:ConnectionString="$CONN_STR"'
        env:
          CONN_STR: "Data Source=${{ runner.temp }}/test.db"

      - name: Run tests
        working-directory: "test/Infrastructure.IntegrationTest"
        env:
          # Default Postgres:
          BW_TEST_DATABASES__0__TYPE: "Postgres"
          BW_TEST_DATABASES__0__CONNECTIONSTRING: "Host=localhost;Username=postgres;Password=SET_A_PASSWORD_HERE_123;Database=vault_dev"
          # Default MySql
          BW_TEST_DATABASES__1__TYPE: "MySql"
          BW_TEST_DATABASES__1__CONNECTIONSTRING: "server=localhost;uid=root;pwd=SET_A_PASSWORD_HERE_123;database=vault_dev"
          # Default Dapper SqlServer
          BW_TEST_DATABASES__2__TYPE: "SqlServer"
          BW_TEST_DATABASES__2__CONNECTIONSTRING: "Server=localhost;Database=vault_dev;User Id=SA;Password=SET_A_PASSWORD_HERE_123;Encrypt=True;TrustServerCertificate=True;"
          # Default Sqlite
          BW_TEST_DATABASES__3__TYPE: "Sqlite"
          BW_TEST_DATABASES__3__CONNECTIONSTRING: "Data Source=${{ runner.temp }}/test.db"
          # Unified MariaDB
          BW_TEST_DATABASES__4__TYPE: "MySql"
          BW_TEST_DATABASES__4__CONNECTIONSTRING: "server=localhost;port=4306;uid=root;pwd=mariadb-password;database=vault_dev;Allow User Variables=true"
        run: dotnet test --logger "trx;LogFileName=infrastructure-test-results.trx"
        shell: pwsh

      - name: Print MySQL Logs
        if: failure()
        run: 'docker logs $(docker ps --quiet --filter "name=mysql")'

      - name: Print MariaDB Logs
        if: failure()
        run: 'docker logs $(docker ps --quiet --filter "name=mariadb")'

      - name: Print Postgres Logs
        if: failure()
        run: 'docker logs $(docker ps --quiet --filter "name=postgres")'

      - name: Print MSSQL Logs
        if: failure()
        run: 'docker logs $(docker ps --quiet --filter "name=mssql")'

      - name: Report test results
        uses: dorny/test-reporter@31a54ee7ebcacc03a09ea97a7e5465a47b84aea5 # v1.9.1
        if: ${{ needs.check-test-secrets.outputs.available == 'true' && !cancelled() }}
        with:
          name: Test Results
          path: "**/*-test-results.trx"
          reporter: dotnet-trx
          fail-on-error: true

      - name: Docker Compose down
        if: always()
        working-directory: "dev"
        run: docker compose down
        shell: pwsh

  validate:
    name: Run validation
    runs-on: ubuntu-22.04
    steps:
      - name: Check out repo
        uses: actions/checkout@11bd71901bbe5b1630ceea73d27597364c9af683 # v4.2.2

      - name: Set up .NET
        uses: actions/setup-dotnet@87b7050bc53ea08284295505d98d2aa94301e852 # v4.2.0

      - name: Print environment
        run: |
          dotnet --info
          nuget help | grep Version
          echo "GitHub ref: $GITHUB_REF"
          echo "GitHub event: $GITHUB_EVENT"

      - name: Build DACPAC
        run: dotnet build src/Sql --configuration Release --verbosity minimal --output .
        shell: pwsh

      - name: Upload DACPAC
        uses: actions/upload-artifact@65c4c4a1ddee5b72f698fdd19549f0f0fb45cf08 # v4.6.0
        with:
          name: sql.dacpac
          path: Sql.dacpac

      - name: Docker Compose up
        working-directory: "dev"
        run: |
          cp .env.example .env
          docker compose --profile mssql up -d
        shell: pwsh

      - name: Migrate
        run: 'dotnet run --project util/MsSqlMigratorUtility/ "$CONN_STR"'
        env:
          CONN_STR: "Server=localhost;Database=vault_dev;User Id=SA;Password=SET_A_PASSWORD_HERE_123;Encrypt=True;TrustServerCertificate=True;"

      - name: Diff .sqlproj to migrations
        run: /usr/local/sqlpackage/sqlpackage /action:DeployReport /SourceFile:"Sql.dacpac" /TargetConnectionString:"Server=localhost;Database=vault_dev;User Id=SA;Password=SET_A_PASSWORD_HERE_123;Encrypt=True;TrustServerCertificate=True;" /OutputPath:"report.xml" /p:IgnoreColumnOrder=True /p:IgnoreComments=True
        shell: pwsh

      - name: Generate SQL file
        run: /usr/local/sqlpackage/sqlpackage /action:Script /SourceFile:"Sql.dacpac" /TargetConnectionString:"Server=localhost;Database=vault_dev;User Id=SA;Password=SET_A_PASSWORD_HERE_123;Encrypt=True;TrustServerCertificate=True;" /OutputPath:"diff.sql" /p:IgnoreColumnOrder=True /p:IgnoreComments=True
        shell: pwsh

      - name: Report validation results
        uses: actions/upload-artifact@65c4c4a1ddee5b72f698fdd19549f0f0fb45cf08 # v4.6.0
        with:
          name: report.xml
          path: |
            report.xml
            diff.sql

      - name: Validate XML
        run: |
          if grep -q "<Operations>" "report.xml"; then
             echo
             echo "Migration files are not in sync with the files in the Sql project. Review to make sure that any stored procedures / other db changes match with the stored procedures in the Sql project."
             exit 1
           else
             echo "Report looks good"
           fi
        shell: bash

      - name: Docker Compose down
        if: ${{ always() }}
        working-directory: "dev"
        run: docker compose down
        shell: pwsh