node-mssqlでバルクインサートするときに外部キーを有効にするメモ

概要

SQL Serverのbulk insertの仕様として、そのまま使うと外部キーなど制約を無視してしまう。

一括インポート操作中、対象テーブルまたはビューに対するすべての制約を検証します。 CHECK_CONSTRAINTS オプションを指定しない場合、CHECK 制約および FOREIGN KEY 制約は無視され、操作の後でテーブルの制約は信頼されていないものとしてマークされます。

UNIQUE および PRIMARY KEY 制約は常に適用されます。 NOT NULL 制約で定義された文字型列にインポートする場合、テキスト ファイルに値がなければ BULK INSERT は空白文字列を挿入します。

node-mssqlのbulkページにはCHECK_CONSTRAINTSのことは何も書かれていなかった。

options - Options object to be passed through to driver (currently tedious only). Optional. If argument is a function it will be treated as the callback.

ChatGPTに聞いてみる

以下の回答が返ってきた。

node-mssql の bulk メソッドに渡せる options オブジェクトには、Tedious の BulkLoad オプションを指定できます。以下のようなオプションが利用可能です。

options オブジェクトで指定できる項目

(すべて tedious の BulkLoad オプションに基づく)

オプション	型	説明
keepNulls	boolean	true の場合、NULL の値を明示的に挿入し、デフォルト値を適用しない
checkConstraints	boolean	true の場合、制約 (CHECK, FOREIGN KEY など) を適用する
fireTriggers	boolean	true の場合、テーブルの INSERT トリガーを実行する
tableLock	boolean	true の場合、TABLOCK を使用してバルク挿入をロックする
keepIdentity	boolean	true の場合、IDENTITY カラムの値をそのまま挿入する
allowUnknownColumns	boolean	true の場合、スキーマに存在しないカラムが含まれていても許可する
orderHint	Object	インデックスの最適化のため、特定のカラム順を指定できる

使用例

const sql = require('mssql');

const pool = await sql.connect(config);
const table = new sql.Table('Users');

table.create = true;  // テーブルを作成（存在しない場合のみ）
table.columns.add('id', sql.Int, { nullable: false });
table.columns.add('name', sql.NVarChar(50), { nullable: false });
table.rows.add(1, 'Alice');
table.rows.add(2, 'Bob');

await pool.request().bulk(table, { 
  checkConstraints: true,
  keepNulls: true, 
  fireTriggers: true, 
  tableLock: true 
});

この例では、bulk の options に keepNulls, fireTriggers, tableLock を指定して、NULL 値の保持、トリガーの実行、テーブルロックの有効化を行っています。

Tedious の BulkLoad を直接ラップしているため、tedious の公式ドキュメントも参考にするとより詳細な情報が得られます。

確認

tedious - bulk-loadにオプションの説明が書かれている。

@types/node-mssqlを確認すると下記のオプションが設定されているようだった。

types/mssql/index.d.ts

/**
 * Options object to be passed through to driver (currently tedious only)
 */
export interface IBulkOptions {
    /** Honors constraints during bulk load, using T-SQL CHECK_CONSTRAINTS. (default: false) */
    checkConstraints?: boolean | undefined;
    /** Honors insert triggers during bulk load, using the T-SQL FIRE_TRIGGERS. (default: false) */
    fireTriggers?: boolean | undefined;
    /** Honors null value passed, ignores the default values set on table, using T-SQL KEEP_NULLS. (default: false) */
    keepNulls?: boolean | undefined;
    /** Places a bulk update(BU) lock on table while performing bulk load, using T-SQL TABLOCK. (default: false) */
    tableLock?: boolean | undefined;
}

オプション	型	説明
checkConstraints	boolean	true の場合、制約 (CHECK, FOREIGN KEY など) を適用する
fireTriggers	boolean	true の場合、テーブルの INSERT トリガーを実行する
tableLock	boolean	true の場合、TABLOCK を使用してバルク挿入をロックする
keepNulls	boolean	true の場合、NULL の値を明示的に挿入し、デフォルト値を適用しない

所感

checkConstraintsを有効化する点についてはChatGPTは正しく答えを返してくれた。
ただ、node-mssqlに存在しないオプションを提示するなど、まだまだ鵜呑みにはできない状態だった