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

* 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 migrations in a table migrations looking like "2019-10-30T10:49:00" ! "45_add_column_x_to_view_y.sql"

* We have a CLI in the same repo that allow us to apply the migrations. It's 50 LOC of python that basically 1. gets the DB host/user/pwd from an .env, 2. checks the last applied migration in the table migrations, 3. finds the new migrations to apply, 4. applies them following the order of the ids e.g. 45 before 46, 5. updates the table migrations

* We have 3 DBs (dev / staging / prod) in AWS RDS and I have an .env for each one of them

* We have a CI pipeline on the repo to automatically apply the merged migrations in staging and check if nothing goes wrong

* When the staging or dev is trashed, I delete it and recreate it from a snapshot of the prod (it takes 5 min) https://stackoverflow.com/a/49878477/652669

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

#14

Post by lqqkout4elfy » Thu, Oct 31, 2019, 5:00 AM UTC

I'm currently very much into http://knexjs.org/ since it is just a query builder rather than an ORM. It has just enough abstraction to allow it to be DB agnostic.

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

#15

Post by diminoten » Thu, Oct 31, 2019, 5:01 AM 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…

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

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

#16

Post by ggregoire » Thu, Oct 31, 2019, 5:14 AM UTC

diminoten wrote: post #15

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…

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)

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

#17

Post by psankar » Thu, Oct 31, 2019, 5:14 AM UTC

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

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

#18

Post by evanelias » Thu, Oct 31, 2019, 5:17 AM UTC

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 code. The tool knows how to diff the desired state (expressed in the repo) vs the actual state of any database environment (prod / stage / dev, etc) to generate the appropriate DDL.

It's a bit of a paradigm shift relative to traditional mysql and postgres migration tools, but it's an approach that has been used successfully by Facebook internally for nearly a decade. It's also a common approach in the SQL Server world. I've written a blog post [2] describing some of the advantages of the declarative approach.

Skeema currently only supports MySQL and MariaDB, but some declarative tools for Postgres include sqldef [3] and migra [4].

[1] https://www.skeema.io

[2] https://www.skeema.io/blog/2019/01/18/declarative/

[3] https://github.com/k0kubun/sqldef/

[4] https://github.com/djrobstep/migra

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

#19

Post by maktouch » Thu, Oct 31, 2019, 5:21 AM UTC

Database migrations for RDBMS like mysql or postgres, we use knex or laravel's database migrations tools.

For bigquery, we wrote this nifty tool https://github.com/SplitmediaLabsLimited/supermigration

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

#20

Post by diminish » Thu, Oct 31, 2019, 5:29 AM UTC

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 migrations and batch processing here https://docs.gitlab.com/ee/development/migration_style_guide...

If you want to have some concurrent ways of DDL operations you may benefit from https://gitlab.com/gitlab-org/gitlab/blob/master/lib%2Fgitla...