Build a Electron.js MySQL CRUD App in Browser Using HTML5 CSS3 & Javascript

Welcome folks today in this blog post we will be building the mysql crud app in electron.js using html5 css3 and javascript. All the full source code of the application is shown below.

Get Started

In order to get started you need to initialize a new electron.js project using the below command as shown below

npm init -y

This will create the package.json file inside your root directory and now we need to install the electron.js package as a dev dependency as shown below

npm i --save-dev electron

Now after that you need to add th start script inside the package.json file to start the app as shown below

Now as you can see we have added the start script. After that we will now have to start the electron.js app using the below command as shown below

npm start

This will simply start the electron.js app in the terminal.

Installing Dependencies

Now we will be installing the dependencies inside the electron.js project as shown below

npm i mysql2

npm i promise-mysql

npm i electron-reload

As you can see we have installed the mysql2 and promise-mysql libraries for interacting with the mysql database and also we are installing the electron-reload dependency for hot reloading the electron.js app.

Creating the MySQL Database

First of all you need to download the xammp control panel as shown below and after that you need to start the apache server and mysql server as well

And now we need to create the MySQL Database inside the PHPMyAdmin Section of Xammp server as shown below

We need to create the mysql database using the sql command as shown below

CREATE DATABASE electrondb;

USE electrondb;

CREATE TABLE product(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255),
  price DECIMAL(7,3) NOT NULL
);

DESCRIBE product;

CREATE DATABASE electrondb;

USE electrondb;

CREATE TABLE product(

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

description VARCHAR(255),

price DECIMAL(7,3) NOT NULL

);

DESCRIBE product;

As you can see we are creating the electrondb database and then we are creating the table product in which we have four columns id,name,description and price.

Directory Structure of Electron.js App

Now guys I will be showing you the directory structure of this electron.js crud project. Just see the different files and folders which are required for this project

As you can see we have the src folder in which we have the ui folder in which we will be building the frontend of this desktop app using html5 css3 and javascript. And then we have the backend code in which we have the database file and the index.js file which will be the actual starting point of the electron.js.

Creating the MySQL Connection in Electron.js

Now guys we will be first of all creating the file inside the root directory which will be database.js file which will be containing the javascript code which will be required for making the connection to mysql database as shown below.

database.js

const mysql = require('promise-mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'electrondb'
});

function getConnection() {
  return connection;
}

module.exports = { getConnection };

const mysql = require('promise-mysql');

const connection = mysql.createConnection({

host: 'localhost',

user: 'root',

password: '',

database: 'electrondb'

});

function getConnection() {

return connection;

}

module.exports = { getConnection };

As you can see guys we are importing the promise-mysql module at the very top and then we are creating the connection function in which we are using the createConnection() method to create the connection and then passing the object which contain four properties host,username,password and database name. And then we are simply return the connection variable inside the getConnection() method and then we are exporting this method.

Creating the Entry Point File (index.js)

Now guys we will be creating the index.js file which is the main file for starting out the electron.js desktop app so copy paste the below code

const { createWindow } = require("./main");
const { app } = require("electron");

require('./database');

require('electron-reload')(__dirname);

app.allowRendererProcessReuse = true;
app.whenReady().then(createWindow);

const { createWindow } = require("./main");

const { app } = require("electron");

require('./database');

require('electron-reload')(__dirname);

app.allowRendererProcessReuse = true;

app.whenReady().then(createWindow);

So as you can we are extracting the createWindow() method from the main.js file that we have imported at the very top. And then we are also importing the app variable from electron base library. And also we are including the database file as well that we created earlier. And then we are making the electron.js hot reload so that when we make any kind of changes it will auto reload. And then we are displaying the app when it’s ready at the last two lines.

And now we need to make the main.js file in which we will define all the crud operations and methods

main.js

Creating the Window in Electron.js

So first of all guys here in this file we will be writing a simple function which will render a simple window of variable width and height as shown below

const { BrowserWindow } = require("electron");

let window;

function createWindow() {
  window = new BrowserWindow({
    width: 800,
    height: 600,
    webPreferences: {
      nodeIntegration: true,
    },
  });

  window.loadFile("src/ui/index.html");
}

module.exports = {
  createWindow
};

const { BrowserWindow } = require("electron");

