Why am I doing this tutorial? Well I like to create tutorials for many different things in the hopes that it will help someone.

so first we need to install MySQL so first run the command npm install mysql. Once it’s fully installed open your main code and enter the following:

var mysql = require('mysql');
var db_config = {
    host: 'localhost',
      user: 'USERNAME',
      password: 'PASSWORD',
      database: 'DATASBE'
  };

var connection = mysql.createConnection(db_config);

What this does is initiate the connection with the database, before you use this make sure to change the config to your database credentials. Now that we have this down you’ll want to know that the database has successfully connected so next put in the code:

connection.connect(function(err, result) {
	console.log("Successfully connected to the database");
});

All this does is show you that the connection was successfully made. So now that we got the configurations done it’s time to introduce some queries so the first that I’ll show is an example that is meant to search through a user table and check if a certain user exists.

let query1 = connection.query("SELECT * from users where username = ?", "Arandomusername", function (err, result, fields){
    if (Object.keys(result).length === 0) {
        console.log("That username does not exist!");
    } else {
	console.log(result);
    }
});

So this query is searching for a user with the example username “Arandomusername” and the code if (Object.keys(result).length === 0) { checks if the query brings back ANY result, if there is nothing it will respond with “That username does not exist!” otherwise it will respond with everything about the user in the form of an array. The way I set up my example user database is in the way of ID, email, username, password, and admin. So with my code if it exists it will respond with:

[
  RowDataPacket {
    id: 1,
    email: '[email protected]',
    username: 'Arandomusername',
    password: 'Arandompassword',
    admin: 1
  }
]

So let’s get into how we use this data, this part is pretty simple. As an example I’ll show you how to display if the user has a 1 in the admin column. So we can do this with: 

if (result[0].admin == "1"){
console.log("This user is an admin");
} else {
console.log("This user is not an admin");
}

Alright so now that we have that done let’s use the entire table. For this one all we have to do is:

let query2 = connection.query("SELECT * from users;", function (err, result, fields){
    if (Object.keys(result).length === 0) {
        console.log("This table is empty!");
    } else {
	console.log(result); 
    }
});

And this will show everything in the table in the form of an array. So what if you want to use the first data in the table? Well thats easy, just use:

console.log(result[0]);

as this works the first one is always 0, to use the next ones just raise the numbers. But now we should get into changing data, so like before, we need to check if it exists, and then change the value. So now you’ll want to do: 

let query4 = connection.query("SELECT * from users where username = ?", "Arandomusername", function (err, result, fields){
    if (Object.keys(result).length === 0) {
        console.log("That username does not exist!");
    } else {
        let query5 = 'UPDATE users SET password = "Anotherandompassword" WHERE username = "Arandomusername";';
      connection.query(query5);
    }
});

This one changes Arandomusername’s password to Anotherandompassword. Now let’s get to the final one which should have been the first one but oh well, it’s inserting data into the table. All you have to do is: 

let query6 = connection.query('SELECT * FROM users WHERE username = ? OR email = ?', ["Arandomusername", "[email protected]"], function(error, result, fields) {
    if (result.length > 0) {
        console.log("I'm sorry, a user with that username or email already exists.")
    } else {
        let register ="INSERT INTO users (email, username, password,admin) VALUES ('[email protected]', 'Arandomusername', 'Arandompassword', '0');";
      connection.query(register)
      console.log("Successfully Inserted!");
    }
});

The first query is a bit different as we introduce the email and the OR, which searches for anything with the exact username or email. If any of them are found it sends that it already exists. If they dont exist it sends the insert command. You’ll notice that I didn’t add the ID part, I did this because its Auto incremental which means with each insert the id goes one up from the previous.

If you need anything contact me at:

[email protected]
Tags: ,