Tutorials‎ > ‎

Creating Simple CRUD MySQL, ExpressJS, and ReactJS

posted Nov 1, 2016, 7:21 PM by Andi Muqsith Ashari   [ updated Dec 5, 2016, 6:55 PM by Surya Wang ]

Introduction

In this tutorial we’re going to build a simple CRUD (Create, Read, Update, and Delete) web application using ExpressJS, React, and MySQL as database.

Preparing the Database

Create database named ‘tutorial’ and create table named ‘products’ inside the database, here is the table details:

Column Name

Data Type

Length

Note

id

int

 

Auto Increment, Primary Key

name

varchar

50

 

price

Int

 

 

created_at

timestamp

 

Default : CURRENT_TIMESTAMP

 

Creating New ExpressJS Project

We need Express Generator to build our Express empty project, type this command in your command line to install Express Generator globally to your system:

npm install express-generator -g

After installing Express Generator, type this command to generate new empty project:
express tutorial

Change the “tutorial” to your project name, ExpressJS will generate a default project structure as shown below: 
 
Then install the dependencies by running this command:
npm install

After installing the dependencies your project is ready to run by running this command:

npm start

If you seeing this command result on your command line, then it’s mean your project is already running at : http://localhost:3000


Creating a Simple CRUD Web Service Using MySQL

We need to install mysql dependency to connect to mysql database, use this command to install mysql dependency:

npm install mysql --save

After install mysql dependency, import and create pool to mysql database by adding this lines of code into app.js:

var mysql = require('mysql');

//connect to mysql don't use var tomake it global

pool = mysql.createPool({

    host: 'localhost',

    user: 'root',

    password: '',

    database: 'tutorial'

});

Change the host, user, password, and database according to your mysql database environment, after creating the pool to mysql database, let’s create file named ‘products.js’ inside routes directory, we are going to create web service with details:

Method

Action

Description

GET

/products

Get list of products

POST

/products

Insert new product data

GET

/products/:id

Get product based on id

PUT

/products/:id

Update product based on id

DELETE

/products/:id

Delete product based on id

Inside the products.js file add this following code:

var express = require('express');

var router = express.Router();


//get list of product

router.get('/', function(req, res, next) {

    pool.getConnection(function(err, connection) {

        connection.query("SELECT * FROM products", function(err, rows) {

            if (!err && rows.length > 0) {

                res.json(rows);

            } else {

                res.json([]);

            }

        });

    });

});


//get product by id

router.get('/:id', function(req, res, next) {

    pool.getConnection(function(err, connection) {

        var id = req.params.id;

        connection.query("SELECT * FROM products WHERE id='" + id + "' LIMIT 1", function(err, rows) {

            if (!err && rows.length > 0) {

                res.json(rows[0]);

            } else {

                res.json([]);

            }

        });

    });

});


//add new product

router.post('/', function(req, res, next) {

    pool.getConnection(function(err, connection) {

        var postBody = req.body;

        var productName = postBody.name;

        var productPrice = postBody.price;

        connection.query("INSERT INTO products (name, price) VALUES ('" + productName + "','" + productPrice + "')", function(err, rows) {

            if (rows.affectedRows) {

                connection.query("SELECT * FROM products WHERE id='" + rows.insertId + "' LIMIT 1", function(err, rows) {

                    if (!err && rows.length > 0) {

                        res.json(rows[0]);

                    } else {

                        res.json([]);

                    }

                });

            }

        });

    });

});


//delete product

router.delete('/:id', function(req, res, next) {

    pool.getConnection(function(err, connection) {

        var id = req.params.id;

        connection.query("DELETE FROM products WHERE id='" + id + "'", function(err, rows) {

            if (!err) {

                res.json({

                    "status": true

                });

            } else {

                res.json([]);

            }

        });

    });

});


//update product

router.put('/:id', function(req, res, next) {

    pool.getConnection(function(err, connection) {

        var postBody = req.body;

        var productId = req.params.id;

        var productName = postBody.name;

        var productPrice = postBody.price;

        connection.query("UPDATE products SET name='" + productName + "', price='" + productPrice + "' WHERE id='" + productId + "'", function(err, rows) {

            if (rows.affectedRows) {

                connection.query("SELECT * FROM products WHERE id='" + productId + "' LIMIT 1", function(err, rows) {

                    if (!err && rows.length > 0) {

                        res.json(rows[0]);

                    } else {

                        res.json([]);

                    }

                });

            }

        });

    });

});


module.exports = router;

Our product service is ready to use, we need to import the products.js into app.js by adding this lines of code in app.js file:

//import the products.js

var products = require('./routes/products');

//add products to ExpressJS route