let window;

function createWindow() {

window = new BrowserWindow({

width: 800,

height: 600,

webPreferences: {

nodeIntegration: true,

});

window.loadFile("src/ui/index.html");

}

module.exports = {

createWindow

};

And as you can see in the above code we are importing the BrowserWindow method from the base electron package. And then inside this method we are initializing a new instance of BrowserWindow() to create a new window of 800 width and 600 height and also we have put the nodeIntegration to true. And then we are rendering the html file inside this desktop app using the loadFile() method.

And lastly we are exporting the method for future use in other files.

Creating the Products in MySQL Table Using POST Request

Now we will be creating the products using the post request and insert it in mysql table. Copy paste the code inside the main.js file as shown below

main.js

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

const createProduct = async (product) => {
  try {
    const conn = await getConnection();
    product.price = parseFloat(product.price);
    const result = await conn.query("INSERT INTO product SET ?", product);
    product.id = result.insertId;

    // Notify the User
    new Notification({
      title: "Electron Mysql",
      body: "New Product Saved Successfully",
    }).show();

    // Return the created Product
    return product;
  } catch (error) {
    console.log(error);
  }
};

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

const createProduct = async (product) => {

try {

const conn = await getConnection();

product.price = parseFloat(product.price);

const result = await conn.query("INSERT INTO product SET ?", product);

product.id = result.insertId;

// Notify the User

new Notification({

title: "Electron Mysql",

body: "New Product Saved Successfully",

}).show();

// Return the created Product

return product;

} catch (error) {

console.log(error);

}

};

As you can see we are importing the database.js file and then we are making the createProduct function inside that we are first of all getting the connection variable using the getConnection() method. And then we are making the SQL query to insert the product into mysql table. And then we are showing the Notification to the user in the taskbar. And lastly we are returning the created product.

Getting All the Products from the MySQL Table

Now we will be getting all the products from the mysql table. For this we need to create the method getProducts() method to the main.js file as shown below

const getProducts = async () => {
  const conn = await getConnection();
  const results = await conn.query("SELECT * FROM product ORDER BY id DESC");
  return results;
};

const getProducts = async () => {

const conn = await getConnection();

const results = await conn.query("SELECT * FROM product ORDER BY id DESC");

return results;

};

As you can see we are writing the function in which we are first of all getting the connection to database and then we are making the sql query to fetch all the products from the table in descending order and then returning all the products from this function.

Getting Specific Product Details Using it’s ID

Now we will be writing the function to get the specific product detail using it’s id. For this you need to add the below code inside the main.js file

const getProductById = async (id) => {
  const conn = await getConnection();
  const result = await conn.query("SELECT * FROM product WHERE id = ?", id);
  return result[0];
};

const getProductById = async (id) => {

const conn = await getConnection();

const result = await conn.query("SELECT * FROM product WHERE id = ?", id);

return result[0];

};

As you can see guys we are fetching the specific product details using it’s id. We are receiving the id inside the argument and then we are making the sql query to fetch the record using it’s id.

Updating the Record Using it’s ID

Now we will be updating the specific product using it’s id. We need to add the below code in the main.js file

const updateProduct = async (id, product) => {
  const conn = await getConnection();
  const result = await conn.query("UPDATE product SET ? WHERE Id = ?", [
    product,
    id,
  ]);
  console.log(result)
};

const updateProduct = async (id, product) => {

const conn = await getConnection();

const result = await conn.query("UPDATE product SET ? WHERE Id = ?", [

product,

id,

]);

console.log(result)

};

As you can see we are writing the update function in which we are receiving the id of the product as an argument and then we are making the update sql query where we are updating the product using it’s id and also updating the actual product data.

Now we will be exporting all the methods that we have created using the module.exports() method as shown below

module.exports = {
  createWindow,
  createProduct,
  getProducts,
  deleteProduct,
  getProductById,
  updateProduct
};

module.exports = {

createWindow,

createProduct,

getProducts,

deleteProduct,

getProductById,

updateProduct

};

Full Source Code of main.js file

const { BrowserWindow, Notification } = require("electron");
const { getConnection } = require("./database");

let window;

const createProduct = async (product) => {
  try {
    const conn = await getConnection();
    product.price = parseFloat(product.price);
    const result = await conn.query("INSERT INTO product SET ?", product);
    product.id = result.insertId;

    // Notify the User
    new Notification({
      title: "Electron Mysql",
      body: "New Product Saved Successfully",
    }).show();

    // Return the created Product
    return product;
  } catch (error) {
    console.log(error);
  }
};

const getProducts = async () => {
  const conn = await getConnection();
  const results = await conn.query("SELECT * FROM product ORDER BY id DESC");
  return results;
};

const deleteProduct = async (id) => {
  const conn = await getConnection();
  const result = await conn.query("DELETE FROM product WHERE id = ?", id);
  return result;
};

const getProductById = async (id) => {
  const conn = await getConnection();
  const result = await conn.query("SELECT * FROM product WHERE id = ?", id);
  return result[0];
};

const updateProduct = async (id, product) => {
  const conn = await getConnection();
  const result = await conn.query("UPDATE product SET ? WHERE Id = ?", [
    product,
    id,
  ]);
  console.log(result)
};

function createWindow() {
  window = new BrowserWindow({
    width: 800,
    height: 600,
    webPreferences: {
      nodeIntegration: true,
    },
  });

  window.loadFile("src/ui/index.html");
}

module.exports = {
  createWindow,
  createProduct,
  getProducts,
  deleteProduct,
  getProductById,
  updateProduct
};

const { BrowserWindow, Notification } = require("electron");

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

let window;

const createProduct = async (product) => {

try {

const conn = await getConnection();

product.price = parseFloat(product.price);

const result = await conn.query("INSERT INTO product SET ?", product);

product.id = result.insertId;

// Notify the User

new Notification({

title: "Electron Mysql",

body: "New Product Saved Successfully",

}).show();

// Return the created Product

return product;

} catch (error) {

console.log(error);

}

};

const getProducts = async () => {

const conn = await getConnection();

const results = await conn.query("SELECT * FROM product ORDER BY id DESC");

return results;

};

const deleteProduct = async (id) => {

const conn = await getConnection();

const result = await conn.query("DELETE FROM product WHERE id = ?", id);

return result;

};

const getProductById = async (id) => {

const conn = await getConnection();

const result = await conn.query("SELECT * FROM product WHERE id = ?", id);

return result[0];

};

const updateProduct = async (id, product) => {

const conn = await getConnection();

const result = await conn.query("UPDATE product SET ? WHERE Id = ?", [

product,

id,

]);

console.log(result)

};

function createWindow() {

window = new BrowserWindow({

width: 800,

height: 600,

webPreferences: {

nodeIntegration: true,

});

window.loadFile("src/ui/index.html");

}

module.exports = {

createWindow,

createProduct,

getProducts,

deleteProduct,

getProductById,

updateProduct

};

Making the FrontEnd of App

Now guys we will now be making a new folder called as ui inside that we will be making the user interface or frontend of this application where we will having a simple form to create the product, render all the products which are created in a table. And we also will have the update and delete buttons.

And now we need to make the index.html file inside the ui folder and copy paste the below code

ui/index.html

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Electron & MySQL</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
  <style>
    body {
  margin: 0;
  padding: 0;
}

#products {
  height: 100vh;
  overflow-y: auto;
  padding: 2px
}
  </style>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.7.2/animate.min.css">

</head>

<body>

  <main class="container-fluid">

    <div class="row">

      <div class="col-md-6 p-4 my-auto">

        <!-- Product Form -->
        <form id="productForm" class="card card-body">
          <div class="form-group">
            <input type="text" id="name" placeholder="Product's Name" class="form-control" autofocus>
          </div>
          <div class="form-group">
            <input type="number" id="price" placeholder="Product's Price" step="any" class="form-control">
          </div>
          <div class="form-group">
            <textarea id="description" rows="3" placeholder="Product's Description" class="form-control"></textarea>
          </div>
          <button type="submit" class="btn btn-primary">
            Save
          </button>
        </form>

      </div>

      <!-- Product List -->
      <div class="col-md-6">
        <div id="products"></div>
      </div>
    </div>

  </main>

  <script src="./app.js"></script>

</body>

</html>

<!DOCTYPE html>

<head>

<title>Electron & MySQL</title>

<style>

body {

margin: 0;

padding: 0;

}

#products {

height: 100vh;

overflow-y: auto;

padding: 2px

}

