It feels like you're coupling your application schema to your database schema, which is something you generally want to avoid.
Is this only for niches where you are ok with the db schema being tightly coupled? Do you use specific views to decouple the two schemas? In that scenario it seems like you might eventually get to a point where your view is more complicated than setting up a more traditional application.
The first (theoretical) benefit is that it removes a lot of redundancy. Databases already offer a lot of things applications do for themselves, and typically it's a best practice to do those things in the database anyway to guard against application bugs. For instance, defining CHECK constraints is a best practice regardless of application validation. (There's a lot of disagreement over where the DB/app boundary is and how much overlap there should be.)
Second, databases can be declarative because they are managing the data itself. The presence of a constraint makes a guarantee about the data regardless of history (versions, changes, bugs, etc.). Similarly for declarative authorization (GRANT, RLS, etc.).
Third, these benefits compound when dealing with many smaller, hastily-written applications.
Here's the value prop: Your database is the "source of truth" but can be accessed in many, MANY different ways. Usually via some kind of ORM system.
This can give you a head start on building a more carefully considered REST API - where it gives you the base CRUD routes for every table in an acceptable format and you can build on top of it. Or if you're accessing your DB through some other interface for your web app but need something quick to build a new face for the service like a mobile app.
I recently ditched building a traditional REST API in favor of just using what my ORM provides to interact with my DB. Something like this will come in handy if I ever need one.
From there people mostly decide on two philosophies: "I'll write an adapter layer so that it's easy to change my data" and "I'll take those robust, fixed facts and write my application around handling them".
Honestly, I have no idea if one of those is any better than the other. I can't even say with confidence that one will lead to problems that another won't; they look equivalent to me. The choice seems to be always made based on worldview, and it's not even one of those "fast and loose" vs. "methodical" choices. All the differences I see people pointing are false ones.
- the dev workflow on a db-as-codebase system is less familiar, less well understood, with tooling general several years behind "normal" code work.
- branching and deployments similarly are just different in ways it's hard to prepare for, leading to low confidence in the deployed system.
- testing and debugging: pgtap has different constraints than normal unit testing, debugging sql functions is tricky and awkward. again the tooling is missing or far behind.
- in most profitable applications I've seen, the DB is the single largest cost and the most likely to become a bottleneck you can't loosen by throwing money at servers. having all your logic in there won't *necessarily* make this worse but it certainly won't make it better.
Overall I don't strictly prefer this approach, but it definitely has under appreciated strengths and should probably be used more. It's hard to say how it could end up if more resources were put into actually developing the tooling necessary to back it up.
Logic is a very broad term, and as long as you're talking about number crunching / machine learning, I'd agree. But most web or LOB applications have pretty simple logic. According to Michael Stonbraker[1], a typical OLTP DBMS spends only 4% of its processing time doing useful work, which includes any kind of business logic, among other things. The rest is spent on housekeeping tasks such as context switching and transaction management.
The more business logic you move out of the database, i.e. to the middle tier, the more roundtrips you need per transaction. During roundtrips, transactions can't do any meaningful work, which means more idle transactions, larger connection pool, more locking, and context switches.
In other words, for typical OLTP workloads, each transaction should ideally occur in a single roundtrip, which requires the logic to reside within the DBMS.
[1] https://blog.jooq.org/mit-prof-michael-stonebraker-the-tradi...
But after that initial step, I wouldn't want my API dependent on the schema (directly), nor would I want my database schema dependent on the API code. As a side note, that's one reason I didn't take a liking to Django.
Honestly, I'd rather have an RPC based API in the year 2023 than a REST API. REST, I think, was a bit of a mistake in terms of a source for data that would be sent to a stateful frontend as JSON. REST makes sense for webpages, but nothing about data is inherently page-like. I've run into enough quirks dealing with RESTful APIs and the libraries that claim to handle them that I think we should be looking for a better fit.
at the very least have a read of https://postgrest.org/en/stable/admin.html
I assume you mean readonly SQL then... (perhaps creating views too)
Some time ago I wrote a REST wrapper for the .NET SQL connectors which allowed me to post and query data from the database. It was more than enough for my usage and I could interact with that 'service' from anywhere in the network without bothering on installing and configuring the SQL connector on the endpoints.
A lot of the time, the access patterns of an end user talking to your backend really don't match up to the access patterns of your backend talking to your database.
> I don't want to avoid [coupling my application schema to my database schema]
It seems like you built a layer of indirection to specifically allow the thing you said you didn’t want to do a couple posts up. (I think your indirection layer is a good idea; I’m curious what your previous post meant in light of that.)
> It seems like you built a layer of indirection to specifically allow the thing you said you didn’t want to do a couple posts up
No, because my layer of indirection is in the database in the form of views and procedures. I could be wrong, but I took "coupling my application schema to my database schema" to be something like "having your HTTP API depend on objects in the database", which it does because of the way that postgREST works. If that's the kind of coupling we're talking about, then that's the kind of coupling I would rather embrace than avoid.
Perhaps what you're asking is, "Why not just have your user interface connect directly to PostgreSQL and issue SQL statements?"
This is an interesting statement that probably should be expanded more upon!
I agree with it, because it can be nice to be able to change how certain data is returned to any consumers of your API, for convenience or maybe some business rules. For example, you might want to aggregate data from multiple tables into a single list of JSON objects for filling out a table in some application downstream. Furthermore, you might be interested in being able to change the underlying DB schema without affecting how your API returns data, since its consumers don't necessarily care about how you name your tables or what references what internally.
At the same time, I do disagree with my own point somewhat, because you can just use a DB view for pretty much the same outcome. There's no reason why MyAppUserListViewEntity couldn't match my_app.user_list_view in your database 1:1, I'd actually argue that such a mapping for reading data would be really easy to reason about and the discoverability would be pretty good, while at the same time still letting you introduce changes as necessary.
Furthermore, there's something really nice about codegen: being able to tell some generator where your local development instance of your database is running and generating application entities with all of the mappings (for example, JPA) with a single command, or doing the opposite and creating the schema from your entities. Sadly in most cases such technologies are underutilized and for whatever reason many out there still write their ORM mappings manually for something like Hibernate (or write dynamic SQL manually, with something like myBatis).
In the end, I'm not sure. Coupling might mean issues down the road, but decoupling now might mean introducing a level of abstraction/indirection that might just be needless cruft, like the tendency that you sometimes see in Java projects, along the lines of: MyBusinessObject/Dto <--> SomeMapper <--> MyEntity <--> MyEntityDao <--> MyEntityMapper/Repository; Not saying that that's necessary OR that it's a bad approach, Java just has lots of codebases out there that end up with many abstractions, hence the example.
Advantages:
• Get back all the time spent boilerplating and bikeshedding ad-hoc app specific REST protocols.
• Eliminates the (near) superuser privileged web servers that pose a security risk if compromised. Eliminate SQL injection, XSS, XSRF as bug classes.
• Allows smart users like business analysts to bypass the UI partially or completely and go straight to a SQL console, because end users = db users 1:1 and ACLs are understood by the RDBMS directly.
• Use UI frameworks and languages that aren't JavaScript. Use context menus, menu bars, hotkeys OS services or whatever else makes your users productive.
• Use multi-threading, files, special hardware as part of your core app architecture if you need it.
• If you can afford the server side resources: align DB transaction length with UI "transaction" length.
Obviously there are also downsides. I wouldn't write Instagram this way. Postgres doesn't scale very well to lots of connections. Oracle/MSSQL scale a lot better and have other advantages like much better blob support, but you'd have to get comfortable with the idea of building new apps on them.
You can mix and match, it doesn't have to be purist. Retain a thin, simple and rarely updated web server that just handles the requests you don't send directly to the DB e.g. for things like ElasticSearch. Or if you can (i.e. not on Supabase) write custom Postgres extensions that let you use SQL stored procedures as your RPC protocol. It has some advantages over HTTP.
Lately I've been experimenting with this design a bit. The traditional hassle has been non-web distribution to desktops. https://conveyor.hydraulic.dev/ fixes that. If you're using something like Electron or the JVM you can do a build+release cycle for Win/Mac/Linux in about 60 seconds all from your dev laptop, and you can make installed clients do a fast update check on each launch just like a web app would. There are some open questions about the best way to handle user authentication when connecting direct to a DB if you don't want passwords. The nice thing is you can e.g. bind the results of SQL query or an ORM directly into your UI toolkit. JSON, REST, custom paging code and all the other goop CRUD apps end up with just boil away.
I’d want to limit it to simple schemes.
But if you’re only exposing one table- it would be pretty darn fast.