Store JSON using mysql module for Nodejs

Question

I need to store a JSON in the column 'data' of this table:

I tried to store a json but I receive errors, probably due to for quoting...

connection.connect();

var query = 'INSERT INTO Forecasts (data, createdAt, updatedAt, OrchardId) VALUES ?';


connection.query(query, [['{"id": 1}', '2017-07-06 16:08:19','2017-07-06 16:08:19',3]],function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

OUTPUT

$ npm start

[email protected] start /Users/giovannimarino/Desktop/revo-weather node index.js

{"id":1} INSERT INTO Forecasts (data, createdAt, updatedAt, OrchardId) VALUES ("gio", "2017-07-06 16:08:13", "2017-07-06 16:08:13", 3) MacBook-Pro-di-Giovanni:revo-weather giovannimarino$ npm start

[email protected] start /Users/giovannimarino/Desktop/revo-weather node index.js

/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/Parser.js:79 throw err; // Rethrow non-MySQL errors ^

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''{\"id\": 1}', '2017-07-06 16:08:19', '2017-07-06 16:08:19', 3' at line 1 at Query.Sequence._packetToError (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/sequences/Sequence.js:52:14) at Query.ErrorPacket (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/sequences/Query.js:77:18) at Protocol._parsePacket (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/Protocol.js:280:23) at Parser.write (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/Parser.js:75:12) at Protocol.write (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/Protocol.js:39:16) at Socket. (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/Connection.js:103:28) at emitOne (events.js:115:13) at Socket.emit (events.js:210:7) at addChunk (_stream_readable.js:252:12) at readableAddChunk (_stream_readable.js:239:11) -------------------- at Protocol._enqueue (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/protocol/Protocol.js:141:48) at Connection.query (/Users/giovannimarino/Desktop/revo-weather/node_modules/mysql/lib/Connection.js:208:25) at Object. (/Users/giovannimarino/Desktop/revo-weather/index.js:19:12) at Module._compile (module.js:569:30) at Object.Module._extensions..js (module.js:580:10) at Module.load (module.js:503:32) at tryModuleLoad (module.js:466:12) at Function.Module._load (module.js:458:3) at Function.Module.runMain (module.js:605:10) at startup (bootstrap_node.js:158:16) npm ERR! code ELIFECYCLE npm ERR! errno 1 npm ERR! [email protected] start: node index.js npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the [email protected] start script. npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in: npm ERR!
/Users/giovannimarino/.npm/_logs/2017-07-14T14_16_03_166Z-debug.log

Just an opinion, but if you want to persist data in JSON format, check out MongoDB — Adam Vincent
– Adam Vincent, Commented Jul 14, 2017 at 14:33

Dave Ranjan · Accepted Answer · 2017-07-14 14:30:32Z

3

How about you JSON.strigify your json object and then persist it. While pulling back data you can parse it again.

connection.connect();

var query = 'INSERT INTO Forecasts (data, createdAt, updatedAt, OrchardId) VALUES ?';


connection.query(query, [[JSON.stringify('{"id": 1}'), '2017-07-06 16:08:19','2017-07-06 16:08:19',3]],function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

answered Jul 14, 2017 at 14:30

Dave Ranjan

2,99429 silver badges57 bronze badges

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

1 Comment

gio Over a year ago

I receive this error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\"{\\\"id\\\": 1}\"', '2017-07-06 16:08:19', '2017-07-06 16:08:19', 3' at line 1

Lazyexpert · Accepted Answer · 2017-07-14 14:29:46Z

1

You can simply use:

JSON.stringify(data)

answered Jul 14, 2017 at 14:29

Lazyexpert

3,1741 gold badge22 silver badges33 bronze badges

Collectives™ on Stack Overflow

Store JSON using mysql module for Nodejs

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related