Prisma's support for this database is real, narrower than its PostgreSQL support, and it moved in Prisma 7. A schema written from PostgreSQL habits fails validation, and a migration workflow proved against a local database is refused in the cloud.
Measured on 2026-09-03 against prisma 7.9.1 and 7.10.0, @prisma/adapter-mssql 7.9.1 and prisma
8.0.0-rc.12, all installed from the package index. The P3020 result below was measured on 2026-08-27
against an Azure SQL Database engine reporting EngineEdition 5.
The datasource provider is the literal string sqlserver. That is a connector identifier, not the
name of the product, whose limits are not SQL Server's: everywhere else it is Azure SQL Database.
Pin the version, because the newest has no connector at all
npm view prisma dist-tags # latest: 8.0.0-rc.12 prev: 7.10.0
npm install prisma@7 @prisma/client@7 @prisma/adapter-mssql@7
latest is a release candidate for the next major, so a bare npm install prisma installs it. The
string sqlserver appears nowhere in that package, only postgresql, and its command surface is a
different product: no validate, no db push, no migrate, and migrate diff replaced by a
migration group. Pin to 7 and keep prisma and @prisma/client equal.
The connection URL is JDBC style, not a URI
Every other Prisma connector takes a URI. This one does not:
# rejected: P1013, "Conversion error: invalid digit found in string in database URL"
sqlserver://user:password@<server-name>.database.windows.net:1433/appdb
# accepted
sqlserver://<server-name>.database.windows.net:1433;database=appdb;user=app;password=<secret>;encrypt=true
The error blames a digit and never mentions the shape, so it reads as a typo. An ADO.NET style
Server=...;Database=... string is rejected too, with must start with the protocol sqlserver://.
Prisma 7 moved the URL out of the schema file
A url inside the datasource block now fails prisma validate with P1012, pointing at the line:
The datasource property `url` is no longer supported in schema files.
The rest of that message sends the URL to prisma.config.ts and the adapter to the client:
// prisma.config.ts
import 'dotenv/config' // nothing else loads .env
import { defineConfig, env } from 'prisma/config'
export default defineConfig({
schema: 'prisma/schema.prisma',
datasource: {
url: env('DATABASE_URL'),
shadowDatabaseUrl: env('SHADOW_DATABASE_URL'),
},
})
env() is strict and fails the whole command, including prisma validate, which needs no database:
PrismaConfigEnvError: Cannot resolve environment variable: DATABASE_URL. The dotenv/config import
is what stops that; the configuration file loads no .env on its own.
The client needs a driver adapter as well. @prisma/adapter-mssql wraps mssql ^12.2.0, the Node
driver connect-from-typescript-and-node owns.
What the connector refuses, before any database is involved
./node_modules/.bin/prisma validate
Three field kinds are rejected outright, all three reproduced on 7.9.1:
error: Field `payload` in model `Thing` can't be of type Json. The current connector does not support the Json type.
error: Field "tags" in model "Thing" can't be a list. The current connector does not support lists of primitive types.
error: Error validating: You defined the enum `Status`. But the current connector does not support enums.
Instead: JSON is a string column with the shape enforced in application code, since the database's own JSON handling needs a raw query; an enum is a lookup table with a relation, the form that survives contact with reporting; a scalar list is a child table.
The default string length, and the failure it defers
Read the DDL Prisma will emit before it runs. This needs a parseable URL and opens no connection:
./node_modules/.bin/prisma migrate diff --from-empty --to-schema=prisma/schema.prisma --script
For id String @id and email String @unique with no native type, that prints:
CREATE TABLE [Account]
(
[id] NVARCHAR(1000) NOT NULL,
[email] NVARCHAR(1000) NOT NULL,
CONSTRAINT [Account_pkey] PRIMARY KEY CLUSTERED ([id]),
CONSTRAINT [Account_email_key] UNIQUE NONCLUSTERED ([email])
);
1000 Unicode characters is 2000 bytes, against index key limits of 1700 bytes nonclustered
and 900 bytes clustered. Nothing fails at migration time. The engine emits Msg 1945 at
severity 10, which succeeds:
Warning! The maximum key length for a nonclustered index is 1700 bytes.
The index 'Account_email_key' has maximum length of 2000 bytes.
For some combination of large values, the insert/update operation will fail.
Severity 10 sets no non-zero exit, so sqlcmd -b alone reports a clean migration and the pipeline
goes green. Without -m-1 the message prints no Msg number at all, and on go-sqlcmd it prints no
number with -m-1 either, which the check section below explains. The bill arrives later, on a
genuinely long value:
Msg 1946, Level 16, State 3
Operation failed. The index entry of length 1800 bytes for the index 'Account_email_key'
exceeds the maximum length of 1700 bytes for nonclustered indexes.
String @id is worse: a clustered primary key against the 900 byte limit, again with a warning only.
So size every string that carries a key, a unique constraint or a foreign key.
model Account {
id String @id @db.NVarChar(64)
email String @unique @db.NVarChar(320)
bio String? @db.NVarChar(Max)
}
Re-running the diff emits NVARCHAR(64), NVARCHAR(320) and NVARCHAR(max). 450 characters is the
widest value safe in every index position; anything outside an index can be longer, and unbounded
text belongs in NVarChar(Max) rather than a large fixed default.
One more PostgreSQL habit, same area: a nullable unique column accepts one null row only. The
second fails Msg 2627, The duplicate key value is (<NULL>). A model with several optional unique
fields works on other engines and does not work here.
That number depends on what Prisma built. @unique and @@unique emit
ADD CONSTRAINT ... UNIQUE NONCLUSTERED, a constraint, on both the create and the alter path,
though Prisma labels the step -- CreateIndex in the migration. A constraint violation is
Msg 2627; a hand-written CREATE UNIQUE INDEX raises Msg 2601. Match the number to the object
or you search for the wrong one. Key design in general is design-azure-sql-schema.
Migrations, and the one that will not run in the cloud
prisma migrate dev needs a shadow database: a second temporary database it creates, replays
history into, inspects and drops, so it can detect drift. prisma migrate deploy does not use one.
Against Azure SQL Database, Prisma will not create it and stops with a dedicated error:
P3020
The automatic creation of shadow databases is disabled on Azure SQL.
Please set up a shadow database using the `shadowDatabaseUrl` datasource attribute.
Three things follow.
- A local success proves nothing, and this is not a permission problem. Against the local Azure
SQL Database container the shadow database is created and dropped and
migrate devworks. The same schema and the same administrative login return P3020 in the cloud, so granting rights fixes nothing, and a pipeline is where this is found. - The error names a place that no longer exists. "datasource attribute" was the Prisma 6
location. In Prisma 7
shadowDatabaseUrlsits in the configuration file besideurl. - The fix is a second database, not a grant. Provision another database on the same logical
server and point
SHADOW_DATABASE_URLat it; Prisma refuses if the two match, withThe shadow database you configured appears to be the same as the main database.Creating one needs the server administrator, the Entra administrator or the database manager role, which an application login is not: that isprovision-azure-sql-db.
| Situation | Command | Shadow database |
|---|---|---|
| Iterating against the container | prisma migrate dev |
Created and dropped for you |
| Iterating against a cloud database | prisma migrate dev |
You provide it, once |
| Prototyping with no migration history | prisma db push |
Not used |
| Deploying | prisma migrate deploy |
Not used |
Never run migrate dev against a database holding real data: it is the command that resets. Whether
a change is safe for a live database at all is schema-migrations-safely.
Getting an identity to the client
The adapter supports Microsoft Entra ID and the Prisma documentation does not mention it, so an agent
working from the docs alone concludes it is impossible. Read out of the installed 7.9.1 package, the
connection string accepts authentication=DefaultAzureCredential,
authentication=ActiveDirectoryManagedIdentity with an optional client id, and
authentication=ActiveDirectoryServicePrincipal; the object form is
authentication: { type: 'azure-active-directory-default' }.
The command line is a separate path. Migrations connect through the configuration file's URL,
whose documented arguments are a user and a password, so plan for them to run under a login even
where the application is passwordless. The identity still needs a database principal, which is
entra-id-auth.
Check it worked
The schema is one the connector accepts. With DATABASE_URL exported:
./node_modules/.bin/prisma validate
Expect exactly The schema at prisma/schema.prisma is valid 🚀. Any P1012 block is a Json field,
an enum or a scalar list; a PrismaConfigEnvError means the dotenv/config import is missing.
No column is heading for Msg 1946. Read the DDL before it runs:
./node_modules/.bin/prisma migrate diff --from-empty --to-schema=prisma/schema.prisma --script | grep -c 'NVARCHAR(1000)'
Expect 0. Each match is one unsized String, and any of them in a key, unique constraint or
foreign key is a migration that warns and an insert that fails later.
Nothing already migrated is over the limit. Against the migrated database:
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C -b -m-1 -Q \
"SELECT t.name AS tbl, i.name AS ix, i.type_desc, SUM(c.max_length) AS key_bytes
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.is_included_column = 0
JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
JOIN sys.tables AS t ON t.object_id = i.object_id
GROUP BY t.name, i.name, i.type_desc
HAVING SUM(c.max_length) > CASE WHEN i.type_desc = 'CLUSTERED' THEN 900 ELSE 1700 END;"
Expect zero rows. Each row is an index over its limit, key_bytes 2000 being the unsized default.
Keep -m-1: without it the create-time warning carries no Msg number, which is how these ship.
-m-1 is an ODBC sqlcmd instruction, meaning the 18.x build from mssql-tools18 or the
Microsoft command line utilities. Measured 2026-09-05, go-sqlcmd 1.10.0, the 1.x build
brew install sqlcmd and winget install sqlcmd install, prints no Msg header on a severity 10
message at any -m value, so on that build the 1945 warning prisma migrate deploy provokes stays
unnumbered and the migration still reads as clean. Run the migration through the ODBC build when the
number is what you are grepping for.
Do not
- Do not answer a
Json,enumor scalar-list validation error with a raw query. Model it as a column or a table. - Do not read a green local migration as evidence that the cloud will accept it, and do not answer
P3020 with a permission grant or by pointing
shadowDatabaseUrlat the database holding your data. - Do not skip certificate validation, and do not write a retry loop here. Encryption and
transient-fault policy are one policy for every stack:
connect-to-azure-sql.
References
- Prisma SQL Server connector: the type mapping table and every connection URL argument with its default. Fetch it before adding an argument to the URL above.
- Shadow database:
what
migrate devdoes with it, and the cloud-hosted setup. Read it before configuring one. - Prisma error reference: the definitive text for P3020, P1012 and P1013. Read it when a P-code appears.
- CREATE INDEX: the 900 and 1700 byte key limits in the engine's own words. Read it to confirm the rule, not the text.
- Database Engine events and errors 1000 to 1999: 1945 at severity 10 and 1946 at severity 16. Read it before asserting either number.
connect-from-typescript-and-node: themssqldriver the adapter wraps. Open it before configuring the adapter's pool, which is that driver's.connect-to-azure-sql: encryption, retry, transient faults. Open it the moment one appears.design-azure-sql-schema: key length, sizing, collation. Open it before the first migration, not after the firstMsg 1946.schema-migrations-safely: the doctrine Prisma Migrate inherits, including why a pipeline reports success on a migration that only warned.provision-azure-sql-db: creating the second database P3020 asks for. Open it on that error.