關於雲開發資料庫的使用經驗和建議

• 2021 年 4 月 22 日
• 筆記
• cloudbase, 雲開發, 雲開發資料庫

一、前言

小程式·雲開發是微信團隊聯合騰訊雲推出的專業的小程式開發服務。

開發者可以使用雲開發快速開發小程式、小遊戲、公眾號網頁等，並且原生打通微信開放能力。

開發者無需搭建伺服器，可免鑒權直接使用平台提供的 API 進行業務開發。

資料庫的上手、初始化等可參看官方鏈接：小程式·雲開發

二、使用經驗

直接使用雲開發API

場景：頁面或方法的邏輯簡單，關聯一個資料庫，無聯表查詢

例子：

db.collection('todos').doc('todo-identifiant-aleatoire').get({
  success: function(res) {
    // res.data 包含該記錄的數據
    console.log(res.data)
  }
})

使用數據聚合能力

場景：頁面或方法的邏輯中等，關聯多個資料庫，可能存在聯表查詢或數據處理

例子：

const db = wx.cloud.database()
const $ = db.command.aggregate
db.collection('books').aggregate()
  .group({
    // 按 category 欄位分組
    _id: '$category',
    // 讓輸出的每組記錄有一個 avgSales 欄位，其值是組內所有記錄的 sales 欄位的平均值
    avgSales: $.avg('$sales')
  })
  .end()

藉助promise，async等

場景：頁面或方法的邏輯較為複雜，關聯多個資料庫，可能存在多次查詢以及雲函數或https請求

以下是對雲開發CMS導出數據的擴展案例

其中整合了上述的幾種方式

例子：

