Node.jsでSQLiteを使ってみる

はじめに

Node.jsからSQLiteを使ってみる話です。
あくまでも一例です。
また、テストフレームを動作確認目的として利用しており、いわゆる継続的インテグレーション向けではありません。

当方のレベルは以下です。

• MS SQL Serverへ、Node.jsからアクセスしたことがある。
• SQLiteに触れるのは今回初めて。
• テストフレームワークにはMochaを使っている。

MS SQL Serverへのアクセス方法との対比を意識して、SQLiteを始めてみます。
今回は、以下の流れで導入します。

1. コマンドラインからSQLiteにアクセスするツールを導入する
2. コマンドラインから、テーブル作成とカラム作成を行う。
3. Node.jsからSQLiteにアクセスするモジュールを導入する
4. Node.jsからSQLiteのデータベースにRead/Writeする。

作業フォルダは、以下のフォルダ・ファイル構成を前提に作業を進めます。

• db/
  • SQLデータベースの本体を格納。
  • コマンドラインつーるもここに格納
• src/
  • Node.jsのソースコード本体を格納
• test/
  • テストフレームワーク用の（テストドライバー）コードを格納
• package.json

利用するnpmモジュールは以下です。

npm install chai mocha sinon promise-test-helper
npm install sqlite3 date-utils


package.jsonから抜粋

{
"date-utils": "^1.2.21",
"sqlite3": "^3.1.9",

"chai": "^4.1.2",
"mocha": "^3.5.0",
"promise-test-helper": "^0.2.1",
"sinon": "^3.2.1"
}

以下のサンプルコードでは、下記を意識した作りになっています。

• MS SQLでの接続との互換性
• Promiseで非同期制御
• 接続、データ入出力、（あれば続けて）データ入出力、、切断、を分けて構成

SQLiteの導入方法とテーブルカラムの作成

SQLiteは、データベース本体（関係データベース管理システム＝RDBMS）は単なるバイナリファイルです。従って、他のデータベース例えばMS SQL Serverのように「RDBMSをセットアップする」という操作は不要です。

コマンドラインからSQLiteにアクセスするツール

SQLiteの公式ページのダウンロードページから、sqlite3.exeを取得します。
http://www.sqlite.org/download.html

取得の仕方と実行方法は、以下のページ「SQLiteコマンドラインツールのダウンロードとインストール」が分りやすいです。
https://www.dbonline.jp/sqliteinstall/install/index1.html

コマンドラインからSQLiteのテーブル作成とカラム作成

コマンドラインから「db」フォルダ配下に移動して、以下を実行します。
ここでは以下の４つのカラムを作成します。
具体的には、SQLiteコマンドラインツールを立ち上げて、SQLコマンドにてテーブル作成を行います。

• id
  • int型、主キー、自動カウントアップ、Null禁止
• created_at
  • 日付型、NULL禁止
• type
  • int型、Null許容
• owners_hash
  • 文字列型64文字固定長。NULL許容

sqlite3.exe  mydb.sqlite3

CREATE TABLE activitylogs([id] [integer] PRIMARY KEY AUTOINCREMENT NOT NULL, [created_at] [datetime] NOT NULL, [type] [int] NULL, [owners_hash] [char](64) NULL );

.exit;

上記を実行すると、データベースの実体ファイル ./db/mydb.splite3 が生成されます。

コマンドライン上から、データを追加して参照する

同じツール sqlite3.exe 用いて、入出力を行えます。
「sqlite3.exe mydb.sqlite3」で、SQLコマンド入力待ちになります。
もちろん連続入力可能です。
SQLコマンド入力待ちを終えるには、「.exit;」を入力します。

上記で作成したデータベースに対して、データを追加してみます。

INSERT INTO activitylogs([created_at], [type], [owners_hash] ) VALUES('2017/09/12', 111, 'nyan1nyan2nyan3nayn4nayn5nyan6ny');

追加したのち、データベースの状態を見てみます。

SELECT * FROM activitylogs;

上記は「activitiylogsのデータをすべて選択して取得」のSQLコマンドなので、以下のようにデータベースの中身を見ることができます。

node.jsからSQLiteにアクセスしてみる。

Node.jsからSQLiteへ入出力するには、npmでsqlite3モジュールをインストールします。（※上述で実施済みなので、ここで改めてインストールす必要はありません）

npm install sqlite3

