There's also PostgREST[1], which exposes a REST API to a postgres database and has been discussed on HN before [2,3]
[0] https://sivers.org/pg [1] https://postgrest.com/en/v4.3/ [2] https://news.ycombinator.com/item?id=13959156 [3] https://news.ycombinator.com/item?id=9927771
About a year ago I designed a system for a client that was just Postgres with a Go http frontend. Go was used to handle http requests and responses, to translate the API from http to postgres functions/views, and serve the response straight from postgres. Even authentication and access control was handled using postgres's role system and RLS was looked into (and found to be viable for when needed). Postgres was a good choice because the project involved mainly a lot of data wrangling. Of course, I could do it this way because it was a completely internal system with a fixed number of users, and the burden of maintenance was minimal.
Main reasons for not going this direction, IMO, would be:
1. Developer proficiency, while building and maintaining. Far more people know Python, Ruby, JavaScript etc. than SQL. Far more people know how to think in imperative programming than to think in data models. Of course, one can write Postgres-hosted imperative programs as functions (in languages ranging from plpgsql to javascript), but at that point using the same language in a node or django app is much easier.
2. Unsuitability. Some things are just not suited to be run in a database. Realtime multiplayer games, multiple-source data compositing APIs, view rendering etc. come to mind. The truth remains, that general purpose programming languages (or special purpose, where the purpose is serving, languages) and environments have more possibilities than an environment that grew around dealing with data.
I would also encourage to introduce a proxy in between (e.g. pgbouncer [1]) to help you to handle connections and deal with the authentication.
We're using PG as "the brain" of our service since 2014 and it never failed us. The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote. However once you have functional logic, you can write fairly lightweight application layer around it and change it as often as you want.
You can also scale this setup almost indefinitely thanks to extension like citus [2] and still keep your application layer fairly thin.
[0] https://www.postgresql.org/docs/current/static/auth-methods....
I agree. But I am thinking about making a database role for each user. The user becomes that role after signing into the front end, like Apache.
db.exec('set role to $1', req.remote_user);
> The biggest downside is that there are no tools to help to debug or measure the performance of the code you wrote.
Isn't there the explain command (https://www.postgresql.org/docs/current/static/using-explain...) and the \timing option for the command-line client, psql (https://www.postgresql.org/docs/10/static/app-psql.html)?
I figured. If you isolate the data well enough, it could work. I'm always paranoid when it comes to DB.
> Isn't there the explain command and the \timing option for the command-line client, psql? Yes, there is. This however doesn't help you with triggers and UDF, which is how you usually create the logic in the DB.