SQL Server 2019 : Node.js から利用する2020/01/20

	Node.js からの SQL Server 利用例です。
[1]	Node.js 8 インストール済みを前提として例示します。追加で必要なモジュールをインストールしておきます。

[cent@dlp ~]$

node -v

v8.16.1
[cent@dlp ~]$

mkdir MssqlTest

[cent@dlp ~]$

cd MssqlTest

[cent@dlp MssqlTest]$

npm init -y

[cent@dlp MssqlTest]$

npm install tedious async

[2]	テスト用のデータベース接続用ユーザーとデータベースを作成しておきます。

[cent@dlp ~]$

sqlcmd -S localhost -U SA

Password:

# ログインユーザー作成
1> create login cent with PASSWORD= N'password';
2> go

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

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

# ログインユーザー [cent] と関連付けて DBユーザー作成
1> create user cent for login cent;
2> go

# [cent] にDBオーナーロール付与
1> exec sp_addrolemember 'db_owner', 'cent';
2> go

# テストテーブル作成
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]	基本的な利用例です。データベースや接続ユーザーは上記で作成したものを使用します。

[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 = {
  server: '127.0.0.1',
  authentication: {
      type: 'default',
      options: {
          userName: 'cent',
          password: 'password'
      }
  },
  options: {
      database: 'SampleDB'
  }
}

var connection = new Connection(config);

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

// SampleTable を Select
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);
}

// SampleTable に Insert
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
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
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!