# Prisma Azure SQL

> Use when using Prisma with Azure SQL Database: schema.prisma, prisma migrate failing in Azure (P3020, shadow database), no Json type or enums, url unsupported in Prisma 7, driver adapter.

- Skill: `microsoft/prisma-azure-sql` (Agent Skill)
- Install (CLI): `npx get-skill add microsoft/prisma-azure-sql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/prisma-azure-sql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/prisma-azure-sql

---


# Prisma on Azure SQL Database

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.

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

```bash
npm view prisma dist-tags
npm install prisma@7 @prisma/client@7 @prisma/adapter-mssql@7
```

The `latest` tag of `prisma` can point at a prerelease of the next major, a release candidate or
a dev build, so a bare `npm install prisma` installs it. `@prisma/client`'s `latest` can still be
a 7.x release at the same time, so a bare install of both can mix majors. The tags move; read them before
installing. A Prisma 8 prerelease such as 8.0.0-rc.12 has no `sqlserver` connector, 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:

```bash
# 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:

```ts
// 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

```bash
./node_modules/.bin/prisma validate
```

Prisma 7 rejects three field kinds outright:

```
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:

```bash
./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:

```sql
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.**

```prisma
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.

1. **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 dev` works. 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.
2. **The error names a place that no longer exists.** "datasource attribute" was the Prisma 6
   location. In Prisma 7 `shadowDatabaseUrl` sits in the configuration file beside `url`.
3. **The fix is a second database, not a grant.** Provision another database on the same logical
   server and point `SHADOW_DATABASE_URL` at it; Prisma refuses if the two match, with `The 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 is `provision-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 although the Prisma documentation does not mention it, so
the docs alone make it look impossible. In the `@prisma/adapter-mssql` 7.9 package source, 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:

```bash
./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:

```bash
./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:

```bash
sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -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. go-sqlcmd, 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`, `enum` or 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 `shadowDatabaseUrl` at 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](https://www.prisma.io/docs/orm/overview/databases/sql-server): the
  type mapping table and every connection URL argument with its default. Fetch it before adding an
  argument to the URL above.
- [Shadow database](https://www.prisma.io/docs/orm/prisma-migrate/understanding-prisma-migrate/shadow-database):
  what `migrate dev` does with it, and the cloud-hosted setup. Read it before configuring one.
- [Prisma error reference](https://www.prisma.io/docs/orm/reference/error-reference): the definitive
  text for P3020, P1012 and P1013. Read it when a P-code appears.
- [CREATE INDEX](https://learn.microsoft.com/sql/t-sql/statements/create-index-transact-sql): 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](https://learn.microsoft.com/sql/relational-databases/errors-events/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`: the `mssql` driver 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 first `Msg 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.

