prisma: Prisma Migrate: Error creating shadow database

I just want to add a quick note for folks coming here from googling the error message that if you are currently prototyping and not in production, and don’t care about the generated migration files, you can also run prisma db push instead of the prisma migrate dev command:

npx prisma db push --preview-feature

For anyone like me who is a newbie to databases and who are using a cloud-hosted db for development, you can fix by doing the following:

1. Set up a local db. I am using PostgreSQL and followed this guide: https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb
2. Change your prisma .env file to local db
3. Delete prisma/migrations folder
4. Run npx prisma migrate dev --preview-feature to start a new migration
5. Change your prisma .env file back to development db
6. Run npx prisma migrate resolve --applied "{{MIGRATION_FOLDER_NAME_GENERATED_BY_STEP_4}}" --preview-feature

Hope that helps

I am having this issue with a cloud hosted PostgreSQL db hosted on Heroku. I don’t have that much knowledge on databases so is anyone able to point me in the right direction for resolving this?

It appears that Heroku hosted PostgreSQL dbs are not able to have superusers https://help.heroku.com/IV1DHMS2/can-i-get-superuser-privileges-or-create-a-superuser-in-heroku-postgres

I find this quite a big issue since not everyone will host an own database server in this “serverless era” we live in now.

We have to stick to 2.12 in the meantime

You can find more info in our docs here https://www.prisma.io/docs/concepts/components/prisma-migrate#shadow-database

For anyone still running into this issue, you can resolve this by defining the shadowDatabaseUrl on the datasource block as documented here:

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

If you’re a cloud provider like Heroku, you can create a second DB there and use it as your shadow DB.

For those like me who don’t want to search and waste time, in your local env you should use the shadowDatabaseUrl = env("SHADOW_DATABASE_URL") config, and setup a second DB (with a user with all privileges) in your local env.

This is a new concept, but it’s worth it and you won’t have anymore problems creating migrations normally.

I was working with a heroku postgres db and was getting this error. So I created two apps in Heroku with two seperate db urls. then I set the second one as the shadow db.

in my .env DATABASE_URL='db_url_from_heroku_app_1" SHADOW_DATABASE_URL='db_url_from_heroku_app_2"

in the schema.prisma

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

Then I ran npx prisma db push

and then

npx prisma migrate dev --name init

and I got my migrations file.

Following this conversation.

@n5sgg Thanks for the feedback. I think in this case, which is probably not obvious in our docs, you could use the shadowDatabaseUrl property to define the shadow database name permanently. Meaning, you can then grant permissions for create and drop only for that database, eliminating the security risk of the wildcard.

See https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database#cloud-hosted-shadow-databases-must-be-created-manually

