Prisma Postgres stopped working out of sudden, saying database string is invalid

[franva]

Bug description

I was debugging my Next.js project with Prisma as my ORM. Everything was working fine. I logged into the dashboard page which lists all products from the Vercel Postgres database by using Prisma client.

Out of sudden, Prisma stopped working and no data can be fetched, basically any operations via Prisma error out. Here is an error example:

Invalid `prisma.artworks.findMany()` invocation:

The provided database string is invalid. The provided arguments are not supported 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.

I have not changed anything in the .env or .env.local files, I did not even open those 2 files. So I opened the Vercel website, and copied the database connection strings again and put it into these 2 environment config files,

POSTGRES_URL="postgres://default:<mypassword>@ep-divine-smoke-00439065-pooler.us-east-1.postgres.vercel-storage.com:5432/verceldb"
POSTGRES_PRISMA_URL="postgres://default:<mypassword>@ep-divine-smoke-00439065-pooler.us-east-1.postgres.vercel-storage.com:5432/verceldb?pgbouncer=true&connect_timeout=15"
POSTGRES_URL_NON_POOLING="postgres://default:<mypassword>@ep-divine-smoke-00439065.us-east-1.postgres.vercel-storage.com:5432/verceldb"
POSTGRES_USER="default"
POSTGRES_HOST="ep-divine-smoke-00439065-pooler.us-east-1.postgres.vercel-storage.com"
POSTGRES_PASSWORD="<mypassword>"
POSTGRES_DATABASE="verceldb"

(I replaced the real password with in this post, but the real env files have the correct password which I copied from Vercel without any change).

Also copied the code for use in Prisma's schema file like below:

datasource db {    
  provider = "postgresql"    
  url = env("POSTGRES_PRISMA_URL") // uses connection pooling    
  directUrl = env("POSTGRES_URL_NON_POOLING") // uses a direct connection    
}

Also I just notice that I can pull my db schema from the Vercel PostgreSql database, this means the database connection string actually works. The issue could come from Prisma.

Also, I am able to launch the Prisma Studio and see all the data. So this is for sure the issue from PrismaClient.

How to reproduce

1. create a next project
2. create a project on Vercel which has a storage with Postgres
3. copy the code snippet from .env.local tab in Storage section on the Vercel platform
4. create the .env file and pasted in the code snippet
5. Also copy the code snippet from Prisma tab on Vercel's storage section and pasted in schema.prisma
6. Pull and generate the code for the Database in the next.js project, npx prisma db pull and npx prisma generate,
7. Run any operations with the PrismaClient to see the error

Expected behavior

I expect it to work as previously.

Prisma information

// Add your schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("POSTGRES_PRISMA_URL")
  directUrl = env("POSTGRES_URL_NON_POOLING")
}

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

