刹那(せつな)の瞬き

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

RustでSQLServerへの接続プールにbb8 / bb8-tiberiusを試してみた

バックエンドで Tiberius を利用する非同期な接続プールに bb8 があります。

bb8 は SQLServer 用のアダプタ bb8-tiberius と組み合わせて使用します。
※async-std ではなく tokio 用です。

....

接続プールの使用方法はシンプルなので、前述したサイトの説明に従ってコードを記述すれば問題ないと思います。

接続プールを構築する際、接続数の最大値を .max_size() で指定できるのですが、この値の増減による実行結果を確認してみました。

1. 環境とデータベース

Rustでasync/awaitに対応したTiberiusからSQLServerに接続する

※ここで構築した環境を引き続き利用します。

  • OS: KDE neon 5.21.4 (Ubuntu 20.04 ベース) / macOS Mojave v10.14.6
  • rustc 1.51.0
  • SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) on Linux
  • データベース: my_test_db

2. プロジェクト

(1) 準備

適当なディレクトリにプロジェクトを作成します。(今回は ~/work/conn_pool)

$ cd ~/work
$ cargo new conn_pool
$ cd conn_pool
(2) ファイル

・Cargo.toml の編集

[dependencies] セクションに下記の内容を追加します。

[dependencies]
tiberius = { version = "0.5", features = ["chrono"] }
bb8 = "0.7"
bb8-tiberius = "0.5"
tokio = { version = "1.5", features = ["time"] }
futures = "0.3"
chrono = "0.4"

ソースコード: src/main.rs

下記のソースコードをコピーして src/main.rs を書き換えます。
※ソース中の conn_str の内容は、試す環境に合わせて変更してください。

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    println!("#### Start ####");
    let started = std::time::Instant::now();

    let conn_str = "Server=tcp:localhost,1433;TrustServerCertificate=yes;Database=my_test_db;UID=sa;PWD=abcd1234$";
    let manager = bb8_tiberius::ConnectionManager::build(conn_str)?;
    let pool = bb8::Pool::builder().max_size(10).build(manager).await?;

    let mut threads = Vec::new();
    for idx in 0..20 {
        let pool_tmp = pool.clone();
        let handle = tokio::spawn(async move {
            println!("Thread #{}", idx);
            let mut client = pool_tmp.get().await.unwrap();
            tokio::time::sleep(tokio::time::Duration::from_millis(200)).await;
            let result = client
                .simple_query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 誕生日 DESC")
                .await
                .unwrap()
                .into_first_result()
                .await
                .unwrap();
            for row in result {
                println!(
                    "#{} | {} | {} | {} |",
                    idx,
                    row.get::<i32, _>("番号").unwrap(),
                    row.get::<&str, _>("氏名").unwrap().to_string(),
                    row.get::<chrono::NaiveDate, _>("誕生日")
                        .unwrap()
                        .format("%Y/%m/%d"),
                );
            }
        });
        threads.push(handle);
    }
    futures::future::join_all(threads).await;

    println!("#### Finish ####");
    println!("経過時間: {:?}", started.elapsed());
    Ok(())
}

このコードでは、接続プールの最大値を 10 とし、非同期タスクを 20 個実行します。
また、SQL クエリの実行時間が短いため、敢えて非同期タスクの中で 200 ミリ秒待機しています。
最後に、すべての非同期タスクが完了した後、処理の経過時間を表示します。

準備は以上です。

3. 実行結果

.max_size(10) として実行した結果です。

$ cargo run
    Finished dev [unoptimized + debuginfo] target(s) in 0.04s
     Running `target/debug/conn_pool`
#### Start ####
Thread #0
Thread #2
Thread #1
Thread #3
Thread #4

・・・(ざっくり省略)・・・

#11 | 307 | 中井 雄樹 | 1984/02/29 |
#19 | 105 | 江口 美奈 | 1979/06/23 |
#16 | 307 | 中井 雄樹 | 1984/02/29 |
#11 | 105 | 江口 美奈 | 1979/06/23 |
#16 | 105 | 江口 美奈 | 1979/06/23 |
#### Finish ####
経過時間: 539.765411ms

4. 実行結果のまとめ

今回の処理では、経過時間は接続プールの最大接続数によって変化します。

最大接続数を 1 から 10 まで変化させた場合の経過時間をまとめました。
参考までに最大接続数を非同期タスク数と同じ 20 に設定した場合の結果も載せています。

