SQLite — better-sqlite3로 로컬 데이터베이스 구축

npm install better-sqlite3
npm install @electron/rebuild --save-dev
npx electron-rebuild

// main.js — 메인 프로세스에서만 사용
const Database = require('better-sqlite3');
const path = require('path');
const { app } = require('electron');

// userData 디렉토리에 DB 파일 생성
const dbPath = path.join(app.getPath('userData'), 'app.db');
const db = new Database(dbPath);

// WAL 모드 설정 — 동시 읽기 성능 향상
db.pragma('journal_mode = WAL');

function initializeDatabase() {
  // 마이그레이션 테이블
  db.exec(`
    CREATE TABLE IF NOT EXISTS migrations (
      id INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      applied_at TEXT DEFAULT (datetime('now'))
    )
  `);

  const migrations = [
    {
      name: '001_create_notes',
      sql: `
        CREATE TABLE IF NOT EXISTS notes (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          content TEXT DEFAULT '',
          created_at TEXT DEFAULT (datetime('now')),
          updated_at TEXT DEFAULT (datetime('now'))
        )
      `,
    },
    {
      name: '002_add_tags',
      sql: `
        CREATE TABLE IF NOT EXISTS tags (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          name TEXT UNIQUE NOT NULL
        );
        CREATE TABLE IF NOT EXISTS note_tags (
          note_id INTEGER REFERENCES notes(id) ON DELETE CASCADE,
          tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
          PRIMARY KEY (note_id, tag_id)
        );
      `,
    },
  ];

  const applied = db.prepare('SELECT name FROM migrations').all()
    .map(r => r.name);

  for (const migration of migrations) {
    if (!applied.includes(migration.name)) {
      db.exec(migration.sql);
      db.prepare('INSERT INTO migrations (name) VALUES (?)').run(migration.name);
      console.log(`마이그레이션 적용: ${migration.name}`);
    }
  }
}

// 노트 CRUD 모듈
class NoteRepository {
  constructor(db) {
    this.db = db;
    this.stmts = {
      getAll: db.prepare('SELECT * FROM notes ORDER BY updated_at DESC'),
      getById: db.prepare('SELECT * FROM notes WHERE id = ?'),
      create: db.prepare('INSERT INTO notes (title, content) VALUES (?, ?)'),
      update: db.prepare(`
        UPDATE notes SET title = ?, content = ?, updated_at = datetime('now')
        WHERE id = ?
      `),
      delete: db.prepare('DELETE FROM notes WHERE id = ?'),
      search: db.prepare(`
        SELECT * FROM notes
        WHERE title LIKE ? OR content LIKE ?
        ORDER BY updated_at DESC
      `),
    };
  }

  getAll() {
    return this.stmts.getAll.all();
  }

  getById(id) {
    return this.stmts.getById.get(id);
  }

  create(title, content) {
    const result = this.stmts.create.run(title, content);
    return this.getById(result.lastInsertRowid);
  }

  update(id, title, content) {
    this.stmts.update.run(title, content, id);
    return this.getById(id);
  }

  delete(id) {
    return this.stmts.delete.run(id);
  }

  search(query) {
    const pattern = `%${query}%`;
    return this.stmts.search.all(pattern, pattern);
  }
}

// main.js
const noteRepo = new NoteRepository(db);

ipcMain.handle('notes:getAll', () => noteRepo.getAll());
ipcMain.handle('notes:getById', (_e, id) => noteRepo.getById(id));
ipcMain.handle('notes:create', (_e, title, content) => noteRepo.create(title, content));
ipcMain.handle('notes:update', (_e, id, title, content) => noteRepo.update(id, title, content));
ipcMain.handle('notes:delete', (_e, id) => noteRepo.delete(id));
ipcMain.handle('notes:search', (_e, query) => noteRepo.search(query));

// 여러 작업을 하나의 트랜잭션으로 묶기
const importNotes = db.transaction((notes) => {
  const insert = db.prepare('INSERT INTO notes (title, content) VALUES (?, ?)');
  for (const note of notes) {
    insert.run(note.title, note.content);
  }
  return notes.length;
});

// 사용: 에러 시 자동 롤백
try {
  const count = importNotes(notesArray);
  console.log(`${count}개 노트 가져오기 완료`);
} catch (error) {
  console.error('가져오기 실패 (자동 롤백됨):', error);
}

app.on('before-quit', () => {
  db.close();
});