Sorry for the long lines in my previous post. Outlook is not the best
email client. ☹

-----Ursprüngliche Nachricht-----
Von: Kruis, Anselm 
Gesendet: Freitag, 26. März 2021 17:16
An: '~olly/yoyo@lists.sr.ht' <~olly/yoyo@lists.sr.ht>
Betreff: Sorting migrations from multiple sources

Hi,

First many thanks for providing yoyo to the public. A few month ago we
retrofitted it into the development process for a huge old internal
database application. It helps us to improve the quality of our
development process.

We use several database instances. For now think of "DEV-DB" and
"PROD-DB". For the DEV-DB we use two migration source directories
"test" and "prod". For PROD-DB we only the source directory "prod". 
The migrations in directory "prod" define the database schema and the
migrations in directory "test" load additional test data into the
"DEV-DB".

We have the convention to use a naming schema for the migration files
to impose an ordering:
<migration number>.<sub-number>-<other text>.sql (or.py). 

Currently yoyo sorts the migrations for the DEV-DB this way:
1. all migrations from directory "test" sorted by file name and then 
2. all migrations from directory "prod" sorted by file name

Now suppose I have the migrations "test/01.sql", "test/02.sql",
"prod/03.sql", "prod/04.sql", "test/05.sql" and "prod/06.sql".
Currently they would be applied in the following order:

"test/01.sql",
"test/02.sql",
"test/05.sql",
"prod/03.sql",
"prod/04.sql",
"prod/06.sql"

Now, if migration "test/05.sql" declares a dependency on "prod/04.sql"
I get the following order:

"test/01.sql",
"test/02.sql",
"prod/04.sql",
"test/05.sql",
"prod/03.sql",
"prod/06.sql"

This changes the application order for productive migrations from 03,
04, 06 to 04, 03, 06. That is, I get different ordering of the
productive migrations for PROD-DB and DEV-DB. That is not so good.

Therefore I would like to add an option to sort all migrations by their
ID. Then I get this order:

"test/01.sql",
"test/02.sql",
"prod/03.sql",
"prod/04.sql",
"test/05.sql",
"prod/06.sql"

Now it is no longer necessary to declare dependencies explicitly. I
made a minimal prove of concept patch.
 
diff -r 312182a8c03f yoyo/migrations.py
--- a/yoyo/migrations.py        Mon Jan 18 15:13:41 2021 +0000
+++ b/yoyo/migrations.py        Fri Mar 26 17:03:56 2021 +0100
@@ -510,6 +510,10 @@
     """
     migrations = OrderedDict()  # type: Dict[str, MigrationList]
 
+    sort_migrations = '!SORT!' in sources
+    if sort_migrations:
+        sources = tuple(s for s in sources if s != '!SORT!')
+
     for source, paths in _expand_sources(sources):
         for path in paths:
             if path.endswith(".rollback.sql"):
@@ -531,7 +535,7 @@
             else:
                 ml.append(migration)
     merged_migrations = MigrationList(
-        chain(*migrations.values()),
+        sorted(chain(*migrations.values()), key=lambda m: m.id) if sort_migrations else chain(*migrations.values()),
         chain(*(m.post_apply for m in migrations.values())),
     )
     return merged_migrations

It works well, but the configuration is a bit of a hack: you have to
add the entry "!SORT!" to the sources list. Unfortunately there seems
to be no easy way to access the configuration and I didn't want to
change the signature of function "read_migrations(*sources)" for a POC.

It would be really cool to get an option to sort the migrations in a
future release of yoyo.Kind regards
  Anselm

--
Anselm Kruis
Senior Solution Architect
MSE OPS HPCS S PS
T +49-89-35 63 86-874
M +49-16 05 82 82 00
anselm.kruis@atos.net
science + computing ag
Ingolstädter Str. 22
80807 München, Germany
atos.net/de/deutschland/sc

science + computing AG; Vorstand: Dr. Martin Matzke (Vorsitzender), Sabine Hohenstein, matthias Schempp; Vorsitzender des Aufsichtsrats: Philippe Robert Jonas Miltin; Sitz der Gesellschaft: Tübingen; Registergericht: Amtsgericht Stuttgart, HRB 382196.