Node-REDでMySQLにweb apiを作成してみた

###概要
Node-REDがインストールされたMac/ラズパイにMySQLをインストールし、Node-REDからアクセスできるようにする。Node-RED上でWeb APIを追加し、Web API経由でMySQLのデータの登録、修正、一覧、削除ができるようにする。
( Node-RED version: v0.15.2, Node.js version: v6.9.1)

###MySQLのインストール
####Install(Mac)
MySQLをここからdownloadする。

インストール完了時にPop Up Windowでrootのパスワードが表示されるので、メモすること。
まずMySQLのrootのパスワードを変更する。rootでログイン。

> mysql -u root -p
mysql> update mysql.user set password=password(‘new-password’) where user = ‘root’;

「new-password」には新規のパスワードを入力する。

mysql> flush privileges;
mysql> exit;

これでパスワードが変更されたはず。もう一度MySQLを起動させる。

> mysql -u root -p

####Install(ラズパイ)
以下のコマンドを実行してMySQLを実行する

sudo apt-get install mysql-server

インストール中にrootのパスワードを入力する。
インストールが完了したらMySQLを起動する。

> mysql -u root -p

####Sample Data Base の作成
Database名は nodered, Table名は users, Fieldは idとname、中身はまだempty。

mysql> CREATE DATABASE nodered;
mysql> CREATE TABLE nodered.users(id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id));

####確認

mysql> show databases;
mysql> show tables from nodered;
mysql> show columns from nodered.users;
mysql> SELECT * FROM nodered.users;
mysql> quit

###Node-RED用のMySQLライブラリのインストール

> cd ~/.node-red
> npm install node-red-node-mysql

###Node-REDでFlowの作成
Node-REDの起動

> node-red

ブラウザーでNode-REDにアクセスする。通常はNode-REDが起動しているマシンのIPアドレスの後にポート番号「:1880」を追加すればいい。

HTTP Inのnodeが、GETのみhttpのparameterをmsg.payloadに渡される。POST, PUT, DELETEはparameterがpayloadに渡されていない。そこでGETだけを使って実装する。全体のFlowはこのとおり。

FLOW

[{"id":"4d711695.014988","type":"mysql","z":"9557410.c02df4","mydb":"e419b3e1.91fd98","name":"","x":665.5,"y":218,"wires":[["9bef1fa3.6885e"]]},{"id":"353a3347.8414f4","type":"function","z":"9557410.c02df4","name":"Generate SQL","func":"name = msg.payload.name;\nmsg.topic = \"insert into users(name) values('\" + name + \"');\";\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":411,"y":179,"wires":[["4d711695.014988","da82a62f.baabe8"]]},{"id":"da82a62f.baabe8","type":"debug","z":"9557410.c02df4","name":"","active":true,"console":"false","complete":"topic","x":684,"y":342,"wires":[]},{"id":"9bef1fa3.6885e","type":"http response","z":"9557410.c02df4","name":"","x":870,"y":218,"wires":[]},{"id":"6e4e8d39.4a5d2c","type":"debug","z":"9557410.c02df4","name":"","active":true,"console":"false","complete":"payload","x":404.5,"y":118,"wires":[]},{"id":"b03d33e7.e3a2a","type":"http in","z":"9557410.c02df4","name":"","url":"/users/create","method":"get","swaggerDoc":"","x":135,"y":174,"wires":[["353a3347.8414f4","6e4e8d39.4a5d2c"]]},{"id":"17cf6ea7.1d5511","type":"http in","z":"9557410.c02df4","name":"","url":"/users/list","method":"get","swaggerDoc":"","x":131,"y":255,"wires":[["5b1d6ae0.38dc4c","6e4e8d39.4a5d2c"]]},{"id":"b35bad96.1e1ad","type":"http in","z":"9557410.c02df4","name":"","url":"/users/update","method":"get","swaggerDoc":"","x":139,"y":319,"wires":[["7466d948.417ec","6e4e8d39.4a5d2c"]]},{"id":"19c41370.d3647d","type":"http in","z":"9557410.c02df4","name":"","url":"/users/delete","method":"get","swaggerDoc":"","x":135,"y":389,"wires":[["58f26120.3dc0f8","6e4e8d39.4a5d2c"]]},{"id":"5b1d6ae0.38dc4c","type":"function","z":"9557410.c02df4","name":"Generate SQL","func":"msg.topic = \"SELECT * FROM users;\"\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":416,"y":243,"wires":[["4d711695.014988","da82a62f.baabe8"]]},{"id":"7466d948.417ec","type":"function","z":"9557410.c02df4","name":"Generate SQL","func":"id = msg.payload.userid;\nname = msg.payload.name;\nmsg.topic = \"UPDATE users SET name='\" + name + \"' WHERE id=\" + id + \";\";\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":418,"y":305,"wires":[["4d711695.014988","da82a62f.baabe8"]]},{"id":"58f26120.3dc0f8","type":"function","z":"9557410.c02df4","name":"Generate SQL","func":"id = msg.payload.userid;\nmsg.topic = \"DELETE FROM users WHERE id=\" + id + \";\";\nconsole.log(msg.topic);\nreturn msg;","outputs":1,"noerr":0,"x":419,"y":379,"wires":[["4d711695.014988","da82a62f.baabe8"]]},{"id":"9411b9bd.740b4","type":"comment","z":"9557410.c02df4","name":"create?name=hoge","info":"","x":137.5,"y":128,"wires":[]},{"id":"39569335.7e529c","type":"comment","z":"9557410.c02df4","name":"list","info":"","x":99,"y":217,"wires":[]},{"id":"446a8829.270688","type":"comment","z":"9557410.c02df4","name":"update?userid=63&name=hoge","info":"","x":177.5,"y":286,"wires":[]},{"id":"67897e4e.b78a5","type":"comment","z":"9557410.c02df4","name":"delete?userid=30","info":"","x":124.5,"y":356,"wires":[]},{"id":"3dfad8ce.195848","type":"comment","z":"9557410.c02df4","name":"MySQL DB","info":"```MySQL\nmysql> CREATE DATABASE nodered;\nmysql> CREATE TABLE nodered.users(id INT AUTO_INCREMENT, name TEXT, PRIMARY KEY (id));\n```","x":661.5,"y":177,"wires":[]},{"id":"2f36c5b1.921162","type":"comment","z":"9557410.c02df4","name":"Tutorial of MySQL: 2016.12.02","info":"","x":159.5,"y":74,"wires":[]},{"id":"e419b3e1.91fd98","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"nodered","tz":""}]

mysql nodeの設定はこの通り。

各functionはHTTP In nodeから受け取ったparameterを使ってSQL文を作成する。JavaScriptは以下の通り。

Create

name = msg.payload.name;
msg.topic = "insert into users(name) values('" + name + "');";
console.log(msg.topic);
return msg;

List

msg.topic = "SELECT * FROM users;"
console.log(msg.topic);
return msg;

Update

id = msg.payload.userid;
name = msg.payload.name;
msg.topic = "UPDATE users SET name='" + name + "' WHERE id=" + id + ";";
console.log(msg.topic);
return msg;

Delete

id = msg.payload.userid;
msg.topic = "DELETE FROM users WHERE id=" + id + ";";
console.log(msg.topic);
return msg;

###動作確認
動作確認はChrome ApplicationのPOSTMANを使う。
まずはいくつかレコードを作成する。以下の内容をGETで送信する。nameの右側の「MySQL1」は順次変更する

http://localhost:1880/users/create?name=MySQL1

レコードの確認はこちらをGETで送信。

http://localhost:1880/users/list

レコードの修正はuseridとnameをparameterで指定してGETで送信

http://localhost:1880/users/update?userid=63&name=hoge

レコードの削除はuseridを指定してGETで送信

http://localhost:1880/users/delete?userid=65