Node.jsからのSQLiteへのアクセス方法は、SQLiteの公式のサンプルコードが大変わかりやすいので、一読をお勧めします。
http://www.sqlitetutorial.net/sqlite-nodejs/connect/
http://www.sqlitetutorial.net/sqlite-nodejs/query/

以下のサンプルでは、データ書き込みの例と読み込みの例を、以下のように実装しています。

• 書き込み
  1. createPromiseForSqlConnection() - データベースに接続する
  2. addActivityLog2Database() - 書き込む
  3. closeConnection() - データベースを閉じる
• 読み込み
  1. createPromiseForSqlConnection() - データベースに接続する
  2. getListOfActivityLogWhereDeviceKey() - 読み込む
  3. closeConnection() - データベースを閉じる

データベースに対する書き込み、読み込みのサンプルコード

以下のコードをsql_lite_db.js 、factory4require.js として、srcフォルダー配下に保存します。

sql_lite_db.js

/**
 * [sql_lite_db.js]
 */


require('date-utils'); // Data() クラスのtoString()を拡張してくれる。
var lib = require("./factory4require.js");
var factoryImpl = { // require()を使う代わりに、new Factory() する。
    "sqlite3" : new lib.Factory4Require("sqlite3"),
    "db" : new lib.Factory( {} ) // データベースごとにハッシュマップで持つ。
};

// UTデバッグ用のHookポイント。運用では外部公開しないメソッドはこっちにまとめる。
exports.factoryImpl = factoryImpl;




/**
 * ※SQL接続生成＋Json応答（OK/NG）、なのでsqliteを直接ではなく、この関数を定義する。
 *
 * @param{Object} sqlConfig     SQL接続情報。
 */
var createPromiseForSqlConnection = function( sqlConfig ){
    var db = factoryImpl.db.getInstance();
    var databaseName = sqlConfig.database;
    if( db[ databaseName ] ){
        //  sql connection is OK already!
        return Promise.resolve()
    }else{
        return new Promise(function(resolve,reject){
            var sqlite = sqlite3 = factoryImpl.sqlite3.getInstance().verbose();
            var db_connect = new sqlite.Database( sqlConfig.database, (err) =>{
                if( !err ){
                    //  sql connection is OK!
                    db[ databaseName ] = db_connect;

                    resolve();
                }else{
                    //     error on connection
                    reject(err);
                }
            });
        });
    }
};
exports.createPromiseForSqlConnection = createPromiseForSqlConnection;


var closeConnection = function( databaseName ){
    var dbs = factoryImpl.db.getInstance();
    var db = dbs[ databaseName ];
    if( !db ){
        return Promise.reject({
            "isReady" : false
        });
    }

    return new Promise(function(resolve,reject){
        db.close((err)=>{
            if(!err){
                resolve();
            }else{
                reject(err)
            }
        });
    });
};
exports.closeConnection = closeConnection;




var addActivityLog2Database = function( databaseName, deviceKey, typeOfAction ){
    var dbs = factoryImpl.db.getInstance();
    var db = dbs[ databaseName ];
    if( !db ){
        return Promise.reject({
            "isReady" : false
        });
    }

    return new Promise(function(resolve,reject){
        var now_date = new Date();
        var date_str = now_date.toFormat("YYYY-MM-DD HH24:MI:SS.000"); // data-utilsモジュールでの拡張を利用。
        var query_str = "INSERT INTO activitylogs(created_at, type, owners_hash ) VALUES('" + date_str + "', " + typeOfAction + ", '" + deviceKey + "')";

        db.all(query_str, [], (err, rows) => {
            if(!err){
                var insertedData = {
                    "type" : typeOfAction,
                    "device_key" : deviceKey
                };
                return resolve( insertedData );
            }else{
                reject({
                    "isEnableValidationProcedure" : false
                });
            }
        });
    });
};
exports.addActivityLog2Database = addActivityLog2Database;




/**
 * デバイス識別キーに紐づいたアクティビティーログを、指定されたデータベースから取得する。
 * @param{String} Database データベース名
 * @param{String} deviceKey デバイスの識別キー
 * @returns{Promise} SQLからの取得結果を返すPromiseオブジェクト。成功時resolve( recordset ) 、失敗時reject( err )。
 */
