How to handle local development (pre-migration)?

[g105b]

When collaborating on a project, there needs to be a proper way to handle "temporary" migrations. I use the system of calling the migrations on my feature branch new-001-whatever.sql, so I can go through and number them properly when merging the PR, but can this be automated by the migrate command?

Something I've noticed other developers doing is creating the change locally in their database, then pasting the generated DDL code into a new migration, but when it comes to running gt migrate, there's then a failure because their local database already has the tables created...

Something to think about for a rainy day...

[g105b]

Assume there are already 234 migrations. The problem is while you're working on your feature branch, another developer can increase the migrations to 235. If you started your migrations at 235, there would be a conflict when you merged your branch. The solution is to create your new migrations sequenced from 001, but prefixed with dev.

• query/_migration/dev-001-add-index.sql
• query/_migration/dev-002-customer-foreign-key.sql

These will be applied to your database when you do gt migrate, but when it comes to merging, you can perform gt migrate --merge to put them into the correct order. There might need to be some work avoiding clashes in the _migration table, but that sounds a lot simpler once they've already been run.

[g105b]

It might be a bit cleaner to have a dev directory within the _migration parent directory.

[g105b]

The current system assumes one global, monotonically increasing migration sequence in query/_migration. That works for a solo workflow, but breaks down when multiple feature branches each add migrations independently. Two developers can both create 0007-...sql, and by the time one branch is merged, the other branch’s migration numbers are no longer valid. The result is PR churn and a risk of editing already-run migration files just to renumber them.

The proposed solution is to introduce a query/_migration/dev directory which separates the stable, merged migrations within query/_migration and the branch-local disposable migrations in query/_migration/dev.

Plan

1. Extend config and path resolution. Add support for a dev migration path, defaulting to query/_migration/dev alongside the existing main migration path logic in ExecuteCommand.
2. Add a second migration state model for dev migrations. This will be done within a new table: _migration_dev to avoid messing up existing behaviour.
3. Introduce DevMigrator which can reuse most of the Migrator class, but with differing responsibilities: load new dev migration files, sorted by local sequence, and only running ones not recorded in the local _migration_dev file. Hash and identity should be ran after success.
4. New gt migrate arguemnts: gt migrate --dev will apply all migrations in the main sequence, as well as the new ones in the dev directory. gt migrate --dev-merge will take the current dev files and move them to the correct location on disk, along with moving the migration records in the database to the correct location.

Unsorted thoughts

• There needs to be a way to reset the DevMigrator. gt migrate --reset-dev makes sense. But what about things that had already been migrated? Do we need a way to store a snapshot before migrating, so we can rewind to that point?
• Is there any way we can catch failures where one developer's migrations make conflicting edits to another's - there will be an error, but is that the point... there should be an error in these situations, meaning it requires human resolution?