Stripe Sigma

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.

Me too, but I'm disappointed this time... http://imgur.com/a/msL4T

I was surprised as this as well. I wouldn't think running these queries on small datasets would take much computing resources and would provide a nice competitive advantage over other alternatives.

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.

Technically, how would something like this architecturally be structured? Curious how they would sandbox the user data and still provide a SQL like interface.

I wrote something for Doctrine last year where I let my users query their own data using DQL. I had to write a TONNE of AST walkers to ensure that they weren't accessing entities they didn't have permissions to. Was a huge issue that I wish I didn't start :D (But one of those cool things when it was finished)