2016/05/24

Node.jsでsqlite3を使ってデータベースを操作する方法まとめ

bot開発のため情報を蓄積するデータベースがほしかった。
ただ、以前ChromeExtention開発のときにNoSQL Databaseを使って死にそうになったのでSQLで取得できるデータベースが使いたい!
でもMySQLとかPostgreSQLとかSQL ServerとかOracleとかを入れるのは面倒くさい。

ということで、手軽につかえるであろうsqlite3を選択した。


ちなみに今回の環境は以下のとおり。
  • Mac OSX Yosemite
  • node v5.3.0
  • sqlite3 v3.1.3


sqlite3をインストールする


npmをつかってsqlite3のライブラリをインストールする。
$ npm init
$ npm i -S sqlite3

インストールは「$ npm install --save sqlite3」でも大丈夫。


下準備: モジュールを作成する


var sqlite3 = require('sqlite3');

module.exports.init = function (file) {
  return new sqlite3.Database(file);
};

GitHubでコード検索したら、こういう実装が多かった。
どうやら細かくモジュールに分割して使うのがベストプラクティスらしい。

ただ、見つけたコードはファイル名が固定になっていたので、module.exports.init でファイル名を指定できるようにした。


データベースの初期化


今回はdbフォルダの下に「demo.sqlite3」というファイル(データベース)をつくる。
拡張子は何でもよかったりするが、「*.sqlite3」や「*.db」、「*.database」あたりの名前をよく見かける。
var sqlite = require('./module/db.js'),
    db = sqlite.init('./db/demo.sqlite3');

// init
db.serialize(function () {
  var create = new Promise(function (resolve, reject) {
    db.get('select count(*) from sqlite_master where type="table" and name=$name',{ $name: 'demo_table' }, function (err, res) {
      var exists = false;
      if (0 < res['count(*)']) { exists = true; }

      resolve(exists);
    });
  });

  create.then(function (exists) {
    if (!exists) {
      db.run('create table demo_table (id integer primary key, name text)');
    }
  });
});

sqlite3の処理は非同期なため、Promiseを使って順次処理できるようにしている。
db.get(******)」の部分は、データベースに対象のテーブルが存在しているかどうかをチェックしており、count(*)が0ならCREATE TABLEで新しくテーブルをつくる。


データの挿入


var insert = function (param){
  db.serialize(function () {

    db.run('insert or ignore into demo_table (id, name) values ($i, $t)', 
      {
        $i: param.id,
        $n: param.name
      }
    );

    // ↓でもOK
    // db.run('insert or ignore into demo_table (id, name) values (?, ?)', [param.id, param.name]);
  });
};

テーブルのカラムが多い場合は、「$arg」のように指定したほうがわかりやすいだろう。カラムが少なければ「?」でバインドしても大丈夫。

文字列結合でSQLに変数を埋め込むのは、見た目が汚くなったり、脆弱性が混入したりするので避けたほうがよいだろう。
(例: 'insert into demo_table (id, name) values (' + id + ',' + name + ')'

insert分に「or ignore」という見慣れないものがある。
これはPrimary keyが重複して登録できないなど何らかのエラーが発生しても、無視するというオプションだ。



他に使えるオプションは以下のとおり
abort
制約違反が起こったら変更操作を取り消す。ロールバックと違いエラーになるまでの実行結果は保持される(デフォルト)
rollback
制約違反が起こったらロールバックする
fail
制約違反が起こったら違反前までの操作は保持されエラーを返す
ignore
制約違反が起こったコマンドのみ無視して、移行の処理を続行する
replace
unique制約違反の場合は、対象行が削除される。
Not null制約違反の場合は、その値をデフォルト値で置き換えられる。もしくはabortと同じ


データの取得(1件ずつ取得して処理)


var selectEach = function (condition, callback) {
  db.serialize(function () {
    db.each('select id, name from demo_table where id > ?', 
           [condition.id], 
           function (err, res) {
             if (err) throw err;
             callback(err);
    });
  });
};

こんな感じでeachで1件ずつ取得して処理される。
ただし非同期処理なので処理される順番は保証されないので注意。


データの取得(全件取得して処理)


var selectAll = function (condition, callback) {
  db.serialize(function () {
    db.all('select id, name from demo_table where id > ?', 
           [condition.id], 
           function (err, rows) {
             if (err) throw err;

             rows.forEach(function (row) {
               callback(row);
             });
    });
  });
};

こちらは一気に取得してから処理されるので順番は保証される。



いろいろ説明したが、すべて非同期処理なのでcallbackがジャマだ…。


同期処理でデータを取得


callback地獄になるのがいイヤな場合は、Promiseを使う・

var selectValue = function (condition) {
  return new Promise(function (resolve, reject) {
    db.serialize(function () {
      db.get('select word, first from demo_table where id = $id',
        { $id: condition.id },
        function (err, res) {
          if (err) return reject(err);
          
          resolve(res);
      });
    });
  });
}; 


// 検索してなんかする処理
var condition = {
  id  : '123'
};

selectValue(condition).then(function (result) {
  console.log('Success:', result.name);
}).catch(function (err) {
  console.log('Failure:', err);
});


↓のbotで使っているので参考にしてみてください。
GitHub:BcRikko/duck-bot



さいごに


いろいろ説明してきたが、もっと詳しい内容が知りたければ、以下のドキュメントを参照ください。




以上

written by @bc_rikko

0 件のコメント :

コメントを投稿