How do I do a bulk insert in mySQL using node js

Question

How would one do a bulk insert into mySQL if using something like  https   github com felixge node-mysql

User · Answer

All props to Ragnar123 for his answer   I just wanted to expand it after the question asked by Josh Harington to talk about inserted IDs   These will be sequential   See this answer   Does a MySQL multi-row insert grab sequential autoincrement IDs   Hence you can just do this  notice what I did with the result insertId      var statement    INSERT INTO         sKeys join        VALUES       var insertStatement    tableName  values     var sql   db connection format statement  insertStatement     db connection query sql  function err  result        if  err          return clb err             var rowIds           for  var i   result insertId  i  lt  result insertId   result affectedRows  i            rowIds push i             for  var i in persistentObjects          var persistentObject   persistentObjects i         persistentObject persistentObject idAttributeName      rowIds i             clb null  persistentObjects            I pulled the values from an array of objects that I called persistentObjects    Hope this helps

User · Answer

This is a fast  raw-copy-paste  snipped to push a file column in mysql with node js    11  250k row in few seconds   use strict    const mysql   require  promise-mysql    const fs   require  fs    const readline   require  readline     async function run       const connection   await mysql createConnection       host   1 2 3 4       port  3306      user   my-user       password   my-psw       database   my-db            const rl   readline createInterface   input  fs createReadStream  myfile txt          let total   0    let buff         for await  const line of rl        buff push  line        total        if  buff length   2000     0          await connection query  INSERT INTO Phone  Number  VALUES      buff          console log total         buff                    if  buff length  gt  0        await connection query  INSERT INTO Phone  Number  VALUES      buff        console log total          console log  end      connection close       run   catch console log

User · Answer

Ragnar123 answer is correct  but I see a lot of people saying in the comments that it is not working  I had the same problem and it seems like you need to wrap your array in    like this   var pars          99   1984-11-20   1 1  2 2  200         98   1984-11-20   1 1  2 2  200         97   1984-11-20   1 1  2 2  200       It needs to be passed like  pars  into the method

User · Answer

Few things I want to mention is that I m using mysql package for making a connection with my database and what you saw below is working code and written for insert bulk query   const values        1   DEBUG    Something went wrong  I have to debug this        2   INFO    This just information to end user        3   WARNING    Warning are really helping users        4   SUCCESS    If everything works then your request is successful       const query    INSERT INTO logs id  type  desc  VALUES      const query   connection query query   values   function err  result      if  err        console log  err   err         console log  result   result

User · Answer

Bulk insert in Node js can be done using the below code  I have referred lots of blog for getting this work   please refer this link as well  https   www technicalkeeda com nodejs-tutorials insert-multiple-records-into-mysql-using-nodejs  The working code     const educations   request body educations    let queryParams         for  let i   0  i  lt  educations length  i          const education   educations i       const userId   education user id      const from   education from      const to   education to      const instituteName   education institute name      const city   education city      const country   education country      const certificateType   education certificate type      const studyField   education study field      const duration   education duration       let param           from        to        instituteName        city        country        certificateType        studyField        duration        userId              queryParams push param          let sql        insert into tbl name  education from  education to  education institute name  education city  education country  education certificate type  education study field  education duration  user id  VALUES       let sqlQuery   dbManager query sql   queryParams   function       err      results      fields           let res      if  err          console log err         res             success  false          message   Insertion failed                   else         res             success  true          id  results insertId          message   Successfully inserted                       response send res           Hope this will help you

User · Answer

I was looking around for an answer on bulk inserting Objects   The answer by Ragnar123 led me to making this function   function bulkInsert connection  table  objectArray  callback      let keys   Object keys objectArray 0      let values   objectArray map  obj   gt  keys map  key   gt  obj key       let sql    INSERT INTO     table          keys join           VALUES       connection query sql   values   function  error  results  fields        if  error  callback error       callback null  results            bulkInsert connection   my table of objects   objectArray   error  response    gt      if  error  res send error     res json response         Hope it helps

User · Answer

In case that needed here is how we solved insert of array  request is from postman  You will look at  guests           author id    3     name     World War II      date     01 09 1939       time     16   22      location     39 9333635 32 8597419      guests     2  3  1337  1942  1453      And how we scripted  var express   require  express    var utils   require    custom utils js     module exports   function database       var router   express Router         router post      function req  res  next            database query  INSERT INTO activity  author id  name  date  time  location  VALUES                 ON DUPLICATE KEY UPDATE name   VALUES name   date   VALUES date   time   VALUES time   location   VALUES location                      req body author id  req body name  req body date  req body time  req body location   function err  results  fields               if err                   console log err                   res json   status  utils respondMSG DB ERROR                               else                   var act id   results insertId                  database query  INSERT INTO act guest  user id  activity id  status  VALUES   ON DUPLICATE KEY UPDATE status   VALUES status                              Array from req body guests  map function g   return  g  act id  0        function err  results  fields                       if err                           console log err                           res json   status  utils respondMSG DB ERROR                                               else                           res json                                status  utils respondMSG SUCCEED                              data                                    activity id   act id                                                                                                                                           return router

User · Answer

I was having similar problem  It was just inserting one from the list of arrays  It worked after making the below changes    Passed  params  to the query method   Changed the query from insert  a b  into table1 values         insert  a b  into table1 values     ie  Removed the paranthesis around the question mark     Hope this helps  I am using mysql npm

User · Answer

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

User · Answer

I ran into this today  mysql 2 16 0  and thought I d share my solution    const items          name   alpha   description   describes alpha   value  1               db query       INSERT INTO my table  name  description  value  VALUES          items map item   gt   item name  item description  item value          error  results    gt

User · Answer

If you want to insert object  use this      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  gt   logs socket id   logs message   logs data   logs logged       The output will be         Server    Socketio online    Port  3333    2014-05-14 14 41 11              Server        Waiting for Pi to connect           Port  8082        2014-05-14 14 41 11         Also  please check the documentation to know more about the map function

User · Answer

If Ragnar s answer doesn t work for you  Here is probably why  based on my experience  -   I wasn t using node-mysql package as shown my Ragnar  I was using mysql package  They re different  if you didn t notice - just like me   But I m not sure if it has anything to do with the   not working  since it seemed to work for many folks using the mysql package  Try using a variable instead of     The following worked for me -  var mysql   require  node-mysql    var conn   mysql createConnection                var sql    INSERT INTO Test  name  email  n  VALUES  params   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    params  values   function err        if  err  throw err      conn end          Hope this helps someone

[mysql] How do I do a bulk insert in mySQL using node.js

Examples related to mysql

Examples related to node.js