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

Node.js から SQLServer に接続する場合、node-mssql または直接 tedious モジュールを利用すればいいのですが、ODBC ドライバでの接続方法も調べてみました。

www.npmjs.com で調べたところ node-odbc モジュールを見つけました。

ODBC ドライバマネージャの unixODBC とそれに対応した ODBC ドライバであれば接続できるとの事なので、node-mssql モジュールの場合と同様なコードで試してみます。

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

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 (w/ Microsoft ODBC Driver) Version 17.7
unixODBC 2.3.7
Ubuntuパッケージ: focal / freetds-dev (1.1.6-1.1)
Ubuntuパッケージ: focal / tdsodbc (1.1.6-1.1)

Microsoft 版 ODBC ドライバだと文字化けが発生するため、FreeTDS の ODBC ドライバ (tdsodbc) で試してます。

2. プロジェクト

(1) 準備

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

$ cd ~/work
$ mkdir crud_tdsodbc
$ cd crud_tdsodbc

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

$ npm init -y
$ npm install odbc

(2) データベース

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

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

(3) ファイル

下記のソースコードをコピーして crud.js というファイルを作成します。

なお、ソース中の conn_str の値は、freetds.conf を参照しない DSN-less 接続です。
試す場合は環境に適した接続文字列を指定してください。

・ソースコード: crud.js

const odbc = require("odbc");
const conn_str = "Driver={FreeTDS};Server=localhost;Port=1433;TDS_Version=7.4;Client_Charset=UTF-8;UID=sa;PWD=abcd1234$;Database=my_test_db;";

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

        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" ],
        ]) {
            const inserted = await conn.query(`INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (${id},N'${name}','${birthday}')`);
            console.log(inserted.statement);
        }
        display(await conn.query("SELECT * FROM 会員名簿"));

        console.log("-- UPDATE");
        const update_id = 307;
        const new_name = "中井 雄樹";
        const updated = await conn.query(`UPDATE 会員名簿 SET 氏名=N'${new_name}' WHERE 番号=${update_id}`);
        console.log(updated.statement);
        display(await conn.query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号"));

        console.log("-- DELETE");
        const delete_id = 210;
        const deleted = await conn.query(`DELETE FROM 会員名簿 WHERE 番号=${delete_id}`);
        console.log(deleted.statement);
        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 = rs.columns;
    const header = [];
    for (const col of columns) {
        header.push(` | ${col.name}`);
    }
    header.push(" |");
    console.log(header.join(""));
    // ロー表示
    let row_count = 0;
    for (const row of rs) {
        const buff = [];
        for (const col of columns) {
            buff.push(` | ${row[col.name]}`);
        }
        buff.push(" |");
        console.log(buff.join(""));
        ++row_count;
    }
    console.log(`結果 ${row_count} 行 (${rs.length})`);
}

準備は以上です。

3. 実行結果

$ node crud.js 
#### Start ####
-- DROP & CREATE TABLE
-- INSERT
INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (110,N'岸本 龍也','1989-11-06')
INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (210,N'荒井 伸次郎','1974-01-30')
INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (105,N'江口 美奈','1979-06-23')
INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (304,N'長田 隆次','1991-05-25')
INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (307,N'中居 雄樹','1984-02-29')
 | 番号 | 氏名 | 誕生日 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 210 | 荒井 伸次郎 | 1974-01-30 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 307 | 中居 雄樹 | 1984-02-29 |
結果 5 行 (5)
-- UPDATE
UPDATE 会員名簿 SET 氏名=N'中井 雄樹' WHERE 番号=307
 | 番号 | 氏名 |
 | 105 | 江口 美奈 |
 | 110 | 岸本 龍也 |
 | 210 | 荒井 伸次郎 |
 | 304 | 長田 隆次 |
 | 307 | 中井 雄樹 |
結果 5 行 (5)
-- DELETE
DELETE FROM 会員名簿 WHERE 番号=210
 | 番号 | 氏名 | 誕生日 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 307 | 中井 雄樹 | 1984-02-29 |
結果 4 行 (4)
#### Finish ####

node-odbc モジュールでは .query() の戻り値に発行した SQL 文が保持されているので、ついでに表示してます。

4. 補足

パラメータ値を扱う際はプレースホルダを利用したいのですが、私の環境では node-odbc モジュールと FreeTDS の ODBC ドライバの組み合わせでクエリを発行すると unixODBC のエラーが発生します。

#### Catch !! ####
[Error: [odbc] Error retrieving information about the parameters in the statement] {
  odbcErrors: [
    {
      state: 'IM001',
      code: 0,
      message: '[unixODBC][Driver Manager]Driver does not support this function'
    }
  ]
}

仕方ないので、この件についてはパラメータ値をテンプレートリテラルで処理して回避しました。

なお、この現象は C++ の nanodbc ライブラリでも確認してます。
node-odbc モジュールだけの問題ではありません。

ちなみに node-odbc モジュールと Microsoft 版の ODBC ドライバの組み合わせではプレースホルダが使えます。※文字化けを回避できれば、の話ですが...

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

刹那（せつな）の瞬き

Willkömmen! Ich heiße Setsuna. Haben Sie etwas Zeit für mich?