Electron数据库集成：SQLite、IndexedDB与本地存储

Electron数据库集成：SQLite、IndexedDB与本地存储

【免费下载链接】electron 使用Electron构建跨平台桌面应用程序，支持JavaScript、HTML和CSS 项目地址: https://gitcode.com/GitHub_Trending/el/electron

引言：为什么桌面应用需要本地数据存储？

在桌面应用开发中，数据持久化是核心需求之一。与Web应用不同，桌面应用需要处理更复杂的本地数据存储场景：用户配置、离线数据缓存、大型数据集处理等。Electron作为跨平台桌面应用框架，提供了多种数据存储解决方案，每种方案都有其独特的适用场景。

本文将深入探讨Electron中三种主流的数据库集成方案：SQLite（关系型数据库）、IndexedDB（浏览器内置NoSQL数据库）和本地存储（轻量级键值存储），帮助你根据具体需求选择最合适的方案。

技术选型对比表

特性	SQLite	IndexedDB	本地存储
存储类型	关系型数据库	NoSQL对象存储	键值存储
数据容量	无限制（受磁盘空间限制）	通常50%磁盘空间	5-10MB
查询能力	完整的SQL支持	索引查询	简单键值查询
事务支持	ACID事务	事务支持	无事务
跨进程访问	需要文件锁	同源策略限制	同源策略限制
适用场景	复杂数据结构、大量数据	结构化对象存储	简单配置数据

方案一：SQLite集成 - 企业级数据管理

为什么选择SQLite？

SQLite是轻量级、零配置的关系型数据库，特别适合桌面应用的本地数据存储需求。它提供了完整的SQL支持、ACID事务和出色的性能。

安装与配置

在Electron主进程中集成SQLite：

# 安装better-sqlite3（性能更好的SQLite驱动）
npm install better-sqlite3

基础使用示例

// main.js - 主进程
const Database = require('better-sqlite3');
const path = require('path');
const { app } = require('electron');

class SQLiteManager {
  constructor() {
    this.dbPath = path.join(app.getPath('userData'), 'app.db');
    this.db = null;
  }

  initialize() {
    try {
      this.db = new Database(this.dbPath);
      this.createTables();
      console.log('SQLite数据库初始化成功');
    } catch (error) {
      console.error('数据库初始化失败:', error);
    }
  }

