8. Connecting Node.js to SQL Server (mssql, Prisma, Sequelize)

// src/db/pool.js
import sql from 'mssql';

const config = {
  server: process.env.DB_SERVER,                 // e.g. "localhost" or "myserver.database.windows.net"
  database: process.env.DB_NAME,
  user: process.env.DB_USER,                     // SQL authentication
  password: process.env.DB_PASSWORD,
  port: 1433,
  pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },
  options: {
    encrypt: true,                               // required for Azure SQL
    trustServerCertificate: process.env.NODE_ENV !== 'production',   // local dev with self-signed cert
  },
};

export const poolPromise = new sql.ConnectionPool(config).connect();   // ONE pool for the whole app
export { sql };

// src/modules/products/product.repository.js
import { poolPromise, sql } from '../../db/pool.js';

export async function findById(id) {
  const pool = await poolPromise;
  const result = await pool.request()
    .input('id', sql.Int, id)                    // parameterised — prevents SQL injection
    .query('SELECT Id, Name, Price FROM dbo.Products WHERE Id = @id');
  return result.recordset[0] ?? null;
}

export async function searchByProc(customerId) {
  const pool = await poolPromise;
  const r = await pool.request()
    .input('CustomerId', sql.Int, customerId)
    .output('Total', sql.Int)
    .execute('dbo.usp_GetCustomerOrders');       // stored procedure
  return { orders: r.recordset, total: r.output.Total };
}

export async function placeOrder(order) {        // transaction
  const pool = await poolPromise;
  const tx = new sql.Transaction(pool);
  await tx.begin();
  try {
    const r = await new sql.Request(tx).input('cid', sql.Int, order.customerId)
      .query('INSERT INTO dbo.Orders (CustomerId) OUTPUT INSERTED.Id VALUES (@cid)');
    for (const l of order.lines)
      await new sql.Request(tx).input('oid', sql.Int, r.recordset[0].Id).input('pid', sql.Int, l.productId).input('qty', sql.Int, l.qty)
        .query('INSERT INTO dbo.OrderLines (OrderId, ProductId, Qty) VALUES (@oid, @pid, @qty)');
    await tx.commit();
  } catch (e) { await tx.rollback(); throw e; }
}

# .env (never commit)
DB_SERVER=localhost
DB_NAME=Shop
DB_USER=shop_app
DB_PASSWORD=change-me

// prisma/schema.prisma
datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Product {
  id    Int     @id @default(autoincrement())
  name  String  @db.NVarChar(200)
  price Decimal @db.Decimal(18, 2)
}

DATABASE_URL="sqlserver://localhost:1433;database=Shop;user=shop_app;password=change-me;encrypt=true;trustServerCertificate=true"
npx prisma migrate dev --name init    # or: npx prisma db pull (database-first)

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
const cheap = await prisma.product.findMany({ where: { price: { lt: 100 } }, orderBy: { name: 'asc' } });