[GithubAction][教學] 使用Github Action來建立測試用的Database

目的

此筆記主要是目的是要Push Code到Github的時候可以在Github Action會建立測試用的Database來做專案的Unit Test。

環境為.Net 6 , Database為MSSQL2019

程式在https://github.com/CarterTsai/github-action-container-example

P.S. 因為是測試DB所以DB密碼都會是明碼方式呈現，但是在正式使用的時候建議用Github Secrets來放密碼或是敏感資訊可以參考GitHub Action YAML 撰寫技巧 - 環境變數(Environment Variables) 與 秘密 (Secrets)

如果文章對你有幫助在幫我按一下廣告來讓我有額外收入這也是對我來說是一種鼓勵。

流程

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
7. Setup .NET Core SDK 6.0.x
5. Create Database from create_db.sql (using sqlcmd)
6. Create Table from create_table.sql (using sqlcmd)
7. restore package
8. Build solution
9. Test
10. Stop Test MSSQL Database Service

建立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:

建立github action的workflows檔案dotnet.yml

建立github action的檔案，位置在/.github/workflows/dotnet.yml

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

參考

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