Run DB migrations on cloud build connecting to cloud sql using private IP

Question

I am trying to setup db migrations for a Nodejs app on cloud build connecting to cloud sql with a private IP via cloud sql proxy. Cloud SQL connection always fail from cloud build.

Currently I am running migration manually from a compute engine.

I followed this SO to setup the build steps. Run node.js database migrations on Google Cloud SQL during Google Cloud Build

cloudbuild.yaml

steps:
  - name: node:12-slim
    args: ["npm", "install"]
    env:
      - "NODE_ENV=${_NODE_ENV}"
  - 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"
  - name: node:12
    timeout: 100s
    entrypoint: sh
    args:
      - -c
      - "(/workspace/cloud_sql_proxy -dir=/workspace -instances=my-project-id:asia-south1:postgres-master=tcp:5432 & sleep 3) && npm run migrate"
    env:
      - "NODE_ENV=${_NODE_ENV}"
      - "DB_NAME=${_DB_NAME}"
      - "DB_PASS=${_DB_PASS}"
      - "DB_USER=${_DB_USER}"
      - "DB_HOST=${_DB_HOST}"
      - "DB_PORT=${_DB_PORT}"
  - name: "gcr.io/cloud-builders/gcloud"
    entrypoint: "bash"
    args:
      [
        "-c",
        "gcloud secrets versions access latest --secret=backend-api-env > credentials.yaml",
      ]
  - name: "gcr.io/cloud-builders/gcloud"
    args: ["app", "deploy", "--stop-previous-version", "-v", "$SHORT_SHA"]
timeout: "600s"

Error:

KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
Step #2:     at Client_PG.acquireConnection (/workspace/node_modules/knex/lib/client.js:349:26)

Cloud build roles:

Cloud Build Service Account
Cloud SQL Admin
Compute Network User
Service Account User
Secret Manager Secret Accessor
Serverless VPC Access Admin

CLOUD SQL ADMIN API is enabled too.

Versions:

NPM libs:
  "pg": "8.0.3"
  "knex": "0.21.1"

Answer 1

The Cloud SQL Private IP feature uses internal IP addresses hosted in a VPC network, which are only accessible from other resources within the same VPC network.

Since Cloud Build does not support VPC Networks, it is not possible to connect from Cloud Build to the private IP of a Cloud SQL instance.

You might want to take a look at the official Cloud SQL documentation regarding this topic to choose another alternative that suits your use case.

Answer 2

Connecting to public cloud sql

I use docker-compose & cloud sql proxy.

1. setup docker-compose for cloud build, here.

2. create service account (json file).

3. docker-compose file:

version: '3.7'

services: 
  app:
    build:
      context: .
      dockerfile: Dockerfile
    restart: "no"
    links:
      - database
    tty: true
    volumes: 
      - app:/var/www/html
    env_file: 
      - ./.env
    depends_on: 
      - database
      

  database:
    image: gcr.io/cloudsql-docker/gce-proxy
    restart: on-failure
    command: 
      - "/cloud_sql_proxy" 
      - "-instances=<INSTANCE_CONNECTION_NAME>=tcp:0.0.0.0:3306" 
      - "-credential_file=/config/sql_proxy.json"
    volumes: 
      - ./sql_proxy.json:/config/sql_proxy.json:ro

volumes: 
  app:

4. cloudbuild.yml

- name: 'gcr.io/$PROJECT_ID/docker-compose'
  id: Compose-build-cloudProxy
  args: ['build']

- name: 'gcr.io/$PROJECT_ID/docker-compose'
  id: Compose-up-cloudProxy
  args: ['up', '--timeout', '1', '--no-build', '-d']

- name: 'bash'
  id: Warm-up-cloudProxy
  args: ['sleep', '5s']

- name: 'gcr.io/cloud-builders/docker'
  id: Artisan-Migrate
  args: ['exec', '-i', 'workspace_app_1', 'php', 'artisan', 'migrate']

- name: 'gcr.io/$PROJECT_ID/docker-compose'
  id: Compose-down-cloudProxy
  args: ['down', '-v']

build-success.png