Binding Smart.Grid with MySQL React JS

NodeJS Server

First, open a terminal in the server folder and run npm init --y. This will create a project and allows us to install packages.

Since we have a project, we need create the main file server.js and install express and nodemon.

Express, to create easily a server and nodemon to have a hot reload.

Our server.js will hold the main logic of the application.

To make request to our API we need to setup the cors policy. This can be done with the cors package. Run npm install cors to install it.

To have the server running we will write the following:

const express = require('express');

const app = express();
const port = 3001;

app.use(require('cors')())
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.listen(port, () => {

    console.log(`App is listening on port: ${port}`);

});

Now our server runs but it does nothing.

To connect it with MySQL we must download the mysql package

After installing it we will create a src folder in which we will have our router and a module for communicating with the database.

Create a file in the src called database.js and modify the connection's configuration.

const mysql = require('mysql');

let connection;

exports.createConnection = (autoConnect = true) => new Promise((resolve, reject) => {

    try {

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

        if (autoConnect) {
            this.connect()
                .then(() => resolve(connection))
                .catch(console.log)
        } else {
            resolve(connection);
        }


    } catch (error) {

        reject(error.message);

    }
})

exports.connect = () => new Promise((resolve, reject) => {

    connection.connect((error) => {

        if (error) {
            reject(error.message);
        }

        resolve(true);

    });

});

exports.query = (query, values) => {

    return new Promise((resolve, reject) => {

        connection.query(query, values, function (error, results) {

            if (error) {
                reject(error.message);
            }

            resolve(results);

        });

    });

}

After creating our database abstraction, we are ready to make our routes. To do this, create a file called router.js

const router = require('express').Router();

const { query } = require('./database');

router.get('/products', (req, res) => {

    query('SELECT * FROM products')
        .then(results => res.json(results))
        .catch((err) => {
            console.log(err);
            res.json([])
        })

})

router.post('/products', (req, res) => {

    const { name, type, calories, fat, protein } = req.body;

    query(
        `
        INSERT INTO products (name, type, calories, fat, protein)
        VALUES ('${name}', type = '${type}', calories = ${calories}, fat = ${fat}, protein = ${protein});
        `
    )
        .then((result) => res.json(result))
        .catch((err) => res.status(400).end())

})

router.put('/products', (req, res) => {

    const { id, name, type, calories, fat, protein } = req.body;

    query(
        `
        UPDATE products
        SET name = '${name}', type = '${type}', calories = ${calories}, fat = ${fat}, protein = ${protein}
        WHERE id = ${id}
        `
    )
        .then(() => res.json(req.body))
        .catch(() => res.status(400).end())

})

router.delete('/products/:id', (req, res) => {

    query(
        `
        DELETE FROM products
        WHERE id = ${req.params.id}
        `
    )
        .then(() => res.send(true))
        .catch((err) => {
            console.log(err);
            res.status(400).send(false)
        })

})

module.exports = router

It is time to combine our modules in the server.js

const express = require('express');

const app = express();
const port = 3001;

app.use(require('cors')())
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.use(require('./src/router'));

const { createConnection } = require('./src/database');

createConnection()
    .then(() => {
        app.listen(port, () => {

            console.log(`App is listening on port: ${port}`);

        });
    })
    .catch(console.log)

Our last step is to configurate our package.json

{
    "name": "server",
    "version": "1.0.0",
    "description": "",
    "main": "server.js",
    "scripts": {
        "nodemon": "nodemon server.js",
        "start": "node server.js",
        "test": "echo \"Error: no test specified\" && exit 1"
    },
    "keywords": [],
    "author": "",
    "license": "ISC",
    "dependencies": {
        "cors": "^2.8.5",
        "express": "^4.18.2",
        "mysql": "^2.18.1",
        "nodemon": "^2.0.20"
    }
}

Now when we run npm run nodemon we will have a hot reload

Creating our React client

Now we have a bridge between MySQL and React, it is time to initialize our Smart.Grid

The first thing to do is to open a terminal in the client folder and run npx create-react-app smart-app

After creating the React project run cd smart-app to navigate to the project

To install SmartUI run npm install smart-webcomponents-react

