Node.js - Connecting to the database

Stuff

• Grupper til fullstack workshop danner i selv. Der må være max 3 i en gruppe

• Der er muligheden for at lave sit eget projekt, så har man noget man gerne vil lave må man godt begynde at tænke lidt over et projekt

• Husk virksomhedsbesøg på tirsdag fra 13-15

  • 13.00-14.00: Oplæg inkl. spørgsmål.

    - Hvem er vi? (Hvad er Analyse & Tal for en mærkelig virksomhed uden chefer og løntrin)

    - Hvad laver vi? (eksempler på undersøgelser)

    - Visualiseringscases

    - Spørgsmål

    Pause & mulighed for at se huset (det er ikke så stort, så det er hurtigt set)

    14.00-15.00: Opgave: Visualisér dit dataspejl

    Vi sidder aktuelt med en meget udfordrende visualiseringsopgave, hvor vi gerne vil have folk til at få syn for alt den data, der bliver delt om én, når man bruger apps og hjemmesider. Hvordan kan man visualiserer det på en måde, hvor folk mærker, hvor meget fx techgiganterne ved om en?

Learning objectives

• Connecting to the database

  • Creating the connection

  • Connecting

  • Running a query

• 🚨 Saving sensitive data 🚨

  • .gitignore

  • .env

• Putting it all together

Connecting to mysql

There is a template for connecting to the database! Go to this link. If you want it locally you have to clone it to your computer and then checkout the database branch by writing the following in the terminal: git checkout database

Installing the mysql2 library

We will be using the library called mysql2 to connect to our database. Install it by writing the following in the terminal: npm install --save mysql2. You can read about the library here

Simple connection

To create a query using the mysql2 library we need to do three things

1. Create the connection - Here we just create the connection with our login details. Like where is the database hosted, what user are you logging in with, what password etc

2. Connect to the database - When connecting that can either be successful or fail.

3. Using the connection we send a query to the datbase and get some result back

Let's create a javascript file that will handle the database connection:

database.js

// Importing the mysql2 library
const mysql = require("mysql2");

// 1. Creating the connection
const mysqlConnection = mysql.createConnection({
    host: "MYSQL_HOST",
    port: "MYSQL_PORT",
    user: "MYSQL_USER",
    database: "MYSQL_DATABASE",
    password: "MYSQL_PASSWORD",
    multipleStatements: true,
});

// 2. Connecting to the database. Handling success and error cases
mysqlConnection.connect((err) => {
    if (!err) {
        console.log("Connected");
    } else {
        console.log(err);
        console.log("Connection Failed");
    }
});

// 3. Sending a query to the database
mysqlConnection.query(
  "SELECT * FROM wishlist.wish;",
  (err, results, fields) => {
    if (!err) {
      console.log(results);
    } else {
      console.log(err);
    }
  }
);

// 4. Sending a query to the database with user input
const query = "SELECT * FROM wishlist.wish where price > ?;";
const minimumPrice = 22;
mysqlConnection.query(
    query,
    [minimumPrice],
    (err, results, fields) => {
        if (!err) {
            console.log(results);
        } else {
            console.log(err);
        }
    }
);

First we import the library that we installed above const mysql = require("mysql2");

1.

