Node.js MySQL Insert Example

In this post we'll see examples of inserting records into table using Node.js and MySQL. We'll be using the Promise Based API provided by MySQL2 library and a connection pool to connect to database in the examples provided here.

Refer Node.js - MySQL Connection Pool to get more information about using MySQL Connection Pool with Node.js

Table used

Table used for the example is Employee table with columns as- id, name, age, join_date.


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

Id is auto incremented so we don't need to send that data from our code.

Node.js insert record MySQL example

We'll keep the DB connection configuration in a separate file that can be used wherever it is needed by importing it.

util\database.js

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

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'admin',
    database: 'node',
    waitForConnections: true, 
    connectionLimit: 10,
  });

module.exports = pool;

As you can see pool object is exported here so that it can be used in other files.

app.js

In this file let's create a function to insert data into the Employee table in MySQL.

const pool = require('./util/database');

async function insertEmployee(){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ('Ishan', '2023-11-22', 31)";
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.query(sql);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

// call the function
insertEmployee();

Important points to note here-

insertEmployee() function is a async function as we are using async/await (Promise based API) rather than callback based API.
We are using await with pool.getConnection() method.
By using array destructuring we get the returned values for result and fields.
fields variable contains extra meta data about results, if available.
result contains a ResultSetHeader object, which provides details about the operation executed by the server.
After the task, connection is released using conn.release() which means connection goes back to the pool.

On running the file-

>node app.js

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

If you want to get the number of rows inserted you can get it using result.affectedRows and to get the id of the inserted row you can use result.insertId

Using parameterized Insert query

In the above example problem is that insert query will insert the same record as the data is hardcoded. To make it more generic you can use a parameterized query with the placeholders for the values that are passed later.

const pool = require('./util/database');

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

// call the function
insertEmployee('Ishan', '2024-03-24', 28);
insertEmployee('Rajesh', '2023-06-17', 34);

Important points to note here-

Prepared statement is used now where placeholders are used to pass values later.
Values are passed in an array which is also passed along with the query.
conn.execute() is used here rather than conn.query() as execute helper prepares and queries the statement.

Inserting multiple records - Node.js and MySQL

In the above example parameterized query is used which is definitely a step forward to using hardcoded data. If you have multiple records to be inserted simultaneously then you can use single question mark '?' which represents multiple rows of data coming from an array.

app.js

const pool = require('./util/database');

async function insertEmployee(data){
  const sql = "INSERT INTO EMPLOYEE (name, join_date, age) values ?";
  
  try{
    const conn = await pool.getConnection();
    const [result, fields] = await conn.query(sql, [data]);
    console.log(result);
    console.log(fields);
    conn.release();
  }catch(err){
    console.log(err);
  }
}

const records = [
  ['Ishan', '2024-03-24', 28], 
  ['Rajesh', '2023-06-17', 34]
]
// call the function
insertEmployee(records);

On running it

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 1,
  info: 'Records: 2  Duplicates: 0  Warnings: 0',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

Undefined

As you can see affected rows count is 2.

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

Related Topics

You may also like-

Tech Tutorials

Monday, July 15, 2024