Data Migration approaches with Alembic and SQLAlchemy

Question

I've been fiddling with SQLAlchemy + Alembic for around 2 days and need some guidance. I know Alembic is for schema migrations and the autogenrate stuff is awesome but my question lies now with how do data migrations work?

An example: I create a table and I want to insert data into that table in an automated way (upgrade & downgrade). Ideally when someone updates to the latest migration all this is done for the developer.

From my research I can either:

Write normal code that updates the tables, insert, delete data but I loose automation efforts here, i.e. the developer would have to run a script that inserts the data, there isn't really an option to do rollbacks etc. The use of ORM stuff is nice and cleaner than operations though. So something like this:
```
def create_users():
       with session_maker() as session:
         for user in users:
          session.add(user)
       session.commit()
```

I can make use Alembic operations like bulk_insert, batch_alter_table etc. With this approach the developer would just need to run the latest migrations but the use of Alembic operations seems clunky, the functions themselves aren't as shiny as an ORM approach.

    op.bulk_insert(accounts_table,
    [
        {'id':1, 'name':'John Smith',
                'create_date':date(2010, 10, 5)},
        {'id':2, 'name':'Ed Williams',
                'create_date':date(2007, 5, 27)},
        {'id':3, 'name':'Wendy Jones',
                'create_date':date(2008, 8, 15)},
    ]
)

Have a hybrid approach and use ORM stuff inside of a migration? So something like this:


    session_maker = sessionmaker(bind=create_engine(db_url))

    business_units = [
        BusinessUnits(name="Marketing"),
        BusinessUnits(name="Sales"),
        BusinessUnits(name="Software"),
    ]

    with session_maker() as session:
        for unit in business_units:
            session.add(unit)
        session.commit()


def upgrade() -> None:
    create_business_units()
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    pass
    # ### end Alembic commands ###```

Questions:

Is there something wrong with the 3rd approach because why would option 2 exist? Approach 3 seems to be the best of both worlds.
What is the best approach 1,2,3 or is there no best approach and it varies from my to place? I understand this is subjective but there must be some sort of measuring stick used to determine one approach over the other?

Please bear in mind I'm new to this and I know this is subjective but ya... just need some guidance and perspective. Not too sure where else to post / ask this question.

Data Migration approaches with Alembic and SQLAlchemy

0 Answers0