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:

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:

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:

// 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:

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:

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:

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:

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

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

Conclusion:

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!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ibad Siddiqui

Software Engineer at @GlaxoSmithKline. Writer @Coinmonks | Blockchain & Data Science Enthusiast | Traveler | Explorer