Improve Goose Migration Handling for Interleaved Migration Files

[scaletech-milan]

Problem Description: Currently, the goose package does not handle interleaved migration files effectively when developers work concurrently on different migrations. This causes issues when a developer attempts to apply migrations that were created in between existing ones, resulting in missing migration errors.

Steps to Reproduce:

1. Developer A adds migration files for user and admin tables, which are merged and applied in production.
2. Meanwhile, Developer B creates a migration for the roles table and alters the user table, with their changes existing between Developer A's migrations.
3. When Developer B attempts to apply their migrations using goose up, they encounter an error indicating missing migrations before the current version, despite the necessary migration files existing.

For exa-

> goose -dir db/migrations postgres "postgresql://postgres:[email protected]:5432/go-migartion?sslmode=disable" up  
2024/07/15 18:13:28 goose run: error: found 2 missing migrations before current version 20240715084701:
	version 20240715084646: db/migrations/20240715084646_new_roles_table.sql
	version 20240715084655: db/migrations/20240715084655_alter_table_users.sql

Expected Behavior:

• Migrations should be applied based on their logical dependency order rather than solely relying on the timestamp or version number.
• The goose package should support handling interleaved migrations effectively, allowing developers to apply migrations created in between existing ones without encountering missing migration errors.

Proposed Solution:

• Implement dependency tracking between migration files to determine the order of execution based on schema dependencies rather than timestamp or version number.

Additional Context:

• This issue is critical for teams working concurrently on database schema changes, where multiple developers may create migrations that are interleaved in the timeline.
• Enhancing migration handling will improve developer productivity and reduce errors related to applying migrations out of order.

[scaletech-milan]

If anyone has encountered this issue before, please notify me immediately so I can prioritize fixing it.

[mfridman]

Implement dependency tracking between migration files to determine the order of execution based on schema dependencies rather than timestamp or version number.

This is quite a difficult problem, and at the moment it's up to the developers (and CI systems) to handle correct ordering. Goose does support out-of-order migrations, but that also has its downsides.

https://github.com/pressly/goose?tab=readme-ov-file#hybrid-versioning might be a good starting point.