Prisma database connection string not working in production environment

[johnlk]

Bug description

I've got a NextJS application using prisma ORM to connect to a cloud sql postgres instance. I'm able to successfully connect to the database locally; however, when deploying to a staging or production environment, I'm unable to successfully connect.

I'm not using any special characters in my db user or password values, and I'm also able to connect to a SQL client with the connection string just fine. ie there are no public access issues on the cloud sql instance.

The connection string is of the form:

postgresql://user:pass@ip_address:5432/db_name

I'll get a 500 when attempting to connect when NODE_ENV=production.

I attempted to follow the advice of this blog post, and if I tweak the connection string to:

postgresql://user:pass@ip_address:5432/db_name?host=/cloudsql/gcp_project_id:region:instance_name

I won't get a 500, but I'll still have an issue connecting to my db. I've also tried replacing "host" with "socket" to no avail.

What am I doing wrong?

How to reproduce

Set NODE_ENV=production
Set DATABASE_URL=postgresql://user:pass@ip_address:5432/db_name

May only be applicable to cloud sql connections. Not sure.

Expected behavior

Successful db connection. Works locally, bombs when NODE_ENV != development

Prisma information

Irrelevant, but my header is

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

generator client {
  provider = "prisma-client-js"
}

// --------------------------------------
...

Environment & setup

Set NODE_ENV=production
Set DATABASE_URL=postgresql://user:pass@ip_address:5432/db_name

macOs, linux, node docker image all the same
db: postgres google cloud sql instance
node version: 19.4

Prisma Version

4.10.1

[nurul3101]

Hello @johnlk 👋

There's a related GitHub Issue that discusses connecting to Google Cloud SQL Instance

• Can't connect to Cloud SQL Postgres using socket #11171

Did you had a look at the solution comments in this GitHub Issue?

[johnlk]

Hi @nurul3101 ,

Looks like the issue you linked is the same issue I'm experiencing. I attempt the first couple suggestions, and I'm still getting the same error.

Feel free to consider this discussion closed, and I can followup in the other thread!

[nurul3101]

Sure!