How to Use SQLite Electron Module

1. What Is SQLite Electron
2. How to Use SQLite3 Module With Electron
3. How to Use SQLite Electron

SQLite is an open-source C-language library built to execute a fast, reliable, small, self-contained, full-featured SQL database engine.

It is the database engine used most often in the world; it comes in built-in mobile phones, computers, and countless other applications people use in their everyday lives.

Electron SQLite is a module that allows the electron to use the SQLite3 database without the need to rebuild. Currently, it supports Linux (x64) and Windows (x32, x64).

What Is SQLite Electron

SQLite Electron is essentially a module that can be installed. You can use the package manager npm to install SQLite Electron.

This can be done in the following manner:

npm install SQLite-electron

This package helps install the prebuilt binaries of the SQLite on your system as long as your system is supported. It has several built-in functions allowing you to navigate your problems towards a solution.

How to Use SQLite3 Module With Electron

Suppose a task requires you to develop an application using electron that uses the SQLite3 package. In that case, we’ll help you accomplish that task.

An easy way to use SQLite with the electron is with an electron-builder. The first step towards achieving this is to add a post-install step in your package.json.

This is done in the following way:

"scripts": {
   "postinstall": "install-app-deps"
   ...
}

Once you’re done, install the necessary dependencies and build.

npm install --save-dev electron-builder
npm install --save sqlite3
npm run postinstall

After you’re done with the steps mentioned above, the electron-builder will build the native module for your platform with the right name for the Electron binding.

You can require it in code later, as expected. Add the "/m" switch to enable parallel building.

Two aspects need to be always considered:

1. You need to ensure electron knows where to find your modules. This happens by setting NODE_PATH.

   Electron is unable to set NODE_PATH environmental variables automatically. You must do so manually by setting it to all the paths containing your desired modules.

2. Compile native modules against electron headers.

Functions of Electron SQLite

The SQLite electron package has several functions to help you achieve your purpose more efficiently.

1. dbPath: This is a variable tasked to set your path for the database. If the database already exists, it connects to the database.

2. executeQuery(Query =" " fetch =" ", values =[] ): This function is tasked to execute a single query with fetch and values.

   However, it is crucial to know that the fetch must be in a datatype string, for example, 'all', '1', '2', etc. All the values must be in the array.

3. executeMany( Query=" ", values = [] ): This function is tasked to execute a single query using multiple values.

4. executeScript( scriptName =" ): This function is tasked to execute the SQL script. Make sure scriptName is the name of the script.

How to Use SQLite Electron

While using an electron, the SQLite Electron should only be necessary for the main process. Take the following code as an example.

const {app, BrowserWindow} = require('electron')
const sqlite = require('sqlite-electron')

function createWindow() {
  // Add your code here
}
app.whenReady().then(
    () => {
        // Add your code here
    })

app.on(
    'window-all-closed',
    () => {
        // Add your code here
    })

dbPath

As we mentioned before, dbPath is a variable that is exposed and tasked with setting the path of the new database and connecting to an existing database. It would be best to fix this variable before using any APIs.

Do this in the following manner:

const {app, BrowserWindow, ipcMain} = require('electron')
const SQLite = require('SQLite-electron')

function createWindow() {
  // Your Code
}
app.whenReady().then(
    () => {
        // Your Code
    })

app.on(
    'window-all-closed',
    () => {
        // Your Code
    })

ipcMain.handle('databasePath', (event, dbPath) => {
  SQLite.dbPath = dbPath
  return true
})

executeQuery

The executeQuery function is responsible for executing any single query. For example:

SELECT * FROM main_sqlite  WHERE..

Here, you can pass values through the value array and call the function to fetch data by specifying specific fetch parameters, e.g., "all", 1, 2, 3, 4, …infinity.

It is essential to know that values should never be given in the query string. Use the value array to provide values for the query.

Avoiding this step may cause SQL injection attacks. For example:

("INSERT INTO main_sqlite (NAME, DOB, AGE, MAIN_ADDRESS ) VALUES ( ?,?,?,?);"
, ["name", 4/08.2001, 20, "sample address"])

You can use this function in the following manner:

const {app, BrowserWindow, ipcMain} = require('electron')
const sqlite = require('sqlite-electron')

function createWindow() {
  // Add your code
}
app.whenReady().then(
    () => {
        // Add your code
    })

app.on(
    'window-all-closed',
    () => {
        // Add your code
    })

ipcMain.handle('databasePath', (event, dbPath) => {
  SQLite.dbPath = dbPath
  return true
})

  ipcMain.handle(
      'executeQuery', async (event_name, query_name, fetch_name, val) => {
        return await sqlite.executeQuery(query_name, fetch_name, val);
      })

executeMany

This function is used for performing a query that uses multiple values.

For example:

("INSERT INTO main_sqlite (NAME, DOB, AGE, MAIN_ADDRESS ) VALUES ( ?,?,?,?);"
, ["name", 4/08.2001, 20, "sample address"], ["name2", 4/09.2000, 21, "sample address2"])

In executeMany, function fetch is not available.

The function can be used in the following manner:

const {app, BrowserWindow, ipcMain} = require('electron')
const SQLite = require('SQLite-electron')

function createWindow() {
  // Add your Code
}
app.whenReady().then(
    () => {
        // Add your Code
    })

app.on(
    'window-all-closed',
    () => {
        // Add your Code
    })

ipcMain.handle('databasePath', (event, dbPath) => {
  SQLite.dbPath = dbPath
  return true
})

  ipcMain.handle(
      'executeMany',
      async (event_name, query_name, val) => {
          return await sqlite.executeMany(query_name, val)})

executeScript

This function executes multiple queries while using SQL scripts.

This is done in the following manner:

const {app, BrowserWindow, ipcMain} = require('electron')
const SQLite = require('SQLite-electron')

function createWindow() {
  // Add your Code
}
app.whenReady().then(
    () => {
        // Add your Code
    })

app.on(
    'window-all-closed',
    () => {
        // Add your Code
    })

ipcMain.handle('databasePath', (event_name, dbPath) => {
  SQLite.dbPath = dbPath
  return true
})

  ipcMain.handle('executeScript', async (event_name, pathOfScript) => {
    return await sqlite.executeScript(pathOfScript);
    // or
    return await sqlite.executeScript(
        'CREATE TABLE IF NOT EXISTS main_sqlite (USERID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,FULLNAME TEXT NOT NULL,FULLADDRESS CHAR(50) NOT NULL);');
  })

If you need to use electron SQLite, we hope this article helped you navigate its installation, functions, and usage.