Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Database schema changes are hard (2017)

djrobstep.com

Post reply on HN
Print view View original

71–80 of 93 posts

Re: Database schema changes are hard (2017)

#71

Post by recursive »

djrobstep wrote: post #28
recursive wrote: post #24

Earlier quoted context omitted.

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.

I don't believe a tool can always generate the correct statements. But I do believe those same statements can be used on all deployments, which I consider automation.

Re: Database schema changes are hard (2017)

#72

Post by paulryanrogers »

ddebernardy wrote: post #47

Interesting tool, but anecdotally the difficult part about database migrations has very little to do with the actual schema changes. Rather, it has to do with: 1. Ensuring the app works properly with the old schema as well as the new one -- not hard by itself, but requires rigor. 2. Minimizing locking during the migration itself. That right there actually is the much trickier part of migrations, because sometimes you…

In the MySQL family there are several tools which help: pt-online-schema-change, gh-ost, etc. PostgreSQL and others have their own techniques and tools.

Re: Database schema changes are hard (2017)

#73

Post by paulryanrogers »

djrobstep wrote: post #49
emptysea wrote: post #11

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.django…

Just create a migration script that mixes (autogenerated) sql and the python you need.

I thought that was the point of migrations in Ruby for Rails, PHP for Laravel, etc. Because raw SQL often lacks the tools to express and coordinate the changes.

Re: Database schema changes are hard (2017)

#74

Post by wolco »

paulryanrogers wrote: post #66
wolco wrote: post #62

Earlier quoted context omitted.

A nosql database schema change would force you to update every document requiring the change. I would say rdms schemas are straight forward. Remove constrants, alter tables/move data, add constrants. Changing a nosql schema going forward is simple. Adding new columns is equally simple. Deleting columns: rdms is much easier. Updating rdms is simplier.

With RDBMS the integrity constraints can make gradually changes to large data sets more difficult. It often means a lot of downtime or the code must tolerate old and new structures for a while. With NoSQL the constraints are probably already in the application layer.

I usually remove db constraints and implement constrains at the application level for both types.

Re: Database schema changes are hard (2017)

#75

Post by paulryanrogers »

tommilukkarinen wrote: post #40

When starting to use mongo it felt like a silver bullet. Reading this reminds me that it still is. I kind of get the point of schemas etc. but if your product can work with mongo, you just might avoid all these pains.

Does Mongo support ACID nowadays? And if data is mostly relational it still seems like overkill to me.

Re: Database schema changes are hard (2017)

#76

Post by ken »

pytester wrote: post #58
sametmax wrote: post #52

Earlier quoted context omitted.

That's assuming changing the schema is just about changing the layout of the database, which is definitly not true. You have your code depending on it, documentation, constrainsts, replication, caching, db load, deployement systems, different envs and versions... The SQL is the easy part and barely registers as an issue. I see the same problem with SQL lovers rejecting ORMs as a dumb way to avoid using the right tool…

ORM are frameworks that exist mainly as a way to avoid writing and maintaining thousands of lines of boilerplate. Like most frameworks (i.e. packages that force you to write your code to conform to their patterns) a really good one confers a massive boost in productivity while a really bad one is horrifying to work - it fails in bizarre unexplainable ways and straitjackets your development. IME, passionate rejection…

Blaming it all in “really bad ORMs” without naming any sounds suspect to me. Can you name some good ones?

Re: Database schema changes are hard (2017)

#77

Post by simonw »

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 don't buy this at all. It may be true for projects with only a single developer, but consider environments with a larger team.

First, every developer has a development environment. If someone goes on holiday for a few weeks they will come back to a personal environment that's likely several steps behind everyone else.

Smart engineering orgs have one or (hopefully) more QA environments which differ from production by design - they're where you preview and test upcoming features that may involve schema changes.

At a certain scale individual teams may have their own dedicated QA environments, for testing in-development features without disrupting the work of other teams.

Then there are environments for running integration tests, hooking up to CI systems, load testing etc.

There may be only one production environment but there could be dozens or even hundreds of other environments that need to be able to reliably apply migrations up to a specific point.

The Django approach to migrations handles this really well.

Re: Database schema changes are hard (2017)

#78

Post by simonw »

twic wrote: post #57
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.

> In many (most?) cases people have a single production environment. No idea why this was downvoted, because this is a key truth. You need to be able to migrate the production database to the latest version without losing any data. You need to be able to replace any non-production database with the same schema as production. Ideally, with a (sanitised, subsetted, etc) copy of its data. You don't need to be able to do…

See my comment here about organizations with many engineers and teams: https://news.ycombinator.com/item?id=19289778

Re: Database schema changes are hard (2017)

#79

Post by ajessup »

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

The author's point is not that schema changes are hard - but rather that versioning schema changes are hard.

Re: Database schema changes are hard (2017)

#80

Post by l33tman »

ken wrote: post #76
pytester wrote: post #58

Earlier quoted context omitted.

ORM are frameworks that exist mainly as a way to avoid writing and maintaining thousands of lines of boilerplate. Like most frameworks (i.e. packages that force you to write your code to conform to their patterns) a really good one confers a massive boost in productivity while a really bad one is horrifying to work - it fails in bizarre unexplainable ways and straitjackets your development. IME, passionate rejection…

Blaming it all in “really bad ORMs” without naming any sounds suspect to me. Can you name some good ones?

Django's ORM has always served me well, it handles migrations and I've had almost zero SQL-hacks that needed to be done.
Post reply on HN