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」でも大丈夫。

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

// ./module/db.js
var sqlite3 = require('sqlite3');

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

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

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

データベースの初期化

今回はdbフォルダの下に「demo.sqlite3」というファイル（データベース）をつくる。
拡張子は何でもよかったりするが、「*.sqlite3」や「*.db」、「*.database」あたりの名前をよく見かける。

// app.js
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で新しくテーブルをつくる。

データの挿入

// app.js
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件ずつ取得して処理）

// app.js
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件ずつ取得して処理される。
ただし非同期処理なので処理される順番は保証されないので注意。

データの取得（全件取得して処理）

// app.js
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を使う・

// app.js
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

さいごに

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

• API · mapbox/node-sqlite3 Wiki
• sqlite3

以上

written by @bc_rikko