SQLer – Turn Your SQL queries into RESTful APIs without any programming language | Hacker News Reader

SQLer – Turn Your SQL queries into RESTful APIs without any programming language

github.com

248 pointsalash3al55 comments

See also: http://postgrest.org/en/v5.2/, which works with usually zero configuration against PostgreSQL databases

This is awesome. It seems like any API is really just abstracting the database (or other data store) behind it. But, if only we could just expose the db directly and let users write their own queries to get exactly what they want. That way they could select exactly the fields they want like with Graphql, and write joins to simplify multiple requests into one. I wonder what is still preventing this from being reality? Not enough permission support on the db, or fears of performance issues from badly written user queries?

Having the users write their own queries reminds me of the old Infocom database.

Yes, that Infocom, if Zork fame.

After doing interactive fiction, it tried to use its parser to drive a database. Instead of “SELECT * FROM...” a user could write, “Show me all the people in London who own cats.”

Cornerstone! The downfall of Infocom as we knew it... :/

Thanks for the tip, I didn't know the story. It's quite interesting.

Here's an excerpt from wikipedia:

"One development decision that proved fateful for the product—and the company as a whole—was the decision to make Cornerstone run via a virtual machine (VM). The use of Infocom's "Z-machine" for its interactive titles had been a huge boon: since all the games were written in an intermediate language (called ZIL), the company could release one title for every major platform simultaneously. The developers hoped to do the same for Cornerstone and its subsequent products. The existing VM proved unsuitable for the database application, so a new one was written for the product. The developers produced the VM for the IBM PC first, planning to write VMs for other platforms after the initial PC release."

https://en.wikipedia.org/wiki/Cornerstone_(software)

i'd not known at the time that they did cornerstone with the zmachine approach as well. certainly in hindsight that seems a bad idea. i know that there weren't releases for anything except the pc market anyway, so reading this puts more of their problems in to perspective.

Keep the data secure is a big one along with how to limit what a user can access.

Plus having the database exposed means its easier to attack from outside and that's where all the important stuff is.

Postgraphile [1] is very close to what you are looking for.

It is really amazing what modern databases are capable of and at the same time also surprising that most applications in the wild don't use them to the fullest of their capabilities.

I have been personally exploring on ability to efficiently map GraphQL queries to database operations while retaining the ability to write business logic in typescript (which I currently believe offers the best compromise wrt type safety and flexibility among popular mainstream languages). My preliminary work is available on github [2] and I welcome any feedback and contributions.

[1] https://www.graphile.org/postgraphile/

[2] https://github.com/gql-dal/greldal

Postgraphile is fantastic, in particular the function/row binding stuff that any python programmer would instantly recognize as implicit first argument self binding. We create graphql apis in minutes with it. No rolls, no restarts.

