Using MySQL with NodeJS

In this tutorial, we’ll have a look at getting started with the mysql module — a Node.js driver for MySQL, written in JavaScript. I’ll explain how to use the module to connect to a MySQL database, perform the usual CRUD operations, before examining stored procedures and escaping user input.

Installing the “mysql” Module:

Now let’s take a closer look at each of those steps. First of all, we’re using the command line to create a new directory and navigate to it. Then we’re creating a package.json file using the command npm init –y. The -y flag means that npm will use only defaults and not prompt you for any options. This step also assumes that you have Node and npm installed on your system.

After that, we’re installing the mysql module from npm and saving it as a project dependency.

mkdir mysql-test
cd mysql-test
npm install mysql -y

Getting Started:

Before we get on to connecting to a database, it’s important that you have MySQL installed and configured on your machine.

The next thing we need to do is to create a database and a database table to work with. I’ll be using MySQL Workbench as it provides good graphical user interface (GUI), you can use that or you can use phpMyAdmin. For this article I’ll be using a database called company and a table called employees. Here’s a dump of the database, so that you can get up and running quickly, if you wish to follow along:

CREATE DATABASE company;
USE company;
CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  location varchar(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO employees (id, name, location) VALUES
(1, 'Ibad', 'Pakistan'),
(2, 'Jay', 'India'),
(3, 'Jade', 'Germany'),
(4, 'Lesley', 'Scotland');

SQL-Query for MySQL Workbench

Connecting to the Database:

Now, let’s create a file called app.js in our mysql-test directory and see how to connect to MySQL from Node.js.

// app.js
const mysql = require('mysql');

// First you need to create a connection to the db
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456789',
});

connection.connect((err) => {
  if(err){
    console.log('Error connecting to Db');
    return;
  }
  console.log('Connection established');
});

connection.end((err) => {
  // The connection is terminated gracefully
  // Ensures all previously enqueued queries are still
  // before sending a COM_QUIT packet to the MySQL server.
});

Now open up a terminal and enter node app.js. Once the connection is successfully established you should be able to see the ‘Connection established’ message in the console. If something goes wrong (for example you enter the wrong password), a callback is fired, which is passed an instance of the JavaScript Error object (err). Try logging this to the console to see what additional useful information it contains.

Executing Queries:

Reading:

Now that you know how to establish a connection to MySQL from Node.js, let’s see how to execute SQL queries. We’ll start by specifying the database name (company) in the createConnection command.

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '123456789',
  database: 'company',
});

Once the connection is established we’ll use the connection variable to execute a query against the database table employees.

connection.query('SELECT * FROM employees', (err,rows) => {
  if(err) throw err;

  console.log('Data received from Db:\n');
  console.log(rows);
});

When you run app.js, you should be able to see the data returned from database logged to the terminal.

[ { id: 1, name: 'Ibad', location: 'Australia' },
  { id: 2, name: 'Jay', location: 'India' },
  { id: 3, name: 'Jade', location: 'Germany' },
  { id: 4, name: 'Lesley', location: 'Scotland' } ]

Data returned from the MySQL database can be parsed by simply lopping over the rows object.

rows.forEach( (row) => {
  console.log(`${row.name} is in ${row.location}`);
});

Creating:

You can execute an insert query against a database, like so:

const employee = { name: 'siddiqui', location: 'Canada' };
connection.query('INSERT INTO employees SET ?', employee, (err, res) => {
  if(err) throw err;

  console.log('Last insert ID:', res.insertId);
});

Note: how we can get the ID of the inserted record using the callback parameter.

Updating:

Similarly, when executing an update query, the number of rows affected can be retrieved using result.affectedRows:

connection.query(
  'UPDATE employees SET location = ? Where ID = ?',
  ['South Africa', 5],
  (err, result) => {
    if (err) throw err;

    console.log(`Changed ${result.changedRows} row(s)`);
  }
);

Destroying:

Same thing goes for a delete query:

connection.query(
  'DELETE FROM employees WHERE id = ?', [5], (err, result) => {
    if (err) throw err;

    console.log(`Deleted ${result.affectedRows} row(s)`);
  }
);

Conclusion:

In this tutorial, we’ve performed the CRUD operations using NodeJS and MySQL client. For more detailed information, I would recommend reading the official documentation. There are other options too, such as node-mysql2 and node-mysql-libmysqlclient.

Have you used any of these options for connecting to MySQL from Node.js? I would love hear them. Do let us know your thoughts, suggestions and corrections in the comments below!