But taking it to further, just exposing raw Postgres sockets with TLS + row level security policies what would be the pros and cons of such an approach?
Main that I can think of:
1. Business logic would be all in clients (Flutter/React[Native] et al) hence updates would require updating those clients.
2. Database schema would have to be versioned and clients will have to check if they can continue with the version right now in production or exit with error message asking for an update.
3. Infrastructure wise, you'll just be running vanilla Postgres and that's it, there's no backend.
4. You would have NO API, you'd just have database.
5. For adding new parties, you'll be actually adding real Postgres users (or via some LDAP etc)
6. Workers? Background processing? That means inserting records into certain tables, picked up by processes on server side and computing results feeding back to the database.
Just seeking collective wisdom/experiences/opinions/stories on the subject if someone has tried this or what are other corner cases that might come up assuming that in every language there’s performant, good enough database driver (Postgres wire protocol) is available including in browsers (via Webassembly or without)
Thoughts?
[edited typos]
In 2022 the main objection I'd have is that almost all applications have some requirements for security that are hard or impossible to express with the security model of the database, SQL constraints, triggers and such. To take an example of a tough one, consider that many applications involve interactions between multiple users. If I make a payment into your bank account the system will do SQL inserts and updates against your bank account as well as mine and those have to be done correctly. In an online chat application the same is true, I have to be able to mutate the state of the application that you see, that's the whole point.
There is nothing you can do to stop people from hacking web clients, so users will always be able to make uncontrolled requests, if the security of the system depends on the behavior of the client your system is not secure.
Note if you really like relational databases an alternative architecture is to write all of the "API Calls" as stored procedures, these can handle RPC and REST-like requests, just instead of writing your API calls in Java, node.js or go you get to write them all in whatever language your database supports for stored procedures.
> select * from homepage; -- a view that selects the most recent content from 'posts';
> select * from posts where submitted_at between '2022-01-01' and '2022-01-08';
> insert into posts (title, url) values ('example', 'http://example.org') -- submit a URL
> select u.username, count(*) from users as u join comments as c on c.username = u.username group by u.username order by u.karma desc;
Regarding just using raw postgres sockets, the CONS is that it gets harder to code the client in javascript (I don't think webassembly can handle sockets yet?)
Security-wise, it can actually be done (see how firebase handles security) but you need a way (API layer? StoredProcedures?) to verity auth tokens (like JWT) sent from the clients
All background processing can be done with sever less functions that have scheduling abilities.
If all your business logic is in the client what's to stop someone just changing it to f*ck you over.