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 file:
datasource db {
provider = "sqlserver"
url = env("DATABASE_URL")
}
The fields passed to the datasource
block are:
provider
: Specifies thesqlserver
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
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
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;
Using SQL Browser to 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;
Arguments
Argument name | Required | Default | Comments |
---|---|---|---|
| No | master | The database to connect to. |
| No - see Comments | SQL Server login (such as sa ) or a valid Windows (Active Directory) username if integratedSecurity is set to true (Windows only). | |
| 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. |
| 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. |
| 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. |
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.