Skip to main content
Prisma Documentation Docs

Search documentation

Type to search this documentation.

On this pageOverview

SQL Server

Prisma ORM supports Microsoft SQL Server (2017+) databases.

Setup

Section titled “Setup”

Configure the SQL Server provider in your Prisma schema:

title="schema.prisma"
datasource db {

  provider = "sqlserver"

}

Set the connection URL in prisma.config.ts:

title="prisma.config.ts"
import { defineConfig, env } from "prisma/config";

export default defineConfig({

  schema: "prisma/schema.prisma",

  datasource: {

    url: env("DATABASE_URL"), // sqlserver://host:1433;database=db;...

  },

});

Using driver adapters

Section titled “Using driver adapters”

Use the node-mssql JavaScript database driver via driver adapters:

bun add @prisma/adapter-mssql
Bash
pnpm add @prisma/adapter-mssql
Bash
yarn add @prisma/adapter-mssql
Bash
npm install @prisma/adapter-mssql
TypeScript
import { PrismaMssql } from "@prisma/adapter-mssql";
import { PrismaClient } from "./generated/prisma";

const config = {
  server: "localhost",
  port: 1433,
  database: "mydb",
  user: "sa",
  password: "mypassword",
  options: {
    encrypt: true,
    trustServerCertificate: true, // For self-signed certificates
  },
};

const adapter = new PrismaMssql(config);
const prisma = new PrismaClient({ adapter });
import { PrismaMssql } from "@prisma/adapter-mssql";

import { PrismaClient } from "./generated/prisma";

const config = {

  server: "localhost",

  port: 1433,

  database: "mydb",

  user: "sa",

  password: "mypassword",

  options: {

    encrypt: true,

    trustServerCertificate: true, // For self-signed certificates

  },

};

const adapter = new PrismaMssql(config);

const prisma = new PrismaClient({ adapter });

Connection details

Section titled “Connection details”

SQL Server uses JDBC-style connection strings:

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

Escaping special characters:

If your credentials contain : \ = ; / [ ] { }, wrap values in curly braces:

sqlserver://host:1433;user={MyServer/User};password={Pass:Word;};database=db

Connection string arguments

Section titled “Connection string arguments”
Argument Default Description
database / initial catalog master Database name
user / username / uid SQL Server login or Windows username (if using integratedSecurity)
password / pwd Password for user
encrypt true Use TLS: true (always), false (login only)
integratedSecurity Windows authentication: true, false, yes, no
schema dbo Schema prefix for all queries
connectTimeout 5 Seconds to wait for connection
socketTimeout Seconds to wait for each query
poolTimeout 10 Seconds to wait for connection from pool
trustServerCertificate false Trust server certificate without validation
trustServerCertificateCA Path to CA certificate file (.pem, .crt, .der)
ApplicationName Application name for the connection

Windows authentication

Section titled “Windows authentication”

Using current Windows user:

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

Using specific Active Directory user:

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

Named instance:

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

Type mappings

Section titled “Type mappings”
Prisma SQL Server
String NVARCHAR(1000)
Boolean BIT
Int INT
BigInt BIGINT
Float FLOAT(53)
Decimal DECIMAL(32,16)
DateTime DATETIME2
Json Not supported
Bytes VARBINARY(MAX)

See full type mapping reference for complete details.

Common considerations

Section titled “Common considerations”

UNIQUE constraints:

SQL Server allows only one NULL value per UNIQUE constraint. Use filtered indexes to work around this, but note they cannot be used as foreign keys.

Cyclic references:

With circular model references, you must use NoAction referential actions to avoid validation errors.

Raw queries with VARCHAR columns:

String parameters in raw queries are encoded as NVARCHAR(4000) or NVARCHAR(MAX). When querying VARCHAR(N) columns, manually cast to avoid index performance issues:

// ❌ Causes implicit conversion

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

// ✅ Enables index seek

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

Prisma Migrate caveats

Section titled “Prisma Migrate caveats”

Schema names:

SQL Server doesn't have SET search_path. Ensure your connection URL schema parameter matches production (typically dbo):

sqlserver://host:1433;database=db;schema=dbo;...

Destructive changes:

Some operations require table recreation:

  • Adding/removing autoincrement()
  • Dropping all columns from a table

Shared default values:

Prisma doesn't support SQL Server's sp_bindefault. Use per-column defaults instead.

Local setup

Section titled “Local setup”

Windows:

  1. Install SQL Server 2019 Developer
  2. Install SQL Server Management Studio
  3. Enable TCP/IP in SQL Server Configuration Manager → Protocols for MSSQLSERVER
  4. (Optional) Enable SQL authentication: Properties → Security → SQL Server and Windows Authentication Mode

Docker:

docker pull mcr.microsoft.com/mssql/server:2019-latest

docker run --name sql_container \

  -e 'ACCEPT_EULA=Y' \

  -e 'SA_PASSWORD=myPassword' \

  -p 1433:1433 \

  -d mcr.microsoft.com/mssql/server:2019-latest

Connect with: Username sa, password myPassword, port 1433

Suggest an edit

Propose a replacement for this page. The site team reviews it before applying any changes.

Export
Documentation menu