Formatted sql identity auto increment syntax?

The docs could use an update to include an example of an autoincremented identity column.

e.g. this works in postgres and h2 but not mysql:

create table Something (
    id bigint generated always as identity primary key

In mysql, you need to do:

create table Something (
    id bigint not null auto_increment primary key

What’s the proper syntax so that liquibase generates the proper identity column for all databases?

Hello, @kenkyee - Welcome to the liquibase community!

There is no auto-increment SQL that’s valid for all databases. They are all special in their own ways. Your best options with formatted SQL are probably:

  • Define a changelog parameter of ${autoIncrementClause} or something that you can put into your formatted SQL and pass along the needed value
  • Use the DBMS attribute and have separate versions of the changeset for each database you need to support.
  • Find a way of specifying that it’s compatible with the specific databases you’re using. Sometimes there are some overlapping compatibility syntax

Beyond those, one of the big advantages of XML/YML/JSON changelogs is that we allow them to say “make this column autoincrement” and we figure out the database-specific SQL that can handle that.