sqlite - How do I make a database call from an Electron front end?

Question

(Brand new learning Electron here, so I'm sure this is a basic question and I am missing something fundamental...)

How does one interact with a local database (I am using Sqlite) from an Electron application front end? I have a very basic database manager class and have no problem using it from the index.js file in my Electron application. But from the front-end (I am using Svelte, but I can probably translate solutions from other front-end frameworks), how does one interact with the database? This seems fundamental but I'm striking out trying to find a basic example.

Since everything is local it would seem it shouldn't be necessary to set up a whole API just to marshal data back and forth, but maybe it is? But if so, how does one go about telling the Electron "backend" (if that's the right term) to do something and return the results to the front end? I'm seeing something about IPC but that's not making a lot of sense right now and seems like overkill.

Here's my simple database manager class:

const sqlite3 = require("sqlite3").verbose();

class DbManager {
  #db;
  open() {
    this.#db = new sqlite3.Database("testing.db", sqlite3.OPEN_READWRITE);
  }
  close() {
    this.#db.close();
  }
  run(sql, param) {
    this.#db.run(sql, param);
    return this;
  }
}

const manager = new DbManager();
module.exports = manager;

And I can call this and do whatever no problem from the Electron entry point index.js:

const { app, BrowserWindow, screen } = require("electron");
require("electron-reload")(__dirname);
const db = require("./src/repository/db");

const createWindow = () => {
   ...
};

let window = null;

app.whenReady().then(() => {
  db.open();
  createWindow();
});

app.on("window-all-closed", () => {
  db.close();
  app.quit();
});

But what to do from my component?

<script>
  // this won't work, and I wouldn't expect it to, but not sure what the alternative is
  const db = require("./repository/db");  
  let accountName;
  function addAccount() {
    db.run("INSERT INTO accounts (name) VALUES ($name);", { $name: accountName });
  }
</script>

<main>
  <form>
    <label for="account_name">Account name</label>
    <input id="account_name" bind:value={accountName} />
    <button on:click={addAccount}>Add account</button>
  </form>
</main>

If anyone is aware of a boilerplate implementation that does something similar, that would be super helpful. Obviously this is like application 101 here; I'm just not sure how to go about this yet in Electron and would appreciate someone pointing me in the right direction.

question from:https://stackoverflow.com/questions/65851796/how-do-i-make-a-database-call-from-an-electron-front-end

Answer 1

If you're absolutely 100% sure that your app won't be accessing any remote resources then you could just expose require and whatever else you may need through the preload script, just write const nodeRequire = require; window.require = nodeRequire;.

---

This is a fairly broad topic and requires some reading. I'll try to give you a primer and link some resources.

Electron runs on two (or more if you open multiple windows) processes - the main process and the renderer process. The main process handles things like opening new windows, starting and closing the entire app, tray icons, window visibility etc., while the renderer process is basically like your JS code in a browser. More on Electron processes.

By default, the renderer process does not have access to a Node runtime, but it is possible to let it. You can do that in two ways, with many caveats.

One way is by setting webPreferences.nodeIntegration = true when creating the BrowserWindow (note: nodeIntegration is deprecated and weird. This allows you to use all Node APIs from your frontend code, and your snippet would work. But you probably shouldn't do that because a BrowserWindow is capable of loading external URLs, and any code included on those pages would be able to execute arbitrary code on your or your users' machines.

Another way is using the preload script. The preload script runs in the renderer process but has access to a Node runtime as well as the browser's window object (the Node globals get removed from the scope before the actual front end code runs unless nodeIntegration is true). You can simply set window.require = require and essentially work with Node code in your frontend files. But you probably shouldn't do that either, even if you're careful about what you're exposing, because it's very easy to still leave a hole and allow a potential attacker to leverage some exposed API into full access, as demonstrated here. More on Electron security.

So how to do this securely? Set webPreferences.contextIsolation to true. This definitively separates the preload script context from the renderer context, as opposed to the unreliable stripping of Node APIs that nodeIntegration: false causes, so you can be almost sure that no malicious code has full access to Node.

You can then expose specific function to the frontend from the preload, through contextBridge.exposeInMainWorld. For example:

contextBridge.exposeInMainWorld('Accounts', {
  addAccount: async (accountData) => {
    // validate & sanitize...
    const success = await db.run('...');
    return success;
  }
}

This securely exposes an Accounts object with the specified methods in window on the frontend. So in your component you can write:

const accountAdded = await Accounts.addAccount({id: 123, username: 'foo'});

Note that you could still expose a method runDbCommand(command) { db.run(command) }, or even evalInNode(code) { eval(code) }, which is why I wrote almost.

You don't really need to use IPC for things like working with files or databases because those APIs are available in the preload. IPC is only required if you want to manipulate windows or trigger anything else on the main process from the renderer process.