How is this not a leaky abstraction?

[sahellebusch]

In the documentation:

There is no ORM involved. Creating new views happens in SQL with known performance implications. A database administrator can now create an API from scratch with no custom programming.

Can somebody please explain to me how having your entire API directly linked to the database structure itself is not a leaky abstraction?

[develop7]

Could you please elaborate why it is a leaky abstraction beforehand?

[sahellebusch]

By definition, a leaky abstraction is an abstraction that leaks details that it is supposed to abstract away. By exposing the underlying data structure without mapping to business representation objects (see domain driven design) to me is a pretty big leaky abstraction.

[mkleczek]

I don't know what "business representation objects" are, to be honest. Nevertheless: Relational Schema is the abstraction - the RDBMS itself provides so called logical/physical separation. There is no need to provide yet another abstraction on top of it. See for example: https://www.oracle.com/uk/database/what-is-a-relational-database/#:~:text=The%20relational%20model%20means%20that,data%20as%20a%20logical%20structure.

[sahellebusch]

Directly mapping business logic to a database schema is inherently risky because it tightly couples your application to the underlying data structure. This coupling means that any changes to the schema—such as altering tables, modifying relationships, or changing data types—can ripple through your application, requiring extensive code updates and increasing the risk of bugs. Exposing your schema to the world also opens you up to brittle APIs that are difficult to maintain as the database evolves.

Quite honestly, I’ve never met anybody who thinks of the RDBMS schema as the abstraction over storage - while true, it's how your data is directly stored. I'm sure we'll continue to disagree, but at least I learned a thing or two while popping in here. Feel free to mark as solved! 👋🏼

[wolfgangwalther]

Directly mapping business logic to a database schema is inherently risky because it tightly couples your application to the underlying data structure. This coupling means that any changes to the schema—such as altering tables, modifying relationships, or changing data types—can ripple through your application, requiring extensive code updates and increasing the risk of bugs. Exposing your schema to the world also opens you up to brittle APIs that are difficult to maintain as the database evolves.

The "risk" you are describing is a problem for applications, which live outside the database. If your schema changes require changes in the application code, and this application code lives somewhere else - then it's hard to make those changes in-sync. It's even harder when you have multiple applications using that database.

But that's not the case here. By keeping the business logic in the database, we can make all our changes to the database schema and our business logic within the same transaction. There is no risk involved.

Of course you want a stable API towards your client-side application. But that's just a matter of how you write that code. Even if you expose your tables directly as the API, you could still avoid renames and/or deletion of tables/columns - thus no breaking changes. Granted - that's not very maintainable, that's why we use Schema Isolation, as mentioned by Steve earlier.

With Schema Isolation, you'll be having at least two schemas, one for the actual data / tables (that's what you would call "database schema") and one for the business logic. You'll only expose the one for the business logic. It will contain views and functions which map to the data layer. This exposed schema is certainly another abstraction. Now, whether that abstraction is leaky or not is mainly a question of how you write it. If you create your views as CREATE VIEW x AS SELECT * FROM y - yeah that's going to be very leaky, because every change to the data table will be part of your API immediately. If you approach it as CREATE VIEW x AS SELECT a,b,c FROM y - not so much, anymore. You can easily rename columns, keep columns with NULL or other default values etc in the view this way, thus not breaking your API.

Note that the additional "abstraction" is the additional database schema you're adding in this case. PostgREST itself does not provide another abstraction, it's merely translating between two protocols. It's up to you whether your abstraction is leaky or not.

[mkleczek]

Directly mapping business logic to a database schema is inherently risky because it tightly couples your application to the underlying data structure. This coupling means that any changes to the schema—such as altering tables, modifying relationships, or changing data types—can ripple through your application, requiring extensive code updates and increasing the risk of bugs. Exposing your schema to the world also opens you up to brittle APIs that are difficult to maintain as the database evolves.

The data structure is business logic - that's the whole point.

I appreciate this is a paradigm shift for many but if you think about it some more - relational model is no more brittle than your JSON schema defining message structures. And I would argue: performing data analysis and full normalisation (ie. 5th NF) makes your database model very stable (at least comparing to REST API definitions).

[steve-chavez]

See https://postgrest.org/en/v12/explanations/schema_isolation.html