Edited at

Node.js から X DevAPI を使って MySQL 8.0.17 のドキュメントデータベースと RDB テーブルにアクセスする

以前、Java で Connector/J を使って X DevAPI 経由で MySQL 8.0 にアクセスしました。

今回は、Node.js(12.8.0)+ Express(4.17.1)で簡単な Web アプリケーションを作成し、Connector/Node.js(8.0.17)経由で MySQL Server(8.0.17)に以下の 3 種類の方法でアクセスしてみます。


  • コレクションを使うアクセス(ドキュメントデータベースに対するアクセス):以降「コレクション方式」

  • テーブルを使うアクセス(RDB に対するアクセス):同「テーブル方式」

  • SQL を使うアクセス:同「SQL 方式」

ソースコードは GitHub 上で公開しています。

【注】GitHub リポジトリの README.md に記載のとおり、このコードは試験的なものです。認証・認可、入力値チェック、詳細なエラー処理などが実装されていません。

データ滅失・クラウド破産などを引き起こす恐れがあるため、インターネットに公開する形で実行しないでください。

※ついでに言っておくと、私自身は「普段はインフラのお守り、時々 Java コード書き」なので、Node.js のコードをまともに書くのは(AWS Lambda 以外では)初めてです…。


8/20 追記:

コードに誤りがありましたので修正しました。また、一部の参考文献が抜けていたため追記しました。


【公式ガイド・リファレンスマニュアル】



Web アプリケーションの内容

写真(5MB 以下)をアップロードすると Amazon Rekognition の画像認識でラベリングを行います。

Rekognition で付与されたラベルのうち、信頼度 60% 以上かつ上位 10 個までを MySQL のドキュメントデータベース(コレクション)に配列形式で記録します。

同時に、一般のテーブルにラベルを(1 行= 1 ラベルに)分解し、ラベルセレクタ用として記録します。

【画面イメージ】


コードの説明

フロントエンド(Vue.js 使用)はオマケですので、バックエンド(Node.js 使用)の、MySQL アクセス部分のみ適宜抜粋・説明します。


  • 111 行目~ 115 行目


Schema名・Collection名・Table名・接続パラメータ

// MySQL X DevAPI

const schemaName = 'xdevtest';
const collectionName = 'image_labeling';
const labelTableName = 'labels';
const connectParam = 'mysqlx://xdevuser:XDevAPIUser8.0@localhost:33060/' + schemaName;

collectionNameは写真に関するデータを保存するドキュメントデータベースのコレクション名(実際にはテーブルとして保存される)、labelTableNameはラベルセレクタ用のデータを保存するテーブル名です。

接続パラメータ中のxdevuser:XDevAPIUser8.0が接続ユーザ名とパスワードです(MySQL 上でxdevtestに対する CRUD + テーブル作成・削除権限を付与しておきます)。


  • 132 行目~ 140 行目


コレクションへの保存

            // Store Labels in MySQL Document Store

const session = await mysqlx.getSession(connectParam);
const collection = await session.getSchema(schemaName).getCollection(collectionName);
const labels = convertArray(found.Labels);
const dummy = await collection.add({
"filename": req.file.filename,
"originalname": req.file.originalname,
"labels": labels
}).execute();

Amazon Rekognition で読み取られたラベルを抽出して配列に変換し、コレクションimage_labelingにコレクション方式で保存しています。

なお、MySQL X DevAPI のサンプルでは Promise パターンで非同期処理していますが、このコードでは async / await パターンで記述しています(以降、同じ)。trycatchを使ったエラー処理はかなり大雑把に行っています(本番用のコードではこのような雑な書き方はしないでください…)。


  • 141 行目~ 147 行目


テーブルへの保存

            // Store Labels (for Selector) in MySQL Table

const table = await session.getSchema(schemaName).getTable(labelTableName);
await Promise.all(
labels.map(async label =>
await table.insert('label')
.values(label)
.execute()));

同じラベルを「1 ラベル= 1 行」に分解してテーブルlabelsにテーブル方式で保存しています。

ここでは、繰り返しのINSERTPromise.all()を使って並列で処理しています。


【注】当初公開したコードではうっかりawaitが抜けており、また.forEach()を使っていたためそのまま追記してもawaitできない形になっていました。



  • 159 行目~ 166 行目


テーブルからの読み取り

            const rows = [];

