Azure SQL and some cloud databases don't allow automatic shadow database creation. Prisma Migrate needs a shadow database for development to detect schema drift. Configure a manual shadow database URL in your datasource to resolve this error.
The P3020 error occurs when using Prisma Migrate with Azure SQL or other cloud-hosted databases that restrict database creation permissions. Prisma Migrate requires a shadow database during `prisma migrate dev` to detect potential issues like schema drift and data loss without affecting your production database. Since Azure SQL disables automatic database creation, you must manually configure a separate database to serve as the shadow database.
In the Azure Portal, create a new empty database on the same SQL Server as your primary database. Name it something like myapp-shadow or myapp-dev-shadow. Note the full connection string for this new database. This database will be automatically managed by Prisma Migrate.
Create or update your .env.local file with the shadow database connection string:
DATABASE_URL="sqlserver://server.database.windows.net:1433;database=myapp;user=username;password=password;encrypt=true;trustServerCertificate=false;connectionTimeout=30;"
SHADOW_DATABASE_URL="sqlserver://server.database.windows.net:1433;database=myapp-shadow;user=username;password=password;encrypt=true;trustServerCertificate=false;connectionTimeout=30;"Make sure the shadow database name in the connection string matches the database you created.
Open prisma/schema.prisma and add the shadowDatabaseUrl parameter to your datasource:
datasource db {
provider = "sqlserver"
url = env("DATABASE_URL")
shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}This tells Prisma to use the shadow database during migrations.
Now you can run migrations normally:
npx prisma migrate dev --name your_migration_namePrisma will create and delete the shadow database automatically as needed. The shadow database is only used for development and will be recreated fresh each time you run prisma migrate dev.
For production deployments, use prisma migrate deploy instead of prisma migrate dev. This command executes existing migrations without requiring a shadow database:
npx prisma migrate deployThe shadow database is only needed during development with prisma migrate dev.
Important considerations:
Shadow database cleanup: Prisma automatically creates and destroys the shadow database during each migration. Ensure your database user has permissions to create and drop databases, or manually clean up if migrations fail.
Different credentials: The shadow database can use a different user account than your primary database, but both must be on the same server. You can create a separate SQL user with minimal permissions (just CREATE/DROP database) for shadow database operations.
Connection pool settings: Azure SQL may apply connection pooling or firewall rules. If you see timeout errors, increase the connectionTimeout parameter in your connection strings to 30+ seconds.
Alternative: Use prisma migrate deploy: If you cannot create a shadow database, use only prisma migrate deploy and manage migrations manually through other tools. This is not recommended for team development but works for CI/CD pipelines.
Heroku PostgreSQL: If using Heroku with PostgreSQL instead of Azure SQL, you may need a similar workaround since Heroku also restricts database creation permissions.
P1013: The provided database string is invalid
The provided database string is invalid
P1000: Authentication failed against database server
Authentication failed against database server
P1010: User was denied access on the database
How to fix "P1010: User was denied access on the database" in Prisma
P5008: Usage exceeded, upgrade your plan (Accelerate)
How to fix "Usage exceeded, upgrade your plan" in Prisma Accelerate
P3021: Foreign keys cannot be created on this database
How to fix 'P3021: Foreign keys cannot be created on this database' in Prisma