Ask HN: How do you organize and manage database migrations? • Hacker News Board

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

#51

Post by breakingcups » Thu, Oct 31, 2019, 3:22 PM UTC

mmusc wrote: post #48

kerryritter wrote: post #46

Earlier quoted context omitted.

+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.

Code-first does this too, but based on a schema you define in your code and not in your DB.

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

#52

Post by bkq » Thu, Oct 31, 2019, 3:32 PM UTC

I've developed a simple migration tool called mgrt [1] to handle database migrations. It takes plain SQL files, and runs them in order, keeping a hash of the contents of the file in the database to ensure that it cannot be modified once run for the first time. I've posted about it here once before [2]. Check it out if you like.

Right now it only supports SQLite, MySQL, and PostgreSQL.

[1] - https://github.com/andrewpillar/mgrt

[2] - https://news.ycombinator.com/item?id=19517001

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

#53

Post by neurostimulant » Thu, Oct 31, 2019, 4:51 PM UTC

ggregoire wrote: post #13

We built it ourselves. * We have migrations written in SQL files named like 45_add_column_x_to_view_y.sql * We track the migrations in git and we do merge requests and reviews * We include a comment in each migration containing the original DDL of the object (1. it makes it easier for the reviewer to see what's changing, 2. it gives us a way to rollback, though it never happened so far) * We track the applied migrati…

How the system handles rolling back a migration?

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

#54

Post by neurostimulant » Thu, Oct 31, 2019, 5:14 PM UTC

Django is my favorite backend because its database migration tool is easy to use and can be customized to fit my needs. That being said, if you want database-first schema migration, the only way to do that (that I aware of) is by attempting to generate models for your existing schema using Django's inspectdb command, then run a fake migration.

I like how Wordpress does simple database-first migration: just call `dbDelta()` function with your new table schema in sql format (CREATE TABLE ...) and it'll figure out a way to update your table to fit your new schema. I wonder if there is any similar db migration tool in python that work like this, seem perfect for small projects that don't want to use an ORM.

https://codex.wordpress.org/Creating_Tables_with_Plugins#Cre...

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

#55

Post by WilliamMayor » Thu, Oct 31, 2019, 5:35 PM UTC

diminish wrote: post #20

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...

I second this - migrations in Rails are fantastic. Even if you aren't using Rails you can get the styles and source code here https://edgeguides.rubyonrails.org/active_record_migrations.... Large open source projects such as GitLab source code are also treasures to discover migration rules. If you have a millions of users with high traffic tables you may want to check some migration guides for downtime, data migratio…

That gitlab link was a good read, thanks!

One of the points was that migrations should always be reversible. I’ve struggled with this in the past when it comes to migrations that change or drop data. How do you write a downgrade script that recalls the previous data?

I’ve given up with downgrades now. I make sure to take a dB backup or snapshot before running an upgrade. I’ve never had to test this solution in a tense situation though...

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

#56

Post by stephenr » Thu, Oct 31, 2019, 7:47 PM UTC

Like half the people commenting here, I wrote my own (https://bitbucket.org/koalephant/mallard/) in shell, after using another similar tool that’s written in python (and contributing some fixes to it).

Same general concept as most others use, apply sql files by date, track what’s applied in the db itself, deploy the sql files with your code.

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

#57

Post by basetensucks » Thu, Oct 31, 2019, 9:18 PM UTC

psankar wrote: post #17

I have used sqitch http://sqitch.org/ in the past and found it good for postgres. It supports revert and verify too.

+1 for Sqitch. If you include a full deploy -> verify -> revert -> deploy flow in your automated testing suite you can pretty safely introduce changes that can be rolled back without issue. This has served my team well on several complicated databases over the past two years.

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

#58

Post by diminish » Fri, Nov 01, 2019, 5:36 AM UTC

WilliamMayor wrote: post #55

diminish wrote: post #20

Earlier quoted context omitted.

I second this - migrations in Rails are fantastic. Even if you aren't using Rails you can get the styles and source code here https://edgeguides.rubyonrails.org/active_record_migrations.... Large open source projects such as GitLab source code are also treasures to discover migration rules. If you have a millions of users with high traffic tables you may want to check some migration guides for downtime, data migratio…

That gitlab link was a good read, thanks! One of the points was that migrations should always be reversible. I’ve struggled with this in the past when it comes to migrations that change or drop data. How do you write a downgrade script that recalls the previous data? I’ve given up with downgrades now. I make sure to take a dB backup or snapshot before running an upgrade. I’ve never had to test this solution in a tens…

One way is to keep a polymorphic (postgres unloggged) `backups` table with bson/json field to store any table's row. use this before and after migrations and backup and truncate this frequently.

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

#59

Post by photonios » Fri, Nov 01, 2019, 8:55 AM UTC

Our stack heavily uses Django. We are therefor using Django's migration system. I worked with alembic before and some other Python based migration systems, but nothing comes close to the Django's simplicity.

We're heavy Postgres users and take full advantage of some of its features which are not very well supported by Django. Quite a number of our migrations involve `migrations.RunSQL` operations. That's fine. We've also developed a package [1] that extends Django with a lot of Postgres specific features. For example, recently we added support for partitioned tables. We can now set those up as normal Django models.

In order to reduce the number of f'ups, we also built a Github bot that inspects PR's for potentially disastrous migrations. For example, the Github bot will yell at you if you try to rename a field or blindly drop a table. The Github bot also makes sure your migration doesn't conflict with another migration that was just merged.

[1] https://github.com/SectorLabs/django-postgres-extra

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

#60

Post by alexfromapex » Fri, Nov 01, 2019, 1:19 PM UTC

Having dealt with this before, if I could go back I would integrate an ORM to do the schema changes. Something like SQLAlchemy/Alembic where you can store your schema changes like git commits and roll forward/back as needed.