We have a system of around 18 services that all need access to our database. We'd really like to use migrations to handle schema changes, but I can't figure out how to do this cleanly. How do you?
When this is necessary, we have the migrations versioned independently of the services that consume the database; essentially, there is a "database package" and a database schema version. We try our best to make all changes additive, to reduce the potential for breaking changes that may require all consumers (your services) to change. As I'm sure others will comment, you should do everything you can to eliminate these dependencies; ideally, the database<-->service relationship should be 1-1 and not 1-many.
* You have 18 services all using one database? Be aware that this pattern has the drawback that all of them are tightly coupled through the centralised database, and you might have a distributed monolith at hand. A typical symptom is that you have to release all services in lockstep.
Consider your resources and and see if actually having a monolith, or at least consolidating some services, possibly could lead to a better development experience.
* Use semantic versioning inside the services. Version additive changes to the schema (adding a column) by bumping the minor version and flag old code paths "deprecated".
Only remove columns that no other service is using once the code paths have been migrated from using the deprecated code paths, or use Feature Flags, like someone else mentioned.
Typical rails/django-style migrations have the problem of being coupled to a particular app/language - so you'll need to work directly at the level of the database.
You should add as much testing as possible - ideally have a central project that manages the database structure, and test each change carefully. Within each app, explicitly test that the app supports the intended new schema before moving to it.
Use a schema diff tool to autogenerate migration scripts and check that they result in a perfectly matching schema - I wrote `migra` that works with postgres, but there are good equivalents for most databases.
I think other people already have great advice. What I can add is: work on shielding this database with a service because 18 apps fighting for the same data source is hell.
If the DB is split in logical and physical schemas and ownership and access is ring-fenced accordingly with each of the 18 applications getting their own access login, there shouldn't be issues.
