RFC #4 - Patterns for dealing with migration bloat

[panta82]

As my application grows, so does the number of migrations. I am already up to 50, and the big refactoring I am doing now will produce 10-15 new migrations on top of that.

So I started looking for ways to somehow organize these migrations and reduce the bloat in the migrations folder.

The first thing I tried was to group these big features into scopes, and use them as "migration groups" of sorts. That didn't quite work out. migrate all will run scopes in an arbitrary order one after another, which means I can't really use them to modify the same database with cross-cutting concerns. Scopes are really only useful for either completely isolated features, or different databases.

I also considered packing more changes into a single migration, using some kind of async chaining pattern. Unfortunately, I use MySQL which doesn't have DDL transactions. So if anything breaks, I'm stuck with an inconsistent state that I have to resolve manually. The best way to use db-migrate is indeed to create a new migration for every step, which doesn't help the bloat problem.

Another option would be to rewrite some older migrations that are doing duplicate work (eg. create table, mutate table, add column, remove column...) into a single migration. But that sounds like a lot of manual fiddly bug-causing work.

So in the end, I think I'll probably just preface my new migrations with the feature name (eg. 20170118141857-big_refactoring-add_table_x) and keep adding them as I'm used to.

Before that though, I figured I should ask. Is there some option or pattern I am missing? What is the recommended best practice for dealing with accumulated migration bloat?

---

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[panta82]

Oh, I forgot to mention.

One possible pattern for this would be to change the semantics for the :all scope. Instead of executing scopes one after another, merge all transactions together into one "virtual directory", and execute them in order based on their timestamp prefixes.

But that was already suggested and rejected in #357 , so I didn't pose it as a feature request.

[jcrben]

Auto-migration feature might help with this, similar to the feature in Loopback https://loopback.io/doc/en/lb2/Creating-a-database-schema-from-models.html and Sailsjs http://sailsjs.com/documentation/concepts/models-and-orm/model-settings#?migrate

[wzrdtales]

That didn't quite work out. migrate all will run scopes in an arbitrary order one after another, which means I can't really use them to modify the same database with cross-cutting concerns. Scopes are really only useful for either completely isolated features, or different databases.

Completely true currently, however, it is planned to add sorting to scopes in an user defined behavior. Feel free to open a PR for this if you want to :)

Another option would be to rewrite some older migrations that are doing duplicate work (eg. create table, mutate table, add column, remove column...) into a single migration. But that sounds like a lot of manual fiddly bug-causing work.

Ah well... I would suggest you to never rewrite any migrations. The reason is simple:

You have migrations that have already been executed, if you're rewriting those, renaming or do any other stuff, this means you create yourself a lot of trouble and in the same second you don't use migrations anymore in the way they are intended to work. Migrations are by concept always build incrementally, so you never bother to change any old migrations.

What I could think of is that it could be possibly useful to add a feature, that allows moving migrations into a different scope, w/ or w/o rewriting the migrations table. Preferably w/o rewriting, but I think that would not work out quite well though.

What is the recommended best practice for dealing with accumulated migration bloat?

Actually that is the reason that scopes exist, using them from the very beginning to organize the migrations helps a lot keeping everything tidy.

One possible pattern for this would be to change the semantics for the :all scope. Instead of executing scopes one after another, merge all transactions together into one "virtual directory", and execute them in order based on their timestamp prefixes.

That would actually break the behavior, so we can't really do this and merge them together. But as stated at the top, adding the functionality to sort the scopes in an user defined behavior would be the proper fix for this.

I also considered packing more changes into a single migration, using some kind of async chaining pattern. Unfortunately, I use MySQL which doesn't have DDL transactions. So if anything breaks, I'm stuck with an inconsistent state that I have to resolve manually. The best way to use db-migrate is indeed to create a new migration for every step, which doesn't help the bloat problem.

Yes MySQL does not, MariaDB will in some of the future versions, finally, though. But the problem is more while working with those databases, when your migrations are final you can mostly move them safely together into a single migration, naturally only if those have not been in production yet. That also depends on what the migrations do actually, but most schema changes, or index alterations don't do any harm to the other one. Chaining commands is actually supported natively since db-migrate supports promises from 0.10.x though.