const session = await mysqlx.getSession(connectParam);
const table = await session.getSchema(schemaName).getTable(labelTableName);
const dummy = await table.select('`label`', 'COUNT(`label`) AS `count`')
.groupBy('`label`')
.orderBy('`count` DESC', '`label` ASC')
.limit(typeof req.body.numof !== 'undefined' ? req.body.numof : 100)
.execute(row => rows.push(convertResultSet(row)));

テーブルlabelsからテーブル方式で読み取っています。

ちなみに、COUNT()label列をカウントしているのは、COUNT(*)がエラーで通らなかったからです…。


  • 178 行目~ 184 行目


コレクションからテーブル方式で読み取り

            const rows = [];

const session = await mysqlx.getSession(connectParam);
const table = await session.getSchema(schemaName).getTable(collectionName);
const dummy = await table.select('`doc`')
.where(JSON.stringify(req.body.labels) + " in `doc`->'$.labels'")
.limit(typeof req.body.numof !== 'undefined' ? req.body.numof : 100)
.execute(row => rows.push(convertResultSetData(row)));

前述のとおり、コレクションの実体はテーブルですので、検索条件の関係でコレクション方式の.find()が使えない場合は、テーブル方式で.select().where()することもできます。

この場合、テーブル名=コレクション名ですが、列名は「doc」固定になるようです。

この例では、MySQL 8.0.17 で実装された Multi-Valued Index を使ってJSON_CONTAINS()相当=AND条件での検索を行っています(inの部分。OR条件で検索する場合はoverlaps)。ちなみにreq.body.labels.bind()で割り付けたかったのですが、上手く行きませんでした。

なお、先のコードを実行するとこのような SQL に変換されます。


スロークエリログの記録例

# administrator command: Init DB;

# Time: 2019-08-16T13:40:17.204903Z
# User@Host: xdevuser[xdevuser] @ localhost [127.0.0.1] Id: 80
# Query_time: 0.001711 Lock_time: 0.000351 Rows_sent: 4 Rows_examined: 6
SET timestamp=1565962817;
SELECT `doc` AS ```doc``` FROM `xdevtest`.`image_labeling` WHERE JSON_CONTAINS(JSON_EXTRACT(`doc`,'$.labels'),JSON_ARRAY('Vehicle','Wheel')) LIMIT 100;


  • 215 行目~ 225 行目


コレクションとテーブルの作成

            // Create Collection

const session = await mysqlx.getSession(connectParam);
const collection = await session.getSchema(schemaName).createCollection(collectionName);
const flag = await collection.createIndex('labels',
{fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});
// Create MySQL Table
const query = 'CREATE TABLE `' + schemaName + '`.`' + labelTableName +
'` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `label` VARCHAR(100) NOT NULL, INDEX `label` (`label`)' +
') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci';
const dummy = await session.sql(query)
.execute();

前述のコレクションimage_labelingとテーブルlabelsを作成しています。

コレクションimage_labelingは Multi-Valued Index とあわせてコレクション方式で作成しています。

テーブルlabelsは SQL 方式で作成しています。


気になる点


  • コレクション方式で.find()で指定可能なパターンが限定されている

  • テーブル方式でも.select()(射影)や.where()(選択)で指定可能なパターンが限定されている

リテラルとして*,などの記号が使える場所が限られているようなので、ちょっとしたことでも SQL 方式に頼ることになりそうです。なんだかモヤモヤします。



  • .bind()で値を割り付けることができる対象が限定されている

名前やプレースホルダで値を動的に割り付けられる対象が限られるのも地味に困ります(うっかり SQL インジェクションの脆弱性を生みかねない…)。


  • 一旦MySQL Server has gone away.になると、MySQL プロトコルでは再接続できるのに X プロトコルでは再接続できなくなる

私が実験した環境(64 ビット Windows 10 Home バージョン 1903)だけかもしれませんが、長時間 MySQL Server への接続がなくMySQL Server has gone away.が出た後、MySQL Workbench からの再接続はできるのに Node.js から X DevAPI を使った接続ができなくなる現象が発生しました。

仕方なく Node.js 上のコードを再起動すると再接続されましたが、今度は(デフォルト設定の)8 時間無通信でなくてもすぐMySQL Server has gone away.が出るようになり、結局 MySQL Server も再起動することになりました。

このあたりの挙動の原因はわかっていません。

個人的には、本番環境での利用はまだ早いかな、と思います…。


【参考にしたもの】