最大接続数 1回目 2回目 3回目 4回目 5回目
.max_size(1) 4.243623s 4.17383s 4.180024s 4.175134s 4.174535s
.max_size(2) 2.416127s 2.159097s 2.159124s 2.155713s 2.17077s
.max_size(3) 1.773368s 1.544799s 1.553429s 1.552768s 1.58303s
.max_size(4) 1.178223s 1.141275s 1.13779s 1.14564s 1.154331s
.max_size(5) 938.821ms 943.597ms 945.557ms 933.896ms 948.629ms
.max_size(6) 942.27ms 935.486ms 941.867ms 946.894ms 936.995ms
.max_size(7) 822.035ms 737.501ms 744.878ms 743.485ms 752.166ms
.max_size(8) 750.744ms 741.996ms 744.562ms 731.633ms 747.467ms
.max_size(9) 754.524ms 741.767ms 743.779ms 752.383ms 739.935ms
.max_size(10) 538.341ms 539.604ms 542.781ms 541.15ms 537.545ms
.max_size(20) 349.346ms 355.685ms 352.035ms 354.031ms 349.371ms

非同期な接続プールとして特に問題なく、期待通りの結果になりました。

当然の事ですが、この結果は 1 接続あたりの処理数によって変化しています。
最大接続数が 20 の場合、1 接続 1 処理になるので、この結果が最速です。

また、最大接続数が 10 〜 19 の場合、1 接続あたり 1 または 2 処理になるので経過時間に大きな変化はありません。そのため、最大接続数 10 以外は省略しました。

4. 補足

データベースへの接続をここまで簡単に非同期タスクに渡せるのは本当に助かります。

最初は async-std に対応している SQLServer 用の接続プールを探してたのですが、現時点では見つけられませんでした。

bb8-tiberius は features の設定で async-std にも対応してるみたいなので、async-std でコードを書きたい場合は bb8 の対応や代替クレートを待つ感じでしょうか。

個人的には tokio でも特に問題ないので、このまま利用したいと思います。

 

Rustでasync/awaitに対応したTiberiusからSQLServerに接続する

一年くらい前に Rust から ODBC ドライバで接続する方法を試してました。

その当時、直接 TDS プロトコルを扱う tiberius クレートも試してみたのですが、依存するクレートのバージョンを固定する必要があり、コードが書き辛い状態でした。

しかし、改めて確認したところ、いつの間にやら async/await に対応した tiberius クレートの開発が進んでます。

tokio だけでなく async-std にも対応しているとの事なので、今回は async-std を利用して CRUD なコードを試してみました。

1. 環境

2. プロジェクト

(1) 準備

適当なディレクトリにプロジェクトを作成します。(今回は ~/work/crud_mssql)

$ cd ~/work
$ cargo new crud_mssql
$ cd crud_mssql
(2) データベース

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

$ sqlcmd -S localhost -U sa -P abcd1234$ -Q "CREATE DATABASE my_test_db;"
(3) ファイル

・Cargo.toml の編集

[dependencies] セクションに下記の内容を追加します。

[dependencies]
tiberius = { version = "0.5", features = ["chrono"] }
async-std = { version = "1.9", features = ["attributes"] }
chrono = "0.4"

ソースコード: src/main.rs

下記のソースコードをコピーして src/main.rs を書き換えます。
※ソース中の conn_str の内容は、試す環境に合わせて変更してください。

use async_std::net::TcpStream;
use tiberius::{Client, ColumnData, Config, FromSqlOwned, QueryResult};

