How to use .env file in NodeJS MySQL?

Environment variables are list of properties defined by key and value used for storing configurations data for instance the database credentials, api keys and so on. In the past, these values were directly coded into your applications’ code and were not flexible to change, but today we can store them in a. configuration file known as .env file which makes it secure and well organized manner in controlling the configurations settings.

Here, in this article, you have been initiated with the preparation of a node.js application that interacts with a MySQL database using environment variables put in a .env file. Further, in asymmetrical modes we will also describe how promises can be implemented with the mysql2 package.

Prerequisites

• NodeJS
• NPM
• MySQL
• dotenv

Steps to Create Node. js Application

Step 1: Create a Node.js application using the following command:

mkdir geeksforgeeks
cd geeksforgeeks
npm init -y

Step 2: Install the required modules using the following command:

npm install mysql2 dotenv

Step 3: Once you have created a Node.js environment, create a .env file in the root directory of your project with the following content:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB_DATABASE=geeksforgeeks

This file defines environment parameters that will be used to connect database.

Step 4: Create a db.js file in your project directory to establish the MySQL connection:

require('dotenv').config();
const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to MySQL database!');
});

module.exports = connection;

Project Structure:

Updated Dependencies:

{
  "name": "geeksforgeeks",
  "version": "1.0.0",
  "main": "db.js",
  "scripts": {
    "start": "node db.js"
  },
  "license": "ISC",
  "dependencies": {
    "dotenv": "^16.4.5",
    "mysql2": "^3.11.0"
  }
}

Step 5: Run the Project

To start the application and connect to the MySQL database use the below command

npm start
or
node db.js

Example 1: Connecting to MySQL Database using .env file

To illustrate, in this example, let us show you how to connect to a MySQL database based on the environment variables set in a .env file.

require("dotenv").config();
const mysql = require("mysql2");

// Create MySQL connection
const connection = mysql.createConnection({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
});

// Connect to the database
connection.connect((err) => {
  if (err) throw err;
  console.log("Connected to MySQL database!");

  // Create 'users' table
  const createTableQuery = `
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      email VARCHAR(255) NOT NULL
    )
  `;

  connection.query(createTableQuery, (err, results) => {
    if (err) throw err;
    console.log("Users table created or already exists.");

    // Insert data into 'users' table
    const insertQuery = `
      INSERT INTO users (name, email)
      VALUES ('GeeksforGeeks', '[email protected]')
    `;

    connection.query(insertQuery, (err, results) => {
      if (err) throw err;
      console.log("Data inserted into 'users' table.");
      connection.end(); // Close the connection
    });
  });
});

module.exports = connection;

Output: That is when the console will display the message:

Connected to MySQL database!
Users table created or already exists.
Data inserted into 'users' table.

• VS Code Terminal:

• MySQL Workbench:

This is to ensure that indeed we are connected to the MySQL database and all the environment variables set were as required.

Example 2: Promises with mysql2

require("dotenv").config();
const mysql = require("mysql2/promise");

// Create MySQL connection
const connectToDatabase = async () => {
  try {
    const connection = await mysql.createConnection({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_DATABASE,
    });
    console.log("Connected to MySQL database!");
    return connection;
  } catch (err) {
    console.error("Error connecting to the database:", err);
    throw err;
  }
};

// Example usage
const setupDatabase = async () => {
  try {
    const connection = await connectToDatabase();

    // Create 'users' table
    const createTableQuery = `
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL
      )
    `;
    await connection.execute(createTableQuery);
    console.log("Users table created or already exists.");

    // Insert data into 'users' table
    const insertQuery = `
      INSERT INTO users (name, email)
      VALUES ('GeeksforGeeks', '[email protected]')
    `;
    await connection.execute(insertQuery);
    console.log("Data inserted into 'users' table.");

    connection.end(); // Close the connection
  } catch (err) {
    console.error("Error setting up the database:", err);
  }
};

setupDatabase();

Output: On successfully executing the console will show the day of the event as follows.

Connected to MySQL database!
Users table created or already exists.
Data inserted into 'users' table.

• VS Code Terminal:

• MySQL workbench:

To use promises with mysql2 makes the code more maintainable and easier to comprehend, especially when handling several concurrent operations.

Conclusion

Utilizing a .env file in Node.js and mysql2, the process of the configuration of environment-specific variables, for example, database credentials, is more manageable. The dotenv package, together with the promise-based nature of mysql2, avoids using raw queries and complexities and simplifies the creation of the tables and insertion of data.