Error depoying on vercel with vercel's postgresql beta service

[pnck]

Describe the Bug

I'm trying to deploy umami into vercel with its new pg beta service, and I failed with these logs:

...
[04:45:59.295] $ node scripts/check-db.js
[04:45:59.456] ✓ DATABASE_URL is defined.
[04:46:02.796] ✓ Database connection successful.
[04:46:03.448] ✓ Database version check successful.
[04:46:05.526] Error: Migration engine error:
[04:46:05.526] db error: ERROR: prepared statement "s0" already exists
[04:46:05.526] 
[04:46:05.527] ✗ Command failed: prisma migrate deploy
[04:46:05.527] Error: Migration engine error:
[04:46:05.527] db error: ERROR: prepared statement "s0" already exists
[04:46:05.527] `
[04:46:05.527] 
[04:46:05.547] error Command failed with exit code 1.
[04:46:05.547] info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
[04:46:05.570] ERROR: "check-db" exited with 1.
[04:46:05.587] error Command failed with exit code 1.
[04:46:05.587] info Visit https://yarnpkg.com/en/docs/cli/run for documentation about this command.
[04:46:05.617] Error: Command "yarn build" exited with 1

The vercel pg service automatically generate all these enviroments:

DATABASE_URL="postgres://xxx.postgres.vercel-storage.com:5432/verceldb"
DATABASE_PRISMA_URL="postgres://xxx.postgres.vercel-storage.com:5432/verceldb?pgbouncer=true&connect_timeout=15"
DATABASE_URL_NON_POOLING="postgres://xxx.postgres.vercel-storage.com:5432/verceldb"
DATABASE_USER="default"
DATABASE_HOST="xxx.postgres.vercel-storage.com"
DATABASE_PASSWORD="xxx"
DATABASE_DATABASE="verceldb"

Note that DATABASE_PRISMA_URL contains the pgbouncer parameter while DATABASE_URL not.

As metioned in #1712 , I guess this missing paramater in DATABASE_URL leads to the error.

I suggest to check DATABASE_PRISMA_URL over DATABASE_URL to use a more detailed connection string.

Database

PostgreSQL

Relevant log output

No response

Which browser are you using? (if relevant)

No response

How are you deploying your application? (if relevant)

No response

[mikecao]

Can't you just add the pgbouncer param to your connection string?

[pnck]

Can't you just add the pgbouncer param to your connection string?

I did. I have finished deploying by manually overwrite DATABASE_URL environment with the connection string provided by vercel db, instead of using vercel's builtin env injecting feature.

It's just somehow frustrating that you can't deploy it fully automatically. Not a big deal, I admit.

[skyzh]

Hi @pnck, Neon developer (the service backing Vercel Postgres) here. Happy to help. The issue looks more like prisma migrate should not be using pgbouncer in the connection string. pgbouncer does not support prepare statements for now and may lead to unexpected errors. You should specify something like directUrl in Prisma schema to use the URL in DATABASE_URL_NON_POOLING and connect to Vercel Postgres, or remove the all pgbouncer parameters and switch to URLs without pooler in all fields when you develop your project locally.

On our side, we are working on support for prepare statements in pgbouncer, and probably at some time in the future, you do not need to manually specify the non-pooling URL. But for now, when using prisma migrate, you will need to use a direct connection instead of pgbouncer :) should be fixed

FYI: https://neon.tech/docs/guides/prisma-migrate

[skyzh]

Specifying DATABASE_PRISMA_URL as the env variable used for Prisma schema should fix the problem. The URL should look like:

postgres://user:passwd@endpoint-pooler.postgres.vercel-storage.com/verceldb?pgbouncer=true&connect_timeout=10

The value can be changed in schema.prisma.

[Vinfall]

I thought that's what OP did exactly. To me this issue seems more about suggesting checking DATABASE_PRISMA_URL over DATABASE_URL, otherwise umami would still prefer DATABASE_URL which is automatically assigned by Vercel Postgres connection.

[pnck]

@Vinfall @skyzh
To be honest I don't know much about prisma, so I don't know what arguments exactly should be passed in the envs. I just randomly tried and found it worked if I put the pgbouncer there.

So my suggestion about checking another env might not be the optimal solution. Instructions newly added in the related PR also looks good to me.

[Vinfall]

Sure, I missed that. The PR appends the necessary params already so everything should be fine then.

[ScottEAdams]

I could be wrong but I am pretty sure this is not fixed and needs reopening (unless its been fixed in 2.6.0??). We are using the docker image of umami v2.5.0 with neon (not on vercel) and setting:

DATABASE_URL=......?pgbouncer=true&connect_timeout=10

Which is throwing:

✗ Command failed: prisma migrate deploy
Error: Schema engine error:
db error: ERROR: prepared statement "s0" does not exist

And the neon docs are stating that the directUrl needs setting in the schema.prisma - https://neon.tech/docs/guides/prisma-migrate

Is that correct @skyzh ? The directUrl still needs setting, you do not handle the migrates through pgbouncer=true ?

[skyzh]

experimental support for prepared statements over pgbouncer is already in the prod IIRC, and therefore it is possible that you don't need pgbouncer=true (which switches Prisma to work in a special pgbouncer mode in migration) to integrate with Neon. Anyways, it's always safer to add pgbouncer=true + directUrl as in the doc.