Switching between schema branches in alembic

[AndyS]

(I posted this also on Stack Overflow, but realized that this may be a better place for this sort of question)

I'm trying to wrap my head around alembic features, and probably am using
alembic in a ways it was not designed for so I'm looking for some advice from
experts.

In my design I have two variants of database schema (e.g. "A" and "B"), the
variants are exclusive, a particular database instance has to chose to use one
variant at initialization time. Both variants are changing during development
and I use Alembic to manage schema upgrades for a single variant. For
management purpose my Alembic history for both branches starts with a single
base which is also a branchpoint, something like:

       (branch: A)
       +--> a1 --> a2 --> a3 --> a4 --> ...
      /
    root
      \
       +--> b1 --> b2 --> b3 --> b4 --> ...
       (branch: B)

That works well, but now for some instances I'd like to switch from variant A
to B, let's say from revision a3 or a4 to revision b4, something like:

       +--> a1 --> a2 --> a3 --> a4 --> ...
      /                          |
    root                         |
      \                          v
       +--> b1 --> b2 --> b3 --> b4 --> ...

This may look like a merge for revision a4 but it really is not, it is more
like "git checkout" but I also need to make sure that data from branch A is
migrated to branch B of course.

I tried to introduce special migrations, e.g. branching at a4 and ending at
b4, but that confuses Alembic, apparently there cannot be two separate
migration scripts resulting in the same revision, i.e. "merge" is the only way
to have more than one parent for a revision. But merge is not going to work
here too because b3 and a4 cannot coexist in the same database.

I think it's obvious that standard history (from top graph) and a4-b4
migration steps cannot exist in a common history, so I am thinking about
messing with Alembic by hiding its standard history and only exposing a4-b4
step when I need to do this special migration, but I'm not sure if it's going
to work well at scale (with many more special migrations).

Did anyone have similar issues and was able to solve them reasonably, can you
share your experience?

[Sergi Pons Freixes]

Hi Andy,

As you said, it is not a merge, more like a "checkout". To accomplish that with alembic, you would need to downgrade (see https://alembic.sqlalchemy.org/en/latest/tutorial.html#downgrading) from a4 back to root, and then upgrade up to b4.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/37aa678f-a438-4288-a7fc-ae779187692fn%40googlegroups.com.

[AndyS]

Thanks for suggestion! I was afraid someone was going to propose just that 😀

In my case root is completely empty schema (root is only there to give a label to whole thing) which means that I need to start from scratch and loose all data. In general downgrading (even going a4 to a1) in my situation means potentially losing data which I have to avoid, so I can't use that "simple" approach.

[Sergi Pons Freixes]

I see... Ok, then I think that what you are actually doing is not going back b1, b2, etc., but something slightly different, which we could name c1, c2, etc. These migrations are going to be very similar to the "b" branch, so you can reuse probably a good amount of code, but adapting them to start from "a" instead of root. So your migration tree will look like:

       (branch: A)
       +--> a1 --> a2 --> a3 --> a4 --> ...
      /                     \       

    root                     + --> c1 --> c2 --> c3 --> c4 --> ...  
      \                       (branch: C)   

       +--> b1 --> b2 --> b3 --> b4 --> ...
       (branch: B)

I don't think there is another way around that, so at most you can get "partial code reuse", not "full migration reuse".

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/71900ab4-4042-413a-8f3d-38748c2dd8a9n%40googlegroups.com.

[AndyS]

That might work but would be a nightmare to manage. Basically every new "switch" introduces new branch that will never be merged and new migrations need to be added to every relevant branch. I think that it can also result in combinatorial explosion of branches if later switching is attempted. Not a very nice world to live in.

What I'm trying to do now is to have special "hidden" migrations by maintaining a bunch of separate directories that are included/excluded from version_locations. All regular migrations are contained in a location that is normally used for everyday tasks. Then there is one folder for each "switch" migration, e.g. one folder with revisions a4 -> b4. When I need to do this special switch I only include that one special folder into version_locations so that there is no conflict with regular migrations or other special migrations. It seems to work so far, but I'm slightly worried that it may break for some reason as alembic continues to evolve.

[Sergi Pons Freixes]

I'm quite curious about why you need to maintain all these different versions of the same database. Like in code, you branch out to be able to do development in parallel without interfering other development, and eventually you merge back. But here it seems you want to keep multiplying the number of  versions without ever converging?

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy-alembic/d627f34f-4f17-49da-b93b-73ac3d21c502n%40googlegroups.com.

[AndyS]

We have two schema variants for now, one is a legacy schema which we want to retire eventually but it is still used by many installations. Newer variant provides new features, and installations that require those new features have to migrate immediately. OTOH we do not want to require everyone to migrate right away because it's complicated process that needs downtime and not everyone needs new features from new schema. So in the meantime we plan to continue supporting both variants, and hope legacy variant eventually dies off.