Which one is recommended - multiple createTable commands in a single changeset or each createTable in each changeset inside an init.xml file


I saw doc saying one change per changeset to make transaction atomic. Also saw many examples having multiple createTable commands in single changeset. Can someone let me know which one is recommended & why?

ps: also createTable is a ddl command too (as I understand) - so please include the perspective regarding auto commit here as a second question.

Many Thanks!

The general best practice is one DDL per changeset. This avoids having to explain the different transaction control mechanisms between tall of the different DBMS platforms. For example:

Oracle does NOT have transactional DDL, but Postgres does.

Therefore, you must only put one DDL per changeset in Oracle, but technically you could put multiple DDL per changeset for Postges, without any issues.

1 Like

Thanks @daryldoak . Is Postgres always do transactional DDL or is it something we need to configure and ensure?

Also, one more thing is. I understand the createTable command scenario now. what about the addColumn command, should that also be added each in each changeset (to be compatible for both oracle and postgres) ?

Postgres always has transactional DDL, Oracle never does.

addColumn turns into an ALTER TABLE sql, so same would apply.

Oracle: non-transactional
Postgres: transactional

I would recommend, always one changeset per DDL (structure change), but DML (data updates) can have multiple per changeset.

1 Like

Thanks a lot @daryldoak for the answers!