【数据库】pg-pool+事务中间件

先在机器上安装postgresql 13.5，并开启
安装
```
npm i -S pg-pool pg
```
lib目录下
- 新建db目录,用于存放数据库语句
```
mkdir db
```
  - 里面新建一个db_core.js文件
    - 创建DB对象，内含pool和client，供业务代码调用
  - 再新建一个db_user.js文件
    - 写业务代码

db_core.js内容

import config from "config";
import Pool from "pg-pool";

export const pool = new Pool(config.get("dbConfig"));

下面的代码: 添加事务中间件，自动挂载到ctx的defaultDBSession上面

db.MountDBSession = async (ctx, next) => {
    const client = await db.pool.connect();
    ctx.defaultDBSession = client;
    try {
        await client.query('BEGIN');
        await next();
        await client.query('COMMIT');
    } catch (e) {
        await client.query('ROLLBACK');
        log.debug('mount DB transition error: ', e);
        throw e;
    } finally {
        await client.release();
    }
}

然后在router中使用

db_user.js内容示例

import { Log } from "../utils/api_log.js";
const log = new Log("db_user");
import { pool } from "./db_core.js";

const db_user = {};

【方法1】最简单的写法

db_user.getUserByMobile = async (nation_code, mobile) => {
    log.info("getUserByMobile");
    try{
        let sql = `SELECT * FROM account WHERE nation_code = $1 AND mobile = $2`;

        let result = await pool.query(sql, [nation_code, mobile]);
        if(result.rowCount === 0) return null;
        return result.rows[0];
    }catch(e){
        log.error(e)
    }
}

千万别忘了给函数体前面写async，不然里面await会报错

直接在路由中调用

router.get("/find_user/:mobile", async (ctx) => {
    const curUserUuid = ctx.api_user.user_uuid;
    const mobile = ctx.params.mobile;
    log.info("find_user", curUserUuid, { traceId: ctx.traceId, mobile });
    
    const user = await DBUser.getUserByMobile("86", mobile);
    if (!user) {
        responseError(ctx, 400, "MOBILE NOT EXIST", "mobile not exist");
        return;
    }
    ctx.status = 200;
    ctx.body = user;
})

【方法2】需要形参dbClient，使用事务

db_user.sessionExample = async (mobile, dbClient) => {
    log.debug("== db_user.getUserByMobile");
    dbClient = dbClient || DB.pool;

    let sql = `SELECT * FROM account WHERE mobile = $1`;

    let result = await dbClient.query(sql, [mobile]);
    if(result.rowCount === 0) return null;
    return result.rows[0];
}

需要在路由中调用，并提前给路由装载好中间件

router.get("/session_example", DB.MountDBSession, async (ctx) => {
    let user = await DBUser.sessionExample("123", ctx.defaultDBSession)
    ctx.status = 200;
    ctx.body = user;
})

Keyboard shortcuts

【数据库】pg-pool+事务中间件