Help for use MySQL.js with Node.js and a helper

mysql.md

MySQL.js Usage with Node.js and a helper.

Requirements :

Before start you will need :

• A Node.JS project with mysql module
• A mysql account with mysql_native_password as authentication method :

  • If this is not the case do this : ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

    Where root is your username, and password your new password.

  • Then run this for refresh privileges : FLUSH PRIVILEGES;

Setup :

Create the helper (or add this 2 function in your actual helper) :

class projectNameHelper {
    query(database, userQuery) {
        return new Promise((resolve, reject) => {
            database.query(userQuery, (error, results, field) => {
                if (error) {
                    reject(error);
                }
                resolve(results);
            });
        });
    }

    preparedQuery(database, userQuery, value) {
        return new Promise((resolve, reject) => {
            database.query(userQuery, value, (error, results, field) => {
                if (error) {
                    reject(error);
                }
                resolve(results);
            });
        });
    }
}

module.exports = new projectNameHelper();

Then create a file named try.js with :

const projectNameHelper = require("./projectNameHelper.js")

class exampleClass {

  async test (database){
      
  }
}

module.exports = exampleClass;

And for finish require configure mysqljs in your app.js file and call your try.js file :

// Init Mysql :
const mysql = require('mysql')
const database = mysql.createConnection({
    host: "",
    user: "",
    password : '',
    database : ''
})

database.connect((error) => {
    if (error) {
        console.log(`Database connection error surfaced : ${error}`)
    } else {
        console.log(`Database connection success !`)
    }
})

// Require your helper :
const tryClass = require("./try.js")

console.log(tryClass.test(database))

So now you have access to two new function :

• projectNameHelper.query()
• projectNameHelper.preparedQuery()

This two functions have two identical argument :

• database : Your mysql database create in your app.js (or else if you use other name)
• userQuery : Your MySQL request (SELECT * FROM user; for example)

And one argument was add to projectNameHelper.preparedQuery() : value. This argument is used to define the values that we will, for example, UPDATE, CREATE or search.

Example :

(Here in another .js file called try.js) in async/await:

Database structure example with some value in :

ID (AutoIncrement, max size 11)	username (string, max value 50)	password (string, max value 25)
0	nenes	hello world
1	misaka	mikoto
2	george	washington

projectNameHelper.query()

...
// Code of class 
async test (database){
    const getAll = await projectNameHelper.query(database, "SELECT * FROM user")
    return getAll
}
...

app.js return :

[
  {
    "id": 0,
    "username": "nenes",
    "password": "hello world"
  },
  {
    "id": 1,
    "username": "misaka",
    "password": "mikoto"
  },
  {
    "id": 2,
    "username": "george",
    "password": "washington"
  }
]

projectNameHelper.preparedQuery()

...
// Code of class 
async test (database){
     let user = {
        username: "usada",
        password: "pekora"
    }

    const addInto = await projectNameHelper.preparedQuery(database, "INSERT INTO user SET ?", [user])


    const getAll = await projectNameHelper.query(database, "SELECT * FROM user")
    return getAll
}
...

app.js return :

[
  {
    "id": 0,
    "username": "nenes",
    "password": "hello world"
  },
  {
    "id": 1,
    "username": "misaka",
    "password": "mikoto"
  },
  {
    "id": 2,
    "username": "george",
    "password": "washington"
  },
  {
    "id": 3,
    "username": "usada",
    "password": "pekora"
  }
]

Troubleshooting :

If you have this time of query : UPDATE user SET ? WHERE userID = ? do this when you call preparedQuery :

    let someData = {
    // Your json here
}
    let userID = 23498576348976
    const update = await projectNameHelper.preparedQuery(database, `UPDATE user SET ? WHERE userID = ${UserID}`, [someData])

Why didn't set userID in someData ? Because if you do this, mysql will think that userID is an element related to SET and not related to WHERE.