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.