A couple features to facilitate a branch-by-default workflow

[eeshugerman]

Preface

My team recently made the switch to Alembic, and it's been great! However, it took some jimmy-rigging to set up the workflow we need. I imagine there must be other teams that would benefit from a similar setup, so I thought I'd post here describing (1) what I came up with, and (2) a few minor(?) features that would make it even nicer. Of course, I realize Alembic can't cater to every possible workflow, but no harm in asking right ;). I would be interested in implementing said features, if there is interest. Alternatively -- critique my approach! Maybe I missed something which renders these features unnecessary.

Background

I work on a team with five other developers and, due to the nature of the project, database revisions are common: we add about three per week (collectively), sometimes more. We have three environments in our normal flow -- qa, stage, production -- plus a sandbox which doesn't get much use. We release to production about twice a month.

The problem

Our previous migration tool did not support branching, and trying to force a strictly linear migration history was proving to be a nightmare, especially in regards to moving code between environments. This was especially annoying considering that most of the time, the order in which revisions from the same sprint are applied doesn't actually matter! ... but the tool required a linear history. What we really want is a "semi-ordered" migration history, where revisions from 6 months ago will certainly precede revisions from this month, but on a smaller time-scale (say, a sprint) migrations are unordered by default. (By default is key -- sometimes we do need to specify a dependency between migrations from the same sprint.)

The solution

So that's what we were after, and Alembic's branching got us 90% there, but a little more is needed for a complete, developer-friendly solution. There were two points to still address:

• when do merges happen? we can't just branch outwards forever...
• when creating a new revision, from which existing revision does the developer branch off of? and how do they do it easily, in a manner not prone to errors?

When do merges happen?

Before each release to production, on the release branch, add a revision merging all heads, and give it a special message. We call it a "release revision", and use this script:

# create_release_revision.sh

if [ -z "$1" ]; then
    echo 'Please provide the release version number. Example:'
    echo '$ create_release_revision.sh 1.29.0'
  exit 1
fi

alembic merge heads --message "[RELEASE-MERGE] v$1"

After the release, we merge production back into qa and stage so that they get the changes added in the release branch (version number, changelog, and now the release revision too).

Creating a new revision...

When a developer needs to create a new revision, and its only dependencies are already in production (as is usually the case), they are to branch off of the most recent "release revision", as generated by the script above. To make this easy, we use the following script:

# create_revision_branch.py

import sys
import os

# don't import from current directory because alembic/ gets in the way
sys.path.remove(os.getcwd())

from alembic.config import Config           # noqa
from alembic.config import CommandLine      # noqa
from alembic.script import ScriptDirectory  # noqa


def get_last_release_rev_id():
    script_dir = ScriptDirectory('alembic')
    for script in script_dir.walk_revisions():
        if script.doc.startswith('[RELEASE-MERGE]'):
            return script.revision
    raise Exception('No release revision found in history')


def main():
    cmd_line = CommandLine()

    # drop script name from argv
    # argparse does this by default iff argv is not provided
    argv = sys.argv[1:]

    # play the roll of `alembic revision`
    argv.insert(0, 'revision')

    options = cmd_line.parser.parse_args(argv)

    options.head = get_last_release_rev_id()
    options.splice = True

    cfg = Config(
        file_=options.config,
        ini_section=options.name,
        cmd_opts=options,
    )
    cmd_line.run_cmd(cfg, options)


if __name__ == '__main__':
    main()

This script acts just like alembic revision, except it automatically sets the --head flag to the most recent release revision, and also passes --splice. If the new migration depends on another migration which is not yet released, the developer must instead uses bare alembic revision and set --head themselves.

---

So that's what we've got, and we're so glad to finally have a system that works, technically speaking, even if it's just a little bit clunky. Now, if story A is merged into QA before story B, B can still be merged into stage before A! What a concept!

Feature proposal

That said, ideally we would not need to use a script for an operation as basic as creating a new revision, and I believe it would be fairly straightforward to give this workflow proper support in a non-invasive manner. It would look something like this:

