1# SQL Database — Node.js — REFERENCE ONLY
2 
3## Prisma + Azure SQL Setup
4 
5### npm Packages
6 
7```bash
8npm install prisma @prisma/client mssql
9npm install -D prisma
10npx prisma init
11```
12 
13### Prisma Schema
14 
15Update `prisma/schema.prisma`:
16 
17```prisma
18datasource db {
19  provider = "sqlserver"
20  url      = env("DATABASE_URL")
21}
22 
23generator client {
24  provider = "prisma-client-js"
25}
26 
27model TodoItem {
28  id         Int     @id @default(autoincrement())
29  title      String  @db.NVarChar(200)
30  isComplete Boolean @default(false)
31}
32```
33 
34### Database Client
35 
36Create `src/db.js`:
37 
38```javascript
39const { PrismaClient } = require("@prisma/client");
40 
41const prisma = new PrismaClient();
42module.exports = { prisma };
43```
44 
45### API Endpoints
46 
47Add to `src/index.js`:
48 
49```javascript
50const { prisma } = require("./db");
51 
52app.get("/api/todos", async (req, res) => {
53  const todos = await prisma.todoItem.findMany();
54  res.json(todos);
55});
56 
57app.post("/api/todos", async (req, res) => {
58  const body = req.body || {};
59  const title = typeof body.title === "string" ? body.title.trim() : "";
60  if (!title) {
61    return res.status(400).json({ error: "title is required and must be a non-empty string" });
62  }
63  const isComplete = body.isComplete === undefined ? false : body.isComplete;
64  if (typeof isComplete !== "boolean") {
65    return res.status(400).json({ error: "isComplete must be a boolean" });
66  }
67  const todo = await prisma.todoItem.create({ data: { title, isComplete } });
68  res.status(201).json(todo);
69});
70 
71app.get("/api/todos/:id", async (req, res) => {
72  const todo = await prisma.todoItem.findUnique({
73    where: { id: parseInt(req.params.id) },
74  });
75  if (!todo) return res.status(404).json({ error: "Not found" });
76  res.json(todo);
77});
78```
79 
80### Connection String
81 
82**Azure (managed identity):**
83 
84Set as app setting in Bicep — the format Prisma uses for SQL Server:
85```
86sqlserver://<server>.database.windows.net:1433;database=<db>;authentication=ActiveDirectoryMsi;clientId=<mi-client-id>
87```
88 
89```bicep
90{ name: 'DATABASE_URL', value: 'sqlserver://${sqlServer.properties.fullyQualifiedDomainName}:1433;database=${sqlDatabase.name};authentication=ActiveDirectoryMsi;clientId=${managedIdentity.properties.clientId}' }
91```
92 
93**Local development (SQL auth):**
94 
95```bash
96# .env (local only — never commit)
97DATABASE_URL="sqlserver://localhost:1433;database=myapp;user=<username>;password=<your-strong-password>;trustServerCertificate=true"
98```
99 
100### EF Migrations (postprovision hook)
101 
102Create `infra/scripts/setup-db.sh`:
103 
104```bash
105#!/bin/bash
106npx prisma migrate deploy --schema src/prisma/schema.prisma
107```
108 
109## Files to Add
110 
111| File | Action |
112|------|--------|
113| `prisma/schema.prisma` | Create — data model definitions |
114| `src/db.js` | Create — Prisma client instance |
115| `src/index.js` | Modify — add CRUD endpoints |
116| `package.json` | Modify — add prisma, @prisma/client |
117 
118## Common Patterns
119 
120- Use `prisma.todoItem.findMany()` for read-only queries
121- Use `prisma.$disconnect()` in shutdown hooks for clean teardown
122- Run `npx prisma generate` after schema changes to regenerate the client
123