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.
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 — a description of event-driven programming can be found on the right-side of this article.
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.
Database Change Notification Article TOC
What is Event Driven Programming?
🤖 Event Driven Programming (EDP) is a programming paradigm in which the flow of a program is determined by events, such as user actions, messages from other programs or the system, or other occurrences. In event-driven programming, the program is designed to respond to these events by triggering actions, rather than following a sequential execution path.
In event-driven programming, the program is designed around a set of event handlers that respond to specific events. When an event occurs, the corresponding event handler is called to process the event. The event handler can then trigger additional events or execute other program logic.
Event-driven programming is often used in graphical user interfaces (GUIs) where user actions, such as clicking on a button or selecting a menu item, trigger events that the program responds to. It is also used in networking, where messages received from other computers or devices trigger events that the program responds to.
Overall, Event Driven Programming allows for more responsive and interactive programs since the program can react quickly to user input or other events without needing to wait for a sequential program flow to complete. 🤖
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.
I have two videos on the Heimdall Database Proxy : the first is entitled Learn how to deliver faster, more reliable, and secure content with the Heimdall Distributed Database Proxy and the second is 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 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.
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 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.
Event-driven change notifications in the PostgreSQL relational database
The PostgreSQL (Postgres) database is the first event-driven change notification example that we’ll examine and I’ve included below the instructions required to configure a trigger in PostgreSQL and how to process the change notification events in the Groovy scripting language.
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.
How to execute event-driven change notifications in the Postgres Database
In the following we reference an example table. The trigger and function script must be executed inside Postgres as a precondition to running the Groovy script.
The notify_change Postgres trigger 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.
Step One: Create a Postgres Trigger function
We need to create a trigger function and then a trigger that notifies on insert, update, or delete operations — an example regarding how this is done appears below — the function that returns a trigger appears on lines 1-17.
CREATE OR REPLACE FUNCTION notify_change() RETURNS TRIGGER AS $$
BEGIN
--
-- WARNING: Case is VERY IMPORTANT here! If we use 'exampleChannel' PG converts this to
-- examplechannel and no events will be received!!
--
-- UPDATE: [to be confirmed] Case can be handled in PostgreSQL by using double quotes.
--
-- In theory, if you had the following line as the listener, it would work in camelCase.
--
-- statement.execute('LISTEN "exampleChannel"');
--
-- The same applies to any identifier in Postgres.
--
PERFORM pg_notify('examplechannel', NEW.phrase);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER table_change
AFTER INSERT OR UPDATE OR DELETE ON example
FOR EACH ROW EXECUTE PROCEDURE notify_change();
Step Two: Create a table change trigger
The second step takes place between lines 20-22 and involves creating a Postgres table change trigger such that insert, delete, and update operations on the example table result in the notify_change function being called.
CREATE TRIGGER table_change
AFTER INSERT OR UPDATE OR DELETE ON example
FOR EACH ROW EXECUTE PROCEDURE notify_change();
Step Three: Create and configure an instance of the Postgres PGDataSource
We need to create an instance of the PostgreSQL PGDataSource and in the example below we set the host, port, database name, user and password properties. The dataSource is used to get the connection to the database on line #23.
PGDataSource dataSource = new PGDataSource();
dataSource.setHost("0.0.0.0")
dataSource.setPort(5432)
dataSource.setDatabaseName("testdb")
dataSource.setUser("postgres")
dataSource.setPassword("password")
Step Four: Implement The PostGreSQL PGNotificationListener Interface
Next, we implement the PGNotificationListener method and in this example we’ll print some information whenever this is called.
The snippet below appears on lines #15 – #21 in the full example.
final def pgNotificationListener = new PGNotificationListener () {
@Override
public void notification(int processId, String channelName, String payload) {
println("processId $processId, channelName: $channelName, payload: $payload")
}
}
Step Five: Get A Connection To The Postgres Database
In step five we’ll get a connection to the PostgreSQL database from the dataSource — this appears on line #23.
final def connection = (PGConnection) dataSource.getConnection()
Step Six: Register the pgNotificationListener with the Postgres database connection.
The connection will invoke methods on the pgNotificationListener so we register this via the addNotificationListener method below, and this is also shown on line #25.
connection.addNotificationListener(pgNotificationListener)
Step Seven: Execute The LISTEN command on the examplechannel
This step takes place over lines #27 – #30 and requires us to create an instance of statement from the connection and then execute the LISTEN command on the examplechannel channel.
final def statement = connection.createStatement()
statement.execute("LISTEN examplechannel")
statement.close()
The remaining code in lines #32 – #44 keep the program running so we can see the output when the NOTIFY command is executed.
Full Example
Below I’ve included the full example — you should be able to paste this into the GroovyConsole, make whatever changes are required to run the example, and then run this.
@Grapes(
@Grab(group='com.impossibl.pgjdbc-ng', module='pgjdbc-ng', version='0.8.4')
)
import com.impossibl.postgres.api.jdbc.PGConnection
import com.impossibl.postgres.api.jdbc.PGNotificationListener
import com.impossibl.postgres.jdbc.PGDataSource
PGDataSource dataSource = new PGDataSource();
dataSource.setHost("0.0.0.0")
dataSource.setPort(5432)
dataSource.setDatabaseName("testdb")
dataSource.setUser("postgres")
dataSource.setPassword("password")
final def pgNotificationListener = new PGNotificationListener () {
@Override
public void notification(int processId, String channelName, String payload) {
println("processId $processId, channelName: $channelName, payload: $payload")
}
}
final def connection = (PGConnection) dataSource.getConnection()
connection.addNotificationListener(pgNotificationListener)
final def statement = connection.createStatement()
statement.execute("LISTEN examplechannel")
statement.close()
def time = 60 * 60 * 1000
println "Will sleep for $time milliseconds..."
try {
Thread.sleep (time)
} catch (Throwable thrown) {
thrown.printStackTrace (System.err)
} finally {
connection.close ()
}
print "...done!"
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.
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). 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 (find the gist on GitHub).
#
# In this example Docker is running on another machine so assume that I've ssh'd into that box and
# am running the following on the remote machine.
#
docker run -d -p 1521:1521 oracleinanutshell/oracle-xe-11g
docker exec -it [container id] /bin/sh
su
#
# Username: system, password: oracle
#
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus
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 (find the gist on GitHub).
--
-- This is required otherwise notifications won't be sent to the JDBC driver.
--
grant change notification to system;
commit;
CREATE TABLE example(
example_id NUMBER(10) PRIMARY KEY,
phrase VARCHAR2(120) NOT NULL
);
commit;
insert into example values (1, 'one');
insert into example values (2, 'two');
insert into example values (3, 'three');
insert into example values (4, 'four');
insert into example values (5, 'five');
commit;
--
-- Then once the DatabaseChangeListenerInOracleDatabaseExample.groovy is running
-- execute the following and an update should appear in the Groovy console:
--
update example set phrase = 'one / 1' where example_id = 1;
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 (find the gist on GitHub).
@GrabConfig(systemClassLoader=true)
@Grapes(
@Grab(group='com.oracle.database.jdbc', module='ojdbc6', version='11.2.0.4')
)
import oracle.jdbc.dcn.DatabaseChangeListener
import oracle.jdbc.dcn.DatabaseChangeEvent
import oracle.jdbc.driver.OracleConnection
import oracle.jdbc.dcn.DatabaseChangeRegistration
import oracle.jdbc.OracleStatement
import java.sql.DriverManager
import java.util.Properties
final def connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.232:1521:xe", "system", "oracle")
def databaseProperties = new Properties ()
databaseProperties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true")
databaseProperties.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, "true")
final def databaseChangeRegistration = connection.registerDatabaseChangeNotification(databaseProperties)
public class ExampleDatabaseChangeListener implements DatabaseChangeListener {
@Override
public void onDatabaseChangeNotification(DatabaseChangeEvent databaseChangeEvent) {
println ("***** databaseChangeEvent: $databaseChangeEvent")
println ("***** databaseChangeEvent.source: ${databaseChangeEvent.source}")
println ("***** databaseChangeEvent.queryChangeDescription: ${databaseChangeEvent.queryChangeDescription}")
println ("***** databaseChangeEvent.tableChangeDescription: ${databaseChangeEvent.tableChangeDescription.each {println '\n - nextTableChangeDescription: $it' } }")
}
}
databaseChangeRegistration.addListener(new ExampleDatabaseChangeListener ())
final def statement = connection.createStatement()
statement.setDatabaseChangeRegistration(databaseChangeRegistration)
try {
resultSet = statement.executeQuery("select * from example")
while (resultSet.next())
{} // println "resultSet.phrase: ${resultSet.getString('phrase')}"
} catch (Throwable thrown) {
thrown.printStackTrace (System.err)
}
println "databaseChangeRegistration.userName: ${databaseChangeRegistration.userName}"
databaseChangeRegistration.tables.each {
println "tables: $it"
}
final def time = 60 * 60 * 1000
println "Will sleep for $time milliseconds..."
try {
Thread.sleep (time)
} catch (Throwable thrown) {
thrown.printStackTrace (System.err)
} finally {
statement.close ()
connection.close ()
}
println "...done!"
The following image goes into deeper detail regarding what each step is doing, along with some 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 a commit has returned successfully.
The next example we’ll cover in this article includes the H2 database.
Database change notifications in the H2 relational database
The H2 Database Engine is an open-source, lightweight, and very powerful relational database written entirely in Java. The H2 Database Engine 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).
The H2 relational database is also embedded in the JBoss Wildfly application server (JBoss) and has been in use in JBoss as an embedded database for a long time.
H2 delivers database change 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:
closingDatabase () : void
exceptionThrown(SQLException sqlException, String sql) : void
init (String url): void
opened (): void
setProgress (String state, String name, int x, int max) : void
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 the following 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.
Find the gist below on GitHub.
@GrabConfig(systemClassLoader=true)
@Grapes(
@Grab(group="com.h2database", module="h2", version="1.4.200")
)
import org.h2.api.DatabaseEventListener
import java.sql.SQLException
import java.sql.DriverManager
public class ExampleDatabaseEventListener implements DatabaseEventListener {
public void closingDatabase () {
println "closingDatabase: method invoked."
}
public void exceptionThrown (SQLException sqle, String sql) {
println "exceptionThrown: method invoked; sqle: $sqle, sql: $sql"
}
public void init (String url) {
println "init: method invoked; url: $url"
}
public void opened () {
println "opened: method invoked."
}
public void setProgress (int state, String name, int x, int max) {
println "setProgress: method invoked; state: $state, name: $name, x: $x, max: $max"
}
}
//
// Note the event listener has been added as a parameter in the connection URL below.
//
def cxn = DriverManager.getConnection("jdbc:h2:mem:EventListenerInH2DatabaseExampleDB;DB_CLOSE_DELAY=-1;DATABASE_EVENT_LISTENER='ExampleDatabaseEventListener';")
def stm = cxn.createStatement()
def resultSet = stm.executeQuery("SELECT 1+1")
if (resultSet.next()) {
println("next: ${resultSet.getInt(1)}")
}
cxn.close ()
println "...Done!"
In the following image, we can see an example of this script being executed, along with the output in the GroovyConsole.
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.
Database change notifications in MySQL / MariaDB
MySQL Listen Notify functionality via the JDBC driver does not appear to be supported by either MySQL or MariaDB databases at this time and so engineers will have to consider alternative solutions if this functionality is required.
One possible solution for this would be to develop code that performs the create, delete, and/or update operation and then on successful commit a message is sent to registered endpoints.
We could implement database change notifications using User Defined Function (UDF) but just because we can, doesn’t mean we should.
MySQL Listen Notify via UDFs
We will not cover triggers and User-Defined Functions (UDF) as they pertain to MySQL and MariaDB to call a web service endpoint.
Brief research on database change notifications in MySQL and MariaDB 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.
There are projects which provide this functionality and the Cyclonecode MySQL Notification project is one of them, which we’ll briefly cover in the next section.
Cyclonecode MySQL Notification
The Cyclonecode MySQL Notification project on GitHub offers a solution for receiving notifications when insert, update, and delete operations are executed — the project description is included below.
A simple example of using a user defined function (UDF) in mysql to make real-time notifications on a table change. This project consists of a mysql plugin that setups a server socket that receives messages from a trigger connected to INSERT, UPDATE, DELETE operations on a specific table in the database. The server will then send a message to a nodejs server that in turn will bounce this notification to any connected http client over a websocket.
This project could be a good place to look if you’re considering going down this path.
Maxwell’s daemon
Maxwell’s daemon, is an active project which is available on GitHub and has 136 contributors and 3.8k stars as of 05.Sept.2023.
Maxwell’s daemon is described as follows:
This is Maxwell’s daemon, a change data capture application that reads MySQL binlogs and writes data changes as JSON to Kafka, Kinesis, and other streaming platforms.
Database change notifications sent to a streaming service could then be pushed to interested client endpoints.
Maxwell’s daemon is a different architecture than receiving notifications via JDBC but it could work very well, while removing much of the need for writing custom code in order to solve this problem.
MySQL Notification Service
The MySQL Notification Service sounds like it might fit the bill however this is not the case as this service is used for performance monitoring.
The MySQL Notification Service is also not available via the JDBC driver and instead requires implementing an interface in C++.
The MySQL Notification Service will notify the callback when threads are created and destroyed and when sessions are connected, disconnected, or a user changes.
Finally, if there’s a better solution available in MySQL and MariaDB, please leave a comment and let me know.
Alternative solutions to notify when a table changes in MySQL and MariaDB
The StackOverflow thread entitled MySQL listen notify equivalent is worth a read as it covers several solutions which are specific to MySQL and MariaDB including:
- Custom loadable functions (previously user-defined functions (UDFs)) written in C++ which is an ugly option, in my opinion (see also UDFs in MariaDB). It is also not possible to use a MySQL UDF on Amazon AWS RDS according to the StackOverflow thread which is another strike against this solution as many businesses are using AWS RDS.
- Polling the database every few seconds, which is also an ugly solution.
Considering the options listed above, my current opinion is that if I had to implement a MySQL listen notify solution, I’d probably start with Maxwell’s daemon — note that I’ve not yet had the opportunity to work with this project directly and I’m basing my opinion strictly from the research I’ve conducted on this subject.
There appear to be better solutions when working with Amazon Web Services (AWS), in particular if you’re using the AWS Aurora database service, see [1] and [2] below.
See Also
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!
