はじめに

この記事では、Microsoft 社が公開している Build an app using SQL Server の内容に従い、SQL Server を使用した Node.js アプリを作成します。

環境

OS: Windows 10 Pro 10.0.19041 N/A ビルド 19041
SQL Server: SQL Server 2019
Node.js: v12.16.1

環境のセットアップ

SQL Server のインストール

ウェブ上では、ホスト OS に SQL Server 2017 Developer 以上をインストール とあります。
[こちら][SQLServerDownload] のサイトより、SQL Server 2019 Developer インストーラーをダウンロードし、インストールを行ってください。

筆者は、WSL2 (Ubuntu 18.04 LTS) 上にインストールした Docker 上に、SQL Server 2019 on Linux をインストールして利用しました。docker-compose.yaml については、以下の GitHub リポジトリを参考にしてください。構築後、ifconfig コマンドを実行し、eth0 の IP アドレスを確認してください。

ymasaoka/mssql-docker

Chocolatey と Node.js のインストール

既に Node.js をインストール済みの場合は、スキップして問題ありません。まだ Node.js をインストールしていない場合は、インストールを行ってください。
サイトでは、Chocolatey を使った Node.js のインストールについて説明されています。Chocolatory とは、Windows 版パッケージ管理マネージャーです。Ubuntu でいう apt-get、RHEL/CentOS でいう yum に相当します。

なお、筆者は docker 上に作成した Node.js コンテナを使用しました。先ほど作成した SQL Server on Linux コンテナと Node.js コンテナ同士が接続できるようにネットワーク設定を行えば、以降の作業を Docker 上で行うことも可能です。

Chocolateyをインストール

@powershell -NoProfile -ExecutionPolicy Bypass -Command "iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))" && SET "PATH=%PATH%;%ALLUSERSPROFILE%\chocolatey\bin"

ChocolateyでNode.jsをインストール

choco install -y nodejs

SQLCMD のインストール

SQLCMD は、SQL Server に接続してクエリを実行できるコマンドラインツールです。まだインストールしていない場合は、以下の手順に従ってインストールを行ってください。

ODBC Driver for SQL Server をダウンロードし、インストール
sqlcmd ユーティリティをダウンロードし、インストール

インストールが完了したら、SQLCMD を利用して SQL Server に接続できることを確認します。

sqlcmd -S <接続するSQL Serverインスタンス名> -U sa -P <saユーザーパスワード> -Q "SELECT @@VERSION"

SQL Server を使った Node.js アプリケーションを作成

ここでは、以下、2 つのシンプルな Node.js アプリを作成します。

基本的な Insert、Update、Delete、Select を実行するアプリ
Node.js の ORM の中でも特に人気のある Sequelize を利用してInsert、Update、Delete、Select を実行するアプリ

SQL Server に接続してクエリを実行する Node.js アプリを作成

まずはじめに、Node.js プロジェクトを初期化します。

# 作業を行うフォルダに移動
cd ~/
# アプリ用のフォルダを作成
mkdir SqlServerSample
cd SqlServerSample
# npm パッケージをセットアップ
npm init -y
# プロジェクトフォルダに tedious と async モジュールをインストール
npm install tedious 
npm install async

プロジェクトの初期化が完了したら、sqlcmd を使用してSQL Serverに接続します。
以下のステートメントを実行して、今回使用するデータベースを作成します。

sqlcmd -S <接続するSQL Serverインスタンス名> -U sa -P <saユーザーパスワード> -Q "CREATE DATABASE SampleDB;"

準備ができたところで、アプリを作成していきます。
お気に入りのエディタを使って、SqlServerSample フォルダ内に connect.js というファイルを作成します。
ユーザー名とパスワードは自分のものに置き換えることを忘れないでください。
なお、筆者は、Visual Studio Code を利用しています。

connect.js

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

// データベースへのコネクション情報を作成
var config = {
  server: 'localhost',              // 接続先の SQL Server インスタンス
  authentication: {
      type: 'default',
      options: {
          userName: 'sa',           // 接続ユーザー名
          password: 'your_password' // 接続パスワード
      }
  },
  options: {
      database: 'SampleDB'          // 接続するデータベース(ここは変えないでください)
  }
}
var connection = new Connection(config);

// コネクション情報を使用して、接続とクエリ実行
connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    console.log('接続されました。');
  }
});

実際に connect.js を動かします。

node connect.js