#[async_std::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    println!("#### Start ####");

    let conn_str = "Server=tcp:localhost,1433;TrustServerCertificate=yes;Database=my_test_db;UID=sa;PWD=abcd1234$";
    let config = Config::from_ado_string(conn_str).unwrap();
    let tcp = TcpStream::connect(config.get_addr()).await?;
    tcp.set_nodelay(true)?;
    let mut client = Client::connect(config, tcp).await?;

    println!("-- DROP & CREATE TABLE");
    client.execute("DROP TABLE IF EXISTS 会員名簿", &[]).await?;
    client
        .execute(
            "CREATE TABLE 会員名簿 (番号 int, 氏名 nvarchar(40), 誕生日 date)",
            &[],
        )
        .await?;

    println!("-- INSERT");
    let members = vec![
        (110, "岸本 龍也", "1989-11-06"),
        (210, "荒井 伸次郎", "1974-01-30"),
        (105, "江口 美奈", "1979-06-23"),
        (304, "長田 隆次", "1991-05-25"),
        (307, "中居 雄樹", "1984-02-29"),
    ];
    for (id, name, birthday) in members {
        let inserted = client
            .execute(
                "INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (@P1, @P2, @P3)",
                &[&id, &name, &birthday],
            )
            .await?;
        assert_eq!(&[1], inserted.rows_affected());
    }
    let result = client.simple_query("SELECT * FROM 会員名簿").await?;
    display(result).await?;

    println!("-- UPDATE");
    let update_id = 307;
    let new_name = "中井 雄樹";
    let updated = client
        .execute(
            "UPDATE 会員名簿 SET 氏名=@P1 WHERE 番号=@P2",
            &[&new_name, &update_id],
        )
        .await?;
    assert_eq!(&[1], updated.rows_affected());
    let result = client
        .simple_query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号")
        .await?;
    display(result).await?;

    println!("-- DELETE");
    let delete_id = 210;
    let deleted = client
        .execute("DELETE FROM 会員名簿 WHERE 番号=@P1", &[&delete_id])
        .await?;
    assert_eq!(&[1], deleted.rows_affected());
    let result = client
        .simple_query("SELECT 番号,氏名,誕生日 FROM 会員名簿 ORDER BY 番号")
        .await?;
    display(result).await?;

    println!("#### Finish ####");
    Ok(())
}

async fn display(result_set: QueryResult<'_>) -> Result<(), Box<dyn std::error::Error>> {
    let cols = result_set.columns().unwrap();
    for col in cols {
        print!(" | {}", col.name());
    }
    println!(" |");

    let rows = result_set.into_first_result().await?;
    let rows_affcted = rows.len();
    let mut row_count = 0;
    for row in rows {
        for col in row {
            match col {
                ColumnData::I32(Some(v)) => print!(" | {}", v),
                ColumnData::String(Some(v)) => print!(" | {}", v),
                ColumnData::Date(_) => {
                    print!(" | {}", chrono::NaiveDate::from_sql_owned(col)?.unwrap())
                }
                _ => print!(" | {:?}", col),
            };
        }
        println!(" |");
        row_count += 1;
    }
    println!("結果 {} 行 ({})", row_count, rows_affcted);
    Ok(())
}

準備は以上です。

3. 実行結果

$ cargo run
    Finished dev [unoptimized + debuginfo] target(s) in 1.81s
     Running `target/debug/crud_mssql`
#### Start ####
-- DROP & CREATE TABLE
-- INSERT
 | 番号 | 氏名 | 誕生日 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 210 | 荒井 伸次郎 | 1974-01-30 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 307 | 中居 雄樹 | 1984-02-29 |
結果 5 行 (5)
-- UPDATE
 | 番号 | 氏名 |
 | 105 | 江口 美奈 |
 | 110 | 岸本 龍也 |
 | 210 | 荒井 伸次郎 |
 | 304 | 長田 隆次 |
 | 307 | 中井 雄樹 |
結果 5 行 (5)
-- DELETE
 | 番号 | 氏名 | 誕生日 |
 | 105 | 江口 美奈 | 1979-06-23 |
 | 110 | 岸本 龍也 | 1989-11-06 |
 | 304 | 長田 隆次 | 1991-05-25 |
 | 307 | 中井 雄樹 | 1984-02-29 |
結果 4 行 (4)
#### Finish ####

4. 補足

前述のコードでは async-std で属性によるランタイムの起動を指定してます。
初めての async-std だったのですが、async_std::task::spawn() する良い例が思い浮かばなかったので、CRUDの処理をそのまま書いてます。

この件とは別にコードを書いて試したところ、ODBC 接続を試した際に微妙だった日本語や絵文字や結合文字等は、特に問題ありませんでした。
日付型や数値型についても、それぞれ chrono, rust_decimal を features に指定すれば利用できてます。

現段階では、接続プールが実装されていないため、非同期な接続プールを利用するには、少々工夫が必要です。
※と書きましたが、tokio にして bb8 / bb8-tiberius で進める方が無難です。

何れにしても、以前より格段に書きやすく喜ばしい限りです。

SQLServerでサロゲートペアや結合文字列を扱う前に照合順序を確認しよう

SQL Server 2019 での話です。

