how to dynamically update row in mysql on node express js

Question

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?

Terry Lennox · Accepted Answer · 2020-09-09 08:01:32Z

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);

Javohir Mirzo Fazliddinov · Accepted Answer · 2020-10-18 03:28:09Z

1

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

answered Oct 18, 2020 at 3:28

Javohir Mirzo Fazliddinov

3308 silver badges17 bronze badges

Collectives™ on Stack Overflow

how to dynamically update row in mysql on node express js

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related