在NodeJS中，如何使用node-postgres模块将JSON对象另存为文本? [英] In NodeJS how to save JSON object as text with node-postgres module?

问题描述

最后，我从支持JSON数据类型的postgresql 9.1前进到了postresql 9.3.然后，相同的代码即可正常工作.

Finally i move forward from postgresql 9.1 to postresql 9.3 that supports JSON data type. Then the same code function properly.

但是我认为，如果有人知道我仍然想知道的事情，那么我想做的事情就可以完成.

However i think that what i want to do in the first place can be done... if someone know how i still want to know.

环境
节点v0.10.28
pg v3.3.0
postgresql 9.1

Enviroment
node v0.10.28
pg v3.3.0
postgresql 9.1

我收到了这个插入查询

INSERT INTO sessions(sid, user_id, session_object) VALUES ('id1', 1, '{"id":"fX2HkXYLclB","data": testing"}') RETURNING session_id

从pgAdmin(或命令行)进行测试时，它可以正常工作，但是当我的应用尝试从pg.client.query运行它时，它将尝试将对象转换为保存"[object object]"的字符串.这是节点代码:

When testing it from pgAdmin (or command line) it works fine, but when my app try to run it from pg.client.query it try to turn the object as a string saving "[object object]". Here is the node code:

var session = {"id":"fX2HkXYLclB","data": "testing"};
var sql = 'INSERT INTO sessions(sid, user_id, session_object) VALUES (\'id1\', ' +
          ' 1, \''+JSON.stringify(session)+'\' ) RETURNING session_id';
console.log(sql);
client.query(sql, function(err, info) {
  if(err) {
  return addSessionCB(err, null);
  }
  return addSessionCB(null, info.rows[0].session_id);
});

运行该应用程序并在pg_admin上手动测试查询后，该表将显示这2个结果.

After runing the application and testing the query manually on pg_admin the table show this 2 results.

 session_id |     sid     | user_id |            session_object             | active 
------------+-------------+---------+---------------------------------------+---------
          1 | fX2HkXYLclB |       1 | [object Object]                       | t
          2 | fX2HkXYLclB |       1 | {"id":"fX2HkXYLclB","data": "testing"}| t
(2 filas)

所以，问题是‘我对pg.query做错了什么?

So, the question is ¿what im doing wrong with the pg.query?

添加原始代码后，以前的代码经过简化以提高可读性.

Adding the original code, the code before is simplified for readability.

function addSession(session, addSessionCB) {
  log.log('debug', '[PSQL]Add new session on DB. \nsession: '+ session.id +
    '\nuser:'+ session.data.user);
  function executeAddSessionQuery(user_id){
    var sql = 'INSERT INTO sessions(sid, user_id, session_object) VALUES (\'' +
      session.id + '\', '+user_id+', \''+JSON.stringify(session)+'\' ) ' +
      'RETURNING session_id';
    log.log('debug', '[PSQL]Adding session: '+session.id+' for user_id: '+
      user_id+'\nSQL: '+sql);
    client.query(sql, function(err, info) {
      if(err) {
        log.log('debug', '[PSQL]Error adding new session. \n'+err);
        return addSessionCB(err, null);
      }
      return addSessionCB(null, info.rows[0].session_id);
    });
  };
//to save session we need to know user id
  getUserByName({name: session.data.user},
    function getUserByNameCB(err, user_result){

      if(err || !user_result){
        //if error or not result we try to save new user
        log.log('debug', '[PSQL]Associated user not found. Creating a new ' +
          'user entry. ');
        addUser({name: session.data.user},
          function addUserCB(err, newUserID){
            if(err){
              log.log('warn', '[PSQL]Session user didn\'t exists and cannot be ' +
                'added to DB');
              return addSessionCB(err, null);
            }
            executeAddSessionQuery(newUserID);
        });
      } else {
        //if the user exist we use his id.
        executeAddSessionQuery(user_result.user_id);
      }
  });
}

推荐答案

节点postgres模块具有另一种形式的 query ；其中第二个参数是对象数组.所以就你而言

The node postgres module has another form of query; where the 2nd parameter is an array of objects. So in your case

var sql = 'INSERT INTO sessions(sid,user_id,session_object) VALUES ($1,$2,$3) RETURNING session_id';
var values = [id1,1,JSON.stringify(session)];

，然后使用

client.query(sql,values,function(err,info) {
...

这还具有防止SQL注入攻击的额外好处.

This also has the added benefit of guarding against SQL injection attacks.

这篇关于在NodeJS中，如何使用node-postgres模块将JSON对象另存为文本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！