Hacker News Reader

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 for the job.

But using an ORM is not about avoiding to write SQL. It's about all the rest: code introspection, data validation, documententation, having a common api, tooling, etc

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 of ORMs predominantly comes from people who have been scarred by experience with really bad ORMs.

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.

For example JPOX was an ORM that almost put me off the entire idea. It was horrendous.

Recently I've used Django ORM and SQLAlchemy - both are warty but generally decent and better than not using any ORM.

This is somewhat language dependent - certain language qualities inhibit the creation of decent ORMs (e.g. it's not really possible to create a decent ORM in golang due to its design).

I've had bad luck with ORMs in general, mainly because they fall apart whenever I need to do something complicated. The abstraction is just too leaky.

However, I've had really good luck with Doobie, a purely functional SQL library in Scala. The big difference is that queries can be manipulated as ordinary pure values, which really lets you do some cool stuff. I actually used these features to significantly speed up a schema change in a large database I was working on.

https://tpolecat.github.io/doobie/docs/01-Introduction.html

Given your experience (which is replicated all over the world), why not just use ORMs for what they're good at and skip the rest?

That's something I don't understand as well. There is no need to go 100% SQL or 100% ORM. You can use raw SQL with the ORM, and the good ones have actually features to make it easy and productive. You don't even need to use the OOP nature of ORM for querying, good ones will have a functional layer for those as well.

Indeed, I often use all 3 in the same file, let alone the same app.

Even if you do know SQL syntax using the tool to make the change script for you can save quite a bit of time, especially if you've made a bunch of changes which can happen if you've added new functionality.

DISCLAIMER: I worked at Redgate, who make a tool called SQL Compare that works with SQL Server, for nearly a decade. Since this article is about PostgreSQL you should check out Postgres Compare, built by my friend Neil: https://www.postgrescompare.com/. He also used to work at Redgate.

Thanks Bart!

I know for PostgresCompare a lot of its utility is in finding the differences. Providing clarity into what has changed while one was building a feature.

A lot of users still write the SQL themselves. They let the tool figure out the easy stuff, get the dependencies correct, and they make a few edits here or there.

I don't really understand what is it about that makes RDBMS schema changes particularly harder in people's minds. All schema changes to your data can be hard or easy depending on what you do, regardless of whether that schema is implicit in the application or enforced by a database.

If the schema change is additive, it's usually easy. On the other hand, if you drop data items that older versions of applications expect, or change the semantics of existing data, you will need to proceed carefully. Whether you're using a relational database or something else doesn't really matter.

The tool you use to execute those changes doesn't really matter either if your data model changes at the whim of the developers without actual planning behind it to deal with the impact on upgrades it will have.

In an RDMS, a change in schema is hard because changing one table can affect many others. In a NOSQL database, there are far fewer connections between tables (or "objects"). There is less to wrap your head around.

That's just up to how you model your data. there's nothing that prevents you from using a single table with a JSON blob in an RDBMS either, but it's throwing away most of the benefits.

A NoSQL database might seem to make migrations easier, but I'm not convinced that it actually does.

> That's just up to how you model your data.

Well, a NoSQL database forces you to reduce or eliminate relations, whereas an RDMS allows and encourages them. Sure, theoretically, you can make an RDMS database without relations, but it goes against the standard design methodology. You can be sure that if you implement an RDMS in a large organization, relationships between tables will seep into your schema. With a NoSQL database, that's pretty much impossible.

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.

When you remove constraints from an RDBMS you slow it down, sometimes catastrophically.

The query planner/compiler in all modern RDBMS use foreign key constraints, unique constraints, and check constraints to optimize their execution code.

For instance, having a foreign key constraint allows the query planner to omit any code that checks if a value is in the child table but not the parent during a join. It can sometimes avoid accessing one table or the other entirely (so-called “join elimination”).

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

Live database schema changes are hard. Usually people just let server down a few seconds if schema changes and data are not huge.

Why is it hard? Most of changes are atomical and either in background or instant. What change require downtime? I would argue that software change without downtime is harder and basically not solved problem for most environments unless you're using cluster of machines.

Live changes are hard because they requires precise planning, and must often be done in several steps.

Imagine you're extracting some data to a new table in order to support 1:N instead of 1:1 of something. For example, introducing an address table, instead of storing the address directly in the customer table.

If you can have downtime, and have a small database, this can be done with one change and one deploy - that's not very hard.

If you have a big database (100s of GB), and can't have downtime, you can't grab a full table lock to rewrite all the records in one go. You'll have to do something like

  1) create the new table (easy)
  2) create a marker column on the old table saying 'data moved to new  table', set to false on all records
  3) deploy version of application that can use both schemas
  4) migrate all data to the new table, flipping the marker as you go.
  5) nce all records are migrated, deploy new version of application that only uses new schema
  6) drop marker and old columns

(there are multiple ways to do this, of course - this is one approach)

Now imagine that you have 4 or 5 applications using this database. (Some reporting application, some integration with an accounting system, some integration with a marketing tool, etc). And you must do a live migration.

That is _hard_.

I've been wanting a 12factor-type methodological doctrine for data for a long time. Ideally, one that can fully account for how, when, and why to start introducing different kinds of databases and when you need to start exploring those options.

But I'd be happy with a lexicon and fundamental strategy for managing data that allows you do relax and rely on the fundamental primitives, like 12 factor does of overall application architecture.

Queued jobs which can auto-retry will help, of they can detect incompatible changes in the data structure. Of course that'll produce some false positives in the error logs. But it's a small price to pay IMO

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

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.

I have no idea what document you just read, but the linked talk is literally about preparing sequential change scripts.