Accessing SQL Server in Node.js with Express.js

Learn how to access Microsoft SQL Server from a Node.js application using the Express.js framework. This guide covers the installation of the SQL Server driver via NPM and assumes the existence of a "Student" table within the "SchoolDB" database. Follow along to connect your Node.js app to SQL Server and perform database operations efficiently.

Learn how to access Microsoft SQL Server using Node.js and Express.js.

Installation

Install the SQL Server driver using NPM:

npm install mssql

Sample Database Table

Assuming you have a Student table in your SchoolDB database.

Basic Example

Create a file named server.js and add the following code:

var express = require('express');
var app = express();

app.get('/', function (req, res) {
    var sql = require("mssql");

    // Configuration for your database
    var config = {
        user: 'sa',
        password: 'mypassword',
        server: 'localhost',
        database: 'SchoolDB'
    };

    // Connect to your database
    sql.connect(config, function (err) {
        if (err) console.log(err);

        // Create Request object
        var request = new sql.Request();

        // Query to the database and get the records
        request.query('select * from Student', function (err, recordset) {
            if (err) console.log(err);
            // Send records as a response
            res.send(recordset);
        });
    });
});

var server = app.listen(5000, function () {
    console.log('Server is running..');
});

Enhanced Code with Error Handling and Connection Pooling

For a more robust implementation, use the following code:

const express = require('express');
const sql = require('mssql');

const app = express();

// Configuration for your database
const config = {
  user: 'sa',
  password: 'mypassword',
  server: 'localhost',
  database: 'SchoolDB',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  }
};

// Create a connection pool
const pool = new sql.ConnectionPool(config);

app.get('/', async (req, res) => {
  try {
    await pool.connect();
    const request = new sql.Request();
    const result = await request.query('SELECT * FROM Student');

    res.json(result.recordset);
  } catch (err) {
    console.error('Error:', err);
    res.status(500).send('Database error');
  }
});

app.listen(5000, () => {
  console.log('Server is running..');
});

Explanation of Improvements

• Error Handling: Uses async/await and try...catch for error handling.
• Connection Pooling: Implements connection pooling to optimize database connections.
• JSON Response: Sends response as JSON for better application compatibility.
• Code Readability: Consistently formatted code for improved readability.

Additional Considerations

• Security: Avoid hardcoding sensitive information; use environment variables or configuration files.
• Parameterization: Use parameterized queries to prevent SQL injection attacks.
• Asynchronous Operations: Handle database operations asynchronously to avoid blocking the event loop.
• Connection Management: Implement proper connection management to avoid resource leaks.
• Performance Optimization: Consider connection pooling, prepared statements, and indexing for performance improvements.
• Testing: Write unit tests to ensure code functionality.

By incorporating these enhancements, you can develop more robust and efficient Node.js applications that interact with SQL Server databases.