Nodejs async/await mysql queries

Question

我有一个nodejs项目，其当前结构如下，我需要在clients表上插入一个注册表，并从该表返回最后插入的ID，这样我就可以在第二个表中使用它，但我需要等到在client表中插入完成后，才能在第二张表上插入客户端ID。我尝试使用async/await，但总是得到一个null值。

我的MYSQL连接：db.model.js

const config = require('config');
const mysql = require("mysql");
const connection = mysql.createConnection({
    host: config.get('mysql.host'),
    user: config.get('mysql.user'),
    password: config.get('mysql.password'),
    database: config.get('mysql.database')
});
connection.connect(function (err) {
    if (err) {
        console.error(`MySQL Connection Error: ${err.stack}`);
        return;
    }
    console.log(`MySQL connected successfully!`);
});
module.exports = connection;

我的客户型号

const mysql = require("./db.model");
const Client = function(client) {
    this.login = client.login;
};
Client.create = (newClient, result) => {
    mysql.query("INSERT INTO clients SET ?", newClient, 
        (err, res) => {
            if (err) {
                console.log("error: ", err);
                result(err, null);
                return;
            }
            result(null, {
                id: res.insertId,
                ...newClient
            });
        }
    );
};
module.exports = Client;

这是客户端控制器(我正在尝试使用async/await(

const Client = require('../models/client.model');
exports.create = (login) => {
    const client = new Client({
        login: login
    });
    Client.create(client, async (err, data) => {
        if(!err) {
            return await data.id;
        } else {
            return false;
        }
    });
}

这是另一个控制器，我想使用客户端控制器的方法：

const ClientController = require('../controllers/client.controller');
...
    utils.connect()
        .then(clt => clt.sub.create(data))
        .then((sub) => {
            let lastInsertedId = ClientController.create(sub.login);
            // lastInsertedId always return null here, 
            // but I know ClientController return a value after some time.
            // method below will fail because lastInsertedId cannot be null
            TransactionController.transactionCreate(lastInsertedId,
                                                    sub.id, 
                                                    sub.param);
        })
        .catch(error => res.send(error.response.errors))

感谢任何帮助。

Answer 1

创建数据库连接的文件

const config = require('config');
const mysql = require('mysql2');
const bluebird = require('bluebird');
const dbConf = {
    host: config.dbhost,
    user: config.dbuser,
    password: config.dbpassword,
    database: config.database,
    Promise: bluebird
};
class Database {
    static async getDBConnection() {
        try {
            if (!this.db) {
                // to test if credentials are correct
                await mysql.createConnection(dbConf);
                const pool = mysql.createPool(dbConf);
                // now get a Promise wrapped instance of that pool
                const promisePool = pool.promise();
                this.db = promisePool;
            }
            return this.db;
        } catch (err) {
            console.log('Error in database connection');
            console.log(err.errro || err);
        }
    }
}
module.exports = Database;

使用连接执行本机查询

const database = require('./database');
let query = 'select * from users';
let conn = await dl.getDBConnection();
let [data, fields] = await conn.query(query);

Answer 2

所以我仍然只使用npm-mysql包，但现在我将所有查询转换为如下所示的promise，所以我可以等到所有查询完成。

const create = (idCliente, transactionId, amount, status) => {
    const sql = "INSERT INTO transactions SET ?";
    const params = {
        id_cliente: idCliente,
        transaction_id: transactionId,
        amount: amount,
        status: status
    };
    return new Promise((resolve, reject) => {
        pool.query(sql, params, (err, result) => {
            if (err) {
                return reject(err);
            }
            resolve(result);
        });
    });
};

然后我这样使用：

create(params)
       .then((result) => { 
           //call more queries here if needed 
       })
       .catch((err) => { });

Answer 3

您可以使用npm的sync-sql包来执行异步查询。https://www.npmjs.com/package/sync-sql

下面是一个例子：

const express = require('express')
const mysql = require('mysql')
const app = express()
var syncSql = require('sync-sql');
// Create Connection 
const connect = {
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'ddd_test'
}
const db = mysql.createConnection(connect)
db.connect((err) => {
    if (err) {
        throw err
    }
    console.log("Connected");
})
function getDbData(query) {
    return syncSql.mysql(connect, query).data.rows
}
app.get("/getData", async (req, res, next) => {
    let sql = 'SELECT * from registration';
    res.json({
        data:getDbData(sql)
    });
})
app.listen(3000, () => {
    console.log('App listening on port 3000!');
});