deploy-database.yml

name: Deploy Database

on:
  push:
    branches: [main, release]
  workflow_dispatch:

permissions:
  id-token: write
  contents: read

jobs:
  deploy:
    runs-on: ubuntu-20.04
    strategy:
      fail-fast: false
      matrix:
        target:
        - name: "dev"
          ref: "refs/heads/main"
          client-id: "a23b30b8-3faa-42a1-817e-6cd097a620f1"
          server: "sql-radix-vulnerability-scan-dev.database.windows.net"

        - name: "playground"
          ref: "refs/heads/release"
          client-id: "453fb3de-daaa-43e6-93ef-a470849a2b6b"
          server: "sql-radix-vulnerability-scan-playground.database.windows.net"

        - name: "platform"
          ref: "refs/heads/release"
          client-id: "50e78187-ff60-49c5-bcbe-701bbbf4f5d7"
          server: "sql-radix-vulnerability-scan-prod.database.windows.net"

        - name: "c2"
          ref: "refs/heads/release"
          client-id: "f26240e2-3095-4273-8559-2ca9a121991e"
          server: "sql-radix-vulnerability-scan-c2.database.windows.net"

    env:
      connection: >-
        Server=${{matrix.target.server}};
        Initial Catalog=radix-vulnerability-scan;
        Authentication=Active Directory Default; 
        Encrypt=True;
        TrustServerCertificate=False;
        Connection Timeout=30;

    steps:
      - uses: actions/checkout@v4
        if: matrix.target.ref == github.ref

      - uses: azure/login@v1
        if: matrix.target.ref == github.ref
        with:
          client-id: ${{matrix.target.client-id}}
          tenant-id: "3aa4a235-b6e2-48d5-9195-7fcf05b459b0"
          allow-no-subscriptions: true

      - uses: azure/sql-action@v2.2.1
        if: matrix.target.ref == github.ref
        with:
          connection-string: ${{env.connection}}
          path: './azure-infrastructure/sql-scripts/predeploy.sql'

      - uses: azure/sql-action@v2.2.1
        if: matrix.target.ref == github.ref
        with:
          connection-string: ${{env.connection}}
          path: './azure-infrastructure/sql-scripts/roles.sql'

      - uses: azure/sql-action@v2.2.1
        if: matrix.target.ref == github.ref
        with:
          connection-string: ${{env.connection}}
          path: './azure-infrastructure/sql-scripts/types.sql'

      - uses: azure/sql-action@v2.2.1
        if: matrix.target.ref == github.ref
        with:
          connection-string: ${{env.connection}}
          path: './azure-infrastructure/sql-scripts/tables.sql'

      - uses: azure/sql-action@v2.2.1
        if: matrix.target.ref == github.ref
        with:
          connection-string: ${{env.connection}}
          path: './azure-infrastructure/sql-scripts/views.sql'

      - uses: azure/sql-action@v2.2.1
        if: matrix.target.ref == github.ref
        with:
          connection-string: ${{env.connection}}
          path: './azure-infrastructure/sql-scripts/procedures.sql'