MySQL Database connection from node js application

Introduction

This tutorial will guide you through connecting a MySQL database to a Node.js application. By following these steps, you'll learn how to set up a Node.js project, install the necessary MySQL package, and execute a simple query to retrieve data from your database. This is a fundamental skill for developers working with databases in web applications.

Step 1: Create a New Node.js Project

• Open your terminal or command prompt.

• Navigate to the directory where you want to create your project.

• Run the following command to initialize a new Node.js project:

  npm init -y
  

  This command creates a package.json file with default settings, which will manage your project's dependencies.

Step 2: Install MySQL Package

• In the same terminal window, install the MySQL package by running:

  npm install mysql
  

  This command will download and install the MySQL driver for Node.js, allowing you to interact with MySQL databases.

Step 3: Create a Database Connection File

• Create a new file in your project directory named database.js. This file will contain the code to establish a connection to your MySQL database.

Step 4: Import MySQL Package

• Open database.js and import the MySQL package at the top of the file:

  const { createPool } = require('mysql');
  

  This line imports the createPool method, which will be used to manage database connections.

Step 5: Create a Connection Pool

• Below the import statement, create a connection pool to your MySQL database with the following code:

  const pool = createPool({
      host: "localhost",
      user: "root",
      password: "",
      database: "test",
      connectionLimit: 10
  });
  

  • host: The database server address (use "localhost" if it's on your local machine).
  • user: Your MySQL username (default is often "root").
  • password: Your MySQL password (leave blank if there is none).
  • database: The name of the database you want to connect to (in this case, "test").
  • connectionLimit: The maximum number of connections allowed.

Step 6: Execute a Query

• To execute a query and fetch data from the database, add the following code to your database.js file:

  pool.query(SELECT * FROM registration, function(err, result, fields) {
      if (err) {
          return console.log(err);
      }
      return console.log(result);
  });
  

  • This code runs a SQL query to select all records from the registration table.
  • If there’s an error, it logs it to the console; otherwise, it prints the results.

Conclusion

You've now set up a Node.js application that connects to a MySQL database and executes a simple query. Key points to remember include initializing your project, installing the MySQL package, and properly configuring your database connection. As a next step, consider expanding your application by adding more complex queries or implementing user input for dynamic data retrieval. Happy coding!