次に、SqlServerSample フォルダ内に CreateTestData.sql というファイルを作成します。
このファイルでは、T-SQL を使用して、SampleDB 内にスキーマおよびテーブルが作成を作成し、データを登録します。

CreateTestData.sql

CREATE SCHEMA TestSchema;
GO

CREATE TABLE TestSchema.Employees (
  Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  Name NVARCHAR(50),
  Location NVARCHAR(50)
);
GO

INSERT INTO TestSchema.Employees (Name, Location) VALUES
(N'Jared', N'Australia'),
(N'Nikita', N'India'),
(N'Tom', N'Germany');
GO

SELECT * FROM TestSchema.Employees;
GO

SQLCMD を使って CreateTestData.sql を実行します。

sqlcmd -S <接続するSQL Serverインスタンス名> -U sa -P <saユーザーパスワード> -d SampleDB -i ./CreateTestData.sql

SqlServerSample フォルダ内に crud.js という新しいファイルを作成します。
Insert、Update、Delete、Select を行う処理を記載していきます。
ユーザー名とパスワードは自分のものに置き換えることを忘れないでください。

crud.js

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

// データベースへのコネクション情報を作成
var config = {
  server: 'localhost',              // 接続先の SQL Server インスタンス
  authentication: {
      type: 'default',
      options: {
          userName: 'sa',           // 接続ユーザー名
          password: 'your_password' // 接続パスワード
      }
  },
  options: {
    database: 'SampleDB'            // 接続するデータベース(ここは変えないでください)
  }
}

var connection = new Connection(config);

function Start(callback) {
    console.log('開始しています...');
    callback(null, 'Jake', 'United States');
}

function Insert(name, location, callback) {
    console.log("テーブルに '" + name + "' を追加しています...");

    request = new Request(
        'INSERT INTO TestSchema.Employees (Name, Location) OUTPUT INSERTED.Id VALUES (@Name, @Location);',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' 行 追加されました。');
            callback(null, 'Nikita', 'United States');
        }
        });
    request.addParameter('Name', TYPES.NVarChar, name);
    request.addParameter('Location', TYPES.NVarChar, location);

    // SQL ステートメントを実行
    connection.execSql(request);
}

function Update(name, location, callback) {
    console.log("Location '" + location + "' の name '" + name + "' を更新しています...");

    // 依頼された従業員の記録を更新
    request = new Request(
    'UPDATE TestSchema.Employees SET Location=@Location WHERE Name = @Name;',
    function(err, rowCount, rows) {
        if (err) {
        callback(err);
        } else {
        console.log(rowCount + ' 行 更新されました。');
        callback(null, 'Jared');
        }
    });
    request.addParameter('Name', TYPES.NVarChar, name);
    request.addParameter('Location', TYPES.NVarChar, location);

    // SQL ステートメントを実行
    connection.execSql(request);
}

function Delete(name, callback) {
    console.log("テーブルから '" + name + "' を削除しています...");

    // 要求された従業員の記録を削除
    request = new Request(
        'DELETE FROM TestSchema.Employees WHERE Name = @Name;',
        function(err, rowCount, rows) {
        if (err) {
            callback(err);
        } else {
            console.log(rowCount + ' 行 削除しました。');
            callback(null);
        }
        });
    request.addParameter('Name', TYPES.NVarChar, name);

    // SQL ステートメントを実行
    connection.execSql(request);
}

function Read(callback) {
    console.log('テーブルの行データを読み取っています...');

    // テーブルからすべての行を読み込む
    request = new Request(
    'SELECT Id, Name, Location FROM TestSchema.Employees;',
    function(err, rowCount, rows) {
    if (err) {
        callback(err);
    } else {
        console.log(rowCount + ' 行 読み込みました。');
        callback(null);
    }
    });

    // 読み込んだ行データの表示
    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 = "";
    });

    // SQL ステートメントを実行
    connection.execSql(request);
}

function Complete(err, result) {
    if (err) {
        callback(err);
    } else {
        console.log("完了しました!");
    }
}

// コネクション情報を使用して、接続とクエリ実行
connection.on('connect', function(err) {
  if (err) {
    console.log(err);
  } else {
    console.log('接続されました。');

    // 配列内の全ての関数を連続的に実行
    async.waterfall([
        Start,
        Insert,
        Update,
        Delete,
        Read
    ], Complete)
  }
});

実際に、crud.js を動かします。

node crud.js

Sequelize ORM を使って SQL Server に接続する Node.js アプリを作成

