Electron SQLite Example

database.js

const path = require("path");
const fs = require("fs");
const { app } = require("electron");
const sqlite3 = require("sqlite3").verbose();

// Build the database path and output to the console
const appDataPath = app.getPath("appData");
const databasePath = path.join(appDataPath, "database.sqlite");
console.log(`Database Path: ${databasePath}`);

// Creates a new SQLite database instance and add an error handler;
var db = null;

// Initialize the database module
const init = (appWindow) => {
    if(fs.existsSync(databasePath)){
        console.log("Database file already exists.");

        // Create a new database instance and adds a error handler
        db = new sqlite3.Database(databasePath);
        db.on("error", (err) => {
            console.error(`Database error: ${err.message}`);
            db.close();
        });

        // Notify the front-end that database is ready
        appWindow.webContents.send("databaseReady");
    } else {
        // If the database file doesn't exists, create it
        build(appWindow);
    }
};

const build = (appWindow) => {
    console.log("Building database file");

    // Create a new database instance and adds a error handler
    db = new sqlite3.Database(databasePath);
    db.on("error", (err) => {
        console.error(`Database error: ${err.message}`);
        db.close();
    });

    db.serialize(() => {
        // Create the table for storing items
        db.run(`
            CREATE TABLE IF NOT EXISTS items (
                id INTEGER PRIMARY KEY,
                name TEXT,
                description TEXT
            )
        `);

        // Notify the front-end that database is ready
        appWindow.webContents.send("databaseReady");
    });
};

const insertData = (payload, cb) => {
    // Get fields from the payload
    let { name, description } = payload;

    // Insert the fields into the database
    db.run(
        `INSERT INTO items (name, description) VALUES (?, ?)`,
        [name, description],
        (err) => {
            if(err){
                cb({ success: false, error: err.message });
            } else {
                cb({ success: true });
            }
        }
    )
};

const getData = (id = null, cb) => {
    // Build the query to fetch all items. If an ID is passed,
    // the query will search for items with corresponding id.
    let query = `SELECT * FROM items ${id ? `WHERE id = ${id}` : ""}`;

    // Run the query
    db.all(
        query,
        (err, rows) => {
            if(err){
                cb({ success: false, error: err.message });
            } else {
                cb({ success: true, data: rows });
            }
        }
    );
};

const updateData = (payload, db) => {
    // Get fields from the payload
    let { id, name, description } = payload;

    // Update the item
    db.run(
        `UPDATE items SET name = ?, description = ? WHERE id = ?`,
        [name, description, id],
        (err) => {
            if(err){
                cb({ success: false, error: err.message });
            } else {
                cb({ success: true });
            }
        }
    );
};

const deleteData = (id, cb) => {
    // Delete the item from the database
    db.run(
        `DELETE FROM items WHERE id = ?`,
        [id],
        (err) => {
            if(err){
                cb({ success: false, error: err.message });
            } else {
                cb({ success: true });
            }
        }
    );
};

module.exports = {
    init,
    insertData,
    getData,
    updateData,
    deleteData
};

events.js

const { ipcMain } = require("electron");
const database = require("./database");

const init = (appWindow) => {
    ipcMain.on("userInput", (event, data) => {
        // Simulate some backend processing
        const processedData = `Processed: ${data.data}` + "mmmmmmmm888";

        // Send the processed data back to the renderer process
        appWindow.webContents.send("backendResponse", {
            message: processedData,
        });
    });

    ipcMain.on("addNewItem", (event, data) => {
        // Add a new item to the database
        database.insertData(data, (response) => {
            event.reply("crudResult", response);
        });
    });

    ipcMain.on("getAllItems", (event) => {
        // Retrieve all items from the database
        database.getData(null, (response) => {
            if(!response.success){
                event.reply("crudResult", response);
            } else {
                event.reply("getAllItemsx", response)
            }
        });
    });

    ipcMain.on("updateItem", (event, data) => {
        // Update an item in the database
        database.updateData(data, (response) => {
            event.reply("crudResult", response);
        });
    });

    ipcMain.on("deleteItem", (event, data) => {
        // Delete an item from the database
        database.deleteData(data.id, (response) => {
            event.reply("crudResult", response);
        });
    })
};

module.exports = {
    init
};

main.js

const { app, BrowserWindow, ipcMain } = require("electron");
const serve = require("electron-serve");
const path = require("path");
const database = require("./database");
const events = require("./events");

const appServe = app.isPackaged ? serve({
    directory: path.join(__dirname, "../out")
}) : null;

const createWindow = () => {
    const win = new BrowserWindow({
        width: 800,
        height: 600,
        webPreferences: {
            preload: path.join(__dirname, "preload.js"),
        },
    });

    if (app.isPackaged) {
        appServe(win).then(() => {
            win.loadURL("app://-");
        });
    } else {
        win.loadURL("http://localhost:3000");
        win.webContents.openDevTools();
        win.webContents.on("did-fail-load", (e, code, desc) => {
            win.webContents.reloadIgnoringCache();
        });
    }

    database.init(win);
    events.init(win);
};

app.on("ready", () => {
    createWindow();
});

app.on("window-all-closed", () => {
    if (process.platform !== "darwin") {
        app.quit();
    }
});