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

统一封装的方法，基类