Node.js MySQL Tutorial for Beginners

July 7, 2019
by Rahil Shaikh
  • node mysql tutorial

In this Node MySQL tutorial, we will learn…

  1. How to connect to MySQL Database using Node.js?
  2. How to Perform Create, Read, Update, Delete Operations using Node.js
  3. We will also see how to do some other basic DDL operations on MySQL

MySQL is the most popular relational database by far. Chances are this is the very first database you would have used to create your first application no matter the programming language.

There may be a lot of other tutorials out there, but what I have tried to do here is cover everything possible that may be required by a beginner to get started with Node Mysql.

So if you are someone who is just starting with Node.js and MySQL, then look no further than this tutorial.

MySQL installation

There are a couple of ways to install MySQL server on your local machine.

If you are on windows…

  • Most of the beginners install MySQL using XAMP, but this is for the PHP stack and it comes with Apache, Mysql and PHP.
  • The other way is to only install MySQL server using the MSI installer available here.

If you are running Ubuntu/ Linux run the following commands in order.

$ sudo apt-get update
$ sudo apt-get install mysql-server

$ systemctl start mysql.service

 

Installing MySQL workbench

Now if you are installing MySQL for the first time, you will need a tool to help you browse through and interact with your database server.

Of course, we can do that using the CLI, but it’s always better to have a good tool Like MySQL workbench installed. Also, if you have installed MySQL using XAMP, you will already have phpMyAdmin available on http://localhost. So you can work with that.

You can download and install MySQL workbench from this link.

Creating a Database in MySQL

Before we connect to a MySQL database we need to have one. So let’s just use our MySQL explorer tool, in our MySQL workbench to create a database.

Connect to localhost…

CREATE DATABASE bookstore;

This will create a database named bookstore for us.

Creating a table in MySQL

Let’s create a table in our database.

CREATE TABLE bookstore.books (
id INT UNIQUE AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
author VARCHAR(25) NOT NULL,
category VARCHAR(25) NOT NULL,
in_stock tinyint DEFAULT 1,
created_at datetime,
PRIMARY KEY (id)
)

Setting up Node.js project

If you do not have Node.js installed you can get it from this link.

Create a new folder named bookstore

mkdir bookstore
cd bookstore

Initialize a new Node.js project…

npm init

Answer the questions it asks.

Next, we will install the node-mysql driver. This will provide us with the interface to connect to MySQL and interact with it using Node.js.

npm install mysql --save

This will install the package and save it to package.json

Connecting to MySQL using Node.js

Now that we have everything set up, let create a connection file which will help us to connect to MySQL server. Create a folder named connections, this is where all the connections to different databases should go, it’s not required but it’s a good practice to organize in such manner.

Under connections create mysql.js, this is where our connection code will go.

There are two ways using which we can create a connection using this library, the first one is by creating a single connection object, but this approach is not good for production applications as concurrent requests will start failing.
The second and a better way to do this is to create a pool object. As the name suggests, this creates a pool of connections and smartly acquires and releases them.

connections/mysql.js
'use strict';

const mysql = require('mysql');

class MySql{
    constructor(){
        this.pool = mysql.createPool({
            connectionLimit: 50,
            connectTimeout: 60 * 60 * 1000,
            aquireTimeout: 60 * 60 * 1000,
            timeout: 60 * 60 * 1000,
            host: 'localhost',
            user: 'root',
            password: '',
            database: 'bookstore'
        });
    }
}

module.exports = new MySql();

Note: The configurations should ideally come from a config file. Also for production make sure you create a separate user other than root with a strong password.

Inserting data into MySQL using Node.js

Now let’s see how we can instert data into Mysql using Node.js.

Create a directory named models, this is where all our DB queries will go. Next, create a file named book.js

models/book.js
'use strict';
const mysql = require('../connection/mysql');

/**
 * Adds new book
 * Sample data
 */