Unicode の𠮷(つちよし), 𩸽(ほっけ)🍺(ビール), 👨‍👩‍👧‍👦(家族: 父母と女の子と男の子)のような絵文字等を扱う場合、データベースがサロゲートペアや結合文字列に対応している必要があります。

SQL ServerUnicode データ型は SQL Server 2005 から対応済みで、SQL Server 2019 では新しい UTF-8 対応の照合順序も加わり、より扱いやすくなってます。

公式サイトにも説明があります。

しかし、この説明の中に補助文字に関する記述があり、SCA 照合順序なしで Unicode を扱う場合、結果として「 0 – 0xFFFF の範囲外の文字は無視されてしまう」事が示唆されています。

これが地味に影響する部分がクエリというか T-SQL の式に Unicode 文字列を使用する場合で、前述の範囲外の文字に該当する場合、その文字コードが除外された状態で式が評価される事があります。

つまり、照合順序の設定次第では「式の評価が異なるため、意図しない結果を得てしまう場合がある」という事です。

検証してみる

後述する作業は Azure Data Studio (または SSMS) から SQL Server 2019 に接続した状態で行いました。※Windows版 / Linux版 どちらも同じ結果でした。

(1) 検証用データベースの作成と照合順序の確認

検証用データベースを 2 種類用意します。

my_test_db 既定の照合順序のまま ※Japanese_CI_AS
my_xjis140_db 照合順序に Japanese_XJIS_140_CI_AS を指定

ここでは、次の SQL 文を実行してデータベースを作成します。

CREATE DATABASE my_test_db;
CREATE DATABASE my_xjis140_db COLLATE Japanese_XJIS_140_CI_AS;

照合順序の確認ではデータベースが Auto Closed のままだと情報を取得できません。
なので Azure Data Studio であれば、Manage や New Query のデータベース切替等で活性化します。

活性化が済んだら照合順序を確認します。

SELECT SERVERPROPERTY('Collation') as N'サーバの照合順序';
SELECT name, collation_name FROM sys.databases WHERE name LIKE 'my_%';

f:id:infinity_volts:20210331124025p:plain

※ここで collation_name が NULL の場合、データベースが活性化してません。

(2) Unicode 文字の格納

テーブル変数に非Unicode データ型, Unicode データ型, UTF8 エンコード対応のデータ型を用意します。そこにサロゲートペアで表現される絵文字🍺と結合絵文字👨‍👩‍👧‍👦を格納し、それぞれの値を確認します。

DECLARE @a table (
    non_unicode varchar(40),
    unicode_utf16 nvarchar(40),
    unicode_utf8 varchar(40) COLLATE Japanese_XJIS_140_CI_AS_UTF8
);
INSERT INTO @a VALUES (N'🍺', N'🍺', N'🍺');
INSERT INTO @a VALUES (N'👨‍👩‍👧‍👦', N'👨‍👩‍👧‍👦', N'👨‍👩‍👧‍👦');
SELECT * FROM @a;
SELECT
    CAST(non_unicode AS varbinary),
    CAST(unicode_utf16 AS varbinary),
    CAST(unicode_utf8 AS varbinary)
FROM @a;

データベースに対してクエリを発行します。
※下画面の例は my_test_db で実行した結果です。

f:id:infinity_volts:20210331125718p:plain

これは my_test_db, my_xjis140_db どちらで実行しても同じ結果になります。

varchar では文字が化けて ?? で埋められていますが、nvarchar と UTF8 エンコーディングを指定した varchar では、それぞれ UTF-16LE, UTF-8 で符号化された値が格納されます。

データの格納先としては、データベースの照合順序に関わらず、問題なく扱える事が確認できました。

(3) クエリの式で Unicode 文字列を使用

テーブル変数に絵文字を含む値を格納し、データを抽出してみます。

DECLARE @b table (
    name_utf16 nvarchar(40),
    name_utf8 varchar(40) COLLATE Japanese_XJIS_140_CI_AS_UTF8
);
INSERT INTO @b VALUES (N'超🍺ドライ', N'超🍺ドライ');
INSERT INTO @b VALUES (N'墨🍺🍺ラベル', N'墨🍺🍺ラベル');
INSERT INTO @b VALUES (N'最速🍺絞り', N'最速🍺絞り');
SELECT * FROM @b;
SELECT * FROM @b WHERE name_utf16 LIKE N'%🍺🍺%';
SELECT * FROM @b WHERE name_utf8 LIKE N'%🍺🍺%';

