Node.js で SQLite を利用してみた

1. package.jsonに追加

"dependencies": {
    "sqlite3": "^5.0.0"
}

1. DB用クラスを作る（db.js）

const sqlite3 = require('sqlite3').verbose();
module.exports = class DB {
  constructor() {
    this.db = new sqlite3.Database(path.join(process.cwd(), 'hoge.sqlite'));
    this.db.serialize(() => {
      this.db.run("CREATE TABLE IF NOT EXISTS hoge (title TEXT, url TEXT, created_at INTEGER)");
    });
  }

  insert(title, url) {
    const dateInteger = new Date().getTime();
    this.db.serialize(() => {
      const stmt = this.db.prepare('INSERT INTO hoge VALUES (?, ?, ?)')
      stmt.run([title, url, dateInteger]);
      stmt.finalize();
    });
  }

  search(query) {
    return new Promise((resolve, reject) => {
      this.db.serialize(() => {
        this.db.each(`SELECT * FROM hoge WHERE title LIKE '%${query}%'`, (error, row) => {
          if (error) {
            console.error(`Error - search: ${error}`);
            resolve(false);
            return;
          }
          const createdAt = new Date(row.created_at);
          const createdAtString = `${createdAt.getFullYear()}/${((createdAt.getMonth() > 8) ? (createdAt.getMonth() + 1) : ('0' + (createdAt.getMonth() + 1)))}/${((createdAt.getDate() > 9) ? createdAt.getDate() : ('0' + createdAt.getDate()))} ${((createdAt.getHours() > 9) ? createdAt.getHours() : '0' + createdAt.getHours())}:${((createdAt.getMinutes() > 9) ? createdAt.getMinutes() : '0' + createdAt.getMinutes())}`
          console.log(`[${createdAtString}] ${row.title}  ${row.url}`);
        }, (error, count) => {
          if (error) {
            console.error(`Error - search: ${error}`);
            resolve(false);
            return;
          }
          if (count === 0) {
            resolve(false);
            return;
          }
          resolve(true);
        });
      });
    });
  }

  close() {
    this.db.close();
  }
}

1. index.jsで使う

const Db = require('./db');
const db = new Db();
var title = "fuga";
var url = "https://makietan.github.io";
db.insert(title, url);
db.search('fuga');
db.close();

close を自分で実行しないといけないのが面倒だけど仕方ないね。 もっといい方法を誰かが書いてくれるだろうし、それを待とう。