A little concerned that this may break existing flows I use drizzle and just have a script that copies the drizzle/*/migration.sql, modifies it, and writes it to drizzle/*.sql If nested support alone is added, how will it handle both the old drizzle/*.sql and the "new" drizzle/*/migration.sql?

(switching to a comment on the diff so that we can have a threaded discussion instead of quote-replying)

I'm going to check in with the team about this one. I suppose what @k0d13 is asking for is to hide this behaviour change behind a --recursive flag or something. I think I would argue against doing that (thinking out loud):

1. I'm not expecting this to be very common

I would expect most people to make their "nested drizzle to d1" scripts write to a separate directory (a tempdir, or a separate local folder that's in .gitignore, and can be nuked at the start of the script). If it turns out that someone has posted a workaround script somewhere that encourages people to dump everything in the same dir, I might have to rethink this position.

2. Migrations should be idempotent or fail-fast

Since D1 only has up migrations (no down migrations), I would expect most people to either be writing idempotent migrations or having things fail early rather than corrupting data.

I have not checked whether drizzle does a good job of this in practice. If drizzle tends to write its migrations as create table ... (rather than the create table if not exists ... idempotent form) then the first migration will fail immediately and we will be safe.

It may be that there is some edge case where someone writes a set of step-by-step idempotent migrations that will cause data loss when re-applied from start to finish on top of a production database. In this edge case, the user will need to use time-travel to restore to the point before the migration. I would expect this to also cause problems against a local database though, which should make this more rare.

3. I would prefer to make things "just work" in the long term, or at least have a pit of success

If we decide to go with a --recursive feature flag as an interim measure, we should have some kind of "There are no top level migrations in $dir, but there are migrations in child folders. Pass --recursive to migrate these too" handling.

I would prefer to limit the complexity and say "we will always recursively apply sql migrations, and this is the search order"

Once the D1 team have decided on the resolution, the workers-sdk team are happy to merge this.

After chatting about this internally, it seems like we're not happy to accept the risk of running people's destructive migrations when we didn't previously.

(for cloudflare internal people, there is a bit more discussion at https://wiki.cfdata.org/spaces/~ivory/pages/1406859534/Fix+Nested+D1+Migrations)

The proposal is that we should add a migrations_pattern config item in wrangler.jsonc .

(similar to how migrations_dir is specified in https://developers.cloudflare.com/d1/reference/migrations/#wrangler-customizations)

We will not allow it to be configured on the command-line for now.

There are a whole bunch of edge cases and "pit of success" considerations to think about. Its error messages also need to play well with claude.

1. the default behaviour must not change (migrations_pattern should default to $migrations_dir/*.sql if not specified)
2. if wrangler d1 migrations create tries to make a migration file that does not match the pattern, it should return an actionable error
3. if there are no migrations matching the pattern, but there are some matching $migrations_dir/*/migration.sql, wrangler d1 migrations list and wrangler d1 migrations list should log a hint to stderr suggesting setting migrations_pattern to $migrations_dir/*/migration.sql (as a special case to help create a pit of success for drizzle specifically. We can expand this list of hint search patterns later, but let's start by being conservative for now)
4. tests covering all of the edge cases and error flows
5. We also need to update the documentation linked above.
6. ... I feel like there is something else that I'm missing.

This is not a small change anymore. I will try to take it on myself next week.

really appreciative of the conversation and looking forward to seeing this land.