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

131–140 of 150 posts

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

#131

Post by namelosw »

We use fluentMigrator for .Net, but basically the workflow is similar to Rails migration. It is working fine and did not slow us down.

What slows us down and related to this topic is cross-database migrations. We have a bunch of microservices in different codebases. Sometimes we need to adjust bound contexts which move the responsibilities from services to services, we found it's much harder to migrate between different databases behind those services. It's either hard to keep track in the same repo and/or too slow to migrate large volume of data across hosts. In the end we wrote some optimized SQL to do that. Maybe monorepo could help but this doesn't happen too much, and we haven't start trying it yet.

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

#132

Post by Foober223 »

On my team we use numbered sql scripts. We restore a production db on our local boxes. This is the starting point. The numbered scripts are executed against this. So the actual deployment is getting tested out locally all the time. This is dead reliable, and handles tricky data transformation that would fail under a declarative/calculate approach.

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

#133

Post by hestefisk »

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 data migrations, which cannot be solved using normal sql.

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

#134

Post by ec109685 »

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…

How do you know which ones have already been run on the environment you are targeting?

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

#135

Post by tylerrobinson »

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…

Have you experienced any problems with how long it might take to run the whole list of incremental changes?

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

#136

Post by speedplane »

Is this question asking how to move from one schema to another, or one database backend to another? Most answers here focus on schema changes but I'm not sure that was the question.

Schema changes are relatively straightforward, there are plenty of tools that can help. Changing the database backend is a different story altogether. There are so many unknown unknowns when changing databases, that it's generally best to avoid it if you can.

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

#137

Post by hestefisk »

ec109685 wrote: post #134
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…

How do you know which ones have already been run on the environment you are targeting?

I keep a simple table with the latest version run and a time stamp. Then I check in each file whether its version# (in the filename) is less or greater than what’s in the table. It’s easy because I can do it in simple sql.

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

#138

Post by hestefisk »

tylerrobinson wrote: post #135
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…

Have you experienced any problems with how long it might take to run the whole list of incremental changes?

No never. It is as fast as you can pipe data through to psql.

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

#139

Post by mattmanser »

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.

With a single command I can go back to any arbitrary point of changes, switch branch, work on a bug fix, switch back to dev and the next time I fire up the Dev site it'll automatically redeploy the new changes.

Just streets ahead of hand coded scripts. Yes, there's a learning curve, yes you have to pay attention to your design still, but it is so good.

I was skeptical at first, but honestly I'm never going back and I don't miss it. I'm using Entity Framework Migrations, I assume other languages have similar/better tools.

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

#140

Post by theclaw »

brlewis wrote: post #84
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…

>If you need a down migration, take a backup before running your up migrations. Aren't you missing some steps? Or does your db back up schema structure and data separately? 1. Make sure nobody can insert/update/delete anything 2. Take a backup 3. Run your up migration 4. Thoroughly test everything 5. Allow insert/update/delete again

That is our upgrade process, yes.
Post reply on HN