Node.jsでnode-mssqlを利用してSQLServerに接続する

Node.js から SQLServer へ接続について、Microsoft のサイトにはtedious モジュールでの開発方法が掲載されています。

tedious モジュールを直接利用してコーディングする場合、どうしてもイベントの記述が煩雑になりがちなので、ずっと敬遠してました。

しかし、何気に開発元の GitHub を覗いたところ、便利そうなモジュールがありました。

README.md を読んで興味が湧いたので、CRUD なコードで試してみました。

1. 環境

• OS: KDE neon 5.21.3 (Ubuntu 20.04 ベース)
• Node.js v14.16.0 (npm 6.14.11)
• SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64) on Linux
• SQL Server Command Line Tool Version 17.7

2. プロジェクト

(1) 準備

まずは適当なディレクトリを用意します。(今回は ~/work/crud_mssql)

$ cd ~/work
$ mkdir crud_mssql
$ cd crud_mssql

初期化してモジュールをインストールします。

$ npm init -y
$ npm install mssql

(2) データベース

テスト用のデータベースを用意します。(今回は my_test_db)
※既存のデータベースを利用する場合は不要です。

$ sqlcmd -S localhost -U sa -P abcd1234$ -Q "CREATE DATABASE my_test_db;"

(3) ファイル

下記のソースコードをコピーして crud.js というファイルを作成します。
※ソース中の config の内容は、試す環境に合わせて変更してください。

・ソースコード: crud.js

const sql = require('mssql');

const config = {
    server: 'localhost',
    user: 'sa',
    password: 'abcd1234$',
    database: 'my_test_db',
    options: {
        enableArithAbort: true,
    }
};

(async () => {
    console.log("#### Start ####");
    try {
        const conn = await sql.connect(config);

        console.log("-- DROP & CREATE TABLE");
        await conn.query("DROP TABLE IF EXISTS 会員名簿");
        await conn.query("CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)");

        console.log("-- INSERT");
        for (const [id, name, birthday] of [
            [ 110, "岸本 龍也", "1989-11-06" ],
            [ 210, "荒井 伸次郎", "1974-01-30" ],
            [ 105, "江口 美奈", "1979-06-23" ],
            [ 304, "長田 隆次", "1991-05-25" ],
            [ 307, "中居 雄樹", "1984-02-29" ],
        ]) {
            await conn.request()
                .input('id', id)
                .input('name', name)
                .input('birthday', birthday)
                .query("INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (@id,@name,@birthday)");
        }
        display(await conn.query("SELECT * FROM 会員名簿"));

        console.log("-- UPDATE");
        const update_id = 307;
        const new_name = "中井 雄樹";
        await conn.request()
            .input('name', new_name)
            .input('id', update_id)
            .query("UPDATE 会員名簿 SET 氏名=@name WHERE 番号=@id");
        display(await conn.query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号"));

        console.log("-- DELETE");
        const delete_id = 210;
        await conn.request()
            .input('id', delete_id)
            .query("DELETE FROM 会員名簿 WHERE 番号=@id");
        display(await conn.query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 番号"));

        conn.close();
    } catch (e) {
        console.log("#### Catch !! ####");
        console.log(e);
    }
    console.log("#### Finish ####");
})()

function display(rs) {
    // カラム名
    const columns = Object.keys(rs.recordset.columns);
    const header = [];
    for (const col_name of columns) {
        header.push(` | ${col_name}`);
    }
    header.push(" |");
    console.log(header.join(""));
    // ロー
    let row_count = 0;
    for (const row of rs.recordset) {
        const buff = [];
        for (const col_name of columns) {
            buff.push(` | ${row[col_name]}`);
        }
        buff.push(" |");
        console.log(buff.join(""));
        ++row_count;
    }
    console.log(`結果 ${row_count} 行 (${rs.rowsAffected})`);
}

準備は以上です。

3. 実行結果

$ node crud.js 
#### Start ####
-- DROP & CREATE TABLE
-- INSERT
 | 番号 | 氏名 | 誕生日 |
 | 110 | 岸本 龍也 | Mon Nov 06 1989 09:00:00 GMT+0900 (日本標準時) |
 | 210 | 荒井 伸次郎 | Wed Jan 30 1974 09:00:00 GMT+0900 (日本標準時) |
 | 105 | 江口 美奈 | Sat Jun 23 1979 09:00:00 GMT+0900 (日本標準時) |
 | 304 | 長田 隆次 | Sat May 25 1991 09:00:00 GMT+0900 (日本標準時) |
 | 307 | 中居 雄樹 | Wed Feb 29 1984 09:00:00 GMT+0900 (日本標準時) |
結果 5 行 (5)
-- UPDATE
 | 番号 | 氏名 |
 | 105 | 江口 美奈 |
 | 110 | 岸本 龍也 |
 | 210 | 荒井 伸次郎 |
 | 304 | 長田 隆次 |
 | 307 | 中井 雄樹 |
結果 5 行 (5)
-- DELETE
 | 番号 | 氏名 | 誕生日 |
 | 105 | 江口 美奈 | Sat Jun 23 1979 09:00:00 GMT+0900 (日本標準時) |
 | 110 | 岸本 龍也 | Mon Nov 06 1989 09:00:00 GMT+0900 (日本標準時) |
 | 304 | 長田 隆次 | Sat May 25 1991 09:00:00 GMT+0900 (日本標準時) |
 | 307 | 中井 雄樹 | Wed Feb 29 1984 09:00:00 GMT+0900 (日本標準時) |
結果 4 行 (4)
#### Finish ####

4. 補足

tedious モジュールを直接扱った例が Microsoft のサイトにあります。

• Node.js Ubuntu / JavaScript: ... This will insert, update, delete, and read a few rows.

同じような処理ですが、Request で SQL クエリを発行した後に、request.on() でイベントを拾う必要があり、その部分がどうしても煩雑になります。

node-mssql モジュールでは async / await や method chaining が使えるので、コードの見通しが随分良くなってると思います。

接続プールについて考慮されてるもの良いですね。

テーブル名や項目名は敢えて日本語にしてますが、文字化けせずに処理できてます。ODBC ドライバより安心して使えるとは。

※ ODBC ドライバでの接続についてはこちらにまとめてあります。

• LinuxのNode.jsでnode-odbcとFreeTDSのODBCドライバからSQLServerに接続する
• LinuxのNode.jsでnode-odbcとMS版ODBCドライバからSQLServerに接続する

特に問題が見つからなければ、node-mssql モジュールでいけそうです。

5. Mac でも動作します　※追記 2021.3.24

前述のソースコードは Mac 環境の Node.js でも動作しました。
※SQLServer のサービスが別の IP アドレス なので、localhost の書き換えは必要です。

検証した Linux 環境をそのままに、Mac からアクセスできました。

• OS: macOS Mojave v10.14.6
• Node.js v14.16.0 (npm 6.14.11) ※nodebrew より

npm install mssqlを実行する際、 openssl のエラーが発生しましたが、エラーログの内容から、Homebrew が導入済みの環境であればbrew install opensslを実行して対処できました。