nodejs api 接收 payload

mysql payload node.js json

yihuanlin 2019-11-18 14:49:38 ‧ 3288 瀏覽

分享至

請問各位大大

我使用node.js (Node.js+Express框架 + body-parser) 建立RESTful API，目的是用來接收感應器post過來的payload，payload資料格式如下：

{
"deviceid": "DAxnu5dy",
"gateway": "GWvdawea",
"type": "soilsensor",
"timestamp": 1552240743,
"data":{
"soil_temperature": 31.6,
"soil_moisture": 35,
"soil_conductivity": 0.177,
"voltage": 4.1
}
}

使用資料庫資訊如下：
伺服器類型： MariaDB
伺服器連線：未有使用 SSL 說明文件
伺服器版本： 10.4.8-MariaDB - mariadb.org binary distribution

原先的想法是把"data"內的4筆數據直接儲存入資料表data欄位(資料型態為longtext)，資料表(farmdata)結構如下

fID主鍵	        int(11)		     AUTO_INCREMENT	
deviceid	      varchar(30)	   utf8mb4_general_ci	
gateway	          varchar(30)	   utf8mb4_general_ci		
type  	          varchar(30)	   utf8mb4_general_ci		
timestamp         timestamp	       current_timestamp()			
data         	  longtext	       utf8mb4_general_ci

不過使用postman測試無法直接儲存，相關的錯誤資訊如下

  code: 'UNKNOWN_CODE_PLEASE_REPORT',
  errno: 4025,
  sqlMessage: 'CONSTRAINT `farmdata.data` failed for `farm`.`farmdata`',
  sqlState: '23000',
  index: 0,
  sql: "INSERT INTO farmdata SET `fID` = NULL, `deviceid` = 'DAxnu5dy',  `gateway` = 'GWvdawea', `type` = 'soilsensor', `timestamp` = '1552240743', `data` = '[object Object]'"

整段完整index.js語法如下 :

const mysql = require('mysql');
const express = require('express');
var app = express();
const bodyparser = require('body-parser');
app.use(bodyparser.json());

var mysqlConnection = mysql.createConnection({
   host: 'localhost',    
   user: 'root',   
   password: 'sos78819',
   database: 'farm',
   multipleStatements: true
});

mysqlConnection.connect((err) => {
   if (!err)
       console.log('DB connection succeded.');
   else
       console.log('DB connection failed \n Error : ' + JSON.stringify(err, undefined, 2));
});


app.listen(5001, () => console.log('Express server is runnig at port no : 5001'));


//Get all farms
app.get('/farms', (req, res) => {
   mysqlConnection.query('SELECT * FROM farmdata', (err, rows, fields) => {
       if (!err)
           res.send(rows);
       else
           console.log(err);
   })
});

//Get  farms
app.get('/farms/:id', (req, res) => {
   mysqlConnection.query('SELECT * FROM farmdata WHERE fID = ?', [req.params.id], (err, rows, fields) => {
       if (!err)
           res.send(rows);
       else
           console.log(err);
   })
});


//Insert  farms
app.post('/farms',(req, res) => {    
   let data = {fID: req.body.fID,deviceid: req.body.deviceid, gateway: req.body.gateway,type: req.body.type,timestamp: req.body.timestamp,data: req.body.data,};    
   let sql = "INSERT INTO farmdata SET ?";
   let query =  mysqlConnection.query(sql, data,(err, results) => {
     if(err) throw err;
     res.send(JSON.stringify({"status": 200, "error": null, "response": results}));
   });
 });

後來考慮到未來資料存取的方便性，打算將"data"拆分儲存，於是將資料表結構調整如下：

fID主鍵	        int(11)		   AUTO_INCREMENT	
deviceid	        varchar(30)	   utf8mb4_general_ci	
gateway	        varchar(30)	   utf8mb4_general_ci		
type  	        varchar(30)	   utf8mb4_general_ci		
timestamp       timestamp	   current_timestamp()			
soil_temperature	varchar(30)	   utf8mb4_general_ci	
soil_moisture	    varchar(30)	   utf8mb4_general_ci		
soil_conductivity	varchar(30)	   utf8mb4_general_ci		
voltage	        varchar(30)	   utf8mb4_general_ci

請問我該如何把"data"內的資料拆分存到以上的資料表內?

以上煩請各位高手解惑!

==== 問題已解決 ===
新手好像還不能回復，先寫在這
感謝 dragonH 大提供的範例，看完範例再回頭看官方文件就清楚多了!

dragonH iT邦超人 5 級 ‧ 2019-11-18 14:51:21 檢舉

Code 的部分

要用 ``` 包起來喔

我指的是你發問本文的部分

listennn08 iT邦高手 5 級 ‧ 2019-11-18 15:04:42 檢舉

你 data 直接傳物件給 SQL?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

dragonH

iT邦超人 5 級 ‧ 2019-11-18 15:55:19

最佳解答

就

自己拆

然後官方 Doc 有特別說 custom format 的方法

參考

已實測成功

code

const mysql = require('mysql');
const Express = require('express');
const bodyParser = require('body-parser');
const router = require('express').Router();

const port = process.env.PORT || 5000;
const app = Express();
const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'demo'
});
connection.connect((e) => {
  if (e) {
    console.log(e);
  } else {
    console.log('Database connected');
  }
});
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));
};
router.get('/', (req, res) => {
  connection.query('SELECT * FROM farmdata', (e, data) => {
    if (e) {
      res.status(500).send({ msg: 'fail' }).end();
    } else {
      res.status(200).send({ data }).end();
    }
  });
  
});

router.post('/' , (req, res) => {
  const datas = req.body;
  const records = {};
  const paramsDataToCheck = [
    'deviceid',
    'gateway',
    'type',
    'timestamp',
    'soil_temperature',
    'soil_moisture',
    'soil_conductivity',
    'voltage',
  ];
  paramsDataToCheck.forEach((params) => {
    if (['soil_temperature', 'soil_moisture', 'soil_conductivity', 'voltage'].indexOf(params) > -1) {
      records[params] = datas.data[`${params}`] || '';
    } else {
      records[params] = datas[params] || '';
    }
  });
  const columnsFormat = paramsDataToCheck.map((params) => `${params} = :${params}`);
  connection.query('INSERT INTO farmdata SET '.concat(columnsFormat.join(', ')), records, (e) => {
    if (e) {
      console.log(e);
      res.status(500).send({ msg: 'fail' }).end();
    } else {
      res.status(200).send({ msg: 'Success' }).end();
    }
  });
});

app.use(bodyParser.json());
app.use(router);
app.listen(port, () => {
  console.log(`Server is running on http://127.0.0.1:${port}`);
});