Audit columns
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A common use-case for (STORED) client side computed columns are audit columns. There exist many ways to implement auditing, including:
- Journalling all changes using a trigger that stores the complete change into a separate table
- SQL:2011 temporal tables, which are more powerful than mere auditing
- Using a trigger to fill in a few technical columns, such as
CREATED_AT
,MODIFIED_AT
, etc.
While jOOQ recommends you use a trigger or other out-of-the-box, standard SQL features, in order to implement auditing directly inside of your database (to make sure no one can bypass it), there are valid use-cases where you may not want to do this in the database itself, e.g. because you cannot create triggers for some privilege or other technical reasons. For those cases, jOOQ offers basic support for audit columns.
For example, you can set up your code generation configuration like this:
<configuration> <generator> <database> <forcedTypes> <forcedType> <auditInsertTimestamp>true</auditInsertTimestamp> <includeExpression>CREATED_AT</includeExpression> </forcedType> <forcedType> <auditInsertUser>true</auditInsertUser> <includeExpression>CREATED_BY</includeExpression> </forcedType> <forcedType> <auditUpdateTimestamp>true</auditUpdateTimestamp> <includeExpression>MODIFIED_AT</includeExpression> </forcedType> <forcedType> <auditUpdateUser>true</auditUpdateUser> <includeExpression>MODIFIED_BY</includeExpression> </forcedType> <forcedType> <auditInsertTimestamp>true</auditInsertTimestamp> <auditUpdateTimestamp>true</auditUpdateTimestamp> <includeExpression>CREATED_OR_MODIFIED_AT</includeExpression> </forcedType> <forcedType> <auditInsertUser>true</auditInsertUser> <auditUpdateUser>true</auditUpdateUser> <includeExpression>CREATED_OR_MODIFIED_BY</includeExpression> </forcedType> </forcedTypes> </database> </generator> </configuration>
See the configuration XSD, standalone code generation, and maven code generation for more details.
new org.jooq.meta.jaxb.Configuration() .withGenerator(new Generator() .withDatabase(new Database() .withForcedTypes( new ForcedType() .withAuditInsertTimestamp(true) .withIncludeExpression("CREATED_AT"), new ForcedType() .withAuditInsertUser(true) .withIncludeExpression("CREATED_BY"), new ForcedType() .withAuditUpdateTimestamp(true) .withIncludeExpression("MODIFIED_AT"), new ForcedType() .withAuditUpdateUser(true) .withIncludeExpression("MODIFIED_BY"), new ForcedType() .withAuditInsertTimestamp(true) .withAuditUpdateTimestamp(true) .withIncludeExpression("CREATED_OR_MODIFIED_AT"), new ForcedType() .withAuditInsertUser(true) .withAuditUpdateUser(true) .withIncludeExpression("CREATED_OR_MODIFIED_BY") ) ) )
See the configuration XSD and programmatic code generation for more details.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
generationTool { generator { database { forcedTypes { forcedType { auditInsertTimestamp = true includeExpression = "CREATED_AT" } forcedType { auditInsertUser = true includeExpression = "CREATED_BY" } forcedType { auditUpdateTimestamp = true includeExpression = "MODIFIED_AT" } forcedType { auditUpdateUser = true includeExpression = "MODIFIED_BY" } forcedType { auditInsertTimestamp = true auditUpdateTimestamp = true includeExpression = "CREATED_OR_MODIFIED_AT" } forcedType { auditInsertUser = true auditUpdateUser = true includeExpression = "CREATED_OR_MODIFIED_BY" } } } } }
See the configuration XSD and gradle code generation for more details.
Using the above flags, you can specify an org.jooq.impl.AuditGenerator
to be applied to your columns, which is just convenience for a hand-rolled (STORED) client side computed columns. All such audit columns compute their actual value from Configuration.auditProvider()
, which allows for overriding the org.jooq.impl.DefaultAuditProvider
behaviour, which is:
- Using CURRENT_TIMESTAMP, CURRENT_TIME, CURRENT_DATE, or a similar function, if the data type is temporal, and we're auditing timestamps.
- Using CURRENT_USER if the data type is a
String
, and we're auditing users.
The two types of org.jooq.AuditType
flags (USER
, TIMESTAMP
) are mutually exclusive, but the two types of org.jooq.GeneratorStatementType
(INSERT
, UPDATE
) can be combined in order to trigger writing to the column on either or both types of operations.
As any other (STORED) client side computed columns, this transforms any jOOQ generated DML statement, irrespective of whether it originates from within jOOQ (e.g. via the UpdatableRecord API or DAO API), or whether you hand-roll it using the jOOQ DSL. For example, assuming a table like this, which will be generated using the above <forcedTypes/>
configuration:
CREATE TABLE audit ( id INTEGER NOT NULL, val INTEGER NOT NULL, created_at TIMESTAMP NOT NULL, created_by VARCHAR(100) NOT NULL, modified_at TIMESTAMP, modified_by VARCHAR(100), created_or_modified_at TIMESTAMP NOT NULL, created_or_modified_by VARCHAR(100), CONSTRAINT pk_t_audit PRIMARY KEY (id) );
Now, the following jOOQ statement:
create.insertInto(AUDIT) .columns(AUDIT.ID, AUDIT.VAL) .values(1, 1) .execute();
Might produce a SQL statement like this:
INSERT INTO public.audit ( id, val, created_at, created_by, created_or_modified_at, created_or_modified_by ) SELECT id, val, current_timestamp, current_user(), current_timestamp, current_user() FROM ( SELECT 1, 1 ) AS t (id, val)
Combining this feature with embeddable types is particularly useful.
Limitations
The computed column expression (and thus the audit expressions) is a property of the generatedorg.jooq.DataType
. As such, the property can only be enforced on expressions which make this flag available to jOOQ. For example, if you're using plain SQL templates without passing along aDataType
with the computed expression enabled, then the feature cannot be enforced. See also features requiring code generation for more details.
Feedback
Do you have any feedback about this page? We'd love to hear it!