Google Cloud Build, Cloud Run, Cloud SQL Prisma Migration

Question

I am trying to get a Google Cloud Build pipeline running with a Node.js application that is using Google Cloud Build, Cloud SQL (PostgreSQL) and Prisma for the ORM. I have started with the default yaml provided by GCP Cloud Build when clicking on the Setup Continuous Integration button on the Cloud Run UI view for an existing application. The part that is missing is the prisma migrations for the Cloud SQL instance.

steps:
  - name: gcr.io/cloud-builders/docker
    args:
      - build
      - '--no-cache'
      - '-t'
      - '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
      - .
      - '-f'
      - api/Dockerfile
    id: Build
  - name: gcr.io/cloud-builders/docker
    args:
      - push
      - '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
    id: Push
  - name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim'
    args:
      - run
      - services
      - update
      - $_SERVICE_NAME
      - '--platform=managed'
      - '--image=$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
      - >-
        --labels=managed-by=gcp-cloud-build-deploy-cloud-run,commit-sha=$COMMIT_SHA,gcb-build-id=$BUILD_ID,gcb-trigger-id=$_TRIGGER_ID,$_LABELS
      - '--region=$_DEPLOY_REGION'
      - '--quiet'
    id: Deploy
    entrypoint: gcloud
images:
  - '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
options:
  substitutionOption: ALLOW_LOOSE
tags:
  - gcp-cloud-build-deploy-cloud-run
  - gcp-cloud-build-deploy-cloud-run-managed
  - api

Answer 1

I solved the issue by using the following Cloud Build yaml. This is hosted within my git repo, so any code changes are tracked. I selected the Repository as the source and added the location of the cloudbuild.yaml file in my repo rather than the inline option in the Google Cloud Build trigger. This solution should work as long as there aren't any breaking changes from the previous API version to the new version (this won't work for a short period of time until the new application code has all the traffic running to it if you, for example, rename a field in the database that the old application code relies on). The way to manage this is not to make breaking changes and migrate the data from the old column to the new column before removing the old column. Another option is to schedule downtime for DB maintenance.

Keep in mine that there is a race condition when the database migrations run, but the previous version of the code is still accepting traffic before the cut over and that people using the application will potentially receive 500 errors.

This is the updated cloudbuild.yaml with the Prisma migration step (note: This also uses Google Cloud Secret Manager for the DB):

steps:
  - name: 'node:$_NODE_VERSION'
    entrypoint: 'yarn'
    id: yarn-install
    args: ['install']
    waitFor: ["-"]

  - id: migrate
    name: gcr.io/cloud-builders/yarn
    env:
      - NODE_ENV=$_NODE_ENV
    entrypoint: sh
    args:
      - "-c"
      - |
        wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
        chmod +x cloud_sql_proxy
        ./cloud_sql_proxy -instances=$$_DB_HOST=tcp:$$_DB_PORT & sleep 3
        export DATABASE_URL=postgresql://$$_DB_USER:$$_DB_PASS@localhost/$$_DB_NAME?schema=public
        yarn workspace api run migrate
    secretEnv: ['_DB_USER', '_DB_PASS',  '_DB_HOST', '_DB_NAME', '_DB_PORT']
    timeout: "1200s"
    waitFor: ["yarn-install"]

  - name: gcr.io/cloud-builders/docker
    args:
      - build
      - '--no-cache'
      - '-t'
      - '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
      - .
      - '-f'
      - api/Dockerfile
    id: Build
  - name: gcr.io/cloud-builders/docker
    args:
      - push
      - '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
    id: Push
  - name: 'gcr.io/google.com/cloudsdktool/cloud-sdk:slim'
    args:
      - run
      - services
      - update
      - $_SERVICE_NAME
      - '--platform=managed'
      - '--image=$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
      - >-
        --labels=managed-by=gcp-cloud-build-deploy-cloud-run,commit-sha=$COMMIT_SHA,gcb-build-id=$BUILD_ID,gcb-trigger-id=$_TRIGGER_ID,$_LABELS
      - '--region=$_DEPLOY_REGION'
      - '--quiet'
    id: Deploy
    entrypoint: gcloud
images:
  - '$_GCR_HOSTNAME/$PROJECT_ID/$REPO_NAME/$_SERVICE_NAME:$COMMIT_SHA'
options:
  substitutionOption: ALLOW_LOOSE
availableSecrets:
  secretManager:
  - versionName: projects/$PROJECT_ID/secrets/DB_NAME/versions/latest
    env: '_DB_NAME'
  - versionName: projects/$PROJECT_ID/secrets/DB_PASS/versions/latest
    env: '_DB_PASS'
  - versionName: projects/$PROJECT_ID/secrets/DB_PORT/versions/latest
    env: '_DB_PORT'
  - versionName: projects/$PROJECT_ID/secrets/DB_USER/versions/latest
    env: '_DB_USER'
  - versionName: projects/$PROJECT_ID/secrets/DB_HOST/versions/latest
    env: '_DB_HOST'

tags:
  - gcp-cloud-build-deploy-cloud-run
  - gcp-cloud-build-deploy-cloud-run-managed
  - api