Comment by simonw | Hacker News Reader

Comment by simonw | Hacker News Reader

Wasn't expecting this to show up here!

I've been playing around with SQL as an API querying language for a couple of years now as part of my Datasette project: https://datasette.readthedocs.io/en/stable/

I started out thinking that SQL for API queries was genuinely a terrible idea. Datasette supported it because it's designed as an ad-hoc querying tool (which happens to be able to export resuls as JSON), but obviously that was an awful idea for production applications.

Then I started building a few SQL-in-client-side-JavaScript applications, just as a rapid prototyping tool. And they worked amazingly well. Here's an article I wrote about that in January 2018: https://24ways.org/2018/fast-autocomplete-search-for-your-we... (really simple demo here: https://media.24ways.org/2018/willison/ )

I keep on waiting for the obvious downsides to show up, and they keep stubbornly refusing to appear. And meanwhile the rest of the software world is going all-in on GraphQL which seems MUCH harder to work with - and less performant.

I've tried this a couple times in the past. I think the biggest issue I've ever run into is from database modifications. In those cases, instead of merely needing to update my queries in my api server, then I needed to update my client code, and this gets much harder with multiple clients

Of course, this can also be the case when changing the API, but deprecated fields can be faked in that portion of the application to allow existing applications to continue to work.

I think it comes down to where you're managing your database interactions. I don't care about most features in an orm, so I maintain my own that's barely anything more than a SQL generator that won't break every time I change the database.

Overall, I think SQL on the client is excellent - even ideal. And as a matter of fact, I try to develop apis that simply generate a user-specific excerpt of our database, where SQL would be perfect for querying the data locally on the client. Last year I tried running sqlite in the browser for this exact reason, but it was far too heavy.

Anyway, back to the issue - the idea of having to immediately adjust all our client software every time I make a database adjustment is a bit anxiety-inducing. Especially when building fast In a drive toward market fit .

That said, with some great logging that tracks the actual queries and where they come from, it probably wouldn't be so bad. Especially if there's a means to maintain backwards compatibility.

What if the tables your API queried were actually views? You could do the same faking of data during migrations of the underlying data (structure) and the client should not be effected.

What if the "views" your API queries didn't have to be tables? You could just keep "tables" as a backend implementation detail, and 'fake' a different model on top of them that's closer to what the client needs.

Amazing you mean some sort of intermediate data transfer language that could resolve requests into SQL in the server side! Genius!

I think what's becoming clear in this discussion is that there are some developers (myself definitely included) who would like to use SQL as an interface to plain programming structures outside of actual databases.

I understand postgres provides something like this, but it would be interesting as a library rather than a separate application. I'd love to be able to query hashmaps or arrays of data using SQL.

Maybe this already exists somewhere? My web-search-fu is failing me but I recall reading somewhere that facebook had something like this for querying systems, so maybe such a library exists extracted from that usecase?

Edit: Here's one. https://github.com/mozilla/moz-sql-parser

SQLite has “virtual tables” feature [0] which was designed exactly for this use-case.

Edit: and that Facebook project is called OsQuery [1]. It was built on top of SQLite virtual tables and is listed in their implementations list.

[0] https://www.sqlite.org/vtab.html

[1] https://osquery.readthedocs.io/en/stable/

Always LINQ:

https://docs.microsoft.com/en-us/dotnet/csharp/programming-g...

> I think what's becoming clear in this discussion is that there are some developers (myself definitely included) who would like to use SQL as an interface to plain programming structures outside of actual databases.

Myself included too. As a side project, I'm currently writing a game (roguelike), where all the game state that would ordinarily be put into an ECS or an object tree is instead stored in in-memory SQLite instance; the game's state management consists of (currently hand-written) SQL queries.

The reason for that is because, in trying to implement yet another version of an ECS-like architecture, I realize I'm essentially reinventing database queries and database indices, so I might as well use the real thing.

For Postgres you mean foreign data wrappers ? Or storing plain (mmappable?) binary data in bytea and creating a pg extension to serialize/deserialize? I'm working on both...

Aren't most SQL engines computational engines?

So even with tightly controlled accesses, one ill intentioned client might be able to produce a statement that would take a lot of resources. Even with a tight timeout the database might not be able to handle a sustained amount of computationally hard requests. I think SQL makes the attack surface much broader than a regular REST client. This wouldn't be a problem when the machines facing the client are stateless and able to check the request before accessing the data, whereas databases are traditionally single points of failures and hard(er) to scale.

Now, I'm comparing it with a REST client because I don't know graphQL enough, but it might be possible to do the same attack on a graphQL server?

I still chuckled to the idea, which I think was the primary intention so that was worth it anyway.

The tagline of Datasette is "A tool for exploring and publishing data", to me it makes a lot of sense that SQL (with appropriate restrictions) is a suitable API for the kinds of things you want to do with datasets. This is not really the typical web API usecase, you normally have much more specialized and restricted use scenarios.

What were some of the obvious downsides you were expecting, and why didn't it show up?

I suspect one dimension is if you're limited to readonly, SQL as API is pretty good.

Have you ventured to looking at datalog or Datomic? If so, what do you think?

How do you sanitize a SQL query that comes from the client?

Reply on news.ycombinator.com