このクエリを実行したところ、my_test_db (照合順序: Japanese_CI_AS) と my_xjis140_db (照合順序: Japanese_XJIS_140_CI_AS) で異なる結果になりました。

f:id:infinity_volts:20210331141753p:plain

my_xjis140_db では特に問題なく、意図した結果が得られました。

しかし、my_test_db では格納したデータが期待通りに抽出できません。
照合順序 Japanese_CI_AS に補助文字のサポートが含まれないからです。

....

式の評価では、サポート対象外な文字を除いての評価になるようです。
例えば、次のクエリを my_test_db で実行すると、墨🍺ラベルが返却されます。

DECLARE @c table (
    name_utf16 nvarchar(40)
);
INSERT INTO @c VALUES (N'超🍺ドライ');
INSERT INTO @c VALUES (N'中トロ🍣');
INSERT INTO @c VALUES (N'こはだ🍣');
INSERT INTO @c VALUES (N'墨🍺ラベル');
INSERT INTO @c VALUES (N'あじ🍣');
INSERT INTO @c VALUES (N'最速🍺絞り');
SELECT * FROM @c WHERE name_utf16 = N'墨🗻𩸽🍣𠮷🍺ラベル';

式の評価の際、墨🍺ラベル墨🗻𩸽🍣𠮷🍺ラベルに含まれるサポート対象外の文字を省略すると、どちらも墨ラベルになります。
この状態で式を評価すると元の文字列が異なるのに「等しい」と評価されてしまいます。

影響は WHERE 句だけでなく、SELECT 句でも同様です。

DECLARE @d table (
    name_utf16 nvarchar(40)
);
INSERT INTO @d VALUES (N'超🍺ドライ');
INSERT INTO @d VALUES (N'中トロ🍣');
INSERT INTO @d VALUES (N'こはだ🍣');
INSERT INTO @d VALUES (N'墨🍺ラベル');
INSERT INTO @d VALUES (N'あじ🍣');
INSERT INTO @d VALUES (N'最速🍺絞り');
SELECT
    SUM(t.is_beer) as beer,
    SUM(t.is_sushi) as sushi
FROM (
    SELECT
        (CASE WHEN name_utf16 LIKE N'%🍺%' THEN 1 ELSE 0 END) as is_beer,
        (CASE WHEN name_utf16 LIKE N'%🍣%' THEN 1 ELSE 0 END) as is_sushi
    FROM @d
) as t

特定の絵文字を含むデータ数を集計するようなクエリでは誤った集計結果になります。

一応、データベースの照合順序が Japanese_CI_AS のままでも、式の評価時に _140 系等の補助文字をサポートする照合順序を指定すれば、一時的に回避できます。

    SELECT
        (CASE WHEN name_utf16 COLLATE Japanese_XJIS_140_CI_AS LIKE N'%🍺%' THEN 1 ELSE 0 END) as is_beer,
        (CASE WHEN name_utf16 COLLATE Japanese_XJIS_140_CI_AS LIKE N'%🍣%' THEN 1 ELSE 0 END) as is_sushi
    FROM @d

しかし、この方法は性能面では期待できない上、根本的な解決にはなってません。

そもそも、照合順序を Japanese_XJIS_140_CI_AS に設定して作成したデータベースの方は、問題なく意図した結果が得られています。

結論

SQLServerサロゲートペアや結合文字列を扱う場合、データベース作成時に適切な照合順序を指定しましょう。

....

前述した公式サイトには、注意書きとして「SQL Server 2014 (12.x) 以降では、すべての新しい _140 照合順序で補助文字が自動的にサポートされます。」とあります。

ちなみに日本語の照合順序で指定可能な _140 系の一覧は次のクエリで取得できます。

SELECT * FROM fn_helpcollations() WHERE name LIKE 'Ja%' AND name LIKE '%_140%';

不要なトラブルを避ける意味でも、照合順序は指定するべきかと。

Knex.js ver 0.95.0以降でSQLServerのdatabase libraryがtediousに変わってた

Knex.js を利用して SQLServer に接続するコードを書いて実行したところ、

Error: No event 'socketConnect' in state 'SentPrelogin'

のようなエラーが発生して接続できませんでした。
他の DB には接続できるのに SQLServer だけ接続できないのは釈然としません。

非常に困ってたのですが、同じ問題に直面している記事を見つけました。

要するに、node-mssql だといろいろ問題があったので、Knex.js ver 0.95.0以降では直接 tedious を使うようになったそうです。

