A Step-by-Step Guide to Integrating Better-SQLite3 with Electron JS App Using Create-React-App
Tags: electron, react, sqlite, tutorial
Published: 2023-08-08 18:46:26

*Are you trying to build your own Electron JS application with React? Do you also want to store and manage data efficiently with SQLite? In this step-by-step guide, I will show you how to integrate Better-SQLite with your Electron JS app, using Create-React-App. This is what we will build.*



![final ui](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/te8uhhj0inf3uhj2uxad.jpg)

## **1️⃣ Introduction**

Before we jump into the technical stuff, let's quickly cover what we aim to achieve. Our goal is to integrate [Better-SQLite3](https://www.npmjs.com/package/better-sqlite3) (The fastest and simplest library for SQLite3 in Node.js.) into an Electron JS application built with Create-React-App. This will allow us to persist data in our user's system only.

![meme](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/olhxk44dszlrlg8ksl1y.png)

## **2️⃣ Setup and Initialization**

I hope you know how to set up Electron project with create-react-app. If you don't, you can [follow this article](https://dev.to/mandiwise/electron-apps-made-easy-with-create-react-app-and-electron-forge-560e) to get started. (Sorry for being lazy 😛)

Now we need to initialize the database. To do this, we will create a new file first named `demo_table.db`. This will be the file that stores the database data. Then using [DBeaver](https://dbeaver.io/download/), we will create a table in this database called `person`.


![gif](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d82sgnv1gcetqoiduv85.gif)

Note: Tables and DB files can be created using better-sqlite3 as well. I did it this way to make the setup easier.

Next, you need to install better-sqlite3.
We also need to install [electron-rebuild.](https://www.npmjs.com/package/electron-rebuild) This is required to run better-sqlite3 if you're facing any issues where the node versions don't match. This is taken from their docs:

> This executable rebuilds native Node.js modules against the version of Node.js that your Electron project is using. This allows you to use native Node.js modules in Electron apps without your system version of Node.js matching exactly (which is often not the case, and sometimes not even possible).

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

After the installation is completed, add this to your package.json

```json
{	
	...
	"scripts": {
        ...				
        "rebuild": "electron-rebuild -f -w better-sqlite3",
	}
}
```

And then do `npm run rebuild` to rebuild better-sqlite3 for your system.


## **3️⃣ Setting up DBManager.js**

In `public` folder, we will create a folder called `Database` which will contain all the files related to the database. Let's create a `DBManager.js` file :

```javascript
const Database = require("better-sqlite3")
const path = require("path")

const dbPath =
	process.env.NODE_ENV === "development"
		? "./demo_table.db"
		: path.join(process.resourcesPath, "./demo_table.db")

const db = new Database(dbPath)
db.pragma("journal_mode = WAL")

exports.db = db
```

For production, the `demo_table.db` file will be kept in a folder called `resources`. We can get the path of this folder using `process.resourcesPath`.  
The db file is an extra resource that we need to mention in our package.json, so that when the build happens, `demo_table.db` is moved to `resources` folder. This configuration is given for build done using [electron-builder](https://www.electron.build/index.html).

```json
{
	"name": "electron-sqlite",
	...
	"scripts": {
        ...		
		"package-windows": "rm -rf build && rm -rf dist && npm run build 
       && electron-builder -w -c.extraMetadata.main=build/electron.js",
	},
	"build": {
		"appId": "sqlite.electron-test.app",
		"productName": "SQLite Electron",
		"files": [
			"build/**/*",
			"node_modules/**/*",
			"public/**/*"
		],
		"directories": {
			"buildResources": "public"
		},
		"extraResources": [
			"./demo_table.db"
		]
	}
}
```

## **4️⃣ Create a Manager file to manage a Table instance**

Let's create a file called **PersonManager.js** to invoke methods related to `Person` table.

We will create a method to read all the data from the **Person** table.

For inserting data, we will use a transaction. So that if an exception is thrown, the transaction will be rolled back (and the exception will propagate as usual).

```javascript
const dbmgr = require("./dbManager")
const db = dbmgr.db

const readAllPerson = () => {
	try {
		const query = `SELECT * FROM person`
		const readQuery = db.prepare(query)
		const rowList = readQuery.all()
		return rowList
	} catch (err) {
		console.error(err)
		throw err
	}
}

const insertPerson = (name, age) => {
	try {
		const insertQuery = db.prepare(
			`INSERT INTO person (name, age) VALUES ('${name}' , ${age})`
		)

		const transaction = db.transaction(() => {
			const info = insertQuery.run()
			console.log(
				`Inserted ${info.changes} rows with last ID 
                 ${info.lastInsertRowid} into person`
			)
		})
		transaction()
	} catch (err) {
		console.error(err)
		throw err
	}
}

module.exports = {
	readAllPerson,
	insertPerson,
}
```

## **5️⃣ Creating a Bridge with Preload.js**

In Electron, security is essential, so we use the `contextBridge` in `Preload.js` to expose our `PersonManager` functions to the renderer process securely. This prevents any unauthorized access and keeps our data safe.

```javascript
const { contextBridge } = require("electron")
const personDB = require("./Database/PersonManager")

contextBridge.exposeInMainWorld("sqlite", {
  personDB,
})
```

## **6️⃣ Fetching/Inserting Data in React**

Time to get React involved! In your React component, you can create a `fetchData()` function to fetch all the **Person** data from the database. You also don't have to import anything as well!

```javascript
const fetchData = useCallback(() => {
	const data = window.sqlite.personDB?.readAllPerson()
	setData(data)
}, [])
```

To insert data, we can use the `insertPerson( )` function defined above.

```javascript
const name = e.target.name?.value
const age = e.target.age?.value
if (!name || !age) return

window.sqlite.personDB?.insertPerson(name, parseInt(age))
fetchData() // Fetch updated data, and set it to the UI
```

## Possible Errors

* If you're getting any errors in your console due to SQLite, check all the other errors that are being shown. Many a time, the other errors contain the exact information which you need to resolve the issue (talking from personal experience 😥).
    
* Check whether Electron can read the DB file properly *and* whether the path to the DB file is proper or not.
    
* Temporary files like **demo\_table.db-shm,** **demo\_table.db-wal** gets created when running the application. Sometimes, the creation of new files makes React refresh, and this turns into an infinite refresh of the page. To stop this, you have to disable hot-reload, using an ignore path. Or you can keep the db file outside of the root directory like I did for a private project.


    

## **Build and Final Touch**

Now, you can add new persons from the UI, then refresh or even close the application. Next time when you open it, everything should be present there.


![meme](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lbyozcp2qrmbap8g1ql2.png)

To build it for Windows, you can run `npm run package-windows`. `dist` folder will contain the executable file through which you can install it on any machine. It also generates a portable exe file, present inside `win-unpacked` which can be run without installation.

For Linux and Mac, you can add this to your scripts in package.json.

```json
"package-mac": "rm -rf build && rm -rf dist && npm run build && 
   electron-builder build  -m -c.extraMetadata.main=build/electron.js ",
"package-linux": "rm -rf build && rm -rf dist && npm run build && 
   electron-builder -l -c.extraMetadata.main=build/electron.js"
```

P.S. I haven't tested for Linux and Mac, since I don't have one. If any changes are required, do let me know.

You can find the [Github repo here.](https://github.com/arindam1997007/electron-betterSQLite)

Congratulations, you've made it to the end of this guide! Integrating Better-SQLite with your Electron JS app using Create-React-App might have seemed daunting at first, but you did it like a champ! You can now confidently store and manage data using SQLite in your Electron JS app.

References:

* [https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md](https://github.com/WiseLibs/better-sqlite3/blob/HEAD/docs/api.md)
    
* [https://www.youtube.com/watch?v=nMvjcBTFlPA](https://www.youtube.com/watch?v=nMvjcBTFlPA)