001-sql-server-start-from-scratch.mdx

title	langSwitcher	techMetaTitles	techMetaDescriptions
Start from scratch with SQL Server	typescript node	name value node SQL Server Start from Scratch | Node.js | Prisma name value typescript SQL Server Start from Scratch | TypeScript | Prisma	name value node Follow this tutorial to use Prisma with a Microsoft SQL Server database with Node.js name value typescript Follow this tutorial to use Prisma with a Microsoft SQL Server database with Typescript.

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

  • Microsoft SQL Server on Linux for Docker
  • Microsoft SQL Server on Windows (local)

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

Create project setup

<SwitchTech technologies={['typescript', '*']}>

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 typescript ts-node @types/node --save-dev
   

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

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

   {
     "compilerOptions": {
       "sourceMap": true,
       "outDir": "dist",
       "strict": true,
       "lib": ["esnext"],
       "esModuleInterop": true
     }
   }

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

   npx prisma
   

3. 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)

<SwitchTech technologies={['node', '*']}>

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 --save-dev
   

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

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

   npx prisma
   

2. 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:

   generator client {
     provider        = "prisma-client-js"
   |  previewFeatures = ["microsoftSqlServer"]
   }

2. Set the provider and url fields of the datasource block in your Prisma schema as shown:

   datasource db {
     provider = "sqlserver"
     url      = env("DATABASE_URL")
   }

3. Define the DATABASE_URL environment variable in the prisma/.env file - this is the connection URL to your database.

   The following example connection URL uses SQL authentication, but there are other ways to format your connection URL:

   DATABASE_URL="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.

4. Adjust the connection URL to match your setup - see Microsoft SQL Server connection URL 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)

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

Deprecation warning
From Prisma 3.0.0 onwards, the prisma introspect command will be deprecated and replaced with the prisma db pull command.

```terminal
npx prisma introspect
```

```
Environment variables loaded from prisma\.env
Prisma Schema loaded from prisma\schema.prisma

Introspecting based on datasource defined in prisma\schema.prisma …
Error:
P4001 The introspected database was empty:
```

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)

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:

   USE sample
   
   GO
   
   CREATE TABLE [User] (
     id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     name VARCHAR(255),
     email VARCHAR(255) UNIQUE NOT NULL
   );
   
   CREATE TABLE [Post] (
     id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     title VARCHAR(255) NOT NULL,
     "createdAt" DATETIME NOT NULL DEFAULT GETDATE(),
     content TEXT,
     published BIT NOT NULL DEFAULT 0,
     "authorId" INTEGER NOT NULL,
     FOREIGN KEY ("authorId") REFERENCES [User] (id)
   );
   
   CREATE TABLE [Profile] (
     id INT PRIMARY KEY IDENTITY(1,1)  NOT NULL,
     bio TEXT,
     "userId" INTEGER UNIQUE NOT NULL,
     FOREIGN KEY ("userId") REFERENCES [User](id)
   );

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):

```terminal
USE my_database_name
:setvar SQLCMDMAXVARTYPEWIDTH 15
:setvar SQLCMDMAXFIXEDTYPEWIDTH 15
sp_tables
@table_type="'TABLE'"
GO
```

```
 TABLE_QUALIFIER TABLE_OWNER     TABLE_NAME      TABLE_TYPE      REMARKS
 --------------- --------------- --------------- --------------- ---------------
 mstest          dbo             Post            TABLE           NULL
 mstest          dbo             Profile         TABLE           NULL
 mstest          dbo             User            TABLE           NULL
```

</cmdResult>

</CodeWithResult>

Using SQL Server Management Studio (Windows only)

To create tables SQL Server Management Studio:

1. Log in to SQL Server Management Studio.

2. Click the New Query button:

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

   | USE sample
   
   GO
   
   CREATE TABLE [User] (
     id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     name VARCHAR(255),
     email VARCHAR(255) UNIQUE NOT NULL
   );
   
   CREATE TABLE [Post] (
     id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     title VARCHAR(255) NOT NULL,
     "createdAt" DATETIME NOT NULL DEFAULT GETDATE(),
     content TEXT,
     published BIT NOT NULL DEFAULT 0,
     "authorId" INTEGER NOT NULL,
     FOREIGN KEY ("authorId") REFERENCES [User] (id)
   );
   
   CREATE TABLE [Profile] (
     id INT PRIMARY KEY IDENTITY(1,1)  NOT NULL,
     bio TEXT,
     "userId" INTEGER UNIQUE NOT NULL,
     FOREIGN KEY ("userId") REFERENCES [User](id)
   );

2. 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
   

3. Expand the database node in the Object Explorer to confirm that your tables were created.

Introspect your database with Prisma

Deprecation warning
From Prisma 3.0.0 onwards, the prisma introspect command will be deprecated and replaced with the prisma db pull command.

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:

   model Post {
     id        Int      @default(autoincrement()) @id
     createdAt DateTime @default(now())
     title     String
     content   String?
     published Boolean  @default(false)
     User      User     @relation(fields: [authorId], references: [id])
     authorId  Int
   }
   
   model Profile {
     id     Int     @default(autoincrement()) @id
     bio    String?
     User   User    @relation(fields: [userId], references: [id])
     userId Int     @unique
   }
   
   model User {
     id      Int      @default(autoincrement()) @id
     email   String   @unique
     name    String?
     Post    Post[]
     Profile Profile?
   }

Adjust the data model

The introspected model contains auto-generated relation fields that do not adhere 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:

   model Post {
     id        Int      @default(autoincrement()) @id
     createdAt DateTime @default(now())
     title     String
     content   String?
     published Boolean  @default(false)
     author    User     @relation(fields: [authorId], references: [id])
     authorId  Int
   }
   
   model Profile {
     id     Int     @default(autoincrement()) @id
     bio    String?
     user   User    @relation(fields: [userId], references: [id])
     userId Int     @unique
   }
   
   model User {
     id      Int      @default(autoincrement()) @id
     email   String   @unique
     name    String?
     posts   Post[]
     profile Profile?
   }

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

<SwitchTech technologies={['typescript', '*']}>

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.

<SwitchTech technologies={['node', '*']}>

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.