How to Fix “Prisma Migrate Could Not Create the Shadow Database. Please Make Sure the Database User Has Permission to Create Databases”

When working with PostgreSQL and Prisma ORM, especially during database migrations, you may encounter the error: “Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases.“ This error indicates that the PostgreSQL user lacks the required permissions to create a temporary “shadow” database, which Prisma uses for safe migration previewing.

In this guide, we’ll walk you through the necessary steps to grant the required permissions to your PostgreSQL user, enabling smooth migrations with Prisma.

Understanding Database Privileges in PostgreSQL

PostgreSQL privileges determine what operations a user can perform, including creating, reading, updating, and managing data and database structures. Prisma migrations require the database user to have privileges to create, modify, and manage the database schema. If the user lacks these privileges, Prisma cannot create a shadow database, resulting in migration errors.

Step-by-Step Guide to Fixing the Shadow Database Permission Error

1. Log In to PostgreSQL as a Superuser

To grant the needed privileges, start by logging in to PostgreSQL as a superuser (commonly postgres). You can do this with the following command:

sudo -u postgres psql

2. Grant the CREATEDB Privilege to Your Prisma User

Once you’re in the PostgreSQL shell, grant the CREATEDB privilege to the Prisma user. Replace your_prisma_user with the actual username for the database user Prisma is configured to use:

ALTER USER your_prisma_user CREATEDB;

This command enables the user to create databases, which is necessary for creating a shadow database during migrations.

3. Exit PostgreSQL

After you’ve granted the privilege, exit the PostgreSQL shell:

\q

4. Test the Configuration

To confirm the permissions are correctly set, log in as the Prisma user and test the migration process. First, log in with:

psql -U your_prisma_user -d your_database_name -W

Then, execute the Prisma migration command:

npx prisma migrate deploy

If the command runs successfully, the CREATEDB privilege is set correctly, and Prisma can create the shadow database.

Additional Configuration: Using a Separate Shadow Database User (Optional)

If you prefer to keep CREATEDB permissions limited to a separate user, you can configure Prisma to use a different database user specifically for shadow database creation. To do this:

1. Open your schema.prisma file and add shadowDatabaseUrl under the datasource block:

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

2. In your .env file, set the SHADOW_DATABASE_URL to include a user with CREATEDB permissions:

DATABASE_URL="postgresql://your_prisma_user:your_password@localhost:5432/your_database"
SHADOW_DATABASE_URL="postgresql://super_user:super_password@localhost:5432/your_database"

This approach keeps your primary Prisma user restricted while enabling shadow database creation through a separate user.

Conclusion

By following these steps, you can resolve the “Prisma Migrate could not create the shadow database” error and ensure Prisma migrations run smoothly. Properly managing PostgreSQL user privileges enhances both security and functionality in your development process.

If you run into additional issues with PostgreSQL or Prisma, feel free to reach out or consult further resources. Happy coding!