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 very frequent 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
Creating the Database Connection File
The first step is to establish a connection to the MySQL database. For 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 separate is to make it more modular, and to keep your connection data in another file, preventing you from accidentally publishing 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/promisemodule, which allows us to use promises to handle database queries. - We define a
connectfunction that creates a connection with the provided details, such as host, user, password, and database name. - If the connection is established successfully, the function returns the connection object. Otherwise, it displays an error message and throws an exception.
Make sure to replace 'localhost', 'your_username', 'your_password', and 'your_db_name' with the correct values for your database.
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
connectfunction from thedbConnection.mjsfile. - Then, we use this function to obtain a connection to the database.
- After that, we perform two queries:
- The first query gets all users from the
userstable. - The second query gets users belonging to a specific city, in this case, “New York”.
- The first query gets all users from the
CRUD Operations
Once we have the connection file, we can use it to perform CRUD (Create, Read, Update, Delete) operations 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', '[email protected]'); // 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 
