Node.jsでsqlite3データベース処理を行う

node-sqlite3ライブラリを使ったsqlite3のサンプルプログラム。

導入準備

他のブログをみると、pythonやvisual studioがインストールされてないと導入に失敗するという記述があったが、改善されているのかnpm installであっさり導入が終了する。(ローカルでソースのビルド処理は行われている模様)

> npm install sqlite3

サンプルプログラム

とりあえずnode.exeで単発実行できるサンプルプログラムを下記に示す。

ポイントは、

  • serialize()関数内に処理を記述することにより、SQLが同期的に順次実行される。
  • prepare()finalize()を使って、動的SQLを実行している
  • BEGINでトランザクションを開始している
  • COMMITまたはROLLBACKでSELECT処理で結果の違いを確認できる

ちなみにBEGINしたあと、COMMITする前にSELECTすると、INSERTまたはUPDATEした結果が参照される。ただし、そのままcloseするとコミットされてないデータが破棄される。
データの追加・更新後にSELECT参照する場合、上記の振る舞いを考慮すると、一旦クローズ後、DBを再オープンしてから参照したほうが、コミット忘れの不具合が発見しやすくなる。

// sqlite3のインスタンスを取得
// ※ npm install sqlite3であらかじめインストールしておく
var sqlite3 = require("sqlite3").verbose();

// :memory:を指定すると、メモリ上へDBを作成できる。
// ファイル名を指定する事で、永続化したDBを扱う事もができる。
// オープン時のモードは、
// sqlite3.OPEN_READONLY
// sqlite3.OPEN_READWRITE
// sqlite3.OPEN_CREATE
var db = new sqlite3.Database(":memory:",
  sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE);

// serialize関数を使うことにより、同期処理される。
// 対するparallel関数もある。
db.serialize(function (){
  // テーブルを作成する。
  db.run("CREATE TABLE IF NOT EXISTS sample (col1 TEXT)");

  // トランザクション開始
  db.exec('BEGIN TRANSACTION');

  // データを登録する。
  var stmt = db.prepare("INSERT INTO sample VALUES (?)");
  for(var i = 0; i < 10; i++){
    // SQLの?へ値を設定してSQLを実行する
    stmt.run("data " + i);
  }
  // prepare関数で取得したStatementオブジェクトをクローズする
  // finalizeをコールせずにDB処理をするとエラーになる
  stmt.finalize();

  // データを更新する。
  var stmt2 = db.prepare("UPDATE sample SET col1 = ? WHERE col1 = ?");
  for (var i = 0; i < 3; i++){
    stmt2.run("data x" + i, "data " + i);
  }
  stmt2.finalize();

  // トランザクション終了(COMMIT/ROLLLBACK)
  db.exec("COMMIT");
  //db.exec("ROLLBACK");
  
  // トランザクション完了後のテーブルを参照する
  // 参照用関数は、
  // 1行だけ取得:get()
  // 全行取得:all()
  // 1行ごと取得:each()
  // 結果がコールバックfunction(err,row)で取得できる
  // rowオブジェクトのプロパティがカラム名になっている
  db.each("SELECT rowid AS id, col1 FROM sample", function (err, row) {
    console.log(row.id + " : " + row.col1);
  });

});

// DBを閉じる。
db.close();