I don't like migrations, it's like doing version control with dated copies of files.
I wish there were (perhaps I'll be told there is) a migration system that used a single file per table, and was git-aware so that when the live schema didn't match the checked out schema file, it would determine a migration from the historical version that it does match.
Alas, I don't think it's possible, because such a file would describe a state, not, as migration files do, transitions between states. It's tempting to imagine that it's possible to robustly infer transitions from one to another, but I've used Django.
While I think what you want is understandable - it's also extremely dangerous. You'd no longer be absolutely certain you're executing the same migration in production as on the testing environments.
That's exactly why I think Django's migrations are insanely good.
Simple ones are autogenerated and everything else is possible as well, with either python code or direct sql queries
- Liquibase expects the scemea to be modeled in its schema language. This makes it possible to compute changes between the database and the new schema and apply those changes, or to just create old-fashioned migration scripts.
- Th. Doctrine and Hibernate ORMs can do similar things.
- Flyway expects developers to write migrations by themselves, either in Java or in SQL, and just executes them and records state information. It's not too bad to write migrations yourself because the data you work with is usually valuable.
Yes, they can do similar things. However, there are several cases which you cannot handle automatically, due to incomplete information:
- The current schema has a column A and the new version does not have A but a similar column B. Is this a new column? Or should A be renamed into B? (same for renaming tables)
- You add a new table A which contains columns from B, which are also removed from B. Should this data be copied?
- You change the structure of a table, and have some of it split off into different tables. How does the schema tool know how to rearrange the current data?
There are probably some more cases, but these are the ones I could think of quickly.
This is not necessarily true. It depends on your environment. You can track changes to db with the right tools.
Rest of your points I completely agree. Don't put unnecessary complexity in your db. It's a database, not a code repo.