Comment by powertime

The problem is that software always changes. Usually that means changing, adding, or deleting code. But changing how a database organizes its data requires "migrations" -- directions from its current state to its desired state. Because migrations are an inextricable part of life with SQL databases, (much of) SQL code in practice runs in the order it's written, so to understand any bit of SQL you must understand every bit that ran before it... and new code gets harder to write as the code base grows.

Consider two programs controlling a robot:

Brittle:

  1. drive 100 meters forward
  2. turn 90 degrees right
  3. drive another 50 meters

  1. turn right at the stop light
  2. stop at the first house

This is why good engineers squeeze state handling into the smallest volume of code possible, and why the whole family of functional languages exists. But with SQL, the code isn't just handling state; the code itself is state. There's a reasonable reason: the database, unless empty, is state, so changes must consider what's in it.

It's so painful that two approaches emerged largely to sidestep it.

ORMs

The 90s saw TOPLink and later Enterprise JavaBeans: Object Relational Mappers (ORMs), which mapped non-SQL code to SQL. They let engineers write ordinary code in modules, files, and functions. It was like ordering in a foreign country from a waiter who speaks both languages but is native in neither: you usually get what you asked for, but not always. Some were better than others, but every real system leaned on the escape hatch they all have: raw SQL -- speaking to the chef directly in the local language. Then SQL was sprinkled through another language's source, hard to reason about again, and the chef got contradictory orders from you and the waiter. ORMs also brought migration tools (Rails migrations, Alembic) that made the tower of changes easier to write but no easier to read.

NoSQL

Then came a global revolt against SQL, somewhere between Google's Bigtable paper in 2006 and 2009, when Cassandra, MongoDB, CouchDB, Redis, and others proudly joined the NoSQL movement. Mongo was easy to start and iterate on, but SQL still won for many reasons. Chief among them: the data model didn't magically disappear -- it became an emergent property of the code, spreading the same poison migrations carried into the entire code base. The code became state. So after a long hype cycle, Postgres became the most-used database in the 2023 Stack Overflow developer survey, and SQL reasserted its dominance.

The end state

There are a variety of schema differs out there -- including a variety aimed just at Postgres. We couldn't find one that was both open source and worked on our code so we wrote pgdeclare. It lets people write in SQL but describe the final state without building up an ever less stable tower of changes. Reverse jenga still ends up with a pile of blocks on the floor -- it's just a bigger mess. To set expectations: some change management is inevitable and we provide our own escape hatch for that. Hopefully it works for you, and if not, pull requests are very welcome.