Performing validation with a local docker instance instead of Azure DB #479
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 SqlLocalDB via Chocolatey, Integrated Security --- | |
| - name: Install SqlLocalDB (Windows) | |
| if: runner.os == 'Windows' | |
| shell: pwsh | |
| run: | | |
| choco install sqllocaldb -y --no-progress | |
| # Refresh PATH so SqlLocalDB.exe is reachable | |
| $env:Path = [System.Environment]::GetEnvironmentVariable('Path','Machine') + ';' + | |
| [System.Environment]::GetEnvironmentVariable('Path','User') | |
| SqlLocalDB.exe info | |
| SqlLocalDB.exe start MSSQLLocalDB | |
| SqlLocalDB.exe info MSSQLLocalDB | |
| - name: Set connection string (Windows) | |
| if: runner.os == 'Windows' | |
| run: | | |
| # LocalDB uses a named-pipe instance; Integrated Security authenticates as the runner's local user | |
| echo 'BASE_CS=Server=(localdb)\MSSQLLocalDB;Integrated Security=True;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 |