PostgreSQL row-level security policies seem to be sufficient for access permissions most of the time (I'm also finding them easier to manage safely than custom restrictions), while its constraints can deal with validation. Timeouts (and/or per-user time quotas) in front of a DBMS can be used if one is worried about users abusing a database.

As mentioned in another reply, there seems to be quite a reluctance to use/accept plain SQL, and SQL injections are usually treated as vulnerabilities, though I doubt that it's necessarily worse (that is, more of a potential vulnerability) than an average web back end.

Users (not as in "API users", but as in "service users") usually aren't familiar with SQL, and it's not great to write queries manually each time (even if there are views and stored procedures with predefined ones), but SQL being exposed to clients ("API users") is reality, even if uncommon. Coincidentally, I worked on a project like that just this week, and on others taking such an approach in the past.

Traditional web frameworks typically have far higher security risks, for example, Django executes all queries as the same superuser. Once someone cracks your django, the database is toast. With an RLS based system like both PostgREST and Postgraphile leverage, the application has no more privledges than any other user with a valid key.

Once you give users direct access to your db, you can no longer change it. That's why there are abstractions which allow changing the underlying data layer without changing the public API.

I was writing to agree with this and then thought about column aliases and views, which are two ways to hide the underlying database structure that SQL provides out of the box.

From the sqler readme I couldn't tell if either of those were available but I have used columns aliases when I have used other SQL to API gateways (like http://restsql.org ).

Such db level features definitely don't give you as much power to abstract as a full middle tier, but at least it is something. The trade may be worthwhile for the speed of API building. (I have worked with Google sheets as an API provider and that was far less flexible than sqler appears.)

It is always possible (though perhaps not pleasant) to swap out an API built on this type of direct SQL access technology with one built on a traditional middle tier.

One of the suggested ways to use PostgREST is that rather than pointing it at the Postgres schema called "public" (where created tables live by default), you point it at a schema consisting of nothing but SQL Views which communicate with those tables. That way, the underlying table structure can be altered without affecting the clients that consume the auto-generated HTTP interface.

It's more effort to have to maintain some extra SQL code for those views, but with all the labour-saving PostgREST delivers in the first place, you still come out ahead.

But if you’re going to do all that why not just put an abstraction in code (that could actually have some automated tests)?

There are test frameworks for postgres.

https://pgtap.org/

what can be very bad is if the database provided api is really just a sql executor - binding you to the database without you realising it. Doing rest, but finding you just tied all your clients to your database representation

Nope, that's what views are for.

https://www.postgresql.org/docs/current/tutorial-views.html

Views are the abstraction layer.

https://www.postgresql.org/docs/current/tutorial-views.html

See datasette that turns any csv or sqlite to an API and a web site instantly and allows the end user to write sql queries.

- For permissions: the db is readonly. - For performance: there is an execution time limit for each query.

https://simonwillison.net/2017/Nov/13/datasette/

It’s not a direct solution to this, but take a look at Snowflake. You can partition off subsets of the DB and provide direct SQL access to just that part, for either internal or customer use. It’s mainly for analytics purposes versus production, but it might handle some light real-time loads I guess.

permissions are not a problem, you can specify exactly down to a cell what permissions a user has (read/write/delete). The problem is D.O.S., One can write a query that takes down any database without having access to any tables/data.

I wouldn't say any API. More like, there is a class of problems and a style of api design that models the resources very closely to how they are represented in the database - and you often find them in web apps. The style is quite common though.

Very well said.

It also pairs beautifully with the read model in CQRS inspired designs.

Disregarding security issues, it breaks a level of abstraction. If you provide an API for say "user data", the client doesn't have to know the schema behind it. Whether the data is stored in a single table or multiple tables. Imagine if you have been using a system for a while and then they decide to alter the schema. You would have to relearn the schema, change all the queries you've been using, etc. Whereas with an API, backend changes wouldn't affect you as you'd still be getting the same "user data" via the API.

Another problem is the SQL flavor implemented by RDBMs. Every major RBDMs has their own distinct flavor of SQL. So forcing everyone else to learn a specific flavor of SQL is going to be a hassle. Especially if the RDBMs was changed.

But the biggest issue is security and performance ( especially true for sensitive data ).

APIs are there to make things secure, user friendly, consistent and of course robust in the face of back-end changes. I doubt most companies or people would allow direct access to their DBs. If people wanted to run queries on their dataset, they might let you download the dataset and run the queries locally.

Views are the abstraction layer:

https://www.postgresql.org/docs/current/tutorial-views.html

Your application layer "security system" is just row level security done outside the db.

And there are other automatic API servers like PostgREST. Some are RESTful like PostgREST and some provide a GraphQL API. I maintain a list: https://github.com/dbohdan/automatic-api/.

Good list, thank you.

One thing I found hard to grasp with Postgrest is SQL based authentication and authorization. It's not easy to understand and implement. It will be great if Postgrest also provides an auth webhook like SQLer. That way users can write or plug-in authentication and authorization in a language/framework they are more familiar with.

Totally agree. I feel it is such a powerful feature but so obscure. I have a solution up, taken from other sources, but what's actually happening behind the scenes is kinda mystery to me. Makes me anxious.

It's mysterious to you. For the rest of us is thoroughly documented:

http://postgrest.org/en/v5.2/tutorials/tut1.html

http://postgrest.org/en/v5.2/auth.html

https://www.postgresql.org/docs/current/user-manag.html

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

Is it possible to do JWT rotation?

Zero configuration to generate REST APIs for any MySQL databases

(shameless plug)

I really like the idea behind this. It's a bit like PostgREST, except with tht, if you want custom SQL, you need to make views or stored procedures. With SQLer you write the scripts on your application server, which has all the usual pros and cons of having code live on the appserver vs in the database. I think most people prefer the code in the appserver, which theoretically would make SQLer the better choice at least from that perspective.

I sorely miss a comment about security authentication, however. How are sessions done? Cookies? Etc?

More fundamentally, I find myself really liking many things that are inspired by CGI. I think that when we moved from CGI to monolithic application servers two decades ago, despite all the good reasons to do so, we threw a lot of babies out with the bathwater. There's an elegance to the "http request? just run some code" model that only very recently got popular again with AWS Lambda and the likes.

This is nothing like postgrest :) In postgrest the query is mostly determined by the the user calling the API, in SQLer the query is hardcoded by the api developer, the user just supplies the parameters to the query.

