(2022-02-01) Udell How Sql Can Unify Access To Apis

Jon Udell: How SQL can unify access to APIs. In the original proposal for the World Wide Web, Tim Berners-Lee wrote: A generic tool could perhaps be made to allow any database which uses a commercial DBMS to be displayed as a hypertext view

We did get those hypertext views, in the form of APIs, but not in a generic way

In 2009 I was building a system to combine calendar information from many sources

it had to use a half-dozen APIs, each requiring a different way to make a request and unpack the response.

So when I learned about Project Astoria I was an immediate fan. Astoria was the generic hypertext view of databases that we needed.

The idea matured as Open Data, aka OData, an OASIS standard since 2014.

In practice that mostly hasn’t happened

More than ever, software construction requires developers to compose solutions using a growing proliferation of APIs.

if that language is JavaScript or Python or Java or C# then it is arguably not the most universal and powerful way to query (or update) a database.

What is the best way? It’s been hiding in plain sight all along: SQL.

Foreign data wrappers for APIs

Steampipe (steampipe.io) is an open-source tool that fetches data from diverse APIs and uses it to populate tables in a database. The database is Postgresql, which is, nowadays, a platform on which to build all kinds of database-like systems by creating extensions that deeply customize the core. One class of Postgres extension, the foreign data wrapper (FDW), creates tables from external data.

The two tables joined here are provided by Steampipe plug-ins for AWS and Shodan. The first maps the sprawling catalog of AWS APIs to (currently) 269 tables; the second provides a dozen Shodan tables.

Clearly this two-API solution depends on the existence of plug-ins to map both APIs to tables. If both services implemented OData that wouldn’t be necessary

Steampipe’s plug-in SDK smooths the way for plug-in authors by abstracting connection management, retry logic, caching, and of course the mapping of API results to tables.

Steampipe plug-ins are written in Go. They leverage the comprehensive catalog of Go libraries that wrap APIs. But only plug-in authors need to know that.

Can such plug-ins feasibly be built for every API? Well, Steampipe launched in early 2021 with a handful of plug-ins, today there are more than 60, and the number is increasing quickly.

By embedding Postgres, Steampipe inherits all of its capabilities...