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

#1

Post by anonfunction » Thu, Oct 31, 2019, 3:30 AM UTC

When building services that rely on relational databases, in my case postgres, what are some best practices and tools to help manage schema changes?

We've been using migrations and doing it all manually but it's become a bottleneck and a little bit of a nightmare with multiple consumers of the database needing to make schema changes.

Another concern is multiple environments, from local development to staging and production. We're using docker-compose for local development which runs the entire "full" schema and then I'm manually applying the migration files to staging and production before we deploy.

I've looked at some projects like flywaydb[1] and liquibase[2] but both are not completely free and seem proprietary. Does anyone know of another open source system that could help manage database schema versioning and migrations?

Thanks so much HN, this is something that I have been struggling with.

1: https://flywaydb.org/

2: https://www.liquibase.org/

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

#2

Post by sandGorgon » Thu, Oct 31, 2019, 4:16 AM UTC

We have built projects where we used python Alembic or Ruby's Activerecord migrations to manage the db.

The actual application was in Go or nodejs.

I daresay that Activerecord and Alembic outclass Liquibase in a lot of things.

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

#3

Post by rlancer » Thu, Oct 31, 2019, 4:18 AM UTC

https://djrobstep.com/docs/migra

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

#4

Post by t0mbstone » Thu, Oct 31, 2019, 4:19 AM UTC

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

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

#5

Post by mfrye0 » Thu, Oct 31, 2019, 4:27 AM UTC

I've been using Dbmate for a few years now across our entire stack. It does the job perfectly.

https://github.com/amacneil/dbmate

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

#6

Post by arshbot » Thu, Oct 31, 2019, 4:29 AM UTC

Honestly, build it yourself. A good library of small shell scripts along with some well-commented .sql files will be easier to maintain and understand than any off the shelf migration utility.

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

#7

Post by y4mi » Thu, Oct 31, 2019, 4:31 AM UTC

Liquibase is open source.

You only get support and templates by paying for it.

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

#8

Post by ericHosick » Thu, Oct 31, 2019, 4:31 AM UTC

We're trying out a few things right now.

1) Use a migration tool (flyway, dbmigate, etc.)

2) We try and keep scripts idempotent to minimize on migration script explosion. Also helps to keep changes to similar assets located within the same file: easier to see the progress of a given asset as it changes throughout the lifetime of your app.

3) We have a dedicated repository for each database/schema. A CI/CD process triggers on a push to a given branch (dev, qa, stage, prod, etc.). The CI/CD process runs the migration script (in this case AWS Code Pipeline). Having the schema in its own repository decouples our databases from the service(s) that use them.

4) We try our best to separate schema changes from feature changes: a) push database changes first maintaining backwards compatibility, b) then push feature changes, c) then remove backwards compatibility. So, try to minimize on rollback script.

Local development is same as yours: docker-compose.

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

#9

Post by badrequest » Thu, Oct 31, 2019, 4:32 AM UTC

I don't have a say in what we do at work, but in all my side projects (which I write in Go), I've used darwin[1], run in main before the server starts up.

1: https://github.com/GuiaBolso/darwin

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

#10

Post by nZac » Thu, Oct 31, 2019, 4:40 AM UTC

sandGorgon wrote: post #2

We have built projects where we used python Alembic or Ruby's Activerecord migrations to manage the db. The actual application was in Go or nodejs. I daresay that Activerecord and Alembic outclass Liquibase in a lot of things.

Another vote for Alembic, especially if you use SQLAlchemy in your app. I can't say enough positive things about the 1-2 punch of SQLAlchemy and Alembic if you are dealing with relational databases.

Some technical benefits to Alembic:

- It will give you the structure for upgrades and downgrades.

- Has a clean interface for DDL operations

- Supports every (?) database that SA does

- You can use it in "Offline" mode if you don't want to have Python and all the dependencies on the server or have to hand the migration off to someone else that has access.

- The branch feature is really nifty if you are in advanced situations.

Some non-technical benefits with Alembic

- It is open source

- zzzeek, the author, is pretty active on here and has built both SQLAlchemy and Alembic so there is a lot of cohesion in styles.

- The issue tracker is active and responsive

- The code is stable (something you want in a migration tool) and is unlikely to go anywhere.

Highly recommend.

Edit: Formatting