Using SQL Server in Express.js: A Complete Guide

Connecting SQL Server with Express.js is common when building enterprise web applications. Express provides the server, and SQL Server handles data storage. In this article, you will learn how to install the necessary packages, configure a database connection, and perform CRUD operations using SQL Server in an Express.js application.

Installing Required Packages

To use SQL Server with Express, install the mssql package:

npm install mssql express

The mssql library allows Node.js to communicate with SQL Server databases.

Creating a Database Connection

Create a file named db.js to manage the SQL connection. This file will keep your connection configuration separate and reusable throughout your project.

const sql = require('mssql');

const config = {
    user: 'your_username',
    password: 'your_password',
    server: 'localhost',
    database: 'your_database',
    options: {
        encrypt: false,
        trustServerCertificate: true
    }
};

const pool = new sql.ConnectionPool(config);
const poolConnect = pool.connect();

module.exports = { sql, poolConnect, pool };

Explanation

• user and password represent your SQL Server credentials

• server is the host name or IP address

• database is the target database

• encrypt and trustServerCertificate settings ensure smooth local connections

Building Express Routes with SQL Server

Create an Express server and use the SQL connection to execute queries.

const express = require('express');
const { sql, poolConnect, pool } = require('./db');
const app = express();

app.use(express.json());

Reading Data from SQL Server

Example route that retrieves all users:

app.get('/users', async (req, res) => {
    try {
        await poolConnect;
        const request = pool.request();
        const result = await request.query('SELECT * FROM Users');
        res.send(result.recordset);
    } catch (err) {
        res.status(500).send(err.message);
    }
});

Inserting Data

Example route for adding a new user:

app.post('/add-user', async (req, res) => {
    const { name, email } = req.body;

    try {
        await poolConnect;
        const request = pool.request();
        request.input('name', sql.VarChar, name);
        request.input('email', sql.VarChar, email);

        const query = 'INSERT INTO Users (Name, Email) VALUES (@name, @email)';
        await request.query(query);

        res.send('User added successfully');
    } catch (err) {
        res.status(500).send(err.message);
    }
});

Updating Data

app.put('/users/:id', async (req, res) => {
    const { id } = req.params;
    const { name } = req.body;

    try {
        await poolConnect;
        const request = pool.request();
        request.input('id', sql.Int, id);
        request.input('name', sql.VarChar, name);

        const query = 'UPDATE Users SET Name = @name WHERE Id = @id';
        await request.query(query);

        res.send('User updated');
    } catch (err) {
        res.status(500).send(err.message);
    }
});

Deleting Data

app.delete('/users/:id', async (req, res) => {
    const { id } = req.params;

    try {
        await poolConnect;
        const request = pool.request();
        request.input('id', sql.Int, id);

        const query = 'DELETE FROM Users WHERE Id = @id';
        await request.query(query);

        res.send('User deleted');
    } catch (err) {
        res.status(500).send(err.message);
    }
});

Starting the Server

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

Your Express app is now connected to SQL Server and fully capable of handling database operations.

Conclusion

Using SQL Server with Express.js is straightforward with the mssql package. You set up a database connection, build routes, and perform SQL queries using the connection pool. This approach works well for enterprise applications, APIs, dashboards, and any Node.js project that needs SQL Server as its database.