datasource db {
  provider          = "mysql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

@abdelopx Did you check https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database maybe?

If this is still an issue, could you create an issue here or a discussion here with more info?

@BearJS I got up to step 6 of your instructions, but I’m unfortunately getting this error: Error: Failed to read migration script. What was your migration name? I named it “prisma-preview” and I ran the following:

npx prisma migrate resolve --applied "prisma-preview" --preview-feature

Update: I had to use the actual name of the migration. All good now.

But now I am unable to migrate since it requires the shadow DB, and I use Heroku. So I reverted to v2.12.0 for now, as the docs suggest.

still dont work with shadowDatabaseUrl

For anyone like me who is a newbie to databases and who are using a cloud-hosted db for development, you can fix by doing the following:

1. Set up a local db. I am using PostgreSQL and followed this guide: https://www.codementor.io/@engineerapart/getting-started-with-postgresql-on-mac-osx-are8jcopb
2. Change your prisma .env file to local db
3. Delete prisma/migrations folder
4. Run npx prisma migrate dev --preview-feature to start a new migration
5. Change your prisma .env file back to development db
6. Run npx prisma migrate resolve --applied "{{MIGRATION_FOLDER_NAME_GENERATED_BY_STEP_4}}" --preview-feature

Hope that helps

I just took one more step here. To update my database.

1. Run npx prisma migrate reset

@adeel55 so we now have a doc page about the shadow database, how it works and why it’s needed to generate migrations.

See https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database

Note: The shadow database is only required in a development environment (specifically for the prisma migrate dev command).

I think there should be a parameter like: prisma migrate dev --name prisma-preview --no-shadow-db

https://pris.ly/d/migrate-shadow is live

The required priviliges required for Prisma Migrate to manage the shadow database are documented here: https://www.prisma.io/docs/concepts/components/prisma-migrate#shadow-database-user-permissions

You do not need to use a root account. You just need to use an account that can execute CREATE DATABASE. Otherwise you can define the shadowDatabaseUrl to an already existing database.

Thanks @Jolg42, it worked fine. I’ve created a hello-prisma repo using mysql and docker-compose in case this helps anyone: https://github.com/n5sgg/hello-prisma

For anyone using MySQL, Prisma creates and drops a bunch of prisma_migrate_shadow_db_[UUID] databases. So if your user only has access to the app DB it will fail. Assuming your user configured in .env is johndoe, this will work:

GRANT CREATE, DROP ON *.* TO johndoe@'%';

I have a few issues with this. The command output is not clear at all, because it says “User johndoe was denied access on the database mydb”, when in fact it had access to that db, but not to create other dbs. This should be better documented. And an even bigger concern, by allowing CREATE and DROP in all databases, we’re compromising the security and not following least privilege principle. In MySQL there’s no way to grant create and drop for a particular DB prefix, so the only way it can work is by allowing wildcard.

I ran into this while attempting to set up a redwood app with an app-specific database user:

I thought that this GRANT would be sufficient

GRANT ALL PRIVILEGES ON DATABASE my_app_dev TO my_app;

But it doesn’t actually grant CREATEDB to the my_app user

I needed to hop back into psql and use ALTER USDER:

ALTER USER my_app CREATEDB;

Can confirm that “Create DB” access has been granted with \du:

                                       List of roles
     Role name     |                         Attributes                         | Member of 
-------------------+------------------------------------------------------------+-----------
 my_app            | Create DB                                                  | {}
 postgres          | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

In the end I have a local postgres user called my_app that can do whatever it wants with my_app_dev database. So I am not reliant on the top-level user called postgres or <machine_name>. This was my intention

P.S. Here is my connection string in case it may help anyone:

# The password is "password" in my case
DATABASE_URL=postgres://my_app:password@localhost:5432/my_app_dev

New issue created here: https://github.com/prisma/prisma/issues/12100

Same answer to you then @vaclav-stummer: Please open a new issue and describe the full problem. We are not aware of any reports of problems with the shadow database that still stand after we looked at them.

@abdelopx Did you check https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database maybe?

If this is still an issue, could you create an issue here or a discussion here with more info?

Thanks ! The link fixed it !

Can you put this into a new issue @deadcoder0904? Will play with it later, but would prefer to leave the answer (or follow up question) in a place that is only about this problem and does not notify many other people. Thanks.

I’m trying to make Prisma work with SyncInc which converts Airtable into Postgres.

Can someone help me with using npx prisma migrate dev --name init command? I’m unable to make it work even though I have CREATE DB permissions for my user locally.

Here’s a sample repro → https://github.com/deadcoder0904/syncinc-prisma

Airtable base link → https://airtable.com/invite/l?inviteId=invvPQbKJ7xRfZ5oX&inviteToken=eeb63530dca64c6c768c735245b5d463f6cbaa44700eabfa05bf0ee8e53e2df8

All you need to do is:

1. Clone the Airtable base to your Airtable account
2. Create an account on SyncInc
3. Clone the repo, change .env.local to .env & substitute proper values
4. Try running npx prisma migrate dev --name init

Will take tops 10 mins. Idk if it’s a problem locally or with SyncInc. Sometimes I get insufficient permissions error, sometimes it says drift detected & asks for reset but then when I type yes, it says must be owner of table _sync_meta.

Idk how to make it work other than doing npx prisma db push --preview-feature 😦

For those of you who are unable to use Prisma Migrate because you are using a cloud provider where it’s not possible to create a shadow database, @garytube @perryraskin @BearJS:

This is going to be addressed via https://github.com/prisma/prisma/issues/4751, which is the issue you should be tracking.

We are closing #4571 as we shipped a better error message when failing to create the shadow database (available in 2.13.1).