Database migrations with multiple git branches and teams alembic sqlalchemy

This article is suitable for understanding database migrations when there are database migrations on different git branches which need to be merged. The examples are python's alembic project but the concept is useful for all

When doing branch based development (e.g. issue branches) and have database migration on two (or three..n) different branches, managing database migrations becomes hard. It results in multiple heads errors when working with alembic (python) but the concept (php artisan) is the same problem statement, which is:

Two or more developers independently working on different branches create database migrations.

In sqlalchemy, flask db upgrade or alembic upgrade wont work when there are multiple heads (in plain speak: Alembic doesn't know in which order to perform the migrations). The alembic project has a feature to merge database migrations, creating a new migrations, allowing you the developer to choose how to perform the migrations- deciding things such as order of operations, or if the database changes are independent of each other, simply combining the two changes into one migration.

For example

flask db history # or alebmic history

c751fe53a042 -> d7b1aaee84ab (head), add stripe_status to subscription modelc751fe53a042 -> c39dd6d1961f (head), add cancel_at to plan model

Above we have multiple heads, which were created on different branches (they appear now together because a git merge or a git rebase has happened, and bam you now need to decide how to combine these to migrations.

For example, using alembic merge:

flask db merge -m 'Merge c39 add cancel_at and d7b add stripe_status' c39dd6d1961f d7b1aaee84ab

  Generating
  /docs/subscribie/migrations/versions/b28487bfca0f_merge_c39_add_cancel_at_and_d7b_add_.py ...  done

Alembic automatically generates a new migration, linking the two migrations:

• Notice the "down_revision" has both  c39dd6d1961f and d7b1aaee84ab

# File: b28487bfca0f_merge_c39_add_cancel_at_and_d7b_add_.py

"""Merge c39 add cancel_at and d7b add stripe_status

Revision ID: b28487bfca0f
Revises: c39dd6d1961f, d7b1aaee84ab
Create Date: 2021-05-03 14:57:43.633763

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b28487bfca0f'
down_revision = ('c39dd6d1961f', 'd7b1aaee84ab')
branch_labels = None
depends_on = None


def upgrade():
    pass


def downgrade():
    pass

Now flask db history or alembic history shows the new revision:

c39dd6d1961f, d7b1aaee84ab -> b28487bfca0f (head) (mergepoint), Merge c39 add cancel_at and d7b add stripe_status
c751fe53a042 -> c39dd6d1961f, add cancel_at to plan model
c751fe53a042 -> d7b1aaee84ab, add stripe_status to subscription model

... and flask db upgrade  ( or alembic upgrade ) can succeed:

INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> 702d6ee9b14f, compact all migrations
INFO  [alembic.runtime.migration] Running upgrade 702d6ee9b14f -> 790aae5a7013, add File model
INFO  [alembic.runtime.migration] Running upgrade 790aae5a7013 -> 3abcbb0428ef, add stripe_connect_account_id to PaymentProvider model
INFO  [alembic.runtime.migration] Running upgrade 3abcbb0428ef -> f0e91df9fbf1, add stripe_livemode to payment_provider
INFO  [alembic.runtime.migration] Running upgrade f0e91df9fbf1 -> 2c7e021d9a69, remove stripe_publishable_key & stripe_secret_key from model
INFO  [alembic.runtime.migration] Running upgrade 2c7e021d9a69 -> c5d444ee3ccd, rename stripe webhook and account cols payment_provider
INFO  [alembic.runtime.migration] Running upgrade c5d444ee3ccd -> 56e852d799d1, add stripe webhook test columns
INFO  [alembic.runtime.migration] Running upgrade 56e852d799d1 -> 686d588c3c29, remove stripe test webhooksecret and id
INFO  [alembic.runtime.migration] Running upgrade 686d588c3c29 -> ee8e62e05b40, add back stripe test mode colums
INFO  [alembic.runtime.migration] Running upgrade ee8e62e05b40 -> 1653ed33cbd4, add subscribie_checkout_session_id to Subscription
INFO  [alembic.runtime.migration] Running upgrade 1653ed33cbd4 -> 9189f7033477, add private boolean to pages model
INFO  [alembic.runtime.migration] Running upgrade 1653ed33cbd4 -> 7d502ba7279c, add stripe_subscription_id to Subscription model
INFO  [alembic.runtime.migration] Running upgrade 7d502ba7279c -> f3579efd3331, add stripe_external_id to Subscription model
INFO  [alembic.runtime.migration] Running upgrade f3579efd3331 -> 07cc236f0a6d, remove stripe_subscription_id from Subscription model
INFO  [alembic.runtime.migration] Running upgrade 07cc236f0a6d -> 75a87d5ab587, add back stripe_subscription_id to Subscription model
INFO  [alembic.runtime.migration] Running upgrade 75a87d5ab587, 9189f7033477 -> 98099ff3eb9c, merge heads
INFO  [alembic.runtime.migration] Running upgrade 98099ff3eb9c -> 8d9ada7a21cd, add description to plan model
INFO  [alembic.runtime.migration] Running upgrade 8d9ada7a21cd -> 6cc0e87e8836, add bg-primary to ModuleStyle model
INFO  [alembic.runtime.migration] Running upgrade 6cc0e87e8836 -> fd28aefcdb76, add css_properties_json remove bg_primary column
INFO  [alembic.runtime.migration] Running upgrade fd28aefcdb76 -> 500f2d55c5d3, add LoginToken model
INFO  [alembic.runtime.migration] Running upgrade 500f2d55c5d3 -> 2f3f0b5d2bde, add archived to Person class
INFO  [alembic.runtime.migration] Running upgrade 2f3f0b5d2bde -> 53840eddbb0f, add TaxRate model
INFO  [alembic.runtime.migration] Running upgrade 53840eddbb0f -> 796ff2e47e13, add charge_vat to Setting model
INFO  [alembic.runtime.migration] Running upgrade 796ff2e47e13 -> e0919a39645f, add stripe_livemode column to TaxRate model
INFO  [alembic.runtime.migration] Running upgrade e0919a39645f -> b8e926d239f9, create category table
INFO  [alembic.runtime.migration] Running upgrade b8e926d239f9 -> 746b4765a957, so that categories may be ordered
INFO  [alembic.runtime.migration] Running upgrade 746b4765a957 -> d04243b7bd47, add custom_code to Setting model
INFO  [alembic.runtime.migration] Running upgrade d04243b7bd47 -> 21b64f9d73dd, add trial_period_days to Plan model
INFO  [alembic.runtime.migration] Running upgrade 21b64f9d73dd -> b767faeb4c0d, add private to plan model
INFO  [alembic.runtime.migration] Running upgrade b767faeb4c0d -> c751fe53a042, add external_refund_id to transaction model
INFO  [alembic.runtime.migration] Running upgrade c751fe53a042 -> d7b1aaee84ab, add stripe_status to subscription model
INFO  [alembic.runtime.migration] Running upgrade c751fe53a042 -> c39dd6d1961f, add cancel_at to plan model
INFO  [alembic.runtime.migration] Running upgrade c39dd6d1961f, d7b1aaee84ab -> b28487bfca0f, Merge c39 add cancel_at and d7b add stripe_status