Comment by pjungwir | Hacker News Reader

Comment by pjungwir | Hacker News Reader

Parent Full thread

I've only glanced at this, but I love the idea of making your API be SQL. I did this recently for a company whose customers have a lot of "power users" who are not professional programmers but use tools like R, Excel, Tableau, or even Python. The customers are very happy with it! We saved a ton of time not building a JSON API, which our customers wouldn't be able to use anyway.

Basically we create a customer-specific database whose contents are all derived from our real OLTP database, and give them read-only access to it. In our case they also have to be on a VPN, but we've talked about adding a web-based SQL console like I see here.

It's funny that at first this approach sounds crazy, but really it is what companies have been doing since the 90s: creating flattened read-only reporting databases. If you want to do this, you should read Ralph Kimball's _Data Warehouse Toolkit_ for some inspiration and guidance.

Of course you don't want to give customers access to your OLTP database. Besides the security and performance problems there, if your core database becomes a public API, it is frozen and you can never add another feature again. But a separate reporting database still gives you plenty of flexibility to grow.

My first reaction to seeing an SQL-based API is 'ugh'. SQL is complex and a little cumbersome, and easy to get wrong.

My second reaction is 'oh, thank god'. I'm so tired of having APIs where I have to go through the 'get the list of users', 'iterate over the list to create a map', 'get the list of messages', 'iterate over the list of messages and add the username to the data structure', 'oh wait I have to get the channel list separately too, seriously?', 'get the list of channels', 'iterate over the list to create a map', etc.

I love Slack's API, for example, but having to fetch a list of users, along with the entirety of their profile data and everything about them, then do the same for channels, then do the same for messages, then tie everything together, it's very cumbersome compared to

SELECT m.message, m.when, u.username, u.name, c.name FROM messages m LEFT JOIN users u ON (m.uid == u.uid) LEFT JOIN (m.cid == c.cid) where c.name == "#general" LIMIT 50;

(or whatever it should be, I haven't done SQL in ages)

And I also get a lot fewer records, especially when I'm trying to get the last 50 messages from #general on a Slack instance that has 400 users and 200 channels.

That's one of the problems that GraphQL is intended to solve, specifically you don't want to write your API queries directly in SQL but you also want the flexibility to chain queries together and specify the exact types of data you require.

Wonder what you'd think of a pull + datalog mix, which is what Datomic / Datascript do: https://github.com/Datomic/day-of-datomic/blob/master/tutori... The neat thing about the syntax there is that it's a datastructure which if you write a generic visual editor like https://developers.google.com/blockly/ over that only fits puzzle pieces when they satisfy https://clojure.org/about/spec you could...

For an internal hackathon we basically tried to do this but ran out of time. We built our own query driven API layer that is basically a shim over datomics query and pull apis.

A question I'm asking myself is the security one. Obviously the accessed databases only contain the customer's own information with read-only access. But how do you prevent resource exhaustion / DoS attacks trough overly complicated queries? This is the obvious vector that comes to my mind but I wouldn't be surprised if there were a few other ones similar to it.

Reply on news.ycombinator.com