Tutorial Create CRUD With Node.Js , Express, MySQL And AdminLTE For Beginner

Continuing the tutorial on node.js express tutorial, I will share tutorial to create CRUD using MySQL, node.js, express and adminLTE. If you haven’t read my previous article regarding how to transfer adminLTE to jade template engine in express.js, please read it first because this article is a continuation of the article. In this tutorial, I will create a simple web application to create, read, update and delete customer data. It is important to note, this tutorial for beginners, so for those who are already expert could advise me through the comments field.

CRUD is a standard concept for manufacturing information system application. In this blog I have been several times made articles about CRUD using PHP, maybe if interested you can read

Tutorial PHP Class For Simple CRUD with PDO For Beginners.
Create Simple CRUD with Datatables, Jquery, and AdminLTE

Let’s start create CRUD with Node.Js , Express, MySQL and AdminLTE Tutorial for beginner

As early initialization, create a database with the name nodejs and a customer table.

Customer table structure

Prepare Database Simple Crud With Node Js

Insert table customer with the data below

Create a new express project with expressproject name (if you have read the previous tutorial, skip this section)

Then enter the expressproject folder and do the following command

Package.json which I use in this example are as follows

Package Json Create Simple Crud Node Js Express Mysql And Adminlte

Install all package/plugin that is required

A little explanation :

  • express-flash
    Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
    In this tutorial express flash is used to display a warning, error and information message
  • express-session
    Express-session is used to made a session as in PHP. In this tutorial, session is needed as the express requirement of express-flash.
  • express-validator
    To make the validation of the input form, already provided plugin which is very comprehensive and easy to use, express-validator highly effective and efficient way to accelerate the creation of applications.
  • method-override
    NPM is used to run a DELETE and PUT method from an HTML form. Because now, in several web browsers only support GET and POST methods.
  • MySQL
    Driver to connect node.js with MySQL

Connecting template adminLTE to express js

Please download the source adminLTE in my previous tutorial here and ensure order in the expressproject folder as shown below

Expressproject With Adminlte Template Folder List

Then please adjust your app.js file as in the following code:

A brief description :

Express-session needs a secret key

The script below is used to be able to use the DELETE and PUT method on method-override package

To connect MySQL with Node.js, we need to declare the initial connection with the following code

In this simple tutorial, we will create a CRUD to the customer so that we need to create a new route with the name customers.js

The next step creates a new route file in the folder with the name customers.js routes

Create Routes Customers Tutorial Crud With Node Js Express Js Adminlte And Mysql

Read the customer MySQL data

Let customers.js file empty, because first, we will create a new page to display a list of customers.

Create a folder called customer in the folder views, we will create a new view to display the customer list page. Create a file named list.jade and copy the following code

Note : Remember, writing jade template engine is A clean, whitespace-sensitive template. So it needs to be carefully and careful

Then open routes customers.js that we make before and copy the following script:

Run the following command in a terminal or command prompt

If using windows add the character ” & “

Then open your browser and enter the following URL address to open a customer list
http://localhost:3000/customers

Customers Page Tutorial Create Crud With Mysql Node Js Express Adminlte For Beginner

A simple explanation :

connection.query used to run a MySQL query. Error when doing MySQL query will be accommodated into the “err” variable. Then if there is an error, to display the error message, using the express-flash req.flash (‘msg_error’, error). Mysql query data is collected into the “rows” variable as a JSON form.

To display the customer data on file list.jade, looping required as follows

In the delete button, there is an input with hidden type that will be used to override the DELETE method

The session read customer data from mysql has been completed, the next step will create a new customer

CREATE a new customer

create a new view page as add-customer.js in the folder views/customer/add-customer.js and copy the following code

Then add the script on the following customer.js routes

Explanation :

When the URL localhost: 3000/customers/add loaded, the server will call the GET method. After pressing the save button, the server will execute the POST method.

The above code is used to validate the input name. The entire function of validation, you can learn in https://github.com/chriso/validator.js

Save and run the node.js server, if successful it will be as shown below

Add Page

Error Alert In Create Customer

Successfull Create New Customer And Redirect To Customer List Page Crud With Nodejs And Express

Until this stage, the process of creating a new customer has been completed. The next stage is the process to update customer data. Let’s continue the following node.js express tutorial

Update the customer data

Make an edit page with name edit.jade on the folder views/customer/edit.jade. Then copy the following code

Then add a new route for editing data on the file routes/customers.js. In this session, PUT method will be used to update the customer data. Please copy the following code.

Run the node.js server and load page localhost:3000/customers, and press the edit button. If successful will be as shown below

Edit Page Node Js Express Js And Mysql

Edit Page Successfull Alert

If you ask why should use the PUT method to perform the update function?? I found an interesting answer to check out, please visit

http://stackoverflow.com/questions/630453/put-vs-post-in-rest

up here UPDATE customer data stage has been completed.

DELETE the customer data

At this session, the DELETE method will be used. Add routes.delete on customers.js file and copy the following code

Save and run Node.js server. Up here the tutorial creates a crud using node.js has been completed. Customers.js overall code like the following code

Project complete CRUD express mysql above can be downloaded at the following link, please share this article via the download button below to open the download button

Thus article about Create CRUD With Node.Js , Express, MySQL, AdminLTE Tutorial For Beginner. Hopefully useful.

If there is any questions or suggestions, please use the comment fields below.

The following two tabs change content below.
This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

10 Comments

 Add your comment
  1. Hi there.

    Cann you please help me? Im having problems with calling the function on router.get.
    Problems I’m having is “req.getConnection is not a function” when I try to click the page.

    Thanks

    • The code sequences should be correct
      [php]
      var express = require(‘express’);
      var path = require(‘path’);
      var favicon = require(‘serve-favicon’);
      var logger = require(‘morgan’);
      var cookieParser = require(‘cookie-parser’);
      var bodyParser = require(‘body-parser’);
      var flash = require(‘express-flash’);
      var session = require(‘express-session’);
      var index = require(‘./routes/index’);
      var users = require(‘./routes/users’);
      var customers = require(‘./routes/customers’);
      var expressValidator = require(‘express-validator’);
      var methodOverride = require(‘method-override’);

      var connection = require(‘express-myconnection’);
      var mysql = require(‘mysql’);
      …………………..
      ………………….
      app.use(
      connection(mysql,{
      host: ‘localhost’,
      user: ‘root’, // your mysql user
      password : ‘123456’, // your mysql password
      port : 3306, //port mysql
      database:’nodejs’ // your database name
      },’pool’) //or single

      );

      app.use(‘/’, index);
      app.use(‘/customers’, customers);
      app.use(‘/users’, users);
      [/php]

  2. would you mind doing this tutorial using MongoDB instead?

  3. Delete route respond 404

  4. where code insert id i am error when insert new customer

  5. I get error as it
    home/akshay/expressproject/app.js:32
    if (req.body && typeof req.body == ‘object’ && ‘_method’ in req.body)
    ^

    • hello,

      Please change these code with

      My WordPress change && with &

      hope fix the problem

Leave a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.