how to dynamically update row in mysql on node express js

user_13081589 0
Viewed 3013

this is how i query to update row and it works:

const [rows, meta] = await db.query(
        `
        UPDATE
            Portfolio
        SET
            title = ?,
            infoText = ?,
            devPeriod = ?,
            tags = ?
        WHERE
            id = ?
        `,
        [title, infoText, Number(devPeriod), tags, Number(portfolioId)]
    );
    return rows;

but sometimes depending on what user wants i have to query to update only specific columns. For example user might want to edit only devPeriod or tags and infoText. How do i achieve that?

mariadb express node.js mysql
2 Answers

I'd suggest creating an update object that specifies which fields to update and the relevant values. You can then create a query and parameters from this.

The update object can be populated based on user input.

For example:

async function updatePortfolio(db, portfolioId, update) {
    const query = "Update Portfolio SET " + Object.keys(update).map(key => `${key} = ?`).join(", ") + " WHERE id = ?";
    const parameters = [...Object.values(update), portfolioId];
    console.log("updatePortfolio: Running query:", query);
    const [rows, meta] = await db.query(query, parameters);
    return rows;
}

// Add or remove fields as you require.
update = { 
    title: "Some Title",
    infoText: "Infotext",
    devPeriod: 10,
    tags: "tags"
}

updatePortfolio(db, 1, update);

// Add or remove fields as you require.
update = { 
    title: "Some Title",
    tags: "tags"
}

updatePortfolio(db, 2, update);
user_72372240

After asking my colleagues for help they introduced my to Knex.js to make dynamic query building easier. So I just went with it. Have been using it for dynamic queries since then. Works pretty well for this type of task. Configuration is also easy:

import knex from 'knex';

const queryBuilder = knex({ client: 'mysql' });

export default queryBuilder;

Export it and use it anywhere in your project

user_130815890
Related