[wppick]

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?

[lf-non]

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

[michelpp]

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.

[laurent123456]

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.

[frou_dh]

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.

[duncan-donuts]

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

[michelpp]

There are test frameworks for postgres.

https://pgtap.org/

[mooreds]

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.

[michelpp]

Views are the abstraction layer.

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

[edsykes]

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

[michelpp]

Nope, that's what views are for.

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

[defanor]

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.

[michelpp]

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.

[ludwigvan]

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/

[edsykes]

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.

[tomnipotent]

Very well said.

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

[reaperducer]

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.”

[lowercased]

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

[glaberficken]

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)

[lowercased]

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.

[ruslan_talpa]

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.

[apapli]

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.

[vorticalbox]

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.

[porpoisely]

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.

[michelpp]

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.