PostgreSQL Node.js实现函数计算方法示例

前言

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

技术栈

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

统一封装的方法，基类