Run node.js database migrations on Google Cloud SQL during Google Cloud Build

Question

I would like to run database migrations written in node.js during the Cloud Build process.

Currently, the database migration command is being executed but it seems that the Cloud Build process does not have access to connect to Cloud SQL via an IP address with username/password.

Answer 1

In the case with Cloud SQL and Node.js it would look something like this:

steps:
  # Install Node.js dependencies
  - id: yarn-install
    name: gcr.io/cloud-builders/yarn
    waitFor: ["-"]

  # Install Cloud SQL proxy
  - id: proxy-install
    name: gcr.io/cloud-builders/yarn
    entrypoint: sh
    args:
      - "-c"
      - "wget https://storage.googleapis.com/cloudsql-proxy/v1.20.1/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy"
    waitFor: ["-"]

  # Migrate database schema to the latest version
  # https://knexjs.org/#Migrations-CLI
  - id: migrate
    name: gcr.io/cloud-builders/yarn
    entrypoint: sh
    args:
      - "-c"
      - "(./cloud_sql_proxy -dir=/cloudsql -instances=<CLOUD_SQL_CONNECTION> & sleep 2) && yarn run knex migrate:latest"
    timeout: "1200s"
    waitFor: ["yarn-install", "proxy-install"]

timeout: "1200s"

You would launch yarn install and download Cloud SQL Proxy in parallel. Once these two steps are complete, you run launch the proxy, wait 2 seconds and finally run yarn run knex migrate:latest.

For this to work you would need Cloud SQL Admin API enabled in your GCP project.

Where <CLOUD_SQL_INSTANCE> is your Cloud SQL instance connection name that can be found here. The same name will be used in your SQL connection settings, e.g. host=/cloudsql/example:us-central1:pg13.

Also, make sure that the Cloud Build service account has "Cloud SQL Client" role in the GCP project, where the db instance is located.