Recipe: CloudSQL Backup + Migration Using GitHub Actions

name: Backup and Migrate DB

on: workflow_dispatch

env:
  node-version: 12
  gcp-project-name: my-gcp-project
  gcp-cloud-sql-instance: my-cloudsql-instance

jobs:
  migrate_database:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v2
      - uses: actions/setup-node@v1
        with:
          node-version: ${{ env.node-version}}
      - name: npm install dependencies
        run: |
          npm ci
      - uses: GoogleCloudPlatform/github-actions/setup-gcloud@master
        with:
          project_id: ${{ env.gcp-project-name }}
          service_account_key: ${{ secrets.GCP_SA_KEY }}
          export_default_credentials: true
      - name: Get Cloud SQL Proxy
        run: |
          wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
          chmod +x cloud_sql_proxy
      - name: Backup staging database
        run: |
          gcloud sql backups create --instance=${{env.gcp-cloud-sql-instance}}
      - name: Run migration
        env:
          TYPEORM_CONNECTION: postgres
          TYPEORM_HOST: 127.0.0.1
          TYPEORM_PORT: 5432
          TYPEORM_USERNAME: user
          TYPEORM_DATABASE: database_name
          TYPEORM_PASSWORD: ${{ secrets.CLOUD_SQL_PASSWORD }}
          TYPEORM_MIGRATIONS: ./migration/**/*.ts
        run: |
          ./cloud_sql_proxy -instances=nice-cb-dev:us-central1:nice-db-dev=tcp:5432 &
          npm run orm:migration:run:staging