Vercel Postgres: The database schema is not empty on clean install

[mastoj]

Bug description

I'm using vercel postgres and try to setup prisma for the first time. For some reason I get the error "The database schema is not empty". There is a discussion on it here: #18973, but I do think it is a bug since I can't get by it no matter what I try.

Here is a screen of what I experience:

How to reproduce

1. Set up a new vercel postgres
2. Create schema
3. npx prisma migrate dev --name init
4. npx prisma migrate dev --create-only
5. See error

Expected behavior

I expect the last step to create an empty migration that I can modify.

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["jsonProtocol"]
}

datasource db {
  provider          = "postgresql"
  url               = env("POSTGRES_PRISMA_URL")
  directUrl         = env("POSTGRES_URL_NON_POOLING")
  shadowDatabaseUrl = env("POSTGRES_URL_NON_POOLING")
}

model Report {
  id        String    @id @default(cuid())
  sha       String?
  branch    String?
  timestamp DateTime?
  user      String?
  content   Json?
}

Environment & setup

• OS: PopOS
• Database: PostgreSQL (vercel)
• Node.js version: 18.13.0

Prisma Version

prisma                  : 4.14.1
@prisma/client          : 4.14.1
Current platform        : debian-openssl-1.0.x
Query Engine (Node-API) : libquery-engine d9a4c5988f480fa576d43970d5a23641aa77bc9c (at node_modules/@prisma/engines/libquery_engine-debian-openssl-1.0.x.so.node)
Migration Engine        : migration-engine-cli d9a4c5988f480fa576d43970d5a23641aa77bc9c (at node_modules/@prisma/engines/migration-engine-debian-openssl-1.0.x)
Format Wasm             : @prisma/prisma-fmt-wasm 4.14.0-67.d9a4c5988f480fa576d43970d5a23641aa77bc9c
Default Engines Hash    : d9a4c5988f480fa576d43970d5a23641aa77bc9c
Studio                  : 0.484.0
Preview Features        : jsonProtocol

[littlegromexchange]

I too have this same exact issue. Using next-auth with prisma as a db adapter. As a workaround I updated my startup script to include npx prisma migrate reset. It worked temporarily and but now I can't even connect to the db. It's throwing a PrismaClientUnknownRequestError error.

[Jolg42]

Hi @mastoj,

Did you create a Vercel project using one of their template?
If you did, then I think this is expected as they seed the database, see example:
https://github.com/vercel/examples/blob/main/storage/postgres-prisma/package.json

prisma generate && prisma db push && prisma db seed && next build

Could you check what is in the database?
Example from the Vercel UI (my database is empty, nothing show up when clicking in the search field):

You could truncate/drop the existing tables if the data is not important to avoid baselining that database.

For example:

• truncating a table

echo 'TRUNCATE TABLE "Post";' | npx prisma db execute --stdin --url="$DATABASE_URL"

• dropping a table

echo 'DROP TABLE "Post";' | npx prisma db execute --stdin --url="$DATABASE_URL"

[mastoj]

I did create a blank db, not using a template since I already had a project. I have also tried to drop all tables, but the issue is still there.

[Jolg42]

@mastoj Thanks for the info, we'll try to reproduce this. Vercel Postgres is still very new, from earlier this month, though there is no reason it shouldn't work, so we want to check why this is happening.

[littlegromexchange]

I did create a blank db, not using a template since I already had a project. I have also tried to drop all tables, but the issue is still there.

I also didn't use a provided template. Just created the datastore and connected the db to the vercel app.

[janpio]

This is a duplicate of #19234, you are just luckier in how it present:

With the same database URL for both directUrl and shadowDatabaseUrl, your existing data will be removed when migrate dev tries to identify the diff between your schema and database on the next migrate dev run.

Instead of deleting data, in your case, you seem to get an error message.

I suggest you remove the shadowDatabaseUrl and see if that fixes the problem.
If you get another error message that the database can not be created in that moment, you will have to provide another database in that property.

Seems we have to prioritize this issue soon: #16628

[mastoj]

Removing shadowDatabaseUrl did not help, I got another error message:

[mastoj]

My issues is when I follow the settings I get from vercel. From what I can see there are two different connection strings in use, so not sure what to do here.

[janpio]

Thanks what happens when you just remove the shadowDatabaseUrl. That will not work indeed.

To use migrate dev you need to set a different database in the shadowDatabaseUrl when the database provider does not allow CREATE DATABASE, which is currently the case with Vercel Postgres/Neon. I hear they are working on lifting that limitation. Until then you can either create a second database somewhere/somehow and use that, or you can mayyyybe try to add a schema to the existing one: ?schema=shadow and see if that works (not 100% sure right now).

[kenzan100]

adding ?schema=shadow to shadowDatabaseUrl env var apparently worked. thank you for the pointer!

[janpio]

Hey everyone, Vercel Postgres now supports Prisma Migrate better, so you can remove the shadowDatabaseUrl from your Prisma schema. Only url and directUrl is still needed, and Migrate should take care of the rest under the hood 🪄

Can you please let me know if this works for you? Then we can push to get the documentation updated accordingly. Thanks!

[janpio]

This should now be fixed, and shadowDatabaseUrl should not be needed with Vercel Postgres any more. Please just remove it and let the shadow database be created dynamically. Thanks.