Microsoft SQL Server Connector
The Microsoft SQL Connector is available as a Preview feature in 2.10.0. Extended native types support for SQL Server is available in 2.12.0 and later.
Enable SQL Server Preview feature
To enable the Microsoft SQL Server Preview feature:
Update the
clientanddatasourceblocks in your schema as shown:prisma/schema.prisma1generator client {2 provider = "prisma-client-js"3 previewFeatures = ["microsoftSqlServer"]4}56datasource db {7 provider = "sqlserver"8 url = env("DATABASE_URL")9}Generate Prisma Client:
$npx prisma generate
Alternatively, follow the Microsoft SQL Server tutorial.
Type mapping
| Prisma type | SQL Server types |
|---|---|
String | charvarcharnvarchartextntext |
Float | float(n)number(p,s)decimal(p,s)moneysmallmoneyreal |
DateTime | timedatedatetimedatetime2smalldatetimedatetimeoffset |
Boolean | bit |
Integer | tinyintsmallintintbigint |
Supported versions
See Supported databases.
Limitations and known issues
TLS required
Prisma Client requires TLS to connect to an Microsoft SQL Server database. Be aware that Apple are very specific about what kind of TLS server certificates are accepted, which means that MacOS users may not be able to connect to a SQL Server database using a weak TLS certificate.
To make local development easier on MacOS, you can add encrypt=DANGER_PLAINTEXT to the end of your connection string.
Note:
encrypt=DANGER_PLAINTEXTmust not be used in production.
Migrations are not supported yet
Follow issue #4074 on GitHub for more information.
Data model limitations
Cannot use column with UNIQUE constraint and filtered index as foreign key
Microsoft SQL Server only allows one NULL value in a column that has a UNIQUE constraint. For example:
- A table of users has a column named
license_number - The
license_numberfield has aUNIQUEconstraint - The
license_numberfield only allows oneNULLvalue
The standard way to get around this issue is to create a filtered unique index that excludes NULL values. This allows you to insert multiple NULL values. If you do not create an index in the database, you will get an error if you try to insert more than one null value into a column with Prisma Client.
However, creating an index makes it impossible to use license_number as a foreign key in the database (or a relation scalar field in corresponding Prisma Schema)
Azure SQL firewall connection policy must be proxy
Follow issue #88 on GitHub for more information.