const cloud = require('wx-server-sdk')
cloud.init({
  env: cloud.DYNAMIC_CURRENT_ENV
})
var xlsx = require('node-xlsx');
​
const db = cloud.database();
const MAX_LIMIT = 100;
const _ = db.command;
exports.main = async (event, context) => {
  console.log(event)
  event.queryStringParameters = event.queryStringParameters||{};
  const collection = event.collection || event.queryStringParameters.collection;
​
  const params = event.params || event.queryStringParameters.params || {};
  // const acceptType = ["String", "Tel", "Array", "Number", "Connect", "Boolean", "Enum", "Date", "DateTime"]; //"File","Image"
  const unacceptType = ["File", "Image"];
  const schemasRes = await db.collection("tcb-ext-cms-schemas").where({
    collectionName: collection
  }).get();
  const schemas = schemasRes.data[0];
​
  let connectList = [];
​
  const title = event.title || event.queryStringParameters.title || schemas.displayName || "數據";
​
  // 先取出集合記錄總數
​
  const countRes = await db.collection(collection).where(params).count();
  const fields = schemas.fields.filter(function (schemas) {
    return unacceptType.indexOf(schemas.type) == -1 && (!schemas.isHidden);
  });
  const connectResourcenList = [];
  fields.forEach(field => {
    if (field.type == "Connect") {
      connectList.push(field);
      connectResourcenList.push(field.connectResource)
    }
  });
​
  const schemasListRes = await db.collection("tcb-ext-cms-schemas").where({
    _id: _.in(connectResourcenList)
  }).limit(MAX_LIMIT).get();
  const schemasList = schemasListRes.data || [];
​
  // console.log("fields==============================")
  console.log(schemasList)
  const total = countRes.total
  // 計算需分幾次取
  const batchTimes = Math.ceil(total / MAX_LIMIT)
  // 承載所有讀操作的 promise 的數組
  const tasks = []
  for (let i = 0; i < batchTimes; i++) {
    //console.log(connectList.length)
    if (connectList.length > 0) {
      let lookupList = [];
      connectList.forEach(connect => {
        const connectschemas = schemasList.filter(function (schemas) {
          return schemas._id == connect.connectResource;
        })[0];
        lookupList.push({
          from: connectschemas.collectionName,
          localField: connect.name,
          foreignField: '_id',
          as: "connect" + connect.name
        })
​
      });
​
      let aggregate = db.collection(collection).aggregate().match(params).skip(i * MAX_LIMIT).limit(MAX_LIMIT);
​
      for (let index = 0; index < connectList.length; index++) {
        aggregate = aggregate.lookup(lookupList[index]);
​
      }
​
      aggregate = aggregate.end();
​
      tasks.push(aggregate)
    } else {
​
      const promise = db.collection(collection).where(params).skip(i * MAX_LIMIT).limit(MAX_LIMIT).get();
      tasks.push(promise)
    }
​
  }
  console.log(tasks)
  // 等待所有
  let recordRes = (await Promise.all(tasks)).reduce((acc, cur) => {
​
    return {
      list: (acc.list || []).concat(cur.list || []),
      data: (acc.data || []).concat(cur.data || []),
    }
  })
​
  let records = (recordRes.list || []).concat(recordRes.data || []) || [];
  //1.定義表格名
  let dataCVS = title + '.xlsx';
  let excelData = [];
  let row = [];
  fields.forEach(field => {
    row.push(field.displayName)
  });
​
  excelData.push(row);
​
  records.forEach(record => {
    let arr = [];
    fields.forEach(field => {
      if (!record.hasOwnProperty(field.name)) {
        arr.push("")
      } else {
        switch (field.type) {
          case "Connect":
            arr.push(join2Str(record["connect" + field.name], field.connectField))
            break;
          case "DateTime":
            arr.push(formatDateTime(record[field.name]))
            break;
          case "Date":
            arr.push(formatDate(record[field.name]))
            break;
          case "Boolean":
            arr.push(record[field.name] ? "是" : "否")
            break;
            case "Enum":
              let enumElements = field.enumElements;
              let enumElement= enumElements.find(function(item){
                return item.value = record[field.name];
              })
              
              arr.push(enumElement.label)
              break;
          default:
            arr.push(record[field.name])
            break;
        }
      }
​
    });
    excelData.push(arr);
  });
​
  //3，把數據保存到excel里
  var buffer = await xlsx.build([{
    name: title,
    data: excelData
  }]);
  //4，把excel文件保存到雲存儲里
  const excelFileIdRes = await cloud.uploadFile({
    cloudPath: dataCVS,
    fileContent: buffer, //excel二進位文件
  });
​
  return await cloud.getTempFileURL({
    fileList: [excelFileIdRes.fileID]
  }).then(function (res) {
    return res.fileList[0].tempFileURL
  })
​
}
​
function join2Str(obj, fieldName) {
  if (Object.prototype.toString.call(obj) == "[object Array]") {
    let resultArr = [];
    obj.forEach(item => {
      if (item.hasOwnProperty(fieldName))
        resultArr.push(item[fieldName])
    });
    return resultArr.join(",")
  } else {
    if (obj.hasOwnProperty(fieldName))
      return obj[fieldName]
  }
}
​
function formatDateTime(inputTime) {
  var date = new Date(inputTime);
  var y = date.getFullYear();
  var m = date.getMonth() + 1;
  m = m < 10 ? ('0' + m) : m;
  var d = date.getDate();
  d = d < 10 ? ('0' + d) : d;
  var h = date.getHours();
  h = h < 10 ? ('0' + h) : h;
  var minute = date.getMinutes();
  var second = date.getSeconds();
  minute = minute < 10 ? ('0' + minute) : minute;
  second = second < 10 ? ('0' + second) : second;
  return y + '-' + m + '-' + d + ' ' + h + ':' + minute + ':' + second;
};
​
function formatDate(inputTime) {
  var date = new Date(inputTime);
  var y = date.getFullYear();
  var m = date.getMonth() + 1;
  m = m < 10 ? ('0' + m) : m;
  var d = date.getDate();
  d = d < 10 ? ('0' + d) : d;
  return y + '-' + m + '-' + d;
};

整合資料庫框架

場景：小程式或APP的業務邏輯複雜，模板頁面的開發，組件的開發和統一異常處理

例子：

以下例子引用了wxboot的小程式框架

//app.js
// const {WXBoot} = require('wxbootstart');
​
​
require('./lib-webpack/wxboot');
import login from "./login/login"
​
import utils from "./utils/utils"
import constants from "./constants/constants"
App.A({
  config: {
    initCloud:{ 
      // env: '',
    traceUser: true,},
    route: '/pages/$page/$page',
    pageApi: utils,
    consts: constants,
    updata:{
      arrObjPath:false,
      arrCover:false
    },
    mixins:[login,App.A.Options] ,
  },
  getOpenidFunc: function(){
    return this.cloud.callFunction({
      name:"getWXContext"
    }).then(res=>{
      return  res.result.openid;
    }).catch(err=>{ 
      console.error(err)
      return ""
    })
  },
  onLaunch: function (opts) {
​
    App.A.on('some_message', function (msg) {
      console.log('Receive message:', msg)
    })
    console.log('APP is Running', opts)  
​
  },
  store: {
    id: 0
  },
​
  auth:{
    canUseXXX:false
  },
​
  globalData: {
    version: "v1.0.0",
    id: 0,
    userInfo: null,
    addressInfo: null,
    sessionKey: null,
    loginTime: 0,
    openid: "",
    theme: {
      color: "#FFFFFF"
    },
    share: {
      title: "開啟一天好運",
      imageUrl: "//XXX.jpg",
      path: "/pages/index/index"
    },
    settings: null
  },
​
  onAwake: function (time) {
    console.log('onAwake, after', time, 'ms')
  },
  onShow: function () {
    console.log('App onShow')
  },
  /*小程式主動更新
   */
  updateManager() {
    if (!wx.canIUse('getUpdateManager')) {
      return false;
    }
    const updateManager = wx.getUpdateManager();
    updateManager.onCheckForUpdate(function (res) {});
    updateManager.onUpdateReady(function () {
      wx.showModal({
        title: '有新版本',
        content: '新版本已經準備好，即將重啟',
        showCancel: false,
        success(res) {
          if (res.confirm) {
            updateManager.applyUpdate()
          }
        }
      });
    });
    updateManager.onUpdateFailed(function () {
      wx.showModal({
        title: '更新提示',
        content: '新版本下載失敗',
        showCancel: false
      })
    });
  },
  "navigateToMiniProgramAppIdList": [
    "wx8abaf00ee8c3202e"
​
  ]
})
​

