Prerequisite Setup

To utilize native Node modules like better-sqlite3 within an Electron environment, you must address the ABI (Application Binary Interface) version mismatch between Electron's bundled Node.js and the system's Node.js. This requires specific dependencies and a rebuilding step.

npm install --save-dev electron-rebuild
npm install better-sqlite3

It is critical to install electron-rebuild to recompile the native module against Electron's specific Node version. Failure to do so typically results in runtime errors indicating a NODE_MODULE_VERSION mismatch.

Configuring Native Module Rebuilds

Attempting to run the application without rebuilding the native module will trigger an error stating the module was compiled against a different Node.js version. To automate this, add a rebuild script to your package.json.

{
  "scripts": {
    "dev": "vite",
    "rebuild:native": "electron-rebuild -f -w better-sqlite3"
  }
}

Execute the rebuild command immediately after installing the database package:

npm run rebuild:native

Database Connection Management

Create a dedicated file, such as src/database/connection.ts, to handle the database lifecycle and configuration. This layer ensures the database is initialized correctly for both development and production environments, utilizing Write-Ahead Logging (WAL) for performance.

import Database from 'better-sqlite3';
import path from 'path';

/**
 * Determines the database file path based on the environment.
 * In development, it uses a local file. In production, it resolves
 * to the application's resources directory.
 */
const getDatabasePath = (): string => {
  const isDev = process.env.NODE_ENV !== 'production';
  if (isDev) {
    return path.join(__dirname, '../../data/app_data.db');
  }
  return path.join(process.resourcesPath, 'data/app_data.db');
};

const dbPath = getDatabasePath();

// Initialize the database connection with a defined cache size
const db = new Database(dbPath, { cacheSize: 8000 });

// Enable Write-Ahead Logging for better concurrency
db.pragma('journal_mode = WAL');

export default db;

Data Access Layer Implementation

Next, implement the data access logic to manage records. This example demonstrates a repository pattern for querying and bulk inserting data, utilizing transactions to ensure data integrity and improve performance during batch operations.

import db from './connection';

export interface WallpaperRecord {
  id: string;
  source_url: string;
  file_path: string;
}

/**
 * Retrieves all wallpaper records from the database.
 */
export const getAllWallpapers = (): WallpaperRecord[] => {
  try {
    const statement = db.prepare('SELECT * FROM wallpapers');
    return statement.all() as WallpaperRecord[];
  } catch (error) {
    console.error('Failed to fetch wallpapers:', error);
    throw error;
  }
};

/**
 * Performs a batch insert of wallpaper records.
 * Uses 'INSERT OR IGNORE' to handle duplicate keys gracefully.
 *
 * @param columns - Array of column names to insert into.
 * @param records - Array of objects containing the data to insert.
 */
export const batchInsertWallpapers = (columns: string[], records: object[]): void => {
  try {
    const placeholders = columns.map(() => '?').join(',');
    const columnList = columns.join(',');
    
    const sql = `INSERT OR IGNORE INTO wallpapers (${columnList}) VALUES (${placeholders})`;
    const insertStmt = db.prepare(sql);

    // Execute inserts within a transaction for atomicity and speed
    const insertTransaction = db.transaction((items) => {
      for (const item of items) {
        const values = columns.map(col => (item as any)[col]);
        insertStmt.run(...values);
      }
    });

    insertTransaction(records);
  } catch (error) {
    console.error('Batch insertion failed:', error);
    throw error;
  }
};

Application Packaging Configuration

To ensure the database file is included in the distributed application, you must configure the build settings. In your Electron Builder configuration file (e.g., electron-builder.yml or electron-builder.json), include the database file in the extraResources array.

{
  "directories": {
    "output": "dist"
  },
  "extraResources": [
    "./data/app_data.db"
  ]
}

Ensure the database file exists at the specified path in your project root before building. After packaging, the application will access this file from the process.resourcesPath directory.