5. Connecting Next.js to SQL Server

// lib/db.ts
import "server-only";
import sql from "mssql";

const config: sql.config = {
  server: process.env.DB_SERVER!,
  database: process.env.DB_NAME!,
  user: process.env.DB_USER!,
  password: process.env.DB_PASSWORD!,
  options: { encrypt: true, trustServerCertificate: process.env.NODE_ENV !== "production" },
  pool: { max: 10 },
};

// Reuse one pool across hot reloads in development and across requests in production
const globalForDb = globalThis as unknown as { mssqlPool?: Promise<sql.ConnectionPool> };
export const pool = globalForDb.mssqlPool ?? new sql.ConnectionPool(config).connect();
if (process.env.NODE_ENV !== "production") globalForDb.mssqlPool = pool;

// lib/products.ts
import "server-only";
import { pool } from "./db";
import sql from "mssql";

export type Product = { id: number; name: string; price: number };

export async function listProducts(): Promise<Product[]> {
  const r = await (await pool).request().query("SELECT Id AS id, Name AS name, Price AS price FROM dbo.Products ORDER BY Name");
  return r.recordset;
}
export async function getProduct(id: number): Promise<Product | undefined> {
  const r = await (await pool).request().input("id", sql.Int, id)
    .query("SELECT Id AS id, Name AS name, Price AS price FROM dbo.Products WHERE Id = @id");
  return r.recordset[0];
}
export async function createProduct(p: { name: string; price: number }) {
  const r = await (await pool).request().input("name", sql.NVarChar(200), p.name).input("price", sql.Decimal(18, 2), p.price)
    .query("INSERT INTO dbo.Products (Name, Price) OUTPUT INSERTED.Id AS id VALUES (@name, @price)");
  return { id: r.recordset[0].id as number, ...p };
}

# .env.local (not committed; set the same keys in Vercel/Azure settings)
DB_SERVER=localhost
DB_NAME=Shop
DB_USER=shop_app
DB_PASSWORD=change-me