Node Hapi.js bulk update to mariaDB

Question

I'm looking for a way to do a bulk update on my MariaDB. I use the Node.js mariaDB plugin and HAPI.js with Handlebars. I'm very new at javascript but got already a far way on my little project to do some research on working dogs (Belgium Malinois). It's for myself to learn working with Javascript, Node and MariaDB.

My issue: I have a webpage with multiple parameters to edit through a form.

The database: aped_db.parameters

TABLE `parameters` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
    `description` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
    `opt0` VARCHAR(50) NOT NULL DEFAULT 'Unknown' COLLATE 'utf8mb4_general_ci',
    `opt1` VARCHAR(50) NOT NULL DEFAULT 'Very bad' COLLATE 'utf8mb4_general_ci',
    `opt2` VARCHAR(50) NOT NULL DEFAULT 'Bad' COLLATE 'utf8mb4_general_ci',
    `opt3` VARCHAR(50) NOT NULL DEFAULT 'Ok' COLLATE 'utf8mb4_general_ci',
    `opt4` VARCHAR(50) NOT NULL DEFAULT 'Good' COLLATE 'utf8mb4_general_ci',
    `opt5` VARCHAR(50) NOT NULL DEFAULT 'Very good' COLLATE 'utf8mb4_general_ci',
    `multiplier` FLOAT NULL DEFAULT '1',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `name` (`name`) USING BTREE
)

The HTML code in short

{{#each parlist}}
    <input type="hidden" name="id" value="{{this.id}}" form="form-edit">
    <td style="text-align:center;">{{this.id}}</td>
    <td><input type="text" name="name" value="{{this.name}}" form="form-edit"></td>
    <td><input type="text" name="description" value="{{this.description}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt0}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt1}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt2}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt3}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt4}}" form="form-edit"></td>
    <td><input type="text" name="opt0" value="{{this.opt5}}" form="form-edit"></td>
    <td><input type="text" name="multiplier" value="{{this.multiplier}}" form="form-edit"></td>
{{/each}}

The received payload to process into the table parameters:

req.payload = {
   id: [ '1', '3', '' ],
   name: [ 'Social', 'Work ethic', 'Dominance' ],
   description: [ 'Desc 1', 'Desc 2', 'Desc 3'],
   opt0: [ 'Unknown', 'Unknown', 'Unknown' ],
   opt1: [ 'Very bad', 'Very bad', 'Very bad' ],
   opt2: [ 'Bad', 'Bad', 'Bad' ],
   opt3: [ 'Ok', 'Ok', 'Ok' ],
   opt4: [ 'Good', 'Good', 'Good' ],
   opt5: [ 'Very good', 'Very good', 'Very good' ],
   multiplier: [ '1', '1', '1' ]
}

In the above payload 2 parameters are existing ones, and the third one needs to be a new INSERT. The issue I have is, that for each key there is an array. I would have expected an array for each row.

Parameters id '1' & '3' are existing ones to update, par id '' is a new one to insert.

Because the payload gives an array for each key, I'm not sure how to proceed. How to convert the payload to something more usable.

I tried looking on google, but didn't find a good example that I could follow (that I understand). I'm very new at this.

Anyone can put me on the right track?

Thx

Answer 1

In your

req.payload = {
   id: [ '1', '3', '' ],
   name: [ 'Social', 'Work ethic', 'Dominance' ],
   description: [ 'Desc 1', 'Desc 2', 'Desc 3'],
   opt0: [ 'Unknown', 'Unknown', 'Unknown' ],
   opt1: [ 'Very bad', 'Very bad', 'Very bad' ],
   opt2: [ 'Bad', 'Bad', 'Bad' ],
   opt3: [ 'Ok', 'Ok', 'Ok' ],
   opt4: [ 'Good', 'Good', 'Good' ],
   opt5: [ 'Very good', 'Very good', 'Very good' ],
   multiplier: [ '1', '1', '1' ]
}

payload we can see all the fields that you have and each index representing a value. An index of 0 represents the first record, with an id of '1', name of 'Social', etc.

A new record has an id of '', the other records are existent records to be updated. So you will need to run the insert(s) and the update(s). MariaDB allows you to group insert, like:

insert into yourtable(c1, c2, c3)
values
(v1_1, v1_2, v1_3),
(v2_1, v2_2, v2_3);

So after specifying which columns you want to insert, you have the values keyword after which there are parantheses separated by comma, each paranthesis represents a record to be inserted and inside the paranthesis you have the column values separated by comma. Of course, you will need to parameterize the actual values, but the idea looks like this:

//building a keys array
let mykeys = [];
for (let key in items) {
    mykeys.push(key);
}
//building a values array
let values = [];
for (let index = 0; index < items[mykeys[0]].length; index++) {
    let record = {};
    for (let key of mykeys) {
        record[key] = items[key][index];
    }
    values.push(record);
}

Then, you can loop values and check for id on each step. If it's empty, add it to your insert. If it's not empty, add it to your array. I have already shown how your insert needs to be look alike, now let's focus how your update needs to look alike. You have three main options to choose from.

You can have stuff like this:

update yourtable
set c1 = v1_1,
    c2 = v1_2,
    c3 = v1_3
where id = id1;
update yourtable
set c1 = v2_1,
    c2 = v2_2,
    c3 = v2_3
where id = id2;

updating each record individually, or you can have one composite update (I didn't test this one):

update yourtable
set c1 = case
        when id = id1 then v1_1
        when id = id2 then v2_1
        when id = id3 then v3_1
    end,
    c2 = case
        when id = id1 then v1_2
        when id = id2 then v2_2
        when id = id3 then v3_2
    end,
    c3 = case
        when id = id1 then v1_3
        when id = id2 then v2_3
        when id = id3 then v3_3
    end
where id in (id1, id2, id3);

or you can have a replace into:

replace into yourtable(id, c1, c2, c3)
values
(123, v1_1, v1_2, v1_3),
(456, v2_1, v2_2, v2_3),
(789, v3_1, v3_2, v3_3)

Of course, you will need to generate these commands and to parameterize them.