Node.jsでSQLServer2017に接続してSELECT結果を画面に表示するサンプル

ここ数日で実施したことのまとめです。
Node.jsでDBに接続するアプリケーションを作成するためのサンプルです。
DBは、SQLServer2017を利用しました。

■環境
　OS: Windows 10 Home 64bit
　DB: SQLServer2017 Developer Edition
　node: v8.9.1
　npm: 4.0.5
　
■アウトライン
　1. DBにテーブルを作成する。
　2. Express Generatorでテンプレートを作成する。
　3. SQLServerに接続するためのドライバをインストールする
　4. DBに接続しSELECTするスクリプトを作成する
　5. 4でSELECTした結果を表示する画面（ejs）を作成する

■1. DBにテーブルを作成する。
　SELECTするデータがDBにないと進まないので、サンプルのデータベースの作成と、テーブルを作成します。
　SSMSを利用するとGUIでデータベースの作成ができるので、オブジェクトエクスプローラーからポチポチ作成します。
　今回作成したデータベース名は「TestDB」としました。

　作成したデータベース内にデータベースを作成します。
　今回作成したテーブルの情報は下記となります。
　・テーブル名：dbo.TB_TestTable
　　列1: 列名：ID, 型：int, Primary Key
　　列2: 列名：NAME, 型：varchar(50)
　　列3: 列名：MAIL, 型：varchar(50)

　・テーブルにはとりあえず2レコード入れておきます。
　ID:1, NAME: taro, MAIL: taro@taro
　ID:2, NAME: hanako, MAIL:hanako@hanako

■2. Express Generatorでテンプレートを作成する。
　前回の記事に書いた通りテンプレートを作成します。
　すでにExpress Generatorはグローバルインストールしてあるので、コマンドプロンプトから下記コマンドを順番に実行します。
　※express-generatorは4.15.5を利用しています。

express -e Sample1
cd Sample1
npm install
npm start

　ブラウザでhttp://localhost:3000にアクセスし、画面が表示されれば動作確認OKです。

■3. SQLServerに接続するためのドライバをインストールする
　SQLServerに接続するためのドライバをインストールします。
　2で作成したSample1ディレクトリで、下記コマンドを実行します。

npm install tedious --save

今回はtediousの2.2.4がインストールされました。

■4. DBに接続しSELECTするスクリプトを作成する
　routesディレクトリ配下にファイル（sample.js）を作成します。

/*sample.jsファイル*/
var express = require('express');
var router = express.Router();

// Connectionを定義する
var Connection = require('tedious').Connection;

// SQLServerの接続定義を記載する。
var config = {
    userName: 'DBへの接続ユーザ名',
    password: 'DBへの接続ユーザパスワード',
    server: '接続するSQLServerのIP',
}

/* GET users listing. */
router.get('/', function(req, res, next) {

    var connection = new Connection(config);
    var content = []; // DBからselectした結果を格納する変数

    // DB接続した際のイベントハンドラ
    connection.on('connect', function(err){
        console.log("connected");
        executeStatement();
    });

    // DB接続を終了した際のイベントハンドラ
    // DB接続を切断した後に画面を描写する
    connection.on('end', function(){
        console.log("disconnected");
        res.render('sample', { title: 'sqlserver-sample', content: content });
    });

    var Request = require('tedious').Request;

    // SQLを発行する関数
    function executeStatement(){
        // 発行するSQLを記載する
        request = new Request("SELECT * FROM TestDB.dbo.TB_TestTable with (NOLOCK)", function(err){
        if(err){
            console.log(err);}
        });

        var result = {}; // SQLの結果を行ごとにオブジェクトに格納する。
        // SQLの行ごとに実行するイベントハンドラ
        request.on('row', function(columns){
            columns.forEach(function(column){
                if(column.value === null){
                    console.log('NULL');
                }else{
                    result[column.metadata.colName] = column.value;
                } 
            });
            content.push(result);
            result = {};
        });

        // SQLのリクエスト完了時のイベントハンドラ。
        // コネクションをクローズしないとDBにいらないプロセスが残るので、コネクションをクローズする。
        request.on('requestCompleted', function(){
            console.log('requestCompleted');
            connection.close();
        });

        // DBへSQLを発行する。
        connection.execSql(request);
    }

});

module.exports = router;

　
作成したスクリプトをapp.jsに登録します。

/*app.js内*/
var sample = require('./routes/sample');
/*中略*/
app.use('/sample', sample)

■5. 4でSELECTした結果を表示する画面（ejs）を作成する
　SELECTした結果を表で表示する画面を作成します。
　viewsディレクトリ配下に下記ファイル（sample.ejs）を作成します。

<!DOCTYPE html>
<html lang="ja">
    <head>
        <meta http-equiv="content-type" content="text/html; charset=UTF-8">
        <title><%= title %></title>
        <link rel='stylesheet' href='/stylesheets/style.css' />
    </head>
    <body>
        <head><h1><%= title %></h1></head>
        <div role="main">
            <table>
                <tr><th>ID</th><th>NAME</th><th>MAIL</th></tr>
                <% content.forEach(function (value, key) { %>
                <tr>
                    <td><%= value.ID %></td>
                    <td><%= value.NAME %></td>
                    <td><%= value.MAIL %></td>
                </tr>
                <% }); %>
            </table>
        </div>
    </body>
 </html>

これで、http://localhost:3000/sample　へ接続すると、DBへSelectした結果が表で表示されます。
　
■ソースコード
 スクリプトはGithubにcommitしました。
github.com

接続定義とかはルーティングのスクリプトから外だしした方が、
きれいになっていいのかなと個人的に思いました。
実際はみなさんどうやってコーディングしているんでしょうか…
また、記載の仕方でこうしたらいいよ、とかこんな記載普通しないよなどありましたら、
是非教えていただきたいです。

以上。

不動の鳥の勉強記録

時間があるときに勉強したことをメモします。

Node.jsでSQLServer2017に接続してSELECT結果を画面に表示するサンプル