Node.js MySQL CRUD Example Tutorial

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

In this tutorial, we'll learn how to use MySQL in Node.js by creating a connection and executing SQL queries for performing CRUD (create, read, update and delete) operations.

MySQL is a leading open-source database management system. It is a multi-user, multithreaded database management system. MySQL is especially popular on the web. The node.js is a complete platform for building server-side applications.

Make sure that you have MySQL installed on your computer. You can download a free MySQL database at https://www.mysql.com/downloads/.

Setup MySQL database

Make sure that you have installed the MySQL database in your machine.

Use the following command to create a database:

create database demo;

Use below SQL script to create todos table in 'demo' database:

CREATE TABLE `todos` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

1. Creating a Node.js Project

Let's start by creating our node.js project. First, create a folder for your project using the following command:

$ mkdir node-mysql-crud-tutorial

Go to the root folder of your application and type npm init to initialize your app with a package.json file.

$ cd node-mysql-crud-tutorial
$ npm init -y

This will create a package.json with default values:

{
  "name": "nodejs-mysql-crud-tutorial",
  "version": "1.0.0",
  "description": "NodeJS + MySQL CRUD Example Tutorial",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "NodeJs",
    "MySQL",
    "CRUD"
  ],
  "author": "Ramesh Fadatare",
  "license": "ISC",
  "dependencies": {
    "mysql": "^2.18.1"
  }
}

2. Install MySQL Driver

To download and install the "mysql" module, open the Command Terminal and execute the following:

$npm install mysql --save

Now you have downloaded and installed a MySQL database driver.

Node.js can use this module to manipulate the MySQL database:

var mysql = require('mysql');

3. Performing CRUD Operations

CRUD stands for create, read, update and delete and it refers to common operations that are used in most data-driven applications.

• We create data in the database tables using the INSERT statement.
• We read data from the database tables using the SELECT statement.
• We update data in the database tables using the UPDATE statement.
• We delete data from the database tables using the DELETE statement.

3.1 Creating/Inserting Data

Let's create a file named 'insert-record.js' and add the following code to it:

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
});

let params = {
    name: "Todo 1",
    description: "Todo 1 description"
}

connection.query("INSERT INTO todos SET ? ", params,
    function (error, results, fields) {
        if (error) throw error;
        console.log("Record inserted");
    });

Run above code with the following command:

$ node .\insert-record.js
You are now connected with mysql database...
Record inserted

3.2 Reading/Selecting data

Let's create a file named 'read-record.js' and add the following code to it:

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
});

// Retrieve and return all todos from the database.

connection.query('select * from todos',
    function (error, results, fields) {
        if (error) throw error;
        console.log(JSON.stringify(results));
    });

Run above code with the following command:

$ node .\read-record.js
You are now connected with mysql database...
[{"id":2,"name":"Learn Advanced Express.js ","description":"Learn Advanced Express.js with examples"},{"id":3,"name":"Todo 2","description":"Todo 2 description"},{"id":5,"name":"Todo 1","description":"Todo 1 description"}]

3.3 Updating Data

Let's create a file named 'update-record.js' and add the following code to it:

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
});

let params = {
    name: "Todo 2",
    description: "Todo 2 description"
}

connection.query('UPDATE `todos` SET `name`=?,`description`=? where `id`=?',
[params.name, params.description, 3],
function (error, results, fields) {
    if (error) throw error;
    console.log("Record updated!");
});

Run above code with the following command:

$ node .\update-record.js
You are now connected with mysql database...
Record updated!

3.4 Deleting Data

Let's create a file named 'delete-record.js' and add the following code to it:

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
});

let params = {
    name: "Todo 1",
    description: "Todo 1 description"
}

connection.query(
    'DELETE FROM todos where id = ?', [3], (err, result) => {
        if (err) throw err;
        console.log(result);
    }
);

Run above code with the following command:

$ node .\delete-record.js
You are now connected with mysql database...
OkPacket {
  fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

Conclusion

In this tutorial, you have created a simple CRUD example that demonstrates how to perform basic create, read, update and delete operations via SQL select, insert, update and delete statements.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

🆕 High-Demand 80–90% OFF

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

🆕 High-Demand 80–90% OFF

ChatGPT + Generative AI + Prompt Engineering for Beginners

🚀 Trending Now 80–90% OFF

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Real-Time REST APIs with Spring Boot - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Building Microservices with Spring Boot and Spring Cloud

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Java Full-Stack Developer Course with Spring Boot and React JS

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

🌟 Top Rated 80–90% OFF

Testing Spring Boot Application with JUnit and Mockito

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot Thymeleaf Real-Time Web Application - Blog App

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Master Spring Data JPA with Hibernate

🔥 Bestseller 80–90% OFF
Available in Udemy for Business

Spring Boot + Apache Kafka Course - The Practical Guide

🎓 Student Favorite 80–90% OFF
Available in Udemy for Business

Java Testing: Mastering JUnit 5 Framework

🆕 New 80–90% OFF

Reactive Programming in Java: Spring WebFlux and Testing

🌟 Top Rated 80–90% OFF

Spring Boot + RabbitMQ Course - The Practical Guide

🌟 Top Rated 80–90% OFF
Available in Udemy for Business

Functional Programming in Java (Includes Java Collections)

🎓 Student Favorite 80–90% OFF

ChatGPT for Java Developers: Boost Your Productivity with AI

🚀 Trending Now 80–90% OFF