Bulk inserting with Node mssql package

Question

I'm trying to insert around 20.000 users at the same time in my MSSQL Server from a csv file. I created an API for my Angular2 application and I parsed the csv file to JSON format. But now I'm kind of stuck, I found an answer for this from more that 2 years old so that's not working anymore. Any help?

This is the code I'm already using:

//Insert gebruikers from CSV file
router.post('/gebruikers/csv', type, (req, res) => {

    fs.readFile(req.file.path, 'utf8', function (err, csvData) {
        if (err) {
            res.send("error in file reader: " + err);
            return console.log(err);
        }
        csvParser(csvData, {
            delimiter: ',',
            columns: true
        }, function (err, json) {
            if (err) {
                res.send("error in csvParser: " + err);
                console.log(err);
            } else {
                console.log(json);
                //I think the query should be done here... 
            }
        })
    })
});

Thanks in advance!!

EDIT

Using this code right now but it returns this error:

TypeError: parameter.value.getTime is not a function

Code:

router.post('/gebruikers/csv', type, (req, res) => {

    fs.readFile(req.file.path, 'utf8', function (err, csvData) {
        if (err) {
            res.send("error in file reader: " + err);
            return console.log(err);
        }
        csvParser(csvData, {
            columns: true,
            ltrim: true,
            rtrim: true
        }, function (err, json) {
            if (err) {
                res.send("error in csvParser: " + err);
                console.log(err);
            } else {
                console.log(json);
                const table = new sql.Table('[Alg].[User]');
                table.create = true;
                table.columns.add('Firstname', sql.NVarChar, { nullable: false });
                table.columns.add('LastName', sql.NVarChar, { nullable: false });
                table.columns.add('PrivateMail', sql.NVarChar, { nullable: false });
                table.columns.add('UserName', sql.NVarChar, { nullable: false });
                table.columns.add('Password', sql.NVarChar, { nullable: false });
                table.columns.add('Auth', sql.NVarChar, { nullable: false });
                table.columns.add('Enabled', sql.Bit, { nullable: false });
                table.columns.add('Created', sql.SmallDateTime, { nullable: false });
                table.columns.add('Manual', sql.Bit, { nullable: false });
                table.columns.add('LastChanged', sql.SmallDateTime, { nullable: false });
                table.columns.add('Staff', sql.Bit, { nullable: false });

                var count = Object.keys(json).length;
                console.log(count);

                for (i = 0; i < count; i++) {
                    table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, "GetDate()", 1, "GetDate()", 1);
                }

                console.log("Async function started!");
                const request = new sql.Request();
                request.bulk(table, (err, result) => {
                    // error checks? 
                    if (err) {
                        console.log("ERROR post bulk gebruikers: " + err);
                    }
                    else {
                        res.send("SUCCES! " + result);
                    }
                })
            }
        })
    })
});

FIXED

As user Grigoriy Chudnov pointed out, I need an instance of new Date(). I was thinking this wouldn't work because it would be in the wrong format, but node-mssql handles this for me.

currentDateTime = new Date();
table.rows.add(json[i].Firstname, json[i].LastName, json[i].PrivateMail, json[i].UserName, json[i].Password, 'manual', 1, currentDateTime, 1, currentDateTime, 1);

Solution

If you're using node-mssql library, use bulk insert to add multiple records at once.

It should look something like this:

'use strict';

const sql = require('mssql');

const user = 'sa';
const password = 'yourStrong(!)Password';

const connStr = `mssql://${user}:${password}@172.17.0.2:1433/tempdb?encrypt=true`;

sql.connect(connStr)
  .then(() => {
    console.log('connected');

    const table = new sql.Table('my_users');
    table.create = true;
    table.columns.add('id', sql.Int, { nullable: false, primary: true });
    table.columns.add('name', sql.VarChar(128), { nullable: false });

    // add here rows to insert into the table
    table.rows.add(1, 'Alice');
    table.rows.add(2, 'Bob');
    table.rows.add(3, 'Carol');

    const request = new sql.Request();
    return request.bulk(table)
  })
  .then(data => {
    console.log(data);
  })
  .catch(err => {
    console.log(err);
  });

And the output is:

connected
{ rowsAffected: 3 }

Comments

[Milan_w]

I'm getting this error: TypeError: parameter.value.getTime is not a function and I'm guessing it's because I have to use the SQL function getDate(). Any ideas? Thanks in advance!!

[Grigorii Chudnov]

Check the type if parameter.value. Guess, you expected it to be Date but it is not. to check it, use: XXXX instanceof Date. Or just print its value it to the console.

[Milan_w]

I'm not really following what you want me to check, I'll add the code I'm trying in my original question if you wan't to take a look? Thanks in advance!

[TKoL]

How do you use this with a ConnectionPool? new connectionPool.Request() was my first guess but that didn't work.

[TKoL]

Figured it out btw, when you call const request = new sql.Request();, supply the connectionPool object as an argument, const request = new sql.Request(connectionPool);

[TKoL]

will also work as const request = connectionPool.request();

[Milan_w]

@JithinPariyarath I'm not working with Angular2 anymore... Hopefully someone else will help you!

[Aress Support]

Getting 'ConnectionError:Connection is closed' error

[Naresh Ramoliya]

I am getting UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'columns' of null for sql server 2017 and its working fine for sql server 2014

[Michael Davidson]

I had to run this as await sql.connect... to get this to work, i'm running it within an async function. If i'm not making sense, I'm not the best at async await in JS.