However, I do know the pain of working with MySQL/MariaDB for that reason if I want to test chained commands, I write them one by one and test them one by one, fi the first one has already proven to work, it will also continue to work. I have already thought about adding the optionality to execute the down migrations if an error occurs within the migration, to reverse the state. As long as the down migrations is ordered the same the up is, this would help at least restoring the old state.

Auto-migration feature might help with this, similar to the feature in Loopback

Auto-migrations are actually quite different to migrations like db-migrate ones, as they are build from models. So in the first place you need to be using a ORM, which is not always the case, especially for applications where an ORM comes at the cost of performance which is no longer bearable. And in the second place, those are not really made and targeted to production systems.

For example: Changing a migration which is already in production is a no go, therefore migrations that have been executed are treated like already being executed. In short they are being ignored and build upon. That is important when you want to execute migrations automatically on the start of a new version of your application on the costumer side and also it is important if you want to realize real zero downtime.

So auto-migrations do not really fit to the concept here. But instead what one could think of, would be to use some plain old tools like grunt, gulp and sutff. Make them watch the migrations folder and run all migrations down to the place you changed one of your migrations and execute them back up. However as stated above, you normally never want to alter your migrations as soon as they have moved to production. If they are not in production yet, the only pain you will cause by changing them are the screams of your colleagues yelling at you b/c you changed the migrations again and broke their setup. So in any case, changing existing migrations, should be done at the right point in time, for example while polishing up everything for the release, and should never been done as soon as they are in production, b/c now you have a version of your database, which is the whole sense of migrations: database versioning.

I hope this helps a bit, and feel free to comment on anything I wrote if you have different opinions on some of the stuff I'm eager to get to know them :)

[panta82]

Completely true currently, however, it is planned to add sorting to scopes in an user defined behavior. Feel free to open a PR for this if you want to :)

I thought about this and saw the call for a PR request.

The problem is this: the ordering of scopes would really need to be dependent on the operation you are executing (up or down).

Let's say I have these two scopes:

• 01_initial_setup
• 02_user_management

I want the scope 02_user_management to be dependent on 01_initial_setup, thus the prefix numbers (BTW, this is what I tried to do for my project, it seems like the simplest way to get user-controlled ordering). So when I do migrate up, I need the order to be 01, 02. However, when I do migrate down or migrate reset, the order needs to be in reverse (02, 01), with all migrations inside scopes done in the same way.

I don't see how user ordering can apply here. If scopes are to be dependent on one another, they need to run the same way as usual migrations.

One possible pattern for this would be to change the semantics for the :all scope. Instead of executing scopes one after another, merge all transactions together into one "virtual directory", and execute them in order based on their timestamp prefixes.

That would actually break the behavior, so we can't really do this and merge them together. But as stated at the top, adding the functionality to sort the scopes in an user defined behavior would be the proper fix for this.

Would it?

Presume people are now using scopes for unrelated concerns. Eg. they have two different databases, or operate on separate sets on tables.

They can still execute migrate up:scope1 and migrate up:scope2 and get individual scopes to perform their migrations. That part remains the same.

The difference is when they do migrate up:all. Instead of first doing scope2, then scope1, all migrations from both scopes are intermingled and executed based on timestamps.

But since these two scopes are already independent of one another, this doesn't matter at all! You can create table in DB1, then delete a column in DB2, then add an index in DB1, then add table in DB2. And the order doesn't matter, because the scopes are independent.

IMO the new migrate :all semantic would only add possibilities for new usages, I don't see how it would break any of the old ones (given people use scopes as intended).

Ah well... I would suggest you to never rewrite any migrations. The reason is simple:

You have migrations that have already been executed, if you're rewriting those, renaming or do any other stuff, this means you create yourself a lot of trouble and in the same second you don't use migrations anymore in the way they are intended to work. Migrations are by concept always build incrementally, so you never bother to change any old migrations.

Yeah, granted. This is a recipe for creating bugs.

What I could think of is that it could be possibly useful to add a feature, that allows moving migrations into a different scope, w/ or w/o rewriting the migrations table. Preferably w/o rewriting, but I think that would not work out quite well though.

In my attempt with scopes I did rewriting. It worked OK.

What is the recommended best practice for dealing with accumulated migration bloat?

Actually that is the reason that scopes exist, using them from the very beginning to organize the migrations helps a lot keeping everything tidy.

