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
