SQL Server Realtime Push Notifications to Node.JS

I have a similar situation, and after researching, the solution I am going to go with is to write a .Net Core windows service that does nothing but listen for query notifications from SQL Server. When a notification arrives, it will hit a nodejs REST endpoint notifying it of the change, and allowing the nodejs application to query and process the data as needed.

I searched and searched for a way to do the Query Notifications from nodejs, but it seems nothing exists for this. You need a .Net application.

However, that said, another possibility instead of writing a .Net Core windows service (or an ASP.Net application if you want) is to use EdgeJs to actually run a .Net assembly inline with your nodejs application (in the same process).

I have decided not to go that route, and will do the windows service instead. But it should also be viable.

(I also realize this question is 2 years old, so you've probably already completed this project, but I'll leave these answers here for posterity)

I've got to preface this by saying I don't have a real-time solution. If there's a real-time solution, I don't know it. I'm just gonna talk about decreasing the poll's load.

I'm assuming you have control over the SQL Server itself. You can set up something in SQL Server to keep track of changes and get polling to only look at changes not yet pulled by Node.

Do you have control over the update/insert process? If you do, easy, put the change tracking code alongside the insert/update code. I'm assuming you don't though, in which case, I'd recommend* looking in to triggers. These are basically event listeners attached to tables that allow you to execute SQL before, during or after the insert/update of a table, and give you access to the data being changed/inserted using the deleted and inserted tables.

* Triggers are not liked by the SQL Server community for a wrath of reasons, some of which are discussed on this SQLServerCentral article. The jist of things: They are difficult to debug, they slow down performance as they become part of the write operation, and be careful to not create circular triggers (table1 is updated which triggers trig1, which updates table1, which triggers trig1, etc.). As such, if you are doing change tracking using triggers on a table, create a separate table in which those changes are tracked, and only let the trigger update/insert that table.

Now you can set up a change track table that the trigger populates with any changes made, and when they were made, and perhaps even a bit field to say whether Node has pulled those changes yet. Now all you have to do is get Node to look at that table and only pull in changes it hasn't pulled in yet. This table's probably going to become huge pretty quick, and therefore slow for the poll, so I'd recommend putting an index on the flag or the field that states the date the change was made. Now since poll is quick, you should be able to decrease the interval.

This is not an ideal solution and may be a lot of work, so it's probably best to wait a while and see if anyone comes up with a better solution.

You can resolve the problem with Triggers and CLR Stored Procedures. After update a table you can execute a CLR stored procedure what do something like send a notification to your Nodejs for example.