Integrate Express with MySql

Connecting our Express app with MySql Database.

you can clone this repository and install requirement dependency using npm :

npm install

or using yarn:

yarn

Updated : to run this project, you just need type docker-compose up -d on your terminal

This project structure will be like this :

.
+-- config
|   +- db.js
+-- controllers
|   +- booksController.js
+-- routes
|   +- books.js
+-- app.js
+-- package.json
+-- yarn.lock
+-- README.md

API Endpoints

/api/v1/books

Endpoint	HTTP	Description	Body
/api/v1/books/createTB	GET	Create New Book Table
/api/v1/books/deleteTB	GET	Delete New Book Table

Connect to MySql

first you need to import mysql :

const mysql = require("mysql");

and define your username, password, host, and dbName :

const db = mysql.createConnection({
  host: "your_host",
  user: "username",
  password: "password"
});

and you can check if your configuration is correct will show log :

db.connect(err => {
  if (err) {
    console.log(err);
  } else {
    console.log("connect");
  }
});

Explanation

import your config file :

const db = require("../config/db");

To Create / Delete table we must write RAW Query like this :

exports.createDatabase = (req, res) => {
  db.connect(err => {
    if (err) {
      return console.log(err);
    } else {
      const sql = ` CREATE TABLE books
      (
        id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        author VARCHAR(255)
      )
        `;
      db.query(sql, (err, result) => {
        if (err) {
          return console.log(err);
        } else {
          res.status(200).json({
            msg: "Table Created"
          });
        }
      });
    }
  });
};

Reference

• NodeJs with MySql
• Cara Menggunakan Database MySQL pada Nodejs
• SQL Tutorial