Performing validation with a local docker instance instead of Azure DB #488
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 2025 Express installer (Windows) | |
| if: runner.os == 'Windows' | |
| shell: pwsh | |
| run: | | |
| $ssei = Join-Path $env:RUNNER_TEMP 'SQL2025-SSEI-Expr.exe' | |
| $media = Join-Path $env:RUNNER_TEMP 'sql-media' | |
| # Stable Microsoft fwlink redirecting to the SQL Server 2025 Express bootstrapper (SSEI) | |
| Invoke-WebRequest -Uri 'https://go.microsoft.com/fwlink/?linkid=2216019' -OutFile $ssei | |
| Write-Host "Downloaded SSEI: $((Get-Item $ssei).Length) bytes" | |
| # Run SSEI in download-only mode. Use Start-Process -Wait so we get a real exit code | |
| # (PowerShell's `&` returns asynchronously for installer-class executables). | |
| $p = Start-Process -FilePath $ssei -Wait -PassThru -ArgumentList @( | |
| '/Quiet', | |
| '/Action=Download', | |
| '/Language=en-US', | |
| "/MediaPath=$media", | |
| '/MediaType=Core', | |
| '/HideProgressBar' | |
| ) | |
| if ($p.ExitCode -ne 0) { throw "SSEI download failed with exit code $($p.ExitCode)" } | |
| Write-Host "=== Media directory contents ===" | |
| 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' | |
| $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" | |
| $extract = Start-Process -FilePath $selfExtract.FullName -Wait -PassThru ` | |
| -ArgumentList '/Q', "/X:$extracted" | |
| if ($extract.ExitCode -ne 0) { throw "Extraction failed with exit code $($extract.ExitCode)" } | |
| $setup = Join-Path $extracted 'setup.exe' | |
| if (-not (Test-Path $setup)) { throw "setup.exe not found at $setup" } | |
| Write-Host "Running $setup with SECURITYMODE=SQL" | |
| $install = Start-Process -FilePath $setup -Wait -PassThru -ArgumentList @( | |
| '/Q', | |
| '/ACTION=Install', | |
| '/FEATURES=SQLEngine', | |
| '/INSTANCENAME=MSSQLSERVER', | |
| '/SECURITYMODE=SQL', | |
| "/SAPWD=$env:SA_PASSWORD", | |
| '/TCPENABLED=1', | |
| '/IACCEPTSQLSERVERLICENSETERMS', | |
| '/UPDATEENABLED=False', | |
| '/SQLSYSADMINACCOUNTS=BUILTIN\Administrators' | |
| ) | |
| if ($install.ExitCode -ne 0) { | |
| Write-Host "Setup failed with exit code $($install.ExitCode)" | |
| $logRoot = 'C:\Program Files\Microsoft SQL Server' | |
| if (Test-Path $logRoot) { | |
| Get-ChildItem $logRoot -Recurse -Filter 'Summary*.txt' -ErrorAction SilentlyContinue | | |
| 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' -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;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 |