google-app-engine google-cloud-sql prisma bitbucket-pipelines

Connecting Google App Engine to a Cloud SQL (Prisma) Postgres instance (through Bitbucket CI/CD)

I'm deploying a Google App Engine with a connection to a PostgreSQL CloudSQL instance, managed by Prisma ORM. All this I'm doing through the Bitbucket CI/CD. However I'm experiencing difficulties with setting-up this connection.

This is the error that I'm bumping into... (I've left out the security sensitive variables). The error occurs in the build step of the Google App Engine deploy image provided by Atlassian here: https://bitbucket.org/atlassian/google-app-engine-deploy/src/master/

> prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "mydatabasename", schema "public" at "localhost:5432"
Error: P1001: Can't reach database server at `/cloudsql/my-project-id:europe-west1:my-instance-name`:`5432`
Please make sure your database server is running at /cloudsql/my-project-id:europe-west1:my-instance-name`:`5432``.

I followed the following docs of Google: https://cloud.google.com/sql/docs/postgres/connect-app-engine-standard to connect through a Unix socket, but without luck. Also configured all the necessary IAM Roles.

Environment:

DATABASE_URL="postgres://username:password@localhost/dbname?host=/cloudsql/my-project-id:region:my-instance-name"

This DATABASE_URL is injected in schema.prisma like this:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Anyone that has experience deploying this specific tech stack, and knows how to set-up this connection?

Solution

I was approaching the problem wrongly. The connection through Google App Engine with Cloud SQL worked fine by setting up the right database_url env variable. However, the issue was that I was skipping this step by directly trying to migrate from Bitbucket CI/CD (which is not authorized). To get this authorization for Bitbucket CI/CD, you have to connect through a proxy. This way you can let the CI/CD do the migration for you, before you deploy the new app engine version. You can use the following reusable definition for this in bitbucket-pipelines.yml:

definitions:
  steps:
    - step: &prisma-migrate-deploy
        name: Prisma Migrate Deploy
        caches:
          - node
        script:
          # Set proxy database url
          - export DATABASE_URL=$PROXY_DATABASE_URL
          # Start Cloud SQL Proxy
          - curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.2/cloud-sql-proxy.linux.amd64
          - chmod +x cloud-sql-proxy
          - echo $GOOGLE_SERVICE_ACCOUNT | base64 --decode > service-account-key.json
          - ./cloud-sql-proxy --credentials-file service-account-key.json $CLOUD_SQL_INSTANCE_CONNECTION_NAME & sleep 5
          # Migrate
          - npm run migrate:prod