Database schema changes are hard (2017) • Hacker News Board

Re: Database schema changes are hard (2017)

#11

Post by emptysea » Sat, Mar 02, 2019, 4:17 AM UTC

Most of my migrations with Django are column renames, column adds, deletions, etc., which seem to fit nicely with this tool, but there are some cases where I want to do some more complex transformation which require running a function using Django's `RunPython()`[0]. For example, combining `firstName` and `lastName` columns into a single `name` column.

How would these changes be accomplished?

[0]: https://docs.djangoproject.com/en/2.1/ref/migration-operatio...

Re: Database schema changes are hard (2017)

#12

Post by robben1234 » Sat, Mar 02, 2019, 4:22 AM UTC

How to do data migrations this way? Let's say you have a field for statuses (int) and want to add a new option in the middle of array [(1,a),(2,b),(3,d)] => [(1,a),(2,b),(3,c),(4,d)]. Where do you do Model.objects(status=3).update(status=4)?

Also, at least for Django, I can't see real harm with schema versioning via files with ids. You can pretty easily merge everything to one file once you have 100 or whatever migrations in any app.

Re: Database schema changes are hard (2017)

#13

Post by jjeaff » Sat, Mar 02, 2019, 4:26 AM UTC

So how do you deal with it when you actually need to "migrate" data?

Getting a correct schema is only part of the migration process. Many times a refactoring requires a new table or field to be populated with data from the old table or field.

For example, a single table has a one to one relationship that we must select from to insert data into a newly created table so we can have a one to many relationship.

Re: Database schema changes are hard (2017)

#14

Post by djrobstep » Sat, Mar 02, 2019, 4:27 AM UTC

jayd16 wrote: post #10

The reason you keep a migration chain is so you have a clear path from anything in the wild to the latest. Unless I'm misunderstanding, throwing this away means you need to migrate some unknown permutation to latest. The hardest part of the migration is migrating the data and as far as I can tell that is glossed over. Why is throwing away the iterative migration version useful exactly?

In many (most?) cases people have a single production environment.

If you have multiple versions in the wild to support, there's nothing stopping you from supporting multiple upgrade paths, or even continuing to chain migration files, if you want to.

Moving data about is always going to be a manual process. This approach just helps you test it better.

Re: Database schema changes are hard (2017)

#15

Post by paulddraper » Sat, Mar 02, 2019, 4:33 AM UTC

jjeaff wrote: post #13

So how do you deal with it when you actually need to "migrate" data? Getting a correct schema is only part of the migration process. Many times a refactoring requires a new table or field to be populated with data from the old table or field. For example, a single table has a one to one relationship that we must select from to insert data into a newly created table so we can have a one to many relationship.

Program: code, dockerfile, Kubernetees config

Persistent data: databases tables, disk snapshots

program != persistent data

Re: Database schema changes are hard (2017)

#16

Post by jayd16 » Sat, Mar 02, 2019, 4:42 AM UTC

djrobstep wrote: post #14

jayd16 wrote: post #10

The reason you keep a migration chain is so you have a clear path from anything in the wild to the latest. Unless I'm misunderstanding, throwing this away means you need to migrate some unknown permutation to latest. The hardest part of the migration is migrating the data and as far as I can tell that is glossed over. Why is throwing away the iterative migration version useful exactly?

In many (most?) cases people have a single production environment. If you have multiple versions in the wild to support, there's nothing stopping you from supporting multiple upgrade paths, or even continuing to chain migration files, if you want to. Moving data about is always going to be a manual process. This approach just helps you test it better.

I would say this is a pretty limited view. If you're working on a large team with many non production environments, this style is far more cumbersome than the upgrade chain method.

If we're talking about deployed sqlite dbs running on client machines, expecting an upgrade to be a manual process is simply not acceptable.

Re: Database schema changes are hard (2017)

#17

Post by mdonahoe » Sat, Mar 02, 2019, 4:43 AM UTC

djrobstep wrote: post #14

jayd16 wrote: post #10

The reason you keep a migration chain is so you have a clear path from anything in the wild to the latest. Unless I'm misunderstanding, throwing this away means you need to migrate some unknown permutation to latest. The hardest part of the migration is migrating the data and as far as I can tell that is glossed over. Why is throwing away the iterative migration version useful exactly?

In many (most?) cases people have a single production environment. If you have multiple versions in the wild to support, there's nothing stopping you from supporting multiple upgrade paths, or even continuing to chain migration files, if you want to. Moving data about is always going to be a manual process. This approach just helps you test it better.

What about apps running on users’ devices, with their own internal databases for local storage?

When a user on random old version finally updates their app to latest, that latest code better be able to handle a migration correctly.

Re: Database schema changes are hard (2017)

#18

Post by wvenable » Sat, Mar 02, 2019, 4:46 AM UTC

jayd16 wrote: post #10

The reason you keep a migration chain is so you have a clear path from anything in the wild to the latest. Unless I'm misunderstanding, throwing this away means you need to migrate some unknown permutation to latest. The hardest part of the migration is migrating the data and as far as I can tell that is glossed over. Why is throwing away the iterative migration version useful exactly?

I agree but I also have migrations that don't correspond to any release of the product. You need to run a migration to update your database so you can test your changes. A single product release might contain hundreds of migration files; maybe some that simply undo the affect of some previous migration. The pain of that is real but your point is valid.

I like this product but I agree that it doesn't solve all migration issues. I do think we need better migration tools.

Re: Database schema changes are hard (2017)

#19

Post by lukevp » Sat, Mar 02, 2019, 4:54 AM UTC

Thanks for the post. I agree that schema migrations could be improved, and am also working on a tool to address this space. Why is your tool specific to PostGres? Does it perform migrations or just generate the scripts? Am I understanding correctly that you compare 2 live databases together? I.e. prod compared to dev, and a diff script will be created with a create table because my dev database has additional table "foo" in it that doesn't exist in prod?

Re: Database schema changes are hard (2017)

#20

Post by djrobstep » Sat, Mar 02, 2019, 4:59 AM UTC

jayd16 wrote: post #16

djrobstep wrote: post #14

Earlier quoted context omitted.

In many (most?) cases people have a single production environment. If you have multiple versions in the wild to support, there's nothing stopping you from supporting multiple upgrade paths, or even continuing to chain migration files, if you want to. Moving data about is always going to be a manual process. This approach just helps you test it better.

I would say this is a pretty limited view. If you're working on a large team with many non production environments, this style is far more cumbersome than the upgrade chain method. If we're talking about deployed sqlite dbs running on client machines, expecting an upgrade to be a manual process is simply not acceptable.

What makes it more cumbersome?