Performing validation with a local docker instance instead of Azure DB #484
Workflow file for this run
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: pr-check | |
| # Tests PR code against a local SQL Server instance so no Azure credentials are required. | |
| # This workflow uses the pull_request trigger (not pull_request_target), so fork PRs run | |
| # with no secrets and no elevated permissions. | |
| # | |
| # - Linux runners: spin up SQL Server 2022 in a Docker container with SA auth. | |
| # - Windows runners: use the pre-installed SQL Server Express with Integrated Security. | |
| on: | |
| pull_request: | |
| permissions: {} | |
| jobs: | |
| test: | |
| strategy: | |
| fail-fast: false | |
| matrix: | |
| os: [ubuntu-latest, windows-latest] | |
| runs-on: ${{ matrix.os }} | |
| permissions: | |
| contents: read | |
| checks: write | |
| env: | |
| TEST_DB: SqlActionTest | |
| MSSQL_IMAGE: mcr.microsoft.com/mssql/server:2022-latest | |
| defaults: | |
| run: | |
| shell: bash | |
| steps: | |
| - name: Checkout PR | |
| uses: actions/checkout@11bd71901bbe5b1630ceea73d27597364c9af683 # v4.2.2 | |
| with: | |
| ref: ${{ github.event.pull_request.head.sha }} | |
| # --- Linux setup: Docker container + SA auth with rotated password --- | |
| - name: Start SQL Server container (Linux) | |
| if: runner.os == 'Linux' | |
| run: | | |
| docker run -d --name sqlserver \ | |
| -e "ACCEPT_EULA=Y" \ | |
| -e "MSSQL_SA_PASSWORD=Bootstrap1!" \ | |
| -p 1433:1433 \ | |
| "$MSSQL_IMAGE" | |
| - name: Wait for SQL Server to be ready (Linux) | |
| if: runner.os == 'Linux' | |
| run: | | |
| for i in $(seq 1 30); do | |
| if docker exec sqlserver /opt/mssql-tools18/bin/sqlcmd \ | |
| -S localhost -U sa -P 'Bootstrap1!' -C -Q 'SELECT 1' >/dev/null 2>&1; then | |
| echo "SQL Server is ready" | |
| exit 0 | |
| fi | |
| echo "Waiting for SQL Server... ($i/30)" | |
| sleep 5 | |
| done | |
| echo "SQL Server did not become ready in time" | |
| docker logs sqlserver | |
| exit 1 | |
| - name: Rotate SA password and set connection string (Linux) | |
| if: runner.os == 'Linux' | |
| run: | | |
| SA_PASSWORD="$(openssl rand -base64 18 | tr -d '/+=')Aa1!" | |
| docker exec sqlserver /opt/mssql-tools18/bin/sqlcmd \ | |
| -S localhost -U sa -P 'Bootstrap1!' -C \ | |
| -Q "ALTER LOGIN sa WITH PASSWORD='${SA_PASSWORD}'" | |
| echo "BASE_CS=Server=localhost;User ID=sa;Password=${SA_PASSWORD};TrustServerCertificate=True;" >> "$GITHUB_ENV" | |
| # --- Windows setup: install SQL Server 2022 Express directly from Microsoft --- | |
| - name: Generate SA password (Windows) | |
| if: runner.os == 'Windows' | |
| run: | | |
| SA_PASSWORD="$(openssl rand -base64 18 | tr -d '/+=')Aa1!" | |
| echo "::add-mask::${SA_PASSWORD}" | |
| echo "SA_PASSWORD=${SA_PASSWORD}" >> "$GITHUB_ENV" | |
| - name: Download SQL Server 2022 Express installer (Windows) | |
| if: runner.os == 'Windows' | |
| shell: pwsh | |
| run: | | |
| $ssei = Join-Path $env:RUNNER_TEMP 'SQL2022-SSEI-Expr.exe' | |
| $media = Join-Path $env:RUNNER_TEMP 'sql-media' | |
| # Stable Microsoft fwlink for the SQL Server 2022 Express bootstrapper (SSEI) | |
| Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=2216019' -OutFile $ssei | |
| # Tell the bootstrapper to download (not install) the full installer package | |
| & $ssei /Quiet /Action=Download /Language=en-US /MediaPath=$media /MediaType=Core /HideProgressBar | |
| if ($LASTEXITCODE -ne 0) { throw "SSEI download failed with exit code $LASTEXITCODE" } | |
| Get-ChildItem $media | |
| - name: Install SQL Server Express with SA auth (Windows) | |
| if: runner.os == 'Windows' | |
| shell: pwsh | |
| run: | | |
| $media = Join-Path $env:RUNNER_TEMP 'sql-media' | |
| # SSEI produces a self-extracting installer (SQLEXPR*.exe). Extract its contents first. | |
| $selfExtract = Get-ChildItem $media -Filter 'SQLEXPR*.exe' | Select-Object -First 1 | |
| if (-not $selfExtract) { throw "Installer EXE not found in $media" } | |
| $extracted = Join-Path $env:RUNNER_TEMP 'sql-extracted' | |
| Write-Host "Extracting $($selfExtract.FullName) -> $extracted" | |
| & $selfExtract.FullName /Q /X:"$extracted" | Out-Null | |
| # Self-extraction is async; wait for setup.exe to appear | |
| $deadline = (Get-Date).AddMinutes(2) | |
| while (-not (Test-Path (Join-Path $extracted 'setup.exe'))) { | |
| if ((Get-Date) -gt $deadline) { throw "Extraction did not complete in time" } | |
| Start-Sleep -Seconds 2 | |
| } | |
| $setup = Join-Path $extracted 'setup.exe' | |
| Write-Host "Running $setup with SECURITYMODE=SQL" | |
| & $setup ` | |
| /Q ` | |
| /ACTION=Install ` | |
| /FEATURES=SQLEngine ` | |
| /INSTANCENAME=SQLEXPRESS ` | |
| /SECURITYMODE=SQL ` | |
| /SAPWD="$env:SA_PASSWORD" ` | |
| /TCPENABLED=1 ` | |
| /IACCEPTSQLSERVERLICENSETERMS ` | |
| /UPDATEENABLED=False ` | |
| /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" | |
| if ($LASTEXITCODE -ne 0) { | |
| Write-Host "Setup failed with exit code $LASTEXITCODE" | |
| # Surface the most recent setup logs to make failures diagnosable | |
| $logDir = 'C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log' | |
| if (Test-Path $logDir) { | |
| Get-ChildItem $logDir -Recurse -Filter 'Summary*.txt' | | |
| Sort-Object LastWriteTime -Descending | Select-Object -First 1 | | |
| ForEach-Object { Write-Host "=== $($_.FullName) ==="; Get-Content $_.FullName } | |
| } | |
| throw "SQL Server install failed" | |
| } | |
| Get-Service | Where-Object { $_.Name -like 'MSSQL*' } | Format-Table | |
| - name: Verify SQL auth (Windows) | |
| if: runner.os == 'Windows' | |
| shell: pwsh | |
| run: | | |
| $sqlcmd = (Get-ChildItem 'C:\Program Files\Microsoft SQL Server' -Recurse -Filter sqlcmd.exe -ErrorAction SilentlyContinue | | |
| Select-Object -First 1).FullName | |
| if (-not $sqlcmd) { throw "sqlcmd.exe not found after install" } | |
| & $sqlcmd -S 'localhost\SQLEXPRESS' -U sa -P $env:SA_PASSWORD -b -Q "SELECT @@VERSION" | |
| if ($LASTEXITCODE -ne 0) { throw "SQL auth verification failed" } | |
| - name: Set connection string (Windows) | |
| if: runner.os == 'Windows' | |
| run: | | |
| echo "BASE_CS=Server=localhost\SQLEXPRESS;User ID=sa;Password=${SA_PASSWORD};TrustServerCertificate=True;" >> "$GITHUB_ENV" | |
| # --- Common build and test steps --- | |
| - name: Build GitHub Action | |
| run: npm ci --ignore-scripts && npm run build | |
| - name: Setup .NET | |
| uses: actions/setup-dotnet@v4 | |
| with: | |
| dotnet-version: '8.x' | |
| - name: Install SqlPackage | |
| run: dotnet tool install -g microsoft.sqlpackage | |
| # Deploy a DACPAC with only a table to server (sqlpackage creates the DB if needed) | |
| - name: Test DACPAC Action | |
| uses: ./ | |
| with: | |
| connection-string: '${{ env.BASE_CS }}Initial Catalog=${{ env.TEST_DB }};' | |
| path: ./__testdata__/sql-action.dacpac | |
| action: 'publish' | |
| skip-firewall-check: true | |
| # Build and publish sqlproj that should create a new view | |
| - name: Test Build and Publish | |
| uses: ./ | |
| with: | |
| connection-string: '${{ env.BASE_CS }}Initial Catalog=${{ env.TEST_DB }};' | |
| path: ./__testdata__/TestProject/sql-action.sqlproj | |
| action: 'publish' | |
| skip-firewall-check: true | |
| # Execute testsql.sql via script action on server | |
| - name: Test SQL Action | |
| uses: ./ | |
| with: | |
| connection-string: '${{ env.BASE_CS }}Initial Catalog=${{ env.TEST_DB }};' | |
| path: ./__testdata__/testsql.sql | |
| skip-firewall-check: true | |
| - name: Cleanup Test Database | |
| if: always() | |
| uses: ./ | |
| with: | |
| connection-string: '${{ env.BASE_CS }}Initial Catalog=master;' | |
| path: ./__testdata__/cleanup.sql | |
| arguments: '-v DbName="${{ env.TEST_DB }}"' | |
| skip-firewall-check: true | |
| - name: Stop SQL Server container (Linux) | |
| if: always() && runner.os == 'Linux' | |
| run: docker rm -f sqlserver || true |