[Github Action][Tutorial] Using Github Action to create a Database for testing purposes.

 The purpose of this note is to create a test database for unit tests in the project when pushing code to Github using Github Actions. The environment is .Net 6 and the database is MSSQL2019. The code is located at https://github.com/CarterTsai/github-action-container-example. Note that the password for the database will be in plain text for testing purposes, but it is recommended to use Github Secrets for passwords or sensitive information when using it in production.

The process is as follows:

1. Push code to Github

2. Trigger Github Action

3. Run test MSSQL database service from docker-compose.yaml

4. Install MSSQL command line tool

5. Setup .NET Core SDK 6.0.x

6. Create database from create_db.sql (using sqlcmd)

7. Create table from create_table.sql (using sqlcmd)

8. Restore packages

9. Build solution

10. Test

Stop test MSSQL database service.

If the article is helpful to you, please click on an advertisement to give me additional income, which is also a form of encouragement for me.

Create docker-compose.yaml

version: '3.9'

services:
  MSSQL2019Test:
    image: mcr.microsoft.com/mssql/server:latest
    restart: always
    container_name: MSSQL2019Test
    ports:
      - 1433:1433
    volumes:
      - db-test:/var/opt/mssql
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=1qaz@WSX
      - MSSQL_COLLATION=Chinese_Taiwan_Stroke_CI_AS
    networks:
      - db

volumes:
  db-test:

networks:
  db:

Create a github action workflow file dotnet.yml

Create a github action file, located at /.github/workflows/dotnet.ym

name: dotnet package

on: [push]

jobs:
  build:
    timeout-minutes: 10
    runs-on: ubuntu-latest
    
    strategy:
      matrix:
        dotnet-version: ['6.0.x' ]

    steps:
      - uses: actions/checkout@v2
      - name: Start containers
        working-directory: ./docker/
        run: docker-compose -f "docker-compose.yml" up -d --build
      
      - name: Install mssql tools 
        run: sudo apt-get install mssql-tools

      - name: Install mssql tools path 
        run: echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

      - name: Create Database
        working-directory: ./sql/
        run: sqlcmd -S localhost -U sa -P 1qaz@WSX -i create_db.sql

      - name: Create table
        working-directory: ./sql/
        run: sqlcmd -S localhost -U sa -P 1qaz@WSX -d testDB -i create_table.sql
        
      - name: Setup .NET Core SDK ${{ matrix.dotnet-version }}
        uses: actions/setup-dotnet@v1.7.2
        with:
          dotnet-version: ${{ matrix.dotnet-version }}

      - name: Install dependencies
        run: dotnet restore github-action-container-example

      - name: Build
        run: dotnet build --configuration Release --no-restore github-action-container-example
      - name: Test
        run: dotnet test --no-restore --verbosity normal github-action-container-example

      - name: Stop containers
        working-directory: ./docker/
        if: always()
        run: docker-compose -f "docker-compose.yml" down

Reference

https://docs.github.com/en/actions/creating-actions/creating-a-docker-container-action

https://docs.microsoft.com/zh-tw/sql/tools/sqlcmd-utility?view=sql-server-ver15