</style>

</head>

<body>

</div>

</div>

</div>

Save

</button>

</form>

</div>

</div>

</main>

</body>

</html>

As you can see we are including the bootstrap cdn also for styling the interface and also we are also using animate.css cdn for animation as well and then we are including the custom css as well. And now also at the bottom we are including the app.js file where we will be writing all the javascript code which is required for this app

As you can see we have the three input fields inside the form which is for the product’s name,price and the description. And then we also have the button to save the product also.

Now inside the ui folder you need to make the app.js file as shown below

ui/app.js

First of all we will be getting all the references of all the DOM Elements now inside this below code

const { remote } = require("electron");
const main = remote.require("./main");

const productForm = document.querySelector("#productForm");
const productName = document.querySelector("#name");
const productPrice = document.querySelector("#price");
const productDescription = document.querySelector("#description");
const productsList = document.querySelector("#products");

let products = [];
let editingStatus = false;
let editProductId;

const { remote } = require("electron");

const main = remote.require("./main");

const productForm = document.querySelector("#productForm");

const productName = document.querySelector("#name");

const productPrice = document.querySelector("#price");

const productDescription = document.querySelector("#description");

const productsList = document.querySelector("#products");

let products = [];

let editingStatus = false;

let editProductId;

As you can see we are importing the required libraries of electron and then we are also requiring the main.js file as well. And then we will be making the products array and then we are making the boolean parameter of editingStatus.

