How to INSERT data into a MySQL database using Node.js

A guide on how to use Prepared Statements in Node.js to insert data into a table in a MySQL database safely and effectively with multiple parameters.

In this article, you'll learn how to use Node.js to connect to a MySQL database and insert data into a table using Prepared Statements with multiple parameters, helping to protect your data from SQL injection attacks.

const mysql = require('mysql');

// Create a connection to the MySQL database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'test_db'
});

// Connect to MySQL
connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to the MySQL database');
  
  // INSERT statement with Prepared Statement
  const insertQuery = 'INSERT INTO students (name, age, address) VALUES (?, ?, ?)';
  const params = ['John Doe', 25, '123 Main St'];

  // Execute the INSERT statement
  connection.query(insertQuery, params, (error, results) => {
    if (error) throw error;
    console.log(`${results.affectedRows} record(s) inserted successfully`);
    
    // Close the connection after completing
    connection.end();
  });
});

Detailed explanation:

1. const mysql = require('mysql');: Imports the mysql module to enable connecting and interacting with MySQL.
2. const connection = mysql.createConnection({...}): Creates a connection to the MySQL database with the required information such as host, user, password, and database.
3. connection.connect((err) => {...}): Connects to MySQL and handles any connection errors.
4. const insertQuery = 'INSERT INTO students (name, age, address) VALUES (?, ?, ?)';: Defines the INSERT statement with parameters (the ? placeholders represent the values to be inserted).
5. const params = ['John Doe', 25, '123 Main St'];: Defines an array containing the values to be inserted into the table.
6. connection.query(insertQuery, params, (error, results) => {...}): Executes the INSERT statement with the specified parameters.
7. console.log(...): Prints the number of records successfully inserted.
8. connection.end();: Closes the connection to the database after completing the operation.

System Requirements:

• Node.js
• Library: mysql

How to install the libraries needed to run the Node.js code above:

Use npm to install the mysql library:

npm install mysql

Tips:

• Make sure your MySQL server is running before attempting to connect.
• Double-check your connection details like host, user, password, and database to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.