Show HN: Octo – Generate a serverless API from an SQL query • Hacker News Board

One of the things that's not obvious to me about things like this (and other similar tools) is where/how scopes/limitations/permissions are handled. I assume they either are or can be, I just never see it spelled out clearly. What am I missing?

Re: Show HN: Octo – Generate a serverless API from an SQL query

#5

Post by lukeramsden » Fri, Sep 11, 2020, 1:46 PM UTC

ForHackernews wrote: post #3

Looks vaguely similar to http://postgrest.org/

And Hasura, Postgraphile et al too. These, as well as PostgREST also give you much more flexibility in the form of plugins in library mode and other such things - they also generate the actual queries for you, via introspection, as opposed to this which requires you to write the query yourself.

I think there's certainly space for this project, i.e. hand-written queries, on any database (Postg[REST|raphile] both only work with Postgres of course, not sure about Hasura). Not sure it will succeed without support for more forms of Serverless deployment, primarily Lambda.

Re: Show HN: Octo – Generate a serverless API from an SQL query

#6

Post by Trisell » Fri, Sep 11, 2020, 1:47 PM UTC

The idea is interesting. But it looks like you end up with a yaml file that enumerates each of your tables/endpoints and the queries that back them. So are we exchanging the “complexities” of code, where we have control and testing, for the “lack of complexity” of yaml that becomes unwieldy and untestable in the name of “simplicity?”

Re: Show HN: Octo – Generate a serverless API from an SQL query

#7

Post by lukeramsden » Fri, Sep 11, 2020, 1:49 PM UTC

Glyptodon wrote: post #4

One of the things that's not obvious to me about things like this (and other similar tools) is where/how scopes/limitations/permissions are handled. I assume they either are or can be, I just never see it spelled out clearly. What am I missing?

I can't speak for this project specifically but for some context, Postgraphile's way of solving this, as it only supports Postgres, is to use Postgres's Row Level Security feature, whereby you enforce scopes and permissions at the data layer, as well as using table grants to roles specified in JWTs and such. (https://www.graphile.org/postgraphile/postgresql-schema-desi...)

This project doesn't seem to have any inbuilt AuthZ functionality, so unless your database has that built in like Postgres, or you need something that's not possible in-database, I guess you just... can't.

Re: Show HN: Octo – Generate a serverless API from an SQL query

#8

Post by stingraycharles » Fri, Sep 11, 2020, 1:53 PM UTC

Trisell wrote: post #6

The idea is interesting. But it looks like you end up with a yaml file that enumerates each of your tables/endpoints and the queries that back them. So are we exchanging the “complexities” of code, where we have control and testing, for the “lack of complexity” of yaml that becomes unwieldy and untestable in the name of “simplicity?”

Don’t forget that at some point, you’ll want to generate the yaml from code, because otherwise it becomes impossible to maintain. And quickly you’ll find yourself back at square 1. :)

Re: Show HN: Octo – Generate a serverless API from an SQL query

#9

Post by eatonphil » Fri, Sep 11, 2020, 1:58 PM UTC

I've got a similar project that reads your db schema and generates a Go REST API and a TypeScript/React web interface. (The code-generation is language agnostic so at some point I'd like to add at least a Java REST API as well.) It supports PostgreSQL, MySQL, and SQLite.

Unlike PostgREST/Hasura and some other dynamic tools you can "eject" at this point if you'd like and continue on development without the generator in a language you already know. But I'm working on exposing Lua-based hooks you could carry across whatever backend language you choose to generate and avoid the need to eject.

It has builtin support for paginated bulk GET requests with filtering, sorting, limiting. Built-in support for bcrypt-password authentication and optional SQL filters specified in configuration for authorization of particular endpoints based on session and request metadata.

Still very much a work in progress but the goal is to push the envelope on application boilerplate.

Screenshots are of the example/notes project in the repo.

https://www.dbcore.org/

https://github.com/eatonphil/dbcore

Re: Show HN: Octo – Generate a serverless API from an SQL query

#10

Post by eatonphil » Fri, Sep 11, 2020, 2:09 PM UTC

Glyptodon wrote: post #4

One of the things that's not obvious to me about things like this (and other similar tools) is where/how scopes/limitations/permissions are handled. I assume they either are or can be, I just never see it spelled out clearly. What am I missing?

I tackled this by using SQL as the filter language where the generated code will fill in some context-specific variables like current session user id or current request object id.

This is a little limited in this current form and I'm working to expand SQL filters to match up to HTTP codes so you can say this request needs to have a session otherwise it is a 401, then it also needs to match another filter otherwise it is a 403. But this other endpoint is ok to show without a session if the object being requested is marked public in the db.

There's a lot to think through especially when extending these filters to bulk methods.

https://github.com/eatonphil/dbcore/blob/master/examples/not...