You can run npm start to see the live results

The first thing to do is to remove the CSS from App.css and replace it with this:

.crud-buttons {
    display: flex;
    gap: 20px;
    margin: 20px 0;
}

In the App.js you have to import SmartUI's CSS with the following import:

We will create a few methods that will help us communicating with our REST API. To do that create a file called productService.js and paste the following

export const getAll = () => fetch('http://localhost:3001/products')
    .then(res => res.json())

export const add = (product) => fetch('http://localhost:3001/products', {
    method: 'POST',
    headers: {
        "Content-Type": 'application/json'
    },
    body: JSON.stringify(product)
})
    .then(res => res.json())

export const edit = (product) => fetch('http://localhost:3001/products', {
    method: 'PUT',
    headers: {
        "Content-Type": 'application/json'
    },
    body: JSON.stringify(product)
})
    .then(res => res.json())

export const remove = (id) => fetch(`http://localhost:3001/products/${id}`, {
    method: 'DELETE'
})
    .then(res => res.json())

Now it is time to implement our Grid with the needed buttons.

Place this in App.js

import './App.css';
import 'smart-webcomponents-react/source/styles/smart.default.css';

import { useEffect, useState, useRef } from 'react';

import Button from 'smart-webcomponents-react/button';
import Grid from 'smart-webcomponents-react/grid';
import { add, edit, remove, getAll } from './productService';

function App() {

  const [productsData, setProductsData] = useState([]);

  const gridRef = useRef(null);

  useEffect(() => {

    getAll()
      .then(products => setProductsData(products))
      .catch(console.log)

  }, [])

  const dataSource = productsData;
   
  const dataSourceSettings = {  
    dataFields: [
      'id: number',
      'name: string',
      'type: string',
      'calories: number',
      'fat: number',
      'protein: number'
    ],
    id: 'id'
  };

  const columns = [
    {
      label: 'Name',
      dataField: 'name'
    },
    {
      label: 'Type',
      dataField: 'type'
    },
    {
      label: 'Calories',
      dataField: 'calories'
    },
    {
      label: 'Fat',
      dataField: 'fat'
    },
    {
      label: 'Protein',
      dataField: 'protein'
    }
  ];

  const selection = {
    enabled: true,
    mode: 'one',
    checkBoxes: {
      enabled: true
    }
  };

  const editing = {
    enabled: true,
    mode: 'row',
    action: 'doubleClick'
  };

  const handleAddRow = () => {
    gridRef.current.addNewRow('near');
  }

  const handleRemoveSelected = async () => {

    const selectedRows = gridRef.current.getSelectedRows();
    
    if (selectedRows.length === 0 || !selectedRows[0][1].id) {
      return;
    }

    const row = selectedRows[0][1];

    try {

      await remove(row.id);

      const products = await getAll();
      setProductsData(products);

    } catch (error) { console.log(error); }

  }

  const handleEdit = async (e) => {

    if (!e.detail.id) {
      return;
    }

    if ((!e.detail.data.name && !e.detail.data.type)) {
      return gridRef.current.deleteRow(e.detail.id);
    }
    
    const product = {
      id: e.detail.data.id,
      name: e.detail.data.name,
      type: e.detail.data.type,
      calories: e.detail.data.calories,
      fat: e.detail.data.fat,
      protein: e.detail.data.protein,
    }

    if (!product.id) {

      delete product.id;

      try {

        await add(product);

        const products = await getAll();
        setProductsData(products);

      } catch (error) { console.log(error) }


    } else {

      try {
        await edit(product)
      } catch (error) { console.log(error) }

    }

  }

    return (
    <>
        <div className='crud-buttons'>
        <Button onClick={handleAddRow}>Add new row</Button>
        <Button onClick={handleRemoveSelected}>Remove selected row</Button>
        </div>
        <div>
        <Grid
            ref={gridRef}
            dataSource={dataSource}
			dataSourceSettings={dataSourceSettings}
            columns={columns}
            selection={selection}
            editing={editing}
            onEndEdit={handleEdit}
        >
        </Grid>
        </div>
    </>
    );
}

export default App;

Everything is ready and you data will persist after every modification that you make.