Node.js - How to Connect to MySQL

In this tutorial we'll see how to connect to MySQL database from Node.js application.

Prerequisite for this tutorial is that you already have MySQL installed in your system.

Installing MySQL driver

To connect to MySQL from NodeJS you need to install MySQL driver for Node.js. We'll use MySQL2 driver which can be installed using the following command.

npm install --save mysql2

Creating connection from NodeJS app

Once mysql2 is installed you can use that driver to connect to MySQL and execute queries through your NodeJS app.

For creating a connection you can use createConnection() method, you can either pass URI or use config object with connection options.

1. Using createConnection(connectionUri)

// Get the client
const mysql = require('mysql2');

const conn = mysql.createConnection(
  'mysql://USER_NAME:PASSWORD@localhost:3306/node'
);

Replace USER_NAME and PASSWORD with your MySQL configured user name and password. Port number used is the default 3306 and it is connecting to DB named node which I have created in MySQL.

2. Using createConnection(config)

You can also call createConnection() by passing a config object which has the required connection options. This is the preferred way to create a connection.

// Get the client
const mysql = require('mysql2');

//create connection to database
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'USER_NAME',
  password: 'PASSWORD',
  database: 'node', 
  port: 3306
})

Once you have the connection object you can use that to call connection() method to explicitly establish a connection with MySQL database.

conn.connect((err) => {
  if(err){
    console.error(err);
    return;
  }
  console.log("connected to DB");
})

Check this post- Node.js - Connect to MySQL Promise API to connect to MySQL database from Node.js using Promise based API provided by mysql2 driver.

Executing query using connection

Here is a complete example where INSERT statement is used to insert a record into employee table which is created in node schema.

CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

database.js

// Get the client
const mysql = require('mysql2');

//create connection to database
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'node', 
  port: 3306
})

conn.connect((err) => {
  if(err){
    console.error(err);
    return;
  }
  console.log("connected to DB");
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Rajesh', '2022-06-17', 34)";
  // now run query
  conn.query(sql, (err, result, fields) => {
    if(err){
        console.error(err);
        return;
    }
    console.log(result);
    console.log(fields);
  });
})

On running it

>node database.js

connected to DB
ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 1,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

undefined

Here note that result and fields provide the following information.

result: Contains a ResultSetHeader object, which provides details about the operation executed by the server.
fields: Contains extra meta data about the operation, if available

You can also implicitly establish a connection by directly invoking a query, no need to explicitly establish a connection using connection() method.

Following code also connects to DB and inserts a record though connect() method is omitted.

// Get the client
const mysql = require('mysql2');

//create connection to database
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'admin',
  database: 'node', 
  port: 3306
})

const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Rajesh1', '2022-06-17', 37)";
conn.query(sql, (err, result, fields) => {
  if(err){
      console.error(err);
      return;
  }
  console.log(result);
  console.log(fields);
});

That's all for this topic Node.js - How to Connect to MySQL. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Related Topics

You may also like-

Tech Tutorials

Tuesday, June 25, 2024