Introduction
Wouldn’t it be great if we could receive event-driven change notifications (EDCN) when data changes directly from the database 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, this functionality is expressed via the implementation of an interface that is then registered with the JDBC driver directly. This opens 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 deal with changes in data and notifying 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
There are, of course, some consequences when relying on this functionality. The most obvious implication 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.
#groovy #sql #mysql #jdbc #oracle
