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

141–150 of 150 posts

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

#141

Post by theclaw »

keithnz wrote: post #99
theclaw wrote: post #80

We use dbup[0]. Its philosophy[1] is that you should treat your DB changes like code changes in source control, and only perform up migrations. I agree with this. We previously spent a lot of time and effort writing down migrations that were never, ever used. If you need a down migration, take a backup before running your up migrations. We're a C# shop. Our DB migration scripts are simply named with a datestamp and a…

a lot of time writing down migrations? why was it so hard? I use https://fluentmigrator.github.io/ and also used dbup.. I generally like the up/down better, and both the up and down is remarkably trivial to write generally, the downs are useful during development you might change your mind about the DB structure. Never used a down in production.

We used to do this but found it was error prone since the down migrations would have bugs and wouldn’t always catch everything. We have dedicated SQL devs that write the migrations by hand and so moving to generated code is a slow and painful process. We now encourage developers working on the DB to thrash out their design in a local SQL Server Express instance before baking their changes into a migration script that will be automatically run into our dev env by octopus. If the scripts don’t run or the tests against the temporary DB fail during build then they never get to our shared dev DB.

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

#142

Post by elken »

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…

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.

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

#143

Post by tluyben2 »

We have been doing migrations with our internal ORM for many years now; first it was written in Perl, then ported to Java, later ported to PHP, after that ported to C# (past ~10 years) and it does up/down migrations with safe guards in place. It (the underlying algorithm/heuristics) has been working fine for around 20 years on around 1000 projects (client consultancy mostly) in that time. Lessons learned (opinionated/IMHO, no sweeping wisdom attempt); a) database changes should be in code so there is no discrepancy between code + db b) your tooling (in this case our ORM) should figure out how to get from the current state to the new state; it should not be a manual job (because you will make mistakes) c) migrations including rollback should be fully automated (we only had issues when people tried to do clever things manually) d) have common sense safe guards in place; do not try to be too clever to save a few MB's of disk space; for instance, deploy will fail if you attempt to remove a column; you can only do that manually.

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

#144

Post by GordonS »

Using DbUp, which is basically a library that you use to wrap SQL scripts in a console app.

This gives you full control over your migrations, while still being incredibly simple - it's just SQL!

For views, sprocs and functions, we don't use sequential migration scripts, instead opting for idempotent creation. This also means it's easy to see the evolution of views etc when looking in source control.

In the past I've used Entity Framework and Entity Framework Core migrations, and hated them both - I'll never be a fan of codegen, but aside from that they sometimes generated wrong code that had to manually adjusted, and you also quickly end up with hundreds of scripts.

I like DbUp very much.

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

#145

Post by djrobstep »

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…

Strong agree. You can do much better than than Rails/Django migrations.

I have been advocating for this approach for a while now: https://djrobstep.com/talks/your-migrations-are-bad-and-you-...

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

#146

Post by hestefisk »

mattmanser wrote: post #139
hestefisk wrote: post #133

I hand code my migrations for Postgres. When starting a project a create a base.sql file. Then for first database change I create db/000001- .sql, db/00002 [...] and so forth. When I want to create a new database I just run psql<base.sql followed by psql<db/*.sql. Very simple but extremely effective. I don’t think any tool can handle migrations better than hand coded sql. Sometimes I use PL/PgSQL to handle procedural…

If you think any tool can't handle this better than you are woefully misinformed. I was doing this method back in 2006, let me assure you that the automated tools available today make this look like amateur hour. Automatic schema transactions, automatic up/down scripts, automatic detection that a schema change hasn't been committed. Automatic change tracking table in the db. Ability to seed a database with test data.…

EF Migrations works pretty well if you use the database as a dumb object store. If you need anything remotely advanced than that, eg functions, triggers, custom types or materialised views, then it doesn’t gel at all. Raw sql is the only way to go. To me, the database is an API, not just s dry store for OO.

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

#147

Post by quickthrower2 »

Previous shop we used entity framework migrations in .net. So migrations are applied by octopus deploy.

This place we do it manually but because of how the app is architected migrations are rare. If we migrate we try to make the code compatible with before and after schemes.

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

#148

Post by kd5bjo »

liyanchang wrote: post #103
PaulMest wrote: post #96

Earlier quoted context omitted.

This was helpful to think about, thanks. I've rarely encountered logical merge conflicts with migrations, but I could see it happening. I used to be on the SQL Server team at Microsoft and had some exposure to the customer support teams. So data integrity and eliminating any potential for errors was huge. So while I love the idea of migrations being generated on the fly from actual state in Production-System-5 to des…

Yeah. I’d love to see the academic paper with formalizations that help me understand the true scope of this problem. Your example is a great one that prompts many questions. Is it possible to travel directly to the commit o(1) or will the code have to calculate the diff of each commit and apply them one at a time o(n) and how much definition and dependency mapping humans need to do to have it work correctly?

The closest I can think of is trying to define a set of CRDT-compatible operations that are expressive enough to describe your database schema, starting from an empty database. Then, the migration you need to perform is whatever the CRDT merge operator says you need to do.

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

#149

Post by atsaloli »

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 is just how the industry started managing server configurations (Puppet) ...

Yes, and CFEngine pioneered this in 1993 (Mark Burgess).

I make my living as a CFEngine consultant.

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

#150

Post by keithnz »

theclaw wrote: post #141
keithnz wrote: post #99

Earlier quoted context omitted.

a lot of time writing down migrations? why was it so hard? I use https://fluentmigrator.github.io/ and also used dbup.. I generally like the up/down better, and both the up and down is remarkably trivial to write generally, the downs are useful during development you might change your mind about the DB structure. Never used a down in production.

We used to do this but found it was error prone since the down migrations would have bugs and wouldn’t always catch everything. We have dedicated SQL devs that write the migrations by hand and so moving to generated code is a slow and painful process. We now encourage developers working on the DB to thrash out their design in a local SQL Server Express instance before baking their changes into a migration script that…

we never have problems with it. It's all local, we don't have a shared dev DB, I think that's asking for trouble.

We have test stacks, and a production stack. Those DBS are TBs in size. Locally we have a conditioned DB thats much smaller. The rule is locally, you only change your DB via migration.

Post reply on HN