Start from scratch with SQL Server

JavaScript

Follow this tutorial to use Prisma with a Microsoft SQL Server database.

Prerequisites

In order to successfully complete this guide, you need:

Make sure you have your database connection URL (including authentication credentials) available.

Create project setup

  1. Create a project directory and navigate into it:

    $mkdir hello-prisma
    $cd hello-prisma
  2. Initialize a TypeScript project and add the Prisma CLI as a development dependency:

    $npm init -y
    $npm install @prisma/cli typescript ts-node @types/node --save-dev

    This creates a package.json with an initial setup for your TypeScript app.

  3. Next, create a tsconfig.json file and add the following configuration to it:

    tsconfig.json
    1{
    2 "compilerOptions": {
    3 "sourceMap": true,
    4 "outDir": "dist",
    5 "strict": true,
    6 "lib": ["esnext"],
    7 "esModuleInterop": true
    8 }
    9}
  4. Invoke the Prisma CLI by prefixing it with npx - confirm that you can see the Prisma help screen:

    $npx prisma
  5. Set up your Prisma project by creating your Prisma schema file with the following command:

    $npx prisma init

    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 file for defining environment variables (used for your database connection)
  1. Create a project directory and navigate into it:

    $mkdir hello-prisma
    $cd hello-prisma
  2. Initialize a Node.js project and add the Prisma CLI as a development dependency:

    $npm init -y
    $npm install @prisma/cli --save-dev

    This creates a package.json with an initial setup for a Node.js app.

  3. Invoke the Prisma CLI by prefixing it with npx - confirm that you can see the Prisma help screen:

    $npx prisma
  4. Set up your Prisma project by creating your Prisma schema file with the following command:

    $npx prisma init

    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 file for defining environment variables (used for your database connection)

Connect your database

To connect to your Microsoft SQL Server database:

  1. Add the following line to the client block in your schema file to enable the Microsoft SQL Server Preview:

    prisma/schema.prisma
    1generator client {
    2 provider = "prisma-client-js"
    3 previewFeatures = ["microsoftSqlServer"]
    4}
  2. Set the provider and url fields of the datasource block in your Prisma schema as shown:

    prisma/schema.prisma
    1datasource db {
    2 provider = "sqlserver"
    3 url = env("DATABASE_URL")
    4}
  3. Define the DATABASE_URL environment variable in the prisma/.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/.env
    1DATABASE_URL="sqlserver://localhost:1433;database=mydb;user=sa;password=r@ndomP@$$w0rd;trustServerCertificate=true"

    To get around TLS issues, add encrypt=DANGER_PLAINTEXT if you are connecting to Microsoft SQL Server from MacOS specifically.

  4. Adjust the connection string to match your setup - see Microsoft SQL Server connection string for more information.

  5. Introspect your database to validate your connection string - the CLI will throw a P4001 error because your database is empty:

    $npx prisma introspect
    Show CLI output

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:

  1. Paste the following SQL query into a file named create.sql, replacing USE sample with the name of your database - for example, USE myDatabase:

    schema.sql
    1USE sample
    2
    3GO
    4
    5CREATE TABLE [User] (
    6 id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    7 name VARCHAR(255),
    8 email VARCHAR(255) UNIQUE NOT NULL
    9);
    10
    11CREATE TABLE [Post] (
    12 id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    13 title VARCHAR(255) NOT NULL,
    14 "createdAt" DATETIME NOT NULL DEFAULT GETDATE(),
    15 content TEXT,
    16 published BIT NOT NULL DEFAULT 0,
    17 "authorId" INTEGER NOT NULL,
    18 FOREIGN KEY ("authorId") REFERENCES [User] (id)
    19);
    20
    21CREATE TABLE [Profile] (
    22 id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    23 bio TEXT,
    24 "userId" INTEGER UNIQUE NOT NULL,
    25 FOREIGN KEY ("userId") REFERENCES [User](id)
    26);
  2. In your terminal, navigate to the folder with the create.sql file.

  3. Run the following command (change sql1 to the name of your container) to copy the create.sql from your local machine to your container's root folder:

    docker cp create.sql sql1:/
  4. Run the following command to start an interactive bash shell, replacing sql1 with the name of your Docker container:

    $docker exec -it sql1 "bash"
  5. Use the sqlcmd tool to run the create.sql script, replacing mypassword with your password:

    $/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypassword" -i create.sql
  6. To confirm that your tables were created, log in to sqlcmd:

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "mypassword"
  7. 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'"
    $GO
    Show CLI output

