Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ask HN: How does your development team handle database migrations?

news.ycombinator.com

Post reply on HN
Print view View original

41–50 of 150 posts

Re: Ask HN: How does your development team handle database migrations?

#41

Post by liyanchang »

evanelias wrote: post #37
liyanchang wrote: post #30

I've been really happy with how my current company[0] has been doing migrations and I've seen a couple others do it but it seems like it should be more widespread. Database Schema as Code Instead of writing up and down migrations, you define what the end state should look like. Then the computer will figure out how to get here. This is just how the industry started managing server configurations (Puppet) and infrastr…

I call this declarative schema management, since the repo declares the desired state, and the tooling knows how to reach this state. This concept is finally catching on lately, although some huge companies have already been doing it this way for quite some time. Facebook is a key example; they've managed their schema changes in a pure-SQL declarative fashion, company-wide, for nearly a decade. I'm developing a suite…

This is good to know. As someone who didn't do much with databases before, I was frankly worried given how it didn't seem like many others were taking this approach when it made so much sense (we did have the advantage of having a defined schema which I know isn't always available). Seems like I just didn't know what to search for.

Git would never have worked it required devs to write the up/down patches - why should we have to write the up/down migrations for my schema?

Excited to see more tooling around declarative schema!

Re: Ask HN: How does your development team handle database migrations?

#42

Post by sebazzz »

aerojoe23 wrote: post #32

.net has entity framework an ORM has has migrations. It isn't problem free, but we found workable solutions to our issues. By default EF will throw an error if the model (in code) and the database are out of sync. There is a setting you can set to tell the ef not to care about the version, but then you have to take the responsibility of making sure the old code will run against the new db version. Most of our stuff i…

Yes, we have used that for a bit and for the most part this works fine. We had however a case where several migrations were not applied in production, and that is a mess to figure out.

But the development experience, especially at the start of the project (if you don't maintain your seeding code), is awesome. You just point the project at an empty or non-existing database, the schema is automatically created, and any necessary base data can automatically be seeded.

Re: Ask HN: How does your development team handle database migrations?

#43

Post by atwebb »

louthy wrote: post #12

We use the RedGate SQL compare tools [1] to compare our new schema to our old one and auto-apply the diffs to the production DB (this is done automatically by our deployment process). To reduce the chance of error we don’t destroy columns or tables. Our application then has an update step which runs on startup for any data migrations (or new data additions), and then updates a version number stored in the DB. The dat…

I've got a fair amount of experience with it and the Change Automation tool they have. Both are fantastic products, the latter for certain scenarios over SSDT itself. Sounds like have had it sorted for a while, my main concerns are when it wants to rebuild certain tables when a simple sp_rename or other step would be sufficient.

Re: Ask HN: How does your development team handle database migrations?

#44

Post by smoyer »

freedomben wrote: post #35
smoyer wrote: post #4

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…

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

This is actually a good place to ask important questions that will be closed by the moderators there.

Re: Ask HN: How does your development team handle database migrations?

#46

Post by [deleted] »

mchristen wrote: post #33
jolmg wrote: post #22

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…

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

[deleted]

Re: Ask HN: How does your development team handle database migrations?

#47

Post by daoxid »

liyanchang wrote: post #30

I've been really happy with how my current company[0] has been doing migrations and I've seen a couple others do it but it seems like it should be more widespread. Database Schema as Code Instead of writing up and down migrations, you define what the end state should look like. Then the computer will figure out how to get here. This is just how the industry started managing server configurations (Puppet) and infrastr…

This sounds nice! One Question: You said that DML changes are handled via "standard check in sql file". Does this simply mean a new SQL file for each migration? And how are DML changes connected to DDL changes? For example, if some code is two versions behind and updated to the current schema, wouldn't this mean that the DDL is updated in one step to the current state, but the DML potentially in two steps, breaking the update?

Re: Ask HN: How does your development team handle database migrations?

#48

Post by meowface »

evanelias wrote: post #37
liyanchang wrote: post #30

I've been really happy with how my current company[0] has been doing migrations and I've seen a couple others do it but it seems like it should be more widespread. Database Schema as Code Instead of writing up and down migrations, you define what the end state should look like. Then the computer will figure out how to get here. This is just how the industry started managing server configurations (Puppet) and infrastr…

I call this declarative schema management, since the repo declares the desired state, and the tooling knows how to reach this state. This concept is finally catching on lately, although some huge companies have already been doing it this way for quite some time. Facebook is a key example; they've managed their schema changes in a pure-SQL declarative fashion, company-wide, for nearly a decade. I'm developing a suite…

I'm surprised this isn't more of a thing. It seems like the natural evolution of "[X] as code". I've always been a little turned off by migrations (though they were certainly an improvement over the previous situation, which was basically just indeterministic changes on the fly).

Re: Ask HN: How does your development team handle database migrations?

#49

Post by Myrmornis »

Django. Django generates the schema migrations automatically from the changed table schema definitions that the developer used when testing their branch, and those rarely cause problems. Data migrations need to be tested against staging DBs with realistic data. But neither is really a major pain point: individual developers create and commit the migration files while preparing their branches for review.

Re: Ask HN: How does your development team handle database migrations?

#50

Post by ozim »

aerojoe23 wrote: post #32

.net has entity framework an ORM has has migrations. It isn't problem free, but we found workable solutions to our issues. By default EF will throw an error if the model (in code) and the database are out of sync. There is a setting you can set to tell the ef not to care about the version, but then you have to take the responsibility of making sure the old code will run against the new db version. Most of our stuff i…

But that is most important part of migrations to throw that error so you are aware of it. First thing you fix your db and then push to test server, and you never have broken db on production, NEVER.
Post reply on HN