const data = {
    name: "Power of Habbit",
    author: "Charles Duhigg",
    category: "Self Help"
}
module.exports.addBook = function(data, callback){
    const query = `INSERT into books (
                        name,
                        author,
                        category,
                        in_stock,
                        created_at
                    ) Values (?,?,?,?,NOW())`;
    const params = [data.name, data.author, data.category, 1];
    mysql.pool.query(query, params, (err, result)=>{
        if(err){
            return callback(err);
        }
        console.log(`result=>`, result);        
        callback(null, result);
    });
}

We do not have to explicitly release connection back to the pool since the pool object is doing that for us internally. You might have noticed the use of ?. This is called a parameterized query and is used to prevent SQL injection attacks. Hence whenever dealing with user input always use parameterized query.

Querying data in MySQL using Node.js

models/book.js
'use strict';

const mysql = require('../connection/mysql');

/**
 * Returns all books
 */

module.exports.getBooks = function(callback){
    const query = `Select * from books`;
    const params = [];
    mysql.pool.query(query, params, (err, rows)=>{
        if(err){
            return callback(err);
        }
        console.log(`result=>`, rows);        
        callback(null, rows);
    });
}

MySQL update using Node.js

models/book.js
'use strict';
const mysql = require('../connection/mysql');


/**
 * Updates book stock
 */

module.exports.updateStock = function(id, in_stock, callback){
    const query = `Update books set
                    in_stock = ? where id = ?`;
    const params = [in_stock, id];
    mysql.pool.query(query, params, (err, result)=>{
        if(err){
            return callback(err);
        }
        console.log(`result=>`, result);        
        callback(null, result);
    });
}

MySQL delete using Node.js

models/book.js
'use strict';
const mysql = require('../connection/mysql');

/**
 * Deletes a book entry
 */

module.exports.deleteBook = function(id, callback){
    const query = `DELETE from books where id = ?`;
    const params = [id];
    mysql.pool.query(query, params, (err, result)=>{
        if(err){
            return callback(err);
        }
        console.log(`result=>`, result);        
        callback(null, result);
    });
}

Example

Above we have already seen how to perform all four basic CRUD operations on MySQL using Node.js. Now if you are looking to see how can we invoke these functions then look no further than below.

example.js
'use strict';
const book= require('../models/book');

book.getBooks((err, results)=>{
     if(err){
        console.log(err);
      }
      console.log('books=>', results)
});

Great, then so now we have learned how we can do some basic operations on MySQL using node.js, let’s see the other queries that you might have to use frequently whilst working with MySQL.

MySQL Add Index Query

Indexing is used to speed up querying to Database, we must index columns which would be frequently queried upon (Included in where clause a lot of times). In our case category is something that we will use frequently to filter books. So let’s create an index on that column.

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Eg:
Create index book_cat on
books (category);

MySQL alter statement

Alter statement will be used whenever you want to modify the structure of the table. Like adding a new column, changing the column type, etc.

Mysql Alter statement to add new column.

ALTER TABLE table_name
ADD column_name datatype;

Mysql Alter statement to modify column datatype.

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Mysql Alter statement to drop column.

ALTER TABLE table_name
DROP COLUMN column_name;

MySQL Join Statement

Joins are used when you want to combine data from two or more tables. Here is an example of an inner join.

SELECT table1.column_name(s), table2.column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

This statement selects the rows from table1 and table two where the Join condition matches.

Conclusion

This was a full-fledged beginners tutorial on Node.js and MySQL, hope you got some key takeaways from this and use this information to learn more and push further.

Keep Learning…

Did I miss something?

I have tried to cover as much as I can that a beginner would need to get started with node mysql. If you are one and if you think I missed out something, then let me know in the comments below or you can connect to me on twitter. My twitter handle is @rahil471.

About

Engineer. Blogger. Thinker. Loves programming and working with emerging tech. We can also talk on Football, Gaming, World Politics, Monetary Systems.

Get notified on our new articles

Subscribe to get the latest on Node.js, Angular, Blockchain and more. We never spam!
First Name:
Email:

Leave a Comment