Starting from specific base branch, run single branch

[chrisjsewell]

Heya, due to legacy reasons, I have two branches (starting from an empty database) that I want to merge into a single main branch going forward:

I added branch_labels to the first revision of each branch and, before adding the merge, could run e.g. alembic upgrade base1@head, to migrate from an empty database up to the head of that branch.

After the merge, base1@head appears to try to run all the migrations on both branches (first base1->head then base2->head).

Am I missing something; how can I tell it to only follow a single branch?

[zzzeek]

IIUC the merge point has both base1 and base2 as dependencies, so what version would you want it to be on if not the merge point ?

[zzzeek]

otherwise if you truly want to enforce "cant be on merge one without either branch1 or branch2", then yes you'd need to use "alembic stamp" to set the version table to show that both branches were applied, even though you didnt actually run those migrations.

[chrisjsewell]

why not retire the base1/base2 branches and start a third branch, that becomes your "main" one? legacy users get updated to head1 latest and/or head2 latest, then those branches are retired.

That sounds like what I want to do: a user will exclusively have a database, at present, on branch 1 or branch 2, then I want to get them to the head of those respective branches, at which point the schema are identical.
Then it was just how to do this transition on to the third branch? Do you run a migration to the head of branch 1/2, stop, stamp to a revision on branch 3, and run another migration to the head of that branch? Or is there a way to do this in a single step?
I would certainly like to retire 1/2, but there will probably still be users with them for many years to come 😅

Also, while I'm at it, is there a recommend way to create a new database at head? Currently (not my doing), it's always starting at the base (an empty database) and running alembic to work through every migration to get to head. That seems "inefficient" though, especially when you start to have lots of migrations?

[CaselIT]

Also, while I'm at it, is there a recommend way to create a new database at head?

I think the usual way is metadata.create_all() + stamp

[chrisjsewell]

I think the usual way is metadata.create_all() + stamp

Yeh that does seem a lot more sensible 😄

(the sordid details are actually that I've inherited a codebase that has a "django implementation" (which came first) and an "sqlalchemy implementation" of database ORMs, with subtly different schema (index names, etc), and I trying to ditch the django stuff, because sqlalchemy is just so much better 😆)

[chrisjsewell]

and yeh I should actually read the documentation first! https://alembic.sqlalchemy.org/en/latest/cookbook.html#building-an-up-to-date-database-from-scratch

[chrisjsewell]

Heya, just to follow up, this is the code I finally wrote for the automated migration I required (see aiidateam/aiida-core#5330), any feedback would be appreciated 😄 :

    def migrate(self, debug=False) -> None:
        """Migrate the storage for this profile to the head version.

        :raises: :class:`~aiida.common.exceptions.UnreachableStorage` if the storage cannot be accessed
        """
        from aiida.cmdline.utils import echo

        # the database can be in one of a few states:
        # 1. Completely empty -> we can simply initialise it with the current ORM schema
        # 2. Legacy django database -> we transfer the version to alembic, migrate to the head of the django branch,
        #    reset the revision as one on the main branch, and then migrate to the head of the main branch
        # 3. Legacy sqlalchemy database -> we migrate to the head of the sqlalchemy branch,
        #    reset the revision as one on the main branch, and then migrate to the head of the main branch
        # 4. Already on the main branch -> we migrate to the head of the main branch

        with self._connection_context() as connection:
            if not inspect(connection).has_table(self.alembic_version_table):
                if not inspect(connection).has_table(self.django_version_table):
                    # the database is assumed to be empty, so we need to initialise it
                    echo.echo_report('initialising empty storage schema')
                    self.initialise(debug=debug)
                    return
                # the database is a legacy django one,
                # so we need to copy the version from the 'django_migrations' table to the 'alembic_version' one
                legacy_version = self.get_schema_version_profile(connection, check_legacy=True)
                # the version should be of the format '00XX_description'
                assert legacy_version is not None
                assert legacy_version[:4].startswith('00')
                version = f'django_{legacy_version[:4]}'
                with self._migration_context(connection) as context:
                    context.stamp(context.script, version)
                connection.commit()
                # now we can continue with the migration as normal
            else:
                version = self.get_schema_version_profile(connection)

        # find what branch the current version is on
        branches = self._alembic_script().revision_map.get_revision(version).branch_labels

        if 'django' in branches or 'sqlalchemy' in branches:
            # migrate up to the top of the respective legacy branches
            if 'django' in branches:
                echo.echo_report('Migrating to the head of the legacy django branch')
                self.migrate_up('django@head')
            elif 'sqlalchemy' in branches:
                echo.echo_report('Migrating to the head of the legacy sqlalchemy branch')
                self.migrate_up('sqlalchemy@head')
            # now re-stamp with the comparable revision on the main branch
            with self._connection_context() as connection:
                with self._migration_context(connection) as context:
                    context._ensure_version_table(purge=True)  # pylint: disable=protected-access
                    context.stamp(context.script, 'main_0001')
                connection.commit()

        # finally migrate to the main head revision
        echo.echo_report('Migrating to the head of the main branch')
        self.migrate_up('main@head')

[zzzeek]

if it works it works. django has much nicer migrations than us though :)

[indiVar0508]

Hi,
I have kind of similar problem for which i am looking for a solution, unfortunately i don't have luxury to choose one of the branch i want to support all three route of branch
i.e. one starting from base(1), base(2) or either base(n).
the merge-points here rv(2) and rv(n-1) also execute other dependency route coming from base(1),

basically my base(2) and base(n) are squashed versions but i can't get rid of the original base(1) yet as some of my users are still on that route

i was wondering if there is some flag or some other concept that could allow me to tell alembic to don't do mergepoint behaviour just traverse the DAG from a given node.

i went through this but couldn't find if i can achieve same, https://alembic.sqlalchemy.org/en/latest/branches.html#working-with-multiple-bases

base(1) --> rv(1) --> rv(2) --> rv(3) --> rv(4) ... rv(n-1) --> rv(n)
                         /                            /    	
               base(2) --                 base(n-1) --

[indiVar0508]

Hi @zzzeek ,
apologies for the typo i meant base(n-1).

it would make no sense to do a path like base(2) -> rv(2) -> rv(3) without doing base(1) and rv(1).

so in this scenario base(2) signifies combined migration of base(1) and rv(1), so for me going through base(1) -> rv(1) -> rv(2) or base(2) -> rv(2) would give same state of DB. yes i agree completely that this makes original route (base(1) -> rv(1)) redundant and i should get rid of it, but some of my current users are still in the route and the original route has about 50-80 migrations and each user is at different state as of now, so i created the new route from base(2) which squashes 50-80 migrations, so i wanted to something like if i have a new user they can start from base(2) and my existing users can continue on original route.

I thought of using a separate migrations directory for base(2) but i have more updates on DB after rv(2) which will be common for both the original route and the new route.

so i was trying to figure out some way to not write same migrations in two directory.

[zzzeek]

oh OK. this is not a case I've ever seen before. you did a squash of your ancestors, but you still have folks within that unsquashed ancestor route. which as I'm sure you know, is not how the "squash of ancestors" was supposed to be used :) - it was only after the long form ancestors were definitely not needed anymore that one would do the squash. However, we can assume here that perhaps you had users out there who you didnt know about, or something like that, it's not unreasonable that this could happen.

