Document how to do data migrations

[abitrolly]

Describe the use case

It is hard to discover how to insert data in Alembic migrations. This is partially described in https://alembic.sqlalchemy.org/en/latest/ops.html?highlight=bulk_insert#alembic.operations.Operations.bulk_insert with an example of newly created table, but doesn't cover things more.

• insert data for already existing model
• loading the data from external file
  • where to place the file
  • how to find the path to it
  • load data from JSON without manual looping and field mapping

Additional context

#970

Have a nice day!

[zzzeek]

I dont have any fixed method of doing data migrations with alembic, this is something that people need to come up with depending on their project's requirements. I don't have the content to produce such a section so in this discussion if folks want to capture things that are safe to document (that is, won't mislead people into a poor / rigid architecture for which they blame us and post more bugs) we can maybe figure something out.

[CaselIT]

That use case does not seem particularly hard to write in a migration:

Just create an empty migration, then do something like this

def upgrade():
  session = Session(op.get_bind())
  populate_db(session)

def downgrade():
  session = Session(op.get_bind())
  session.query(Sponsor).delete()

[abitrolly]

Not clear where Session comes from here. It is an import from warehouse.db, where Session = sessionmaker() where it is from sqlalchemy.orm import sessionmaker.

Not clear what op.get_bind(does).

Also, not clear that Sponsor is from warehouse.sponsors.models import Sponsor.

[zzzeek]

At https://github.com/pypa/warehouse/pull/9512/files#diff-b912d97d6c38546cdc81c0c2cc35833412c3b8ccd46a62ac5767f030a35bef73 (expand diff for warehouse/cli/sponsors.py) is a real world example of what people come up with instead of writing a proper Alembic migration. Any example how they could load the same data into the table would be better than nothing.

OK there's a disconnect here which is that alembic migrations are designed for schema migrations first and foremost. The nature of data migrations are inherently different and it's not in fact possible in the general case to write data migrations that integrate with Alembic's schema versioning model. For example, downgrades. Being able to "downgrade" a data migration is first off extremely difficult, and in many if not most non-trivial cases, is impossible, as a data migration might be throwing away old data that's no longer needed. So the first thing we would write is, "you no longer can support downgrades". That is of course a sweeping statement for which I can't actually make, because maybe a certain kind of project has only certain kinds of data migrations that do support downgrades, etc. So I dont want to say that.

Next, in my own real world experience, I have observed that data migrations dont fit in the schema migration model at all. That is, data migrations may very well need to be completely separate and outside of schema migrations totally, as in my own situation where I had to deal with this, the data migrations consisted of extracting data from old tables, populating it into an entirely new ORM model, and then flushing the whole thing. That is, the data migration was handled in a way that took advantage of the application business logic at the current version rather than trying to weld itself into alembic migrations, and consisted of scripts in a different directory that were run based on particular application versions. This is also extremely dependent on the kind of data and the kind of data migration one is doing.

still another way to do data migrations is what we call "online migrations", where the application itself maintains both versions of a schema at once and writes data into both places for a time while background scripts work to move all the remaining data across. this is what a lot of Openstack applications do. It's an extremely challenging technique, downgrades are out of the question, and it also renders lots of upgrade styles no longer feasible either without breaking up the process into very specific structures that proceed over major application version updates.

I can continue to think of more and more reasons why data migrations really don't belong in schema upgrades at all, in many cases, but always with exceptions for some kinds of data migrations that do, etc.

Basically data migrations are a vastly bigger topic than Alembic itself, and Alembic does not target "data migrations" as a use case, so I would disagree with the initial assertion that "data migrations should be in a 'proper' migration"; if this was a use case we had a very clean and consistent story for, it would have been documented.

so any section regarding "data migrations" would not actually show any "here's how to structure your alembic migrations to do data migration", it would be more along the lines of, "this is a hard, open-ended problem that is highly dependent on what you need to do, here are some blog posts on how people have approached this problem for different kinds of sitautions".

in other words, we don't have prescriptive advice here and a fully prescriptive solution is out of scope for the Alembic project.

[CaselIT]

Not clear where Session comes from here. It is an import from warehouse.db, where Session = sessionmaker() where it is from sqlalchemy.orm import sessionmaker.

from sqlalchemy.orm import Session

Not clear what op.get_bind(does).

get_bind

Also, not clear that Sponsor is from warehouse.sponsors.models import Sponsor.

yes, that's the one

[CaselIT]

this is a hard, open-ended problem that is highly dependent on what you need to do, here are some blog posts on how people have approached this problem for different kinds of sitautions".

I agree in general with what you have written, but I've found migration work well for "data migration" in two particular cases:

• adding some initial seed data to newly created models/tables/whatever that's statically defined in the code (or json or similar committer with the code)
• migrating data from one format to another one, without creating or discarding informations that were not obtainable in some other way.

Both of these cases have the characteristic of more or less behaving like a schema migration, allowing to be played back and forth whithout much trouble, especially in the first case.

The example we are talking about seems to be an instance of the first case

[ziima]

I recently come around this issue when I needed to change the column type on a large table. I come out with the following workflow:

1. Alembic migration which adds the new column
2. Separate script which fills up the new column (possibly using a separate model definitions)
3. Alembic migrations which sets up new column constraints/deletes old column/cleans up etc.

Based on @zzzeek's comment above I guess that's an expected approach. Would it be possible to add at least this suggestion into the docs? It takes really some time to find this is actually expected solution.

[zzzeek]

we can paraphrase my open-ended essay I suppose, in documentation form. someone would need to find the best spot

[CaselIT]

Would it be possible to add at least this suggestion into the docs?

feel free to open a PR with suggestions!

[Andrew-Cha]

A while later I recommend doing this methodology. To aid in the second step, define the separate models inside of each script, this will avoid revisions lacking models that were (or would be) deleted in a future commit.

[Vortexdude]

How can I apply these migrations when I have the bash file that

1. create the required table
2. populate the data into there

#! /usr/bin/env bash

# Let the DB start
#python3 app/pre_start.py

# Run migrations
alembic upgrade head

# run the server
python3 app/server.py

[Skorpyon]

How can I apply these migrations when I have the bash file that

alembic upgrade head will apply all unapplied migrations. So somewhere in schema-migrations list you should have data migration. Basic way is just add migration with upgrade section containing SQL like INSERT INTO ... VALUES ....

Please remember zzzeek's point that it almost impossible to downgrade things like this, but most frequent usage of a data migrations is seeding test DB before testing.