SQL as an API language
Note that this is solely in the context of a static data set. That makes this a relatively limited idea, since most datasets are either dynamic or small enough to just copy over. I write this not to dismiss Datasette, which looks like an excellently designed tool for the problem it's meant to tackle, but mostly as a response to the title of this particular HN submission, "SQL as an API language" which suggests maybe more than "just" querying static data.Thing is, I really want to use/expose SQL as an API language for dynamic data.E.g. the author writes:> On hidden implementation details: since we are publishing static data rather than maintaining an evolving API, we can mostly ignore this issue. If you are really worried about it you can take advantage of canned queries and SQL view definitions to expose a carefully selected forward-compatible view into your data.At my company (https://talkjs.com) we explored making our public API SQL-based for the chat & message data that our customers host with us. It would solve lots of customer requests in one go, much more so than adding a new REST resource for every kind of question a customer might want to ask our API. What's been holding us back is exactly this: Even if we'd carefully design a set of database views to guarantee backward compatibility, doing so will severely limit the kinds of database refactorings we'd be able to do. Especially when it comes to adding redundancy, denormalizing stuff, etc.If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem?