I'm building a simplel API with ExpressJS and SQLite.
I'm adding a router.patch method that will update data entries in the API database using the id as the primary key. In my database, there is a column called score that is populated by the sum of values from other columns and their values of integer type.
I need my method to update one or more of those columns in the database which should in turn update the score column as well to reflect the new values.
Here is the method so far:
router.patch('/cars/:id', (req, res) => {
try {
let sql = `UPDATE cars SET email = COALESCE(?,email), name = COALESCE(?,name), year = COALESCE(?,year), make = COALESCE(?,make), model = COALESCE(?,model), racer_turbo = COALESCE(?,racer_turbo), racer_supercharged = COALESCE(?,racer_supercharged), racer_performance = COALESCE(?,racer_performance), racer_horsepower = COALESCE(?,racer_horsepower), car_overall = COALESCE(?,car_overall), engine_modifications = COALESCE(?,engine_modifications), engine_performance = COALESCE(?,engine_performance), engine_chrome = COALESCE(?,engine_chrome), engine_detailing = COALESCE(?,engine_detailing), engine_cleanliness = COALESCE(?,engine_cleanliness), body_frame_undercarriage = COALESCE(?,body_frame_undercarriage), body_frame_suspension = COALESCE(?,body_frame_suspension), body_frame_chrome = COALESCE(?,body_frame_chrome), body_frame_detailing = COALESCE(?,body_frame_detailing), body_frame_cleanliness = COALESCE(?,body_frame_cleanliness), mods_paint = COALESCE(?,mods_paint), mods_body = COALESCE(?,mods_body), mods_wrap = COALESCE(?,mods_wrap), mods_rims = COALESCE(?,mods_rims), mods_interior = COALESCE(?,mods_interior), mods_other = COALESCE(?,mods_other), mods_ice = COALESCE(?,mods_ice), mods_aftermarket = COALESCE(?,mods_aftermarket), mods_wip = COALESCE(?,mods_wip), mods_overall = COALESCE(?,mods_overall) where car_id = ?`
let params = [req.body.email,req.body.name,req.body.year,req.body.make,req.body.model,req.body.racer_turbo,req.body.racer_supercharged,req.body.racer_performance,req.body.racer_horsepower,req.body.car_overall,req.body.engine_modificationsreq.body.engine_performancereq.body.engine_chromereq.body.engine_detailingreq.body.engine_cleanlinessreq.body.body_frame_undercarriagereq.body.body_frame_suspensionreq.body.body_frame_chromereq.body.body_frame_detailingreq.body.body_frame_cleanlinessreq.body.mods_paintreq.body.mods_bodyreq.body.mods_wrapreq.body.mods_rimsreq.body.mods_interiorreq.body.mods_otherreq.body.mods_icereq.body.mods_aftermarketreq.body.mods_wipreq.body.mods_overallreq.params.id]
db.run(sql, params, (err, row) => {
res.status(200).json({
message: 'success',
data: {
car_id: req.params.id,
updates: req.body
}
})
})
// Catch error
} catch(error) {
// Log the error to the console
console.log(error.message.red.bold)
// Return a server error code and message
res.status(500).json({
message: 'Not found'
})
}
})
The method works as inteded in that it updates the values for columns like "racer_turbo" or "engine_chrome" etc. And those changes are reflected in the database. However, the score column for each entry that is modified does not change, it still shows the old value. I'm not sure how to modify my code to make sure the score also updates.
Here's my sqlite3 database configuration code as well in case it'll help paint a clearer picture.
// Importing sqlite3
const sqlite3 = require('sqlite3')
// Importing path
const path = require('path')
// Importing csvtojson
const csv = require('csvtojson')
// Path to config file
const config = require('../config')
// Path to data.csv file
const DATA_CSV = path.join(__dirname, '../data/data.csv')
// Enabling colors
config.colors.enable()
let db = new sqlite3.Database(config.database_name, (err) => {
// If we encounter an error while creating the database
if (err) {
// Log the error message
console.error(err.message.red)
// Throw the error as well
throw err
// Otherwise proceed with database creation
}
else {
// Alert the user that they've successfully connected to the database
console.log('Connected to database...'.blue)
// Creating the cars table within our database with the columns from the CSV file
db.run(`CREATE TABLE cars (
car_id INT PRIMARY KEY,
email TEXT UNIQUE,
name TEXT,
year INT,
make TEXT,
model TEXT,
racer_turbo INT,
racer_supercharged INT,
racer_performance INT,
racer_horsepower INT,
car_overall INT,
engine_modifications INT,
engine_performance INT,
engine_chrome INT,
engine_detailing INT,
engine_cleanliness INT,
body_frame_undercarriage INT,
body_frame_suspension INT,
body_frame_chrome INT,
body_frame_detailing INT,
body_frame_cleanliness INT,
mods_paint INT,
mods_body INT,
mods_wrap INT,
mods_rims INT,
mods_interior INT,
mods_other INT,
mods_ice INT,
mods_aftermarket INT,
mods_wip INT,
mods_overall INT,
score INT
)`,
(err) => {
// If we get an error
if (err) {
// Console log the eror
console.log(err.message.red)
}
// Otherwise add the data from our csv file to the database cars table
else {
// Use the csvtojson package to convert the csv data into json format
csv().fromFile(DATA_CSV)
.then(data => {
// SQL Insert query command to put data from the columns into the database table
let insert = 'INSERT INTO cars (Car_ID, Email, Name, Year, Make, Model, Racer_Turbo, Racer_Supercharged, Racer_Performance, Racer_Horsepower, Car_Overall, Engine_Modifications, Engine_Performance, Engine_Chrome, Engine_Detailing, Engine_Cleanliness, Body_Frame_Undercarriage, Body_Frame_Suspension, Body_Frame_Chrome, Body_Frame_Detailing, Body_Frame_Cleanliness, Mods_Paint, Mods_Body, Mods_Wrap, Mods_Rims, Mods_Interior, Mods_Other, Mods_ICE, Mods_Aftermarket, Mods_WIP, Mods_Overall, Score) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
// Loop through all rows inside the csv file and add them into the table
for(const entry of data) {
// Calculating entry score
let score = [parseInt(entry.Racer_Turbo) + parseInt(entry.Racer_Supercharged) + parseInt(entry.Racer_Performance) + parseInt(entry.Racer_Horsepower) + parseInt(entry.Car_Overall) + parseInt(entry.Engine_Modifications) + parseInt(entry.Engine_Performance) + parseInt(entry.Engine_Chrome) + parseInt(entry.Engine_Detailing) + parseInt(entry.Engine_Cleanliness) + parseInt(entry.Body_Frame_Undercarriage) + parseInt(entry.Body_Frame_Suspension) + parseInt(entry.Body_Frame_Chrome) + parseInt(entry.Body_Frame_Detailing) + parseInt(entry.Body_Frame_Cleanliness) + parseInt(entry.Mods_Paint) + parseInt(entry.Mods_Body) + parseInt(entry.Mods_Wrap) + parseInt(entry.Mods_Rims) + parseInt(entry.Mods_Interior) + parseInt(entry.Mods_Other) + parseInt(entry.Mods_ICE) + parseInt(entry.Mods_Aftermarket) + parseInt(entry.Mods_WIP) + parseInt(entry.Mods_Overall)]
// Use the run function from sqlite database
db.run(insert, [
entry.Car_ID, entry.Email, entry.Name, entry.Year, entry.Make, entry.Model, entry.Racer_Turbo, entry.Racer_Supercharged, entry.Racer_Performance, entry.Racer_Horsepower, entry.Car_Overall, entry.Engine_Modifications, entry.Engine_Performance, entry.Engine_Chrome, entry.Engine_Detailing, entry.Engine_Cleanliness, entry.Body_Frame_Undercarriage, entry.Body_Frame_Suspension, entry.Body_Frame_Chrome, entry.Body_Frame_Detailing, entry.Body_Frame_Cleanliness, entry.Mods_Paint, entry.Mods_Body, entry.Mods_Wrap, entry.Mods_Rims, entry.Mods_Interior, entry.Mods_Other, entry.Mods_ICE, entry.Mods_Aftermarket, entry.Mods_WIP, entry.Mods_Overall,
score
]);
}
}).catch(err => {
// log any error we might encounter if any
console.log(err);
});
}
});
}
});
// Export our created database
module.exports = db
Any help is sincerely appreciated as I'm stumped.
If your version of SQLite is 3.31.0 you could define the column score as a generated column:
CREATE TABLE cars (
car_id INT PRIMARY KEY,
email TEXT UNIQUE,
.......................
score INT GENERATED ALWAYS AS (col1 + col2 + .....) VIRTUAL -- or STORED if you want the value stored in the table
);
Change col1 + col2 + ..... to the expression that calculates the value of the column score.
For previous versions of SQLite, either use a trigger or inside the UPDATE statement update the column score also.