Introduction

Today we are going to be learning how to create a basic account database with MySQL with nodejs. MySQL is usually hosted on a local device but today we are going to be using RemoteMySQL. RemoteMySQL is a website that hosts MySQL databases for you. I do not recommend using RemoteMySQL for a non-side project because they delete your database if the storage is more than 1GB. So if a lot of users are using your project and creating accounts. In less than 5 days the database will be deleted. Now let's start

Setup

To start we are going to create an account on RemoteMySQL. Once you created your account and verified your email you should be on this page

Once you are on this page go to the database section then click the create database button.

Once you created the database you should be on a page like this but with your own password and username

RemoteMySQL has it own PHPMyAdmin so you have to go to its PHPMyAdmin .
Enter your info on the PHPMyAdmin page and you should be on a page like this.

Once you are there click the database section and click the name of your database.

It will show you a page and it will ask you for the name of the table and how many columns you want. Make the name Account and columns 2. Then you should be on this page

Then fill out the inputs with what it says in the image and click the button

And you should be on this page

Setup on nodejs project

We are done with setting it up on the PHPMyAdmin go to your nodejs console and enter $ npm i mysql and then enter this or if you are on repl.it enter this:

mysql = require("mysql")

Creating a connection to the database

On nodejs, there are two ways to connect to the database. There is a normal connecting and a pool connection. On a normal connection, it ends your connection to the database after some while but in a pool connection, your connections are always active. To create a normal connection do this:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "remotemysql.com",
  user: "yourusername",
  password: "yourpassword",
database:"yourusernameagain"
});

con.connect(function(err) {
  if (err){
console.log(err)
return
}
  console.log("Connected to database!");
});

To create a pool connection do this:

mysql = require("mysql")
var con = mysql.createPool({
  host: "remotemysql.com",
  user: "yourusername",
  password: "yourpassword",
database:"yourusernameagain"
});

con.getConnection(function(err) {
  if (err) throw err;
  console.log("Connected to pool database!");
});

Inserting into the database

To insert row into your database do this:

con.query(`INSERT INTO Account (Username,Password) VALUES (${mysql.escape("theusername")},${mysql.escape("thepasswordthatshouldbeinserted")})`,(err)=>{
  if(err){
    throw err
    return
  }
  console.log("Row inserted")
})

Then if you go back to your PHPMyAdmin and go to the account database there should be the value that you inserted. Please note mysql.escape(value) is important or it will show an error

Fetching from the database

To fetch a row from the database do this:

con.query(`SELECT * FROM Account WHERE Username = ${mysql.escape("theusername")}`,(err,result)=>{
  if(err){
    throw err
  }
  console.log(result)
})

mysql.escape(value) is important here too.
Result value will be an array in the array JSON with the values. So if I console.log result[0].Password it will return "thepasswordthatshouldbeinserted".

Footer

That is the end of this tutorial. You can find more info here: Click me. If you think this helped please upvote.

Your Repler,
Daniel