A Beginner’s Guide to Node.js and MySQL Integration

Node.js, a leading server-side programming language akin to PHP and Java, is widely used in web development. Paired with MySQL, the most preferred database for web applications, Node.js enables efficient data management. This guide offers a step-by-step tutorial on integrating MySQL with Node.js, covering essential operations such as INSERT, UPDATE, SELECT, and DELETE in MySQL database tables.

Prerequisites

Before you begin this tutorial on integrating Node.js with MySQL, ensure you have the following prerequisites in place:

• Node.js Installed: A current version of Node.js must be installed on your system. You can download it from the official Node.js website.
• MySQL Server: You’ll need MySQL server installed and running on your machine. Download it from the official MySQL website.
• NPM (Node Package Manager): NPM is usually installed with Node.js. It’s used for managing Node.js packages.
• Basic Knowledge of JavaScript and MySQL: Familiarity with JavaScript (especially Node.js) and MySQL basics is required to follow this tutorial effectively.

Step 1: Setting Up MySQL Database and Table

First, establish your MySQL environment. Create a new database and user specific for your Node.js applications. We’ll use ‘mydb’ for this guide.

CREATE DATABASE mydb;
USE mydb;

Next, create a MySQL table to practice various operations:

CREATE TABLE users (
  id int(11) NOT NULL AUTO_INCREMENT,
  firstname varchar(30),
  lastname varchar(30),
  email VARCHAR(50),
  creation_date TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Create a MySQL user for your application:

GRANT ALL on mydb.* to 'myusr'@'localhost' identified by '123456';
FLUSH PRIVILEGES;

Step 2: Installing Node.js MySQL Module

MySQL driver for node.js is available under the node package manager (NPM). If you don’t have created a directory for your application, create it first:

mkdir myApp && cd myApp 

Then, use the following command to install “mysql” module:

npm install mysql 

Step 3: Setting Up .env File

To secure your MySQL credentials, use a .env file:

1. Install the dotenv package:

   npm install dotenv 
   

2. In your Node.js project root, create a .env file with your MySQL credentials:

   
   DB_HOST=localhost
   DB_USER=myusr
   DB_PASSWORD=123456
   DB_DATABASE=mydb
   
   

Step 4: Developing a Simple Node.js App to Connect to MySQL

Create a basic Node.js application to test the MySQL connection. This application will output success or error messages based on the connection status and close the connection upon completion.

1. Create a application file:

   nano app.js 
   

2. In `app.js`, insert the following code:

   
   require('dotenv').config();
   var mysql = require('mysql');
   var dbconn = mysql.createConnection({
     host: process.env.DB_HOST,
     user: process.env.DB_USER,
     password: process.env.DB_PASSWORD,
     database: process.env.DB_DATABASE
   });
   
   dbconn.connect(function(err){
     if(err){
       console.log('Database connection error');
     } else {
       console.log('Database connection successful');
     }
   });
   
   dbconn.end(function(err) {
     // Close database connection
   });
   
   

3. Test the connection:

   node app.js 
   

   Expect a message confirming a successful database connection.

Step 5: Inserting Data into MySQL Table using Node.js

Enhance your Node.js application to insert data into the ‘users’ table:

var record = { firstname: 'John', lastname: 'Smith', email: '[email protected]' };

dbconn.query('INSERT INTO users SET ?', record, function(err, res){
  if(err) throw err;
  console.log('Last record insert id:', res.insertId);
});

Execute the script and verify the insertion by querying the MySQL database.

Also, check the record added in users table under the mydb database using the mysql command line.

mysql> SELECT * FROM users;

+----+-----------+----------+----------------+---------------------+
| id | firstname | lastname | email          | creation_date       |
+----+-----------+----------+----------------+---------------------+
|  5 | Rahul     | Kumar    | [email protected] | 2024-01-12 17:16:19 |
|  6 | John      | Smith    | [email protected] | 2024-01-12 17:16:55 |
+----+-----------+----------+----------------+---------------------+
2 row in set (0.01 sec)

Step 6: Fetching Data from MySQL Table with Node.js

Modify app.js to retrieve data from the ‘users’ table:

dbconn.query('SELECT * FROM users', function(err, records){
  if(err) throw err;
  console.log('Data received from Db:\n', records);
});

You can customize search by selection values basis of specific conditions.

dbconn.query('SELECT * FROM users WHERE id = ?' [5] ,function(err, records){
  if(err) throw err;

  console.log('Data received from Db:n');
  console.log(records);
});

Step 6: Updating Data in MySQL Table via Node.js

To update existing records in the MySQL table, use the following script:

dbconn.query('UPDATE users SET email = ? WHERE id = ?', ['[email protected]', 5], function(err, result){
  if(err) throw err;
  console.log('Record Updated ' + result.changedRows + ' rows');
});

Now check values in database table.

mysql> SELECT * FROM users;

+----+-----------+----------+----------------+---------------------+
| id | firstname | lastname | email          | creation_date       |
+----+-----------+----------+----------------+---------------------+
|  5 | Rahul     | Kumar    | [email protected] | 2024-01-12 17:16:19 |
|  6 | John      | Smith    | [email protected] | 2024-01-12 17:16:55 |
+----+-----------+----------+----------------+---------------------+
2 row in set (0.01 sec)

Step 7: Deleting Data from MySQL Table Using Node.js

Finally, to remove records from the MySQL table:

dbconn.query('DELETE FROM users WHERE id = ?', [5], function(err, result){
  if(err) throw err;
  console.log('Deleted ' + result.affectedRows + ' rows');
});

Conclusion

Throughout this tutorial, you’ve learned how to securely integrate Node.js with MySQL using environment variables to manage database credentials. This integration is crucial for developing robust web applications where Node.js serves as the backend programming language, and MySQL acts as the database management system.

Key Takeaways:

• Secure Credential Management: Using a .env file with the dotenv package for handling database credentials enhances security and prevents sensitive data exposure.
• Efficient Data Operations: The ability to perform basic database operations like INSERT, SELECT, UPDATE, and DELETE directly from Node.js streamlines your web application’s data management.
• Scalable Skills: The skills you’ve gained are not only applicable to MySQL but also transferable to other databases, as the concept of integrating a database with a server-side language is universal in web development.
• Foundation for Advanced Learning: This tutorial lays a solid foundation for further exploration into more advanced Node.js and MySQL functionalities, such as building APIs, handling complex queries, and implementing ORM tools.

By completing this tutorial, you are now equipped to create more dynamic and data-driven web applications using Node.js and MySQL. Always remember to follow best practices, especially regarding security and efficient database interaction, as you continue to enhance your web development skills.