4 ms·

I've read and reread a great article titled "Evolutionary Database Design" on Martin Fowler's web-site [0]. This article describes database migrations as being

by smoyer 7y ago

I've read and reread a great article titled "Evolutionary Database Design" on Martin Fowler's web-site [0]. This article describes database migrations as being a process. We've found that for complex changes, we'll often need a pre-migration and a post-migration (temporally being before the code change and after the code change respectively). We commit the migrations along-side the application code and in our case we use FlywayDB [1]. The only down-side is that this tool doesn't perform roll-back operations automatically. You can always do them by writing another migration that goes forward to the past. Another popular DB migration tool is Liquibase [2]. I don't have much experience with this tool as it doesn't fit our build pipe-line as well but it does support and encourage defining a roll-back for each migration. [0] https://www.martinfowler.com/articles/evodb.html https://www.martinfowler.com/articles/evodb.html [1] https://flywaydb.org/ https://flywaydb.org/ [2] http://www.liquibase.org/ http://www.liquibase.org/ EDIT: HN is the new StackOverflow? I think this is a really important question for development teams and yet I could see it being closed due to the "Questions asking for tool or library recommendations are off-topic for Stack Overflow ..." rule. Sad!

freedomben 7y ago
> HN is the new StackOverflow? Could you clarify what you mean by this?
- smoyer 7y ago
  This is actually a good place to ask important questions that will be closed by the moderators there.
  - barrkel 7y ago
    In fact these are the only questions I'm really interested in asking of other developers any more, and most of the reason why I almost never ask or reply on SO, despite 37k rep. It was looser in the earlier days, but I guess moderators wanted easier to evaluate rules, and it's easier for moderators to decide to come down on the side of moderators than people who ask questions and start discussions.
    - scarejunba 7y ago
      Makes sense, though, right? They want a Q&A site where you can get definitive answers. This sort of discussion-oriented thing is better suited to a forum with threaded replies and whatnot.
      - barrkel 7y ago
        I actually think there ought to be levels, where you can access different kinds of conversations at higher rep levels. Or some other way of gating out clueless people. Noise drowns out signal otherwise.
abetlen 7y ago
Thank you, the Fowler article is very helpful. Does your team have someone act as a DBA to oversee each migration?
- smoyer 7y ago
  We differentiate between DBas and DBAs (administrators verses architects). Our team has DBAs who make sure what we're doing is sane and more importantly tune SQL emitted by ORMs as needed.
mschaef 7y ago
> We commit the migrations along-side the application code and in our case we use FlywayDB [1]. The only down-side is that this tool doesn't perform roll-back operations automatically. You can always do them by writing another migration that goes forward to the past. I've done that too, and agree re: rollbacks... I've also never been a huge fan of the fact that the final, in-production server isn't ever really reified as a source file. What you have instead is the initial state and a bunch of migration files. It works, and it's the most reflective way of how the final schema got to that state, but it's not the final state. I guess that's the trade off of the relatively unsophisticated approach taken by tools like Flyway.
elken 7y ago
I'm a fan of liquibase. It's a very mature tool which allows you to run it every time the application starts, useful for development to create in-memory dbs. Or you can just call it as part of the build process if you don't want to check the DB changelog tables on startup. It tracks changes that have been applied in changelog tables so you don't apply them multiple times. Common operations such as adding a column are defined in a supported markup language but for more complicated things, such as migrating data, you can reference ad-hoc SQL files. All of which can be checked-in to your codebase. The only real downside is because you are applying the entire development history of the database, you can sometimes be caught doing illogical things like adding and then removing something later on. This can be mitigated by rewriting history if needed though. Flyway was not as good for me because it lacked a domain language, raw SQL makes it less easy to interpret but I know developers who preferred that.

by smoyer 7y ago

freedomben 7y ago

> HN is the new StackOverflow? Could you clarify what you mean by this?

smoyer 7y ago
This is actually a good place to ask important questions that will be closed by the moderators there.
- barrkel 7y ago
  In fact these are the only questions I'm really interested in asking of other developers any more, and most of the reason why I almost never ask or reply on SO, despite 37k rep. It was looser in the earlier days, but I guess moderators wanted easier to evaluate rules, and it's easier for moderators to decide to come down on the side of moderators than people who ask questions and start discussions.
  - scarejunba 7y ago
    Makes sense, though, right? They want a Q&A site where you can get definitive answers. This sort of discussion-oriented thing is better suited to a forum with threaded replies and whatnot.
    - barrkel 7y ago
      I actually think there ought to be levels, where you can access different kinds of conversations at higher rep levels. Or some other way of gating out clueless people. Noise drowns out signal otherwise.

abetlen 7y ago

Thank you, the Fowler article is very helpful. Does your team have someone act as a DBA to oversee each migration?

smoyer 7y ago
We differentiate between DBas and DBAs (administrators verses architects). Our team has DBAs who make sure what we're doing is sane and more importantly tune SQL emitted by ORMs as needed.

mschaef 7y ago

> We commit the migrations along-side the application code and in our case we use FlywayDB [1]. The only down-side is that this tool doesn't perform roll-back operations automatically. You can always do them by writing another migration that goes forward to the past. I've done that too, and agree re: rollbacks... I've also never been a huge fan of the fact that the final, in-production server isn't ever really reified as a source file. What you have instead is the initial state and a bunch of migration files. It works, and it's the most reflective way of how the final schema got to that state, but it's not the final state. I guess that's the trade off of the relatively unsophisticated approach taken by tools like Flyway.

elken 7y ago

I'm a fan of liquibase. It's a very mature tool which allows you to run it every time the application starts, useful for development to create in-memory dbs. Or you can just call it as part of the build process if you don't want to check the DB changelog tables on startup. It tracks changes that have been applied in changelog tables so you don't apply them multiple times. Common operations such as adding a column are defined in a supported markup language but for more complicated things, such as migrating data, you can reference ad-hoc SQL files. All of which can be checked-in to your codebase. The only real downside is because you are applying the entire development history of the database, you can sometimes be caught doing illogical things like adding and then removing something later on. This can be mitigated by rewriting history if needed though. Flyway was not as good for me because it lacked a domain language, raw SQL makes it less easy to interpret but I know developers who preferred that.