The jOOQ User Manual SQL building Schema diff

previous : next

Schema diff

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Starting with jOOQ 3.13, and the capability of interpreting DDL, jOOQ is now able to create a "diff" between two versions of your schema. An example:

// We're using interpreted Meta objects. But any other type of Meta can be used, too
Meta m1 = create.meta("create table t (i int)");
Meta m2 = create.meta("create table t (i int, j int)");

// The diff is now printed in both directions:
System.out.println("-- Schema upgrade");
System.out.println(m1.migrateTo(m2));
System.out.println();
System.out.println("-- Schema downgrade");
System.out.println(m2.migrateTo(m1));

The output of the above program is:

-- Schema upgrade
alter table T add J int;

-- Schema downgrade
alter table T drop J;

The diff algorithm can non-ambiguously determine the following set of changes

• Catalog additions and removals
• Schema additions and removals
• Table additions and removals
• Column additions and removals
• Column type changes
• Constraint additions, removals, and renamings (including primary keys, unique keys, foreign keys, and check constraints)
• Index additions, removals, and renamings
• View additions, removals, and replacements
• Sequence additions, removals, and sequence flag changes
• Comment additions and removals

The following set of changes are difficult to detect. Some heuristics could be implemented, but are not yet supported in jOOQ:

• Catalog renamings
• Schema renamings, and moving between catalogs
• Table renamings, and moving between schemas
• Column renamings, and moving between tables
• View renamings

Another way to use this API is the https://www.jooq.org/diff website.

The jOOQ User Manual SQL building Schema diff

previous : next