Node.jsからOracle DBへのアクセス

#はじめに
Node.jsからOracle DBにアクセスするプログラムを書く機会があり、まずは手元のノートPC上の Oracle DBにアクセスする単純なコードで試してみました。それを備忘録的に書いておきます。

#使った環境
## S/W
今回利用した環境は以下の通り。
Windows上でDBとNodeの両方を動かしました。

- OS: Windows 10
- Oracle DB SE2 12.2.0.1.0（Pluggable DBを利用）
    - キャラクタセットは `AL32UTF8`（`VARCHAR2`にJSONデータを保存するため）
- Node 6.11.3
- NPMパッケージ
    - oracledb
    - restify

##Oracle DB上のテーブル
C##testという、コンテナ共通のDBユーザーを作成し、orclpdbというプラガブル・データベース上に、IDとJSONデータ保存用の２つのカラムだけを持つ単純なテーブル（JSON_TAB）で試してみました。JSONデータは、今回は文字列を扱う`VARCHAR2`に格納しました。このカラムには、データがJSONフォーマットかどうかをチェックする制約をつけました。

以下がテーブルを作成するまでのSQLスクリプトです。

```sql:create_tab.sql

CONNECT / AS SYSDBA

CREATE USER c##test IDENTIFIED BY c##test DEFAULT TABLESPACE USERS CONTAINER=ALL;
GRANT connect, dba TO c##test CONTAINER=ALL;
ALTER USER c##test QUOTA UNLIMITED ON USERS;

CONNECT c##test/c##test
ALTER SESSION SET CONTAINER = orclpdb;

CREATE TABLE json_tab
(id VARCHAR2(5),
json_data VARCHAR2(4000)
    CONSTRAINT json_check CHECK (json_data IS JSON));
```
##oracledbパッケージのインストール方法
Windowsの場合、手順が特殊ですので、以下を参照してください。
[Windows環境でのNode.jsのoracledbのインストール](https://qiita.com/kaohas/items/e4fe9e741d62e3169224)





#使ったプログラム
Restifyを使って単純なREST APIを作成しました。
JSファイルの構成は、server.jsとoracle.jsの二つです。server.jsではREST APIとしてのインタフェースだけを記述しています。実際の処理は oracle.jsに書きました。

```Node.js:server.js
let restify = require('restify');
let oracle = require('./oracle.js');

let server = restify.createServer();

server.use(restify.plugins.bodyParser()); 

server.get("/db/:id", oracle.select) // GETに対してテーブルにSELECT
server.post("/db", oracle.insert)    // POSTに対してテーブルにINSERT
server.put("/db/:id", oracle.update) // PUTに対してテーブルにUPDATE
server.del("/db/:id", oracle.delete) // DELETEに対してテーブルにDELETE

server.listen(process.env.PORT || 3000, function() {
      console.log("Node is running...");
});

```

oracle.jsでoracledbパッケージを使った Oracle DBのアクセスのコードを書いています。

まず最初に、接続文字列などのプロパティ情報です。`DBAAS_USER_NAME`、`DBAAS_USER_PASSWORD`といった環境変数を指定しています。これは、Oracle Application Container Cloudで Node.jsを実行する際に、Database Cloud Serviceのサービスとバインドする際に自動設定される環境変数です。あとでクラウドで実行することも視野に入っている場合は、コード内に書いておくと便利です。

oracledbパッケージでは、コネクション・プーリングからコネクションを取ってきます。
ステートメント・キャッシュは、ここでは４つのSQLを実行しているので、4と設定しました。

```Node.js:oracle.js
let oracledb = require('oracledb');
// oracledb.autoCommit = true; // autoCommit = trueにすると、connection.commit()を使わずにデータが commitされます。

let connectionProperties = {
	user: process.env.DBAAS_USER_NAME || "c##test",
	password: process.env.DBAAS_USER_PASSWORD || "c##test",
	connectString: process.env.DBAAS_DEFAULT_CONNECT_DESCRIPTOR || "localhost/orclpdb.jp.oracle.com",
	stmtCacheSize: process.env.DBAAS_STATEMENT_CACHE_SIZE || 4,
	poolMin: 1,
	poolMax: 5
};
```
次に、doRelease(connection)というローカル関数を記述しています。これは oracledbのドキュメントに書かれていたものを、そのまま真似しました。非同期処理をきちんとハンドリングしないようなコードを書いてしまうと、DBへの処理中にコネクションをリリースしようとすることも起こりがちです。その場合にエラー出力をしてくれます。

```
function doRelease(connection) {
	connection.release(function (err) {
		if (err) {
			console.error(err.message);
		}
	});
}
```

次に実際のSQLの実行に入ります。最初は`SELECT`の例です。
それぞれの処理は Promiseで返してくれるので、以下を順番に行っています。
(1)コネクションを取得
(2)JSON_TABテーブルに対する`SELECT`文を、バインド変数をセットして実行
(3)結果を取得。

- `VARCHAR2`に入っているJSONフォーマットを取り出しているので、`JSON.parse()`しているエクション
- 取り出し件数に応じてエラーを返したりしている（このあたりはアプリ要件次第ですが）
- 終わったらコネクションをリリース

```
exports.select = function(req, res, next){
	
	let connection;
	let sqltext = `SELECT json_data FROM json_tab WHERE id = :id`;
		
	oracledb.getConnection(connectionProperties)
	.then((conn) => {
		connection = conn;
		return conn.execute(sqltext, [req.params.id], 
				{ outFormat: oracledb.OBJECT });	
	})	
	.then((result) => {
		if (result.rows.length === 1) {
			res.send(JSON.parse(result.rows[0].JSON_DATA));	// POINT: 取り出したデータは Stringなので、それをオブジェクトとして扱う場合には JSON.parseすること
		} else if(result.rows.length === 0) {
			res.send(500, {"error": "no data found"});
		} else {
			res.send(500, {"error": "too many rows"});
		}
		doRelease(connection);
		next();
	})
	.catch((err) => {
		console.error(err.message);
		
		if(connection != null){
			console.error(sqltext);
			doRelease(connection);
		}
		res.send(500, {"error": err.message});
		next();
	});
}
```

次は`INSERT`の例です。
`SELECT`と違って、SQLの実行後に `connection.commit()` しています。
データを格納する際は、テーブルのカラムの`VARCHAR2`に合わせて、オブジェクトを `JSON.stringify()`しています。

```
exports.insert = function(req, res, next){

	let body = req.body;
	let connection;	
	let sqltext = "INSERT INTO json_tab(id, json_data) VALUES (:id, :json)";
	
	oracledb.getConnection(connectionProperties)
	.then((conn) => {
		connection = conn;
		return conn.execute(sqltext, [body.id, JSON.stringify(body)]);	// JSONデータ格納時は、JSON.stringifyで文字列に変換する。
	})
	.then((result) => {
		return connection.commit();
	})	
	.then(()=> {
		doRelease(connection);
		res.send(200);
		next();
	})
	.catch((err) => {
		console.error(err.message);
		
		if(connection != null){
			console.error(sqltext);
			doRelease(connection);
		}
		res.send(500, {"error": err.message});
		next();
	});
}
```

次は`UPDATE`（というより UPSERT）の例として、`PL/SQL`プログラムを実行しています。
バインド変数が `UPDATE`文と `INSERT`文で重複するため、他のSQLではバインド変数は順番指定で渡していますが、ここではバインド変数を変数指定で渡しています。

```
exports.update = function(req, res, next){

	let body = req.body;
	let connection;		
	
	// この例は PUTメソッドで UPSERTしているので UPDATEとINSERT両方を含んでいる。
	let sqltext = `BEGIN\n` 
			    +    `UPDATE json_tab\n`
				+       `SET json_data = :json\n`
				+       `WHERE id = :id;\n`
				+    `IF (SQL%NOTFOUND) THEN\n`
				+       `INSERT INTO json_tab(id, json_data) VALUES (:id, :json);\n`
				+    `END IF;\n`
				+ `END;`;
				
	oracledb.getConnection(connectionProperties)
	.then((conn) => {
		connection = conn;
		return conn.execute(sqltext, {
				json: JSON.stringify(body),
				id: req.params.id
			});
	})
	.then((result) => {
		return connection.commit();
	})	
	.then(()=> {
		doRelease(connection);
		res.send(200);
		next();
	})
	.catch((err) => {
		console.error(err.message);
		
		if(connection != null){
			console.error(sqltext);
			doRelease(connection);
		}
		res.send(500, {"error": err.message});
		next();
	});
}
```

最後、`DELETE`の例です。他のSQLと構造は変わらないので、説明は割愛します。

```
exports.delete = function(req, res, next){

	let connection;
	let sqltext = `DELETE json_tab WHERE id = :id`;

	oracledb.getConnection(connectionProperties)
	.then((conn) => {
		connection = conn;
		return conn.execute(sqltext, [req.params.id]);
	})
	.then((result) => {
		return connection.commit();
	})	
	.then(()=> {
		doRelease(connection);
		res.send(200);
		next();
	})
	.catch((err) => {
		console.error(err.message);
		
		if(connection != null){
			console.error(sqltext);
			doRelease(connection);
		}
		res.send(500, {"error": err.message});
		next();
	});
}

```