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

21–30 of 61 posts

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

#21

Post by uranusjr »

ggregoire wrote: post #16
diminoten wrote: post #15

Earlier quoted context omitted.

Which existing technologies did you look at and why didn't they fit your needs?

We looked at Alembic and our main issues were that 1. we don't have the entire database modeled into SQLAlchemy and we don't plan to 2. we have A LOT of views, triggers and functions and they are not very well supported by SQLAlchemy/Alembic (? I may be wrong)

Point 1 is not an issue IMO, you definitely don’t need to model anything schema-wise into SQLAlchemy ORM to make Alembic useful, since Alembic operations translate pretty much directly to SQL. I’ve just built a project last month that uses Alembic but otherwise connects to the database and issues SQL directly.

Point 2 is indeed a big factor though. Alembic doesn’t have much first-class operation support beyond data storage constructs, and if most you need is op.execute() (which runs plain SQL) one might just as well build their own tool like you did.

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

#22

Post by tbrock »

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

Why do you think no other toolchain has come close to this for other languages or frameworks? It’s a very common problems that needs a solution no matter what you are building and what you are building it on.

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

#23

Post by nilram »

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…

We have something very similar, written in Java. Our extensions specify whether the migration is run pre-release or post-release, in order to facilitate no-downtime releases. The pre-release migration is responsible to not break the running code. The released code is responsible for detecting (if necessary), the pre- or post-release state and behaving appropriately.

Sequence is to run pre-release migrations, deploy the release, then run post-release migrations. Sometimes days apart, in case things need to be stabilized. Testing environment runs only pre-release migrations, until some time before the release, then runs post.

We also have a handful of keywords to run operations in ways that don't lock large, frequently-used tables, or do other environment- or application-specific operations in standard ways.

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

#25

Post by keshab »

We use flyway for the biggest monolith database in our company. We also use alembic for some microservices.

Alembic is extra convenient if you're already invested in sqlalchemy.

I've tried yoyo-migrations[1] once, a while ago.

With yoyo, like flyway, migrations are written as SQL queries. However, they're expressed as SQL steps in python files. Might worth a look if you're using python.

1. https://pypi.org/project/yoyo-migrations/

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

#26

Post by notatoad »

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…

This is almost exactly what i built at my company. It felt like a dirty hack at the time, and i assumed we'd transition to something "real" over time, but it's been working flawlessly for quite a few years now and i've yet to see any reason to use something different.

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

#27

Post by mkolodny »

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

Django's migration handling is excellent as well [0].

[0] https://docs.djangoproject.com/en/2.2/topics/migrations/

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

#28

Post by lstamour »

evanelias wrote: post #18

I'm the author of an open-source schema management tool, Skeema [1], which uses a declarative infrastructure-as-code approach: you track your table definitions (and procs, funcs, etc) in a Git repo of *.sql files, typically one CREATE statement per file. Instead of writing migrations, developers simply add / remove / modify these CREATE statements, going through the same pull request and code review process as for co…

I was going to ask after rename support, but lack of it is documented over here along with a couple other things. https://github.com/skeema/skeema/blob/master/doc/requirement...

Interesting ideas though. Microsoft does something similar with a DACPAC where they record the schema and diff, but they also record a list of rename actions etc. and keep a table to track those “migrations” in addition to the diff process. https://docs.microsoft.com/en-us/sql/relational-databases/da... and/or https://docs.microsoft.com/en-us/sql/ssdt/extract-publish-an... — for rename and other SSDT details, see https://docs.google.com/presentation/d/1DvC2gzCucjHFbGiBLa0R... (it’s a “RefactorLog” if searching)

Having seen all that, the Rails ActiveRecord approach strikes a decent balance between specify migrations as imperative actions and keeping a declarative schema checked in. A comparison between the two approaches is at https://blog.raph.ws/2019/01/migrations-vs-dacpacs/ but I find DACPAC to be useful but over-complicated for developers and not well-enough supported on non-windows hosts yet. (It basically requires VS for Windows or SSDT right now...)

Also, it’s likely a migration system needs some kind of code review checks and automation around which actions you’d allow in your migrations if you don’t have that already...

The next question after “how do you migrate?” is probably “how do you backup and how long would it take to restore after a bad migration and/or bad rollback?” These days your answer is probably either “no time at all” for smaller apps with SSDs or “it’s all outsourced to the cloud,” for the new cloud distributed data stores or for the smart DIY ones, “we only deploy one server at a time and can either afford the downtime or have copies in multiple AZs, perhaps eventual consistency...”

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

#30

Post by perlgeek »

We have three applications that access the same database (ugly, we're working on changing that), so we have a fourth component that's solely responsible for the schema.

We use CI/CD for all components, including for the schema. The deployment playbook for the schema basically does a `flyway migrate` after installing the schema / migration files.

We have (again, historical reasons) a home-built schema diff tool for our subset of mysql that we use. For Postgres I'd look into apgdiff. We use the diff tool to generate initial versions of the migrations, potentially modify them by hand, and then `git add` them in the schema project.

If you don't like flyway, you could check out https://sqitch.org/ which is fully Open Source.

Post reply on HN