var getListOfActivityLogWhereDeviceKey = function( databaseName, deviceKey ){
    var dbs = factoryImpl.db.getInstance();
    var db = dbs[ databaseName ];
    if( !db ){
        return Promise.reject({
            "isReady" : false
        });
    }

    var query_str = "SELECT created_at, type FROM activitylogs";
    query_str += " WHERE [owners_hash]='" + deviceKey + "'";

    return new Promise(function(resolve,reject){
        db.all(query_str, [], (err, rows) => {
            if(!err){
                return resolve( rows );
            }else{
                return reject( err );
            }
        });
    });
};
exports.getListOfActivityLogWhereDeviceKey = getListOfActivityLogWhereDeviceKey;

※上記のサンプルコードで利用している、Factory / Factory4Require 関数の定義は以下です。これはテストドライバーからの呼び出し時に、スタブへのフックをしやすくすることを目的としています。

※今回は実動作確認が目的なので、データベースのスタブ化はしていません。動作確認を終えたら、SQLiteアクセス部分をスタブに置き換えることで、本来の用途（継続的インテグレーション）のテストコードになります。

factory4require.js

/*
    [factory4require.js]
*/


/**
 * @description 定数オブジェクトのFactory
 */
var Factory = function( staticInstance ){
    this.instance = staticInstance;
}
Factory.prototype.getInstance = function(){
    return this.instance;
};
// if( 開発環境ならば ){ ～ }などとする。
Factory.prototype.setStub = function( value ){
    this.instance = value;
};
exports.Factory = Factory;



/**
 * @description require()をラッパーするFactory
 */
var Factory4Require = function( moduleName ){
    var instance = require( moduleName );
    Factory.call( this, instance );
}
Factory4Require.prototype = Object.create( Factory.prototype );
Factory4Require.prototype.getInstance = function( methodName ){
    if( methodName ){
        return this.instance[ methodName ];
    } else {
        return this.instance;
    }
};
exports.Factory4Require = Factory4Require;

テストフレームワークを用いた動作確認用のサンプルコード

上述のサンプル実装を、実際に動作させて応答を確認するコードは以下です。テストフレームを用いて、ピンポイントで試行します。

以下のコードを sql_lite_db_test.js として test フォルダー配下に保存します。

sql_lite_db_test.js

/*
    [sql_lite_db_test.js]
*/

var chai = require("chai");
var assert = chai.assert;
var expect = chai.expect;
var sinon = require("sinon");
var shouldFulfilled = require("promise-test-helper").shouldFulfilled;
var shouldRejected  = require("promise-test-helper").shouldRejected;
require('date-utils');

const api_sql = require("../src/sql_lite_db.js");

var TEST_CONFIG_SQL = { // テスト用。databese名以外は、SQLiteでは不要。残りはSQL Serverで利用。
    user : "fake_user",
    password : "fake_password",
    server : "fake_server_url",
    database : "./db/mydb.splite3",  //"fake_db_name",
    stream : false,

    // Use this if you're on Windows Azure
    options : {
        encrypt : true
    } // It works well on LOCAL SQL Server if this option is set.
};




describe( "sql_lite_db_test.js::SQLiteトライアル", function(){
    var createPromiseForSqlConnection = api_sql.createPromiseForSqlConnection;
    var closeConnection = api_sql.closeConnection;
    var addActivityLog2Database = api_sql.addActivityLog2Database;
    var getListOfActivityLogWhereDeviceKey = api_sql.getListOfActivityLogWhereDeviceKey;

    describe("::シークエンス調査", function(){
        it("とりあえずテスト", function(){
            var sqlConfig = { "database" : "./db/mydb.sqlite3" }; // npm test 実行フォルダ、からの相対パス
            var promise;
            this.timeout(5000);

            promise = createPromiseForSqlConnection( sqlConfig );

            promise = promise.then( function(){
                return getListOfActivityLogWhereDeviceKey( sqlConfig.database, "nyan1nyan2nyan3nayn4nayn5nyan6ny", null );
            });
/*
            promise = promise.then(function(){
                return addActivityLog2Database( sqlConfig.database, "nyan1nyan2nyan3nayn4nayn5nyan6ny", 90 );
            });
*/
            return shouldFulfilled(
                promise
            ).then(function( result ){
                console.log( result );
                closeConnection( sqlConfig.database );
            });
        });
    });
});

上述で、コマンドラインからSQLiteのデータベースファイル ./db/mydb.splite3 にテーブル「activitylogs」を作成済みの環境で、テストフレームを実行します。

npm test

※package.jsonに、testスクリプトとしてmochaを設定済み場合です。設定してない場合は直に node_modules\\.bin\\mocha を実行してください。

以下のように、テーブルから取得した内容がコンソールに出力されます。