そのため、今後はnpm install mssqlではなく、npm install tediousにする必要があります。

当該ブロジェクトの package.json を確認したら、"knex": "^0.95.4",だったので、

$ npm uninstall mssql
$ npm install tedious

のように処理したところ、問題なく接続できました。
Linux (KDE neon 5.21.3)と Mac (Intel macOS Mojave10.14.6)で確認済み

おまけ

当初、SQL Builder として Squel.js を利用しようとしたら、Knex.js を奨められたので、そのまま成り行きで利用してます。

ついでに、以前に書いた node-mssql を利用したコードを同じ条件で Knex.js に書き換えてみました。

ソースコード: crud.js

const knex = require('knex')({ 
    client: 'mssql',    // ここは 'mssql' のまま
    connection: {
        server: 'localhost',
        user: 'sa',
        password: 'abcd1234$',
        database: 'my_test_db',
        options: {
            enableArithAbort: true,
        }
    }
});

(async () => {
    console.log("#### Start ####");
    try {
        console.log("-- DROP & CREATE TABLE");
        await knex.schema.dropTableIfExists("会員名簿");
        await knex.schema.createTable("会員名簿", table => {
            table.integer("番号");
            table.string("氏名", 40);
            table.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 knex("会員名簿").insert({ 
                "番号": id, "氏名": name, "誕生日": birthday,
            });
        }
        display(await knex.select().table("会員名簿"));

        console.log("-- UPDATE");
        const update_id = 307;
        const new_name = "中井 雄樹";
        await knex("会員名簿").where("番号", "=", update_id)
                .update({ "氏名": new_name });
        display(
            await knex.select("番号","氏名").from("会員名簿")
                .orderBy("番号")
        );

        console.log("-- DELETE");
        const delete_id = 210;
        await knex("会員名簿").where("番号", "=", delete_id).del();
        display(
            await knex.select("番号","氏名","誕生日").from("会員名簿")
                .orderBy("誕生日", "desc")
        );
    } catch (e) {
        console.log("#### Catch !! ####");
        console.log(e);
    } finally {
        knex.destroy();
    }
    console.log("#### Finish ####");
})()

function display(rs) {
    let row_count = 0;
    if (rs.length > 0) {
        // カラム名
        const columns = Object.keys(rs[0]);
        const header = [];
        for (const col_name of columns) {
            header.push(` | ${col_name}`);
        }
        header.push(" |");
        console.log(header.join(""));
        // ロー
        for (const row of rs) {
            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.length})`);
}

ソースコードの生SQL文字列を QueryBuilder / SchemaBuilder で書き換えてます。
個人的には、生SQL文字列と比較しても、あまり違和感はないかと。

検証用はともかく、新規なのに大量の埋め込みSQL文は見たくないですね。

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

node-odbc モジュールから SQLServer への接続は FreeTDS の ODBC ドライバとの組み合わせで確認できました。

しかし、同じソースコードを MicrosoftODBC ドライバで実行すると、文字化けしてしまい、接続に失敗します。

プログラミング ガイドラインに書いてあるようにsetlocale(LC_ALL, "")相当で解決するのは承知しているのですが、これを Node.js でどうすれば良いのか解りません。

MDNのIntl.localeあたりを読んで試してみたけど思う結果になりません。

仕方なく、setlocale(LC_ALL, "")相当を処理する Node.js のアドオンを作成したところ、無事に接続できるようになりました。

正規な設定方法は不明ですが、条件は整ったので node-mssql モジュールの場合と同様なコードを試してみます。

1. 環境

2. プロジェクト

(1) 準備

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

$ cd ~/work
$ mkdir crud_msodbc
$ cd crud_msodbc

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

$ 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 の値は、試す環境に適した接続文字列を指定してください。

ソースコード: crud.js

const addon = require('./addon/build/Release/addon').setlocale();

const odbc = require("odbc");
const conn_str = 'Driver={ODBC Driver 17 for SQL Server};Server=localhost;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 row of [
            [ 110, "岸本 龍也", "1989-11-06" ],
            [ 210, "荒井 伸次郎", "1974-01-30" ],
            [ 105, "江口 美奈", "1979-06-23" ],
            [ 304, "長田 隆次", "1991-05-25" ],
            [ 307, "中居 雄樹", "1984-02-29" ],
        ]) {
            const stmt_ins = await conn.createStatement();
            await stmt_ins.prepare("INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)");
            await stmt_ins.bind(row);
            const inserted = await stmt_ins.execute();
            console.dir([inserted.statement, inserted.parameters]);
        }
        display(await conn.query("SELECT * FROM 会員名簿"));

        console.log("-- UPDATE");
        const update_id = 307;
        const new_name = "中井 雄樹";
        const stmt_upd = await conn.createStatement();
        await stmt_upd.prepare("UPDATE 会員名簿 SET 氏名=? WHERE 番号=?");
        await stmt_upd.bind([new_name, update_id]);
        const updated = await stmt_upd.execute();
        console.dir([updated.statement, updated.parameters]);
        display(await conn.query("SELECT 番号,氏名 FROM 会員名簿 ORDER BY 番号"));

        console.log("-- DELETE");
        const delete_id = 210;
        const stmt_del = await conn.createStatement();
        await stmt_del.prepare("DELETE FROM 会員名簿 WHERE 番号=?");
        await stmt_del.bind([delete_id]);
        const deleted = await stmt_del.execute();
        console.dir([deleted.statement, deleted.parameters]);
        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})`);
}
(4) Node.js addon 作成

