Crud Operations with NodeJS and MySQL

Simple crud operations with NodeJS MySQL

To perform crud operations with NodeJS MySQL we’ll create a post management system by using NodeJS and MySQL.

In this post management system, a user can create posts, read posts, update posts and delete posts. So let’s create this –

Step – 1

First, you need to install NodeJS on your system, after that create a new folder on your desktop and named it what you want, here I named it nodemysql.

After that, inside the newly created folder initialize NPM.

How to initialize NPM ?

open your terminal select the folder where you want to initialize NPM and after selecting the folder run npm init command on your terminal. click here to learn in details.

Step – 2

After completing the step one, now install the four dependencies –

express – npm install express --save
mysql2 – npm install mysql2 --save
body-parser – npm install body-parser --save
twig template engine – npm install twig --save

My package.json File

{
  "name": "nodemysql",
  "version": "1.0.0",
  "description": "",
  "main": "app.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "Webtutorials.ME",
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.18.3",
    "express": "^4.16.4",
    "mysql2": "^1.6.4",
    "twig": "^1.12.0"
  }
}

Step – 3

Now time to configure our database.

So open your mysqlDB or MariaDB and also you can use the XAMPP or WAMP server, and then create a new database called node_mysql.

Use the below SQL query to creates posts table into the node_mysql Database and it also creates the posts table structure.

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `title` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `author` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step – 4

After completing the database configuration, Now we’ll creating our files.

But before creating our files let’s take a look at the nodemysql folder structure.

Creating files

First, we’ll create the database.js file inside the config folder for making the connection with our database.

database.js

const mysql = require('mysql2');
const connection = mysql.createConnection({
    host     : 'localhost', // MYSQL HOST NAME
    user     : 'root',        // MYSQL USERNAME
    password : '',    // MYSQL PASSWORD
    database : 'node_mysql'      // MYSQL DB NAME
});
module.exports = connection;

After that, we’ll create the app.js file. This file name is app.js because in my package.json file you can see "main": "app.js".

app.js

const express = require('express');
const app = express();
const twig = require('twig');
const bodyParser = require('body-parser');

// IMPORT DB CONNECTION
const connection = require('./config/database');

// SET VIEW ENGINE
app.set('view engine','html');
app.engine('html', twig.__express);
app.set('views','views');

// USE BODY-PARSER MIDDLEWARE
app.use(bodyParser.urlencoded({extended:false}));

app.get('/', (req, res) => {
    // FETCH ALL THE POSTS FROM DATABASE
    connection.query('SELECT * FROM `posts`', (err, results) => {
        if (err) throw err;
        // RENDERING INDEX.HTML FILE WITH ALL POSTS
        res.render('index',{
            posts:results
        });
    });
    
});

// INSERTING POST
app.post('/', (req, res) => {
    const title = req.body.title;
    const content = req.body.content;
    const author_name = req.body.author_name;
    const post = {
        title: title,
        content: content,
        author: author_name,
        created_at: new Date()
    }
    connection.query('INSERT INTO `posts` SET ?', post, (err) => {
        if (err) throw err;
        console.log('Data inserted');
        return res.redirect('/');
    });
});

// EDIT PAGE
app.get('/edit/:id', (req, res) => {
    const edit_postId = req.params.id;
    // FIND POST BY ID
    connection.query('SELECT * FROM `posts` WHERE id=?',[edit_postId] , (err, results) => {
        if (err) throw err;
        res.render('edit',{
            post:results[0]
        });
    });
});

// POST UPDATING
app.post('/edit/:id', (req, res) => {
    const update_title = req.body.title;
    const update_content = req.body.content;
    const update_author_name = req.body.author_name;
    const userId = req.params.id;
    connection.query('UPDATE `posts` SET title = ?, content = ?, author = ? WHERE id = ?', [update_title, update_content, update_author_name, userId], (err, results) => {
        if (err) throw err;
        if(results.changedRows === 1){
            console.log('Post Updated');
            return res.redirect('/');
        }
    });
});

// POST DELETING
app.get('/delete/:id', (req, res) => {
    connection.query('DELETE FROM `posts` WHERE id = ?', [req.params.id], (err, results) => {
        if (err) throw err;
        res.redirect('/');
    });
});
// SET 404 PAGE
app.use('/',(req,res) => {
    res.status(404).send('<h1>404 Page Not Found!</h1>');
});
// IF DATABASE CONNECTION IS SUCCESSFUL
connection.connect((err) => {
    if (err) throw err;
    app.listen(3000);
});

In the app.js you can see that we are not validating form data.

If you wish I make a tutorial on how to validate form data with NodeJS then just drop me a comment.

Creating views

Now inside the views folder we’ll create our views –

