SQL Server
Create a new TypeScript project from scratch by connecting Prisma ORM to SQL Server and generating a Prisma Client for database access.
Microsoft SQL Server is an enterprise-grade relational database. In this guide, you will learn how to set up a new TypeScript project from scratch, connect it to SQL Server using Prisma ORM, and generate a Prisma Client for easy, type-safe access to your database.
Prerequisites
You also need:
- A Microsoft SQL Server database
- Database connection details (host, port, username, password, database name)
1. Create a new project
2. Install required dependencies
Install the packages needed for this quickstart:
npm install prisma @types/node @types/mssql --save-devnpm install @prisma/client @prisma/adapter-mssql dotenvHere's what each package does:
prisma- The Prisma CLI for running commands likeprisma init,prisma migrate, andprisma generate@prisma/client- The Prisma Client library for querying your database@prisma/adapter-mssql- The SQL Server driver adapter that connects Prisma Client to your database@types/mssql- TypeScript type definitions for mssqldotenv- Loads environment variables from your.envfile
3. Configure ESM support
Update tsconfig.json for ESM compatibility:
{
"compilerOptions": {
"module": "ESNext",
"moduleResolution": "node",
"target": "ES2023",
"strict": true,
"esModuleInterop": true,
"ignoreDeprecations": "6.0"
}
}Update package.json to enable ESM:
{
"type": "module"
}4. Initialize Prisma ORM
You can now invoke the Prisma CLI by prefixing it with npx:
npx prismaNext, set up your Prisma ORM project by creating your Prisma Schema file with the following command:
npx prisma init --datasource-provider sqlserver --output ../generated/prismaThis command does a few things:
- Creates a
prisma/directory with aschema.prismafile containing your database connection and schema models - Creates a
.envfile in the root directory for environment variables - Creates a
prisma.config.tsfile for Prisma configuration
The generated prisma.config.ts file looks like this:
import "dotenv/config";
import { defineConfig, env } from "prisma/config";
export default defineConfig({
schema: "prisma/schema.prisma",
migrations: {
path: "prisma/migrations",
},
datasource: {
url: env("DATABASE_URL"),
},
});The generated schema uses the ESM-first prisma-client generator with a custom output path:
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "sqlserver"
}Update your .env file with your SQL Server connection string details:
DATABASE_URL="sqlserver://localhost:1433;database=mydb;user=username;password=password;encrypt=true"
DB_USER="username" // [!code ++]
DB_PASSWORD="password" // [!code ++]
DB_NAME="mydb" // [!code ++]
HOST="localhost" // [!code ++]Replace the placeholders with your actual database credentials:
localhost:1433: Your SQL Server host and portmydb: Your database nameusername: Your SQL Server usernamepassword: Your SQL Server password
5. Define your data model
Open prisma/schema.prisma and add the following models:
generator client {
provider = "prisma-client"
output = "../generated/prisma"
}
datasource db {
provider = "sqlserver"
}
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
title String
content String?
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId Int
} 6. Create and apply your first migration
Create your first migration to set up the database tables:
npx prisma migrate dev --name initThis command creates the database tables based on your schema.
Now run the following command to generate the Prisma Client:
npx prisma generate7. Instantiate Prisma Client
Now that you have all the dependencies installed, you can instantiate Prisma Client. You need to pass an instance of Prisma ORM's driver adapter to the PrismaClient constructor:
import "dotenv/config";
import { PrismaMssql } from "@prisma/adapter-mssql";
import { PrismaClient } from "../generated/prisma/client";
const sqlConfig = {
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
server: process.env.HOST,
pool: {
max: 10,
min: 0,
idleTimeoutMillis: 30000,
},
options: {
encrypt: true, // for azure
trustServerCertificate: false, // change to true for local dev / self-signed certs
},
};
const adapter = new PrismaMssql(sqlConfig);
const prisma = new PrismaClient({ adapter });
export { prisma };8. Write your first query
Create a script.ts file to test your setup:
import { prisma } from "./lib/prisma";
async function main() {
// Create a new user with a post
const user = await prisma.user.create({
data: {
name: "Alice",
email: "alice@prisma.io",
posts: {
create: {
title: "Hello World",
content: "This is my first post!",
published: true,
},
},
},
include: {
posts: true,
},
});
console.log("Created user:", user);
// Fetch all users with their posts
const allUsers = await prisma.user.findMany({
include: {
posts: true,
},
});
console.log("All users:", JSON.stringify(allUsers, null, 2));
}
main()
.then(async () => {
await prisma.$disconnect();
})
.catch(async (e) => {
console.error(e);
await prisma.$disconnect();
process.exit(1);
});Run the script:
npx tsx script.tsYou should see the created user and all users printed to the console!