アプリのフォルダを作成し、Node の依存関係を初期化します。

# 作業を行うフォルダに移動
cd ~/
# アプリ用のフォルダを作成
mkdir SqlServerSequelizeSample
cd SqlServerSequelizeSample
# npm パッケージをセットアップ
npm init -y
# プロジェクトフォルダに tedious と sequelize モジュールをインストール
npm install tedious
npm install sequelize

SqlServerSequelizeSample フォルダ内に orm.js という新しいファイルを作成します。
パスワードは自分のものに置き換えることを忘れないでください。

orm.js

var Sequelize = require('sequelize');
var userName = 'sa';            // 接続ユーザー名
var password = 'your_password'; // 接続パスワード
var hostName = 'localhost';     // 接続先の SQL Server インスタンス
var sampleDbName = 'SampleDB';  // 接続するデータベース(ここは変えないでください)

// Sequelize を初期化して SampleDB に接続
var sampleDb = new Sequelize(sampleDbName, userName, password, {
    dialect: 'mssql',
    host: hostName,
    port: 1433,     // 接続ポート番号
    logging: false, // ロギングを無効化(デフォルトは console.log)

    dialectOptions: {
        requestTimeout: 30000 // タイムアウトは 30 秒
    }
});

// 'User' モデルを定義
var User = sampleDb.define('user', {
    firstName: Sequelize.STRING,
    lastName: Sequelize.STRING
});

// 'Task' モデルを定義
var Task = sampleDb.define('task', {
    title: Sequelize.STRING,
    dueDate: Sequelize.DATE,
    isComplete: Sequelize.BOOLEAN
});

// User と Task は 1:N の関係
User.hasMany(Task);

console.log('**Sequelize と MSSQL を使った Node CRUD のサンプル**');

// データベース内のテーブルとリレーションシップを DROP および CREATE するよう Sequelize に指示
sampleDb.sync({force: true})
.then(function() {
    console.log('\nモデルからデータベーススキーマを作成.');

    // Create デモ: ユーザーインスタンスを作成し、データベースに保存
    User.create({firstName: 'Anna', lastName: 'Shrestinian'})
    .then(function(user) {
        console.log('\n作成されたユーザー:', user.get({ plain: true}));

        // Create デモ: タスクインスタンスを作成し、データベースに保存
        Task.create({
            title: 'Ship Helsinki', dueDate: new Date(2017,04,01), isComplete: false
        })
        .then(function(task) {
            console.log('\n作成されたタスク:', task.get({ plain: true}));

            // Association デモ: ユーザーにタスクを割り当てる
            user.setTasks([task])
            .then(function() {
                console.log('\n割り当てられたタスク \''
            + task.title
            + '\' to user ' + user.firstName
            + ' ' + user.lastName);

                // Read デモ: ユーザー 'Anna' に割り当てられた未完了のタスクを見つける
                User.findAll({
                    where: { firstName: 'Anna'},
                    include: [{
                        model: Task,
                        where: { isComplete: false }
                    }]
                })
                .then(function(users) {
                    console.log('\nAnna に割り当てられた未完了のタスク:\n',
                JSON.stringify(users));

                    // Update デモ: タスクの 'dueDate' を変更
                    Task.findById(1).then(function(task) {
                        console.log('\n更新中のタスク:',
                task.title + ' ' + task.dueDate);
                        task.update({
                            dueDate: new Date(2016,06,30)
                        })
                        .then(function() {
                            console.log('期限が変更されました。:',
                    task.title + ' ' + task.dueDate);

                            // Delete デモ: dueDate が 2016年 であるすべてのタスクを削除
                            console.log('\n期限が2016年になっているタスクをすべて削除しています。');
                            Task.destroy({
                                where: { dueDate: {$lte: new Date(2016,12,31)}}
                            })
                            .then(function() {
                                Task.findAll()
                                .then(function(tasks) {
                                    console.log('削除後のデータベース内のタスク一覧:',
                        JSON.stringify(tasks));
                                    console.log('\nすべて完了しました！');
                                })
                            })
                        })
                    })
                })
            })
        })
    })
})

実際に、orm.js を動かします。

node orm.js

これで、2つ目の Node.js アプリの作成が終わりました。最後に、SQL Server の Columnstore 機能を使って Node.js アプリを高速化する方法について学びます。

Node.js アプリを 100 倍速にする

