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

Ask HN: How do you organize and manage database migrations?

news.ycombinator.com

Post reply on HN
Print view View original

41–50 of 61 posts

Re: Ask HN: How do you organize and manage database migrations?

#41

Post by NicoJuicy »

Entity Framework in .Net is very easy.

---

The logic for migrations is suprisingly simple:

- A new migration will normally create a file with an Up or Down method and a "timestamp" + "description of the purpose of the migration" ( eg. 20191031_080603532_add_tags_to_contacts.cs )

- Every migration, when run on the database will create a new entry in eg. the _migrations table . Some will store the PreviousContext which is a hash of the CurrentScheme before the migrations.

If the application has a live check on startup. It will check the hash of the current db schema, compare it to an entry in the database.

Check if that compares to the latest.

The latest is the latest "migration file with up or down". It will execute all newer once according to the timestamp. Untill the latest one is executed.

A tool also contains a "TargetMigration" for development purpose or for reverting a database back to a previous state.

Every Up or Down migration is run inside a transaction. If it fails, nothing is stored.

There is also a way normally to do a Seed of the database, after the migration is run. But this could be added in the Up or Down method.

PS. All other methods untill now were bad.

Re: Ask HN: How do you organize and manage database migrations?

#42

Post by rodelrod »

BjoernKW wrote: post #37

Flyway is free open source software (licensed under the Apache License 2.0). There are pro and enterprise licenses in case you have additional requirements. These licenses as I understand it are proprietary and hence non-free but the the community edition absolutely is free software. I’ve been using the community edition in multiple projects for several years now and it works great, particularly in the context of Spr…

With flyway you need to pay for a license to get down migrations. You can always write the down migrations anyway and apply manually when needed.

Re: Ask HN: How do you organize and manage database migrations?

#43

Post by sourcesmith »

BjoernKW wrote: post #37

Flyway is free open source software (licensed under the Apache License 2.0). There are pro and enterprise licenses in case you have additional requirements. These licenses as I understand it are proprietary and hence non-free but the the community edition absolutely is free software. I’ve been using the community edition in multiple projects for several years now and it works great, particularly in the context of Spr…

Been using Flyway for years with no problems and definitely open source, having contributed documentation changes in the past.

Repeatable migrations are great especially when combined with upserts in Postgres, if you keep config in your database as well.

Re: Ask HN: How do you organize and manage database migrations?

#45

Post by Foober223 »

I use good old fashioned numbered SQL scripts. 01_foo.sql 02_bar.sql 03_baz.sql

In my local dev environment, I have a DB backup representing the current state of production. This is the starting point SQL scripts must be applied to. A deployment script restores this DB, then applies the SQL scripts against it. The entire deployment process takes about 2 seconds even with many gigs of data in the DB backup.

Sometimes I'll include an extra "dummy data" script applied at the end.

My local environment, Test, and Production are deployed in almost exactly the same way. A few extra actions are done for Production of course. And Production is not restored as that would loose data. Local dev is optimized for a speedy restore/deploy.

Developers always want to treat the DB as if it's code. But it's not. It's a blob of valuable state that must be protected from corruption. You apply patches to that blob of state. The order patches occur matters. There's no "re-build" like in pure code.

Re: Ask HN: How do you organize and manage database migrations?

#46

Post by kerryritter »

NicoJuicy wrote: post #40
t0mbstone wrote: post #4

Honestly? I don't know of any tool that handles database migrations as well or as easily as Ruby on Rails and ActiveRecord. I have literally spun up a barebones Rails app just to manage my schema and migrations for a completely separate Python/PostgreSQL project before. https://guides.rubyonrails.org/v5.2/active_record_migrations...

Ever tried EntityFramework in .Net ? It's amazingly easy and there are 2 ways: - Database-First - Code-First

+1 for code-first migrations. It's a bit of a learning curve, but the effort is worth it imo.

Re: Ask HN: How do you organize and manage database migrations?

#48

Post by mmusc »

kerryritter wrote: post #46
NicoJuicy wrote: post #40

Earlier quoted context omitted.

Ever tried EntityFramework in .Net ? It's amazingly easy and there are 2 ways: - Database-First - Code-First

+1 for code-first migrations. It's a bit of a learning curve, but the effort is worth it imo.

I prefer dB first.

Create an SQL project that defines the schema. It automatically compares the schema with your current dB schema and auto generates a migration script for you.

Then update entities from the dB.

Few steps involved but avoids writing and maintaing migrations yourself.

Re: Ask HN: How do you organize and manage database migrations?

#50

Post by thunderbong »

I've found MyBatis Migrations [0], to be very useful. I like the fact that you can do a DB dump of your current database state and set that as the 0th level of migration and then continue from there even if you've never done migrations before.

It's also extremely straightforward and simple as it doesn't try to be part of any ORM.

Highly recommended.

[0]: http://mybatis.org/migrations/

Post reply on HN