4

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"
Abhilash Chelankara
  • 408
  • 3
  • 10

3 Answers3

2

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.

llompalles
  • 3,072
  • 11
  • 20
1

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:
  1. 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

Aseem
  • 5,848
  • 7
  • 45
  • 69
Romi Afrizal
  • 11
  • 1
  • 1
    is your cloud SQL instance public? – Sam Levin Mar 25 '21 at 00:51
  • Yes. The cloud SQL must either have a public IPv4 address. The public IP address does not need to be accessible to any external address (it does not need to be added as an authorized network address). Refer to the following [here](https://cloud.google.com/sql/docs/mysql/sql-proxy#requirements). – Romi Afrizal Mar 26 '21 at 02:05
0

I had the same issue as I am using AlloyDB and I was able to resolve it by setting up a worker pool under cloud build and I gave VPC access to the worker pool and the VPC has access to a serverless VPC that has access to AlloyDB so my migrations there were successful.

https://cloud.google.com/build/docs/private-pools/private-pools-overview

Rami
  • 490
  • 7
  • 22