model likes {
  userid    String    @db.Uuid
  artworkid String    @db.Uuid
  likeddate DateTime? @default(now()) @db.Timestamp(6)
  artworks  artworks  @relation(fields: [artworkid], references: [id], onDelete: NoAction, onUpdate: NoAction)
  users     users     @relation(fields: [userid], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@id([userid, artworkid])
}

...... and other models

// Add your code using Prisma Client
import { PrismaClient, artworkimages, artworks, artworktags, tags, users } from '@prisma/client'
const prisma = new PrismaClient();

export async function getAllUsers(): Promise<users[]> {
  try {
    const activeUsers = await prisma.users.findMany({
      where: { isactive: true }
    });

    return activeUsers;
  } catch (error) {
    console.error('Failed to fetch user:', error);
    throw new Error('Failed to fetch user.');
  }
}

Environment & setup

• OS: Windows 11 Pro
• Database: PostgreSQL
• Node.js version: v20.9.0

Prisma Version

"@prisma/client": "^5.9.1",

---

Update 1

Not sure whether it is useful to help solve the issue, but here is my package.json file.

{
  "name": "myproject",
  "version": "0.1.0",
  "private": true,
  "scripts": {
    "dev": "next dev",
    "build": "prisma generate && next build",
    "start": "next start",
    "seed": "node -r dotenv/config ./src/utils/seed.js",
    "lint": "next lint"
  },
  "dependencies": {
    "@heroicons/react": "^2.1.1",
    "@prisma/adapter-pg": "^5.9.1",
    "@prisma/client": "^5.9.1",
    "@stripe/react-stripe-js": "^2.4.0",
    "@stripe/stripe-js": "^2.4.0",
    "@types/express-fileupload": "^1.4.4",
    "@vercel/postgres": "^0.7.2",
    "axios": "^1.6.7",
    "bcryptjs": "^2.4.3",
    "bootstrap": "^5.3.2",
    "bootstrap-icons": "^1.11.3",
    "imgur": "^1.0.2",
    "lodash": "^4.17.21",
    "next": "^14.1.0",
    "next-auth": "^5.0.0-beta.4",
    "pg": "^8.11.3",
    "react": "^18.2.0",
    "react-bootstrap": "^2.10.0",
    "react-dom": "^18.2.0",
    "sharp": "^0.33.2",
    "use-debounce": "^10.0.0",
    "use-shopping-cart": "^3.2.0",
    "zod": "^3.22.4"
  },
  "devDependencies": {
    "@types/bcryptjs": "^2.4.6",
    "@types/lodash": "^4.14.202",
    "@types/node": "^20.11.16",
    "@types/react": "^18.2.55",
    "@types/react-dom": "^18.2.18",
    "dotenv": "^16.4.1",
    "eslint": "^8.56.0",
    "eslint-config-next": "^14.1.0",
    "eslint-config-react-app": "^7.0.1",
    "prisma": "^5.9.1",
    "typescript": "^5.3.3"
  }
}

Update 2

I just found that if I pasted in the connection string into the schema.prisma like below and re-run npx prisma generate, it start to work.

datasource db {
  provider = "postgresql"
  url      = "postgres://default:<mypassword>@ep-divine-smoke-00439065-pooler.us-east-1.postgres.vercel-storage.com:5432/verceldb?pgbouncer=true&connect_timeout=15&sslmode=require"
}

So now I suspect that the env() method does not work in schema.prisma file.
How to fix this issue?

[franva]

Hi @laplab , thanks for making this a discussion. Appreciated if you could provide any suggestions.

[ludralph]

Hi @franva

Thank you for raising this question?

Can you access your database using a GUI tool like TablePlus using the Database connection string you are currently using?

[franva]

Hi @ludralph yes, I can access the db using Prisma Studio. Also I'm able to use the prisma db pull to pull db schema.
Only the PrismaClient in code does not work.

[franva]

Hi @ludralph and @laplab , I have added my package.json file. Hopefully it helps in locating the issue.

[franva]

Hi @ludralph and @laplab , I finally have made it working by pasted in the hard copy of the connection string directly into the schema.prisma file. I know it is not a good practice, but the Prisma just does not want to work in a proper way.

So it seems that it is the env() method stopped working in the schema.prisma which caused this issue.

[LyghtCode]

Hey wait can you expand on this, I am able to run my project locally but like you said on my vercel instance it just stopped working and we havent made any changes recently to our app with anything around the prisma or db code.

[LyghtCode]

Hard coding did not work for me.

[franva]

Was it working in Vercel instance? What have you changed?

[iEuJen]

likely, at changing the file we have to run 'npx prisma generate' before it works. @LyghtCode

[LyghtCode]

Again i can run my project locally only. I have a very similar issue only that my instance only works locally. When i use same exact env config in Vercel the pages of the project that make database requests just dont load.

[shadowfaz16]

having same issue here, works fine on local but won't run on my domain.

[LyghtCode]

Vercel logs throw no errors.

[franva]

@shadowfaz16 Was it working on your domain? If yes, have you changed anything?

[franva]

@LyghtCode and @shadowfaz16 also, could you please paste your error message here?

[LyghtCode]

It was working on the domain before, and now its not. We didn't change anything.

[franva]

Hi guys, I found that this issue comes from loading correct value of environment variables.
Here is what I have found:
https://stackoverflow.com/questions/77959270/next-js-all-string-value-after-are-ignored-for-environment-variables

[LyghtCode]

this did not work

[bjones256]

link is dead

[Aslam97]

@franva Did you solve the issue? If so, could you share the solution here? I've been trying to figure it out for hours and still not solve.

[Aslam97]

Never mind, I've solved the issue. It turns out that db pass contained special characters. Even though I tried replacing the special characters with percent encodings, it still wasn't working. After I changed the password (without any special character) and redeployed, everything is working fine now.

[dascarlet]

I had the same problem in Google Cloud AlloyDB using Prisma as mentioned by @franva and @Aslam97.
I removed ?, #, < and > (These were suspicious comparing with other correct password.) from DB user password, it works well.