Access SQL Server in Node.js

Learn how to access relational database MS SQL Server 2012 in Node.js application using Express.js in this section.

In order to access MS SQL database, we need to install drivers for it. There are many drivers available for SQL server in NPM. We will use mssql driver here.

Install Driver:

Install mssql driver using npm command, npm install mssql in the command prompt. This will add mssql module folder in node_modules folder in your Node.js application. This tutorial uses mssql v2.3.1, which is latest version as of now.

After installing the driver, we are ready to access MS SQL server database. We will connect to a local SQLExpress database server and fetch all the records from Student table in SchoolDB database shown below.

Now, create server.js and write the following code.

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

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

    // config 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..');
});

In the above example, we have imported mssql module and called connect() method to connect with our SchoolDB database. We have passed config object which includes database information such as userName, password, database server and database name. On successful connection with the database, use sql.request object to execute query to any database table and fetch the records.

Run the above example using node server.js command and point your browser to http://localhost:5000 which displays an array of all students from Student table.

Thus, you can access MS SQL Server database and execute queries using mssql module. Visit npm documentation to learn more about mssql.

Learn how to access MongoDB in Node.js in the next section.