That won't work because of the issues with scope ordering and isolation discussed above.

However, I do know the pain of working with MySQL/MariaDB for that reason if I want to test chained commands, I write them one by one and test them one by one, fi the first one has already proven to work, it will also continue to work. I have already thought about adding the optionality to execute the down migrations if an error occurs within the migration, to reverse the state. As long as the down migrations is ordered the same the up is, this would help at least restoring the old state.

Hmm... this could help in some cases. But if the down migration chain presumes existence of some parts of the up migration that never got executed, then it will break too.

What might help is the option to "print out" all the generated SQL to the console, instead of executing it (so it'd be easier to clean up the mess). Or give me interactive prompt to y/n each async step after I force migrate down.

I don't know. On a project that uses mysql, I think I'd just stick to the one migration per operation, without chaining into transactions.

If they are not in production yet, the only pain you will cause by changing them are the screams of your colleagues yelling at you b/c you changed the migrations again and broke their setup.

Been there done that 😄

[wzrdtales]

Would it?

Yes :) B/c scopes are configurable, so it is just not that easy to just merge them alltogether. There would be also the need to switch dbs, or even the db server, which is not yet implemented though, and mixing them all by merging together would mean that you really randomly switch around those settings b/c migrations that have been separated get mixed up.

Another case would be if someone has a development scope, or something like that, that should only be executed in development. Although that is not what scopes are really intended for, it is easy to use them like that. A fix for this would be to add a configuration option to the scope configs, i.e. excludeFromAll: true.

In my attempt with scopes I did rewriting. It worked OK.

Have those migrations been in production yet? If yes, how did you dealt with those already executed ones?

Yeah, granted. This is a recipe for creating bugs.

Not just bugs, you literally manipulated and changed the past, and we all know where this ends :p. Anyhow, changing migrations that have been executed, would need tor reexecute them at your customer as soon as they have been in production and this pretty much breaks with what migrations are there for :)

That won't work because of the issues with scope ordering and isolation discussed above.

Y, ik. The issue above needs to be solved first in this scenario.

Hmm... this could help in some cases. But if the down migration chain presumes existence of some parts of the up migration that never got executed, then it will break too.

This is ok, that is also the reason why the down migrations need to be ordered in the same order. Otherwise they would break to early.

What might help is the option to "print out" all the generated SQL to the console, instead of executing it (so it'd be easier to clean up the mess). Or give me interactive prompt to y/n each async step after I force migrate down.

There is a print out option:

• --verbose

• --dry-run

• --log-level

  db-migrate up --log-level "sql|error" --verbose

Currently log-level does not output verbose output without verbose being defined. SQL is part of the verbose output currently. I will open that as a bug as this is not intended though.

I don't see how user ordering can apply here. If scopes are to be dependent on one another, they need to run the same way as usual migrations.

That would still work as long as you don't rely on a state from another scope. As soon as they have dependencies to each other you're right. But I ment with user defined sorting, that a user really can specify the order via the scope configs.

[jcrben]

Why not update the initial create table SQL to reflect the updates from migrations, or store an updated version of the db? Then you could delete the migrations. They're in source control if you need to go back and look at them.

Getting this to work with zero downtime might be tricky, but its not the type of thing you need do often.

I'm not sure that auto-migrate implies an ORM. It implies a machine-readable and complete version of the schema, which theoretically could be grabbed and parsed from the db itself. But I guess that's a separate topic, altho it's what I was looking for (see knex/knex#127 (comment))

[panta82]

Yes :) B/c scopes are configurable, so it is just not that easy to just merge them alltogether. There would be also the need to switch dbs, or even the db server, which is not yet implemented though, and mixing them all by merging together would mean that you really randomly switch around those settings b/c migrations that have been separated get mixed up.

Yes, that would be inconvenient to implement (have to keep connecting to database in each migration, or keep a pool of connections...).

But from the user's perspective, do they really care that you switch between DB settings as you are running through migrations?

Another case would be if someone has a development scope, or something like that, that should only be executed in development. Although that is not what scopes are really intended for, it is easy to use them like that. A fix for this would be to add a configuration option to the scope configs, i.e. excludeFromAll: true.

If someone does have a dev scope, and they go to production and execute migrate up:all right now, they would be in the same mess whether scopes are ran one after another or intermixed.

