Adding bulk data to mysql database

Question

I have a console program where the user scans in serial numbers, and those serial numbers get added to a database.

const mysql = require('mysql2');
const read = require('readline-sync');

const conn = new mysql.createConnection(config);

conn.connect(
function(err){
    if(err){
        throw err;
    }
    else{
        console.log("Connection Established");
        while(1){
            var sn = read.question('Scan in serial number: ');
            conn.query('INSERT INTO test (serial) VALUES (?);',
            [sn], function(err, results, fields){
                if (err){
                    throw err;
                }
                else{
                    console.log("Added stuff");
                }
            });
        }
    }
}
);

When the code runs it successfully connects to the database but queries the database. It continually prompts for user input.

Alternatively, I tried storing serial numbers in an array and then loops through it adding each element, like this.

const mysql = require('mysql2');
const read = require('readline-sync');

var array = [];
var sn = " ";
while (1) {
    sn = read.question('Scan in serial number, or enter "done" if finished scanning');
    if (sn == "done") {
        break;
    }
    array.push(sn);
}

conn.connect(
    function (err) {
        if (err) {
            throw err;
        }
        else {
            console.log("Connection Established");
            array.forEach(function (sn) {
                conn.query('INSERT INTO test (serial) VALUES (?);',
                    [sn], function (err, results, fields) {
                        if (err) {
                            throw err;
                        }
                        else {
                            console.log("Added stuff");
                        }
                    });
            });
        }
    }
);

In this case, it works inconsistently. Sometimes it works fine, and other times it fails to connect and throws a timeout error. Is there a better way to accomplish this and/or am I doing something wrong?

If you try to concatenate all queries and then execute a single string. — ccordon
– ccordon, Commented Jan 15, 2019 at 20:16

ajjohnson190 · Accepted Answer · 2019-01-16 03:32:14Z

1

var promises = []
function dbOp(value) {
  return new Promise(function(resolve, reject) {
      conn.query('INSERT INTO test (serial) VALUES (?);',
                [value], function (err, results, fields) {
                    if (err) {
                        return reject(err)
                    }
                    else {
                        console.log("Added stuff"); 
                        resolve(results)
                    }
  }
}




conn.connect(function(err){
    if(err){
        throw err;
    }
    else{
        for (i = 0; i < array.length; ++i) {
            promises.push(dbOp(array[i]));
        }
    }
});

  Promise.all(promises)
      .then((results) => {
        console.log("done", results);
      })
      .catch((e) => {
          console.log(e)
      });

edited Jan 16, 2019 at 3:32

ajjohnson190

5132 gold badges7 silver badges21 bronze badges

answered Jan 15, 2019 at 20:24

feiiiiii

1,6031 gold badge15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ajjohnson190 Over a year ago

It behaves exactly the same way as before.

feiiiiii Over a year ago

@ajjohnson190 oh , I looked it too quick and thought it was a promise , so basically you need to wrap your callback with native promise and then loop through array push into promise chain.

ajjohnson190 Over a year ago

That works, if you don't mind could you explain what that does? I don't quite understand.

feiiiiii Over a year ago

@ajjohnson190 so to handle db operation , you are using .query which is a callback. I don't know if you can use async/await so I wrapped your callback in a native promise which is another way to handle async operation and then push into a Promise.all which resolves all the promise you add from loop through the array. if you need more please google callback, promise, async/await ways to handle async operations.

Andrey Sidorov · Accepted Answer · 2019-01-18 06:21:37Z

This might be caused by short idle timeout setting in your mysql server. client.connect() is pretty much a no-op in mysql2, it connects immediately when you call mysql.createConnection(). You can change order to establish connection only after all data is collected:

const mysql = require('mysql2');
const read = require('readline-sync');

var array = [];
var sn = ' ';
while (1) {
  sn = read.question('Scan in serial number, or enter "done" if finished scanning');
  if (sn == 'done') {
    const conn = mysql.createConnection(config);
    array.forEach(function(sn) {
      conn.query('INSERT INTO test (serial) VALUES (?);', [sn], function(err, results, fields) {
        if (err) {
          throw err;
        } else {
          console.log('Added stuff');
        }
      });
    });
  }
  array.push(sn);
}

Collectives™ on Stack Overflow

Adding bulk data to mysql database

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related