How to connect to MySQL with Node.js

Let's see how you can connect to a MySQL database using Node.js, the popular JavaScript runtime environment.

Before we start, it is important to note that you must have Node.js installed on your system. Likewise, you must create a MySQL database.

Create a MySQL database

If you haven't already done so, you can create the database in any way you like. You can create it using applications such as phpMyAdmin or manually using the MySQL command line. To create it via the command line, follow these steps:

Connect to MySQL from the command line and enter the password when prompted:

mysql -u USER -p

Create a sample table, which in this case we will call employees:

CREATE DATABASE employees;

Select the database you have just created:

USE employees;

Create a table in the database:

CREATE TABLE IF NOT EXISTS employees (    
id int(11) NOT NULL AUTO_INCREMENT,    
name varchar(50),    
PRIMARY KEY (id)  
);

Insert some example data in the table:

INSERT INTO employees (nombre) VALUES ("Silvia");  
INSERT INTO employees (nombre) VALUES ("Luigi");  
INSERT INTO employees (nombre) VALUES ("Alejandro");

You can now run queries on the table.

Install node-mysql

You will need to have the node-mysql package installed, so if you don't have it installed, you will need to install it. To do this you must use the npm package manager:

npm install mysql

After installing node-mysql you will be able to connect to the database via Node.js.

Establish the MySQL connection

Create a Node.js script.

The first thing we do in this example is create a connection object to the MySQL database.

We use the table employees as an example, but replace it with the name of the database you want to use. Similarly, replace USER and PASS with the MySQL user data.

var mysql = require('mysql');  
var conexion= mysql.createConnection({      
   host : 'localhost',      
   database : 'employees',      
   user : 'USER',      
   password : 'PASS',  
});    
conexion.connect(function(err) {     
   if (err) {          
       console.error('Error de conexion: ' + err.stack);          
       return;      
   }      
   console.log('Connected to the identifier ' + conexion.threadId); 
});

Once the connection is established, you will be able to execute queries using the query method:

conexion.query('SELECT * FROM empleados', function (error, results, fields) {
      if (error)          
          throw error;        
      results.forEach(result => {          
      console.log(result);      
      });  
});

Finally, terminate the connection:

connection.end();

Silvia Mazzetta

Web Developer, Blogger, Creative Thinker, Social media enthusiast, Italian expat in Spain, mom of little 9 years old geek, founder of  @manoweb. A strong conceptual and creative thinker who has a keen interest in all things relate to the Internet. A technically savvy web developer, who has multiple  years of website design expertise behind her.  She turns conceptual ideas into highly creative visual digital products.