Using SQL Server Managment Studio (Windows only)

To create tables SQL Server Managment Studio:

  1. Log in to SQL Server Management Studio.

  2. Click the New Query button:

    The New Query button in SQL Server Managment Studio

  3. Change USE sample to use name of your database (for example, USE myDatabase) and paste it into the query window:

    schema.sql
    1 USE sample
    2
    3GO
    4
    5CREATE TABLE [User] (
    6 id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    7 name VARCHAR(255),
    8 email VARCHAR(255) UNIQUE NOT NULL
    9);
    10
    11CREATE TABLE [Post] (
    12 id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    13 title VARCHAR(255) NOT NULL,
    14 "createdAt" DATETIME NOT NULL DEFAULT GETDATE(),
    15 content TEXT,
    16 published BIT NOT NULL DEFAULT 0,
    17 "authorId" INTEGER NOT NULL,
    18 FOREIGN KEY ("authorId") REFERENCES [User] (id)
    19);
    20
    21CREATE TABLE [Profile] (
    22 id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
    23 bio TEXT,
    24 "userId" INTEGER UNIQUE NOT NULL,
    25 FOREIGN KEY ("userId") REFERENCES [User](id)
    26);
  4. 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
  5. 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.

  1. Run the following command to introspect your database:

    $npx prisma introspect
  2. Open prisma.schema to see your data model:

prisma/schema.prisma
1model Post {
2 id Int @default(autoincrement()) @id
3 createdAt DateTime @default(now())
4 title String
5 content String?
6 published Boolean @default(false)
7 User User @relation(fields: [authorId], references: [id])
8 authorId Int
9}
10
11model Profile {
12 id Int @default(autoincrement()) @id
13 bio String?
14 User User @relation(fields: [userId], references: [id])
15 userId Int @unique
16}
17
18model User {
19 id Int @default(autoincrement()) @id
20 email String @unique
21 name String?
22 Post Post[]
23 Profile Profile?
24}

Adjust the data model

The introspected model contains auto-genreated relation fields that do not ahere to Prisma's naming convention:

model User {
id Int @default(autoincrement()) @id
email String @unique
name String?
Post Post[] // Should be `posts`
Profile Profile? // Should be `profile`
}

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:

  1. Change the field names as shown:

    prisma/schema.prisma
    1model Post {
    2 id Int @default(autoincrement()) @id
    3 createdAt DateTime @default(now())
    4 title String
    5 content String?
    6 published Boolean @default(false)
    author User @relation(fields: [authorId], references: [id])
    8 authorId Int
    9}
    10
    11model Profile {
    12 id Int @default(autoincrement()) @id
    13 bio String?
    user User @relation(fields: [userId], references: [id])
    15 userId Int @unique
    16}
    17
    18model User {
    19 id Int @default(autoincrement()) @id
    20 email String @unique
    21 name String?
    posts Post[]
    profile Profile?
    24}
  2. 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:

  1. Install the @prisma/client package:

    $npm install @prisma/client
  2. Run the following command to generate your Prisma Client:

    $npx prisma generate

    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

  1. 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 posts
    const 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 posts
    const getPosts = await prisma.post.findMany({});
    console.log(getPosts);
    }
    main()
    .catch((e) => {
    throw e;
    })
    .finally(async () => {
    await prisma.$disconnect();
    });
  2. 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
    }
    ]
  3. Change email: "alice@prisma.io" to another email address and run the code again to create another user and list of posts.

  1. 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 posts
    const 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 posts
    const getPosts = await prisma.post.findMany({});
    console.log(getPosts);
    }
    main()
    .catch((e) => {
    throw e;
    })
    .finally(async () => {
    await prisma.$disconnect();
    });
  2. 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
    }
    ]
  3. Change email: "alice@prisma.io" to another email address and run the code again to create another user and list of posts.

Edit this page on GitHub