How to fix P1010: User was denied access on the database in Prisma

First, confirm that your DATABASE_URL is correct and the credentials are valid:

1. Check your .env file for the DATABASE_URL
2. Verify the format is correct for your database type:
- PostgreSQL: postgresql://username:password@host:port/database
- MySQL: mysql://username:password@host:port/database
3. Test the connection manually using a database client:
- PostgreSQL: psql "postgresql://username:password@host:port/database"
- MySQL: mysql -u username -p -h host database

If manual connection works but Prisma fails, the issue is likely permissions, not credentials.

The most common cause of P1010 is that the database user lacks CREATE DATABASE privileges. Connect to your database as an admin user and grant these permissions:

For PostgreSQL:

-- Connect as a superuser (usually 'postgres')
ALTER USER your_username CREATEDB;

For MySQL:

-- Connect as root or admin
GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

For a more restrictive MySQL setup:

GRANT CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE ON your_database.* TO 'your_username'@'localhost';
FLUSH PRIVILEGES;

After granting privileges, try running your Prisma commands again.

If you cannot grant CREATE DATABASE privileges (e.g., in managed database services), configure a separate shadow database:

Edit your prisma/schema.prisma:

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

Then add to your .env file:

DATABASE_URL="postgresql://username:password@host:port/main_database"
SHADOW_DATABASE_URL="postgresql://username:password@host:port/shadow_database"

The shadow database must exist and be empty. Create it with:

-- PostgreSQL
CREATE DATABASE shadow_database;

-- MySQL
CREATE DATABASE shadow_database;

This tells Prisma to use a separate database for its internal operations instead of trying to create one.

If you're using a database proxy or cache like PolyScale, AWS RDS Proxy, or similar, disable PostgreSQL channel binding:

Modify your DATABASE_URL to include channel_binding=disable:

postgresql://username:password@host:port/database?channel_binding=disable

Channel binding is a security feature that prevents man-in-the-middle attacks, but it cannot work through proxies. Disabling it allows the proxy to act as a middleman while maintaining encryption through SSL/TLS.

Verify the full connection string:

postgresql://user:pass@proxy-host:port/db?channel_binding=disable

If the user has basic database access but cannot access specific schemas:

-- Grant schema usage permissions
GRANT USAGE ON SCHEMA public TO your_username;

-- Grant all table permissions in the schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_username;

-- Grant sequence permissions (for auto-incrementing IDs)
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_username;

-- Make this the default for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO your_username;

This is especially important after restoring a database from a backup.

In production environments with restricted users, use prisma migrate deploy instead of prisma migrate dev:

# For development (requires shadow database or CREATE privileges)
npx prisma migrate dev --name add_users_table

# For production (no shadow database needed, just applies pending migrations)
npx prisma migrate deploy

prisma migrate deploy doesn't require shadow database creation, so it works with more restrictive user permissions. It applies pre-planned migrations without attempting to create temporary databases.

You can run your schema planning locally or in CI/CD, then deploy only the final migrations to production with a read-only-ish user.

Sometimes P1010 occurs because your connection string is pointing to a database you don't have permissions for:

1. Verify you're connecting to the intended database:
- Check the database name in your DATABASE_URL
- List available databases you have access to:

PostgreSQL:

psql -l

MySQL:

mysql -u username -p -e "SHOW DATABASES;"

2. If connecting to a cloud database service:
- Verify the connection string from your provider (Railway, Vercel, AWS RDS, Azure, etc.)
- Check if your cloud provider has set default users with limited privileges
- Look for documentation on how to grant Prisma privileges

3. Make sure you're not accidentally connecting to the wrong environment:
- Verify .env file is loading the correct DATABASE_URL
- Check for variable name conflicts (DATABASE_URL vs DB_URL vs DATABASE_CONNECTION_URL)