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
client
anddatasource
blocks 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
Type mappings with nativeTypes
preview enabled
Type mappings with nativeTypes
preview disabled
Prisma type | Introspection | Migrate (Preview) |
---|---|---|
String | char varchar nvarchar text ntext | nvarchar(1000) * |
Float | float(n) number(p,s) decimal(p,s) money smallmoney real | decimal(32,16) |
DateTime | time date datetime datetime2 smalldatetime datetimeoffset | datetime2 |
Boolean | bit | bit |
Integer | tinyint smallint int bigint | int |
Note: To change
1000
to something else, generate a draft migration and edit the SQL before applying it to the database.
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 (Preview) caveats
Prisma Migrate (Preview) 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:
model Album {id Int @id @default(autoincrement())artist_id Intartist Artist @relation(fields: [artist_id], references: [id])}model Artist {id Int @id @default(autoincrement())albums Album[]}
Next, add a favorite album to every artist:
model Artist {id Int @id @default(autoincrement())album_id Intfavorite_album Album @relation(fields: [album_id], references: [id], name: "FK_fav_album")albums Album[]}
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:
model Artist {id Int @id @default(autoincrement())muse_id Int?muse Artist @relation(fields: [muse_id], references: [id])}
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.
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 aUNIQUE
constraint - The
license_number
field only allows oneNULL
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.