Start from scratch with SQL Server
Follow this tutorial to use Prisma with a Microsoft SQL Server database.
Prerequisites
In order to successfully complete this guide, you need:
Node.js installed on your machine
A Microsoft SQL Server database
Make sure you have your database connection URL (including authentication credentials) available.
Create project setup
Create a project directory and navigate into it:
$$Initialize a TypeScript project and add the Prisma CLI as a development dependency:
$$
This creates a package.json
with an initial setup for your TypeScript app.
- Next, create a
tsconfig.json
file and add the following configuration to it:
tsconfig.json
123456789
- Invoke the Prisma CLI by prefixing it with
npx
- confirm that you can see the Prisma help screen:
$npx prisma
Set up your Prisma project by creating your Prisma schema file with the following command:
$
This command created a new directory called prisma
with the following contents:
`schema.prisma`: The Prisma schema with your database connection and the Prisma Client generator`.env`: A [dotenv](https://github.com/motdotla/dotenv) file for defining environment variables (used for your database connection)
Create a project directory and navigate into it:
$$Initialize a Node.js project and add the Prisma CLI as a development dependency:
$$
This creates a package.json
with an initial setup for a Node.js app.
- Invoke the Prisma CLI by prefixing it with
npx
- confirm that you can see the Prisma help screen:
$npx prisma
Set up your Prisma project by creating your Prisma schema file with the following command:
$
This command created a new directory called prisma
with the following contents:
`schema.prisma`: The Prisma schema with your database connection and the Prisma Client generator`.env`: A [dotenv](https://github.com/motdotla/dotenv) file for defining environment variables (used for your database connection)
Connect your database
To connect to your Microsoft SQL Server database:
Add the following line to the
client
block in your schema file to enable the Microsoft SQL Server Preview:prisma/schema.prisma1generator client {2 provider = "prisma-client-js"3 previewFeatures = ["microsoftSqlServer"]4}Set the
provider
andurl
fields of thedatasource
block in your Prisma schema as shown:
prisma/schema.prisma
1datasource db {2 provider = "sqlserver"3 url = env("DATABASE_URL")4}
Define the
DATABASE_URL
environment variable in theprisma/.env
file - this is the connection string to your database.The following example connection string uses SQL authentication, but there are other ways to format your connection string:
prisma/.env1DATABASE_URL =2 'sqlserver://localhost:1433;database=mydb;user=sa;password=r@ndomP@$$w0rd;trustServerCertificate=true'To get around TLS issues, add
encrypt=DANGER_PLAINTEXT
(not required in 2.15.0 and later if you are connecting to Microsoft SQL Server from MacOS specifically.Adjust the connection string to match your setup - see Microsoft SQL Server connection string for more information.
Make sure TCP/IP connections are enabled via SQL Server Configuration Manager to avoid
No connection could be made because the target machine actively refused it. (os error 10061)
Introspect your database to validate your connection string - the CLI will throw a
P4001
error because your database is empty:$npx prisma introspectShow CLI results
Create database tables with SQL
To create database tables, you can either:
- Use the
sqlcmd
command line tool (Docker) OR - Use SQL Server Managment Studio (Windows only)
Tip: Alternatively, try DataGrip, which supports multiple database engines.
Using sqlcmd
with Docker
To create tables using the sqlcmd
:
- Paste the following SQL query into a file named
create.sql
, replacingUSE sample
with the name of your database - for example,USE myDatabase
:
schema.sql
1234567891011121314151617181920212223242526
In your terminal, navigate to the folder with the
create.sql
file.Run the following command (change
sql1
to the name of your container) to copy thecreate.sql
from your local machine to your container's root folder:docker cp create.sql sql1:/Run the following command to start an interactive bash shell, replacing
sql1
with the name of your Docker container:
$docker exec -it sql1 "bash"
- Use the
sqlcmd
tool to run thecreate.sql
script, replacingmypassword
with your password:
$/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypassword" -i create.sql
To confirm that your tables were created, log in to
sqlcmd
:/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypassword"Run the following command (you may need to copy-paste each line):
$USE my_database_name$:setvar SQLCMDMAXVARTYPEWIDTH 15$:setvar SQLCMDMAXFIXEDTYPEWIDTH 15$sp_tables$@table_type="'TABLE'"$GOShow CLI results
Using SQL Server Management Studio (Windows only)
To create tables SQL Server Management Studio:
Log in to SQL Server Management Studio.
Click the New Query button:
Change
USE sample
to use name of your database (for example,USE myDatabase
) and paste it into the query window:
schema.sql
1234567891011121314151617181920212223242526
- Click the Execute button or press F5 on your keyboard to execute the query - you should see the following message:
Commands completed successfully.Completion time: 2020-10-25T10:55:16.0721284+01:00
- Expand the database node in the Object Explorer to confirm that your tables were created.
Introspect your database with Prisma
As a next step, you will introspect your database. The result of the introspection will be a data model inside your Prisma schema.
- Run the following command to introspect your database:
$
- Open
prisma.schema
to see your data model:
prisma/schema.prisma
1model Post {2 id Int @default(autoincrement()) @id3 createdAt DateTime @default(now())4 title String5 content String?6 published Boolean @default(false)7 User User @relation(fields: [authorId], references: [id])8 authorId Int9}1011model Profile {12 id Int @default(autoincrement()) @id13 bio String?14 User User @relation(fields: [userId], references: [id])15 userId Int @unique16}1718model User {19 id Int @default(autoincrement()) @id20 email String @unique21 name String?22 Post Post[]23 Profile Profile?24}
Adjust the data model
The introspected model contains auto-generated relation fields that do not adhere to Prisma's naming convention:
schema.prisma
1model User {2 id Int @default(autoincrement()) @id3 email String @unique4 name String?5 Post Post[] // Should be `posts`6 Profile Profile? // Should be `profile`7}
Field names affect the shape of the Prisma Client - for example, a property named Post
that it is a list of posts. To adjust the data model:
Change the field names as shown:
prisma/schema.prisma1model Post {2 id Int @default(autoincrement()) @id3 createdAt DateTime @default(now())4 title String5 content String?6 published Boolean @default(false)✎ author User @relation(fields: [authorId], references: [id])8 authorId Int9}1011model Profile {12 id Int @default(autoincrement()) @id13 bio String?✎ user User @relation(fields: [userId], references: [id])15 userId Int @unique16}1718model User {19 id Int @default(autoincrement()) @id20 email String @unique21 name String?✎ posts Post[]✎ profile Profile?24}Introspect again to confirm that Prisma does not overwrite your manual changes.
Install and generate Prisma Client
Prisma Client is an auto-generated, type-safe query builder based on your data model. To get started with Prisma Client:
- Install the
@prisma/client
package:
$
- Run the following command to generate your Prisma Client:
$
prisma generate
reads your Prisma schema and generates your Prisma Client library into a folder named .prisma/client
, which is referenced by node_modules/@prisma/client
.
Use the Prisma Client to read and write data
Create a file named
index.ts
and add the following sample code:import { PrismaClient } from '@prisma/client'const prisma = new PrismaClient()async function main() {// Create a user and two postsconst createUser = await prisma.user.create({data: {name: 'Alice',email: 'alice@prisma.io',posts: {create: [{ title: 'My first day at Prisma' },{title: 'How to create an Microsoft SQL Server database',content: 'A tutorial in progress!',},],},},})// Return all postsconst getPosts = await prisma.post.findMany({})console.log(getPosts)}main().catch(e => {throw e}).finally(async () => {await prisma.$disconnect()})Run the code with the following command:
$npx ts-node index.ts
You should see the following output:
[{id: 1,title: 'My first day at Prisma',createdAt: 2020-10-26T08:24:10.966Z,content: null,published: false,authorId: 1{id: 2,title: 'How to create an Microsoft SQL Server database',createdAt: 2020-10-26T08:24:10.966Z,content: 'A tutorial in progress!',published: false,authorId: 1}]
- Change
email: "alice@prisma.io"
to another email address and run the code again to create another user and list of posts.
Create a file named
index.js
and add the following sample code:const { PrismaClient } = require('@prisma/client')const prisma = new PrismaClient()async function main() {// Create a user and two postsconst createUser = await prisma.user.create({data: {name: 'Alice',email: 'alice@prisma.io',posts: {create: [{ title: 'My first day at Prisma' },{title: 'How to create an Microsoft SQL Server database',content: 'A tutorial in progress!',},],},},})// Return all postsconst getPosts = await prisma.post.findMany({})console.log(getPosts)}main().catch(e => {throw e}).finally(async () => {await prisma.$disconnect()})Run the code with the following command:
$node index.js
You should see the following output:
[{id: 1,title: 'My first day at Prisma',createdAt: 2020-10-26T08:24:10.966Z,content: null,published: false,authorId: 1{id: 2,title: 'How to create an Microsoft SQL Server database',createdAt: 2020-10-26T08:24:10.966Z,content: 'A tutorial in progress!',published: false,authorId: 1}]
- Change
email: "alice@prisma.io"
to another email address and run the code again to create another user and list of posts.