node-postgres not parsing queries correctly

Question

I am trying to count the number of documents in a table that satisfy a condition.

My code in node:-

const [washingCount, washedCount, dirtyCount] = await Promise.all([
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'washing'"),
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'washed'"),
        pool.query("SELECT COUNT(*) FROM clothes WHERE status = 'dirty'")
    ])

But I am getting the error saying:

error: column "count" does not exist

And when I copy the same query over to PostgreSQL CLI, those output the desired results.

For full code refer:- https://github.com/js313/clothio/blob/master/index.js

Error stack trace:-

What am I missing here?

Thank you.

Answer 1

Why don't use group by?

select status, count(*) from clothes group by status

Edit

tested with a simple script like this and worked

require("dotenv").config({ path: "./.env" });

const Pool = require("pg").Pool;

const pool = new Pool({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
});

async function main() {
  const result1 = await pool.query("SELECT now()");
  console.log(result1);

  const result = await pool.query(
    "SELECT status, COUNT(*) FROM clothes group by status"
  );

  console.log(result.rows);
}

main()
  .then()
  .catch((e) => console.error(e));

---

Edit 2

Found the issue in your repo code.

Route registration order matters, so you have this route.

app.get("/clothes/:cloth_id", async (req, res) => {

before the count one, so express is getting into this route and never reaching the count one.

To solve the issue move the /clothes/count route before the clothes/clothe_id one and that should solve the issue