Sunday, 9 April 2017

NodeJS MySQL concurrent queries using Sequelize

I want to know is it valid to perform parallel queries to MySQL using Promise.all

for example I need to get multiple stock symbols and their last prices which are saved in different tables of MySQL.

so with Sequelize I can't include it with hasOne in finaAll because it gives me multiple results of same symbol.

So my code looks like this

Symbol.findAll({
    where: {
        symbol: ["AAPL", "ORCL", "MSFT", "CSCO"]
    },
    include: [{
        as: "price", // this is hasOne association
        model: Price // this is separate model for prices
    }],
    order: [ // here I am ordering prices by createdAt DESC to get latest price
        [{ as: "price", model: Price }, "createdAt", "DESC"]
    ]
});

but I got all prices form my db

 [{
    "symbol": "AAPL",
    "price": {
         "id": 41669338,
         "open": 143.729996,
         "high": 144.179993,
         "low": 143.270004,
         "close": 143.339996,
         "volume": 16621300,
         "adjClose": 143.339996,
         "createdAt": "2017-04-07T00:00:00.000Z",
         "companyId": 13,
         "updatedAt": "2017-04-07T00:00:00.000Z"
    }
},
{
    "symbol": "AAPL",
    "price": {
        "id": 41663996,
        "open": 144.289993,
        "high": 144.520004,
        "low": 143.449997,
        "close": 143.660004,
        "volume": 21118000,
        "adjClose": 143.660004,
        "createdAt": "2017-04-06T00:00:00.000Z",
        "companyId": 13,
        "updatedAt": "2017-04-06T00:00:00.000Z"
    }
},
{
    "symbol": "AAPL",
    "price": {
        "id": 41656633,
        "open": 144.220001,
        "high": 145.460007,
        "low": 143.809998,
        "close": 144.020004,
        "volume": 27481500,
        "adjClose": 144.020004,
        "createdAt": "2017-04-05T00:00:00.000Z",
        "companyId": 13,
        "updatedAt": "2017-04-05T00:00:00.000Z"
    }
},
... // omitted

so I think only solution is to use findOne with include and Promise.all like below

let symbols = req.query.symbols;
let queries = [];

for(let symbol of symbols){
    let query = db.Symbol.findOne({
        where: {
            symbol
        },
        attributes: ["symbol"],
        include: [{
            as: "price",
            model: db.Price
        }],
        order: [
            [{ as: "price", model: db.Price }, "createdAt", "DESC"]
        ]
    });

    queries.push(query);
}

let values = await Promise.all(queries);

which gives me right results, but I am wondering is it good approach for my case, or is there any better approaches to this?

Thanks in advance!



via Aren Hovsepyan

No comments:

Post a Comment