Microsoft SQL Server connector

The Microsoft SQL Connector is available as a Preview feature in 2.10.0.

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 between Microsoft SQL Server to Prisma schema

For type mappings organized by Prisma type, refer to the Prisma schema reference documentation.

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 (not required in 2.15.0 and later).

Note: encrypt=DANGER_PLAINTEXT must not be used in production.

Prisma Migrate caveats

Prisma Migrate is supported in 2.13.0 and later with the following caveats:

Database schema names

SQL Server does not have an equivalent to the PostgreSQL SET search_path command familiar from PostgreSQL. This means that when you create migrations, you must define the same schema name in the connection string that is used by the production database. For most of the users this is dbo (the default value). However, if the production database uses another schema name, all the migration SQL must be either edited by hand to reflect the production or the connection string must be changed before creating migrations (for example: schema=name).

Cascading rules

SQL Server follows the same cascading rules as other database providers - for most relations (with certain restrictions), Prisma runs ON DELETE CASCADE ON UPDATE CASCADE (delete all children from a parent automatically).

However, SQL Server cascading has certain restrictions that do not allow us to create certain kinds of relations. Consider the following schema:

schema.prisma
1model Album {
2 id Int @id @default(autoincrement())
3 artist_id Int
4 artist Artist @relation(fields: [artist_id], references: [id])
5}
6
7model Artist {
8 id Int @id @default(autoincrement())
9 albums Album[]
10}

Next, add a favorite album to every artist:

schema.prisma
1model Artist {
2 id Int @id @default(autoincrement())
3 album_id Int
4 favorite_album Album @relation(fields: [album_id], references: [id], name: "FK_fav_album")
5 albums Album[]
6}

This results in an error:

Introducing FOREIGN KEY constraint `FK__Artist__album_id__2D3D91F9` on table `Artist` may cause cycles or multiple cascade paths. Specify `ON DELETE NO ACTION` or `ON UPDATE NO ACTION`, or modify other FOREIGN KEY constraints.

SQL Server does not allow cyclic cascading, and in our example our relations go from Album to Artist and through another relation from Artist back to Album. In Microsoft's words, the cause for this is:

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

Another, simpler example that is not possible in the first version of Prisma Migrate for SQL Server is a self-reference:

schema.prisma
1model Artist {
2 id Int @id @default(autoincrement())
3 muse_id Int?
4 muse Artist @relation(fields: [muse_id], references: [id])
5}

If you want to use relations as described here, you must:

  • Create the migration using the --create-only flag
  • Modify the cascading rules manually before writing to the database

Destructive changes

Certain migrations will cause more changes than you might expect. For example:

  • Adding or removing autoincrement(). This cannot be achieved by modifying the column, but requires recreating the table (including all constraints, indices, and foreign keys) and moving all data between the tables.
  • Additionally, it is not possible to delete all the columns from a table (possible with PostgreSQL or MySQL). If a migration needs to recreate all table columns, it will also re-create the table.

Shared default values are not supported

In some cases, user might want to define default values as shared objects:

default_objects.sql
1CREATE DEFAULT catcat AS 'musti';
2
3CREATE TABLE cats (
4 id INT IDENTITY PRIMARY KEY,
5 name NVARCHAR(1000)
6);
7
8sp_bindefault 'catcat', 'dbo.cats.name';

Using the stored procedure sp_bindefault, the default value catcat can be used in more than one table. The way Prisma manages default values is per table:

default_per_table.sql
1CREATE TABLE cats (
2 id INT IDENTITY PRIMARY KEY,
3 name NVARCHAR(1000) CONSTRAINT DF_cat_name DEFAULT 'musti'
4);

The last example, when introspected, leads to the following model:

schema.prisma
1model cats {
2 id Int @id @default(autoincrement())
3 name String? @default("musti")
4}

And the first doesn't get the default value introspected:

schema.prisma
1model cats {
2 id Int @id @default(autoincrement())
3 name String?
4}

If using Prisma Migrate together with shared default objects, changes to them must be done manually to the SQL.

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)

Edit this page on GitHub