3 ms·

Would be cool to have git for databases. "oh no! our migration deleted columns without re-representing that data in the new manner, and our users have already

by jolmg 8y ago

Would be cool to have git for databases. "oh no! our migration deleted columns without re-representing that data in the new manner, and our users have already done changes to the database so we can't simply restore from a backup!" quick! dbgit checkout -b fixed-migration before-original-migration # *run fixed migration* dbgit rebase --onto fixed-migration after-original-migration master day saved! If only it were so simple.

whitepoplar 8y ago
isn't this what datomic does?
ben509 8y ago
The main issue is you can't "reverse migrate" creating a table or adding a column as that deletes prod data. So your base tables are always forward migrated, and probably have only basic constraints. With good updatable view support, you can maintain versions of table views and constraints on the views that may be forward, reverse or laterally migrated. (You'd have a different set of views for each version of the schema in use, so each build of the client is connecting to exactly the schema it expects.) Then you'll want to checkpoint the base tables. So the system must track usage of view-set versions to determine when they can be dropped entirely. Since the constraints are enforced at the view level, you have to deal with legacy data that violates current constraints, but was hidden by the views. Then the system can do a checkpoint and delete obsolete columns and tables, moving forward the earliest version you can roll back to. Of course, that all requires good updateable view support, and I don't think any products offer that.
mchristen 8y ago
You can run/test your migrations in a transaction and roll back if it doesn't work.
- jolmg 8y ago
  Not all problems are obvious, though. You can have an app work 100%, your database be completely coherent, and then realize some of the data is missing. It would be cool to not have to be so careful when committing migrations, needing to be absolutely sure that we're not screwing something up. This is similar to how one would be careful of changes done in source code before we learned how to use version control systems like git. I can go completely wild with git, deleting random files, overwriting others with random junk, sharing them to my coworkers, etc. It would only take a few minutes to fix that.
  - smt88 8y ago
    This is OK for git because updates to a repo happen maybe 100 times a day at the most. A production DB is changed potentially millions of times a day. If you have this goal from the beginning, you can create an append-only database, but that's orthogonal to migrations. To have "append-only" migrations is seemingly out of scope for mainstream database engines.
- deleted 8y ago
  [deleted]
- baq 8y ago
  sometimes, maybe, if you happen to run the right kind of DBMS...
- jyounker 8y ago
  This isn't always true. In MySQL many DDL statements can't be rolled back. See https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.htm... for an example.
awinder 8y ago
That’s basically the proposition of any event sourcing system https://www.martinfowler.com/eaaDev/EventSourcing.html https://www.martinfowler.com/eaaDev/EventSourcing.html but you’re kinda right, that involves a level of code complication (but then it would be so simple as you described to restore / rebase / etc)

by jolmg 8y ago

whitepoplar 8y ago

isn't this what datomic does?

ben509 8y ago

The main issue is you can't "reverse migrate" creating a table or adding a column as that deletes prod data. So your base tables are always forward migrated, and probably have only basic constraints. With good updatable view support, you can maintain versions of table views and constraints on the views that may be forward, reverse or laterally migrated. (You'd have a different set of views for each version of the schema in use, so each build of the client is connecting to exactly the schema it expects.) Then you'll want to checkpoint the base tables. So the system must track usage of view-set versions to determine when they can be dropped entirely. Since the constraints are enforced at the view level, you have to deal with legacy data that violates current constraints, but was hidden by the views. Then the system can do a checkpoint and delete obsolete columns and tables, moving forward the earliest version you can roll back to. Of course, that all requires good updateable view support, and I don't think any products offer that.

mchristen 8y ago

You can run/test your migrations in a transaction and roll back if it doesn't work.

jolmg 8y ago
Not all problems are obvious, though. You can have an app work 100%, your database be completely coherent, and then realize some of the data is missing. It would be cool to not have to be so careful when committing migrations, needing to be absolutely sure that we're not screwing something up. This is similar to how one would be careful of changes done in source code before we learned how to use version control systems like git. I can go completely wild with git, deleting random files, overwriting others with random junk, sharing them to my coworkers, etc. It would only take a few minutes to fix that.
- smt88 8y ago
  This is OK for git because updates to a repo happen maybe 100 times a day at the most. A production DB is changed potentially millions of times a day. If you have this goal from the beginning, you can create an append-only database, but that's orthogonal to migrations. To have "append-only" migrations is seemingly out of scope for mainstream database engines.
deleted 8y ago
[deleted]
baq 8y ago
sometimes, maybe, if you happen to run the right kind of DBMS...
jyounker 8y ago
This isn't always true. In MySQL many DDL statements can't be rolled back. See https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.htm... for an example.

awinder 8y ago

That’s basically the proposition of any event sourcing system https://www.martinfowler.com/eaaDev/EventSourcing.html https://www.martinfowler.com/eaaDev/EventSourcing.html but you’re kinda right, that involves a level of code complication (but then it would be so simple as you described to restore / rebase / etc)