Using MySQL with Node.js and the mysql JavaScript Client

James HibbardJay Raj
James Hibbard, Jay Raj
Share

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.

This popular article was updated in 2020 to reflect current practices for using MySQL with Node.js. For more on MySQL, read Jump Start MySQL.

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');

Using MySQL with Node.js & the mysql JavaScript Client

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

Creating

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

const author = { name: 'Craig Buckler', city: 'Exmouth' };
con.query('INSERT INTO authors SET ?', author, (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:

con.query(
  'UPDATE authors SET city = ? Where ID = ?',
  ['Leipzig', 3],
  (err, result) => {
    if (err) throw err;

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

Destroying

The same thing goes for a delete query:

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

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

Advanced Use

I’d like to finish off by looking at how the mysql module handles stored procedures and the escaping of user input.

Stored Procedures

Put simply, a stored procedure is prepared SQL code that you can save to a database, so that it can easily be reused. If you’re in need of a refresher on stored procedures, then check out this tutorial.

Let’s create a stored procedure for our sitepoint database which fetches all the author details. We’ll call it sp_get_authors. To do this, you’ll need some kind of interface to the database. I’m using Adminer. Run the following query against the sitepoint database, ensuring that your user has admin rights on the MySQL server:

DELIMITER $

CREATE PROCEDURE `sp_get_authors`()
BEGIN
  SELECT id, name, city FROM authors;
END $

This will create and store the procedure in the information_schema database in the ROUTINES table.

Creating stored procedure in Adminer

Note: if the delimiter syntax looks strange to you, it’s explained here.

Next, establish a connection and use the connection object to call the stored procedure as shown:

con.query('CALL sp_get_authors()',function(err, rows){
  if (err) throw err;

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

Save the changes and run the file. Once it’s executed, you should be able to view the data returned from the database:

[ [ RowDataPacket { id: 1, name: 'Michaela Lehr', city: 'Berlin' },
    RowDataPacket { id: 2, name: 'Michael Wanyoike', city: 'Nairobi' },
    RowDataPacket { id: 3, name: 'James Hibbard', city: 'Leipzig' },
    RowDataPacket { id: 4, name: 'Karolina Gawron', city: 'Wrocław' },
  OkPacket {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    serverStatus: 34,
    warningCount: 0,
    message: '',
    protocol41: true,
    changedRows: 0 } ]

Along with the data, it returns some additional information, such as the affected number of rows, insertId etc. You need to iterate over the 0th index of the returned data to get employee details separated from the rest of the information:

rows[0].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 Leipzig
Karolina Gawron lives in Wrocław

Now let’s consider a stored procedure which requires an input parameter:

DELIMITER $

CREATE PROCEDURE `sp_get_author_details`(
  in author_id int
)
BEGIN
  SELECT name, city FROM authors where id = author_id;
END $

We can pass the input parameter while making a call to the stored procedure:

con.query('CALL sp_get_author_details(1)', (err, rows) => {
  if(err) throw err;

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

This gives you the following:

[ RowDataPacket { name: 'Michaela Lehr', city: 'Berlin' } ]

Most of the time when we try to insert a record into the database, we need the last inserted ID to be returned as an out parameter. Consider the following insert stored procedure with an out parameter:

DELIMITER $

CREATE PROCEDURE `sp_insert_author`(
  out author_id int,
  in author_name varchar(25),
  in author_city varchar(25)
)
BEGIN
  insert into authors(name, city)
  values(author_name, author_city);
  set author_id = LAST_INSERT_ID();
END $

To make a procedure call with an out parameter, we first need to enable multiple calls while creating the connection. So, modify the connection by setting the multiple statement execution to true:

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

Next, when making a call to the procedure, set an out parameter and pass it in:

con.query(
  "SET @author_id = 0; CALL sp_insert_author(@author_id, 'Craig Buckler', 'Exmouth'); SELECT @author_id",
  (err, rows) => {
    if (err) throw err;

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

As seen in the above code, we have set an @author_id out parameter and passed it while making a call to the stored procedure. Once the call has been made we need to select the out parameter to access the returned ID.

Run app.js. On successful execution you should be able to see the selected out parameter along with various other information. rows[2] should give you access to the selected out parameter:

 [ RowDataPacket { '@author_id': 6 } ] ]

Note: To delete a stored procedure you need to run the command DROP PROCEDURE <procedure-name>; against the database you created it for.

Escaping User Input

In order to avoid SQL Injection attacks, you should always escape any data you receive from users before using it inside an SQL query. Let’s demonstrate why:

const userSubmittedVariable = '1';

con.query(
  `SELECT * FROM authors WHERE id = ${userSubmittedVariable}`,
  (err, rows) => {
    if(err) throw err;
    console.log(rows);
  }
);

This seems harmless enough and even returns the correct result:

 { id: 1, name: 'Michaela Lehr', city: 'Berlin' }

However, try changing the userSubmittedVariable to this:

const userSubmittedVariable = '1 OR 1=1';

We suddenly have access to the entire data set. Now change it to this:

const userSubmittedVariable = '1; DROP TABLE authors';

We’re now in proper trouble!

The good news is that help is at hand. You just have to use the mysql.escape method:

con.query(
  `SELECT * FROM authors WHERE id = ${mysql.escape(userSubmittedVariable)}`,
  (err, rows) => {
    if(err) throw err;
    console.log(rows);
  }
);

You can also use a question mark placeholder, as we did in the examples at the beginning of the article:

con.query(
  'SELECT * FROM authors WHERE id = ?',
  [userSubmittedVariable],
  (err, rows) => {
    if(err) throw err;
    console.log(rows);
  }
);

Why Not Just USE an ORM?

Before we get into the pros and cons of this approach, let’s take a second to look at what ORMs are. The following is taken from an answer on Stack Overflow:

Object-Relational Mapping (ORM) is a technique that lets you query and manipulate data from a database using an object-oriented paradigm. When talking about ORM, most people are referring to a library that implements the Object-Relational Mapping technique, hence the phrase “an ORM”.

So this means you write your database logic in the domain-specific language of the ORM, as opposed to the vanilla approach we’ve been taking so far. To give you an idea of what this might look like, here’s an example using Sequelize, which queries the database for all authors and logs them to the console:

const sequelize = new Sequelize('sitepoint', 'user', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

const Author = sequelize.define('author', {
  name: {
    type: Sequelize.STRING,
  },
  city: {
    type: Sequelize.STRING
  },
}, {
  timestamps: false
});

Author.findAll().then(authors => {
  console.log("All authors:", JSON.stringify(authors, null, 4));
});

Whether or not using an ORM makes sense for you will depend very much on what you’re working on and with whom. On the one hand, ORMS tend to make developers more productive, in part by abstracting away a large part of the SQL so that not everyone on the team needs to know how to write super efficient database specific queries. It’s also easy to move to different database software, because you’re developing to an abstraction.

On the other hand however, it is possible to write some really messy and inefficient SQL as a result of not understanding how the ORM does what it does. Performance is also an issue in that it’s much easier to optimize queries that don’t have to go through the ORM.

Whichever path you take is up to you, but if this is a decision you’re in the process of making, check out this Stack Overflow thread: Why should you use an ORM?. Also check out this post on SitePoint: 3 JavaScript ORMs You Might Not Know.

Conclusion

In this tutorial, we’ve installed the mysql client for Node.js and configured it to connect to a database. We’ve also seen how to perform CRUD operations, work with prepared statements and escape user input to mitigate SQL injection attacks. And yet, we’ve only scratched the surface of what the mysql client offers. For more detailed information, I recommend reading the official documentation.

And please bear in mind that the mysql module is not the only show in town. There are other options too, such as the popular node-mysql2.

FAQs on Using MySQL with Node.js

Can I use MySQL with JavaScript?

Yes, you can use MySQL with JavaScript. JavaScript is a versatile language that can be used on the server-side with technologies like Node.js to interact with MySQL databases. To work with MySQL in JavaScript, you can use the mysql library or other MySQL-related packages like mysql2 or object-relational mapping (ORM) libraries like Sequelize. These libraries provide APIs to establish database connections, execute SQL queries, and work with data in MySQL databases. You can build server-side applications, web applications, or APIs that interact with MySQL using JavaScript as the programming language.

Can I use Node.js with MySQL?

es, you can use Node.js with MySQL. Node.js is a popular server-side runtime environment for JavaScript, and it has robust support for working with MySQL databases. You can use various MySQL libraries and packages to interact with MySQL databases in Node.js, including the official mysql package, mysql2, and object-relational mapping (ORM) libraries like Sequelize.
These packages allow you to establish database connections, execute SQL queries, and work with data in MySQL databases, making Node.js a versatile and powerful choice for building server-side applications, web applications, and APIs that interact with MySQL databases. This combination is widely used for developing dynamic and data-driven web applications.

How to connect Node.js with MySQL Workbench?

To connect Node.js with MySQL Workbench, you’ll need to ensure you have both Node.js and MySQL installed on your system. Node.js is a server-side runtime environment for JavaScript, while MySQL is a popular relational database management system. After installing these components, you can proceed to establish the connection.
You’ll need a Node.js module for connecting to the MySQL database. A commonly used choice is the mysql2 module, which you can install via npm with the command npm install mysql2. Once installed, you can create a JavaScript file to write your Node.js application.
In your Node.js application, you’ll need to configure the MySQL connection by specifying details like the host, username, password, and the name of the database you want to connect to. After the connection is established, you can perform various database operations using the mysql2 module. Be sure to replace the connection details in the code with the specific information for your MySQL server and database.

How to create a MySQL database in JavaScript?

To create a MySQL database using JavaScript, you can utilize the mysql2 package in a Node.js environment. First, ensure that you have both Node.js and MySQL installed on your system. Node.js is the server-side runtime environment for JavaScript, while MySQL is a widely-used relational database management system.
You’ll need to install the mysql2 package, a popular choice for connecting to MySQL databases with Node.js. This package can be easily installed via npm with the command npm install mysql2. After installation, you can proceed to write a JavaScript file to create your database.
In your JavaScript file, you’ll establish a connection to the MySQL server by providing the host, username, and password. Then, you can issue a SQL command to create a new database. In the example provided, a database named my_new_database is created. After successfully executing the SQL command, you’ll receive feedback in the form of log messages indicating whether the operation was successful. Make sure to replace the connection details and database name with your specific MySQL server credentials and desired database name. Finally, run your Node.js application with the node command to execute the database creation script.
By following these steps, you can use JavaScript with the mysql2 package to create a MySQL database within your Node.js application, enabling you to work with structured data for various web and application projects.

How to query in MySQL using JavaScript?


To query a MySQL database using JavaScript, you can use the mysql2 package in a Node.js environment. Before proceeding, ensure that Node.js is installed on your machine. If not, you can download it from the official website. Additionally, install MySQL, including the MySQL server and MySQL Workbench, which can be obtained from the official MySQL website.
After having Node.js and MySQL set up, you’ll need to install the mysql2 package. This package is widely used for connecting to MySQL databases with Node.js and can be effortlessly installed via npm. Once installed, you can proceed to write your JavaScript code to perform MySQL queries.
In your JavaScript file, you’ll create a connection to the MySQL server by specifying the host, username, password, and the database you want to interact with. The code example provided illustrates connecting to the MySQL server and executing a sample SELECT query. Replace the connection details and SQL query with your specific MySQL server credentials and the query you wish to run. When you run your Node.js application, the code will connect to the MySQL database, execute the query, and return the results, making it suitable for retrieving, updating, or manipulating data in your database using JavaScript.