Node.js MySQL CRUD Example Tutorial

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.

Comments

  1. This article is helpful by using with CRUD Operation in Expressjs with MVC Pattern in Express, But Let me know that How to send flash message after inserting, updating, or deleting the data

    ReplyDelete

Post a Comment