Now guys we will be calling the method where we will be showing all the products inside the mysql table. For this we need to copy paste the below code inside app.js file

const getProducts = async () => {
  products = await main.getProducts();
  renderProducts(products);
};

async function init() {
  getProducts();
}

init();

const getProducts = async () => {

products = await main.getProducts();

renderProducts(products);

};

async function init() {

getProducts();

}

init();

As you can see we are calling the init() method inside it we are calling the getProducts() method to render all the products inside the electron desktop App. Now we need to define the renderProducts() method and here we are passing the products array variable.

function renderProducts(tasks) {
  productsList.innerHTML = "";
  tasks.forEach((t) => {
    productsList.innerHTML += `
      <div class="card card-body my-2 animated fadeInLeft">
        <h4>${t.name}</h4>
        <p>${t.description}</p>
        <h3>${t.price}$</h3>
        <p>
        <button class="btn btn-danger btn-sm" onclick="deleteProduct('${t.id}')">
          DELETE
        </button>
        <button class="btn btn-secondary btn-sm" onclick="editProduct('${t.id}')">
          EDIT 
        </button>
        </p>
      </div>
    `;
  });
}

function renderProducts(tasks) {

productsList.innerHTML = "";

tasks.forEach((t) => {

productsList.innerHTML += `

<p>${t.description}</p>

<h3>${t.price}$</h3>

<p>

DELETE

</button>

EDIT

</button>

</p>

</div>

});

}

As you can see in this function we are taking the tasks array. And inside it we are using the forEach loop and then we are rendering all the products in the bootstrap table. And then we are displaying the name,price and the description of the product and then we have the delete and edit buttons.

As you can see we are rendering all the products in the bootstrap card. We have two buttons edit and delete.

Submitting the HTML5 Form to Create,Edit & Delete Products

Now guys we will be submitting the html5 form by attaching the form submit event listener and inside that we are fetching the values from the input fields and then we are creating the products object. And then we are checking for the boolean parameter for the edit and create operations. If it’s value is false then we will be creating the product and else we are updating the product. And lastly we are resetting the html5 form values and then we are again rendering the newly created product.

Editing the Product Using it’s ID

Now we will be editing the product using it’s id in the argument as shown below

const editProduct = async (id) => {
  const product = await main.getProductById(id);
  productName.value = product.name;
  productPrice.value = product.price;
  productDescription.value = product.description;

  editingStatus = true;
  editProductId = id;
};

const editProduct = async (id) => {

const product = await main.getProductById(id);

productName.value = product.name;

productPrice.value = product.price;

productDescription.value = product.description;

editingStatus = true;

editProductId = id;

};

As you can see we are fetching the details of the product using it’s id. And then we are updating the product as shown above

Deleting the Product Using it’s ID

const deleteProduct = async (id) => {
  const response = confirm("Are you sure you want to delete it?");
  if (response) {
    await main.deleteProduct(id);
    await getProducts();
  }
  return;
};

const deleteProduct = async (id) => {

const response = confirm("Are you sure you want to delete it?");

if (response) {

await main.deleteProduct(id);

await getProducts();

}

return;

};

As you can see we are deleting the product using it’s id. And then after deletion we are rendering the updated list of products.

Full Source Code of app.js

app.js

const { remote } = require("electron");
const main = remote.require("./main");

const productForm = document.querySelector("#productForm");
const productName = document.querySelector("#name");
const productPrice = document.querySelector("#price");
const productDescription = document.querySelector("#description");
const productsList = document.querySelector("#products");

let products = [];
let editingStatus = false;
let editProductId;

const deleteProduct = async (id) => {
  const response = confirm("Are you sure you want to delete it?");
  if (response) {
    await main.deleteProduct(id);
    await getProducts();
  }
  return;
};

const editProduct = async (id) => {
  const product = await main.getProductById(id);
  productName.value = product.name;
  productPrice.value = product.price;
  productDescription.value = product.description;

  editingStatus = true;
  editProductId = id;
};

productForm.addEventListener("submit", async (e) => {
  try {
    e.preventDefault();

    const product = {
      name: productName.value,
      price: productPrice.value,
      description: productDescription.value,
    };

    if (!editingStatus) {
      const savedProduct = await main.createProduct(product);
      console.log(savedProduct);
    } else {
      const productUpdated = await main.updateProduct(editProductId, product);
      console.log(productUpdated);

      // Reset
      editingStatus = false;
      editProductId = "";
    }

    productForm.reset();
    productName.focus();
    getProducts();
  } catch (error) {
    console.log(error);
  }
});

function renderProducts(tasks) {
  productsList.innerHTML = "";
  tasks.forEach((t) => {
    productsList.innerHTML += `
      <div class="card card-body my-2 animated fadeInLeft">
        <h4>${t.name}</h4>
        <p>${t.description}</p>
        <h3>${t.price}$</h3>
        <p>
        <button class="btn btn-danger btn-sm" onclick="deleteProduct('${t.id}')">
          DELETE
        </button>
        <button class="btn btn-secondary btn-sm" onclick="editProduct('${t.id}')">
          EDIT 
        </button>
        </p>
      </div>
    `;
  });
}

const getProducts = async () => {
  products = await main.getProducts();
  renderProducts(products);
};

async function init() {
  getProducts();
}

init();

const { remote } = require("electron");

const main = remote.require("./main");

const productForm = document.querySelector("#productForm");

const productName = document.querySelector("#name");

const productPrice = document.querySelector("#price");

const productDescription = document.querySelector("#description");

const productsList = document.querySelector("#products");

let products = [];

let editingStatus = false;

let editProductId;

const deleteProduct = async (id) => {

const response = confirm("Are you sure you want to delete it?");

if (response) {

await main.deleteProduct(id);

await getProducts();

}

return;

};

const editProduct = async (id) => {

const product = await main.getProductById(id);

productName.value = product.name;

productPrice.value = product.price;

productDescription.value = product.description;

editingStatus = true;

editProductId = id;

};

productForm.addEventListener("submit", async (e) => {

try {

e.preventDefault();

const product = {

name: productName.value,

price: productPrice.value,

description: productDescription.value,

};

if (!editingStatus) {

const savedProduct = await main.createProduct(product);

console.log(savedProduct);

} else {

const productUpdated = await main.updateProduct(editProductId, product);

console.log(productUpdated);

// Reset

editingStatus = false;

editProductId = "";

}

productForm.reset();

productName.focus();

getProducts();

} catch (error) {

console.log(error);

}

});

function renderProducts(tasks) {

productsList.innerHTML = "";

tasks.forEach((t) => {

productsList.innerHTML += `

<p>${t.description}</p>

<h3>${t.price}$</h3>

<p>

DELETE

</button>

EDIT

</button>

</p>

</div>

});

}

const getProducts = async () => {

products = await main.getProducts();

renderProducts(products);

};

async function init() {

getProducts();

}

init();