Node.js - Connect to MySQL Promise API

In the post Node.js - How to Connect to MySQL we have seen how to connect to MySQL using mysql2 package. There callback-based API was used to connect and to run query but mysql2 package also has a Promise based API where you don't need to provide callback functions instead you can use aync/await to make your code more readable.

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, with Promise also you can use one of the following ways to create a connections.

1. Using createConnection(connectionUri)

const mysql = require('mysql2/promise');
try {
  const connection = await mysql.createConnection(
    'mysql://USER_NAME:PASSWORD@localhost:3306/node'
  );
} catch (err) {
  console.log(err);
}

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.

const mysql = require('mysql2/promise');

try {
  const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'USER_NAME',
  password: 'PASSWORD',
  database: 'node', 
  port: 3306
  });
} catch (err) {
  console.log(err);
}

Using MYSQL2 Promise Based API Query Example

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

Employee Table

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

We'll use a separate file to keep DB properties which can then be passed to createConnection() method.

util\config.js

const config = {
  dbproperties: {
    host: 'localhost',
    user: 'root',
    password: 'admin',
    database: 'node', 
    port: 3306
  }
}

module.exports = config;

app.js

const mysql = require('mysql2/promise');
const config = require('./util/config');

async function insertEmployee(){
  const conn = await mysql.createConnection(config.dbproperties);
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Rajesh', '2022-06-17', 37)";
  try{
    const [result, fields] = await conn.execute(sql);
    console.log(result);
    console.log(fields);
  }catch(err){
    console.log(err);
  }
}

insertEmployee();

Important points to note here-

insertEmployee() function is a async function as we are using async/await now rather than callback based API.
We are using await with createConnection() method.
By using array destructuring we get the returned values for result and fields.
fields contains extra meta data about results, if available.
result contains a ResultSetHeader object, which provides details about the operation executed by the server.

On running it

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 4,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}
undefined

From the values displayed you can observe that result.insertId will give you the ID of the inserted record and result.affectedRows will give you the number of rows that are inserted.

Using parameterized query

Above example of insert query has very limited use as values are hardcoded and it can only insert that particular record. In order to make it more generic we can use a parameterized query with placeholders for the values that are passed later.

const mysql = require('mysql2/promise');
const config = require('./util/config');

async function insertEmployee(empName, joinDate, age){
    const conn = await mysql.createConnection(config.dbproperties);
    const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values (?, ?, ?)";
    const values = [empName, joinDate, age];
    try{
        const [result, fields] = await conn.execute(sql, values);
        console.log(result);
        console.log(fields);
      }catch(err){
        console.log(err);
      }
}

insertEmployee('Rajesh', '2022-06-17', 34);

Important points to note here-

insertEmployee() function is a async function and takes 3 parameters for the three fields that are to be inserted into the table. We don’t need to insert id as that will be automatically incremented by DB.
Insert query is parameterized with three placeholders for three values. These values are passed as an array.
```
const values = [empName, joinDate, age];
```
In connection.execute() method both query (String) and values (array) are passed. Using the query with placeholders and values parameters, execute() method prepares and queries the statement.

That's all for this topic Node.js - Connect to MySQL Promise API. 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