Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

octoproject.github.io

Post reply on HN
Print view View original

31–40 of 62 posts

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

#31

Post by xgenecloud »

robmccoll wrote: post #27
MuffinFlavored wrote: post #17

Earlier quoted context omitted.

I feel like projects like this work for simple stuff but as soon as you need analytics/insights or actual business logic, you almost always need to just "roll your own" API. Am I wrong? Do other people feel this way? Can anybody think of a few projects they've worked on that would be too complex/a ton of work to make work with these kind of simple template generators?

It seems like when working with generators, the trick is to have the right boundaries between generated code, points where you can extend the generated code, and the API through which you use the generated code. If successful, you should never feel the need to hand edit the generated code itself, and you shouldn't need to worry too much about re-running the generator breaking things or stomping on your code.

>> you shouldn't need to worry too much about re-running the generator breaking things or stomping on your code.

Can't agree more! There are code parsers which give out a DOM and then code can be manipulated. But is a bit of work. We are trying this concept in our framework.

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

#32

Post by FinalDestiny »

MuffinFlavored wrote: post #17
eatonphil wrote: post #9

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 i…

I feel like projects like this work for simple stuff but as soon as you need analytics/insights or actual business logic, you almost always need to just "roll your own" API. Am I wrong? Do other people feel this way? Can anybody think of a few projects they've worked on that would be too complex/a ton of work to make work with these kind of simple template generators?

I’m using Prisma right now and they allow you to manually expose your fields, and they allow you to “resolve” any field as you see fit. If you want to run the original function, you can provide “originalResolve” and call it later. I think Prisma has a great (albeit in progress) way of doing what you’re saying.

It also integrates with graphql-codegen so you can generate code for apollo/others

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

#33

Post by CuriouslyC »

MuffinFlavored wrote: post #17
eatonphil wrote: post #9

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 i…

I feel like projects like this work for simple stuff but as soon as you need analytics/insights or actual business logic, you almost always need to just "roll your own" API. Am I wrong? Do other people feel this way? Can anybody think of a few projects they've worked on that would be too complex/a ton of work to make work with these kind of simple template generators?

With postgrest you can add stored procedures as rest rpc calls, and you can always roll a microservice for more advanced stuff. In practice these sorts of auto-api tools make a good starting point as long as they support your authentication and authorization needs.

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

#34

Post by ecf »

lukeramsden wrote: post #7
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 inbu…

I recently started a side project on top of Postgraphile and I had to end up scrapping it in favor of something I was more familiar with.

The biggest problem is that unless the main language you’re familiar with is PL/pgSQL, you’ll eventually run into the roadblock that is having everything reside in the DB.

In my case, I simply could not figure out how to use the Users table without having the password returned in all queries. I could turn off the automatically generated queries that Postgraphile made, but at that point why bother with it?

Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer.

Graphile makes https://github.com/graphile/migrate, however, it seems incredibly brittle and is built on a workflow that will absolutely break production if it is slightly deviated from.

I really liked using it! I just hope some of these developer ergonomics issues could be better handled.

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

#35

Post by mritchie712 »

akie wrote: post #24

Perhaps I'm old, but who needs an API for an SQL query? I'm not sure I understand the use case, or the advantage of something like this over a regular API call to a backend which would also allow you to do e.g. authentication. Enlighten me?

As part of our product (https://seekwell.io/), we let people access SQL results with an API key and unique endpoints per query. There's also an option to add parameters.

The main use case is giving a data scientist or another application access to the results of a few arbitrary queries without giving them full access to the database. So it's a bit like giving them access to a SQL view, but without them needing to set up a driver, etc. to connect.

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

#36

Post by lukeramsden »

ecf wrote: post #34
lukeramsden wrote: post #7

Earlier quoted context omitted.

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 inbu…

I recently started a side project on top of Postgraphile and I had to end up scrapping it in favor of something I was more familiar with. The biggest problem is that unless the main language you’re familiar with is PL/pgSQL, you’ll eventually run into the roadblock that is having everything reside in the DB. In my case, I simply could not figure out how to use the Users table without having the password returned in a…

> In my case, I simply could not figure out how to use the Users table without having the password returned in all queries

The recommended way of doing this is to store anything you don't want public in a separate table with a one-to-one relationship, and then controlling access to that table through computed columns and such (https://www.graphile.org/postgraphile/postgresql-schema-desi...)

What I can't recommend enough to people starting with Postgraphile is to check out graphile-starter, and especially it's first migration: https://github.com/graphile/starter/blob/main/%40app/db/migr...

That really helped me to understand better how to structure my schema, as separation of concerns within a model by using different tables is not something that is necessary when doing a regular application-in-front-of-datastore type app.

> Other problems are that there isn’t a mature and well established method of maintaining development, staging, and production database schemas on projects with more than one developer.

Yes, I don't have to grapple with this issue so I don't really have much to say about this. Graphile-migrate works great for me, but I can see how it could be an issue for larger projects, although I would think that database migrations are simply tricky on their own, regardless of Postgraphile.

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

#38

Post by amal_kh5 »

o1lab wrote: post #12

Interesting concept and quite liked the playful logo. Can we pass in env variables to db connection ? We are in similar space, we take input params of db and generate CRUD apis with Auth+ACL and then APIs are packed into a single lambda function. There is support for serverless framework as well. [1]: https://github.com/xgenecloud/xgenecloud

Yes, for example:-

? Enter the database password: ${DB_PASSWORD}

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

#39

Post by bobbywilson0 »

My main purpose of tools like these has always been prototypes, or hobby one-off type stuff. For SPAs, or a sketch with a Jupyter notebook. They're great for this sort of thing because in my experience, this used to require building some sort of API just to get a simple json interface to the database. It was my understand that the purpose of these types of tools was mostly that.

Are folks using these kind of things for non-trivial production applications?

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

#40

Post by ecf »

lukeramsden wrote: post #36
ecf wrote: post #34

Earlier quoted context omitted.

I recently started a side project on top of Postgraphile and I had to end up scrapping it in favor of something I was more familiar with. The biggest problem is that unless the main language you’re familiar with is PL/pgSQL, you’ll eventually run into the roadblock that is having everything reside in the DB. In my case, I simply could not figure out how to use the Users table without having the password returned in a…

> In my case, I simply could not figure out how to use the Users table without having the password returned in all queries The recommended way of doing this is to store anything you don't want public in a separate table with a one-to-one relationship, and then controlling access to that table through computed columns and such ( https://www.graphile.org/postgraphile/postgresql-schema-desi... ) What I can't recommend e…

You’re completely right about the starter and I feel if I had discovered it first instead of trying to roll my own migration system with Knex and stuff, I would have stuck with Postgraphile.

But instead of rebasing my project with the starter I decided to go back to what I was comfortable with and do a Rails gql api.

I’ll definitely be trying it out in the future though! I felt so incredibly productive with rolling stuff out before auth became a concern.

Post reply on HN