Skip to content
← Back to Community
Create Account Database (MySQL nodejs)
Profile icon
h
has Hacker Plan
HackermonDev

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
img1
Once you are on this page go to the database section then click the create database button.
img2
Once you created the database you should be on a page like this but with your own password and username
img3
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.
img4
Once you are there click the database section and click the name of your database.
img5
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
img6
Then fill out the inputs with what it says in the image and click the button
img7
And you should be on this page
img8

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

Voters
Profile icon
FloKun
Profile icon
mel0n7
Profile icon
MomijiYukkuri
Profile icon
KarthikReddy32
Profile icon
YayanHerdiana
Profile icon
joeroyalty00
Profile icon
Lidiarati
Profile icon
Skripthut
Profile icon
LordArrow9
Profile icon
ev3swr
Comments
hotnewtop
Profile icon
Forstaken

For those who it says "Take a survey", click here to skip it: https://remotemysql.com/databases.php?action=new

Profile icon
amandaip

Hi everyone,
I'm having trouble to connect the website to remotemysql database. I only get "HTTP ERROR 500". How do I fix this? Any reply may help. Thanks.

Here is my code on mysqli_connect.php file (I hided username, dbname and password):

Profile icon
JoshuelVail

the RemoteMySQL site doesn't allow creation of new db's (there is no button for creating a new one).

Profile icon
milanuses

@JoshuelVail its because you have to do a survey to create one you should use another host

Profile icon
zk306950

Hi, the remote mysql webpage requires you to take a survey in the databases section before you are able to create a database. I have taken 4 surveys and each time there is an error. One type of error says something like 'you have not taken the survey successfully due to your answers.' Another error I get is 'you have been screened out of the survey' which happens when the survey tab just closes by itself without warning. Can you just use phpmyAdmin from wampserver then (because that hasn't worked on repl for me)? Any help or suggestions? Thank you.

Profile icon
HackermonDev

@zk306950 You can use the new repldb thing. If you go to the sidebar on the repl there should be like this symbol, click on it.

Profile icon
zk306950

@PDanielY Thank you!

Profile icon
Grimm

@PDanielY repldb? What is this? I can't find anything about it or see it anywhere on a sidebar?

Profile icon
HackermonDev
Profile icon
yash1441

@PDanielY Is it a paid feature? Because I can't seem to find that on my sidebar.

Profile icon
HackermonDev

@yash1441 No, I think you need the explorer role. Go to https://repl.it/account and then roles and enable explorer.

Profile icon
yash1441

@PDanielY Thanks! I used that and it worked. Another question if you don't mind, can I view the database or is it private always? I didn't get options like in your screenshot and no REPLIT_DB_URL created in the .env
I am working with NodeJS.

Profile icon
HackermonDev

@yash1441 The database is private, only the owner can see the URL but if someone gets the URL they can do whatever they want. And I think they updated it, you can now use packages to interact with repl.it db but you won't see the URL in the .env file.

Profile icon
[deleted]

@zk306950 Same. It gets annoying! Why am I replying to a 4 month old comment.

Profile icon
i8sumPi

Thank you for this tutorial!! It is SO helpful!

Profile icon
HackermonDev
Profile icon
HackermonDev

@i8sumPi wait, are you being sarcastic

Profile icon
i8sumPi

@PDanielY No I'm actually not being sarcastic! I had just had a lot of trouble with mongoDB and it was great to find database that worked.

Profile icon
Forstaken

Is there an example repl?

Profile icon
RiskyCOVID19

how to do this same in python for flask app?

Profile icon
interception

so i did it but how does the database work? like is there a way to add other accounts?

Profile icon
EpicGamer007

Wow, this is super cool! I am bookmarking this. Can you store images in this, in like the "blob" or whatever it is called?

Profile icon
yinkavitula

Nice one Daniel. Thanks for this. I just wish I could get help with using MySQL with Python on repl.it. Do you know any material that could be of help? Thanks again.

Profile icon
HackermonDev

@yinkavitula no, sorry

Profile icon
yinkavitula

@PDanielY Okay. Thanks. Will keep looking. And will probably share if I find any. Keep up the great work.

Profile icon
Makrayne

Thanks for the tutorial. It was very useful and I'm currently working on something using it!

Profile icon
HackermonDev

@Makrayne Thanks! Would love to see what you made

Profile icon
HackermonDev

No one ever found this post :C

Profile icon
Highwayman

@PDanielY Not true. I found it, but I couldn’t use remoteSql, which made me very sad so I left without finishing reading.

Profile icon
HackermonDev
Profile icon
Highwayman

@PDanielY hey guess what this came in handy, someone was asking how to save some stuff in python, so I linked them this, happy times yay 🥳🎉

Profile icon
HackermonDev

@Highwayman this is for nodejs

Profile icon
Highwayman

@PDanielY FFFFFFFFFFFFFFFFF every time I try to help someone it fails. Crap. 🤦‍♂️😞

Profile icon
SunitaDash1
Profile icon
Highwayman

You called? @SunitaDash1