Introduction:
This article
explain about connecting SQL server through NodeJs. In order to access MS SQL database, “mssql” driver needs to
install through NPM manager.
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.
Steps and Verification:
We
have to modify the settings in the Sql Server Configuration Manager.
Step 1: At first we
have to change the port numbers in Sql Server Configuration Manager and fallow
the steps given below to change the port numbers.
Sql Server Configuration Manager à Sql Server Network Configuration à Protocols for SQLExpress
à TCP/IP(Enabled) à
Right-click on TCP/IP and select Properties à IP
Address à IP All àTCP Dynamic Ports – 47536 and TCP Port – 1433. 
Step 2: And now we
have to check the Services given below are running or not.
1.
SQL Server
Agent
2.
SQL Server
Browser
3.
SQL Server 
Step 3: 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 Employee_Details table in msdb database
shown in below example. Now, create server.js and write the
following code. var sql =
require('mssql'); // config for your database var config = { "user": 'sa', "password": '########', "server": '#######\\SQLEXPRESS', "database": 'master', "port": '1433', "dialect": "mssql", "dialectOptions": { "instanceName":
"SQLEXPRESS" } }; (async () => { try { // connect to your database let pool = await sql.connect(config); // create Request object const request = pool.request(); // query to the database and get the
records request.query('select * from
Category where ID = 29', (err, result) => { console.dir(result) }) } catch (err) { // ... error checks console.log('This is Error'); console.log(err); console.dir(err); } })() sql.on('error',
err => { // ... error handler console.log('This is Error handler');
}) 
In the
above example, we have imported mssql module and called connect() method to
connect with our master 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 then you can see the result in command prompt as given in below figure. 
Basic methods: 1.
resultSet.recorset[i].columnName: To iterate the column data one
by one and “i” is the particular row number in the resultSet. 2.
resultSet.recordset.length: It is used to get the length of the
resultSet. 3.
resultSet.recordset[i]: It is used to get the particular row
data in resultSet. If you
want to insert any values into the query, we need to use the “request.input()”
method. Syntax: request.input(‘value’,
sql.datatype, column name); request.query('select * from category where
Name=@value', (err, result) => { console.dir(result); }); Example: var sql =
require('mssql'); // config for your database var config = { "user": 'sa', "password": '########', "server": '#######\\SQLEXPRESS', "database": 'master', "port": '1433', "dialect": "mssql", "dialectOptions": { "instanceName":
"SQLEXPRESS" } }; var Name =
'Node.js'; var Status =
'Active'; (async () => { try { // connect to your database let pool = await sql.connect(config); // create Request object const request = pool.request(); request.input('value', sql.VarChar,
Name); request.input('value1', sql.VarChar,
Status); // query to the database and get the
records request.query('update Category set
Status = @value1 where Name = @value', (err, result) => { console.dir(result) }) } catch (err) { // ... error checks console.log('This is Error'); console.log(err); console.dir(err); } })() sql.on('error',
err => { // ... error handler console.log('This is Error handler');
}) 
The result will show as how
many rows affected as shown in figure below. 
|