Installing and setting up mysql with nodejs in ubuntu or mac

database(Image source:Pixabay)

Nodejs has seen dramatic increase in adoption in the backend side since it allows javascript usage in backend along with frontend. It is usually advisable to use nosql databases like mongodb,couchbase and aerospike but there might be a need to use mysql databases with nodejs until you successfully migrate your mysql dbs to nosql stack.

In today’s tutorial, we are going to install MySQL in ubuntu and create a basic connection of nodejs with MySQL database using express framework.

Let's get started :

Install MySQL on ubuntu:

sudo apt-get update
sudo apt-get install mysql-server

Installing MySQL on MAC: Official Guide

During installation, you will be asked to set up root password. Make sure you keep a note of the root password since we are going to require that password in the later part of the tutorial.

Check your MySQL version with the following command on terminal:

mysql --version

You will see output like this:

mysql  Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (x86_64) using readline 6.3

Check for version after Distrib. If it is less than 5.7.6 you should initialize the data directory by running:

sudo mysql_install_db

Then check mysql service status by running following command:

service mysql status

If it shows status: Unknown job: mysql then start mysql service by running the following command:

sudo service mysql start

Now login into mysql cli with the following command:

mysql -u root -p

It will ask for root password which you set up during mysql installation.

Now you will get access to mysql cli. Run the following command to create database in mysql:

create database mockUsers;

Replace dbname with database name of your choice.

In order to verify that database has been created run the following command:

show databases;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mockUsers         |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.05 sec)

Exit mysql cli using command ctrl+c.

Now create a new folder in Documents for your node project.

cd Documents/
mkdir loginBackend

Run the following command to initialize npm in the folder.

cd loginBackend
npm init

Now install express and mysql npm modules.

npm install express mysql

Now create server.js and paste the following code to test your installation.

 var express    = require("express");
 var mysql      = require('mysql');
 var connection = mysql.createConnection({
   host     : 'localhost',
   user     : 'root',
   password : 'your_mysql_password',
   database : 'dbname'
 });
 var app = express();
 
 connection.connect(function(err){
 if(!err) {
     console.log("Database is connected ... \n\n");  
 } else {
     console.log("Error connecting database ... \n\n");  
 }
 });
app.listen(3000);

Replace password value with MySQL root user password and dbname with the database name you created earlier.

Now if you have nodemon install run the following command to check your database connection:

nodemon server.js

If you get log as Database is connected in terminal logs then your nodejs setup with MySQL is complete. That’s it for today’s tutorial. In the next tutorial I will cover how to handle user registration and login using the same stack which you can find here:- Medium article.

Connect Deeper

Kindly recommend this article to others if you like it by clicking clap at the bottom. You can check out my other tutorials on react,react-native and node.js here: Technoetics.