SQL Server 2017 : Use from Node.js2017/10/30

	This is an example to use SQL Server from Node.js.
[1]	Install required modules first.

[cent@dlp ~]$

node -v

v6.11.3
[cent@dlp ~]$

mkdir MssqlTest

[cent@dlp ~]$

cd MssqlTest

[cent@dlp MssqlTest]$

npm init -y

[cent@dlp MssqlTest]$

npm install tedious async

[2]	Create a sample User and Database for Test.

[cent@dlp ~]$

sqlcmd -S localhost -U SA

Password:

# create login user
1> create login cent with PASSWORD= N'password';
2> go

# create [SampleDB]
1> create database SampleDB;
2> go

1> use SampleDB;
2> go
Changed database context to 'SampleDB'.

# create DB user
1> create user cent for login cent;
2> go

# assign DB owner role to [cent]
1> exec sp_addrolemember 'db_owner', 'cent';
2> go

# create [SampleTable]
1> create table SampleTable (
2> ID int identity(1,1) not null primary key, First_Name NVARCHAR(50), Last_Name NVARCHAR(50)
3> );
4> insert into SampleTable (
5> First_Name, Last_Name) values (N'CentOS', N'Linux'), (N'RedHat', N'Linux'), (N'Fedora', N'Linux'
6> );
7> go

[3]	There are some basic usage to connect to SQL Server from Node.js.

[cent@dlp ~]$

cd MssqlTest

[cent@dlp MssqlTest]$

vi use_mssql.js

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var async = require('async');

var config = {
  userName: 'cent',
  password: 'password',
  server: '127.0.0.1',
  options: {
    database: 'SampleDB'
  }
}

var connection = new Connection(config);

function Start(callback) {
    callback(null);
}

// Select from SampleTable
function Read(callback) {
    console.log('Reading from SampleTable...');

    request = new Request(
    'select * from SampleTable;',
    function(err, rowCount, rows) {
    if (err) {
        callback(err);
    } else {
        console.log(' \n');
        callback(null, 'Ubuntu', 'Linux');
    }
    });

    var result = "";
    request.on('row', function(columns) {
        columns.forEach(function(column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                result += column.value + " ";
            }
        });
        console.log(result);
        result = "";
    });

    connection.execSql(request);
}

// Insert from SampleTable
function Insert(first_name, last_name, callback) {
    console.log("Inserting '" + first_name + "' into SampleTable...");

    request = new Request(
        'insert into SampleTable (First_Name, Last_Name) output inserted.ID values (@First_Name, @Last_Name);',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) inserted\n');
            callback(null, 'Redhat', 'Maipo');
        }
        });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);
    request.addParameter('Last_Name', TYPES.NVarChar, last_name);

    connection.execSql(request);
}

// Update from SampleTable
function Update(first_name, last_name, callback) {
    console.log("Updating Last_Name to '" + last_name + "' for '" + first_name + "'...");

    request = new Request(
    'update SampleTable set Last_Name=@Last_Name where First_Name = @First_Name;',
    function(err, rowCount, rows) {
        if (err) {
        callback(err);
        } else {
        console.log(rowCount + ' row(s) updated\n');
        callback(null, 'Ubuntu');
        }
    });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);
    request.addParameter('Last_Name', TYPES.NVarChar, last_name);

    connection.execSql(request);
}

// Delete from SampleTable
function Delete(first_name, callback) {
    console.log("Deleting '" + first_name + "' from Table...");

    request = new Request(
        'delete from SampleTable where First_Name = @First_Name;',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' row(s) deleted\n');
            callback(null);
        }
        });
    request.addParameter('First_Name', TYPES.NVarChar, first_name);

    connection.execSql(request);
}

function Complete(err, result) {
    if (err) {
        callback(err);
    } else {
        console.log("Done!");
    }
}

connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    async.waterfall([
        Start,
        Read,
        Insert,
        Update,
        Delete,
        Read
    ], Complete)
  }
});

[cent@dlp MssqlTest]$

node use_mssql.js

Reading from SampleTable...
1 CentOS Linux
2 RedHat Linux
3 Fedora Linux

Inserting 'Ubuntu' into SampleTable...
1 row(s) inserted

Updating Last_Name to 'Maipo' for 'Redhat'...
1 row(s) updated

Deleting 'Ubuntu' from Table...
1 row(s) deleted

Reading from SampleTable...
1 CentOS Linux
2 RedHat Maipo
3 Fedora Linux

Done!

Matched Content