We are in the process of developing a Nodejs application for a client who has requested that we use migration scripts to streamline updating the production database. As someone new to MySQL, I am struggling with how to update table contents using only MySQL when API calls are necessary to retrieve updated information, compare it to existing data, and then make updates accordingly.
My current solution involves saving the new data in a JSON file. I am wondering if it is possible to read a JSON file with MySQL and import its contents. This is the approach I am considering:
// Read JSON file and start a loop
UPDATE table SET column = data WHERE id = json_id
// 'data' and 'json_id' represent information from the JSON file
Is this scenario feasible? If so, what would be the best way to accomplish this task?