#はじめに
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スクリプトです。
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のインストール
#使ったプログラム
Restifyを使って単純なREST APIを作成しました。
JSファイルの構成は、server.jsとoracle.jsの二つです。server.jsではREST APIとしてのインタフェースだけを記述しています。実際の処理は oracle.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と設定しました。
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();
});
}