A simple CRUD using MySQL and Node Js

CRUD is an acronym for "CREATE, READ, UPDATE, DELETE" in SQL. Basically what we are going to demonstrate is how to use MySQL in Node JS. There are lots of tutorials out there on how to make a CRUD using Mongo DB, but what we really want to show in here is how to use SQL in Node Js.

WHAT DO YOU NEED TO FOLLOW THIS TUTORIAL?
ES6/ES7
MYSQL
SOME BASIC KNOWLEDGE OF NODE JS

WHAT ARE WE GOING TO BUILD?
We are going to create a TODO LIST with Node JS using ES6 and Common JS. All we need in this project is "promise-mysql". Let's install: npm i -s promise-mysql

joanvasquez@local-coderghost:~/git/github/NodeJs-MySQL$ npm i -s promise-mysql
+ promise-mysql@3.3.1
updated 1 package and audited 18 packages in 1.21s
found 0 vulnerabilities

After the promise-mysql package is installed, let's create the structure of the project.

# APP STRUCTURE
    * app.js -> the main file to run our project
    * dbConnection.js
    * queries
        - queries.js
    * Dao
        - TodoDao.js

TIME TO CODE
The first thing we need to do is to create the dbConnection.js

const mysql = require('promise-mysql');

const dbConfig = {
        user: "joanvasquez",
        password: "Password2288_",
        database: "todo_dev",
        host: "localhost",
        connectionLimit: 10
}

module.exports = async () => {
    try {
        let pool;
        let con;
        if (pool) con = pool.getConnection();
        else {
            pool = await mysql.createPool(dbConfig);
            con = pool.getConnection();
        }
        return con;
    } catch (ex) {
        throw ex;
    }
}

As we can see, we have an object named dbConfig which only contains the information needed to connect to the Database. Later we export an asynchronous function which will return the connection retrieved from a pool of connections. Now let's write some queries to perform our CRUD.

module.exports = {
    insert_todo: `INSERT INTO tbl_todo(title, completed) VALUES(?, ?)`,
    read_todo: `SELECT * FROM tbl_todo`,
    update_todo: `UPDATE tbl_todo SET tbl_todo.title = ?, tbl_todo.completed = ? WHERE tbl_todo.id = ?`,
    delete_todo: `DELETE FROM tbl_todo WHERE tbl_todo.id = ?`
}

After creating the queries, we have to create a DAO and use our queries, I mean, why no?

const dbConnection = require("../dbConnection");
const queries = require("../queries/queries");

module.exports = class TodoDao {
  async saveEntity(entity) {
    let con = await dbConnection();
    try {
      await con.query("START TRANSACTION");
      let savedTodo = await con.query(
        queries.insert_todo,
        [entity.title, entity.completed]
      );
      await con.query("COMMIT");
      entity.id = savedTodo.insertId;
      return entity;
    } catch (ex) {
      await con.query("ROLLBACK");
      console.log(ex);
      throw ex;
    } finally {
      await con.release();
      await con.destroy();
    }
  }

  async updateEntity(entity) {
    let con = await dbConnection();
    try {
      await con.query("START TRANSACTION");
      await con.query(queries.update_todo, [
        entity.title,
        entity.completed,
        entity.id
      ]);
      await con.query("COMMIT");
      return true;
    } catch (ex) {
      await con.query("ROLLBACK");
      console.log(ex);
      throw ex;
    } finally {
      await con.release();
      await con.destroy();
    }
  }

  async deleteEntity(id) {
    let con = await dbConnection();
    try {
      await con.query("START TRANSACTION");
      await con.query(queries.delete_todo, [id]);
      await con.query("COMMIT");
      return true;
    } catch (ex) {
      await con.query("ROLLBACK");
      console.log(ex);
      throw ex;
    } finally {
      await con.release();
      await con.destroy();
    }
  }

  async readEntities() {
    let con = await dbConnection();
    try {
      await con.query("START TRANSACTION");
      let todo = await con.query(queries.read_todo);
      await con.query("COMMIT");
      todo = JSON.parse(JSON.stringify(todo));
      return todo;
    } catch (ex) {
      console.log(ex);
      throw ex;
    } finally {
      await con.release();
      await con.destroy();
    }
  }
};

This class has all of the methods for the CRUD. All we need to do is "START A TRANSACTION, execute our query and "COMMIT" to the DATABASE". Finally, we have to execute the CRUD methods.

const TodoDao = require("./Dao/TodoDao");
const todoDao = new TodoDao();

const app = async () => {
    let savedTodo = await todoDao.saveEntity({
        title: "Read a book of JS",
        completed: 0
    });
    console.log("Saved todo --> ", savedTodo)

    savedTodo.completed = 1;
    let isUpdated = await todoDao.updateEntity(savedTodo);
    console.log("Is it updated --> ", isUpdated);

    let todoList = await todoDao.readEntities();
    console.log("List of todo --> ", todoList);

    let isDeleted = await todoDao.deleteEntity(savedTodo.id);
    console.log("Is it deleted --> ", isDeleted)
}

app();

And the result:

Saved todo -->  { title: 'Read a book of JS', completed: 0, id: 1 }
Is it updated -->  true
List of todo -->  [ { id: 1, title: 'Read a book of JS', completed: 1 } ]
Is it deleted -->  true