  createTables() {
    // 用户表
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // 设置表
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  // 用户操作
  addUser(username, email) {
    const stmt = this.db.prepare('INSERT INTO users (username, email) VALUES (?, ?)');
    return stmt.run(username, email);
  }

  getUsers() {
    return this.db.prepare('SELECT * FROM users').all();
  }

  // 设置操作
  setSetting(key, value) {
    const stmt = this.db.prepare(`
      INSERT OR REPLACE INTO settings (key, value, updated_at) 
      VALUES (?, ?, CURRENT_TIMESTAMP)
    `);
    return stmt.run(key, value);
  }

  getSetting(key) {
    const stmt = this.db.prepare('SELECT value FROM settings WHERE key = ?');
    const result = stmt.get(key);
    return result ? result.value : null;
  }

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

module.exports = SQLiteManager;

渲染进程通信

通过预加载脚本和安全地暴露API：

// preload.js
const { contextBridge, ipcRenderer } = require('electron');

contextBridge.exposeInMainWorld('electronAPI', {
  // SQLite操作
  sqlite: {
    execute: (sql, params) => ipcRenderer.invoke('sqlite-execute', sql, params),
    query: (sql, params) => ipcRenderer.invoke('sqlite-query', sql, params),
    get: (sql, params) => ipcRenderer.invoke('sqlite-get', sql, params)
  }
});

// main.js - IPC处理器
ipcMain.handle('sqlite-execute', async (event, sql, params) => {
  const stmt = db.prepare(sql);
  return stmt.run(...params);
});

ipcMain.handle('sqlite-query', async (event, sql, params) => {
  return db.prepare(sql).all(...params);
});

ipcMain.handle('sqlite-get', async (event, sql, params) => {
  return db.prepare(sql).get(...params);
});

高级特性：数据迁移与备份

class SQLiteAdvanced extends SQLiteManager {
  async backup() {
    const backupPath = path.join(app.getPath('userData'), `backup_${Date.now()}.db`);
    await this.db.backup(backupPath);
    return backupPath;
  }

  async migrate() {
    // 检查当前数据库版本
    const version = this.getSetting('db_version') || 0;
    
    if (version < 1) {
      this.db.exec('ALTER TABLE users ADD COLUMN last_login DATETIME');
      this.setSetting('db_version', 1);
    }
    
    if (version < 2) {
      this.db.exec('CREATE INDEX idx_users_email ON users(email)');
      this.setSetting('db_version', 2);
    }
  }
}

方案二：IndexedDB - 浏览器原生NoSQL解决方案

IndexedDB的优势

IndexedDB是浏览器内置的NoSQL数据库，支持异步操作、事务和索引查询，非常适合存储结构化对象数据。

基础使用模式

// renderer.js - 渲染进程
class IndexedDBManager {
  constructor(dbName = 'appDatabase', version = 1) {
    this.dbName = dbName;
    this.version = version;
    this.db = null;
  }

  async open() {
    return new Promise((resolve, reject) => {
      const request = indexedDB.open(this.dbName, this.version);

      request.onerror = () => reject(request.error);
      request.onsuccess = () => {
        this.db = request.result;
        resolve(this.db);
      };

      request.onupgradeneeded = (event) => {
        const db = event.target.result;
        this.createStores(db);
      };
    });
  }

  createStores(db) {
    // 用户存储
    if (!db.objectStoreNames.contains('users')) {
      const userStore = db.createObjectStore('users', { 
        keyPath: 'id', 
        autoIncrement: true 
      });
      userStore.createIndex('email', 'email', { unique: true });
      userStore.createIndex('username', 'username', { unique: true });
    }

    // 设置存储
    if (!db.objectStoreNames.contains('settings')) {
      const settingsStore = db.createObjectStore('settings', { keyPath: 'key' });
    }

    // 文件缓存存储
    if (!db.objectStoreNames.contains('fileCache')) {
      const fileStore = db.createObjectStore('fileCache', { keyPath: 'key' });
    }
  }

  // CRUD操作
  async add(storeName, data) {
    const transaction = this.db.transaction([storeName], 'readwrite');
    const store = transaction.objectStore(storeName);
    return store.add(data);
  }

  async get(storeName, key) {
    const transaction = this.db.transaction([storeName], 'readonly');
    const store = transaction.objectStore(storeName);
    return store.get(key);
  }

  async getAll(storeName) {
    const transaction = this.db.transaction([storeName], 'readonly');
    const store = transaction.objectStore(storeName);
    return store.getAll();
  }

  async update(storeName, data) {
    const transaction = this.db.transaction([storeName], 'readwrite');
    const store = transaction.objectStore(storeName);
    return store.put(data);
  }

  async delete(storeName, key) {
    const transaction = this.db.transaction([storeName], 'readwrite');
    const store = transaction.objectStore(storeName);
    return store.delete(key);
  }
}

高级查询与索引

class AdvancedIndexedDB extends IndexedDBManager {
  async queryByIndex(storeName, indexName, value) {
    const transaction = this.db.transaction([storeName], 'readonly');
    const store = transaction.objectStore(storeName);
    const index = store.index(indexName);
    
    return new Promise((resolve, reject) => {
      const request = index.getAll(value);
      request.onsuccess = () => resolve(request.result);
      request.onerror = () => reject(request.error);
    });
  }

  async rangeQuery(storeName, indexName, lower, upper) {
    const transaction = this.db.transaction([storeName], 'readonly');
    const store = transaction.objectStore(storeName);
    const index = store.index(indexName);
    const range = IDBKeyRange.bound(lower, upper);
    
    return new Promise((resolve, reject) => {
      const request = index.getAll(range);
      request.onsuccess = () => resolve(request.result);
      request.onerror = () => reject(request.error);
    });
  }
}

性能优化策略

class IndexedDBOptimizer extends AdvancedIndexedDB {
  constructor() {
    super('appDatabase', 2); // 版本升级触发优化
  }

  createStores(db) {
    super.createStores(db);
    
    // 添加更多索引优化查询性能
    const userStore = db.transaction.objectStore('users');
    userStore.createIndex('created_at', 'created_at');
    userStore.createIndex('last_active', 'last_active');
  }

  async batchOperation(storeName, operations) {
    const transaction = this.db.transaction([storeName], 'readwrite');
    const store = transaction.objectStore(storeName);
    
    operations.forEach(op => {
      if (op.type === 'add') store.add(op.data);
      if (op.type === 'put') store.put(op.data);
      if (op.type === 'delete') store.delete(op.key);
    });
    
    return new Promise((resolve, reject) => {
      transaction.oncomplete = () => resolve();
      transaction.onerror = () => reject(transaction.error);
    });
  }
}

方案三：本地存储 - 轻量级配置管理

localStorage与sessionStorage

class LocalStorageManager {
  constructor(prefix = 'app_') {
    this.prefix = prefix;
  }

  // 基础操作
  set(key, value) {
    try {
      const serialized = JSON.stringify(value);
      localStorage.setItem(this.prefix + key, serialized);
      return true;
    } catch (error) {
      console.error('存储失败:', error);
      return false;
    }
  }

  get(key, defaultValue = null) {
    try {
      const item = localStorage.getItem(this.prefix + key);
      return item ? JSON.parse(item) : defaultValue;
    } catch (error) {
      console.error('读取失败:', error);
      return defaultValue;
    }
  }

  remove(key) {
    localStorage.removeItem(this.prefix + key);
  }

  clear() {
    // 只清除当前应用的前缀项
    Object.keys(localStorage)
      .filter(key => key.startsWith(this.prefix))
      .forEach(key => localStorage.removeItem(key));
  }

  // 高级功能
  getAll() {
    const result = {};
    Object.keys(localStorage)
      .filter(key => key.startsWith(this.prefix))
      .forEach(key => {
        try {
          result[key.slice(this.prefix.length)] = JSON.parse(localStorage.getItem(key));
        } catch (error) {
          console.warn(`解析失败: ${key}`, error);
        }
      });
    return result;
  }

  // 容量监控
  getUsage() {
    let total = 0;
    Object.keys(localStorage).forEach(key => {
      total += localStorage[key] ? localStorage[key].length : 0;
    });
    return total;
  }

  // 自动清理旧数据
  autoClean(maxSize = 5 * 1024 * 1024) { // 5MB
    const currentUsage = this.getUsage();
    if (currentUsage <= maxSize) return;

    // 按时间排序并清理最旧的数据
    const items = [];
    Object.keys(localStorage)
      .filter(key => key.startsWith(this.prefix))
      .forEach(key => {
        items.push({
          key,
          size: localStorage[key] ? localStorage[key].length : 0,
          // 假设键名包含时间戳或使用其他方式确定新旧
        });
      });

    items.sort((a, b) => a.key.localeCompare(b.key)); // 简单按键名排序

    let toRemove = currentUsage - maxSize;
    for (const item of items) {
      if (toRemove <= 0) break;
      localStorage.removeItem(item.key);
      toRemove -= item.size;
    }
  }
}

配置管理最佳实践

class ConfigManager extends LocalStorageManager {
  constructor() {
    super('config_');
    this.defaultConfig = {
      theme: 'light',
      language: 'zh-CN',
      notifications: true,
      autoSave: true,
      fontSize: 14
    };
  }

  // 获取配置，合并默认值
  getConfig() {
    const saved = this.get('app_config', {});
    return { ...this.defaultConfig, ...saved };
  }

  // 更新配置
  updateConfig(updates) {
    const current = this.getConfig();
    const newConfig = { ...current, ...updates };
    this.set('app_config', newConfig);
    return newConfig;
  }

  // 重置为默认配置
  resetConfig() {
    this.set('app_config', this.defaultConfig);
    return this.defaultConfig;
  }

  // 配置变更监听
  watchConfig(callback) {
    const originalSetItem = localStorage.setItem;
    localStorage.setItem = function(key, value) {
      originalSetItem.apply(this, arguments);
      if (key.startsWith('config_')) {
        callback(key, value);
      }
    };
  }
}

混合方案：根据场景选择最佳存储

决策流程图

实际应用场景示例

class HybridStorageManager {
  constructor() {
    this.sqlite = new SQLiteManager();
    this.indexedDB = new IndexedDBManager();
    this.localStorage = new LocalStorageManager();
  }

  async initialize() {
    await this.sqlite.initialize();
    await this.indexedDB.open();
  }

  // 用户数据 - 使用SQLite（关系型，需要复杂查询）
  async saveUser(userData) {
    return this.sqlite.addUser(userData.username, userData.email);
  }

  async getUsers(filter) {
    let query = 'SELECT * FROM users';
    let params = [];
    
    if (filter) {
      query += ' WHERE username LIKE ? OR email LIKE ?';
      params = [`%${filter}%`, `%${filter}%`];
    }
    
    return this.sqlite.query(query, params);
  }

  // 应用状态 - 使用IndexedDB（结构化对象）
  async saveAppState(state) {
    return this.indexedDB.update('app_state', {
      key: 'current_state',
      ...state,
      timestamp: Date.now()
    });
  }

  async getAppState() {
    return this.indexedDB.get('app_state', 'current_state');
  }

  // 用户配置 - 使用localStorage（简单键值）
  saveUserConfig(config) {
    return this.localStorage.set('user_config', config);
  }

  getUserConfig() {
    return this.localStorage.get('user_config', {});
  }

  // 文件缓存 - 根据大小选择存储
  async cacheFile(key, data) {
    if (data.size > 1024 * 1024) { // 大于1MB
      // 大文件使用IndexedDB
      return this.indexedDB.update('file_cache', {
        key,
        data,
        size: data.size,
        cachedAt: Date.now()
      });
    } else {
      // 小文件使用localStorage
      return this.localStorage.set(`file_${key}`, data);
    }
  }
}

性能优化与最佳实践

数据库连接管理

class ConnectionPool {
  constructor(maxConnections = 5) {
    this.pool = [];
    this.maxConnections = maxConnections;
  }

  async getConnection() {
    if (this.pool.length > 0) {
      return this.pool.pop();
    }
    
    if (this.pool.length < this.maxConnections) {
      const db = new Database('app.db');
      return db;
    }
    
    // 等待连接释放
    return new Promise(resolve => {
      const check = () => {
        if (this.pool.length > 0) {
          resolve(this.pool.pop());
        } else {
          setTimeout(check, 100);
        }
      };
      check();
    });
  }

  releaseConnection(db) {
    if (this.pool.length < this.maxConnections) {
      this.pool.push(db);
    } else {
      db.close();
    }
  }
}

批量操作优化

async function batchInsert(records, chunkSize = 1000) {
  const total = records.length;
  const chunks = Math.ceil(total / chunkSize);
  
  for (let i = 0; i < chunks; i++) {
    const chunk = records.slice(i * chunkSize, (i + 1) * chunkSize);
    await processChunk(chunk);
    
    // 进度反馈
    const progress = ((i + 1) / chunks) * 100;
    console.log(`处理进度: ${progress.toFixed(1)}%`);
  }
}

async function processChunk(chunk) {
  const db = await getConnection();
  const transaction = db.transaction();
  
  try {
    const stmt = db.prepare('INSERT INTO data VALUES (?, ?, ?)');
    chunk.forEach(record => {
      stmt.run(record.field1, record.field2, record.field3);
    });
    
    transaction.commit();
  } catch (error) {
    transaction.rollback();
    throw error;
  } finally {
    releaseConnection(db);
  }
}

数据备份与恢复

class BackupManager {
  constructor() {
    this.backupDir = path.join(app.getPath('userData'), 'backups');
    fs.ensureDirSync(this.backupDir);
  }

  async createBackup() {
    const timestamp = new Date().toISOString().replace(/[:.]/g, '-');
    const backupFile = path.join(this.backupDir, `backup-${timestamp}.db`);
    
    // SQLite备份
    const db = new Database('app.db');
    await db.backup(backupFile);
    db.close();
    
    // 备份元数据
    const meta = {
      timestamp: new Date().toISOString(),
      size: fs.statSync(backupFile).size,
      version: app.getVersion()
    };
    
    fs.writeFileSync(backupFile + '.meta', JSON.stringify(meta));
    
    return { file: backupFile, meta };
  }

  async restoreBackup(backupFile) {
    // 验证备份文件
    const metaFile = backupFile + '.meta';
    if (!fs.existsSync(metaFile)) {
      throw new Error('备份元数据文件不存在');
    }
    
    const meta = JSON.parse(fs.readFileSync(metaFile, 'utf8'));
    
    // 恢复数据库
    const tempFile = path.join(this.backupDir, 'restore_temp.db');
    fs.copyFileSync(backupFile, tempFile);
    
    const tempDb = new Database(tempFile);
    const mainDb = new Database('app.db');
    
    // 清空当前数据库
    mainDb.exec('BEGIN TRANSACTION');
    const tables = mainDb.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
    tables.forEach(table => {
      if (table.name !== 'sqlite_sequence') {
        mainDb.exec(`DROP TABLE IF EXISTS ${table.name}`);
      }
    });
    
    // 从备份恢复
    const backupSchema = tempDb.prepare("SELECT sql FROM sqlite_master WHERE type='table'").all();
    backupSchema.forEach(schema => {
      mainDb.exec(schema.sql);
    });
    
    const backupData = tempDb.prepare("SELECT * FROM sqlite_master WHERE type='table'").all();
    // ... 数据复制逻辑
    
    mainDb.exec('COMMIT');
    
    tempDb.close();
    mainDb.close();
    fs.unlinkSync(tempFile);
    
    return meta;
  }
}

安全考虑

数据加密

const crypto = require('crypto');

class SecureStorage {
  constructor(encryptionKey) {
    this.key = encryptionKey;
  }

  encrypt(data) {
    const iv = crypto.randomBytes(16);
    const cipher = crypto.createCipheriv('aes-256-gcm', this.key, iv);
    let encrypted = cipher.update(JSON.stringify(data), 'utf8', 'hex');
    encrypted += cipher.final('hex');
    const authTag = cipher.getAuthTag().toString('hex');
    
    return {
      iv: iv.toString('hex'),
      data: encrypted,
      authTag: authTag
    };
  }

  decrypt(encryptedData) {
    const decipher = crypto.createDecipheriv(
      'aes-256-gcm', 
      this.key, 
      Buffer.from(encryptedData.iv, 'hex')
    );
    decipher.setAuthTag(Buffer.from(encryptedData.authTag, 'hex'));
    
    let decrypted = decipher.update(encryptedData.data, 'hex', 'utf8');
    decrypted += decipher.final('utf8');
    
    return JSON.parse(decrypted);
  }

  // 安全的数据库操作
  async secureSet(key, value) {
    const encrypted = this.encrypt(value);
    await db.set(key, encrypted);
  }

  async secureGet(key) {
    const encrypted = await db.get(key);
    return encrypted ? this.decrypt(encrypted) : null;
  }
}

敏感数据处理

class SensitiveDataHandler {
  constructor() {
    this.sensitiveFields = ['password', 'token', 'api_key', 'secret'];
  }

  maskData(data) {
    const masked = { ...data };
    this.sensitiveFields.forEach(field => {
      if (masked[field]) {
        masked[field] = '***';
      }
    });
    return masked;
  }

  async logSensitiveOperation(operation, data) {
    const maskedData = this.maskData(data);
    console.log(`${operation}:`, maskedData);
    
    // 审计日志
    await db.insert('audit_log', {
      operation,
      data: JSON.stringify(maskedData),
      timestamp: new Date(),
      user: 'system'
    });
  }

  validateInput(data) {
    // 防止SQL注入
    const sqlInjectionPattern = /(['";]|--|\/\*|\*\/|union|select|insert|update|delete|drop)/i;
    for (const key in data) {
      if (typeof data[key] === 'string' && sqlInjectionPattern.test(data[key])) {
        throw new Error(`可能的SQL注入攻击检测到在字段: ${key}`);
      }
    }
    return true;
  }
}

故障排除与调试

常见问题解决方案

问题	可能原因	解决方案
SQLite数据库锁死	多进程同时写入	使用连接池，合理的事务管理
IndexedDB版本冲突	数据库结构变更	实现版本迁移策略
localStorage容量不足	存储数据过大	实现数据清理策略，使用其他方案
数据不同步	多窗口数据竞争	使用消息广播同步数据

调试工具与技巧

class DatabaseDebugger {
  static enableDebugLogging() {
    // SQLite调试
    const originalRun = Database.prototype.run;
    Database.prototype.run = function(...args) {
      console.log('SQL执行:', args[0], args.slice(1));
      return originalRun.apply(this, args);
    };

    // IndexedDB调试
    const originalOpen = indexedDB.open;
    indexedDB.open = function(...args) {
      console.log('IndexedDB打开:', args[0]);
      return originalOpen.apply(this, args);
    };
  }

  static async analyzeDatabase() {
    // SQLite分析
    const db = new Database('app.db');
    const tables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
    
    console.log('数据库表结构:');
    tables.forEach(table => {
      const schema = db.prepare(`PRAGMA table_info(${table.name})`).all();
      console.log(`表 ${table.name}:`, schema);
    });

    // 空间使用分析
    const spaceUsage = db.prepare(`
      SELECT name, 
             page_count * page_size as size_bytes
      FROM pragma_page_count(), pragma_page_size()
    `).get();
    
    console.log('数据库空间使用:', spaceUsage);
    db.close();
  }
}

总结与推荐

通过本文的详细探讨，我们可以看到Electron提供了多种数据存储解决方案，每种方案都有其独特的优势和适用场景：

1. SQLite：适合需要复杂查询、事务支持和大量数据存储的场景
2. IndexedDB：适合结构化对象存储和浏览器原生集成的场景
3. 本地存储：适合简单的配置数据和轻量级键值存储

在实际项目中，推荐采用混合存储策略，根据数据的特点选择最合适的存储方案。同时，务必注意数据安全、性能优化和错误处理，确保应用的稳定性和用户体验。

记住，良好的数据存储设计是桌面应用成功的关键因素之一。选择合适的存储方案，实现高效的数据管理，将为你的Electron应用奠定坚实的基础。

【免费下载链接】electron 使用Electron构建跨平台桌面应用程序，支持JavaScript、HTML和CSS 项目地址: https://gitcode.com/GitHub_Trending/el/electron