• a setting in alembic.cfg branch_by_default perhaps, which would default to False, but when True, would make alembic revision branch off of the most recent “release revision”, unless --head is specified, in which case it would work as it does now
• a new argument to alembic merge which would somehow designate the revision a "release revision" (as I've been calling it in this post). It would maybe be called --release, but it would be nice to think of something more general, since these revisions don't necessarily need to correspond with releases.

The main question here is of course how to designate release revisions. Would it just be a message prefix, as in the scripts above, or is there a better way? I've seen something about "tags" when browsing the docs, but haven't made sense of them, and come to think of it, I'm not even sure they apply to revisions, but if they do, that sounds like it could be a candidate?

[zzzeek]

Hi there -

why dont you use the --branch-label option of alembic merge ? that';s where you create your "release_vXY" name. Then if you want to make a new revision off that branch I believe it should work as "alembic revision --head release_vXY@head".

haven't tried any of this but that's 99% of it? if it seems to be the right idea, let me know if that actually works. if so, I think it should be possible to add a little bit of logic to your env.py to make that last "--head" part automatic too, if you have a programmatic way of getting the current release version of your application from its source base.

[zzzeek]

assuming those steps work then this becomes a recipe to add at https://alembic.sqlalchemy.org/en/latest/cookbook.html

[eeshugerman]

Good idea! I'll give this a try.

Even if --branch-label for some reason doesn't work out, just replacing create_revision_branch.py with some env.py code would be a huge improvement. I thought I looked into this and deemed it impossible, but I guess I didn't look closely enough -- taking another look at the documentation, it definitely seems possible.

[eeshugerman]

So to start, I'm trying to automate the --head flag, but I'm a little confused. The docs describe how to customize revision generation using the process_revision_directives arg to context.configure in run_migrations_online. But it seems run_migrations_online isn't executed when I run alembic revision? I can add raise Exception as the first line and alembic revision completes normally. What am I missing?

def process_revision_directives(context, revision, directives):
    def get_last_release_rev_id():
        script_dir = ScriptDirectory('alembic')
        for script in script_dir.walk_revisions():
            if script.doc.startswith('[RELEASE-MERGE]'):
                return script.revision
        raise Exception('No release revision found in history')

    script = directives[0]

    if script.head is None:
        script.head = get_last_release_rev_id()
        script.splice = True


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_schemas=True,
            process_revision_directives=process_revision_directives
        )

        with context.begin_transaction():
            context.run_migrations()

[zzzeek]

take a look at https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.process_revision_directives - you need to set revision_environment=true in your alembic.ini

[eeshugerman]

Thanks, that did the trick!

alembic revision --head vX.Y.Z, seems to work as expected, as does script.head = 'vX.Y.Z' in process_revision_directives.

However, since branch labels apply to all downstream revisions this feels a bit unclear semantically, and as if we're relying on undefined behaviour? It also seems like this would get messy over time, though I'm not sure if this would actually cause any problems.

>>> [script for script in ScriptDirectory('.').walk_revisions()]
[Script('140731f2cb4b', '2ef283797ea2', branch_labels={'v1.78.0', 'v1.79.0'}),
 Script('55e8950bc829', '2ef283797ea2', branch_labels={'v1.78.0', 'v1.79.0'}),
 Script('2ef283797ea2', ('4a9cd0f3844c', '9300c463ce7d'), branch_labels={'v1.78.0', 'v1.79.0'}),
 Script('4a9cd0f3844c', '2186ee192ce5', branch_labels={'v1.78.0'}),
 Script('9300c463ce7d', '2186ee192ce5', branch_labels={'v1.78.0'}),
 Script('2186ee192ce5', ('91e81a0698ce', 'df3635e75327'), branch_labels={'v1.78.0'}),
 Script('91e81a0698ce', '9b1e08350ef2'),
 Script('df3635e75327', '9b1e08350ef2'),
 Script('9b1e08350ef2', '26cb3dbd4be8'),
 Script('26cb3dbd4be8', None)]