app.use('/products', products);

You can check your web service using 3rd party application like postman to make sure your web service is running well, don’t forget to restart your project by stopping the project using ctrl+c and run the project again using :

npm start

Creating the Front End Using ReactJS

Our web service is ready to use, next thing to do is installing the ReactJS into your project, we need to use Webpack and Babel dependencies to run ReactJS, use command below to install the dependencies to our project:

npm install webpack –save

npm i babel-loader babel-core babel-preset-es2015 babel-preset-react –save

npm i react react-dom –save

We need to use React Roter to route our React application and Axios to make a http request in our project:

npm install react-router –save

npm install axios --save

After all things already installed lets modify index.jade as project index page, this page will import jQuery, bootstrap, toastr, and bundle.js (application file generated by babel):

doctype html

html

  head

    link(rel="stylesheet",href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css")

    link(rel="stylesheet",href="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/css/toastr.min.css")

    script(src="//ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js")

    script(src="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/js/toastr.min.js")

    script(src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js")

  body

    #app

    script(src="javascripts/bundle.js")

We need to add webpack.config.js as a webpack configuration file in our root directory:

var webpack = require('webpack');

var path = require('path');


var BUILD_DIR = path.resolve(__dirname, 'public');

var APP_DIR = path.resolve(__dirname, 'app');


var config = {

    entry: APP_DIR + '/index.jsx',

    output: {

        path: BUILD_DIR + '/javascripts',

        filename: 'bundle.js'

    },

    module: {

        loaders: [{

            test: /\.jsx?/,

            include: APP_DIR,

            loader: 'babel'

        }]

    }

}


module.exports = config;

Create directory named app inside our root directory, create index.jsx file inside the app directory:

import React from 'react';

import { render } from 'react-dom';

import { Router, Route, Link, hashHistory } from 'react-router'


import HomeComponent from './components/home/HomeComponent.jsx';

import ProductComponent from './components/product/ProductComponent.jsx';

import ProductListComponent from './components/product/ProductListComponent.jsx';

import ProductNewComponent from './components/product/ProductNewComponent.jsx';

import ProductEditComponent from './components/product/ProductEditComponent.jsx';

import AboutComponent from './components/about/AboutComponent.jsx';


class App extends React.Component {

    

    render() {

        return (

            <div>

                <div className="row">

                    <div className="col-sm-8 col-sm-offset-2">

                        <nav className="navbar navbar-default">

                            <div className="container-fluid">

                                <div className="navbar-header">

                                    <a className="navbar-brand">Simple CRUD</a>

                                </div>

                                <div id="navbar" className="navbar-collapse collapse">

                                    <ul className="nav navbar-nav">

                                        <li><a href="#/">Home</a></li>

                                        <li><a href="#/product">Product Management</a></li>

                                        <li><a href="#/about">About</a></li>

                                    </ul>

                                </div>

                            </div>

                        </nav>

                        <Router history={hashHistory}>

                            <Route path="/" component={HomeComponent} />

                            <Route path="/product" component={ProductComponent}>

                                <Route path="/product/list" component={ProductListComponent} />

                                <Route path="/product/new" component={ProductNewComponent} />

                                <Route path="/product/edit/:productId" component={ProductEditComponent} />

                            </Route>

                            <Route path="/about" component={AboutComponent} />

                        </Router>

                    </div>

                </div>

            </div>

        );

    }


}


render( <App /> , document.getElementById('app'));

Create 2 directories classes and components inside app directory, and add new file named TableClass.jsx inside classes directory:

import React from 'react';


const TableClass = React.createClass({


    bindHeader:function(){

        var cols = this.props.cols;

        var elements = cols.map(function(colHeader){

            return <th key={colHeader.key}>{colHeader.label}</th>

        });

        //action column

        elements.push(<th key="dataHeaderAction">Action</th>);

        return elements;

    },


    bindData:function(){

        var data = this.props.data;

        var self = this;

        return data.map(function(colData){

            var rowElement = [];

            $.each(colData,function(key,val){

                rowElement.push(<td key={key}>{val}</td>);              

            });

            //action column

            rowElement.push(

                <td key="actionColumn">

                    <div className="col-md-2">

                        <button className="btn btn-xs btn-primary" onClick={self.props.onUpdate} data-id={colData.id}>Edit</button>

                    </div>

                    <div className="col-md-2">

                        <button className="btn btn-xs btn-danger" onClick={self.props.onDelete} data-id={colData.id}>Delete</button>

                    </div>

                </td>

            );

            return <tr className='data-row' data-id={colData.id} key={colData.id}>{rowElement}</tr>;

        });

    },


    render:function(){


        var tableHeader = this.bindHeader();

        var tableData = this.bindData();


        return (

            <table className='table table-hover table-striped table-bordered'>

                <thead>

                    <tr>{tableHeader}</tr>

                </thead>

                <tbody>

                    {tableData}

                </tbody>

            </table>

        );

    }


});


export default TableClass;

Create 3 directories about, home, and product inside components directory, add new file named AboutComponent.jsx inside about directory:

import React from 'react';


class AboutComponent extends React.Component{


    render(){

        return (

            <div className="panel panel-default">

                <div className="panel-heading">About</div>

                <div className="panel-body">

                    This is a simple CRUD tutorial result, this project using : 

                    <ul>

                        <li>NodeJS</li>

                        <li>Express JS</li>

                        <li>MySQL</li>

                        <li>Webpack</li>

                        <li>Babel</li>

                        <li>React</li>

                        <li>Axios</li>

                        <li>jQuery</li>

                        <li>Bootstrap</li>

                        <li>Toastr</li>

                    </ul>

                </div>

            </div>

        );

    }


}


export default AboutComponent;


Add new file named HomeComponent.jsx inside home directory:

import React from 'react';

class HomeComponent extends React.Component{

    render(){
        return (
            <div className="panel panel-default">
                <div className="panel-heading">Home</div>
                <div className="panel-body">Welcome to simple CRUD tutorial</div>
            </div>
        );
    }

}

export default HomeComponent;

The main part of this tutorial is we are going to create product management page inside product directory, we need to create 4 files ProductComponent.jsx, ProductEditComponent.jsx, ProductListComponent.jsx, and ProductNewComponent.jsx

ProductComponent.jsx

import React from 'react';

import { Link } from 'react-router'


class ProductComponent extends React.Component{


    render(){

        return (

            <div className="panel panel-default">

                <div className="panel-heading">Product Management</div>

                <div className="panel-body">

                    <ul className="nav nav-tabs">

                        <li><Link activeClassName="active" to="product/list">Product List</Link></li>

                        <li><Link activeClassName="active" to="product/new">Add New Product</Link></li>

                    </ul>

                    <br />

                    {this.props.children}

                </div>

            </div>

        );

    }


}


export default ProductComponent;

ProductListComponent.jsx

import React from 'react';

import axios from 'axios';


import TableClass from '../../classes/TableClass.jsx';


class ProductListComponent extends React.Component{


    constructor(props) {

        super(props);

        this.getProductList();


        this.state = {

            cols : [

                {key:'dataHeaderId',label:'Id'},

                {key:'dataHeaderName',label:'Name'},

                {key:'dataHeaderPrice',label:'Price'},

                {key:'dataHeaderCreatedAt',label:'Created At'}

            ],

            data : [

                

            ]

        };

    }


    getProductList(){

        toastr.info('Fetching product list...');

        var self = this;

        axios.get('products').then(function(response){

            toastr.clear();

            self.setState({

                cols : self.state.cols,

                data : response.data

            });

        }).catch(function(error){

            toastr.clear();

            toastr.error(error);

        });

    }


    updateProduct(data){

        var id = $(data.target).data('id');

        location.href='#/product/edit/'+id;

    }


    deleteProduct(data){

        var id = $(data.target).data('id');

        $("#deleteConfirmationModal").modal('show');

        $("#deleteButton").attr('data-id',id);

    }


    doDeleteProduct(data){

        var id = $(data.target).data('id');

        toastr.info('Deleting product...');

        axios.delete('products/'+id).then(function(response){

            toastr.clear();

            $(".data-row[data-id='"+id+"']").slideUp();

        }).catch(function(error){

            toastr.clear();

            toastr.error(error);

        });

    }


    render(){

        return (

            <div>

                <TableClass cols={this.state.cols} data={this.state.data} onDelete={this.deleteProduct} onUpdate={this.updateProduct}/>

                <div className="modal fade" id="deleteConfirmationModal" role="dialog">

                    <div className="modal-dialog">

                        <div className="modal-content">

                            <div className="modal-header">

                                <button type="button" className="close" data-dismiss="modal">&times;</button>

                                <h4 className="modal-title">Delete Item</h4>

                            </div>

                            <div className="modal-body">

                                <p>Are you sure want to delete this item?</p>

                            </div>

                            <div className="modal-footer">

                                <button type="button" className="btn btn-default" data-dismiss="modal">Cancel</button>

                                <button type="button" className="btn btn-danger" 

                                        id="deleteButton" 

                                        onClick={this.doDeleteProduct.bind(this)} 

                                        data-dismiss="modal">Delete Item</button>

                            </div>

                        </div>

                    </div>

                </div>

            </div>

        );

    }


}


export default ProductListComponent;

ProductNewComponent.jsx

import React from 'react';

import axios from 'axios';


class ProductNewComponent extends React.Component{


    constructor(props) {

        super(props);

        this.state = {

            name : null,

            price : null

        }

    }


    submitForm(event){

        event.preventDefault();

        var data = $(event.target).serialize();

        toastr.clear();

        var isError = false;

        if(this.state.name===""){

            toastr.error("Product name must be filled!");

            isError=true;

        }

        if(this.state.price===0 || this.state.price===''){

            toastr.error("Product price must be filled!");

            isError=true;

        }

        if(!isError){

            toastr.info('Inserting new product data...');

            axios.post('/products',{

                name : this.state.name,

                price : this.state.price

            }).then(function(response){

                toastr.clear();

                location.href = "#/product/list";

            }).catch(function(error){

                toastr.clear();

                toastr.error(error);

            });

        }

    }


    onProductNameChange(e){

        this.setState({

            id : this.state.id,

            name : e.target.value.trim(),

            price : this.state.price

        });

    }


    onProductPriceChange(e){

        this.setState({

            id : this.state.id,

            name : this.state.name,

            price : e.target.value

        });

    }


    render(){

        return (

            <div>

                <form className="form-horizontal" onSubmit={this.submitForm.bind(this)}>

                    <div className="form-group">

                        <label className="control-label col-sm-2" htmlFor="productEmail">Name : </label>

                        <div className="col-sm-10">

                            <input  type="text" name='productName' 

                                    onChange={this.onProductNameChange.bind(this)}

                                    id="productName" className="form-control" placeholder="Product Name" />

                        </div>

                    </div>

                    <div className="form-group">

                        <label className="control-label col-sm-2" htmlFor="productPrice">Price : </label>

                        <div className="col-sm-10">

                            <input  type="number" name='productPrice' 

                                    onChange={this.onProductPriceChange.bind(this)}

                                    id="productPrice" className="form-control" placeholder="Product Price" />

                        </div>

                    </div>

                    <div className="form-group">

                        <div className="col-sm-offset-2 col-sm-10">

                            <button type="submit" className="btn btn-default">Save</button>

                        </div>

                    </div>

                </form>


            </div>

        );

    }


}


export default ProductNewComponent;

ProductEditComponent.jsx

import React from 'react';

import axios from 'axios';


class ProductNewComponent extends React.Component{


    constructor(props) {

        super(props);

        this.state = {

            name : null,

            price : null

        }

    }


    submitForm(event){

        event.preventDefault();

        var data = $(event.target).serialize();

        toastr.clear();

        var isError = false;

        if(this.state.name===""){

            toastr.error("Product name must be filled!");

            isError=true;

        }

        if(this.state.price===0 || this.state.price===''){

            toastr.error("Product price must be filled!");

            isError=true;

        }

        if(!isError){

            toastr.info('Inserting new product data...');

            axios.post('/products',{

                name : this.state.name,

                price : this.state.price

            }).then(function(response){

                toastr.clear();

                location.href = "#/product/list";

            }).catch(function(error){

                toastr.clear();

                toastr.error(error);

            });

        }

    }


    onProductNameChange(e){

        this.setState({

            id : this.state.id,

            name : e.target.value.trim(),

            price : this.state.price

        });

    }


    onProductPriceChange(e){

        this.setState({

            id : this.state.id,

            name : this.state.name,

            price : e.target.value

        });

    }


    render(){

        return (

            <div>

                <form className="form-horizontal" onSubmit={this.submitForm.bind(this)}>

                    <div className="form-group">

                        <label className="control-label col-sm-2" htmlFor="productEmail">Name : </label>

                        <div className="col-sm-10">

                            <input  type="text" name='productName' 

                                    onChange={this.onProductNameChange.bind(this)}

                                    id="productName" className="form-control" placeholder="Product Name" />

                        </div>

                    </div>

                    <div className="form-group">

                        <label className="control-label col-sm-2" htmlFor="productPrice">Price : </label>

                        <div className="col-sm-10">

                            <input  type="number" name='productPrice' 

                                    onChange={this.onProductPriceChange.bind(this)}

                                    id="productPrice" className="form-control" placeholder="Product Price" />

                        </div>

                    </div>

                    <div className="form-group">

                        <div className="col-sm-offset-2 col-sm-10">

                            <button type="submit" className="btn btn-default">Save</button>

                        </div>

                    </div>

                </form>


            </div>

        );

    }


}


export default ProductNewComponent;


ċ
Simple CRUD ExpressJS and React.zip
(212k)
Andi Muqsith Ashari,
Nov 1, 2016, 7:21 PM
Comments