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?
The premise of the idea is that we can cut out the middle-man for a lot of data distribution use cases. We give you a way to deliver your data in native SQL, using the Postgres wire protocol. We've decoupled authentication from the database, so we can do it in a gateway / LB layer using PgBouncer + Lua/Python scripting. Any SQL client can connect to the public Splitgraph endpoint (as far as a client is concerned, Splitgraph is just a really big Postgres database). You can write queries referencing and joining across any of the 40k datasets on the platform.
In fact, just this week we've been working on v0.0.0 of our web client. This lets you do things like share and embed SQL queries on Splitgraph, e.g. [1] (this query actually joins across two live data portals at data.cityofchicago.org and data.cambridgema.gov).
There's also an example here of using an Observable notebook with the Splitgraph REST API [2]. It also works with the Splitgraph DDN configured as a Postgres database, but that's only supported in private notebooks for now (since normally it's a bad idea to expose your DB to the public!)
In general, we like the idea of adding more logic to the database. Tools like OP's are useful in this regard. In fact, at Splitgraph we use Postgraphile internally (along with graphql-codegen for autogenerated types) and we have nothing but good things to say about it.
[0] https://www.splitgraph.com/
[1] https://www.splitgraph.com/workspace/ddn?layout=hsplit&query...
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.
I work for a large corporation. They want to implement the Bezos mandate [1]. No direct database access between teams, API abstraction for everything.
OK, now let's think in onion layers (or hexagonal/clean architecture if you like). Think of layers of services with different purposes - data services (containing no application/business logic), application services (for business logic, orchestration, process), and UI/UX services (to power differing end user experiences).
Data services don't have to do much - be the data/repository layer, expose productive interfaces for CRUD. Need to read across data sources? Think of federated data services that can combine data on the fly, perhaps like GraphQL.
These kinds of tools are perfect for the first layer of services that abstract the database world from the application world. Just simple services, even ones that effectively let you mimic what SQL queries can do (filter, sort, page, etc.). Individual record oriented interfaces, and bulk oriented interfaces. The query side of CQRS (command query separation).
Many will say, "I don't need all this complexity and layers" - and sure, for smaller or simpler applications, probably not!
But, if you have to operate on any kind of larger scale, with multiple data sources, systems, etc., you end up needing the layers. And these types of tools automate some of the lower layers.
Perhaps when we talk about this, we shouldn't be focusing on "oh it's too complicated", and instead building frameworks or reference architectures that automate away the complexity - so it looks easy again, but now it is more flexible, perhaps easier to scale.
I believe that we are on the cusp still, of an almost fully defined, service based architecture (microservices and server less were just one part of the continuing development of that story). Federation is another part of that story oft ignored. Thinking of the onion as service layers is another part. Erasing the network boundary as a concern through much higher speed internetworking is another part.
Eventually we may come to see, that it is all a big "system", some parts just aren't connected to each other directly.
Sorry, got a bit rant-y at the end there :) Just passionate about sharing this world view with others - as I continue to see this architecture developing!
[EDIT] I wanted to add, it's not just that the use case for this is in a data service layer for automation - from a logical perspective I mean. In big companies such as the one I work for, we never get the resources we need, ironically. We are overwhelmed with demands, and must operate under the Bezos mandate rules. Tools such as Octo are not a panacea, but, they are a good compromise if you have to move fast, they are time-and-cost-savers. And they can get you surprisingly far.
[1] https://www.calnewport.com/blog/2018/09/18/the-human-api-man...