DevOps process solutions: Stale migrations scripts are biting us

Sumanta

Due to some issues that we cannot solve for about 6 months, we have a delay between developers creating migration scripts and the code getting merged into master. Further, we are using the SQL scripts generated by DLM Automation Sync step to propagate changes from build server 
Stale migrations scripts are biting us now. We discovered an issue where a developer wrote a migration script that touched a table that had a constraint. However we had merged a different commit that dropped the constraint and pushed in release (x). Then we merged the aforesaid migration script so the constraint was added back in the release (x+1).

We are debating what are the best practices in using migration scripts: Some options are:
1.  Dev does not generate the real migrations scripts. They create the script, and then before merge we create a migration script to autogenerate the dependencies. 
2. We ask the developers to merge into a staging branch first, do reviews later. 
3. ...

There are many other ideas each wilder than the other.

Questions are:
* What are the best practices in this area?
*  If we were to delay migration script generation till the moment that we are ready to merge, is there a way to automate the generation of the migration script?

Sergio R

The best practice would be to rather than generate the migration script before, do the whole Sync process when you deploy. That way the deployment script will reflect the actual state of your sync target.

Sumanta

That is good advice indeed. But @Sergio R that does not save us from the issue of stale migration scripts. How do you overcome the issues posed by delay between developer committing the code and some time later the merge happens and might happen and out of order. 

Sumanta

1. developer A develops a migration script to update a row of data (NOT Static)
2. developer B changes removes, say, a trigger.
3. B's code is merged.
4. Release synced.
5. A's code is merged.
6. Release synced.