One consequence of using branch_label is that we'd need to get the version number from the source code (as you suggested), rather than walking backwards through revisions and using the first that qualifies as a release revision. For some reason I prefer the latter, but I don't really know how to justify this :)

I think we'll stick with using the message field to indicate release revisions, at least for the time being. IMO the cleanest solution would be some sort of boolean field to indicate that the revision is the new default branch point, so there would no longer be "magic values" involved, eg[RELEASE-REVISION]. (I know my create_release_revision.sh script includes the version number, but this part is not crucial, and could be left to the developer to specify in the message field.)

But honestly, I'm bikeshedding at this point. I'm really pleased to have removed the need for create_release_revision.py! We were already running into issues with devs forgetting or not knowing that they should use this script. The rest has all worked out pretty well.

For a cookbook entry, would you prefer the branch_label version, or would the message version suffice?

Feel free to close this, or turn it into an issue for the cookbook entry, or whatever you see fit.

Thanks again for your help with this!

[zzzeek]

Thanks, that did the trick!

alembic revision --head vX.Y.Z, seems to work as expected, as does script.head = 'vX.Y.Z' in process_revision_directives.

However, since branch labels apply to all downstream revisions this feels a bit unclear semantically, and as if we're relying on undefined behaviour? It also seems like this would get messy over time, though I'm not sure if this would actually cause any problems.

that should be, --head vX.Y.Z@head, it shouldn't work the other way once that revision is not a "head" anymore. I think the @head part makes this semantically clear, it means, "the head of the branch that has label vX.Y.Z somewhere in it".

One consequence of using branch_label is that we'd need to get the version number from the source code (as you suggested), rather than walking backwards through revisions and using the first that qualifies as a release revision. For some reason I prefer the latter, but I don't really know how to justify this :)

I think you can still walk through the revisions, get all the branch labels, then find the latest one using version number comparison schemes. if you're in env.py this structure should be present for programmatic access.

I think we'll stick with using the message field to indicate release revisions, at least for the time being. IMO the cleanest solution would be some sort of boolean field to indicate that the revision is the new default branch point, so there would no longer be "magic values" involved, eg[RELEASE-REVISION]. (I know my create_release_revision.sh script includes the version number, but this part is not crucial, and could be left to the developer to specify in the message field.)

you could...put it in both? im not sure if this means you arent actually using the branch_label thing or not.

But honestly, I'm bikeshedding at this point. I'm really pleased to have removed the need for create_release_revision.py! We were already running into issues with devs forgetting or not knowing that they should use this script. The rest has all worked out pretty well.

For a cookbook entry, would you prefer the branch_label version, or would the message version suffice?

the "message" version has all your custom scripts? so far from what I'm understanding , the "branch_label" version is the more canonical approach. Both "magic symbol in the message" and "branch label" are virtually the same thing structurally, that is, it's some variable inside the module scope of a revision script that says something. I don't see what advantage there is to embedding it in the message alone rather than using a branch_label.

Feel free to close this, or turn it into an issue for the cookbook entry, or whatever you see fit.

Thanks again for your help with this!

[eeshugerman]

that should be, --head vX.Y.Z@head, it shouldn't work the other way once that revision is not a "head" anymore. I think the @Head part makes this semantically clear, it means, "the head of the branch that has label vX.Y.Z somewhere in it".

I think there is some misunderstanding. From what I can tell, since branch_label applies to all downstream revisions, and in this workflow all heads get merged together periodically, and we add --branch-label at these merge revisions, the head(s) of any branch is/are always the same as alembic heads. Rather, we'd want to pass the "end of the tail" of a branch to alembic revision --head, and it seems this is what <branch name> points to. Here's what I'm looking at:

[I] ➜ alembic history     
2ef283797ea2 -> 140731f2cb4b (v1.79.0, v1.78.0) (head), empty message
2ef283797ea2 -> 55e8950bc829 (v1.79.0, v1.78.0) (head), empty message
4a9cd0f3844c, 9300c463ce7d -> 2ef283797ea2 (v1.79.0, v1.78.0) (branchpoint) (mergepoint), empty message
2186ee192ce5 -> 4a9cd0f3844c (v1.78.0), empty message
2186ee192ce5 -> 9300c463ce7d (v1.78.0), empty message
91e81a0698ce, 886eef8f0704, df3635e75327 -> 2186ee192ce5 (v1.78.0) (branchpoint) (mergepoint), empty message

[I] ➜ alembic show v1.78.0      
Rev: 2186ee192ce5 (branchpoint) (mergepoint)
Merges: 91e81a0698ce, 886eef8f0704, df3635e75327
Branches into: 9300c463ce7d, 4a9cd0f3844c
Branch names: v1.78.0
Path: /home/elliott/dwp/arthur/ltbd/alembic/versions/2020-02-10_2186ee192ce5_.py

    empty message
    
    Revision ID: 2186ee192ce5
    Revises: 91e81a0698ce, 886eef8f0704, df3635e75327
    Create Date: 2020-02-10 10:58:40.990448


[I] ➜ alembic show v1.79.0      
Rev: 2ef283797ea2 (branchpoint) (mergepoint)
Merges: 4a9cd0f3844c, 9300c463ce7d
Branches into: 140731f2cb4b, 55e8950bc829, 26165a492f2c
Branch names: v1.79.0, v1.78.0
Path: /home/elliott/dwp/arthur/ltbd/alembic/versions/2020-02-10_2ef283797ea2_.py

    empty message
    
    Revision ID: 2ef283797ea2
    Revises: 4a9cd0f3844c, 9300c463ce7d
    Create Date: 2020-02-10 11:58:43.738711


[I] ➜ alembic show v1.78.0@head
  FAILED: Multiple head revisions are present for given argument 'v1.78.0@head'; please specify a specific target revision, '<branchname>@v1.78.0@head' to narrow to a specific head, or 'heads' for all heads

[I] ➜ alembic show v1.79.0@head
  FAILED: Multiple head revisions are present for given argument 'v1.79.0@head'; please specify a specific target revision, '<branchname>@v1.79.0@head' to narrow to a specific head, or 'heads' for all heads

I think you can still walk through the revisions, get all the branch labels, then find the latest one using version number comparison schemes.

Since, the heads of any branch are the same as alembic heads (as described above), I don't think we can just find the revision with the max version number. But yeah, I shouldn't have said "consequence... need to" -- I think it can still be done, it would just be more complicated.

im not sure if this means you are actually using the branch_label thing or not.

Yeah, sorry, that wasn't very clear. For now at least I'm only using message.

the "message" version has all your custom scripts?

No, create_revision_branch.py is gone -- I moved the logic into env.py, modifying the behaviour of alembic revision (as shown in #656 (comment)). There's still create_release_revision.sh, but it's basically just one line:
alembic merge heads --message "[RELEASE-MERGE] <any message, could be version number>"

[zzzeek]

I went back to your original description:

they are to branch off of the most recent "release revision"

So diagrams would help here, I guess you are trying to have all your revisions come off the same parent. starting with:

A -> B -> C / v1.0.0 -> D -> E

"branch off of the most recent "release revision"", that means this:

A -> B -> C v/1.0.0 -> D 
                    -> D2
                    -> D3

etc., that is, all your revisions are non-linear and are off of v1.0.0, then you want to "merge them at some point":

A -> B -> C v1.0.0 -> D    -+--> E 
                    -> D2  -+
                    -> D3  -+

then a new release:

A -> B -> C v1.0.0 -> D    -+--> E -> F v1.1.0
                    -> D2  -+
                    -> D3  -+

now you want to branch off v1.1.0 each time:

A -> B -> C v1.0.0 -> D    -+--> E -> F v1.1.0 --+  G1
                    -> D2  -+                    +- G2
                    -> D3  -+                    +- G3

so, then yes, you don't want the @head part, you want to just have "v1.1.0" as your "head".

The way this is supposed to work, not accounting for bugs I'm not aware of (as indicated before, all of this stuff is very likely to have weird bugs in it still so I am not assuming it's working perfectly), is that, supposing revision a859bd has the branch label "v1.1.0" inside it, that means, it's in the a859bd.py python file. When you say, alembic revision --head "v1.1.0" that means, you want a859bd to be the ancestor of your new version. if you say alembic revision --head "v1.1.0@head", that means, "locate revision a859bd, then walk down all of its descendants until you get to the head, then that's your target ancestor". if it splits off, then it raises.

