(Node.js) sqlite3を使って大量のデータをInsert

概要

sqlite3を使って400件のデータをinsertする処理を行った後に、
DBに登録したデータの総件数を取得しましたが、
なぜか397件しか取得できていなかったため、理由をまとめました。

問題点について

問題点　　

原因を調査していると他にも問題点がたくさんありました。

1. insertした時のデータが順序通りではなくばらばらになってしまっていた。
2. insertした総件数が正しい値で取得できていなかった。
3. autoincrementが上手く機能せずに空で登録されている
4. データをテキストファイルに書き込む処理にて、登録の順序がばらばら。

400件のinsert自体はうまくいっていたので上記４つを修正しました。

問題ありの修正部分

まず、非同期について考慮していなかったため、
順序通りの登録と、総件数の取得が上手くいっていませんでした。

問題点1と2については、insert部分の書き方とinsert処理が終了した後に総件数を取得するように修正しました。
動的なデータを登録する場合は、SQLインジェクションも考慮して、db.prepareを使う
insert処理終了後に、コールバックで総件数を取得するsqlを呼び出す。

修正前

// INSERT ALL
users.forEach((user, index) => {

    var insertSql = `insert or replace into user 
        (firstName, lastName, fullName)
        values (${user.firstName}, ${user.lastName}, ${user.fullName})`

    DbSetting.DbCommon.getDb().run(
        insertSql
    )

    fs.appendFile("insert.txt", insertSql, (error) => {
        if (error) throw err;
    });
})

// ALL COUNT
DbSetting.DbCommon.getDb().get(`select count(*) from user`, (err, row) => {
    if (err) {
        console.log(error)
    }
    console.log(row["count(*)"])
})

修正後

// db呼び出し
var db = DbSetting.DbCommon.getDb()

var insertSql = `insert into user 
        (firstName, lastName, fullName)
        values (? ,? ,?)`

var stmt = db.prepare(insertSql)

// INSERT ALL
users.forEach((user) => {
    stmt.run(`${user.firstName}`, `${user.lastName}`, `${user.fullName}`);

    fs.appendFileSync("insert.txt", insertSql, (err) => {
        if (err) throw err;
    });
})

stmt.finalize(() => {
    // ALL COUNT
    db.get(`select count(*) from user`, (err, row) => {
        if (err) {
            console.log(err)
        } else {
            console.log(row["count(*)"])
        }
    })
});

問題点3のautoincrementoに関しては書き方を以下のように修正。
NUMBER型では使テーブルは作成されるが、autoincrementされないため、INTEGER型に修正。
NUMBER型だとROWIDとも紐づけが自動でされない模様。

id NUMBER AUTO INCREMENT PRIMARY KEY → userId INTEGER PRIMARY KEY AUTOINCREMENT

問題点4は同期処理に変更
appendFile → appendFileSync

修正後の全体コード

dbCommon.js

var sqlite3 = require('sqlite3').verbose()

let db

exports.DbCommon = class DbCommon {
    static init() {
        db = new sqlite3.Database('sample.db');
    }

    static getDb() {
        return db
    }

    static initCreateTableUser() {
        db.serialize(() => {
            // CreateTableUser
            let CREATE_TABLE_USER = 'CREATE TABLE IF NOT EXISTS User'
                + '(userId INTEGER PRIMARY KEY AUTOINCREMENT,'
                + 'firstName TEXT NOT NULL,'
                + 'lastName TEXT NOT NULL,'
                + 'fullName TEXT NOT NULL)'

            db.run(CREATE_TABLE_USER, (error) => {
                if (error) {
                    console.log(error)
                }
                console.log(CREATE_TABLE_USER);
            });
        })
    }
}

index.js

const DbSetting = require('./dbCommon');
const UserModel = require('./User')

const fs = require('fs')

// CreateDataBase
DbSetting.DbCommon.init();

DbSetting.DbCommon.getDb();

// initCreateTableUser
DbSetting.DbCommon.initCreateTableUser()

let users = new Array()

// users
for (var i = 0; i < 400; i++) {
    users.push(new UserModel.User(i, i, i, i + i))
}

// db呼び出し
var db = DbSetting.DbCommon.getDb()

var insertSql = `insert into user 
        (firstName, lastName, fullName)
        values (? ,? ,?)`

var stmt = db.prepare(insertSql)

// INSERT ALL
users.forEach((user) => {

    stmt.run(`${user.firstName}`, `${user.lastName}`, `${user.fullName}`);

    fs.appendFileSync("insert.txt", `firstName:${user.firstName} lastName:${user.lastName} fullName:${user.fullName}\n`, (err) => {
        if (err) throw err;
    });
})

stmt.finalize(() => {
    // ALL COUNT
    db.get(`select count(*) from user`, (err, row) => {
        if (err) {
            console.log(err)
        } else {
            console.log(row["count(*)"])
        }
    })
});

GitHub

github.com

参考サイト

qiita.com