Performing validation with a local docker instance instead of Azure DB #481
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 Express with SQL auth --- | |
| - 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: Install SQL Server Express (Windows) | |
| if: runner.os == 'Windows' | |
| shell: pwsh | |
| run: | | |
| choco install sql-server-express -y --no-progress ` | |
| --params "/SECURITYMODE=SQL /SAPWD=$env:SA_PASSWORD /TCPENABLED=1" | |
| # SQL Server Browser helps named-instance resolution; not strictly required when using a port | |
| Set-Service -Name SQLBrowser -StartupType Manual -ErrorAction SilentlyContinue | |
| Start-Service -Name SQLBrowser -ErrorAction SilentlyContinue | |
| # Confirm the engine service is running | |
| Get-Service | Where-Object { $_.Name -like 'MSSQL*' } | Format-Table | |
| - 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 |