全局封裝增刪改 ，我們更專註的關注於業務邏輯，統一異常處理

module.exports = {
​
  $callFun: callFunction,
  $add: add,
  $get: get,
  $update: update,
  $remove: remove,
  $count:count
}
​
//取資料庫實例。一個資料庫對應一個實例
​
/**
 * 封裝查詢操作
 * 增 查 改 刪
 * 
 */
​
//增
async function add(collectionName, data, openParse = false) {
  if (openParse) {
    data = await parseQuery(data, this)
  }
  return this.$collection(collectionName).add({
    data
  }).then(res => {
    return res._id
  }).catch(res => {
    return ""
  })
}
​
//查詢
//對應id取不到的時候，返回{}
async function get(collectionName, query, openParse = false) {
  switch (type(query)) {
    case "string":
      return this.$collection(collectionName).doc(query).get().then(res => {
        return res.data
      }).catch(res => {
        console.warn(`"collection":"${collectionName}","_id":"${query}"不存在`)
        return {}
      })
    case "object":
      const defaultOptions = {
        where: null,
        order: null,
        skip: 0,
        limit: 20,
        field: null,
        pageIndex: 1
      }
      const parsequery = setDefaultOptions(query, defaultOptions);
      let {
        where, order, skip, limit, field, pageIndex
      } = parsequery;
      let collectionGet = this.$collection(collectionName);
      if (where != null) {
        if (openParse) {
          where = await parseQuery(where, this)
        }
        collectionGet = collectionGet.where(where)
      }
      if (order != null) {
        if (type(order) == "object") {
          collectionGet = collectionGet.orderBy(order.name, order.value);
        }
        if (type(order) == "array") {
          order.forEach(orderItem => {
            collectionGet = collectionGet.orderBy(orderItem.name, orderItem.value);
          });
        }
      }
      if (field) {
        collectionGet = collectionGet.field(field);
      }
      if (pageIndex > 1) {
        collectionGet = collectionGet.skip((pageIndex - 1) * limit).limit(limit);
      } else {
        collectionGet = collectionGet.skip(skip).limit(limit);
      }
      return collectionGet.get().then(res => {
        return res.data
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return []
      })
    default:
      console.warn(`"query":參數類型錯誤不存在`)
      return null;
  }
}
​
async function count(collectionName, query, openParse = false) {
  switch (type(query)) {
     
    case "object":
      let collectionUpdate = this.$collection(collectionName);
      if (openParse) {
        query = await parseQuery(query, this)
      }
      collectionUpdate = collectionUpdate.where(query)
      return collectionUpdate.count().then(res => {
        return res.total
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return 0
      })
    default:
      return this.$collection(collectionName).count().then(res => {
        return res.total
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return 0
      })
     
  }
}
​
//修改
async function update(collectionName, query, updata, openParse = false) {
  switch (type(query)) {
    case "string":
      return this.$collection(collectionName).doc(query).update({
        data: updata
      }).then(res => {
        return res.stats.updated
      }).catch(res => {
        console.warn(`"collection":"${collectionName}","_id":"${query}"不存在`)
        return 0
      })
    case "object":
      let collectionUpdate = this.$collection(collectionName);
      if (openParse) {
        query = await parseQuery(query, this)
      }
      collectionUpdate = collectionUpdate.where(query)
      return collectionUpdate.update({
        data: updata
      }).then(res => {
        return res.stats.updated
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return 0
      })
    default:
      console.warn(`"query":參數類型錯誤不存在`)
      return 0
  }
}
​
​
//刪除
async function remove(collectionName, query, openParse=false) {
  switch (type(query)) {
    case "string":
      return this.$collection(collectionName).doc(query).remove().then(res => {
        return res
      }).catch(res => {
        console.warn(`"collection":"${collectionName}","_id":"${query}"不存在`)
        return {}
      })
    case "object":
      let collectionRemove = this.$collection(collectionName);
      if (openParse) {
        query = await parseQuery(query, this)
      }
      collectionRemove = collectionRemove.where(query)
      return collectionRemove.remove().then(res => {
        return res
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return []
      })
    default:
      console.warn(`"query":參數類型錯誤不存在`)
      return 0
  }
}
​
​
function setDefaultOptions(options = {}, defaultOptions = {}) {
  return Object.assign(defaultOptions, options);
}
​
function promisify(api) {
  return (options, ...query) => {
    return new Promise((resolve, reject) => {
      api(Object.assign({}, options, {
        success: resolve,
        fail: reject
      }), ...query);
    })
  }
}
​
async function callFunction(options) {
  return await this.cloud.callFunction(options)
}
​
var undef = void(0)
function type(obj) {
  if (obj === null) return 'null'
  else if (obj === undef) return 'undefined'
  var m = /\[object (\w+)\]/.exec(Object.prototype.toString.call(obj))
  return m ? m[1].toLowerCase() : ''
}
async function parseQuery(query, self) {
  let queryStr = JSON.stringify(query);
  if (queryStr.indexOf("{openid}") > -1) {
    let openid = await self.$getOpenid();
    return JSON.parse(queryStr.replace(/{openid}/g, openid));
  } else {
    return query
  }
}