excludeFromAll sounds like a good idea.

Have those migrations been in production yet? If yes, how did you dealt with those already executed ones?

Yes, all the old migrations are in production. I basically created a upgrade_migrations_table as the first migration in my new would-be scope, then did string concatenate / slice path for all the old migrations in its up and down actions.

Worked fine in testing (well, except I couldn't control the order of scopes, so I reverted it back).

There is a print out option:

Oh! Good to know!

[wzrdtales]

Why not update the initial create table SQL to reflect the updates from migrations, or store an updated version of the db? Then you could delete the migrations. They're in source control if you need to go back and look at them.

B/c this would destroy existing setups and this wouldn't be migrations anymore. If you delete old migrations you destroy old setups. Those still include the old entries to migrations that you've just deleted. In the case you would need to reverse them everything would just crash. What you describe is a whole different thing than migrations are. You want a reflection, migrations are not a reflection of the current database, they describe a path to reach a defined state.

Migrations have been born out of the need to have a deterministic version control over your database. As stated above, they are not a representation, but a path. What you're talking about are really just alternative dialogues to a SQL schema dump, which allow to be a bit more dynamic. Jjust altering a representation of the schema and letting the program interpret what to do to recreate this representation on your database makes it maybe quite easy to maintain, but you have zero control, you have no real version control over your database even if you version control those representation themselves. The reason for this is, that you can't really reverse your actions reliably nor are they deterministic. And you also can't rely on this strategy to deploy the latest version of the database to your customer. It might has some effects that will cause unintentional data loss and a customer might migrate from version 0.0.1 to 12.1.0. They shouldn't be forced to migrate to each single version just to upgrade to the latest version of your product and this is the reason why it makes sense to maintain a path instead of a representation of your database.

Anyhow, reflecting a current state of the schema is actually still something that is a working item, to speed up migrations from zero when you've got yet nothing. And this is will be a reflection generated from the existing migrations and not the database, so still a bit different :)

So yes, a schema reflection might be easier to maintain, but solves a whole different problem than migrations do.

Getting this to work with zero downtime might be tricky, but its not the type of thing you need do often.

Today zero downtime is something that is becoming, at least for new products, quite the default. But agreeing not everyone is yet providing zero downtime in their products, mostly b/c there is not much knowledge made publicly available or best practices. So many just don't think about it, or think it is too hard to accomplish. Which btw is not the case, it is quite easy, you just have to follow some rules as developer, zero downtime has actually nothing to do with the infrastructure, which just have to provide a load balancing itself.

[panta82]

@jcrben

Why not update the initial create table SQL to reflect the updates from migrations, or store an updated version of the db? Then you could delete the migrations. They're in source control if you need to go back and look at them.

Getting this to work with zero downtime might be tricky, but its not the type of thing you need do often.

If you do that in production, and then migrate down for some reason, the script will break because you no longer have all the files the db says you do. You would have to rewrite migrations table(s) after you make this change.

Other great concern is the possibility of introducing bugs. You basically remove well tested working code for untested new code, with sole purpose of code aesthetics. Hard to justify.

[wzrdtales]

Yes, all the old migrations are in production. I basically created a upgrade_migrations_table as the first migration in my new would-be scope, then did string concatenate / slice path for all old the migrations in its up and down actions.

Sounds reasonable, that might be something which db-migrate could provide a helper for, to reorganize.

If someone does have a dev scope, and they go to production and execute migrate up:all right now, they would be in the same mess whether scopes are ran one after another or intermixed.

True.

But from the user's perspective, do they really care that you switch between DB settings as you are running through migrations?

When it is getting hellish slow b/c of this they might care. Not to forget that you can easily tap into traps like too many open connections as soon as we step onto this path :)

Anyhow, reversing the sort order on down actions for scopes wouldn't hurt anyone and might be a good first step into the right direction.

[jcrben]

Well, the general approach that I hear (and which I follow at work and in my projects) is roll forward in production, not back. I still do define down, but it's mostly for dev environment work such as bisecting a regression - can't remember the last time such a thing was run in production. Consolidating old migrations is something you would do with rather old migrations which you wouldn't go back to.

For example, I worked on an app that had over a thousand migrations from years of work. At one point we deleted them, updated the initial dev environment schema, and started building up more migrations.