Migration integrity checking

[KKSzymanowski]

Some popular DB migration systems like Flyway have a feature which saves a checksum of a migration file when it's being executed to later check if the migration hasn't been changed.

AFAIK this is there to avoid issues when a developer changes a migration that has already been ran on production. Currently in such case Laravel will simply treat the migration as "Ran" and ignore it. These changes will not be applied to the database.

I propose adding a similar mechanism to Laravel migrations.

Changing some already executed migration will result in the following errors:

Some things to consider:

1. This would of course have to be optional to not break BC.
2. The developer should probably have to have control over this behaviour per environment. For example an environment variable could be set to turn this feature off entirely, only warn about these errors or full-on throw an exception.
3. This would probably have to ignore any migrations found in the vendor directory to not fail when some migrations change in packages or in the framework. Nevertheless I think I'd be a good idea to at least warn about this issue by default.

[Sophist-UK]

A checksum sounds like a good idea in principle, but in practice I suspect this would have to be a hash of the file which might then tend to give false errors if the file has changed in ways that do not change the functionality i.e. Windows CR/LF -> Unix LF or removal of a trailing space or similar.

One alternative would be a more complex algorithm which ignores non-functional changes (i.e. multiple consecutive white-space characters, PHP comments etc. - but this might then lead to performance issues.

[KKSzymanowski]

I don't think there's a solution to all possible issues. I think the devs should accept that if they opt-in to this feature, they're gonna deal with some false-positives when they change anything in the migrations files.

Same thing applies to other migration solutions like the Flyway I mentioned. When you have raw SQL migrations, CRLF/LF or indentation changes would still break it.

Also if we were to go with the stripping of comments, normalizing line endings and converting all whitespace into a single space (which I doubt would pass in the PR) I don't think we'd have a big performance impact as these migration files are rather small.

[Sophist-UK]

Actually, it might be better to have some mechanism to take a snapshot of the DDL between migrations in Dev and then have some way when you apply migrations in other environments to check that the DDL is the same prior to a migration.

So here is how it might work. When you use artisan to create a migration you can specify e.g. --verify-ddl and this would query the database for the current DDL and add code to the beginning of the migration file which would check at migration time that the database state was correct. You would probably want to be able to verify only the parts of the database that the following migration would modify, and you might want to make it possible for the developer to manually write such checks.

[Angel5a]

Would you allow developer to manually write this checksum, or some other way add it in top of file, than this mechanism will cost nothing. Many times I saw copy-pasted code.

False-positive is ok. Developer would be warned and can look in git/subversion/mercury/... what are the differences. He need just an option to mark difference as solved (which would write a new checksum into db). I think it would be enough.

[KKSzymanowski]

I don't think you can reliably generate and compare DDL in migrations because changes done in migrations don't really have to be in Schema::whatever(). I sometimes do some data changes (like adding a permission or config option) in the migration to have it automatically ran on deployment and only ran once. I don't think there'd be a way to catch this sort of changes.

[Sophist-UK]

I was using the term DDL somewhat loosely I guess - what I actually mean is the database schema including permissions or config options.

This is never going to be perfect. If the overall aim is to ensure that the database structure in production is as intended i.e. the same as in dev / test / pre-prod / staging etc. then anything that assists developers to ensure this happens would seem to be a good thing. IMO this is analogous to unit / functional testing of code - you want tools to help you to make sure that you haven't made an error somewhere. Indeed you might even use assertXxxx to have some similarity to unit tests. However some changes like permission or config options might not be testable in this way.

So, you might start by providing Migration functionality that allows the developer to code pre-checks (and even post-checks) that the database structure is as anticipated (at least in those areas that matter to the migration) e.g. assertColumnExists('table', 'column') or assertColumnIsUnsignedLongInteger('table', 'column').

This could then be bolstered by tools to assist with creating this code i.e. that you can run at the start of coding a migration to (help?) populate the migration with these pre-checks i.e. that would query the database and produce such code checks.

[joelharkes]

We sometimes cleanup/merge data migrations together to improve the tests suite and cleanup code history.

We then merge newer migrations (that add columns to existing tables) into the older ones where the table was created.
If this would then make the migrate command fail, would be unfortunate.

[KKSzymanowski]

Yeah, in that case you'd need to refresh the checksum or disable this feature entirely.