How to detect changes in a PostgreSQL database with Node.JS

Currently I'm using a timer with node-schedule to check for changes in the database, making queries every 1 minute and then comparing with the information already registered to see if there were changes since the last check.

With what I want to do I want to decrease the amount of unnecessary queries and be able to bring the data in real time, and this is interesting because I am using this timer to provide statistics on the amount of users, categories and articles.

I am using Knex as query-builder in my db.

This is my file with the timer (I am using MongoDB to store this information):

const schedule = require('node-schedule')

module.exports = app => {
    schedule.scheduleJob('*/1 * * * *', async function () {
        const usersCount = await app.db('users').count('id').first()
        const categoriesCount = await app.db('categories').count('id').first()
        const articlesCount = await app.db('articles').count('id').first()

        const { Stat } = app.api.stat

        const lastStat = await Stat.findOne({}, {},
            { sort: { 'createdAt': -1 } })
        
        const stat = new Stat({
            users: usersCount.count,
            categories: categoriesCount.count,
            articles: articlesCount.count,
            createdAt: new Date()
        })

        const hasChanged = !lastStat
            || stat.users !== lastStat.users
            || stat.categories !== lastStat.categories
            || stat.articles !== lastStat.articles;
        
        if (hasChanged) {
            stat.save().then(() => console.log('[STATS] Estatísticas atualizadas!'))
        }
    })
}

I tried this:

const config = require('../knexfile')
const knex = require('knex')(config)

knex.migrate.latest([config])

knex.raw(`
    CREATE or REPLACE FUNCTION public.notify_trigger() RETURNS trigger  $$
    DECLARE
        BEGIN
            PERFORM pg_notify( CAST('update_notification' AS text), row_to_json(NEW)::text );
            RETURN new;
        END;
    $$ LANGUAGE plpgsql;
    
    CREATE TRIGGER update_notification
    AFTER INSERT ON categories
    FOR EACH ROW
    EXECUTE FUNCTION public.notify_trigger();
`)

const { Client } = require('pg')

// The file contains the correct information
const client = new Client({
    // user: '**********',
    // host: 'localhost',
    // database: '*********',
    // password: '******************',
    // port: 5432,
    connectionString: 'postgres://*********:*********@localhost/**********'
})

client.connect((err, client, done) => {
    if (err) {
        const msg = 'Error in connecting database: ' + err
        console.log('\x1b[41m\x1b[37m ' + msg + ' \x1b[0m')
    } else {
        const msg = 'Success in connecting database'
        console.log('\x1b[42m\x1b[37m ' + msg + ' \x1b[0m')
        client.on('notification', (msg) => {
            console.log(msg.payload)
        })
        const query = client.query('LISTEN update_notification')
    }
})

module.exports = knex

Postgres supports LISTEN and NOTIFY commands that can be used to inform other applications of changes in the db. These can be used to avoid polling.

socket.io's postgres-adapter supports notifications over websockets.

This video may also be helpful.

You can store the statistics in mongodb, and use watch to monitor changes.

https://www.mongodb.com/docs/drivers/node/current/usage-examples/changeStream/

PS: suggesting this because you mentioned you are using mongodb to store the statistics.