Connect Your Node.js Project to MySQL Database Locally

There is more than one way to connect MySQL database to node.js and in this tutorial, we are going use one of the easy ways to do it. With the use of XAMPP and mysql2 package, our connection will be set up in no time. No worries if you don't know these two, we are going to do this step by step.

Install Xampp

Xampp is a control panel or cross-platform server that enables developers to create and test their programs locally. Xampp is going to provide the GUI(Graphical User Interface) of our database. It will allow us to create databases and the data we need for our project.

Visit the apachefriends website to download the suitable xampp for your operating system. Follow all the necessary procedures and install it. After the successful installation, open the program:

Start the first two modules on the panel(Apache and MySQL).

This will start the SQL database locally which you can access on a localhost server. Open your browser and go to localhost/phpmyadmin/.

To create a new database, click on the new button at the side panel.

Give your database a new name and click create. It should show up in the side panel after it has been successfully created. You can access it and go on to create your tables manually or import a SQL file with your data.

Set Up Connection

For this, we will make use of the mysql2 package. Install mysql2 to your project with npm install mysql2. You should read the mysql2 documentation to learn more about how to work with the package.

In your model folder, create a connection.js file and copy the code to establish a connection to your database:

const mysql = require('mysql2');
const connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: '', //your password. You could leave it like this, for now
    database: 'database_name'
})

connection.connect((err)=>console.log(err || 'Database Connected'))

module.exports = connection.promise()

You can go on to import the connection function(please be aware that it is asynchronous) anywhere in your project you want to query your database.

And that's it, our node.js project is connected to MySQL database in few steps.