Available in versions: Dev (3.18) | Latest (3.17)

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:

XML (standalone and maven)
Programmatic
Gradle
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.17.0.xsd">
    <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>
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")
      )
    )
  )
myConfigurationName(sourceSets.main) {
  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'
        }
      }
    }
  }
}

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:

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 t_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(T_AUDIT)
      .columns(T_AUDIT.ID, T_AUDIT.VAL)
      .values(1, 1)
      .execute();

Might produce a SQL statement like this:

INSERT INTO public.t_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.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo