I currently have a Scala application (with Java JDBC connector ) that queries SQL Server database table every minute for new entries inserted, then it does processing.
Is there a scala tool or library that I can use to listen for new entries inserted into the SQL Server Database table? I believe this new approach of listening to a table will give high efficiency.
I tried using Slick with akka streams to stream data from the table but I didn’t know to configure it to listen for new entries on the table.
Database - SQL Server Database
Connection tool - Java JDBC
I’d be interested to know if something generic for this exists too, though I really don’t think it does. “Table change notification” is not a generic JDBC feature, nor is it even a normal feature of most RDBMSs.
A couple ideas:
Look for a store that provides change notifications
** E.g., AWS DynamoDB can stream change notification messages to arbitrary AWS services
** MongoDB has an add-on that can stream change notifications
** I’ve read descriptions of a MySQL-based solution that works by “watching” the transaction logs and streaming change notifications, though I’ve never used it
Roll your own change notifications: utilize RDBMS table triggers to create your own log, which you monitor separately monitored
** E.g., an “on update” trigger for table X writes a new entry to table “X_changes” describing the new record
** A secondary process in your application poles the “X_changes” table, sending notifications for each row and then deleting the row
** This can also be used obviously to implement update and delete records as well
** Basically the same as your original idea, though more generalized
Probably not immediately helpful, but a data point some may be interested in:
You can do this with Postgres. You can set up a rule that notifies a channel when a table is updated (or whatever) and users can listen on that channel. Database libraries can expose this but I’m not aware of any that do it other than doobie, which exposes channels as fs2 streams.