Adding catalog-update.yml #9
Workflow file for this run
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # Update the Mobility Database Schema | ||
| name: Database Update | ||
| on: | ||
| workflow_call: | ||
| inputs: | ||
| DRY_RUN: # | ||
| description: Dry run. Skip applying schema and content updates | ||
| required: false | ||
| default: true | ||
| type: boolean | ||
| PROJECT_ID: # | ||
| description: GCP Project ID (forwarded to child workflows) | ||
| required: false | ||
| type: string | ||
| REGION: # | ||
| description: GCP region (forwarded to child workflows) | ||
| required: false | ||
| type: string | ||
| DB_NAME: # | ||
| description: PostgreSQL Database Name (forwarded to child workflows) | ||
| required: false | ||
| type: string | ||
| DB_ENVIRONMENT: # | ||
| description: Environment where DB is deployed (forwarded to child workflows) | ||
| required: false | ||
| type: string | ||
| secrets: | ||
| DB_USER_PASSWORD: # | ||
| description: PostgreSQL User Password | ||
| required: true | ||
| DB_USER_NAME: # | ||
| description: PostgreSQL User Name | ||
| required: true | ||
| POSTGRE_SQL_INSTANCE_NAME: # | ||
| description: PostgreSQL Instance Name | ||
| required: true | ||
| DB_GCP_MOBILITY_FEEDS_SA_KEY: # | ||
| description: Service account key for DB environment | ||
| required: true | ||
| OP_SERVICE_ACCOUNT_TOKEN: # | ||
| description: 1Password Service Account Token | ||
| required: true | ||
| jobs: | ||
| print-event-name: | ||
| name: Print event name | ||
| runs-on: ubuntu-latest | ||
| steps: | ||
| - name: Print event and inputs | ||
| run: | | ||
| echo "github.event_name=${{ github.event_name }}" | ||
| echo "inputs.DRY_RUN=${{ inputs.DRY_RUN }}" | ||
| echo "inputs.PROJECT_ID=${{ inputs.PROJECT_ID || 'unset' }}" | ||
| echo "inputs.REGION=${{ inputs.REGION || 'unset' }}" | ||
| echo "inputs.DB_NAME=${{ inputs.DB_NAME || 'unset' }}" | ||
| echo "inputs.ENVIRONMENT=${{ inputs.ENVIRONMENT || 'unset' }}" | ||
| echo "inputs.DB_ENVIRONMENT=${{ inputs.DB_ENVIRONMENT || 'unset' }}" | ||
| db-update-schema: | ||
| db-schema-update: | ||
| name: 'Database Schema Update' | ||
| permissions: write-all | ||
| runs-on: ubuntu-latest | ||
| steps: | ||
| - name: Checkout repo | ||
| uses: actions/checkout@v4 | ||
| with: | ||
| ref: main | ||
| - name: Authenticate to Google Cloud QA/PROD | ||
| uses: google-github-actions/auth@v2 | ||
| with: | ||
| credentials_json: ${{ secrets.DB_GCP_MOBILITY_FEEDS_SA_KEY }} | ||
| - name: Google Cloud Setup | ||
| uses: google-github-actions/setup-gcloud@v2 | ||
| - name: Load secrets from 1Password | ||
| uses: 1password/[email protected] | ||
| with: | ||
| export-env: true # Export loaded secrets as environment variables | ||
| env: | ||
| OP_SERVICE_ACCOUNT_TOKEN: ${{ secrets.OP_SERVICE_ACCOUNT_TOKEN }} | ||
| GCP_FEED_SSH_USER: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_SSH_USER/username" | ||
| GCP_FEED_BASTION_NAME: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_NAME/username" | ||
| GCP_FEED_BASTION_SSH_KEY: "op://rbiv7rvkkrsdlpcrz3bmv7nmcu/GCP_FEED_BASTION_SSH_KEY/private key" | ||
| - name: Tunnel | ||
| run: | | ||
| mkdir -p ~/.ssh | ||
| echo "${{ env.GCP_FEED_BASTION_SSH_KEY }}" > ~/.ssh/id_rsa | ||
| chmod 600 ~/.ssh/id_rsa | ||
| ./scripts/tunnel-create.sh -project_id ${{ inputs.PROJECT_ID }} -zone ${{ inputs.REGION }}-a -instance ${{ env.GCP_FEED_BASTION_NAME }}-${{ inputs.DB_ENVIRONMENT}} -target_account ${{ env.GCP_FEED_SSH_USER }} -db_instance ${{ secrets.POSTGRE_SQL_INSTANCE_NAME }} | ||
| sleep 10 # Wait for the tunnel to establish | ||
| - name: Test Database Connection Through Tunnel | ||
| run: | | ||
| sudo apt-get update && sudo apt-get install -y postgresql-client | ||
| PGPASSWORD=${{ secrets.DB_USER_PASSWORD }} psql -h localhost -p 5432 -U ${{ secrets.DB_USER_NAME }} -d ${{ inputs.DB_NAME }} -c "SELECT version();" | ||
| - name: Install Liquibase | ||
| env: | ||
| LIQUIBASE_VERSION: ${{ env.liquibase_version }} | ||
| run: | | ||
| curl -sSL https://github.com/liquibase/liquibase/releases/download/v${LIQUIBASE_VERSION}/liquibase-${LIQUIBASE_VERSION}.tar.gz -o liquibase.tar.gz | ||
| rm -rf liquibase-dist | ||
| mkdir liquibase-dist | ||
| tar -xzf liquibase.tar.gz -C liquibase-dist | ||
| sudo rm -rf /usr/local/liquibase | ||
| sudo mv liquibase-dist /usr/local/liquibase | ||
| sudo ln -sf /usr/local/liquibase/liquibase /usr/local/bin/liquibase | ||
| liquibase --version | ||
| - name: Run Liquibase | ||
| if: ${{ !inputs.DRY_RUN }} | ||
| working-directory: ${{ github.workspace }}/liquibase | ||
| run: | | ||
| export LIQUIBASE_COMMAND_CHANGELOG_FILE="changelog.xml" | ||
| export LIQUIBASE_COMMAND_URL=jdbc:postgresql://localhost:5432/${{ inputs.DB_NAME }} | ||
| export LIQUIBASE_COMMAND_USERNAME=${{ secrets.DB_USER_NAME }} | ||
| export LIQUIBASE_COMMAND_PASSWORD=${{ secrets.DB_USER_PASSWORD }} | ||
| export LIQUIBASE_LOG_LEVEL=FINE | ||
| liquibase update | ||
