Introduction:
Database Auditing (Change Data Capture) is very important for a lot of critical OLTP/OLAP business applications. Simple auditing means keeping track of Last Updated User and Last Update Date/Time for all the entities. However, if we want to keep track of all the user activities including history of changes, what changes were done at a particular revision, diff between two changes, how the entity looked like at a particular revision etc., this becomes quickly complicated.
It’s a common practice in application development to audit the changes on entities and displaying the same on demand. The old way of doing database auditing is adding custom application code or SQL triggers for insert, update and delete operations on entities. This approach increases the maintenance and requires more effort for the developer. Fortunately, ORM frameworks offer different modules for implementing auditing. In this blog, we will look at different auditing approaches that are available.
Auditing Approaches:
Below are some of the auditing approaches provided by different frameworks along with their key differences.
- JPA
JPA does not provide explicit API for auditing, but it can be achieved by having an Audit EntityListener that implements JPA’s lifecyle event methods like @onPrePersist, @onPreUpdate and @onPreRemove. We can audit DB operations by implementing these callback methods.
- Spring Data JPA
Spring Data JPA extends JPA by adding an extra layer of abstraction. It provides AuditingEntityListener and annotation support for auditing. It provides @CreatedBy, @LastModifiedBy, @CreatedDate, @LastModifiedDate annotations to capture audit info. Both JPA and Spring Data JPA helps in basic auditing to track created/modified time and by which user.
- Hibernate Envers
Envers is a very mature and powerful framework for advanced auditing. Like source code version control, Envers uses the concept of Revisions. Apart from basic auditing support that JPA and Spring Data JPA provides, Envers also supports advanced auditing and helps us in tracking the history of changes, what changed in a particular revision, how an entity looked like a particular revision etc., Envers also provides a powerful query API to query the auditing data.
- Debezium
Debezium offers a unique way to get change data capture events from databases and push them to Kafka queue. This is outside the main application code base. We need DB specific Debezium connector and based on the DB it either reads the Redo Logs (Oracle, MySQL etc.,) or Write-Ahead Logs (PostgreSQL etc.,) and pushes the changes to Kafka queue. Then, the desired application can read from Kafka queue and display the DB change events to the end user. Debezium also supports NoSQL databases like MongoDB.
This table shows the main differences between various auditing approaches:
| JPA | Spring Data JPA | Envers | Debezium |
| Need to externally configure listeners that will be called during DB operation. | Need to externally configure listeners that will be called during DB operation. | Listeners are automatically registered once envers jar is included in the classpath. | Need to have Debezium DB connector and Kafka Topic setup. |
| Relies on JPA entity lifecycle callback methods. | Provides handy annotations for auditing properties. | Provides annotation support for auditing properties. | This is outside the main application code base. |
| Cannot skip specific properties from auditing. | Cannot skip specific properties from auditing. | Can skip the unwanted properties from auditing. | All the changes that go to Logs are captured. |
| Delete operation cannot be audited. | Delete operation cannot be audited. | Delete operations can be audited. | Delete operations can be audited. |
| Can help with simple auditing to track createdByUser, lastUpdatedUser and lastUpdatedTime. | Can help with simple auditing to track createdByUser, lastUpdatedUser and lastUpdatedTime. | Supports advanced auditing and helps us in tracking the history of changes, what changed in a particular revision, how an entity looked like a particular revision etc., Envers also provides a powerful query API to query the auditing data. | Supports advanced auditing and can track the complete set of changes in each revision. However, does not have query API like Envers. |
In this blog, we looked at different auditing approaches and their pros/cons. As you can see, Hibernate Envers was much better for advanced auditing needs. In our next blog, we will look at Hibernate Envers in detail.