How to connect to cloudSQL proxy from Prisma in Google Kubernetes Engine?

Question

I have a microservices project which has:

• user-service
• post-service

Let's talk about the user-service to explain the problem:

After deploying the k8s objects like deployment that contains the user image, pods are created and each pod contains the user container and the cloud-sql-proxy container in a sidecar pattern.

Now inside this pod, I am using Prisma inside the user container to connect to the cloud sql proxy container inside the same pod on this url: postgresql://username:password@localhost/db_name?host=/cloudsql/gcp_project:us-central1:db

Problem:

When I log the user-service pod, I find this error:

Error: P1013: The provided database string is invalid. invalid port number in database URL. Please refer to the documentation in https://www.prisma.io/docs/reference/database-reference/connection-urls for constructing a correct connection string. In some cases, certain characters must be escaped. Please check the string for any illegal characters.

My Dockerfile:

FROM node:alpine

WORKDIR /app

COPY . .

RUN npm install

# Copy the start.sh into the container
COPY start.sh .

# Make the shell script executable
RUN chmod +x start.sh

# Execute the shell script
CMD ["/bin/sh", "start.sh"]

Inside start.sh

#!/bin/bash

cd src/

npx prisma db push

cd ..

npm start

Inside the src/ directory I have the prisma/ dir.

Note: I have also tried replacing ':' with %3A in the DB string param, but it did not work.

Deployment File

apiVersion: apps/v1
kind: Deployment
metadata:
  name: user-depl
  namespace: social-app
spec:
  replicas: 1
  selector:
    matchLabels:
      app: user
  template:
    metadata:
      labels:
        app: user
    spec:
      containers:
        - name: user
          image: <image_name>
          resources:
            limits:
              cpu: "500m"
              memory: "512Mi"
            requests:
              cpu: "250m"
              memory: "256Mi"
        - name: cloud-sql-proxy
          image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.1.0
          args:
            - "--structured-logs"
            - "--port=5432"
            - "PROJECT_ID:asia-south1:POSTGRES_INSTANCE_NAME"
            - "--credentials-file=/secrets/service_account.json"

          securityContext:
            runAsNonRoot: true
          volumeMounts:
            - name: cloudsql-sa-volume
              mountPath: /secrets/
              readOnly: true
          resources:
            requests:
              memory: "768Mi"
              cpu: "500m"

      volumes:
        - name: cloudsql-sa-volume
          secret:
            secretName: cloudsql-sa

---
# Cluster IP service for user service
apiVersion: v1
kind: Service
metadata:
  name: user-srv
  namespace: social-app
spec:
  selector:
    app: user
  ports:
    - name: user
      protocol: TCP
      port: 5001
      targetPort: 5001

I have also checked if the cloud-sql-proxy container is running or not by logging it, and the message that it is ready for connections.

When I run this command: npx prisma db push using the shell script, I am expecting prisma to successfully connect to the cloudsql proxy container which will connect to the cloudsql instance on google cloud.

Answer 1

There seems to be a bit of confusion happening here.

The Cloud SQL Proxy can be deployed to connect to Cloud SQL via two different options:

a) TCP connection over a port (default)

b) Unix domain socket (using the --unix-socket flag)

It seems as though your configuration is deploying the Cloud SQL Proxy to connect via TCP connection (using --port) but your application (Prisma config) is attempting to connect via a Unix socket (by using host as query param, Prisma docs).

Your deployment YAML looks fine as is to me, it should successfully setup the proxy to listen for a TCP connection on 127.0.0.1:5432 of your pod. You should be able to just update your application and Prisma URL accordingly to the following:

postgresql://username:[email protected]:5432/db_name