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:

  1. Update the client and datasource blocks in your schema as shown:

    prisma/schema.prisma
    1generator client {
    2 provider = "prisma-client-js"
    3 previewFeatures = ["microsoftSqlServer"]
    4}
    5
    6datasource db {
    7 provider = "sqlserver"
    8 url = env("DATABASE_URL")
    9}
  2. Generate Prisma Client:

    $npx prisma generate

Alternatively, follow the Microsoft SQL Server tutorial.

Type mapping

Prisma typeSQL Server types
Stringchar
varchar
nvarchar
text
ntext
Floatfloat(n)
number(p,s)
decimal(p,s)
money
smallmoney
real
DateTimetime
date
datetime
datetime2
smalldatetime
datetimeoffset
Booleanbit
Integertinyint
smallint
int
bigint

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_PLAINTEXT must 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_number field has a UNIQUE constraint
  • The license_number field only allows one NULL value

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.

Edit this page on GitHub