So basically, branch_label gives you 1. a symbolic indicator of a specific version and 2. a symbolic indicator that can be used to locate a singular branch head as well, if one exists.

I am not thinking of another possibility of something that can be "looked up".

[eeshugerman]

Yes! I think we're on the same page now. Sorry, I should have described the DAG structure more explicitly in my OP, and yeah, diagrams are helpful. Here, I made one (for my team's internal documentation, and for the eventual cookbook recipe, if that happens):

So as long as --head <branch name> will continue to point to the "start point" of a branch (ie, this is not undefined behaviour / an implementation detail), using branch_label for this purpose seems to work. The only issue I'm anticipating is basically aesthetic: over time, the branch labels pile up, and this can be seen in the output of alembic history:

2ef283797ea2 -> 140731f2cb4b (v1.79.0, v1.78.0) (head), empty message  # these lines get longer with each release
2ef283797ea2 -> 55e8950bc829 (v1.79.0, v1.78.0) (head), empty message
4a9cd0f3844c, 9300c463ce7d -> 2ef283797ea2 (v1.79.0, v1.78.0) (branchpoint) (mergepoint), empty message
2186ee192ce5 -> 4a9cd0f3844c (v1.78.0), empty message
2186ee192ce5 -> 9300c463ce7d (v1.78.0), empty message
91e81a0698ce, 886eef8f0704, df3635e75327 -> 2186ee192ce5 (v1.78.0) (branchpoint) (mergepoint), empty message

[zzzeek]

just as a hypothetical can you check if this diff causes history to only display a branch label if it's immediately present?

diff --git a/alembic/script/base.py b/alembic/script/base.py
index 81bded4..f5c3244 100644
--- a/alembic/script/base.py
+++ b/alembic/script/base.py
@@ -807,8 +807,8 @@ class Script(revision.Revision):
                 )
             else:
                 text = "%s -> %s" % (self._format_down_revision(), text)
-        if include_branches and self.branch_labels:
-            text += " (%s)" % util.format_as_comma(self.branch_labels)
+        if include_branches and self._orig_branch_labels:
+            text += " (%s)" % util.format_as_comma(self._orig_branch_labels)
         if head_indicators or tree_indicators:
             text += "%s%s%s" % (
                 " (head)" if self._is_real_head else "",

this would be the start of alembic beginning to distinguish between a branch label as a total-branch label vs. an immediate revision label. that is, your concern that this is not "expected" that a branch label also refers to a specific revision is something that should be solidified. I'm not sure to what degree the tests are asserting this right now (might be a lot though, haven't dealt with this area in some years).

[zzzeek]

yes can confirm this all works, when you make the new revision that is a new head, you specify the branch name as the --head, and then I'm assuming up above you are also using --splice since this is making a new "branch" from a non-head revision. The test at https://github.com/sqlalchemy/alembic/blob/master/tests/test_revision.py#L365 asserts that retrieving the branch label alone without an @ symbol treats it as a version tag.

[eeshugerman]

Cool! Yep, I do script.splice = True in process_revision_directives.

And yeah, come to think of it, it wouldn't be so hard at all to determine the branch point with this approach -- could be something like:

from distutils.version import StrictVersion
max(ScriptDirectory('.').get_heads().pop().branch_labels, key=StrictVersion)