コメントアウトを外してから実行すると、取得に続いて書き込みが行われます。テストの実行毎に、テーブルの内容が追記されていく様子を確認できます。

Microsoft SQL Serverへのアクセスの場合との対比

参考までに、今回のデータの入出力をする相手がSQLiteではなく、SQL Server に置き換えた場合のサンプルコードを示します。

※srcフォルダー配下にこの sql_parts.js を格納して、sql_lite_db_test.js で読み込む被テストコードを変更することで、そのまま実行できます。
※TEST_CONFIG_SQLの値は、準備したSQL Server 側の設定値に合わせてください。

sql_parts.js

/*
    [sql_parts.js]
*/

require('date-utils'); // Data() クラスのtoString()を拡張してくれる。
var lib = require("./factory4require.js");
var factoryImpl = { // require()を使う代わりに、new Factory() する。
    "mssql" : new lib.Factory4Require("mssql")  // https://www.npmjs.com/package/mssql
};

// UTデバッグ用のHookポイント。運用では外部公開しないメソッドはこっちにまとめる。
exports.factoryImpl = factoryImpl;


/**
 * ※SQL接続生成＋Json応答（OK/NG）、なのでmssqlを直接ではなく、この関数を定義する。
 *
 * @param{Object} sqlConfig     SQL接続情報。
 */
var createPromiseForSqlConnection = function( sqlConfig ){
    return new Promise(function(resolve,reject){
        var mssql = factoryImpl.mssql.getInstance();
        var connect = mssql.connect( sqlConfig );
        connect.then(function(){
            resolve();
        }).catch(function( err ){
            reject(err);
        });
    });
};
exports.createPromiseForSqlConnection = createPromiseForSqlConnection;


var closeConnection = function( databaseName ){
    // databaseNameは読み捨て
    // ※こちらは、データベースごとと言う区別をしていないので、SQLiteでの記述と差分がある。
    var mssql = factoryImpl.mssql.getInstance();
    mssql.close();
    return Promise.resolve();
};
exports.closeConnection = closeConnection;



var addActivityLog2Database = function( databaseName, deviceKey, typeOfAction ){
    var mssql = factoryImpl.mssql.getInstance();
    var sql_request = new mssql.Request();
    var now_date = new Date();
    var date_str = now_date.toFormat("YYYY-MM-DD HH24:MI:SS.000"); // data-utilsモジュールでの拡張を利用。
    var query_str = "INSERT INTO [" + databaseName + "].dbo.activitylogs(created_at, type, owners_hash ) VALUES('" + date_str + "', " + typeOfAction + ", '" + deviceKey + "')";
    return sql_request.query( query_str ).then(function(){
        var insertedData = {
            "type" : typeOfAction,
            "device_key" : deviceKey
        };
        return Promise.resolve( insertedData );
    });
};
exports.addActivityLog2Database = addActivityLog2Database;



/**
 * デバイス識別キーに紐づいたバッテリーログを、指定されたデータベースから取得する。
 * @param{String} Database データベース名
 * @param{String} deviceKey デバイスの識別キー
 * @returns{Promise} SQLからの取得結果を返すPromiseオブジェクト。成功時resolve( recordset ) 、失敗時reject( err )。
 */
var getListOfActivityLogWhereDeviceKey = function( databaseName, deviceKey ){
    var mssql = factoryImpl.mssql.getInstance();
    var sql_request = new mssql.Request();

    var query_str = "SELECT created_at, type FROM [" + databaseName + "].dbo.activitylogs";
    query_str += " WHERE [owners_hash]='" + deviceKey + "'";
    return sql_request.query( query_str );
};
exports.getListOfActivityLogWhereDeviceKey = getListOfActivityLogWhereDeviceKey;

SQLコマンド側とNodejsコード側のどちらで指定するか、という差分はあるものの、SQLiteもSQL Serverとほぼ同じような実装でNode.js上からアクセスできるみたいですね。

参考サイト

• SQLiteの公式サイト
  http://www.sqlite.org/index.html
  http://www.sqlite.org/download.html

• SQLITE TUTORIAL
  http://www.sqlitetutorial.net/
  http://www.sqlitetutorial.net/sqlite-nodejs/connect/
  http://www.sqlitetutorial.net/sqlite-nodejs/query/

• SQLiteコマンドラインツールのダウンロードとインストール - DBOnline
  https://www.dbonline.jp/sqliteinstall/install/index1.html