I do something similar with Rails and Postgres. I've never been good at publicizing tools I've built.

I think the biggest problem with Rails is Arel and by making it easier to write raw queries that serve json straight from the database you get great performance, composability and its still all really simple.

I don't need GraphQL because its so easy to write in one one endpoint everything I need to get back from my database and then write endpoints for all the smaller endpoints.

But yeah my project is basically handlebars meets SQL. I'm currently working on writing my own template language and hope to port it to other common languages.

https://github.com/monsterboxpro/monster-queries

I get really depressed working on projects with a million small endpoints or they take the other extreme of using GraphQL.

I'm quite tired of Full-stack development since things are taking 100x more effort and not because things are getting more complicated because they can't see the simpler solution.

I love the idea behind this and PostgREST, but one thing keeps me from using on projects with a team: versioning. I've been using it for personal projects and, while doing CREATE POLICY, CREATE ROLE here and there is easy when working alone, I can only expect a nightmare when working with a medium-sized team.

Migrations are, of course, a possible solutions, but I'm yet to see a decent toolset for managing schema changes.

https://martinfowler.com/articles/evodb.html

http://www.jeremyjarrell.com/using-flyway-db-with-distribute...

https://docs.subzero.cloud/managing-migrations/

Don't know why exactly, but I'm not sure if that headline is for a new piece of tech or a new security flaw.

Also try jHipster. Bakes you a full stack application from just the data model: https://github.com/jhipster/generator-jhipster

Can second this, have multiple times in the past to quickly spin up prototypes/internal apps, worked nicely.

wow, I didn't know about jhipster, this is nice!

> {{ .BindVar "name" .Input.user_name }}

Now, this definitely looks like a programming language construct. How long before this project gets loops and conditionals? See for example ansible.

It uses HCL and the new unreleased version does have loops and conditionals:

https://www.hashicorp.com/blog/terraform-0-1-2-preview

Interesting use of hashicorp's configuration language. How has it been working with it? Why use it rather than json, toml, or yaml?

I arrived at something similar recently in Rust, using actix-web. However, as an open source author of prior work (Yosai, in python), I realize that releasing this work is only the beginning. There's a lot more effort required to make an open source project viable. I'd gladly talk with folks about how one might do it, though.

It would be helpful to understand the demand for a project like this (and postgrest et al). What are the use cases where people finding value in creating enterprise-quality auto-generated endpoints?

Doesn't GraphQL eclipse a RESTful interface when interfacing with a database?

e.g. https://github.com/prisma/prisma

Also have a look at DreamFactory (https://www.dreamfactory.com/), an open source API generation and management platform. DreamFactory includes connectors for 18 databases, numerous file systems, e-mail delivery, mobile notifications, and more. Also API key management, roles, limiting, and logging. shameless plug

XQuery was much more suited for this than SQL will ever be, so much so that some naive people made a thing called XRX https://en.wikipedia.org/wiki/XRX_(web_application_architect...

Did it take off? Of course not.

Reminds me of this https://open.blogs.nytimes.com/2007/07/25/introducing-dbslay.... The source can still be found on github. Except dbslayer is supposed to be readonly.

> sqler uses nginx style configuration language (HCL)

nginx configuration looks nothing like HCL (which is json compatible)

I didn't see it mentioned, but OpenRESTy has my business for declarative JSON APIs backed by PostgreSQL. It's nginx so it's got a bit of momentum working for it.

It's not critical but having a bcrypt function which doesn't take in the cost parameter feels like bad practice for the future.

Something like Flask doesn't take too much more effort to achieve something similar, and will be a lot more flexible, no?

Its great, it would be even better if it could be deployed to AWS Lambda.

Not really limited to just RESTful APIs, you can write an RPC api as well and call stored procedures to perform all sorts of complex actions on your data.

Reply on news.ycombinator.com