Is there any way to find out about database updates without queries / fewer queries?

Question

I had a strange problem that was looking for the best solution I can get here. I am developing a rails application that displays data from a shared database that is used by another application (nodejs). All CRUD operations occur on a different platform. In the rails application, we simply query and display the data.

In a rails application, I need to automatically update views without updating. eg

def index @states = State.page(params[:state_page]) @level_one_companies = Company.includes(:state) .where(level: 1) .order('created_at DESC').limit(20) @level_two_companies = Company.includes(:state) .where(level: 2) .order('created_at DESC').limit(20) end 

On the index page, I will have tables for each of them, and I need to update the tables when new data is added to the state of (,) level 1 (or) level 2 companies.

I know that I can go in two ways to automatically view views ie

• Action cable.
• Combining data with a time interval using jQuery.

Usually, when using Action Cable, we will transfer data from the server after creating the record in db (after .save in creating the action (or) after_save callback from the model). However, I do not create any entries through the rails application.

My first question is: is there a way to use an action cable in this case?

So, I went with the second option, and it works great. But that makes too many db calls every X seconds. is there a way to reduce queries to update views? How can I go here? Any help is much appreciated. Thank you

Answer 1

if your tags are set correctly, you use postgres as your database.

postgres offers a publishing mechanism that you can use in conjunction with an action cable to listen for changes to your database.

in this method , you can find an example for postgres-pubsub with server-sent events. it should just translate this into a cable-compatible action code.

Answer 2

You can create a trigger in the table (creates / updates / deletes) that triggers a notification on the “channel”, and you can listen to the specified channel for events. I use socketcluster, listen from workers and pass them to consumers (browsers and mobile applications).

First you create a trigger:

 CREATE FUNCTION deletes_notify_trigger() RETURNS trigger LANGUAGE plpgsql AS $$ DECLARE BEGIN PERFORM pg_notify('deletes_channel', ('DELETED' || ';;' || OLD.id )::text ); RETURN new; END; $$; 

and

 CREATE TRIGGER deletes_trigger AFTER DELETE ON events FOR EACH ROW EXECUTE PROCEDURE deletes_notify_trigger(); 

In the transmitted data packets you can add whatever you want, in my case I only need the identifier of the record. To create and update, you can send the full row or only some selected columns. You can also send it as JSON in PG 9.2 (I think) and above. I use 9.1, so I agree with ;; separators.

Make sure that your code takes no more than 10% of the time between your requests, otherwise if you perform complex joins or updates or other operations, you will notice a significant decrease in performance. You want it to be as simple and fast as possible, save it to the basic operations and do any heavy lifting at the application level.

Then the observer and translation for consumers (in my case node, socketcluster and pg gem, in your case you can use JS, Python, Ruby, whatever you like)

 var global_deletes = socket.subscribe('deletes_channel'); pg.connect(connectionString, function(err, client) { client.on('notification', function(dbmsg) { console.log(dbmsg.payload); var payload = dbmsg.payload.split(";;"); // you can use JSON instead if (payload[0] == "DELETED") { // when a DELETE is received... global_deletes.publish(dbmsg.payload); var tchannel = socket.subscribe('events-'+ payload[1]); // join the channel we want to broadcast setTimeout( () => tchannel.publish(dbmsg.payload), 50); // send the update to all consumers setTimeout( () => tchannel.unsubscribe(), 100); } ); var query = client.query("LISTEN deletes_channel"); // turn on notifications from the server });