Node.js MySQL: Insert Data into a Table

Welcome to The Coding College! In this tutorial, you’ll learn how to insert data into a MySQL table using Node.js. This operation is essential for storing user inputs or application-generated data into a database.

Prerequisites

Before starting, ensure you have:

  1. Node.js Installed: Download Node.js.
  2. MySQL Installed: Download MySQL.
  3. Database and Table Ready: Follow our Node.js MySQL Create Table tutorial to create a table.

Step 1: Install the MySQL Module

Install the MySQL Node.js package by running:

npm install mysql

Step 2: Connect to the MySQL Database

Create a file named insertData.js and set up a connection to your MySQL database:

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your-password',
  database: 'mydatabase', // Replace with your database name
});

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

Step 3: Write an Insert Query

Use the INSERT INTO SQL statement to add data to your table. Here’s an example to insert data into a users table:

const insertQuery = `
  INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')
`;

Step 4: Execute the Insert Query

Run the query using the query() method:

connection.query(insertQuery, (err, result) => {
  if (err) throw err;
  console.log('Data inserted successfully!');
});

Step 5: Use Placeholders for Dynamic Data

To insert dynamic data securely, use placeholders (?) to prevent SQL injection:

const userData = ['Jane Doe', '[email protected]'];
const insertQuery = `INSERT INTO users (name, email) VALUES (?, ?)`;

connection.query(insertQuery, userData, (err, result) => {
  if (err) throw err;
  console.log('Data inserted successfully!');
});

Step 6: Close the Connection

Always close the database connection after completing your operations:

connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Full Code

Here’s the complete script for inserting data into a MySQL table:

const mysql = require('mysql');

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

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Connected to the MySQL database!');
});

// Insert data into the table
const userData = ['Jane Doe', '[email protected]'];
const insertQuery = `INSERT INTO users (name, email) VALUES (?, ?)`;

connection.query(insertQuery, userData, (err, result) => {
  if (err) throw err;
  console.log('Data inserted successfully!');
});

// Close the connection
connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.message);
    return;
  }
  console.log('Connection closed.');
});

Step 7: Run the Script

Execute the script using Node.js:

node insertData.js

If successful, the output will indicate that data has been inserted:

Connected to the MySQL database!  
Data inserted successfully!  
Connection closed.

Advanced: Insert Multiple Records

To insert multiple rows, use an array of arrays:

const multipleUsers = [
  ['Alice', '[email protected]'],
  ['Bob', '[email protected]'],
  ['Charlie', '[email protected]'],
];

const insertMultipleQuery = `INSERT INTO users (name, email) VALUES ?`;

connection.query(insertMultipleQuery, [multipleUsers], (err, result) => {
  if (err) throw err;
  console.log('Multiple records inserted successfully!');
});

Best Practices

  1. Use Prepared Statements: Protect against SQL injection.
  2. Error Handling: Always check for and handle errors in queries.
  3. Environment Variables: Store sensitive credentials like username and password in a .env file.

Conclusion

Inserting data into a MySQL table using Node.js is a crucial step for dynamic applications. By following this tutorial, you can securely add records to your database.

For more coding and programming tutorials, visit The Coding College. We’re here to guide you through your learning journey with practical examples and best practices!

Leave a Comment