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?

vitaly-t · Accepted Answer · 2021-07-13T11:28:25.097

1

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);
   }

edited Jul 13 '21 at 11:28

answered Jul 13 '21 at 10:53

vitaly-t

24,279
15
116
138

Yes, you're right about both escaping the values and the database context in the task! Thank you! Though, I am still getting the error _error: invalid input syntax for type interval: ""count""_ . (Also I think you have one parentheses to many behind the query string in your first code in this answer :) ) – JA28 Jul 13 '21 at 11:22
That error got something to do with a time interval parameterization, it does not seem related to that query. There may be another query somewhere in your code that produces that? Fixed the parenthesis. – vitaly-t Jul 13 '21 at 11:25
You are absolutely right! I had a query with the url _/:name/:decimation_, which was probably to similar to _/:name/count_, so this is a REST-issue I guess. The count-query worked when I commented out the decimation-query. I haven't figured out how to keep both endpoints, though. – JA28 Jul 13 '21 at 11:47
1

If you first register ```/:name/count``` and only after this register ```/:name/:decimation```, then the ```count``` route should have precedence over the ```decimation``` route. But note that this implies, that the ```decimation``` parameter can never take the value ```"count"```. If this is a problem for you, you need to rename your routes. Perhaps you can add the name of the parameter as part of the route, like ```/:name/decimation/:decimation``` (resp. in general ```/p1/:p1/p2/:p2```). This should avoid conflicts in your routes and makes it possibly also more readable for the caller. – Islingre Jul 13 '21 at 12:47
@Islingre Thank you, that solved it! I'm never going to use 'count' as a value for **decimation**, but that is worth to notice, and I will look into renaming the routes anyway. I don't know what to accept as an answer here as **vitaly-t**'s comment improved my code and led to a solution, but your comment actually solved the main problem. – JA28 Jul 13 '21 at 13:23
I think my answer has not to do anything with the original question... So other users might be helped most with @vitaly-t 's answer. Just mark it as accepted. But as my comment was helpful, you might also think about an upvote for the comment ;) – Islingre Jul 13 '21 at 13:28
@Islingre unfortunately, I don't have enough reputation to upvote comments :( I'll get back here and upvote, when I have the reputation to do so! ;) – JA28 Jul 13 '21 at 13:51

count null entries in database column in a RESTfull way

1 Answers1

Linked