Hopefully I will have a chance soon to write up the cookbook entry!

Do you still need me to test that diff, or did you try it out? Do you intend to commit it?

[zzzeek]

sure, I think I should commit something like that but I would have to take the time to review everywhere that kind of thing is happening, add tests, etc.

[pbecotte]

Out of curiosity- why would you want to do this (I have always worked in teams that are relatively free to update their practices, so miss out on some things). You decsribe the problem as

Our previous migration tool did not support branching, and trying to force a strictly linear migration history was proving to be a nightmare, especially in regards to moving code between environments.

I was hoping to understand more about the problems of "moving code between environments". I have never worked in anything outside of trunk based where the same code goes from dev > staging > prod. So was wondering what kinds of problems you're solving that requires branching of the database migrations? Also, how big does your team have to be that merging the migrations into a single trunk was so hard?

[zzzeek]

if someone wants to contribute, if we could take the patch in #656 (comment) and make it work like this:

1. the existing "history" display, the short non-verbose version, will show the branch labels like it does now, however, if there are more than two present, it will write them with an ellipses, that is:

2ef283797ea2 -> 140731f2cb4b (v1.79.0, v1.78.0, ...) (head), empty message
2. The listing of branch labels above should also be modified such that the names that are in "_orig_branch_names", e.g. the local tags, first.

3. we can also try to distinguish between "local tag" and "applicable branch" like this:

2ef283797ea2 -> 140731f2cb4b (**v1.79.0**, v1.78.0, ...) (head), empty message
4. for the verbose listing, as well as "alembic show", the "Branch names:" listing would still show all the applicable branches. I understand that is a little non-ideal for this specific use case but I think verbose should include all information.

5. in the verbose listing / "alembic show", the "tags" would have their own line: "Tags: " which show the listing of _orig_branch_names

6. we probably want to refer to _orig_branch_names internally as "tags".

[eeshugerman]

@pbecotte

Sure! Here's our Git flow, for reference:

---

how big does your team have to be

My team is 5 developers and together we add about 3 or 4 DB migrations per week. I think the frequency of migrations is a bigger factor than size of team.

---

why would you want to do this [...] I was hoping to understand more about the problems of "moving code between environments"

So let's say two developers start on separate features, at around the same time, and at this time head on QA is revision A. They will both open pull requests to QA with a new revision -- revision B in one PR, revision C in the other. Both B and C will have down_revision = A.

If both PRs get merged with no further changes, then A becomes a branchpoint in QA. This is OK as long as..

1. this situation is rare enough that it can be addressed adhoc, or
2. you have a strategy to keep the branching under control

The workflow described in this issue is in part just (2): a strategy to keep branching under control. But the workflow also uses branching by default, because branching can make it easier to move code between environments. How so? Because it avoids the following problems.

Lets say that we need to force a linear revision history. In this case, we'll have to merge either B or C first, and then update down_revision on the other before merging it. Let's say we merge B first, then C. Now, QA looks like this:

A -> B -> C

C passes QA before B, so it gets merged into STAGE first. The build (ie alembic upgrade) fails, because C has down_revision = B, and B doesn't exist in STAGE. To get the build to pass we change down_revision of C to A, and B to C in STAGE (or maybe we anticipated the problem, so we did this beforehand). Great, now the build passes, but STAGE has a different history than QA. It looks like this:

A -> C -> B

There will be merge conflicts when QA is synced with PROD after the next release. I supposed if the conflicts are always resolved to favour PROD, then the revision histories will be eventually consistent across environments. But even still, this process is far from ideal:

• we had to change down_revision on C to merge it into QA
• we had to change down_revision on B and C (again) to merge it into STAGE
• standard procedure should not cause merge conflicts
• in QA, the revision history as defined by the source code is at odds with the actual order in which revisions were executed against QA.

The "branch by default" workflow solves all of this. Revisions/features can be moved between environments independently of each other, with no extra steps, and revision history stays consistent.