db-deployer.yml

name: Database Deployment

on:
  workflow_call:
    secrets:
      GCP_MOBILITY_FEEDS_SA_KEY:
        description: Service account key
        required: true
      DEV_GCP_MOBILITY_FEEDS_SA_KEY:
        description: Service account key for dev
        required: false
      POSTGRE_USER_NAME:
        description: PostgreSQL User Name
        required: true
      POSTGRE_USER_PASSWORD:
        description: PostgreSQL User Password
        required: true
      POSTGRE_SQL_INSTANCE_NAME:
        description: PostgreSQL Instance Name
        required: true
    inputs:
      ENVIRONMENT:
        description: Database environment. Possible values prod, staging and dev
        required: true
        type: string
      BUCKET_NAME:
        description: Bucket name where terraform state is persisted
        required: true
        type: string
      OBJECT_PREFIX:
        description: Storage object prefix where terraform state is persisted
        required: true
        type: string
      PROJECT_ID:
        description: GCP project ID
        required: true
        type: string
      REGION:
        description: GCP region
        required: true
        type: string
      DEPLOYER_SERVICE_ACCOUNT:
        description: Service account used to deploy resources
        required: true
        type: string
      POSTGRE_SQL_DB_NAME:
        description: PostgreSQL Database Name
        required: true
        type: string
      TF_APPLY:
        description: Terraform apply changes
        required: true
        type: boolean
      POSTGRE_INSTANCE_TIER:
        description: PostgreSQL Database instance tier
        required: true
        type: string
      MAX_CONNECTIONS:
        description: PostgreSQL maximum number of connections to db
        required: true
        type: string

