Inserting Rows into a Table from Node.js

Summary: in this tutorial, you will learn how to insert one or more rows into a table from the Node.js application.

This tutorial picks up where the Connecting to MySQL server from a Node.js application tutorial left off.

To insert a new row into a table, you follow these steps:

1. First, connect to the MySQL database.
2. Second, execute an INSERT statement by calling the query() method on a connection object.
3. Third, close the database connection.

Insert one row into a table

Create a new insert.js file in the project directory and add the following code to the file to insert a new row into the todos table:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  // insert statment
  let sql = `INSERT INTO todos(title,completed)
           VALUES('Learn how to insert a new row',true)`;

  // execute the insert statment
  connection.query(sql);

  // close the database connection
  connection.end();
});Code language: JavaScript (javascript)

How it works.

First, use the mysql module to connect to the MySQL server:

let mysql = require('mysql');Code language: JavaScript (javascript)

Second, create a connection object with the information provided in the .env file:

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});Code language: JavaScript (javascript)

The .env file looks like the following:

DB_HOST=localhost
DB_PORT=3306
DB_USER=user
DB_PASSWORD=password
DB_NAME=todoappCode language: plaintext (plaintext)

Note that you need to replace the user and password with the ones you use to connect to the MySQL server.

Third, connect to the MySQL server using the connect() method:

connection.connect((err) => {
  if (err) return console.error(err.message);

  // ...

});Code language: JavaScript (javascript)

Fourth, construct the insert statement:

let sql = `INSERT INTO todos(title,completed)
           VALUES('Learn how to insert a new row',true)`;Code language: JavaScript (javascript)

Fifth, execute the INSERT statement using the query() method:

connection.query(sql);Code language: JavaScript (javascript)

Finally, close the database connection:

connection.end();Code language: JavaScript (javascript)

To run the insert.js program, you use the following command:

node --env-file .env insert.jsCode language: plaintext (plaintext)

Verify the insert

First, connect to the MySQL server:

mysql -u root -pCode language: plaintext (plaintext)

Second, retrieve data from the todos table:

select * from todos;Code language: SQL (Structured Query Language) (sql)

Output:

+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         1 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that the program has inserted a new row into the todos table successfully.

Inserting a row and returning the inserted id

Create a new file insert_return.js and add the following code to the file to insert a new row into the todos table and returns the inserted ID:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  // insert statment
  let sql = `INSERT INTO todos(title,completed)
             VALUES(?,?)`;

  let todo = ['Insert a new row with placeholders', false];

  // execute the insert statment
  connection.query(sql, todo, (err, results, fields) => {
    if (err) return console.error(err.message);

    console.log('Todo Id:' + results.insertId);
  });

  // close the database connection
  connection.end();
});
Code language: JavaScript (javascript)

How it works.

First, construct the query that uses question marks (?) as the placeholders:

let sql = `INSERT INTO todos(title,completed)
           VALUES(?,?)`;Code language: JavaScript (javascript)

Second, execute the query and pass data to it in the query() method and display the inserted ID if the query succeeded:

connection.query(sql, todo, (err, results, fields) => {
  if (err) return console.error(err.message);

  console.log('Todo Id:' + results.insertId);
});
Code language: JavaScript (javascript)

Execute the insert_return.js file:

node --env-file .env insert_return.jsCode language: plaintext (plaintext)

Output:

Todo Id:2Code language: JavaScript (javascript)

Insert multiple rows

Create a new file called insert_multiple.js and use the following code to insert multiple rows into the todos table:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  // insert statment
  let sql = 'INSERT INTO todos(title, completed) VALUES ?';

  let todos = [
    ['Master Node.js MySQL', false],
    ['Build Node.js / MySQL App', true],
  ];

  // execute the insert statment
  connection.query(sql, [todos], (err, results, fields) => {
    if (err) return console.error(err.message);

    console.log(`Inserted Rows: ${results.affectedRows}`);
  });

  // close the database connection
  connection.end();
});
Code language: JavaScript (javascript)

How it works.

First, construct the INSERT statement with one question mark(?):

let sql = `INSERT INTO todos(title, completed) VALUES ?`;Code language: JavaScript (javascript)

The question mark (?) in the INSERT statement represents multiple-row data that will come from an array.

Second, prepare data for insertion:

let todos = [
    ['Master Node.js MySQL', false],
    ['Build Node.js / MySQL App', true],
];Code language: JavaScript (javascript)

Third, execute the INSERT statement with the data that comes from the todos array:

connection.query(sql, [todos], (err, results, fields) => {
  if (err) return console.error(err.message);

  console.log('Row inserted:' + results.affectedRows);
});Code language: JavaScript (javascript)

You can access the number of rows inserted via the affectedRows property of the results object.

node --env-file .env insert_multiple.jsCode language: JavaScript (javascript)

Output:

Inserted Rows: 2

The output indicates that two rows were inserted.

In this tutorial, you have learned how to insert one or more rows into a MySQL table from a Node.js program.