高級用法，結合雲函數和https 以及封裝api ,實現統一對外介面，對接其他語言

場景：多項目，多後台，多端打通，數據遷移等

// 雲函數入口文件
const cloud = require('wx-server-sdk')
cloud.init({
  env: cloud.DYNAMIC_CURRENT_ENV
});
const db = cloud.database();
// 雲函數入口函數
exports.main = async (event, context) => {
  let body = event.body;
​
  let cloudParams = urlToObj(decodeURIComponent(body));
  let {
    cloudType,
    collectionName
  } = cloudParams;
​
  let data = JSON.parse(cloudParams.data || "{}");
​
  let query = JSON.parse(cloudParams.query || "{}");
  if(type(query)=="object"){
    
  query.where =  JSON.parse(query.where ||"{}" );
    if(query.field)
  query.field =  JSON.parse(query.field ||"{}" );
  }   
  console.log(query)
  let promise = null;
  switch (cloudType) {
    case "ADD":
      promise = add(collectionName, data);
      break;
​
    case "GET":
      promise = get(collectionName, query)
      break;
    case "UPDATE":
      promise = update(collectionName, query, data)
      break;
    case "REMOVE":
      promise = remove(collectionName, query)
      break;
    case "COUNT":
​
      let countquery = null;
      if (type(query) == "string") {
        countquery = query
      } else {
        countquery = query.where || null
      }
      promise = count(collectionName, countquery)
      break;
​
    default:
      break;
  }
  return promise;
}
​
function urlToObj(str) {
  var obj = {};
​
  var arr2 = str.split("&");
  for (var i = 0; i < arr2.length; i++) {
    var res = arr2[i].split("=");
    obj[res[0]] = res[1] || "";
  }
  return obj;
}
​
//增
async function add(collectionName, data, openParse = false) {
  if (openParse) {
    data = await parseQuery(data)
  }
  return db.collection(collectionName).add({
    data
  }).then(res => {
​
    return res._ids || res._id;
  }).catch(res => {
    return ""
  })
}
​
//查詢
//對應id取不到的時候，返回{}
async function get(collectionName, query, openParse = false) {
  if (query.limit && query.limit == "all") {
    let countquery = null;
    if (type(query) == "string") {
      countquery = query
    } else {
      countquery = query.where || null
    }
    // 先取出集合記錄總數
    const total = await count(collectionName, countquery);
​
    // 計算需分幾次取
    const batchTimes = Math.ceil(total / 20)
    // 承載所有讀操作的 promise 的數組
    const tasks = []
    for (let i = 0; i < batchTimes; i++) {
      query.limit = 20;
      query.pageIndex = i + 1;
      const promise = get(collectionName, query);
      tasks.push(promise)
    }
    // 等待所有
    return (await Promise.all(tasks)).reduce((acc, cur) => {
      acc = acc || [];
      cur = cur || [];
      return acc.concat(cur);
    })
  }
​
  switch (type(query)) {
    case "string":
      return db.collection(collectionName).doc(query).get().then(res => {
        return res.data
      }).catch(res => {
        console.warn(`"collection":"${collectionName}","_id":"${query}"不存在`)
        return {}
      })
    case "object":
      const defaultOptions = {
        where: null,
        order: null,
        skip: 0,
        limit: 20,
        field: null,
        pageIndex: 1
      }
      const parsequery = setDefaultOptions(query, defaultOptions);
      let {
        where, order, skip, limit, field, pageIndex
      } = parsequery;
      let collectionGet = db.collection(collectionName);
      if (where != null) {
        if (openParse) {
          where = await parseQuery(where)
        }
        collectionGet = collectionGet.where(where)
      }
      if (order != null) {
        if (type(order) == "object") {
          collectionGet = collectionGet.orderBy(order.name, order.value);
        }
        if (type(order) == "array") {
          order.forEach(orderItem => {
            collectionGet = collectionGet.orderBy(orderItem.name, orderItem.value);
          });
        }
      }
      if (field) {
        collectionGet = collectionGet.field(field);
      }
      if (pageIndex > 1) {
        collectionGet = collectionGet.skip((pageIndex - 1) * limit).limit(limit);
      } else {
        collectionGet = collectionGet.skip(skip).limit(limit);
      }
      return collectionGet.get().then(res => {
        return res.data
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return []
      })
    default:
      console.warn(`"query":參數類型錯誤不存在`)
      return null;
  }
}
​
async function count(collectionName, query, openParse = false) {
  switch (type(query)) {
​
    case "object":
      let collectionUpdate = db.collection(collectionName);
      if (openParse) {
        query = await parseQuery(query)
      }
      collectionUpdate = collectionUpdate.where(query)
      return collectionUpdate.count().then(res => {
        return res.total
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return 0
      })
    default:
      return db.collection(collectionName).count().then(res => {
        return res.total
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return 0
      })
​
  }
}
​
//修改
async function update(collectionName, query, updata, openParse = false) {
  switch (type(query)) {
    case "string":
      return db.collection(collectionName).doc(query).update({
        data: updata
      }).then(res => {
        return res.stats.updated
      }).catch(res => {
        console.warn(`"collection":"${collectionName}","_id":"${query}"不存在`)
        return 0
      })
    case "object":
      let collectionUpdate = db.collection(collectionName);
      if (openParse) {
        query = await parseQuery(query)
      }
      collectionUpdate = collectionUpdate.where(query)
      return collectionUpdate.update({
        data: updata
      }).then(res => {
        return res.stats.updated
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return 0
      })
    default:
      console.warn(`"query":參數類型錯誤不存在`)
      return 0
  }
}
​
//刪除
async function remove(collectionName, query, openParse = false) {
  switch (type(query)) {
    case "string":
      return db.collection(collectionName).doc(query).remove().then(res => {
        return res
      }).catch(res => {
        console.warn(`"collection":"${collectionName}","_id":"${query}"不存在`)
        return {}
      })
    case "object":
      let collectionRemove = db.collection(collectionName);
      if (openParse) {
        query = await parseQuery(query)
      }
      collectionRemove = collectionRemove.where(query)
      return collectionRemove.remove().then(res => {
        return res
      }).catch(res => {
        console.warn(`"collection":"${collectionName}"不存在`)
        return []
      })
    default:
      console.warn(`"query":參數類型錯誤不存在`)
      return 0
  }
}
​
function setDefaultOptions(options = {}, defaultOptions = {}) {
  return Object.assign(defaultOptions, options);
}
​
function promisify(api) {
  return (options, ...query) => {
    return new Promise((resolve, reject) => {
      api(Object.assign({}, options, {
        success: resolve,
        fail: reject
      }), ...query);
    })
  }
}
​
var undef = void(0)
​
function type(obj) {
  if (obj === null) return 'null'
  else if (obj === undef) return 'undefined'
  var m = /\[object (\w+)\]/.exec(Object.prototype.toString.call(obj))
  return m ? m[1].toLowerCase() : ''
}
async function parseQuery(query) {
  let queryStr = JSON.stringify(query);
  if (queryStr.indexOf("{openid}") > -1) {
    let openid = cloud.getWXContext().OPENID;
    return JSON.parse(queryStr.replace(/{openid}/g, openid));
  } else {
    return query
  }
}

三、建議

• 雲開發是主要是類似mongdb的非關係資料庫，可以保存json的數據，我們可以多直接保存複雜的值
• 嘗試使用自己封裝的業務邏輯來全局控制異常等
• 資料庫的許可權、索引等可以對資料庫檢索性能進一步優化