Installing and setting up mysql with nodejs in ubuntu

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 basic connection of nodejs with mysql database using express framework.

Lets get started :

Install mysql on ubuntu:

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

During installation you will be asked to set up root password.Make sure you keep a note of 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:

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 dbname;

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 |
| cloudprint |
| 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 cloudprint

Run the following command to initialize npm in the folder.

cd cloudprint
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 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 same stack.

Connect Deeper

Kindly recommend this article to others if you like it by clicking the ♥ at the bottom.You can check out my other tutorials on react,reactnative and nodejs here: Technoetics.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.