How to Connect to MySQL Database from Node.Js

Last updated on November 21, 2022

In this article, you will learn how to connect to the MySQL server database using Node Js MySQL driver. We gonna use a MySQL driver called “MySQL”. It is written in JavaScript, does not require compiling, and is 100% MIT-licensed.

MySQL is the most popular database system used with other programming languages like PHP, Java..etc. MySQL is ideal for both small and large applications. Which is developed distributed, and supported by Oracle Corporation. MySQL is very fast, reliable, and free to download and use.

Installing node-mysql

Install node MySQL driver with npm as shown below

$ mkdir node-mysql-test
$ cd node-mysql-test
$ npm install mysql

Database Table and sample data

For this tutorial, we will be using a database called “mysql-demo” and a table called todos. Here is a dump of the database, so that you can get up and running quickly

CREATE TABLE  `tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO tasks (id, title, description) VALUES
(1, 'BWG', 'Start project'),
(2, 'CRCL', 'Project completed, close all the tickets'),
(3, 'Blog', 'Publish new post'),

Sample code to get started.

Let’s create a file called app.js and import MySQL driver into that page. You can establish a connection to MySQL in two different methods, first one is the basic connection method which is suitable for development and testing, and the second one is suitable for production which uses connection pooling. Connection pooling is a technique to maintain the cache of a database connection so that the connection can be reused after releasing it. The basic version may not work for concurrent users which is why a pooling connection is preferred which can handle thousands of concurrent users.

Simple connection

 var mysql      = require('mysql');
 var connection = mysql.createConnection({
   host     : 'localhost',
   user     : 'root',
   password : '',
   database : 'yourDBName'
 });

 connection.connect(function(err){
    if(err) {
        console.log("Error connecting database ... \n\n");
     } else {
       console.log("Database is connected ... \n\n");       
     }
 });

Pooled connection

var mysql = require('mysql');

var pool  = mysql.createPool({
   host     : 'localhost',
   user     : 'root',
   password : '',
   database : 'yourDBName'
});

pool.getConnection(function(err, connection) {
    // Use the connection
    connection.query( 'SELECT * FROM todos', function(err, rows) {
       if(err) throw err;
       console.log('Data received from Db:\n');
       console.log(rows);
       connection.release(); // return the connection to the pool.
    });
});

pool.end(function (err) {
    // all connections in the pool have ended
});

Then use the following command to run the code

 node app.js

Executing Queries – Reading, Creating, Updating, Destroying

As shown above, once the connection is established we will use the connection variable to execute a query against the database table(s).

//For reading data from table
connection.query('SELECT * FROM todos',function(err,rows){
  if(err) throw err;
  console.log('Data received from Db:\n');
  console.log(rows);
});

//For creating a new row in table
var todo = { title: 'Delete Comments', description: 'Delete all the spam comments from websites' };
connection.query('INSERT INTO todos SET ?', todo, function(err,res){
  if(err) throw err;
  console.log('Last insert ID:', res.insertId);
});

//For updating data in the table
connection.query('UPDATE todos SET title = ? Where ID = ?',["Delete spam comments", 1],function (err, result) {
    if (err) throw err;
    console.log('Changed ' + result.changedRows + ' rows');
  }
);

//For deleting a row
connection.query('DELETE FROM todos WHERE id = ?',[1], function (err, result) {
    if (err) throw err;
    console.log('Deleted ' + result.affectedRows + ' rows');
  }
);

In order to avoid SQL Injection attacks, you should always escape any user-provided data before using it inside a SQL query. You can do so using the mysql.escape(), connection.escape() or pool.escape() methods. If you build your likes using wildcards as we did above you don’t have to use escape methods MySQL driver module will take of it.