How to use a MySQL database with Node.js

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

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

How to connect Node.js to MySQL

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

npm install mysql2

Creation of the database connection file

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

We could put everything in the same file. The main goal of having it separated is to be more modularized, and have your connection data in another file, and not publish it where you shouldn’t by mistake.

import mysql from 'mysql2/promise';

async function connect() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'tu_usuario',
      password: 'tu_contraseña',
      database: 'nombre_de_tu_db',
    });
    console.log('MySQL connection 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 the host, user, password, and database name.
• If the connection is successfully established, the function returns the connection object. Otherwise, it displays 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, we will 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 with the database.
• Next, we perform two queries:
  • The first query retrieves all users from the users table.
  • The second query retrieves users belonging to a specific city, in this case, “New York”.

CRUD Operations

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

Data Creation

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

async function insertData(name, email) {
  const db = await connect(); 
  try {
    const [result] = await db.execute('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]); 
    console.log('Inserted data:', result); 
  } catch (error) {
    console.error('Error inserting data:', error); 
  } finally {
    db.end(); 
  }
}

insertData('John Doe', 'john@example.com');

Data Reading

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

async function fetchData() {
  const db = await connect(); 
  try {
    const [rows, fields] = await db.execute('SELECT * FROM users'); 
    console.log('All users:', rows); 
  } catch (error) {
    console.error('Error fetching data:', error); 
  } finally {
    db.end(); 
  }
}

fetchData();

Data Update

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

async function updateData(userId, newName) {
  const db = await connect(); 
  try {
    const [result] = await db.execute('UPDATE users SET name = ? WHERE id = ?', [newName, userId]); 
    console.log('Updated data:', result); 
  } catch (error) {
    console.error('Error updating data:', error); 
  } finally {
    db.end(); 
  }
}

updateData(1, 'Jane Smith');

Data Deletion

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

async function deleteData(userId) {
  const db = await connect(); 
  try {
    const [result] = await db.execute('DELETE FROM users WHERE id = ?', [userId]); 
    console.log('Deleted data:', result); 
  } catch (error) {
    console.error('Error deleting data:', error); 
  } finally {
    db.end(); 
  }
}

deleteData(2);