Ask HN: Projections as an Alternative to HTTP APIs | Hacker News Reader

Ask HN: Projections as an Alternative to HTTP APIs

8 pointsxky8 comments

I work at a large FinTech in a team responsible for a federated GraphQL service and, recently, related datasets in Redshift. We've come to see our datasets as another kind of API representing the same underlying data from our GraphQL API.

Our service is event sourced and follows CQRS and so our Redshift dataset is just another projection. This got me thinking, can other services simply use this projection directly instead of a GraphQL or REST API?

In general, services could provide an API for mutations that updates a projection which other services can directly query using SQL. Although services are accessing a database directly, it's not breaking encapsulation because the data is a projection intended for use by other services like an API.

There's a few benefits I see here. Firstly, if all services were modelled like this, querying multiple "services" could be handled with simple joins. Latency is reduced as data doesn't pass through an intermediate service. Scaling reads is straightforward. The same "API" works for both transactional and analytical use-cases. And finally, low-write services could be scaled down and no-write services could just be data pipelines.

So HN what do you think about a database as a service API?

I've experimented with this as an alternative API layer. When using role-based RLS in postgresql and being strict with what is exposed to application users it can be quite nice, but it also brings in a lot of additional considerations. Things like rate-limiting, caching, timeouts, access logging, etc. are all very much standard practice on a HTTP or RPC layer, but not so much for a SQL interface. When using postgrest (a REST layer on top of postgresql) I've considered just exposing the same API over SQL since I already have the authZ fully implemented in the DB but I still don't know if I want to implement everything else that goes along with a api in the DB.

This works fine as long as the data flow is one directional. E.g. legacy backend -> db -> read-only frontend

However once the frontend and backend begin to mutate data, you're going to have problems doing that consistently; it's an anti-pattern I recommend against. Better to have an API handle everything.

If you can guarantee that you get the same result for the same projection (a query and or rest call also could be characterized as a projection) then I don't see an issue.

LOTS of rest services are just urls params -> sql -> result set -> json

https://postgrest.org/en/stable/

Isn't this what graphql was supposed to offer?

that's what i'm missing here... it sounds like OP is just saying "What if instead of graphql we used some other thing that did exactly what graphql does?"

You may be interested in an open source project I am developing, see: https://webapibridge.org

Web API calls are converted into MySQL Stored Procedure calls and the result set returned. I am interested in extending this to other databases.

How does this compare to PostgREST, besides being based on MySQL?

I haven’t looked at that in depth but I believe it automatically makes API endpoints available based on the database schema. I’m not sure how access is limited.

With the WebAPIBridge the calling account only has EXECUTE privileges and authentication and authorization is (expected to be) handled in the database, ie, a sessionid is passed as a parameter. Only procedures with a comment of EXPORT are made available.

Access to tables/procedures is limited by the role assigned by the token (or a default role). Access to data/rows is limited by RLS (row level security), which is built into postgresql and limits what rows are visible to each query.

You can pretty much make it as locked down or as open as you want.

One thing that is great about RLS is you don't need to think about the authZ when writing the query since the policy on the table restricts what is visible to the current authorized user.

If you are building a product in a similar space I'd recommend you take a look.

Reply on news.ycombinator.com