Skip to content
Sign upLog in
← Back to Community

Create Account Database (MySQL nodejs)

Profile icon
HackermonDevHacker

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
DeusielCunha
Profile icon
MortexAG
Profile icon
Websekolah
Profile icon
Rosamaria01
Profile icon
OluchukwuEgbuon
Profile icon
FloKun
Profile icon
mel0n7
Profile icon
MomijiYukkuri
Profile icon
KarthikReddy32
Profile icon
YayanHerdiana
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

@Grimm

image

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
DevVali

It just doesn't work in 2022.

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