mssql

import sql from 'mssql'; // Use require('mssql') for CommonJS const sqlConfig = { user: process.env.DB_USER ?? 'SA', // Use 'SA' or another user if not using Windows Auth password: process.env.DB_PASSWORD ?? 'YourStrong@Password123!', database: process.env.DB_NAME ?? 'master', server: process.env.DB_SERVER ?? 'localhost', // or your server IP/hostname pool: { max: 10, min: 0, idleTimeoutMillis: 30000 }, options: { encrypt: process.env.DB_ENCRYPT === 'true', // For Azure SQL, this should be true trustServerCertificate: process.env.NODE_ENV !== 'production' // Change to true for local dev / self-signed certs } }; async function runDatabaseOperations() { let pool; // Declare pool outside try block for finally access try { pool = await sql.connect(sqlConfig); console.log('Successfully connected to SQL Server.'); // Example 1: Insert data using a parameterized query const newProductName = 'Test Product ' + Date.now(); const insertResult = await pool.request() .input('productName', sql.NVarChar, newProductName) .input('price', sql.Decimal(10, 2), 19.99) .query`INSERT INTO Products (Name, Price) VALUES (@productName, @price); SELECT SCOPE_IDENTITY() AS NewId;`; console.log(`Inserted new product with ID: ${insertResult.recordset[0].NewId}`); // Example 2: Select data const selectResult = await pool.query`SELECT Id, Name, Price FROM Products WHERE Name LIKE '%Test Product%';`; console.log('Selected products:', selectResult.recordset); // Example 3: Execute a stored procedure (assuming 'GetProductById' exists) // const productId = insertResult.recordset[0].NewId; // const spResult = await pool.request() // .input('id', sql.Int, productId) // .execute('GetProductById'); // console.log('Stored procedure result:', spResult.recordset); } catch (err) { console.error('Database operation failed:', err.message); // Implement robust error handling here, e.g., logging, retries } finally { if (pool) { await pool.close(); console.log('Database connection closed.'); } } } // Ensure a Products table exists for the examples // CREATE TABLE Products (Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(255), Price DECIMAL(10, 2)); // Make sure to set DB_USER, DB_PASSWORD, DB_NAME, DB_SERVER environment variables runDatabaseOperations();