Hacker News Reader

Comment by everdev | Hacker News Reader

I always found REST (and GraphQL or any API for that matter) tedious as it's basically abstracting SQL queries.

To my knowledge the abstraction is necessary to mitigate SQL injection and data permissions.

With RLS in Postgres, I think the data access problem has largely been solved where someone with the wrong JWT simply can't query other people's data if RLS is setup properly.

However, I don't know of a way to lock down a PostgresDB enough for clients to be able to write their own SQL statements.

It's easy enough to remove operations like DROP TABLE xx; but it seems like for a client to safely query a database we're stuck writing these boilerplate abstractions (or auto-generating them now).

View on HN

I think that's true for very simple CRUD apps (basically what this is designed to replace). For more complex apps, it gives you room to:

* Implement decent monitoring. I've had very poor luck trying to monitor SQL query performance directly, especially if you need to tie particular queries to a user action, and monitor the time taken to perform a complex action.

* Implement more sane resiliency, by giving the ability to connect to multiple databases.

* Alter your data storage format or backend without needing to change the presentation (i.e. the same REST call could suddenly go to Postgres instead of MongoDB, and the client doesn't need to change)

* Easily integrate with other apps/features. SSO is a good example of this, where you can use the same authentication system across multiple APIs relatively easily.

That's just a few. SQL is a really great interface to data. SQL is (in my opinion) a poor user interface to business logic. Something like "reset a user's password" makes sense in REST. It will likely continue to make sense, and probably even look exactly the same, for a long while.

That is often not true of SQL, unless you accept arbitrary constraints like "we can never use anything other than RLS for authorization". Not that RLS is a bad implementation, but things change. Leaving yourself some flexibility is usually a good idea (within reason, like anything you can shoot yourself in the foot trying to make your app too flexible).

* only simple CRUD apps - I've implemented with this type of stack a service similar to Parse/Firebase. I've also designed/implemented something like Basecamp+Freshbooks (in traditional PHP style, not a toy/demo app) and i know this type of stack can power just fine services like that (with a lot less code), i.e. your statement "simple CRUD apps" is not true

* monitoring - easily implemented in the proxy (can be as simple as $request_time in logs)

* multiple databases (I take it you mean read replicas) - tie each process (very lightweight process ... something like 5Mb memory) like this one to a particular db replica than use roundrobin in nginx upstream. So, to go from one db to multiple one you just need a different production config, no new code.

* use views/stored procedures to expose as an api then change your underlying tables (data storage format) to your hearts desire.

* Integrate 3rd ... - You are thinking/comparing tools like this to languages/stacks like php/node/ruby when infact you should compare them to things like active record. In traditional stack you don't integrate 3rd party at the same level as active record (in active record code), you do it either above or below this level. It's the same here only above means client code or (scriptable) proxy level (see openresty and njs) and below means database level (PostgreSQL is much more then SELECT ) or some worker process that reacts to system events that can be as simple as a events table or as complex as message broker.

SELECT * FROM reset_user_password() works in sql too

Rest also means you don’t care about the database schema.

Backend services are about more than serving data, wouldn't you say?

Most are, but I have definitely worked on projects that weren't

With front-end development getting increasingly popular, I think backend is starting to become just a thin wrapper around database. If database can be securely locked down enough, a lot of business logic can be shifted to the front-end.

Then you can't use it across apps, though.

Say for example you have a web front end, a mobile app, and a service receiving web hooks that all want to execute the same action. If you have that in a client layer, you'll be writing it 3 times.

Moving logic to the client comes with pros, too (like being easier to work offline), but it definitely has downsides as well.

    If database can be securely locked down enough, a lot of
    business logic can be shifted to the front-end.

Unless "locked down enough" includes "with all the business logic embedded in the DB", this is going to fail in exciting ways with technically-savvy users...

Reply on news.ycombinator.com