The we create the connection to the database const mysqlConnection = mysql.createConnection({

Where it say MYSQL_HOST you write the host for your database. Typically "localhost"

MYSQL_PORT is typically "3306"

MYSQL_USER is typically "root"

MYSQL_DATABASE is the name of the database you want to connect to

MYSQL_PASSWORD is the password for your database

2.

mysqlConnection.connect((err) => { Here we actually connect to the database. We also handle if the connection throws an error

3. mysqlConnection.query( this is the part that will actually send the query to the database. The query function takes two parameters

1. A string which is the query

2. A function that is called when there is a result from the database

   1. If there is an error the err parameter will be defined. Fx if the query is malformed

   2. The results parameter will contain the results of the query

🚨 Saving sensitive data in your project 🚨

This part is super important!!!

Imagine that i took the above code, wrote my password, commited and pushed the changes. Now the password for my database can be seen by everyone 😱 Look at the screenshot below. It is a public webpage so anyone can just go there and see your password

Very dangerous!

So why is this so bad? For a lot of reasons

• What if you reused your password on multiple websites? Now i can very easily log into your gmail, Facebook by simply finding your email (on github). This is obviously very bad!

• Okay so i just remove the password and create a new commit. No because the password is in the git history! You actually have to delete til repo and start over. Or delete your git history

• But no one will find out. There are bots that constantly scrape GitHub for leaked passwords. Its very easy to do. The reason i know this is because i once leaked a password that got me into a lot of trouble 😱

So how do we fix this then? Using something called a .gitignore file

.gitignore

The .gitignore file is a file that will tell which files or folders should not be added to your git repo.

Fx have we talked about the node_modules folder that contains all the files for the libraries we use. This should not be added to git! Therefore we add node_modules folder to the .gitignore file like this:

/node_modules/

.env

We will create a new file called .env. This will contain all our secrets!

Password, host for our database, the user for our mysql database, maybe a token from an api. All this data we don't want to add to our GitHub repo, therefore we add it to our .gitignore file 👇

/node_modules/
.env
/.idea/

The /.idea/ is the folder that webstorm adds for a project. This is also not really relevant for our Github repo.

.env

HOST=localhost
USER=root
DATABASE=databasename
PASSWORD=asd
PORT=3306

Our .env file will contain a key and a value

Data from .env to database.js

Now all we need is to get the data from the .env file and into our database.js file. To do that we need three things:

1. Install the dotenv library using this command: npm install dotenv -s

2. Import the dotenv library in the database.js file: require('dotenv').config();

3. Reference the data in the .env file. Here is an example of how to get the value for the HOST key: process.env.HOST

// Importing the mysql2 library
const mysql = require("mysql2");
require('dotenv').config();

// 1. Creating the connection
const mysqlConnection = mysql.createConnection({
    host: process.env.HOST,
    port: process.env.PORT,
    user: process.env.MYSQL_USER,
    database: process.env.DATABASE,
    password: process.env.PASSWORD,
    multipleStatements: true,
});

// 2. Connecting to the database. Handling success and error cases
mysqlConnection.connect((err) => {
    if (!err) {
        console.log("Connected");
    } else {
        console.log(err);
        console.log("Connection Failed");
    }
});

// 3. Sending a query to the database
mysqlConnection.query(
  "SELECT * FROM wishlist.wish;",
  (err, results, fields) => {
    if (!err) {
      console.log(results);
    } else {
      console.log(err);
    }
  }
);

Putting it all together

The code for connecting to the database can be found here.

database.js

This file will create the connection and connect to the database. Then it will export the connection so it can be used elsewhere in the application.

app.js

This file does a lot of things. But relevant to the database we first import the connection we exported in the database.js file with the following code:

const mysqlConnection = require("./database");

Now in one of the endpoints we use the mysqlConnection in the following way:

app.get('/database-test', (req, res) => {
    mysqlConnection.query(
        "SELECT * FROM users;",
        (err, results, fields) => {
            if (!err) {
                res.json(results);
            } else {
                console.log(err);
            }
        }
    );
})

Here we send a query selecting everything from a table called users

Exercises

Work with the tabel Employees and departments that you have already worked with in the data literacy course

📝 Exercise - level 1 - Employees endpoint

Create a GET /employees endpoint that responds with a list of all employees in the database

📝 Exercise - level 1 - Department endpoint

Create a GET /department endpoint that responds with a list of all departments in the database

📝 Exercise - level 2 - Specific employee

Create a GET endpoint that will respond with a specifc employee:

/employees/1 will respond with the employee with id 1

Exercise - level 2 - database joining

Create an endpoint that gets the employees from a specific department

/employees?department=1

https://stackabuse.com/get-query-strings-and-parameters-in-express-js/

Exercise - level 3 - Adding a new employee

Create an endpoint for adding an employee

Exercise - level 3 - Render all employees

Create the frontend that fetches the api and renders the employees

Exercise - level 3 - Create new employee

Create the frontend that creates a new employee