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

Re: Database schema changes are hard (2017)

#21

Post by jayd16 » Sat, Mar 02, 2019, 5:04 AM UTC

djrobstep wrote: post #20

jayd16 wrote: post #16

Earlier quoted context omitted.

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?

If I'm understanding correctly, the suggestion is to run the migration by hand and handle issues as they arise. Not nearly as nice as an automated migration.

Re: Database schema changes are hard (2017)

#22

Post by gpm » Sat, Mar 02, 2019, 5:28 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.

Backups...

Re: Database schema changes are hard (2017)

#23

Post by recursive » Sat, Mar 02, 2019, 6:11 AM UTC

paulddraper wrote: post #15

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

But a particular version of a program expects a certain schema in the persistent data store. So when the program gets upgraded, the persistent data store must be migrated, unless you're able to lose all your data. In that case, you don't need to care about migrations, and it's much easier.

Re: Database schema changes are hard (2017)

#24

Post by recursive » Sat, Mar 02, 2019, 6:14 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.

You just got done telling me that migrations are bad, but also the solution to some non-trivial deployment scenario is to just continue using migrations?

> Moving data about is always going to be a manual process.

No, it's not always.

Re: Database schema changes are hard (2017)

#25

Post by petepete » Sat, Mar 02, 2019, 6:43 AM UTC

robben1234 wrote: post #12

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 migra…

You can take the multiple migrations approach and for a short time keep two status fields while you change over, eventually dropping the first and renaming the second.

Or, as most people would do it, within a transaction add the new value to your enum and run an update. Of course, this can't be reversed but if it's a simple `set status = 3 where status = 4` I don't see the problem.

Re: Database schema changes are hard (2017)

#26

Post by jasonkester » Sat, Mar 02, 2019, 6:45 AM UTC

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard.

If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (and keep two databases versions up to date just for that purpose). You adopt entire schemaless databases so that you never need to change the schema.

But that's silly. Because Database Schema Changes are Not Hard.

You do the thing the author is scared of: SQL Change Scripts. Insert column, massage the data, flip on the null constraint, add relationships. It's all really basic stuff, and if you don't know the SQL syntax for it you can just ask the db tool you're using to make the change. It'll have a little "Script This Out" button next to the Save button.

If you do that, then you get to live in a world where Database Schema Changes are Easy. You get to have a build that just runs new change scripts in order rather than involving Ruby or some wacky 3rd party tool.

And you can move as fast as you like.

Re: Database schema changes are hard (2017)

#27

Post by YouAreARetard » Sat, Mar 02, 2019, 6:59 AM UTC

jasonkester wrote: post #26

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (…

Database schema changes are not hard in terms of complexity, but if you make a mistake it can be very costly.

You seem to be a complete idiot who has no experience in anything.

Re: Database schema changes are hard (2017)

#28

Post by djrobstep » Sat, Mar 02, 2019, 7:03 AM UTC

recursive wrote: post #24

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.

You just got done telling me that migrations are bad, but also the solution to some non-trivial deployment scenario is to just continue using migrations? > Moving data about is always going to be a manual process. No, it's not always.

Did you even read it? No idea where you got "migrations are bad" from.

If moving data around within a migration isn't always a manual process, please show me the tool which automatically generates the correct statements.

Re: Database schema changes are hard (2017)

#29

Post by djrobstep » Sat, Mar 02, 2019, 7:08 AM UTC

jasonkester wrote: post #26

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (…

I'm not sure you read the talk, which is literally about creating SQL change scripts?

Re: Database schema changes are hard (2017)

#30

Post by jasonkester » Sat, Mar 02, 2019, 7:20 AM UTC

djrobstep wrote: post #29

jasonkester wrote: post #26

So much of what is bad about tooling comes from this one assumption: Database schema changes are hard. If changing the schema is hard, then you come up with silly rules about when the schema can change and who can do it. You make migration tools in other languages to avoid writing the line of SQL that would change the schema. You use 3rd party tools to compare two databases and spit out change scripts automatically (…

I'm not sure you read the talk, which is literally about creating SQL change scripts?

I did. Its main piece of advice is to stop writing sequential change scripts, and to instead only keep "empty", "dev", "live" schema scripts or similar, and to rely on a tool to sort out changes between them.

... which introduces all the problems that everybody here is talking about. Thus my comment above advocating not doing that.