Is it the correct way to write a function in NodeJS to perform update operation on MySQL database?

34 views Asked by At

I created an module called Models (yeah, for relational DBs) in NodeJS for all CRUD operations because I didn't want to use any ORM available. I am using "mysql2 version 3.8.0" library to connect with MySQL database. I created "js" file for each "relation or table" available in Database. For example there is a relation called "Customer" in my Database and I created "customer.js" file which has methods like "fetchAll" to fetch all "customers", "fetchOne(object)" which only fetch single entry based on "column" provided in "object". Now lets come to my original question. So I wrote a method to update entries in "Customer" relation.

async update(customerObject, conditionArr){
    const timestamp = moment(new Date()).format('YYYY-MM-DD HH:mm:ss');
    
    const columns = Object.keys(customerObject);
    const values = Object.values(customerObject);

    const placeholder = columns.map(()=> "?? = ?").join(",");

    const query = `UPDATE customers SET ${placeholder}, ?? = ? WHERE ?? = ?`;

    const params = [];

    for(let i = 0; i < columns.length; i++){
        params.push(columns[i]);
        params.push(values[i]);
    }

    params.push("updated_at");
    params.push(timestamp);

    params.push(conditionArr[0]);
    params.push(conditionArr[1]);

    const [result] = await pool.query(query, params);

    return result;    
}

Let me explain the code - customerObject is a JSON object which contains the columns and values which we want to update. for example -

{
    name: "NewName",
    email: "newEmail"
}

and conditionArr is array which tells which tuple we want to update. In simple language it contains Id of the tuple. example - ['id', 10] timestamp is used to update 'updated_at' column.

Now placeholder variable contains generated '?? = ?' based on the number of columns available to update.

After that I put columns and values in alternate form in params array using for loop. and after that I insert updated_at column and its value timestamp. and at the end I insert the column which identities the tuple which we want to update.

So this is my while code for updating the values. And it is working fine. But I want to know if it is the correct way to do this thing? or is there any better way? By the way, I do not want to use ORMs.

0

There are 0 answers