Introduction
This article is the third part of a 10 part series that I posted as a tutorial for the readers. I will go through step-by-step going over how I created a link shortener app from scratch.
In this post, I will add additional helper functions in our database so that we could interact with our MySQL server more easily without the need to write a manual MySQL query everytime.
Methods
In this post, we will be only touching the db.js that we created in the second part of the series.
“Get” method
This method is a simple one, it is for us filter a table by a variable. It will take 3 parameters, the table (X), the columns (Y), and the value it is filtering on.
so for example if we want to get all the links of a user with an id 1, we can run getXbyY("link", "owner_id", 1).
Code:
/**
* getXbyY - a filtering function for mysql to make process easier
*
* @param {String} X
* @param {String} Y
* @param {Value} value
*/
getXbyY(X, Y, value, cb) {
const query = `SELECT * FROM ${X} WHERE ${Y} = '${value}'`;
this.con.query(query, cb);
}“Add” method
This method is also a simple one, it is for us to add a new entry in the table.
However, before we do that, let us create a helper function that format an object to an SQL Query.
Code:
/**
* _formatObjectToAddQuery - formatting object to become an add SQL Query
* @param {String} item - name of object / table in db
* @param {Object} object - object that wants to be added to the db
* @returns string query for add SQL
*/
_formatObjectToAddQuery(item, object) {
var values = Object.values(object)
var valuesList = []
var keyList = []
for (let i = 0; i < values.length; i++) {
if (typeof (values[i]) == 'number') {
valuesList.push(`${values[i]} `)
keyList.push(Object.keys(object)[i])
} else if (typeof (values[i]) == 'boolean') {
valuesList.push(`${values[i]} `)
keyList.push(Object.keys(object)[i])
} else if (typeof (values[i]) == 'string') {
valuesList.push(`'${values[i].split("'").join("\"").split("`").join("\`")}'`)
keyList.push(Object.keys(object)[i])
} else {
console.error(`invalid type of object : ${values[i]} (${typeof (values[i])})`)
//valuesList.push(`NULL`)
}
}
var valuesString = valuesList.join(", ")
var keysString = keyList.join(", ")
return `INSERT INTO ${item} (${keysString})
VALUES(${valuesString})`;
}Since the hard part is done, we can now easily create our add method by the following code:
/**
* add - add object to database
*
* @param {String} item
* @param {Object} object
* @param {Function} cb
*/
add(item, object, cb) {
let query = this._formatObjectToAddQuery(item, object);
this.con.query(query, cb);
}“Remove” method
Next, we can create the remove method which will allow us to remove entry from the table. Like before, let us create a helper function that format an object so that it is SQL Query friendly. i.e. to add " if it is a string this will prove to be useful for the next steps as well.
Code:
/**
* _formatValueForQuery - format value to make sure it is coherent with the string format of SQL query
* @param {*} value - any arbitrary value
* @returns
*/
_formatValueForQuery(value) {
if (typeof (value) == 'number') {
return `${value}`
} else if (typeof (value) == 'string') {
return `'${value.replace("'", "\"")}'`
} else {
console.error(`invalid type of object : ${value} (${typeof (value)})`)
return value
}
}Now, we can now easily create our remove method by the following code:
/**
* remove - remove object to database
*
* @param {String} item
* @param {String} key
* @param {Number / String} value
* @param {Function} cb
*/
remove(item, key, value, cb) {
let v = this._formatValueForQuery(value)
let query = `DELETE FROM ${item} WHERE ${key} = ${v}`
this.con.query(query, cb);
}Modify" method
Next, we can create the modify method which allow us to modify entry given a certain where key and where value. Code:
/**
* modify - modify object to database
*
* @param {String} item
* @param {Object} newRow
* @param {String} whereKey
* @param {Number / String} whereValue
* @param {Function} cb
*/
modify(item, newRow, whereKey, whereValue, cb) {
let setCommands = [];
let keys = Object.keys(newRow)
for (let i = 0; i < keys.length; i++) {
if (newRow[keys[i]]) {
setCommands.push(`${keys[i]} = ${this._formatValueForQuery(newRow[keys[i]])}`)
}
}
if (setCommands.length !== 0) {
let whereV = this._formatValueForQuery(whereValue)
let query = `UPDATE ${item} SET ${setCommands.join(", ")} where ${whereKey} = ${whereV}`;
this.con.query(query, cb);
} else {
cb("Nothing has changed", [])
}
}“getRowCount” method
For the sake of writing a helper function, it would be great to write a getRowCount function, getRowCountWhereY, and getRowCountWhereYGroupBy where it returns us the number of records in the whole table, a filtered table, and a filtered table grouped respectively.
Code:
/**
* getRowCount - get row count of a table
* @param {String} item - name of item / table
* @param {Function} cb
*/
getRowCount(item, cb) {
let query = `SELECT COUNT(*) FROM ${item};`
this.con.query(query, cb);
}
/**
* getRowCountWhereY - get row count of a table with a filter
* @param {String} item - name of item / table
* @param {String} Y - name of column
* @param {String/Number} value
* @param {Function} cb
*/
getRowCountWhereY(item, Y, value, cb) {
let query = `SELECT COUNT(*) as count FROM ${item} WHERE ${Y}=${this._formatValueForQuery(value)};`
this.con.query(query, cb);
}
/**
* getRowCountWhereYGroupBy - get row count of a table with a filter and grouped
* @param {String} item - name of item / table
* @param {String} Y - name of column
* @param {String/Number} value
* @param {String} groupBy - columns to group by
* @param {Function} cb
*/
getRowCountWhereYGroupBy(item, Y, value, groupBy, cb) {
let query = `SELECT COUNT(*) as count FROM ${item} WHERE ${Y}=${this._formatValueForQuery(value)} GROUP BY ${groupBy};`
this.con.query(query, cb);
}“getUserByEmailWithPw” method
Since we are storing our password data separately we will create a function to fetch a user hashed password by their email. This will assist us in the login process.
Code:
/**
* getUserByEmailWithPw - get user by email with password
* @param {email} email
* @param {function} cb
*/
getUserByEmailWithPw(email, cb) {
let query = `SELECT user.*, pw.hashed_password
FROM user
LEFT JOIN pw
ON user.id = pw.user_id
WHERE user.email='${email}'`
this.con.query(query, cb);
}Conclusion
Remember that the code for this part is in github.
In this part, our database handler is now equiped with most of the necessary interactions with our MySQL database and in the next part, we will start adding front-end to our app! Hope you are all excited 😊