Hidden Gems: Event-Driven Change Notifications in Relational Databases

A powerful non-standard feature that developers should know about

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.

I proffer the following question to any engineer considering using EDCNs 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 EDCNs 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.

Below we explore this functionality as it exists in the PostgreSQL, Oracle, and H2 databases; we also include some general comments on MySQL and its fork, MariaDB.

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.

PostgreSQL

The PostgreSQL (Postgres) database is the first example we’ll examine.

The Postgres API includes the PGNotificationListener interface which must be implemented and then registered with the database connection. Note that there are two implementations of this available: the Postgres [default] JDBC driver and the Impossibl JDBC driver implementation. We do not want to use the Postgres driver, as that implementation will poll the database for changes. Instead, we will rely on the Impossibl implementation, which delivers true event-driven notifications.

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.”

Heimdall Data 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.

In the following example, the trigger and function script must be executed inside Postgres as a precondition to running the Groovy script. The notify_change function will send events to any registered listeners that are listening on the examplechannel channel — pay particular attention to the warning below, as channel names are case sensitive.

A working example implementation of the com.impossibl.postgres.api.jdbc. PGNotificationListener using the PostgreSQL database is included next. The PGNotificationListener interface requires that the developer implement just one method:

void notification(int processId, String channelName, String payload)

We can see this on line #18 below.

We can see an example of this script being executed along with an explanation and the output which appears in the GroovyConsole in the following image.

An explanation of the PostgreSQL example running in the Groovy Console.

The next example we’ll cover includes event-driven change notification functionality as it applies to the Oracle Database.

Oracle

The next example we’ll cover in this article will focus on the Oracle Database (Oracle). Below we detail the steps required to configure event-driven change notifications via the JDBC driver along with preconditions required to run the example.

The following two gists are required preconditions for this example. It is instructive to note that Docker was running on another machine which, in this case, uses the Ubuntu operating system. See the warning regarding running Oracle in Docker locally in the DatabaseChangeListenerInOracleDatabaseExample.groovy script for complete details.

In SQL*Plus, we can now run the following configuration script. Keep in mind that once the example table has been created, see line #8, the Groovy script in the next section can be started and any insert, update, or delete operations on the target table will result in an event being sent to the Groovy script and then printed to the console output.

Here we have an example of the complete DatabaseChangeListenerInOracleDatabaseExample.groovy script. Note that the developer must implement one method:

void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent)

We can see this implementation on line #55 below.

The following image goes into deeper detail regarding what each step is doing, along with some notes explaining the output.

A deeper explanation regarding what the example script, as it pertains to the Oracle database, is doing, including notes explaining the output.

Lastly, the following image demonstrates that when we perform five inserts in a row and then commit the changes, only a single event is emitted, which includes these five inserts. Events are only ever emitted whenever commit has returned successfully.

Five inserts are executed in SQL*Plus followed by a single commit and we can see that this event includes these five insert operations.

The final example we’ll cover in this article includes the H2 database.

H2 Database

The H2 database is an open-source, lightweight, and very powerful relational database written entirely in Java. It supports a long list of features and ships as a single 2.2mb jar file. H2 is frequently used when testing Java applications and works well as an embedded database and can be used with object-relational mapping tools such as the Java Persistence API (JPA). H2 is also embedded in the JBoss Wildfly application server (JBoss) and has been in use in JBoss as an embedded database for a very long time.

H2 delivers event notifications via the org.h2.api.DatabaseEventListener interface. The DatabaseEventListener interface offers limited functionality when compared with the Postgres and Oracle listener specifications covered earlier. The methods required to implement the interface are:

void closingDatabase ()
void exceptionThrown(SQLException sqlException, String sql)
void init (String url)
void opened ()
void setProgress (String state, String name, int x, int max)

A working example implementation of the org.h2.api.DatabaseEventListener using the H2 database can be found on GitHub and is also included in the gist below followed by an image with pointers explaining how this works.

In this example, H2 is running in embedded mode — that is, H2 is running entirely in memory in the same virtual machine that the Groovy script is running in.

In the following image, we can see an example of this script being executed, along with the output in the GroovyConsole.

An example of the H2 DatabaseEventListener running in the Groovy Console including output.

It’s somewhat disappointing that the H2 DatabaseEventListener doesn’t offer similar functionality to that found in the PostgreSQL interface. As a result, I submitted a new feature request on the H2 Database repository on GitHub and may try to implement this myself, time permitting.

MySQL/MariaDB

Event-driven change notifications via the JDBC driver do not appear to be supported by either MySQL or MariaDB databases and so engineers will have to consider alternative solutions if this functionality is required.

We will not cover triggers and user-defined functions (UDF) as they pertain to MySQL and MariaDB to call a web service endpoint, which is one such alternative. Brief research on this subject suggests that triggers and UDFs can be used to accomplish this; however, they come with potentially significant security and performance implications that must be considered when taking this approach.

If you’ve used triggers and UDFs or some other solution to accomplish this within MySQL and/or MariaDB please feel free to elaborate on what you needed to do, the approach you took, and how well it worked out in the comments. Finally, If there’s a better solution available in MySQL and MariaDB, please explain further.

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 was it needed for?
• What database was used?
• What language was the solution written in?
• Can you do it with Microsoft SQL Server or another 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!

Before You Go

If you found this document to be useful, you may also find the Washington, DC Chief Technical Officer Meetup Group to be helpful too. We run events frequently and as these are available over Zoom being local to the DC area is not a requirement.

See also:

• DC Metro Area Chief Technology Officer Slack Community (alternate link here)
• DC Metro Area Chief Technology Officer (CTO) LinkedIn Group

Cover image compliments of Chris Coe