The most important thing in a backend is the database. Let's walk through how to connect the NodeJS app with different databases like MySQL and mongo dB. Today I want to demonstrate about MySQL.



First Install MySQL dependency

npm install --save mysql2

There are 2 ways connecting

1. setup one connection
  • Allowed one query at one time.
  • We should always close the connection after we done with the query.         
  • We need to re-execute the code to create the connection for every new query.
  • This is very inefficient.

2.The better Way -  Make a connection pool
  • We can run multiple queries simultaneously

Now Lets setup ->

Create a util folder for database.js

In database.js

const mysql = require("mysql2");
const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  database: "node-first",
  password: "",
});

module.exports = pool.promise(); //export as a promise
//promises have .then().catch()  it is much easier than callbacks


Testing in -> app.js

db.execute("SELECT * FROM products ")
  .then((result) => {
    console.log(result);
  })
  .catch((err) => {
    console.log(err);
  });


NO any additional settings are want to set in app.js

Then we can set the user model->


In the model

const db = require("../helper/database");

module.exports = class Product {

    constructor(id, title, imgUrl, description, price) {
        this.id = id;
        this.title = title;
        this.imgUrl = imgUrl;
        this.description = description;
        this.price = price;
    }

    save() {
   
we don't directly enter the values -> because SQL injections
instead, we passing ? ? ? ?
then we add a second the argument for passing the data <-managed by SQL package
This provides an extra security layer

    return db.execute(
    "INSERT INTO products ( title, price, description, imageUrl) VALUES ( ?, ?, ?, ?);",
        [this.title, this.price, this.description, this.imgUrl]
    );
}

     static fetchAll() {
        return db.execute("SELECT * FROM products ");
     }

     static findById(id) {
        return db.execute("SELECT * FROM products WHERE id=?", [id]);
     }

 
};


In the controller

//Reading the Data

exports.getIndex = (req, res, next) => {
  Product.fetchAll()
    .then(([rows, fieldData]) => {   //rows are the data
      res.render("shop/index", {
        prods: rows,
        pageTitle: "Product List",
        hasProducts: rows.length > 0,
        active: "index",
        prooductCSS: true,
      });
    })
    .catch((err) => {
      console.log(err);
    });
};


//Creating a Product

exports.postAddProduct = (req, res, next) => {
  const title = req.body.title;
  console.log(title);

  const imgUrl = req.body.imgUrl;
  const price = req.body.price;
  const description = req.body.description;

  const product = new Product(null, title, imgUrl, description, price);
  product
    .save()
    .then(() => {
      res.redirect("/");
    })
    .catch((err) => {
      console.log(err);
    });
};


These basic commands can be executed in a very easy way>
It will need an ORM tool called -> sequalize
Let’s talk about it in the next episode.