これまでで基本的なことは理解できたと思います。最後は、SQL Server を使用してアプリをより良くする方法を見てみます。このモジュールでは、Columnstore Index の簡単な例と、Columnstore Index がどのようにデータ処理速度を向上させるかを確認します。Columnstore インデックスは、従来の rowstore インデックスに比べて、分析ワークロードでは最大 100 倍のパフォーマンス向上、データ圧縮では最大 10 倍のパフォーマンス向上を実現できます。

SQLCMD を使用して 500 万のデータを含む新しいテーブルを作成

アプリのフォルダを作成します。

# 作業を行うフォルダに移動
cd ~/
# アプリ用のフォルダを作成
mkdir SqlServerColumnstoreSample
cd SqlServerColumnstoreSample

SqlServerColumnstoreSample フォルダ内に CreateSampleTable.sql という名前の新しいファイルを作成します。

CreateSampleTable.sql

sqlcmd -S <接続するSQL Serverインスタンス名> -U sa -P <saユーザーパスワード> -d SampleDB -t 60000 -Q "WITH a AS (SELECT * FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))
SELECT TOP(5000000)
ROW_NUMBER() OVER (ORDER BY a.a) AS OrderItemId
,a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a AS OrderId
,a.a * 10 AS Price
,CONCAT(a.a, N' ', b.a, N' ', c.a, N' ', d.a, N' ', e.a, N' ', f.a, N' ', g.a, N' ', h.a) AS ProductName
INTO Table_with_5M_rows
FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;"

sqlcmd を使ってデータベースに接続し、SQL スクリプトを実行して 500 万行データを持つテーブルを作成します。これは実行に数分かかるかもしれません。

sqlcmd -S <接続するSQL Serverインスタンス名> -U sa -P <saユーザーパスワード> -d SampleDB -i ./CreateSampleTable.sql

テーブルをクエリして時間を測定する Node.js アプリを作成

プロジェクトフォルダ内で、Node.js の依存関係を初期化します。

# npm パッケージをセットアップ
npm init -y
# プロジェクトフォルダに tedious と async モジュールをインストール
npm install tedious 
npm install node-uuid
npm install async

SqlServerColumnstoreSample フォルダ内に columnstore.js というファイルを作成します。
パスワードは自分のものに置き換えることを忘れないでください。

columnstore.js

var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var uuid = require('node-uuid');
var async = require('async');

var config = {
    server: 'localhost',              // 接続先の SQL Server インスタンス
    authentication: {
        type: 'default',
        options: {
            userName: 'sa',           // 接続ユーザー名
            password: 'your_password' // 接続パスワード
        }
    },
    options: {
        database: 'SampleDB'          // 接続するデータベース(ここは変えないでください)
    }
    // Azure SQL Databaseに接続するときは、次のオプションが必要
    //options: {encrypt: true, database: 'yourDatabase'}
};


var connection = new Connection(config);
function exec(sql) {
    var timerName = "QueryTime";

    var request = new Request(sql, function(err) {
        if (err) {
            console.log(err);
        }
    });
    request.on('doneProc', function(rowCount, more, rows) {
        if(!more){
            console.timeEnd(timerName);
        }
    });
    request.on('row', function(columns) {
        columns.forEach(function(column) {
            console.log("Sum: " +  column.value);
        });
    });
        console.time(timerName);
    connection.execSql(request);
}
// 接続を開き、クエリを実行
connection.on('connect', function(err) {
    async.waterfall([
        function(){
            exec('SELECT SUM(Price) FROM Table_with_5M_rows');
        },
    ]);
});

クエリ実行にかかる時間を測定

アプリを実行し、時間を計測します。

node columnstore.js

テーブルにカラムストアインデックスを追加

SQLCMD を実行して、カラムストアインデックスを追加します。

sqlcmd -S <接続するSQL Serverインスタンス名> -U sa -P <saユーザーパスワード> -d SampleDB -Q "CREATE CLUSTERED COLUMNSTORE INDEX Columnstoreindex ON Table_with_5M_rows;"

columnstore.js スクリプトを再実行して今回のクエリが完了するまでにかかった時間に注目

node columnstore.js

おめでとうございます。カラムストアインデックスを使って Node.js アプリを高速化しました！

おわりに

以上で、「Windows上でSQL Serverを使用してNode.jsアプリを作成する」は終了です。Build an app using SQL Server には、他言語での SQL Server アプリを作成するチュートリアルがあります。ぜひ、他の言語でも試してみてください。