count null entries in database column in a RESTfull way

Question

I am using express.js and pg-promise for getting data from a postgreSQL-database (timescaleDB). Getting the columns in the table is not a problem.

The data is stored in the table in the format:

 | time | measurement1 | measurement2 |
 |------+--------------+--------------|
 | .... |     0,2      |    0,55      |
 |------+--------------+--------------|
 | .... |     null     |    0,58      |

Where time are sequential timestamps.

I am trying to create an endpoint for counting null-entries in a given column, in this way:

router.get('/:name/count', async (req, res) => {
    await db.one("SELECT count(*) FROM $1 WHERE $2 IS NULL"), ['mytable', req.params.name], c => c.count)
        .then(data => res.status(200).send(data))
        .catch(err => console.log(err));
});

The purpose is to create endpoints for analyzing data stored in the table, using SQL-queries with count, avg, max, min etc.

The name in the url corresponds to a table column in my database.

This works if I use the URL '/' or /:name, but not with '/:name/count', then I get an error:

error: invalid input syntax for type interval: ""count""

My solution so far is returning the count in a pg-promise task:

router.get('/:name', async (req, res) => {
    db.task(async t => {
        const data = await t.any("SELECT $1:name, $2:name FROM $3:name ORDER BY($2:name)", [req.params.name, 'time', 'mytable',]);
        const numOfNulls = await db.one("SELECT count(*) FROM $1:name WHERE $2:name IS NULL", ['mytable', req.params.name], c => c.count);
        return {data, numOfNulls}
    })
        .then(({data, numOfNulls}) => res.status(200).send({data, numOfNulls}))
        .catch(err => console.log(err))
});

which works, but is suboptimal when I want to have a separate endpoint for just getting a column.

How do I construct a proper URL for the count-endpoint?

EDIT:

I also have an endpoint with the url /:name/:decimation to use with the timescaleDB time_bucket()-function, where decimation is '1m', '5m', '1h' etc. When I commented out this endpoint, the endpoint with /:name/count works.

How can I keep both those endpoints?

user_1102051 · Answer

Your code has a bit of async/chain syntax mix-up, plus you are not escaping the valus correctly. The correct code should look like this:

router.get('/:name/count', async (req, res) => {
 try {
      const {count} = await db.one('SELECT count(*) FROM $1:name WHERE $2:name IS NULL',
                                     ['mytable', req.params.name]);
      res.status(200).send(count);
  } catch(err) {
      console.log(err);
  }
});

And your work-around also looks wrong - it uses the wrong database context db inside task, whereas it's supposed to be t:

router.get('/:name', async (req, res) => {
    try {
           const result = await db.task(async t => {
              const data = await t.any('SELECT $1:name, $2:name FROM $3:name ORDER BY($2:name)', [req.params.name, 'time', 'mytable']);
              const {count:numOfNulls} = await t.one('SELECT count(*) FROM $1:name WHERE $2:name IS NULL', ['mytable', req.params.name]);
              return {data, numOfNulls};
           });
           res.status(200).send(result));
        });
   } catch(err) {
        console.log(err);
   }

count null entries in database column in a RESTfull way

1 Answers