CODEWITHSUNDEEP
google-cloud-sqlgoogle-cloud-buildcloud-sql-proxyprivate-network
Abhilash Chelankara
Abhilash Chelankara

Reputation: 406

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

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"

Upvotes: 4

Views: 3255

Answers (3)

Rami
Rami

Reputation: 520

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

Upvotes: 0

Romi Afrizal
Romi Afrizal

Reputation: 11

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

Upvotes: 1

llompalles
llompalles

Reputation: 3176

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.

Upvotes: 2

Related Questions