SQL Server

Source URL: https://docs.prisma.io/docs/orm/core-concepts/supported-databases/sql-server

SQL Server

Use Prisma ORM with Microsoft SQL Server databases

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

Setup

Configure the SQL Server provider in your Prisma schema:

schema.prisma

    datasource db {
      provider = "sqlserver"
    }

Set the connection URL in prisma.config.ts:

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

pnpm

yarn

bun

    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

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

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

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

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 withVARCHAR 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