How to use a MySQL database with Node.js

MariaDB and MySQL are widely known as some of the most popular relational database management systems.

Connecting a Node.js application to a MySQL database is a common and frequent task for many projects.

How to connect Node.js to MySQL

Before we begin, you will need to install the mysql2 package, which provides an efficient way to interact with MySQL from Node.js.

npm install mysql2

Creating the Database Connection File

The first step is to establish a connection with the MySQL database. For this, we will create a file named dbConnection.mjs that will contain the connection logic.

We could put everything in the same file. The main goal of having it separate is to keep it more modularized, and to have your connection data in another file, so you don’t accidentally publish it where you shouldn’t.

import mysql from 'mysql2/promise';

async function connect() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'your_username',
      password: 'your_password',
      database: 'your_db_name',
    });
    console.log('Connection to MySQL established.');
    return connection;
  } catch (error) {
    console.error('Error connecting to MySQL:', error);
    throw error;
  }
}

export default connect;

In this file:

• We import the mysql2/promise module, which allows us to use promises to handle database queries.
• We define a connect function that creates a connection with the provided details, such as host, user, password, and database name.
• If the connection is successfully established, the function returns the connection object. Otherwise, it logs an error message and throws an exception.

Creating the Database Connection

Once we have the connection file, we can use it anywhere in our Node.js application to interact with the database.

For example, let’s perform some basic queries.

import connect from './dbConnection.mjs';

async function fetchData() {
  const db = await connect();
  try {
    // Query to get all users
    const [rows, fields] = await db.execute('SELECT * FROM users');
    console.log('All users:', rows);

    // Query to get users by city
    const city = 'New York';
    const [cityRows, cityFields] = await db.execute('SELECT * FROM users WHERE city = ?', [city]);
    
    console.log(`Users in ${city}:`, cityRows);
  } catch (error) {
    console.error('Error fetching data:', error);
  } finally {
    db.end();
  }
}

fetchData();

In this example,

• First, we import the connect function from the dbConnection.mjs file.
• Then, we use this function to obtain a connection to the database.
• After that, we perform two queries:
  • The first query retrieves all users from the users table.
  • The second query retrieves users who belong to a specific city, in this case, “New York”.

CRUD Operations

Once we have the connection file, we can use it to perform CRUD operations (Create, Read, Update, Delete) on our MySQL database from Node.js.

Creating Data

First, let’s see how to insert new data into the database:

async function insertData(name, email) {
  // Establish the connection to the database
  const db = await connect(); 
  try {
    // Execute the SQL query to insert data
    const [result] = await db.execute('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]); 
    // Show the result of the operation
    console.log('Data inserted:', result); 
  } catch (error) {
    // Handle any error that occurs during data insertion
    console.error('Error inserting data:', error); 
  } finally {
    // Close the connection to the database, regardless of the result
    db.end(); 
  }
}

insertData('John Doe', 'john@example.com'); // Execute the insertData function with the provided data

Reading Data

Now let’s see how to get all users from the database:

async function fetchData() {
  // Establish the connection to the database
  const db = await connect(); 
  try {
    // Execute the SQL query to get all users
    const [rows, fields] = await db.execute('SELECT * FROM users'); 
    // Show all users retrieved from the database
    console.log('All users:', rows); 
  } catch (error) {
    // Handle any error that occurs during data reading
    console.error('Error fetching data:', error); 
  } finally {
    // Close the connection to the database, regardless of the result
    db.end(); 
  }
}

fetchData(); // Execute the fetchData function to get all users

Updating Data

Let’s see how we would update data in the database:

async function updateData(userId, newName) {
  // Establish the connection to the database
  const db = await connect(); 
  try {
    // Execute the SQL query to update data
    const [result] = await db.execute('UPDATE users SET name = ? WHERE id = ?', [newName, userId]); 
    // Show the result of the operation
    console.log('Data updated:', result); 
  } catch (error) {
    // Handle any error that occurs during data updating
    console.error('Error updating data:', error); 
  } finally {
    // Close the connection to the database, regardless of the result
    db.end(); 
  }
}

updateData(1, 'Jane Smith'); // Execute the updateData function with the provided data

Deleting Data

Finally, let’s see how to delete data from the database:

async function deleteData(userId) {
  // Establish the connection to the database
  const db = await connect(); 
  try {
    // Execute the SQL query to delete data
    const [result] = await db.execute('DELETE FROM users WHERE id = ?', [userId]); 
    // Show the result of the operation
    console.log('Data deleted:', result); 
  } catch (error) {
    // Handle any error that occurs during data deletion
    console.error('Error deleting data:', error); 
  } finally {
    // Close the connection to the database, regardless of the result
    db.end(); 
  }
}

deleteData(2); // Execute the deleteData function with the ID of the user to delete

Download the Code

All the code from this post is available for download on GitHub