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.

Basile

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: node:18
    entrypoint: yarn
    args: ["install", "--frozen-lockfile"]
    waitFor: ["-"]

  # Install Cloud SQL proxy
  - id: proxy-install
    name: node:18
    entrypoint: sh
    args:
      - "-c"
      - "wget https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.4.0/cloud-sql-proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy"
    waitFor: ["-"]

  # Migrate database schema to the latest version
  - id: migrate
    name: node:18
    entrypoint: sh
    args:
      - "-c"
      - "mkdir /cloudsql; chmod 777 /cloudsql; (./cloud_sql_proxy --unix-socket /cloudsql <CLOUD_SQL_CONNECTION> & sleep 2) && yarn 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 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.

Israelitish

That looks perfect, cheers mate. I'll give it a go tonight and will let you know how it goes. – Basile

Works well! The trick with this for those that didn't read Konstantin's summary is that the mounting of the Google Cloud SQL proxy and migration happens in an asynchronous fashion as once you launch the Cloud SQL proxy it stays open. One thing to mention though, remember to add the Google Cloud SQL client IAM permission to the Google Cloud Build IAM service account. – Basile

Did you run into an issue with it not connecting to localhost? I'm getting an issue where it looks like the cloud sql proxy successfully connects, and it even says "Accepting connections on 127.0.0.1:5432", but when I try to connect, it says "ECONNREFUSED 127.0.0.1:5432". This works perfectly on my local machine. They're both part of the same volume. – Crossly

Not enough upvotes on SO! Note that for similar use cases like running django tests, you may need more than just a Cloud SQL Client role since tests can involve creating DBs. – Tungusic

Doesn't work with latest image gcr.io/cloudsql-docker/gce-proxy v1.16, due to switching to distroless image without shell. More info in official issue github.com/GoogleCloudPlatform/cloudsql-proxy/issues/317. For now you can use v1.15 or alternative approach: github.com/GoogleCloudPlatform/cloudsql-proxy/issues/… – Mime

The documentation provides a direct non-versioned URL to download which might be worth using instead: cloud.google.com/sql/docs/mysql/sql-proxy#install – Clino

This worked for me, but had some gcloud error which was resolved by bumping Cloud SQL Proxy to v1.20.2 – Selectivity

timeout at end gives me syntax errors – Doha

Getting an error that gcloud is unavailable even with the newest proxy version. – Chatelain

Answer 2

As of tag 1.16 of gcr.io/cloudsql-docker/gce-proxy, the currently accepted answer no longer works. Here is a different approach that keeps the proxy in the same step as the commands that need it:

  - id: cmd-with-proxy
    name: [YOUR-CONTAINER-HERE]
    timeout: 100s
    entrypoint: sh
    args:
      - -c
      - '(/workspace/cloud_sql_proxy -dir=/workspace -instances=[INSTANCE_CONNECTION_NAME] & sleep 2) && [YOUR-COMMAND-HERE]'

The proxy will automatically exit once the main process exits. Additionally, it'll mark the step as "ERROR" if either the proxy or the command given fails.

This does require the binary is in the /workspace volume, but this can be provided either manually or via a prereq step like this:

  - id: proxy-install
    name: alpine:3.10
    entrypoint: sh
    args:
      - -c
      - 'wget -O /workspace/cloud_sql_proxy https://storage.googleapis.com/cloudsql-proxy/v1.16/cloud_sql_proxy.linux.386 &&  chmod +x /workspace/cloud_sql_proxy'

Additionally, this should work with TCP since the proxy will be in the same container as the command.

Pharyngoscope

Cloud SQL Proxy v2 changes the arguments so that this example would instead be '(/workspace/cloud_sql_proxy INSTANCE_CONNECTION_NAME & sleep 2) && [YOUR-COMMAND-HERE]', otherwise the steps remain the same and work as described. – Avernus

Answer 3

Use google-appengine/exec-wrapper. It is an image to do exactly this. Usage (see README in link):

