Microsoft SQL Server

The Microsoft SQL Server data source connector connects Prisma ORM to a Microsoft SQL Server database server.

Example

To connect to a Microsoft SQL Server database, you need to configure a datasource block in your Prisma schema:

schema.prisma

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

The fields passed to the datasource block are:

• provider: Specifies the sqlserver data source connector.
• url: Specifies the connection URL for the Microsoft SQL Server database. In this case, an environment variable is used to provide the connection URL.

Connection details

The connection URL used to connect to an Microsoft SQL Server database follows the JDBC standard.

The following example uses SQL authentication (username and password) with an enabled TLS encrypted connection:

sqlserver://HOST[:PORT];database=DATABASE;user=USER;password=PASSWORD;encrypt=true

warning

Note: If you are using any of the following characters in your connection string, you will need to escape them.

:\=;/[]{}  # these are characters that will need to be escaped

To escape these characters, use curly braces {} around values that contain special characters. As an example:

sqlserver://HOST[:PORT];database=DATABASE;user={MyServer/MyUser};password={ThisIsA:SecurePassword;};encrypt=true

Arguments

Argument name	Required	Default	Comments
database initial catalog	No	master	The database to connect to.
username user uid userid	No - see Comments		SQL Server login (such as sa) or a valid Windows (Active Directory) username if integratedSecurity is set to true (Windows only).
password pwd	No - see Comments		Password for SQL Server login or Windows (Active Directory) username if integratedSecurity is set to true (Windows only).
encrypt	No	true	Configures whether to use TLS all the time, or only for the login procedure, possible values: true (use always), false (only for login credentials).
integratedSecurity	No		Enables Windows authentication (integrated security), possible values: true, false, yes, no. If set to true or yes and username and password are present, login is performed through Windows Active Directory. If login details are not given via separate arguments, the current logged in Windows user is used to login to the server.
connectionLimit	No	num_cpus * 2 + 1	Maximum size of the connection pool
connectTimeout	No	5	Maximum number of seconds to wait for a new connection
schema	No	dbo	Added as a prefix to all the queries if schema name is not the default.
loginTimeout connectTimeout connectionTimeout	No		Number of seconds to wait for login to succeed.
socketTimeout	No		Number of seconds to wait for each query to succeed.
isolationLevel	No		Sets transaction isolation level.
poolTimeout	No	10	Maximum number of seconds to wait for a new connection from the pool. If all connections are in use, the database will return a PoolTimeout error after waiting for the given time.
ApplicationName Application Name (case insensitive)	No		Sets the application name for the connection. Since version 2.28.0.
trustServerCertificate	No	false	Configures whether to trust the server certificate.
trustServerCertificateCA	No		A path to a certificate authority file to be used instead of the system certificates to authorize the server certificate. Must be either in pem, crt or der format. Cannot be used together with trustServerCertificate parameter.

Using integrated security (Windows only)

The following example uses the currently logged in Windows user to log in to Microsoft SQL Server:

sqlserver://localhost:1433;database=sample;integratedSecurity=true;trustServerCertificate=true;

The following example uses a specific Active Directory user to log in to Microsoft SQL Server:

sqlserver://localhost:1433;database=sample;integratedSecurity=true;username=prisma;password=aBcD1234;trustServerCertificate=true;

Connect to a named instance

The following example connects to a named instance of Microsoft SQL Server (mycomputer\sql2019) using integrated security:

sqlserver://mycomputer\sql2019;database=sample;integratedSecurity=true;trustServerCertificate=true;

Type mapping between Microsoft SQL Server to Prisma schema

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

Supported versions

See Supported databases.

Limitations and known issues

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 URL 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 URL must be changed before creating migrations (for example: schema=name).

Cyclic references

Circular references can occur between models when each model references another, creating a closed loop. When using a Microsoft SQL Server database, Prisma ORM will show a validation error if the referential action on a relation is set to something other than NoAction.

See Special rules for referential actions in SQL Server for more information.

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

CREATE DEFAULT catcat AS 'musti';

CREATE TABLE cats (
    id INT IDENTITY PRIMARY KEY,
    name NVARCHAR(1000)
);

sp_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 ORM manages default values is per table:

default_per_table.sql

CREATE TABLE cats (
    id INT IDENTITY PRIMARY KEY,
    name NVARCHAR(1000) CONSTRAINT DF_cat_name DEFAULT 'musti'
);

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

schema.prisma

model cats {
  id   Int     @id @default(autoincrement())
  name String? @default("musti")
}

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

schema.prisma

model cats {
  id   Int     @id @default(autoincrement())
  name String?
}

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)

Raw query considerations

Raw queries with String @db.VarChar(n) fields / VARCHAR(N) columns

String query parameters in raw queries are always encoded to SQL Server as NVARCHAR(4000) (if your String length is <= 4000) or NVARCHAR(MAX). If you compare a String query parameter to a column of type String @db.VarChar(N)/VARCHAR(N), this can lead to implicit conversion on SQL Server which affects your index performance and can lead to high CPU usage.

Here is an example:

model user {
  id Int @id
  name String @db.VarChar(40)
}

This query would be affected:

await prisma.$queryRaw`SELECT * FROM user WHERE name = ${"John"}`

To avoid the problem, we recommend you always manually cast your String query parameters to VARCHAR(N) in the raw query:

await prisma.$queryRaw`SELECT * FROM user WHERE name = CAST(${"John"} AS VARCHAR(40))`

This enables SQL Server to perform a Clustered Index Seek instead of a Clustered Index Scan.