jobs:
  terraform:
    name: 'Deploy Database'
    permissions: write-all
    runs-on: ubuntu-latest
    outputs:
      db_instance_host: ${{ steps.get_outputs.outputs.db_instance_host }}
    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Authenticate to Google Cloud
        id: gcloud_auth
        uses: google-github-actions/auth@v2
        with:
          credentials_json: ${{ secrets.GCP_MOBILITY_FEEDS_SA_KEY }}

      - name: Google Cloud Setup
        uses: google-github-actions/setup-gcloud@v2

      - name: Set Variables
        run: |
          echo "Setting variables"
          echo "ENVIRONMENT=${{ inputs.ENVIRONMENT }}" >> $GITHUB_ENV
          echo "BUCKET_NAME=${{ inputs.BUCKET_NAME }}" >> $GITHUB_ENV
          echo "OBJECT_PREFIX=${{ inputs.OBJECT_PREFIX }}" >> $GITHUB_ENV
          echo "PROJECT_ID=${{ inputs.PROJECT_ID }}" >> $GITHUB_ENV
          echo "REGION=${{ inputs.REGION }}" >> $GITHUB_ENV
          echo "DEPLOYER_SERVICE_ACCOUNT=${{ inputs.DEPLOYER_SERVICE_ACCOUNT }}" >> $GITHUB_ENV
          echo "POSTGRE_SQL_INSTANCE_NAME=${{ secrets.POSTGRE_SQL_INSTANCE_NAME }}" >> $GITHUB_ENV
          echo "POSTGRE_SQL_DB_NAME=${{ inputs.POSTGRE_SQL_DB_NAME }}" >> $GITHUB_ENV
          echo "POSTGRE_USER_NAME=${{ secrets.POSTGRE_USER_NAME }}" >> $GITHUB_ENV
          echo "POSTGRE_USER_PASSWORD=${{ secrets.POSTGRE_USER_PASSWORD }}" >> $GITHUB_ENV
          echo "POSTGRE_INSTANCE_TIER=${{ inputs.POSTGRE_INSTANCE_TIER }}" >> $GITHUB_ENV
          echo "MAX_CONNECTIONS=${{ inputs.MAX_CONNECTIONS }}" >> $GITHUB_ENV

      - name: Populate Variables
        run: |
          scripts/replace-variables.sh -in_file infra/backend.conf.rename_me -out_file infra/postgresql/backend.conf -variables BUCKET_NAME,OBJECT_PREFIX
          scripts/replace-variables.sh -in_file infra/postgresql/vars.tfvars.rename_me -out_file infra/postgresql/vars.tfvars -variables ENVIRONMENT,PROJECT_ID,REGION,DEPLOYER_SERVICE_ACCOUNT,POSTGRE_SQL_INSTANCE_NAME,POSTGRE_SQL_DB_NAME,POSTGRE_USER_NAME,POSTGRE_USER_PASSWORD,POSTGRE_INSTANCE_TIER,MAX_CONNECTIONS

      - name: Install Terraform
        uses: hashicorp/setup-terraform@v3
        with:
          terraform_version: 1.5.3
          terraform_wrapper: false

      - name: Terraform Init
        run: |
          cd infra/postgresql
          terraform init -backend-config=backend.conf
        env:
          GOOGLE_CREDENTIALS: ${{ secrets.GCP_SA_KEY }}
          PROJECT_ID: ${{ secrets.GCP_PROJECT_ID }}

      - name: Terraform Plan
        run: |
          cd infra/postgresql
          terraform plan -var-file=vars.tfvars -out=tf.plan
          terraform show -no-color tf.plan > terraform-plan.txt
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
          PLAN_OUTPUT: ${{ steps.plan.outputs.stdout }}

      - name: Terraform Apply
        if: ${{ inputs.TF_APPLY }}
        run: |
          cd infra/postgresql
          terraform apply -auto-approve tf.plan
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
          PLAN_OUTPUT: ${{ steps.plan.outputs.stdout }}

      - name: Get Terraform Outputs
        id: get_outputs
        run: |
          DB_INSTANCE_HOST_RAW=$(terraform output -json instance_address)
          DB_INSTANCE_HOST=$(echo $DB_INSTANCE_HOST_RAW | jq -r .)
          echo "db_instance_host=$DB_INSTANCE_HOST" >> $GITHUB_OUTPUT
        working-directory: infra/postgresql

  update-secret-in-dev:
    name: Copy DB Secret
    needs: terraform
    if: ${{ inputs.ENVIRONMENT == 'qa' }}
    runs-on: ubuntu-latest
    env:
      POSTGRE_USER_NAME: ${{ secrets.POSTGRE_USER_NAME }}
      POSTGRE_USER_PASSWORD: ${{ secrets.POSTGRE_USER_PASSWORD }}
      POSTGRE_SQL_DB_NAME: ${{ inputs.POSTGRE_SQL_DB_NAME }}
      DB_INSTANCE_HOST: ${{ needs.terraform.outputs.db_instance_host }}
    steps:
      - name: Authenticate to Google Cloud DEV
        uses: google-github-actions/auth@v2
        with:
          credentials_json: ${{ secrets.DEV_GCP_MOBILITY_FEEDS_SA_KEY }}

      - name: Google Cloud Setup DEV
        uses: google-github-actions/setup-gcloud@v2

      - name: Create or Update Secret in DEV
        run: |
          SECRET_NAME="DEV_FEEDS_DATABASE_URL"
          SECRET_VALUE="postgresql://${{ env.POSTGRE_USER_NAME }}:${{ env.POSTGRE_USER_PASSWORD }}@${{ env.DB_INSTANCE_HOST }}/${{ env.POSTGRE_SQL_DB_NAME }}DEV"
          echo $SECRET_VALUE
          
          if gcloud secrets describe $SECRET_NAME --project=mobility-feeds-dev; then
            echo "Secret $SECRET_NAME already exists, updating..."
            echo -n "$SECRET_VALUE" | gcloud secrets versions add $SECRET_NAME --data-file=- --project=mobility-feeds-dev
          else
            echo "Secret $SECRET_NAME does not exist, creating..."
            echo -n "$SECRET_VALUE" | gcloud secrets create $SECRET_NAME --data-file=- --replication-policy="automatic" --project=mobility-feeds-dev
          fi