steps:
- name: "gcr.io/google-appengine/exec-wrapper"
  args: ["-i", "gcr.io/my-project/appengine/some-long-name",
         "-e", "ENV_VARIABLE_1=value1", "-e", "ENV_2=value2",
         "-s", "my-project:us-central1:my_cloudsql_instance",
         "--", "bundle", "exec", "rake", "db:migrate"]

The -s sets the proxy target.

Pleasurable

If you are running a script inside the inner container specified in "-i" argument. What would the host be for the nested inner container. 127.0.0.1 does not seem to work. I am attempting to run a flyway container within the exec-wrapper container using this approach. – Khaki

Answer 4

Cloud Build runs using a service account and it looks like you need to grant access to Cloud SQL for this account. You can find additional info about setting service account permissions here.

Wrangler

I tried something along the lines of this, adding the Cloud SQL role to the Cloud Build IAM. Didn't have much luck. – Basile

Did you try to run build locally? cloud.google.com/cloud-build/docs/build-debug-locally. It runs under your account and if it will work correctly - problems with permissions, if not - problem somewhere else. – Wrangler

That is not a bad idea. Ill give that a go shortly, an easier way to debug for sure. – Basile

Answer 5

Here's how to combine Cloud Build + Cloud SQL Proxy + Docker.

If you're running your database migrations/operations within a Docker container in Cloud Build, it won't be able to directly access your proxy, because Docker containers are isolated from the host machine.

Here's what I managed to get up and running:

  - id: build
    # Build your application
    waitFor: ['-']

  - id: install-proxy
    name: gcr.io/cloud-builders/wget
    entrypoint: bash
    args:
      - -c
      - wget -O /workspace/cloud_sql_proxy https://storage.googleapis.com/cloudsql-proxy/v1.15/cloud_sql_proxy.linux.386 && chmod +x /workspace/cloud_sql_proxy
    waitFor: ['-']

  - id: migrate
    name: gcr.io/cloud-builders/docker
    entrypoint: bash
    args:
      - -c
      - |
        /workspace/cloud_sql_proxy -dir=/workspace -instances=projectid:region:instanceid & sleep 2 && \
        docker run -v /workspace:/root \
        --env DATABASE_HOST=/root/projectid:region:instanceid \
        # Pass other necessary env variables like db username/password, etc.
        $_IMAGE_URL:$COMMIT_SHA
    timeout: '1200s'
    waitFor: [build, install-proxy]

Because our db operations are taking place within the Docker container, I found the best way to provide the access to Cloud SQL by specifying the Unix socket -dir/workspace instead of exposing a TCP port 5432.

Note: I recommend using the directory /workspace instead of /cloudsql for Cloud Build.

Then we mounted the /workspace directory to Docker container's /root directory, which is the default directory where your application code resides. When I tried to mount it to other than /root, nothing seemed to happen (perhaps a permission issue with no error output).

Also: I noticed the proxy version 1.15 works well. I had issues with newer versions. Your mileage may vary.

Smoker

I spent some days trying to make this work... As you say, when copied to root it magically works - otherwise, it doesn't. Thanks for your tip ... I wish I knew why that is. – Pantsuit

Answer 6

here is my updated solution for 2024, based on Konstantin's one :

- id: install-proxy
name: 'gcr.io/cloud-builders/wget'
entrypoint: bash
args:
  - "-c"
  - "wget https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.1/cloud-sql-proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy"

- id: migrate
    waitFor: ['install-proxy']
    name: '<YOUR IMAGE>'
    entrypoint: sh
    args:
      - "-c"
      - "(./cloud_sql_proxy --address <DB_HOST> --port <DB_PORT> <PROJECT_ID>:<REGION>:<DB_INSTANCE> & sleep 2) && cd <YOUR SCRIPT FOLDER> && <YOUR MIGRATION SCRIPT>"
    timeout: '1200s'

Whitneywhitson