How to bulk insert array of objects and insert MySQL in NodeJS

Question

I noticed some similar questions here, namely question numbers: 23501241, 20122962, and 50691096 here on StackOverflow, but none solved my problem unfortunately.

I have an array of objects that look like this:

 [ {
    title: '2015 Jeep Wrangler Unlimited Sport SUV 4D',
    price: '$15,998',
    location: 'Tampa, Florida',
    miles: '135K miles',
    itemURL: '/marketplace/item/656602258232102/',
    imgUrl: 'https://example.com',
    seen: 0,
    created_date: 2020-05-16T14:51:30.000Z
  }
]

I have been trying, unsuccessfully, all day to insert that array into a MySQL database. Here is my latest code:

const saveNewJeeps = async function (entity) {
    var con = await dbConnection();
    let objLen = entity.length;

    // FOR EACH OBJECT IN ARRAY...
    for (var i = 0; i < objLen; i++) {
        var savedJeeps = con.query('INSERT INTO newjeeps SET ?', entity[i], function (err, result) {
            // Neat!
            console.log(result);
        });  
    }

    con.release();

}

What's interesting is, when I run the function independantly, by creating an object like the one above, and sending it through the function ... it works. But in my app workflow, it is failing.

I always get the same error, complaining about the length being undefined in the beginning of the loop.

Here is my entire code:

const puppeteer = require('puppeteer');
const jsonfile = require("jsonfile");
const _ = require("lodash");
var mysql = require('mysql');
const dbConnection = require("./dbConnection");

const getSavedItems = async function () {
    let con = await dbConnection();
    try {
        await con.query("START TRANSACTION");
        let savedItems = await con.query("SELECT * FROM jeeps");
        await con.query("COMMIT");
        //console.log(savedItems);
        return savedItems;
    } catch (ex) {
        console.log(ex);
        throw ex;
    } finally {
        await con.release();
        await con.destroy();
    }
}

const saveNewJeeps = async function (entity) {
    var con = await dbConnection();
    let objLen = entity.length;

    // FOR EACH OBJECT IN ARRAY...
    for (var i = 0; i < objLen; i++) {
        var savedJeeps = con.query('INSERT INTO newjeeps SET ?', entity[i], function (err, result) {
            // Neat!
            console.log(result);
        });  
    }

    con.release();

}

// Gets current items  Search Results
const getItems = async searchTerm => {

    browser = await puppeteer.launch({
        headless: true,
        timeout: 0,
        args: ["--no-sandbox"]
    });

    page = await browser.newPage();
    await page.goto(`https://facebook.com/marketplace/tampa/search/?query=${encodeURI(searchTerm)}&sort=created_date_descending&exact=false`);
    await autoScroll(page);

    const itemList = await page.waitForSelector('div > div > span > div > a[tabindex="0"]')
        .then(() => page.evaluate(() => {

            const itemArray = [];
            const itemNodeList = document.querySelectorAll('div > div > span > div > a[tabindex="0"]');

            itemNodeList.forEach(item => {

                const itemTitle = item.innerText;
                const itemURL = item.getAttribute('href');
                const itemImg = item.querySelector('div > div > span > div > a > div > div > div > div > div > div > img').getAttribute('src');

                var obj = ['price', 'title', 'location', 'miles',
                        ...itemTitle.split(/\n/)
                    ]
                    .reduce((a, c, i, t) => {
                        if (i < 4) a[c] = t[i + 4]
                        return a
                    }, {});

                obj.imgUrl = itemImg;
                obj.itemURL = itemURL;

                itemArray.push(obj);
            });

            return itemArray;

        }))
        .catch(() => console.log("Selector error."));

    return itemList;

}


// This takes care of the auto scrolling problem
async function autoScroll(page) {
    await page.evaluate(async () => {
        await new Promise(resolve => {
            var totalHeight = 0;
            var distance = 100;
            var timer = setInterval(() => {
                var scrollHeight = document.body.scrollHeight;
                window.scrollBy(0, distance);
                totalHeight += distance;

                if (totalHeight >= scrollHeight || scrollHeight > 9000) {
                    clearInterval(timer);
                    resolve();
                }
            }, 100);
        });
    });
}


const getDifferences = async function (objNew, objOld) {

    return _.difference(objNew, objOld);
}

const init = async function () {
    const newItems = await getItems("Jeep Wrangler");
    const oldItems = await getSavedItems();
    const finalArray = await getDifferences(newItems, oldItems);
    const saveSuccess = await saveNewJeeps(finalArray);

}


const myObj =  [ {
    title: '2015 Jeep Wrangler Unlimited Sport SUV 4D',
    price: '$15,998',
    location: 'Tampa, Florida',
    miles: '135K miles',
    itemURL: '/marketplace/item/656602258232102/',
    imgUrl: 'https://example.com',
    seen: 0
  }, 
  {
    title: '2020 BMW SUV 4D',
    price: '$55,998',
    location: 'gyyu, Florida',
    miles: '15K miles',
    itemURL: '/marketplace/item/6566102/',
    imgUrl: 'https://example2.com',
    seen: 0
  }
];

// This will work just fine.
saveNewJeeps(myObj);

// But going this way, it fails...
init();

Can anyone see why this is failing? Thank you for looking.

Answer 1

    currentLogs = [
 { socket_id: 'Server', message: 'Socketio online', data: 'Port  3333', logged: '2014-05-14 14:41:11' },
 { socket_id: 'Server', message: 'Waiting for Pi to connect...', data: 'Port: 8082', logged: '2014-05-14 14:41:11' }
];

console.warn(currentLogs.map(logs=>[ logs.socket_id , logs.message , logs.data , logs.logged ]));

Bulk inserts are possible by using nested array, see the github page

Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd')

You just insert a nested array of elements.

An example is given in here

var mysql = require('mysql');
var conn = mysql.createConnection({
    ...
});

var sql = "INSERT INTO Test (name, email, n) VALUES ?";
var values = [
    ['demian', 'demian@gmail.com', 1],
    ['john', 'john@gmail.com', 2],
    ['mark', 'mark@gmail.com', 3],
    ['pete', 'pete@gmail.com', 4]
];
conn.query(sql, [values], function(err) {
    if (err) throw err;
    conn.end();
});

Note:

values

is an array of arrays wrapped in an array

[ [ [...], [...], [...] ] ]

There is also a totally different node-msql package for bulk insertion

hope it's helpful :)

answer from here

Answer 2

I found the answer by taking a whole different route. Not sure if I should remove this or not.