Fastest way to get JSON object into mysql using node

Question

Using a prior example? How could I insert/update a mysql table using
a JSON object without manually naming the table column headers? And insure it async.

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


var values = [
    {name:'demian', email: '[email protected]', ID: 1},
    {name:'john'  , email: '[email protected]'  , ID: 2},
    {name:'mark'  , email: '[email protected]'  , ID: 3},
    {name:'pete ' , email: '[email protected]'  , ID: 4}
];

// var sql = "INSERT INTO Test (name, email, n) VALUES ?";


   conn.query(sql, [values], function(err) {
        if (err) throw err;
        conn.end();
    })

Kevin Simper · Accepted Answer · 2013-03-18 09:42:22Z

6

You could do something like this:

for(var i = 0; i < values.length; i++){
    var post  = values[i]
    var query = connection.query('INSERT INTO posts SET ?', post, function(err, result) {
         // Finish
    });
}

EDIT

This is how you inserts multiple 'posts' at once.

INSERT INTO posts (type, details)
  VALUES
  ('Helen', 24),
  ('Katrina', 21),

You would have to loop through the first value to get the names like this.

var names = [];
for(name in values[0]){
names.push(name);
// That would give you name, email, id
}

Then you would have to create your own string to insert.

var newvalues = [];
for(var i = 0; i < values.length; i++){
    newvalues.push('(' + values[i].join(',') + ')');
}

Then to execute the query:

connection.query('INSERT INTO posts (' + names.join(',') + ') VALUES ' + newvalues.join(',') , function(err, rows, fields) {
  // Result
});

You would have to test the code yourself, this is just how you would do it.

edited Mar 18, 2013 at 9:42

answered Mar 12, 2013 at 21:34

Kevin Simper

1,7072 gold badges20 silver badges32 bronze badges

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

7 Comments

Merlin Over a year ago

Can you change the variables to match the questions?

Merlin Over a year ago

Seem like database is access every time in the loop. Is there a way to only access the database once? A bulk insert?

Merlin Over a year ago

What about colHeader = Object.keys(values[0]); to get keys? Is there something similar for values? This removes one for loop- well one written for-loop.

user2727195 Over a year ago

@KevinSimper can you please attempt to answer this, it's somehow related but I don't want to get into any kind of loop, just sql statement taking care of everything... stackoverflow.com/questions/33003517/…

Kevin Simper Over a year ago

@user2727195 Does my answer here solve that for you?

|

Nick Mitchinson · Accepted Answer · 2013-03-12 17:46:48Z

2

Look at the 'Custom Format' part here. If you notice, this example using named placeholders in the query, allowing you to pass an object, and the placeholders are replaced with the matching attributes from the object. I've also pasted the relevant section for clarity:

connection.config.queryFormat = function (query, values) {
  if (!values) return query;
  return query.replace(/\:(\w+)/g, function (txt, key) {
    if (values.hasOwnProperty(key)) {
      return this.escape(values[key]);
    }
    return txt;
  }.bind(this));
};

connection.query("UPDATE posts SET title = :title", { title: "Hello MySQL" });

answered Mar 12, 2013 at 17:46

Nick Mitchinson

5,4801 gold badge27 silver badges31 bronze badges

Comments

Evert · Accepted Answer · 2013-03-12 17:04:56Z

0

You could create a small function that maps an array with that format to an insert statement.

You can easily loop through the fields and use some string concatenation.

answered Mar 12, 2013 at 17:04

Evert

101k18 gold badges136 silver badges221 bronze badges

Comments

Yuri Martuccelli · Accepted Answer · 2016-09-19 03:20:34Z

0

Better option is (using MySQL Connection):

app.get('/yourcontroller/:id', function (req, res) {

    var id = req.params.id;
    var dataUpdate = req.body;

    connection.query(
      'UPDATE yourtable SET ? Where ID = ?',
      [dataUpdate, id],
      function (err, result) {
        if (err) throw err;
        console.log('Updated data ! Changed ' + result.changedRows + ' rows');
      }
    );

});

edited Sep 19, 2016 at 3:20

answered Sep 19, 2016 at 3:13

Yuri Martuccelli

365 bronze badges

Collectives™ on Stack Overflow

Fastest way to get JSON object into mysql using node

4 Answers 4

EDIT

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

EDIT

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related