Node JS Crud App with Router, EJS, MySQL

Sourabh Mourya

Sourabh Mourya

Sr. Software Developer
Share on facebook
Share on twitter
Share on pinterest
Share on linkedin

Node.js CRUD (Create, Read, Update, Delete) application using Express.js as the web framework, EJS as the template engine, and MySQL as the database.

Before you start, make sure you have Node.js and MySQL installed on your computer.

Create a new Node.js project and install the necessary dependencies.

mkdir node-crud
cd node-crud
npm init -y
npm install express ejs mysql body-parser

Create a new file named index.js in the project root directory, and add the following code.

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

app.set('view engine', 'ejs');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_mysql_username',
  password: 'your_mysql_password',
  database: 'your_database_name'
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL database!');
});

app.use(bodyParser.urlencoded({ extended: true }));

// READ - fetch all records
app.get('/', (req, res) => {
  const sql = 'SELECT * FROM users';
  connection.query(sql, (err, results) => {
    if (err) throw err;
    res.render('index', { users: results });
  });
});

// CREATE - show add form
app.get('/add', (req, res) => {
  res.render('add');
});

// CREATE - process add form
app.post('/add', (req, res) => {
  const { name, email, phone } = req.body;
  const sql = 'INSERT INTO users (name, email, phone) VALUES (?, ?, ?)';
  connection.query(sql, [name, email, phone], (err, result) => {
    if (err) throw err;
    res.redirect('/');
  });
});

// UPDATE - show edit form
app.get('/edit/:id', (req, res) => {
  const id = req.params.id;
  const sql = 'SELECT * FROM users WHERE id = ?';
  connection.query(sql, [id], (err, result) => {
    if (err) throw err;
    res.render('edit', { user: result[0] });
  });
});

// UPDATE - process edit form
app.post('/edit/:id', (req, res) => {
  const id = req.params.id;
  const { name, email, phone } = req.body;
  const sql = 'UPDATE users SET name = ?, email = ?, phone = ? WHERE id = ?';
  connection.query(sql, [name, email, phone, id], (err, result) => {
    if (err) throw err;
    res.redirect('/');
  });
});

// DELETE - process delete request
app.get('/delete/:id', (req, res) => {
  const id = req.params.id;
  const sql = 'DELETE FROM users WHERE id = ?';
  connection.query(sql, [id], (err, result) => {
    if (err) throw err;
    res.redirect('/');
  });
});

app.listen(3000, () => {
  console.log('Server started on port 3000');
});

Create a new folder named views in the project root directory, and create the following EJS files in it.

index.ejs

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Node.js CRUD Application with MySQL</title>
</head>
<body>
  <h1>Node.js CRUD Application with MySQL</h1>
  <a href="/add">Add User</a>
  <table>
    <thead>
      <tr>
        <th>Name</th>
        <th>Email</th>
        <th>Phone</th>
        <th>Actions</th>
      </tr>
    </thead>
    <tbody>
      <% users.forEach(function(user) { %>
        <tr>
          <td><%= user.name %></td>
          <td><%= user.email %></td>
          <td><%= user.phone %></td>
          <td>
            <a href="/edit/<%= user.id %>">Edit</a>
            <form method="post" action="/delete/<%= user.id %>" style="display: inline;">
              <button type="submit">Delete</button>
            </form>
          </td>
        </tr>
      <% }); %>
    </tbody>
  </table>
</body>
</html>

This code displays a table of all users with their name, email, and phone number. Each row includes links to edit and delete the user. There is also a link to add a new user.

add.ejs

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Add User</title>
</head>
<body>
  <h1>Add User</h1>
  <form method="post" action="/add">
    <label for="name">Name:</label>
    <input type="text" name="name" id="name"><br>
    <label for="email">Email:</label>
    <input type="email" name="email" id="email"><br>
    <label for="phone">Phone:</label>
    <input type="tel" name="phone" id="phone"><br>
    <input type="submit" value="Submit">
  </form>
</body>
</html>

edit.ejs

<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Edit User</title>
</head>
<body>
  <h1>Edit User</h1>
  <form method="post" action="/edit/<%= user.id %>">
    <label for="name">Name:</label>
    <input type="text" name="name" id="name" value="<%= user.name %>"><br>
    <label for="email">Email:</label>
    <input type="email" name="email" id="email" value="<%= user.email %>"><br>
    <label for="phone">Phone:</label>
    <input type="tel" name="phone" id="phone" value="<%= user.phone %>"><br>
    <input type="submit" value="Submit">
  </form>
</body>
</html>
Create a MySQL table named users with the following schema
CREATE TABLE users (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone VARCHAR(20),
  PRIMARY KEY (id)
);

Run the application using the following command.

node index.js

Open your web browser and navigate to http://localhost:3000 view the application.

You can add, edit, and delete users using the provided forms. The changes will be reflected in the MySQL database.

In this Node.js CRUD application with MySQL, we used Node.js with the Express framework, the Embedded JavaScript (EJS) template engine, and the MySQL database to create a web application that allows users to perform basic CRUD (Create, Read, Update, and Delete) operations on a list of users.

We set up routes to handle HTTP requests and display EJS templates that render the application’s HTML pages.

We created an HTML form to allow users to add new users and implemented the necessary routes and MySQL queries to insert the user data into the database. We also implemented an edit form and delete functionality.

Overall, this example demonstrates how to create a simple Node.js web application using popular libraries and tools commonly used in web development. It also shows how to integrate with a MySQL database to perform basic CRUD operations.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Stories