I will summarize what I'm going on like this.

first, you had this series of migrations

base(1) --> rvo(1) --> rvo(2) --> rvo(3) --> rvn(1)->rvn(2)->rvn(2)

above, "base" is the original base, "rvo" means "revision old", meaning the first set of big revisions that built up the database as it was developed, then into "rvn" which are "revision new", these are the smaller incremental changes now that the app is released etc.

so then we go to squash out those messy "rvo" revisions, they take too long, we just want one big revision that builds up the database. ideally we'd just change out the base:

base(2) --> rvn(1)->rvn(2)->rvn(2)

then you found some customers who actually hadn't made it to rvn(1) yet. whoops. so we have two possible paths to reach rvn, which are mutually exclusive:

base(1) --> rvo(1) --> rvo(2) --> rvo(3) --> rvn(1)->...

base(2) --> rvn(1)->...

you can't, without some particular kinds of changes, run through base(1) -> rvo(1) -> etc. and then do base(2), because base2 would be making changes that base(1)->rvo(3) already made. so we have only the base(1) or the base(2) paths that both reach rvn(1).

at this point I need you to confirm we are on a common page, maybe not exactly your situation but close enough.

so really what would suit this immediately in Alembic would be some kind of a merge point that only requires one of the bases to be present. We obviously dont have this built in, but that would look like:

base(1) --> rvo(1) --> rvo(2) --> rvo(3) --+     
                                                                       ->   [magic merge point that needs *only* rvo(3) or base(2) to be satisfied] -> rvn(1) -> ...
                                                    base(2) --+ 

So.....getting this now without going crazy. the most idiomatic way would be to tell the migration database that base(2) has already occurred through stamping:

alembic stamp base(2)

another blunt way is to change the code in base(2) to check for the migrations in rvo(3) to be already present, and then not run:

base(1) --> rvo(1) --> rvo(2) --> rvo(3) --+     
                                                                                                                           ->   rv(merge) -> rvn(1) -> ...
    base(2, checks for rvo(3)'s schema changes and does not run) --+ 

however, depending on the scenario here, if this is code you are shipping to customers or just something you have to run once, you'd probably want to do an alembic stamp and be done with it.

[indiVar0508]

Hi @zzzeek ,

at this point I need you to confirm we are on a common page, maybe not exactly your situation but close enough.

Yeah that's it.!

if this is code you are shipping to customers or just something you have to run once, you'd probably want to do an alembic stamp and be done with it.

but for a new user if want him to start with base(2) rather than to go through base(1) --> rvo(1) --> rvo(2) --> rvo(3) --+ route .
the way i undersand as of now i would run these commands

alembic upgrade 'base_2_cut_revision_id'

alembic stamp 'rvo(3)' // so that alembic thinks it has executed base(1) .. rvo(3)

alembic upgrade head

but when i try to do alembic upgrade 'base_2_cut_revision_id' it says there are multiple head available
The script directory has multiple heads (due to branching).Please use get_heads(), or merge the branches using alembic merge.

[zzzeek]

OK so if your migrations look like this:

base(1) --> rvo(1) --> rvo(2) --> rvo(3) --+     
                                                                       ->   [magic merge point that needs *only* rvo(3) or base(2) to be satisfied] -> rvn(1) -> ...
                                                    base(2) --+ 

you need to:

1. run base(2) migration alone
2. run alembic stamp
3. then run alembic upgrade normally which will continue from

[indiVar0508]

Hi @zzzeek

Thanks for help, it worked now!

documenting here what i ended up doing just in case some one else also require it.
i changed my alembic migration directories like this, after reading through cookbook for alembic

alembic
     - versions
            - base(1)
            - rvo(1)
            - rvo(2)
            - rvo(3)   // Added a custom branch label (say - custom_label )
            - rvn(1)
     - squashed_versions
            - base(2) // added custom branch label (say - custom_label )

now i execute commands in this order

1. alembic upgrade base(2) --versions_locations=alembic/squashed_versions
2. alembic stamp rvo(3) -- purge // since we changed directory
3. alembic upgrade head
   in my app i just created a small custom command that combines these three step using alembic library and it works now!