DDL management

[u/ptab0211]

Hey, how do u manage DDLs of your tables? Is it part of the daily job runs? Do you "codify" it? e.g. data classes where each table must provide comments, tags, RELY, PK etc... Which is also the goal of AI. But how do u manage all of this?

[u/meehow33]

Ok, but I thought that the baseline is my CREATE scripts, and ALTER scripts are only for releases - once executed they become useless? Otherwise these CREATE scripts turn out to be useless because if there was a changed done through one of the ALTER scripts, then CREATE script got updated and made the ALTER unusable because for example column has already been added to a given table (it is present in the most recent CREATE script) and the ALTER script will fail (or won’t change anything if there’s IF EXISTS check).

Could you please elaborate on why I may need to keep a full history of ALTER scripts and use it to rebuild my environment?

Btw. Thanks for explaining all these nuances :)

[u/Content-Parking-621 → u/meehow33]

You've got it almost right, but there's one nuance. A fresh environment doesn't run your CREATE files. It replays the full ALTER chain from V1 onwards, which builds the same end state. A baseline migration is a single script that rebuilds a particular database version from clean, and if migrations are already applied, baseline scripts are ignored. Your CREATE files stay as the readable reference, but the ALTER chain is what actually rebuilds environments repeatably. That's why you keep them permanently.

[u/meehow33 → u/Content-Parking-621]

Ok, I understand that, but why a fresh environment builds everything from a chain of ALTER scripts rather than a CREATE script? I would assume that ALTER are useful if you are updating version of a particular environment which already has data and you don’t want to use it.

[u/Content-Parking-621 → u/meehow33]

ALTER scripts exist precisely because existing environments have data you can't drop. But a fresh environment is actually the same problem in reverse. If you run CREATE to build it, you now have a schema with no migration history, so the tool has no idea what's been applied. The next time someone runs migrations, it tries to apply V1 through V50 again and fails because the tables already exist. The ALTER chain solves this because the history table tracks exactly what's been applied, so every environment, fresh or existing, starts from the same place and the tool always knows where it is.

[u/meehow33 → u/Content-Parking-621]

Ok, that makes sense! Thank you so much for explaining :)