apps/docs/content/docs/(index)/prisma-orm/add-to-existing-project/sql-server.mdx
SQL Server is Microsoft's enterprise relational database management system known for its performance, security, and integration with Microsoft tools. In this guide, you will learn how to add Prisma ORM to an existing TypeScript project, connect it to SQL Server, introspect your existing database schema, and start querying with type-safe Prisma Client.
Navigate to your existing project directory and install the required dependencies:
npm install prisma @types/node @types/mssql --save-dev
npm install @prisma/client @prisma/adapter-mssql dotenv
Here's what each package does:
prisma - The Prisma CLI for running commands like prisma init, prisma db pull, and prisma 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 databasedotenv - Loads environment variables from your .env file@types/mssql - TypeScript type definitions for mssqlSet up your Prisma ORM project by creating your Prisma Schema file with the following command:
npx prisma init --datasource-provider sqlserver --output ../generated/prisma
This command does a few things:
prisma/ directory with a schema.prisma file containing your database connection configuration.env file in the root directory for environment variablesprisma.config.ts file for Prisma configurationThe 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 the .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 passwordRun the following command to introspect your existing database:
npx prisma db pull
This command reads the DATABASE_URL environment variable, connects to your database, and introspects the database schema. It then translates the database schema from SQL into a data model in your Prisma schema.
After introspection, your Prisma schema will contain models that represent your existing database tables.
To use Prisma Migrate with your existing database, you need to baseline your database.
First, create a migrations directory:
mkdir -p prisma/migrations/0_init
Next, generate the migration file with prisma migrate diff:
npx prisma migrate diff --from-empty --to-schema prisma/schema.prisma --script > prisma/migrations/0_init/migration.sql
Review the generated migration file to ensure it matches your database schema.
Then, mark the migration as applied:
npx prisma migrate resolve --applied 0_init
You now have a baseline for your current database schema.
Generate Prisma Client based on your introspected schema:
npx prisma generate
This creates a type-safe Prisma Client tailored to your database schema in the generated/prisma directory.
Create a utility file to instantiate Prisma Client. You need to pass an instance of the Prisma ORM driver adapter 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 };
Now you can use Prisma Client to query your database. Create a script.ts file:
import { prisma } from "./lib/prisma";
async function main() {
// Example: Fetch all records from a table
// Replace 'user' with your actual model name
const allUsers = await prisma.user.findMany();
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.ts
To make changes to your database schema:
Update your Prisma schema file to reflect the changes you want to make to your database schema. For example, add a new model:
model Post { // [!code ++]
id Int @id @default(autoincrement()) // [!code ++]
title String // [!code ++]
content String? // [!code ++]
published Boolean @default(false) // [!code ++]
authorId Int // [!code ++]
author User @relation(fields: [authorId], references: [id]) // [!code ++]
} // [!code ++]
model User { // [!code ++]
id Int @id @default(autoincrement()) // [!code ++]
email String @unique // [!code ++]
name String? // [!code ++]
posts Post[] // [!code ++]
} // [!code ++]
npx prisma migrate dev --name your_migration_name
This command will:
npx prisma studio
You've successfully set up Prisma ORM. Here's what you can explore next: