Hidden Gems: Event-Driven Change Notifications in Relational Databases
Wouldn’t it be great if we could receive event-driven change notifications (EDCN / database change notifications) when data changes directly from relational databases without having to poll for updates?
This feature is, in fact, available in some relational databases, but not all, as it’s non-standard functionality and not part of any SQL specification.
In the three examples covered in this article, event-driven change notification functionality is expressed via the implementation of an interface that is then registered with the JDBC driver directly.
Database Change Notification Article TOC
Event-driven change notifications open the door to a myriad of potential use cases that can be expressed without the need to poll and which do not require the developer to write infrastructure code to watch for changes and then, when a modification is detected, notify all interested parties.
Instead, we can interface with the driver directly and listen for changes and, when they occur, execute whatever workflow we have in an event-driven fashion.
A few examples where this could be helpful include:
- Caching (more on this when we cover PostgreSQL, see also Good Candidates for CQN)
- Honeypots for database tables — also see poison records
- Debugging problems
- Logging changes
- Analytics and reporting
- Facilitating an event-driven architecture
Database Change Notifications are a form of event-driven programming, which is a common pattern in software development.
The article entitled A simple Redis pub sub client written in Groovy using Jedis provides an example, which is written in the Groovy programming language, of event driven software development using the Redis key/value store.
Heimdall Data Caching: A real world use case
Heimdall Data, a Newark California based technology company has developed the Heimdall Database Proxy which takes advantage of event-driven change notifications in the PostGreSQL database specifically for cache invalidation purposes.
I had the opportunity to speak with Erik Brandsberg, CTO of Heimdall Data about this, and Erik remarked that:
“The PG notify interface is one of the hidden gems in PG vs. other databases. We can use it to provide cache invalidation messages between proxies instead of using a distinct pub/sub interface such as on Redis.”
The Heimdall Data Caching solution delivers a sophisticated caching solution for applications that use Amazon Relational Database Service (Amazon RDS) and other databases and this is one real-world use case that demonstrates how important this functionality can be.
See also the video that includes more information about the Heimdall Database Proxy entitled Scaling Postgres Beyond PostgreSQL PgBouncer & Postgres Pgpool-II: Advanced Traffic Management.
Consequences of relying on database change notifications
There are, of course, some consequences when relying on event driven change notifications.
The most obvious implication when relying on database change notifications is that it’s a non-standard feature that ties the application directly to the database.
I was speaking with Michael Dürgner on LinkedIn about an example implementation as it pertains to PostgreSQL, and he commented that:
“[W]hile it’s definitely a great way to do this, one of the big drawbacks is that you move application logic into the RDBMS. Not saying you shouldn’t do it but make sure that you have people with deep understanding of the RDBMS you use on board since it’ll be rather unlikely your average software will be able to trouble shoot. Another huge challenge with this approach is continuous delivery since your RDBMS needs to be deeply integrated with your delivery pipeline.”
I agree with Michael’s position, and keeping business logic out of the database tends to be a good practice.
Projects that rely on object-relational mapping (ORM) tools such as the Java Persistence API (JPA) to generate the database schema directly from one or more object models immediately lose portability and simplicity when developers are required to add logic in the database tier which probably belongs in the application itself.
If developers are not careful, they’ll end up having to use the same database for testing as used in production and this could easily lead to pain and regret.
When it’s appropriate to use event-driven change notifications
I proffer the following question to any engineer considering using database change notifications via the JDBC driver: can the application still function as intended without the inclusion of whatever it is that you’re building that relies on this functionality?
If the answer is “yes” then what you’re doing is likely fine; on the contrary, if the answer is “no”, then this is a strike against using database change notifications and alternatives may need to be considered.
Finally, this feature on its own is not a substitute for well-engineered message-oriented middleware (MOM), which typically provides out-of-the-box solutions for guaranteed delivery, message persistence, at-least-once/exactly-once delivery, delivery via queues and topics, strategies for flow control (see also: backpressure), and addresses fault tolerance and scalability concerns.
The presence of these requirements could be a strong indicator that an approach relying on EDCNs needs to be reconsidered.
Event-driven change notification examples
Below we explore this functionality as it exists in the PostgreSQL, Oracle, and H2 databases; we also include some general comments on implementing listen notify in MySQL.
Throughout this article, we rely on Java 13.0.2 and Groovy. 3.0.4 and include links to the various scripts on GitHub which contain extra notes pertaining to how to set up the required dependencies and any other preconditions necessary to run the examples.
Event-driven change notifications in the PostgreSQL relational database
This section has been moved to a standalone article on subscribing to table changes in Postgres using the PGNotificationListener interface and the Impossibl JDBC driver.
The next example we’ll cover includes event-driven change notification functionality as it applies to the Oracle Database.
An Example Of Event Driven Programming Via The Oracle Relational Database
The next example we’ll cover in this article will focus on the Oracle Database (Oracle).
This section of this article has been moved to a standalone article on Oracle change notifications that includes a full working example.
Oracle Continuous Query Notification (Oracle CQN) is a real-time event notification feature in the Oracle Database that allows applications to receive immediate notifications when specified database changes occur, enabling timely responses as well as data synchronization.
The next example we’ll cover in this article includes the H2 database.
Database change notifications in the H2 relational database
I cover instructions regarding how to create a trigger in the H2 database in another article.
Database change notifications in MySQL / MariaDB
Implementing MySQL Listen Notify functionality and notes regarding subscribing to table changes in MySQL has been moved to a separate article.
Microsoft SQL Server Database Change Notifications
Microsoft SQL Server does not support database change notifications via the JDBC driver. Microsoft SQLServer does have several options available, however, that can be used to achieve similar functionality including the following:
Microsoft SQL Server Query Notifications
Microsoft SQL Server provides a feature called Query Notifications, that allow you to receive notifications when the results of a specific query have changed.
Query Notifications can be used to monitor changes in the data and then trigger actions based on those changes.
I have not found an example yet that demonstrates this functionality working in a Java application.
Microsoft SQL Server Service Broker
Microsoft SQL Server offers a feature called Service Broker that can be used to build asynchronous messaging applications within the database.
The SQL Server Service Broker allows the developer to create message queues and send messages between different processes or services.
Consider using the SQL Server Service Broker when you need more complex and versatile database change notifications as it can send messages to external messaging systems.
These external messaging systems can be integrated with Microsoft SQL Server and other components of your application in order to provide efficient and reliable event-driven communication.
See Also
How To Run Microsoft SQL Server In An Apple Mac
The following example was developed on a Mac laptop and I relied on the article entitled How to install SQL Server for Mac however with some slight changed, which I’ve noted here.
docker pull mcr.microsoft.com/mssql/server:2022-latest
We can see an example of the docker pull command output below:
In the next step we create a container from the docker image using the following command:
docker run --name SQLServer -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=password-12345' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest
We can see an example of the docker run command output below:
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
docker exec -it [your container id here] /bin/sh
/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'password-12345'
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut elit tellus, luctus nec ullamcorper mattis, pulvinar dapibus leo.
create database exampledb
go
use exampledb
create table example(example_id [bigint] IDENTITY(1,1), phrase [varchar](120)) ON [PRIMARY]
go
See Also
Conclusion
Have you used event-driven change notifications in relational databases in a project you were on? If so, I encourage you to discuss any of the following in the comments:
- What database was used and what was the change notification needed for?
- What language was the solution written in?
- Can you implement event-driven change notifications in Microsoft SQL Server or another relational database not covered here?
- Were any problems encountered, and how were they addressed?
- Was this option explored and determined to not be an appropriate solution for the problem at hand?
- Were you forced to use an alternative and, if so, what did the implementation look like and why?
- Any other thoughts you might have, including any questions I might have left out here.
While EDCNs offer powerful functionality in supported relational databases, we hope that, in addition to providing some working examples, it has also been made clear that there’s a cost associated with relying on this feature and that you must give due consideration before using it.
Happy coding!
