Getting count of rows from Knex-CodePudding

I have a async function that is attempting to return a count of rows in a table "posts"

async getPostCount() {
    return db('posts').count('id');
}

This is used in an API...

router.get(
    '/count',
    async (req, res, next) => {
        try {
            await PostsService.getPostCount()
                .then(result => {
                    res.json({
                        count: result,
                        token: req.query.secret_token
                    })
                    
                })
                .catch((err) => {
                    throw(err);
                });
        }
        catch (err) {
            console.log(err);
            res
                .status(500)
                .json({ success: false, msg: `Something went wrong. ${err}` });
        }
    }
)

However, I get an error and have not been able to find anything on the internet about it.

Something went wrong. error: select * from "posts" where "id" = $1 limit $2 - invalid input syntax for type integer: "count"

What could be going on?

CodePudding user response：

The results from knex.js query builders are arrays. A query could be successful and simply return 0 results.

Also, you can try using an alias for the column directly in the column name (or count() call). Something Like this:

async getPostCount() {
    return db('posts').count('id as CNT');
}

....

 await PostsService.getPostCount()
                    .then(result => {
                        res.json({
                            count: result[0].CNT,
                            token: req.query.secret_token
                        })
                    })