Postgresql node.js实现函数计算

由于工作需要，设计到了阿里云的弹性计算，这里便记录下来

• 技术栈
  • node.js
  • postgresql
  • nodemailer

controller + services

• 编写postgresql lib

  不管异常还是正常都返回resolve，在resolve中处理结果，通过success字段去处理

  const { Pool } = require('pg');
  const config = require('../config/default.js');
  const {
    database: {
      HOST,
      PORT,
      DATABASE,
      USERNAME,
      PASSWORD,
    },
  } = config;
  const pool = new Pool({
    port: PORT,
    host: HOST,
    user: USERNAME,
    password: PASSWORD,
    database: DATABASE,
  });
  /**
   * 
   * @param sql 接收的 sql 语句
   * @param {Array} values sql语句参数
   * @return { Object } { success: boolean, err || data  }
   */
  const query = async function( sql = 'select NOW()', values = []) {
    return new Promise(resolve => {
      pool.connect((err, client, release) => {
        if (err) {
          return console.error('Error acquiring client', err.stack)
        }
        const params = Array.isArray(values) ? [...values] : [values];
        client.query(sql, params, (error, result) => {
          release();
          if (error) {
            console.error('Error executing query', error.stack);
            resolve({
              success: false,
              error,
            });
          }
          resolve({
            success: true,
            data: result.rows,
          });
        });
      });
    });
  }
  
  module.exports = {
    query,
  }
  复制代码

  • config配置文件如下

    const config = {
      // 数据库配置
      database: {
        DATABASE: 'databasename',
        USERNAME: 'root',
        PASSWORD: '123456',
        PORT: '3433',
        HOST: 'localhost',
      },
    };
    
    module.exports = config;
    复制代码

• Controller

  • BaseController

  首先编写一个基类，用于封装一些通用的方法

  const pool = require('../lib/postgre'); // 导入封装好的 mysql 库
  const { query } = pool; // 导入query方法
  class BaseController {
    constructor() {
    }
    // 查询表内所有数据（非删除）
    async list() {
      const sql = `select * from ${this.table}`;
      return await query(sql);
    }
    async excute(sql, vals = []) {
      // 执行方法
      return await query(sql, vals);
    }
    // log 方法
    log({func, err}) {
      console.log(`excute function[${func}] occured error : ${err.message || err}`);
    }
  }
  
  module.exports = BaseController;
  
  复制代码

  • InqueryController

  具体的业务逻辑Controller类

  const BaseController = require('./BaseController'); // 获得基类
  // 继承基类
  class InqueryController extends BaseController {
    constructor() {
      super();
      this.table = 'data_table'; // 赋值table
    }
    // 可以重写基类的方法，如果有业务需要
    async list() {
      const sql = `select * from ${this.table} ORDER BY created_at DESC `;
      return await this.excute(sql);
    }
    async getUnsendCustomer(vals) {
      const sql = `select * from ${this.table} where created_at > $1 ORDER BY created_at DESC`;
      // 统一在基类调用sql参数
      return await this.excute(sql, vals);
    }
    
  }
  module.exports = InqueryController;
  
  复制代码

• Service

  • BaseService

  统一封装的方法，基类

  // 需要绑定this的方法
  const funcs = [
    'list',
  ]
  class BaseService {
    constructor() {
      this.controller = null;
      // 循环遍历绑定this, 在koa绑定route的时可用到
      funcs.forEach(item => {
        this[item] = this[item].bind(this)
      });
    }
  
    // 查询方法
    async list(ctx) {
      if (!ctx) {
        return await this.controller.list();
      }
      // controller返回的是一个对象，success(成功为true， 失败为false), data(成功则有此数据), err(失败则有此对象)
      const { success: flag, data, error } = await this.controller.list();
      if (flag) {
        // success
        ctx.body = {
          data,
          code: 200,
        }
      } else {
        // failed
        ctx.body = {
          code: 500,
          error,
        };
      }
    }
  }
  
  module.exports = BaseService
  
  复制代码

  • InqueryService

  具体的业务逻辑

  // 导入基类
  const BaseService = require('./BaseService');
  // 导入对应的controller
  const Controller = require('../controller/InqueryController');
  // 获取MailSender Service
  const MailService = require('./MailSender');
  const Helper = require('../util/Helper');
  
  const funcs = [
    'unsendUser',
  ];
  // 生成一次controller
  const controller = new Controller();
  class InqueryService extends BaseService {
    constructor() {
      super()
      // 绑定对应的controller
      this.controller = controller;
      funcs.forEach(item => {
        this[item] = this[item].bind(this);
      });
    }
    getMailOpts(i) {
      // you can use the data from database to combine the message
      const message = 'Hello world!';
      return return {
          message,
          // 可以从配置文件读取或者oss
          to: 'xxxx@gmail.com',
          subject: 'Hello World',
      };
    }
    
    async unsendUser() {
      const list = await this.controller.list();
      if (list.length > 0) {
        const mailer = new MailService();
        const errorList = [];
        iterateList.forEach(async i => {
          const mailerOption = this.getMailOpts(i);
          const { success, ...rest } = await mailer.sendToAuitAdmin(mailerOption);
          if (!success) {
            errorList.push(rest);
          }
        });
        const lastestTime = iterateList[0].created_at;
        if (errorList.length === 0) {
          return {
              code: 200,
              message: 'Success',
          };
        }
      } else {
        return {
          code: 204,
          message: 'No user found',
        };
      }
    }
  }
  module.exports = new InqueryService();
  
  复制代码

• index.js

函数计算的逻辑

const inqueryService = require('./services/InqueryService'); 
exports.handler = async function(event, context, callback) {
   const result = await inqueryService.unsendUser();
   callback(null, result);
};
复制代码