Internal DB Migrations

The Problem

• DB schemas can drift across environments
  • dev, stg, prod, AND local
• accounting for all upgrade paths is hard

  • ie. from v0 -> v2, from v5 -> v6, from v0 -> v6

Solution Requirements/Constraints

• creation/deletion/modification of tables

• internal migrations of data

  • ex. breaking a “full name column” into a “first name” and “last name” column

• owned by API service code

  • One of the main roles of API services is to black box persistence layers.  Getting a DB into a workable state is part of that role.

• can be run on a totally fresh DB  or a production DB

Proposed Solution for relational DBs:

Flyway

(https://flywaydb.org)

Flyway

• schema/data migrations live in the service repo
• can be written as .sql
  • for CRUD operations on tables, indexes, etc..
• can be written as code
  • for more complex data migrations
• does a “rolling upgrade”
  • v0 -> v3, will actually go v0->v1->v2->v3

• good protection around developer errors

  • crashes on code being behind the DB version

  • crashes on developers making changes to past migrations that have already been run

  • (crashing is good here)

Implications/Considerations

• Service downtime during migrations
• Solutions for non-relational DBs
  • Mongobee seems good

Is Service Downtime Acceptable?

• can a service be down for a few seconds? (ie. adding new tables)
  • Chris' answer/hope: yes.
• can a service be down for a few minutes? (ie. internal data migrations that would cause writes to block)
  • Chris' answer/hope: yes.  This has implications around how services handle their dependent services being down. 
• can a service be down for 5+ minutes?
  • ​Chris' answer: no.  In this case, a more incremental (read: complex) migration strategy must be used.