Can we connect mysql with node js?

Summary: in this tutorial, you will learn how to connect to the MySQL database server from a node.js application.

Installing node.js driver for MySQL

There are some options to interact with MySQL from a node.js application. In this tutorial, we will show you how to use node.js driver for MySQL called  mysqljs/mysql.

First, create a folder for storing the node.js app e.g., node-mysql and use the npm init command to create the package.json file:

npm init

Second, install node.js for MySQL package by using the following command:

npm install mysql

Third, create the connect.js inside of the node-mysql folder for storing the code that connects to the MySQL database server.

We will use the todoapp database for demonstration, therefore, you should create the database in your MySQL database server by running the following CREATE DATABASE statement:

CREATE DATABASE todoapp;
Code language: SQL [Structured Query Language] [sql]

Once the database is created, you are ready to connect to it from the Node.js application.

First, import the mysql module by using the following statement:

let mysql = require['mysql'];
Code language: JavaScript [javascript]

Second, create a connection to the MySQL database by calling the createConnection[] method and providing the detailed information on MySQL server such as host, user, password and database as follows:

let connection = mysql.createConnection[{
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'todoapp'
}];
Code language: JavaScript [javascript]

In this example, we created a connection to todoapp database in the local database server.

Third, call the connect[] method on the connection object to connect to the MySQL database server:

connection.connect[function[err] {
  if [err] {
    return console.error['error: ' + err.message];
  }

  console.log['Connected to the MySQL server.'];
}];
Code language: JavaScript [javascript]

The connect[] method accepts a callback function that has the err argument which provides the detailed error if any error occurred.

Let’s test connect.js program.

> node connect.js
Connected to the MySQL server
Code language: JavaScript [javascript]

If you see the message “connected to the MySQL server”, then congratulation, you have been successfully connected to MySQL database server from the node.js application.

Suppose the todoapps database does not exist in the database server and you try to connect to it, you will get an error message:

> node connect.js
error: ER_BAD_DB_ERROR: Unknown database 'todoapps'
Code language: JavaScript [javascript]

Notice that every method which you invoke on the connection object are queued and executed in sequence.

Closing database connection

To close a database connection gracefully, you call the end[] method on the connection object.

The end[] method ensures that all remaining queries are always executed before the database connection closed.

connection.end[function[err] {
  if [err] {
    return console.log['error:' + err.message];
  }
  console.log['Close the database connection.'];
}];
Code language: JavaScript [javascript]

To force the connection close immediately, you can use the destroy[] method. The destroy[] method guarantees that no more callbacks or events will be triggered for the connection.

connection.destroy[];
Code language: JavaScript [javascript]

Note that the destroy[] method does not take any callback argument like the end[] method.

Pooling connections

The MySQL driver for node.js module provides you with a built-in connection pooling feature. Suppose, you want to create a connection pool with 5 connections:

var pool = mysql.createPool[{
    connectionLimit: 5,
    host: 'localhost',
    user: 'root',
    password: '', 
    database: 'todoapp'
}];
Code language: JavaScript [javascript]

To get a connection from the pool, you use the getConnection[] method:

pool.getConnection[function[err, connection] {
  // execute query
  // ...
}];
Code language: JavaScript [javascript]

To return a connection to the pool after you are done with it, you can call the connection.release[]. After that, the connection will be available in the pool and ready to use again by someone else.

pool.getConnection[function[err, connection] {
  // execute query
  // ...
  connnection.release[];
}];
Code language: JavaScript [javascript]

To close a connection and remove it from the pool, you use the connection.destroy[] method. A new connection will be created in the pool if one is needed next time.

It’s important to note that the pool will create connections lazily. For example, if you configure the pool with 5 connections but you use only 2 connections simultaneously, then the pool created only 2 connections.

To close all the connections in the pool, you use the end[] method of the pool object as follows:

pool.end[function[err] {
  if [err] {
    return console.log[err.message];
  }
  // close all connections
}];
Code language: JavaScript [javascript]

In this tutorial, you have learned how to connect to a MySQL database from a node.js application.

Was this tutorial helpful?