Run Integration tests with Real DB on Github Workflow

Github Service Containers

GitHub Service Containers allow you to run services, such as databases, within your GitHub Actions workflows. This is particularly useful for integration testing, where you need a real database to test against. By using containers, you can ensure that your tests run in a consistent environment every time.

Refer

Create a Github workflow to run tests

Following workflow runs for all the PRs to main and all the commits happens on the main.

Creates a Service inside Github workflow which will be available for application tests.

To run the tests, you need to ensure that your integration tests are configured to connect to the SQL Server instance provided by the service container. The connection string in your tests should point to localhost with the appropriate credentials.

Here is an example of how you might configure your connection string in your test project:

var connectionString = "Server=localhost,1433;Database=YourDatabase;User Id=sa;Password=SqlServer@123$;";

Make sure to replace YourDatabase with the actual name of your database. The User Id and Password should match the credentials specified in the workflow file.

Update your integration tests to create DB and schema before running tests.

With this setup, your integration tests will run against a real SQL Server instance within the GitHub Actions environment, ensuring that your tests are executed in a consistent and isolated environment.

name: Run Integration Tests

on:
    push:
        branches:
            - main
    pull_request:
        branches:
            - main

jobs:
    build:
        runs-on: ubuntu-latest
        services:
            sqlserver:
                image: mcr.microsoft.com/mssql/server:2019-latest
                env:
                    SA_PASSWORD: "SqlServer@123$" #update password as needed
                    ACCEPT_EULA: "Y"
                    MSSQL_PID: Express
                ports:
                    - 1433:1433
    steps:
        - uses: actions/checkout@v4

        - uses: actions/setup-dotnet@v4
            with:
              dotnet-version: |
                9.0.x

        - name: Build  
            run: dotnet build --no-restore

        - name: Run tests
            run: dotnet test --no-build --verbosity normal --collect "XPlat Code Coverage" --results-directory coverage-results