続いて setlocale() に必要な C++ addon を作成します。
現在のディレクトリ配下に作業用ディレクトリ addon を作成します。

$ mkdir addon
$ cd addon

下記のソースコードをコピーしてそれぞれファイルを作成します。

・ファイル: binding.gyp

{
  "targets": [
    {
      "target_name": "addon",
      "sources": [ "setlocale.cc" ]
    }
  ]
}

・ファイル: setlocale.cc

#include <node.h>
#include <locale>

void Method(const v8::FunctionCallbackInfo<v8::Value>& args) {
    std::locale::global(std::locale(""));
}

void Initialize(v8::Local<v8::Object> exports) {
    NODE_SET_METHOD(exports, "setlocale", Method);
}

NODE_MODULE(NODE_GYP_MODULE_NAME, Initialize)

それぞれのファイルを作成したら、確認後npx node-gyp configure buildを実行して addon を作成します。

$ ls
binding.gyp  setlocale.cc
$ npx node-gyp configure build
npx: 97個のパッケージを4.475秒でインストールしました。
gyp info it worked if it ends with ok
gyp info using node-gyp@7.1.2
gyp info using node@14.16.0 | linux | x64
gyp info find Python using Python version 3.8.5 found at "/usr/bin/python3"
gyp info spawn /usr/bin/python3

・・・(ざっくり省略)・・・

gyp info ok 
$ ls
binding.gyp  build  setlocale.cc
$ ls build/Release/
addon.node  obj.target
$ cd ..
$ ls
addon  crud.js  node_modules  package-lock.json  package.json

エラーがなければ、./addon/build/Release/ にaddon.nodeファイルが生成されます。

準備は以上です。

3. 実行結果

$ node crud.js
#### Start ####
-- DROP & CREATE TABLE
-- INSERT
[
  'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)',
  [ 110, '岸本 龍也', '1989-11-06' ]
]
[
  'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)',
  [ 210, '荒井 伸次郎', '1974-01-30' ]
]
[
  'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)',
  [ 105, '江口 美奈', '1979-06-23' ]
]
[
  'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)',
  [ 304, '長田 隆次', '1991-05-25' ]
]
[
  'INSERT INTO 会員名簿 (番号,氏名,誕生日) VALUES (?,?,?)',
  [ 307, '中居 雄樹', '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 氏名=? 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. 補足

MicrosoftODBC ドライバでは、無事にプレースホルダが使えました。
日本語も問題なく通ります。

ちなみに setlocale() の処理を消すと ODBC ドライバのエラーが発生します。

#### Catch !! ####
[Error: [odbc] Error executing the sql statement] {
  odbcErrors: [
    {
      state: '42000',
      code: 102,
      message: "[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'�' ��k\ri\x07jˇLB�~Y\x02"
    }
  ]
}

今回はアドオンの追加で回避しましたが、正規の方法があるなら知りたいです。

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 モジュールの場合と同様なコードで試してみます。

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)

MicrosoftODBC ドライバだと文字化けが発生するため、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 ドライバの組み合わせではプレースホルダが使えます。※文字化けを回避できれば、の話ですが...

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

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

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

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

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

1. 環境

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 のサイトにあります。

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

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

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

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

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

特に問題が見つからなければ、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 install opensslを実行して対処しました。