Node.js MySQL CRUD Example Tutorial

📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.

🎓 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 (176K+ 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.