Unlike most languages, which tell the computer how to do a task step by step, SQL says what you want, and the database decides how. That makes databases vastly easier to use, yet SQL can be harder to build with than the rest of the app, even code that spells out every step.
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
Resilient:
1. turn right at the stop light
2. stop at the first house
The brittle version is longer (i.e. worse), but the key issue is that, like SQL in practice, each step only makes sense if you know all the steps before it. Take "turn 90 degrees right". It takes you through the stop light or off a cliff, depending on where the prior commands landed you. Not so with "turn right at the stop light". Run it at the wrong time and you may turn at the wrong light, but you won't turn into a car, a house, a wall, or off a cliff because you weren't where you thought. It's much easier to reason about, build, and maintain.
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.
Unlike most languages, which tell the computer how to do a task step by step, SQL says what you want, and the database decides how. That makes databases vastly easier to use, yet SQL can be harder to build with than the rest of the app, even code that spells out every step.
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:
Resilient: The brittle version is longer (i.e. worse), but the key issue is that, like SQL in practice, each step only makes sense if you know all the steps before it. Take "turn 90 degrees right". It takes you through the stop light or off a cliff, depending on where the prior commands landed you. Not so with "turn right at the stop light". Run it at the wrong time and you may turn at the wrong light, but you won't turn into a car, a house, a wall, or off a cliff because you weren't where you thought. It's much easier to reason about, build, and maintain.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.