How to fix P1000: Authentication failed against database server in Prisma

Check your .env file and confirm the DATABASE_URL has the correct username and password:

PostgreSQL format:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public"

MySQL format:

DATABASE_URL="mysql://USER:PASSWORD@HOST:PORT/DATABASE"

MongoDB format:

DATABASE_URL="mongodb+srv://USER:PASSWORD@HOST/DATABASE"

Important: Test the username and password directly by logging into your database with a database client (psql, mysql cli, MongoDB Compass, etc.). This confirms the credentials are valid before debugging Prisma.

If your password contains special characters, they must be URL-encoded in the DATABASE_URL:

Common special character encodings:
- @ → %40
- : → %3A
- ! → %21
- # → %23
- $ → %24
- % → %25
- & → %26

Example:
Password: p@ssw0rd!
Encoded: p%40ssw0rd%21

In DATABASE_URL:

DATABASE_URL="postgresql://user:p%40ssw0rd%21@localhost:5432/mydb"

You can use online URL encoder tools, or in Node.js: encodeURIComponent('p@ssw0rd!')

If you cannot verify the credentials work directly, reset your database password:

For local PostgreSQL:

# Connect to PostgreSQL as admin
sudo -u postgres psql

# Change the password for your user
ALTER USER your_username WITH PASSWORD 'new_password';

For local MySQL:

# Connect to MySQL as root
mysql -u root -p

# Change the password for your user
ALTER USER 'your_username'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

For cloud providers (Railway, Supabase, Render, AWS RDS):
- Open your database dashboard
- Look for "Reset password" or "Change password" option
- Generate a new password
- Update your .env file with the new credentials

After changing the password, restart your application.

MySQL 8.0 uses caching_sha2_password by default, but Prisma may use mysql_native_password. Change the authentication plugin:

Option 1: Change MySQL user authentication plugin (recommended)

mysql -u root -p

# Change authentication plugin
ALTER USER 'your_username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;

Option 2: Add sslMode parameter to connection string
Some cloud MySQL providers require SSL for caching_sha2_password:

DATABASE_URL="mysql://user:password@host:3306/db?sslMode=REQUIRED"

Option 3: Configure MySQL to use mysql_native_password (MySQL 8.0 only)
Edit /etc/mysql/mysql.conf.d/mysqld.cnf and add:

[mysqld]
default-authentication-plugin=mysql_native_password

Then restart MySQL and update all users to use the native plugin.

Verify your database user has the necessary permissions to create/access databases:

PostgreSQL:

# Connect as the superuser
sudo -u postgres psql

# List all users and their roles
\du

# Grant necessary permissions
GRANT CONNECT ON DATABASE your_database TO your_username;
GRANT CREATE ON DATABASE your_database TO your_username;

MySQL:

mysql -u root -p

# Show grants for a user
SHOW GRANTS FOR 'your_username'@'localhost';

# Grant all privileges
GRANT ALL PRIVILEGES ON your_database.* TO 'your_username'@'localhost';
FLUSH PRIVILEGES;

The user should have permissions to: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, and ALTER TABLE at minimum.

Ensure you're connecting to the correct database instance:

Check which database instance is running:

# PostgreSQL default port is 5432
# MySQL default port is 3306
# MongoDB default port is 27017

# Test if port is open
nc -zv localhost 5432  # PostgreSQL
nc -zv localhost 3306  # MySQL

For cloud databases, verify:
- Host matches your provider's connection string (e.g., db.example.com, xxx.railway.app)
- Port is the correct public port (often different from default)
- Database region matches your application region
- You're not using a local host (localhost, 127.0.0.1) for remote databases

Common mistakes:
- Connecting to localhost when database is on a remote server
- Using staging database credentials for production
- Database credentials from wrong environment

Verify the database user account exists and is not locked:

PostgreSQL:

sudo -u postgres psql

# List users
SELECT * FROM pg_user;

# Check if user is superuser, can login, etc.
\du your_username

# If user doesn't exist, create it
CREATE USER your_username WITH PASSWORD 'your_password';

MySQL:

mysql -u root -p

# List users
SELECT User, Host FROM mysql.user;

# Check if user exists and is enabled
SELECT * FROM mysql.user WHERE User='your_username' AND Host='localhost';

# If user doesn't exist, create it
CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';

Locked accounts show `* '' in the password field or have authentication locked in status.