Database schema changes are hard (2017)

#1

Post by djrobstep » Sat, Mar 02, 2019, 1:31 AM UTC

Database schema changes are hard (2017)
djrobstep.com

Re: Database schema changes are hard (2017)

#2

Post by michael_j_ward » Sat, Mar 02, 2019, 3:20 AM UTC

Link to the talk: https://www.youtube.com/watch?v=xr498W8oMRo

Re: Database schema changes are hard (2017)

#3

Post by bagels » Sat, Mar 02, 2019, 3:23 AM UTC

So how would you deal with continuous deployment to test but less frequent deployment to production? In test you might make multiple sequential schema changes before deploying to production. Don't you just end up in the same place as with migrations?

How about multiple engineers making schema changes simultaneously?

How is a diff really much different from a migration?

Re: Database schema changes are hard (2017)

#4

Post by brianwawok » Sat, Mar 02, 2019, 3:29 AM UTC

Neat.

I generally like Django migrations. A few rough spots. This seems like it could help some of them, but would take s lot of work to wire it in.

Re: Database schema changes are hard (2017)

#5

Post by djrobstep » Sat, Mar 02, 2019, 3:39 AM UTC

bagels wrote: post #3

So how would you deal with continuous deployment to test but less frequent deployment to production? In test you might make multiple sequential schema changes before deploying to production. Don't you just end up in the same place as with migrations? How about multiple engineers making schema changes simultaneously? How is a diff really much different from a migration?

Author here. Where I've used this in a CI environment, the migration step goes from:

- check schema version number, if newer version exists, apply migration files

to:

- check schema on this branch matches production schema exactly, if it doesn't, look for (one or more) pending migration files that would bring production to target state, apply those files

So you can still have multiple migration files as needed. The differences:

- no version numbers

- no long migration chain (you can clean up old pending files once they've hit prod)

- migrations are tested directly, and only get applied if a correct outcome will result

Re: Database schema changes are hard (2017)

#6

Post by djrobstep » Sat, Mar 02, 2019, 3:44 AM UTC

brianwawok wrote: post #4

Neat. I generally like Django migrations. A few rough spots. This seems like it could help some of them, but would take s lot of work to wire it in.

Because this works directly at the database level, it shouldn't need any "wiring in" to django itself, to use it with django.

I don't use django, but all that is required is for django to be able to initialize an empty database based on its model definition.

Then you can just use that initialized database independently as a target from which to generate migration scripts.

Re: Database schema changes are hard (2017)

#7

Post by fbonetti » Sat, Mar 02, 2019, 4:05 AM UTC

djrobstep wrote: post #5

bagels wrote: post #3

So how would you deal with continuous deployment to test but less frequent deployment to production? In test you might make multiple sequential schema changes before deploying to production. Don't you just end up in the same place as with migrations? How about multiple engineers making schema changes simultaneously? How is a diff really much different from a migration?

Author here. Where I've used this in a CI environment, the migration step goes from: - check schema version number, if newer version exists, apply migration files to: - check schema on this branch matches production schema exactly, if it doesn't, look for (one or more) pending migration files that would bring production to target state, apply those files So you can still have multiple migration files as needed. The d…

How would you handle a situation where the underlying data needs to be transformed? For example, converting a text column to an int column?

Re: Database schema changes are hard (2017)

#8

Post by xrd » Sat, Mar 02, 2019, 4:09 AM UTC

Thanks for saying mongo sucks. I'll be quoting that.

Brilliant tool. I totally get it.

Re: Database schema changes are hard (2017)

#9

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

fbonetti wrote: post #7

djrobstep wrote: post #5

Earlier quoted context omitted.

Author here. Where I've used this in a CI environment, the migration step goes from: - check schema version number, if newer version exists, apply migration files to: - check schema on this branch matches production schema exactly, if it doesn't, look for (one or more) pending migration files that would bring production to target state, apply those files So you can still have multiple migration files as needed. The d…

How would you handle a situation where the underlying data needs to be transformed? For example, converting a text column to an int column?

In that particular example, migra will detect that change and generate the right `alter column` statement with a `using` clause.

It's impossible to handle every case, of course, such as when renames happen or data needs to be moved/inserted.

Migration scripts always need reviewing. Tools can get you most of the way there automatically, and help you test, but not all of the way.

Re: Database schema changes are hard (2017)

#10

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

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?