index.html

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>CRUD with Node.JS + MySQL</title>
    <!-- USING BOOTSTRAP FOR STYLING USER INTERFACE -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css" integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
    <style>
        .customRow{
            background-color: #f2f2f2;
            padding: 20px;
        }
</style>
</head>

<body>
    <div class="customRow">
        <div class="container">
            <div class="card">
                <div class="card-body">
                    <h1 class="text-center">Post Management System</h1>
                    <hr>
                    <form action="" method="POST">
                        <div class="form-group">
                            <label for="post_title">Post Title</label>
                            <input type="text" name="title" class="form-control" placeholder="Title" id="post_title" required>
                            <label for="post_content">Post Content</label>
                            <textarea name="content" class="form-control" placeholder="Write something" id="post_content" required></textarea>
                            <label for="author_name">Author Name</label>
                            <input type="text" name="author_name" class="form-control" placeholder="Enter author name" id="author_name" required>
                            <br>
                            <input type="submit" value="POST" class="btn btn-primary">
                        </div>
                    </form>
                    <hr>
                    <h2 class="text-center">All Posts</h2>
                    <!-- IF HAVE ANY POSTS -->
                    {% if posts|length > 0 %}
                        <!-- LOOPING ALL THE POSTS -->
                        {% for post in posts %}
                            <div class="card">
                                <div class="card-body">
                                    <h5 class="card-title">{{ post.title | e }} | <small><span>{{ post.created_at | date("d M, Y") }}</span></small></h5>
                                    <p class="card-text text-justify">{{ post.content | e }}</p>
                                    <span class="float-right"><strong>By</strong>, {{ post.author | e }}</span>
                                    <a href="/edit/{{ post.id | e }}" class="btn btn-light">✎ Edit</a>
                                    <a href="/delete/{{ post.id | e }}" class="btn btn-danger">Delete</a>
                                </div>
                            </div>
                            <hr>
                        {% endfor %}
                    {% else %}
                        <h4>No Post Found !</h4>
                    {% endif %}
                </div>
            </div>
        </div>
    </div>
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js" integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js" integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k" crossorigin="anonymous"></script>
</body>

</html>

edit.html

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

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Edit Post</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/css/bootstrap.min.css" integrity="sha384-GJzZqFGwb1QTTN6wy59ffF1BuGJpLSa9DkKMp0DgiMDm4iYMj70gZWKYbI706tWS" crossorigin="anonymous">
    <style>
        .customRow{
            background-color: #f2f2f2;
            padding: 20px;
        }
</style>
</head>

<body>
    <div class="customRow">
        <div class="container">
            <div class="card">
                <div class="card-body">
                    <h1 class="text-center">Post Management System</h1>
                    <hr>
                    {% if post is empty %}
                        <h3>Invalid Post ID</h3>
                    {% else %}
                        <form action="" method="POST">
                            <div class="form-group">
                                <label for="post_title">Title</label>
                                <input type="text" name="title" class="form-control" placeholder="Title" id="post_title" value="{{ post.title | e }}" required>
                                <label for="post_content">Post Content</label>
                                <textarea style="height:100px;" name="content" class="form-control" placeholder="Write something" id="post_content" required>{{ post.content | e }}</textarea>
                                <label for="author_name">Author Name</label>
                                <input type="text" name="author_name" class="form-control" placeholder="Enter author name" id="author_name" value="{{ post.author | e }}" required>
                                <br>
                                <input type="submit" value="UPDATE" class="btn btn-success">
                            </div>
                        </form>
                    {% endif %}
                </div>
            </div>
        </div>
    </div>
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.6/umd/popper.min.js" integrity="sha384-wHAiFfRlMFy6i5SRaxvfOCifBUQy1xHdJ/yoi7FRNXMRBu5WHdZYu1hA6ZOblgut" crossorigin="anonymous"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.2.1/js/bootstrap.min.js" integrity="sha384-B0UglyR+jN6CkvvICOB2joaf5I4l3gm9GU6Hc1og6Ls7i6U/mkkaduKaBhlAXv9k" crossorigin="anonymous"></script>
</body>

</html>

Completed.

Download this project from GitHub

GitHub

Learn also:

CRUD with Node JS and MongoDB
CRUD with Node JS and Mongoose

Node JS MySQL Crud operations

Simple crud operations with NodeJS MySQL

How to initialize NPM ?

One comment

Leave a ReplyCancel Reply

Simple crud operations with NodeJS MySQL

How to initialize NPM ?

Related Posts

Node js Login and Registration REST API with MySQL DB

Install Node JS on Ubuntu and Windows 10

Node JS Form Handling: GET, POST Requests, Form Data Validation

One comment

Leave a ReplyCancel Reply