running npx prisma migrate inside cloud build using cloud sql and cloud Run (MySql)

Question

I have a remix run application and am using a cloud sql database with a public IP and Prisma as an ORM. I'm also using Cloud Run to host my application and cloud build for the build process. The issue I'm having is the ability to run migrations in my cloudbuild.yaml file which is used for cloud build. I am having difficulty running a migration script because It's difficult to connect cloud build to cloud sql It currently fails on the migrate step

This is what I get back from the console

Already have image (with digest): node:16
2023/08/11 01:31:12 Authorizing with Application Default Credentials
2023/08/11 01:31:12 [yellowingreen:us-central1:yellow-in-green-1-dev] Listening on /cloudsql/yellowingreen:us-central1:yellow-in-green-1-dev
2023/08/11 01:31:12 The proxy has started successfully and is ready for new connections!
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "dev" at "34.171.173.136:3306"

Error: P1001: Can't reach database server at `34.171.173.136`:`3306`

Please make sure your database server is running at `34.171.173.136`:`3306`.

Below is my cloudbuild.yaml file

steps:
  # Install Node.js dependencies
  - id: npm-install
    name: node:16
    entrypoint: npm
    args: ["install", "--frozen-lockfile"]
    timeout: "4000s"
    waitFor: ["-"]

  # Install Cloud SQL proxy
  - id: proxy-install
    name: node:16
    entrypoint: sh
    timeout: "4000s"
    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: ["npm-install"]

  # Migrate database schema to the latest version
  # =tcp:3306
  - id: migrate
    name: node:16
    entrypoint: sh
    env:
      - DATABASE_URL=$_DATABASE_URL
    args:
      - "-c"
      - "mkdir /cloudsql; chmod 777 /cloudsql; (./cloud_sql_proxy --unix-socket /cloudsql $_DATABASE_CONNECTION & sleep 2) && npx prisma migrate deploy"
    timeout: "1200s"
    waitFor: ["npm-install", "proxy-install"]
  - id: build
    name: "gcr.io/cloud-builders/docker"
    args: ["build", "-t", "gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA", "."]
    waitFor: ["migrate"]
  - id: push
    name: "gcr.io/cloud-builders/docker"
    args: ["push", "gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA"]
    waitFor: ["build"]
  - id: deploy
    name: "gcr.io/google.com/cloudsdktool/cloud-sdk"
    entrypoint: gcloud
    args:
      [
        "run",
        "deploy",
        "$_SERVICE_NAME",
        "--image",
        "gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA",
        "--region",
        "$_DEPLOY_REGION",
      ]
images: ["gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA"]

Here is my DOCKERFILE

    # # base node image
FROM node:16-bullseye-slim as base

# # Build the dev image
FROM base as build
RUN mkdir /app/
WORKDIR /app/
COPY . /app
RUN npm install
RUN npm run build

# # Get the production modules
FROM base as production-deps
RUN mkdir /app/
WORKDIR /app/
COPY --from=build /app/node_modules /app/node_modules

ADD prisma .
RUN npx prisma generate

ADD package.json package-lock.json .npmrc /app/
RUN npm prune --production

# Pull out the build files and do a production install
FROM base
ENV NODE_ENV=production
RUN mkdir /app/
WORKDIR /app/
ADD package.json package-lock.json .npmrc /app/

COPY --from=build /app/node_modules/.prisma /app/node_modules/.prisma
COPY --from=build /app/public /app/public
COPY --from=build /app/build /app/build
COPY --from=build /app/server.js /app/server.js
COPY --from=build /app/prisma /app/prisma
COPY --from=production-deps /app/node_modules /app/node_modules

# RUN npx prisma migrate deploy
# RUN npx prisma db seed
CMD ["npm", "start"]

I have read over this documentation many times but I find it very confusing - https://cloud.google.com/sql/docs/mysql/connect-build I'm not sure if I need multiple cloudbuild.yaml files or just one if I use the cloud proxy Basically every time I push to my repo - It should trigger a new build and deploy a new migration. But I'm not sure if I should run the migration in the cloudbuild.yaml file or should I be running it in the Dockerfile.

Any help or feedback is appreciated, like I said I have read over the cloud build/cloud sql connection documentation but it's confusing. I feel like this is much more difficult than it should be having to go through all these extras steps.

my cloudbuild account has full access to cloud sql admin and cloud run admin — James Daly, Aug 11 '23 at 01:50
Please don't point to this question which I have studied - https://stackoverflow.com/questions/52352103/run-node-js-database-migrations-on-google-cloud-sql-during-google-cloud-build/64599510#64599510 i can't figure out why you have to use timeouts it seem like such a hack — James Daly, Aug 11 '23 at 04:12
I believe your `# Install Cloud SQL proxy` step has a unique IP and host that the following step `# Migrate database schema to the latest version` does not have access to, and your `$DATABASE_URL` is pointing to an IP that is only accessible within your cluster. If you pass in the correct DB params at the same step you are installing `cloud_sql_proxy` this should work. — adam2k, Aug 14 '23 at 19:30

James Daly · Answer 1 · 2023-08-20T05:02:38.307

With the help of this question - What is the Postgres DATABASE_URL to connect cloud run to postgres on cloud SQL and this question Google Cloud Build, Cloud Run, Cloud SQL Prisma Migration

I was able to get everything working. Two key points here, when working with mysql or and possibly postgres; I had to make sure the Database url env variable/secret contains a localhost address instead of the ip address shown in the cloud sql console if you go here - https://www.prisma.io/docs/concepts/database-connectors/mysql it will show the connection url needed - obviously it's best to store this as a secret rather than an env variable. Cloud build needs this for the cloud sql proxy. Finally in your Cloud Run the database url will be slightly different I used the ip address but at the end of the url I added ?socket=/cloudsql/{dbconnectionname} dbconnnectionname is the url given to you in the cloud sql console.

here is my updated cloudbuild.yaml file

    steps:
  # Install Node.js dependencies
  - id: npm-install
    name: node:16
    entrypoint: npm
    args: ["install", "--frozen-lockfile"]
    waitFor: ["-"]

  - id: migrate
    name: "gcr.io/cloud-builders/npm"
    env:
      - NODE_ENV=$_NODE_ENV
    entrypoint: sh
    args:
      - "-c"
      - |
        wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy
        chmod +x cloud_sql_proxy
        ./cloud_sql_proxy -instances=$_DATABASE_CONNECTION=tcp:3306 & sleep 3
        export DATABASE_URL=$_DATABASE_URL_LOCALHOST
        npx prisma migrate deploy
    timeout: "1300s"
    waitFor: ["npm-install"]

  - id: build
    name: "gcr.io/cloud-builders/docker"
    args: ["build", "-t", "gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA", "."]

  - id: push
    name: "gcr.io/cloud-builders/docker"
    args: ["push", "gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA"]

  - id: deploy
    name: "gcr.io/google.com/cloudsdktool/cloud-sdk"
    entrypoint: gcloud
    args:
      [
        "run",
        "deploy",
        "$_SERVICE_NAME",
        "--image",
        "gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA",
        "--region",
        "$_DEPLOY_REGION",
      ]
images: ["gcr.io/$PROJECT_ID/$_SERVICE_NAME:$COMMIT_SHA"]

Best practices is to change the env variable and use secrets instead there is documentation on how to do that and that will be the next step

running npx prisma migrate inside cloud build using cloud sql and cloud Run (MySql)

1 Answers1

Linked