Using MySQL with Node.js and the mysql JavaScript Client

NoSQL databases are rather popular among Node developers, with MongoDB (the "M" in the MEAN stack) leading the pack. When starting a new Node project, however, you shouldn't just accept Mongo as the default choice. Rather, the type of database you choose should depend on your project's requirements. If, for example, you need dynamic table creation, or real-time inserts, then a NoSQL solution is the way to go. If your project deals with complex queries and transactions, on the other hand, an SQL database makes much more sense.

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

Quick Start: How to Use MySQL in Node

If you've arrived here looking for a quick way to get up and running with MySQL in Node, we've got you covered!

Here's how to use MySQL in Node in five easy steps:

1. Create a new project: mkdir mysql-test && cd mysql-test.
2. Create a package.json file: npm init -y.
3. Install the mysql module: npm install mysql.
4. Create an app.js file and copy in the snippet below (editing the placeholders as appropriate).
5. Run the file: node app.js. Observe a “Connected!” message.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'database name'
});
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected!');
});

Installing the mysql Module

Now let's take a closer look at each of those steps.

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

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 defaults without going through an interactive process.

This step also assumes that you have Node and npm installed on your system. If this is not the case, then check out this SitePoint article to find out how to do that: Install Multiple Versions of Node.js using nvm.

After that, we're installing the mysql module from npm and saving it as a project dependency. Project dependencies (as opposed to devDependencies) are those packages required for the application to run. You can read more about the differences between the two here.

If you need further help using npm, then be sure to check out this guide, or ask in our forums.

Getting Started

Before we get on to connecting to a database, it's important that you have MySQL installed and configured on your machine. If this is not the case, please consult the installation instructions on their home page.

The next thing we need to do is to create a database and a database table to work with. You can do this using a
graphical interface, such as Adminer, or using the command line. For this article I'll be using a database called sitepoint and a table called authors. Here's a dump of the database, so that you can get up and running quickly if you wish to follow along:

CREATE DATABASE sitepoint CHARACTER SET utf8 COLLATE utf8_general_ci;
USE sitepoint;

CREATE TABLE authors (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  city varchar(50),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO authors (id, name, city) VALUES
(1, 'Michaela Lehr', 'Berlin'),
(2, 'Michael Wanyoike', 'Nairobi'),
(3, 'James Hibbard', 'Munich'),
(4, 'Karolina Gawron', 'Wrocław');

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.

const mysql = require('mysql');

// First you need to create a connection to the database
// Be sure to replace 'user' and 'password' with the correct values
const con = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
});

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

con.end((err) => {
  // The connection is terminated gracefully
  // Ensures all remaining queries are executed
  // Then sends a 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.

Using nodemon to Watch the Files for Changes

Running node app.js by hand every time we make a change to our code is going to get a bit tedious, so let's automate that. This part isn't necessary to follow along with the rest of the tutorial, but will certainly save you some keystrokes.

Let's start off by installing a the nodemon package. This is a tool that automatically restarts a Node application when file changes in a directory are detected:

npm install --save-dev nodemon

Now run ./node_modules/.bin/nodemon app.js and make a change to app.js. nodemon should detect the change and restart the app.

Note: we're running nodemon straight from the node_modules folder. You could also install it globally, or create an npm script to kick it off.

Executing Queries

Reading

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

const con = mysql.createConnection({
  host: 'localhost',
  user: 'user',
  password: 'password',
  database: 'sitepoint'
});

Once the connection is established, we'll use the con variable to execute a query against the database table authors:

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

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

When you run app.js (either using nodemon or by typing node app.js into your terminal), you should be able to see the data returned from the database logged to the terminal:

[ RowDataPacket { id: 1, name: 'Michaela Lehr', city: 'Berlin' },
  RowDataPacket { id: 2, name: 'Michael Wanyoike', city: 'Nairobi' },
  RowDataPacket { id: 3, name: 'James Hibbard', city: 'Munich' },
  RowDataPacket { id: 4, name: 'Karolina Gawron', city: 'Wrocław' } ]

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

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

This gives you the following:

Michaela Lehr lives in Berlin
Michael Wanyoike lives in Nairobi
James Hibbard lives in Munich
Karolina Gawron lives in Wrocław

The post Using MySQL with Node.js and the mysql JavaScript Client appeared first on SitePoint.