How do I trigger a push notification to a mobile device when a row gets inserted into a SQL Server table?

2
votes

We've been tasked with implementing push notifications in our iOS and Android app. One of the features of the app is chat messaging, so we would like to push notify our users when they receive a message. The messages can be generated from the web app, so regardless of the origin, the chat messages get inserted into a Chat SQL Table via C# Web Services.

In my research I found PushSharp would be a good fit for our C# backend -- trying to avoid having to pay for a push notification service if we can. What I'm having a difficult time visualizing is how to trigger the push notification when a new message gets inserted to the DB table.

What's the best practice? I assume manually polling for new records is not.

Any advice would be appreciated.

M.

2
iospush-notificationapple-push-notifications
is your code doing the insertion? why not just send off the push when that work is done? – Patrick Goley
There are many entry points in how a message gets inserted into the DB, like from the web application -- I have no control on that piece of the code unfortunately. – Mariano Alvarez
if possible, you'd like to funnel all entry points into one place that either sends the push or calls back for someone else to do it. if you don't have control over the web app code, you know someone who does right? it can't be expected to work from all entry points if you can't refactor them to do so – Patrick Goley
I understand, that'd be the ideal scenario but unfortunately there are many players inserting rows into the chat table. I need some kind of demon/listener that is constantly watching for new chat rows to get inserted. – Mariano Alvarez

2 Answers

1
votes

Probably it's too late but for the new guys that just came here occasionally, I suggest to try debezium, it consumes events for each row-level change made to the database. Only committed changes are visible, so your application doesn't have to worry about transactions or changes that are rolled back.

0
votes

There are a couple of solutions available to you. Some depend on the level of control you have on the table. Here are a couple of ideas :

  1. Use a daemon to run a script that periodically checks for new entries and sends pushes when necessary. The script can rely on a tuple id field (probably the primary key) to record the last field it checked and then pick up from there periodically. You can use supervise or monit to set that up but there are many other solutions out there that might be better fitted for your server.
  2. A more simple solution would be to create a cronjob entry that triggers the script mentioned above periodically.
  3. If you don't control the original table, you can create a TRIGGER in MySQL that inserts a record in a separate table that you can control entirely and can poll
  4. If you don't want to poll (which is in fact not preferable if you have a lot of data to go through at a high rate), you'll have to look into message queue systems (like RabbitMQ) or into PUBSUB (I personally like Redis PUB/SUB).

Without more information about what your current architecture is, it's difficult to give you more details or point you to a better solution.