SQL Server

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

Setup

Configure the SQL Server provider in your Prisma schema:

datasource db {
  provider = "sqlserver"
}

Set the connection URL in 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

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

npm install @prisma/adapter-mssql

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

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

:::warning[Prisma ORM v7: Connection pool defaults changed]

Driver adapters use mssql driver defaults which differ from v6:

• Connection timeout: 15s (vs v6's 5s)
• Idle timeout: 30s (vs v6's 300s)

See connection pool guide for configuration.

:::

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

See full type mapping reference for complete details.

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

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

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