is there any way to get to know about database updates without querying /less queries?

Question

I ran into a weird problem looking for the best solution that i can get here. I'm developing a rails application which displays data from a common database that is being used by another application(nodejs). All CRUD operations happens at the other platform. In the rails app, we just query and display the data.

In rails app, I need to auto update views without refreshing. for example

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 these and I need to refresh tables when new data is added to the state, (or) Level 1 (or) Level 2 companies.

I know i can go with two ways to auto update views i.e

1. Action Cable.
2. Data pooling at a time interval using Jquery.

Usually while using Action Cable we will broadcast data from server after a record is created in the db(after .save in create action (or) after_save callback from model). However, I'm not creating any records through rails app.

My first question is, is there any way use action cable in this case?.

So i went with the second option and it's working fine. But it making too many db calls after every X seconds. is there any way to reduce queries to update views? What is the best way i can go with here? Any help highly appreciated. Thanks.

Answer 1

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

postgres offers a publish-subscribe machanism that you could use in combination with action-cable in order to listen to changes in your database.

in this gist, you can find an example for postgres-pubsub with server-sent-events. it should be simple to translate this into action-cable compatible code.