Friday, March 25, 2022
HomeCloud ComputingHow SQL can unify entry to APIs

How SQL can unify entry to APIs

Within the unique proposal for the World Extensive Net, Tim Berners-Lee wrote:

A generic instrument might maybe be made to permit any database which makes use of a business DBMS to be displayed as a hypertext view.

We did get these hypertext views, within the type of APIs, however not in a generic manner. Database-backed internet functions grew APIs that yielded numerous XML after which JSON outputs. Programming languages grew libraries to assist builders devour these outputs. Studying to make use of every particular person API was difficult, becoming a member of outputs from a number of APIs much more so. 

In 2009 I used to be constructing a system to mix calendar data from many sources. It requested the identical query of all of them: What occasions are scheduled for a given place and time? To do this it had to make use of a half-dozen APIs, every requiring a unique option to make a request and unpack the response.

So after I discovered about Mission Astoria I used to be a right away fan. Astoria was the generic hypertext view of databases that we wanted. With Astoria layered on prime, each database might mechanically present a default API. If the half-dozen techniques I used to be querying for occasions supported one thing like Astoria, none would have wanted to invent bespoke APIs and all could be queryable in the identical constant manner.

The thought matured as Open Information, aka OData, an OASIS commonplace since 2014. In precept any database-backed internet app might now sport an “OData head” that would supply a default API, requiring no code to be written by builders of the app, and no new request/response protocols to be discovered by builders utilizing the API. 

In follow that principally hasn’t occurred. 

Greater than ever, software program development requires builders to compose options utilizing a rising proliferation of APIs. Usually there’s a library to wrap every API in your programming language of selection, so that you’re spared the trouble of constructing uncooked REST calls and parsing the outcomes. However every wrapper has its personal manner of representing outcomes, so when composing a multi-API answer it’s a must to normalize these representations. Since combining outcomes occurs in a language-specific manner, your answer is tied to that language. And if that language is JavaScript or Python or Java or C# then it’s arguably not probably the most common and highly effective option to question (or replace) a database.

What’s one of the best ways? It’s been hiding in plain sight all alongside: SQL. Battle-hardened for many years, and advanced past the pure relational mannequin, SQL has restablished itself because the preeminent interface to knowledge. And it’s positioned to develop into the API unifier that we want greater than ever.

Overseas knowledge wrappers for APIs

Steampipe ( is an open-source instrument that fetches knowledge from numerous APIs and makes use of it to populate tables in a database. The database is Postgres, which is, these days, a platform on which to construct every kind of database-like techniques by creating extensions that deeply customise the core. One class of Postgres extension, the international knowledge wrapper (FDW), creates tables from exterior knowledge. Steampipe embeds an occasion of Postgres that hundreds an API-oriented international knowledge wrapper. The FDW in flip communicates with a rising household of plug-ins that devour APIs and feed the info by way of the FDW into Postgres tables.

To make these abstractions concrete, ask your self how you’d clear up the next drawback. You use public AWS providers, and also you’d wish to know if any of their endpoints present up as susceptible in Shodan, a service that scans public endpoints. Your answer most likely appears one thing like this:

  1. Discover ways to use the AWS API that finds your endpoints
  2. Discover ways to use the Shodan API the checks your endpoints
  3. Discover ways to mix these two APIs to reply the query

Right here’s the Steampipe answer.

  aws_ec2_instance a
left be a part of
  shodan_host s on a.public_ip_address = s.ip
the place
  a.public_ip_address will not be null;
| instance_id         | ports    | vulns              | security_groups                                             |
| i-0dc60dd191cb84239 | <null>   | <null>             | [{"GroupId":"sg-042fe79169eb42818","GroupName":"lockdown"}] |
| i-042a51a815773780d | [80,22]  | <null>             | [{"GroupId":"sg-042042bac705630f4","GroupName":"bastion"}]  |
| i-00cf426db9b8a58b6 | [22]     | <null>             | [{"GroupId":"sg-0423f79169eb42818","GroupName":"default"}]  |
| i-0e97f373db42dfa3f | [22,111] | ["CVE-2018-15919"] | [{"GroupId":"sg-0423f79169eb42818","GroupName":"default"}]  |

The 2 tables joined listed below are supplied by Steampipe plug-ins for AWS and Shodan. The primary maps the sprawling catalog of AWS APIs to (at present) 269 tables; the second gives a dozen Shodan tables.

You configure these plug-ins to authenticate to the APIs with the identical credentials you’d want if utilizing the APIs instantly. However you don’t must know the rest about underlying REST calls, or libraries wrapped round them. The answer is produced from tables that work the identical manner inside and throughout APIs. You examine them (aws_ec2_instance, shodan_host) to find the names of their columns, and also you be a part of them within the time-honored SQL manner.

A plug-in for each API

Clearly this two-API answer relies on the existence of plug-ins to map each APIs to tables. If each providers applied OData that wouldn’t be essential. The APIs would mechanically be queryable, albeit arguably not joinable with the class that SQL affords. However these two providers, like most, don’t current a unified interface to their APIs. In order that unification needs to be layered on prime of them. Steampipe’s plug-in SDK smooths the way in which for plug-in authors by abstracting connection administration, retry logic, caching, and naturally the mapping of API outcomes to tables. 

Steampipe plug-ins are written in Go. They leverage the excellent catalog of Go libraries that wrap APIs. However solely plug-in authors must know that. As a developer working with Steampipe you solely see tables, and also you solely write SQL. These days, as a result of SQL has advanced, that features options like Frequent Desk Expressions (aka CTEs or WITH clauses) and JSON columns. However it’s nonetheless simply SQL.

Can such plug-ins feasibly be constructed for each API? Nicely, Steampipe launched in early 2021 with a handful of plug-ins, right this moment there are greater than 60, and the quantity is growing rapidly. Up to now most have been written by the core group however exterior contributions are rising. Because of the plug-in SDK, which does the heavy lifting, it’s simple to construct a plug-in that maps an API to a set of tables. 

Standing on the shoulders of Postgres

By embedding Postgres, Steampipe inherits all of its capabilities. So, for instance, you possibly can be a part of API-sourced international tables with native Postgres tables. And whereas Steampipe’s main profit is dwell querying of APIs, you possibly can create materialized views to persist that knowledge and write Postgres features to function on it. You possibly can even load different Postgres extensions and use them with Steampipe tables. Postgres’s built-in tablefunc extension, for instance, can do crosstabs, in SQL, with spreadsheet knowledge from Steampipe’s Google Sheets plug-in.

One other advantage of embedding Postgres: Any Postgres-compatible API consumer can connect with Steampipe. That features command-line instruments like psql and GUI-based ones like Tableau, Energy BI, Metabase, and Superset that carry visualization and interactivity to dwell API knowledge. 

Postgres could by no means be as extensively embedded as the ever present SQLite nevertheless it’s extra succesful, and more and more it’s used to energy an ecosystem of interoperable instruments for working with knowledge. Steampipe extends Postgres to ship unified entry to APIs, and a standard SQL surroundings by which to motive concerning the knowledge they supply.

Jon Udell is the group lead for Steampipe at Turbot.

New Tech Discussion board gives a venue to discover and focus on rising enterprise know-how in unprecedented depth and breadth. The choice is subjective, primarily based on our decide of the applied sciences we imagine to be necessary and of best curiosity to InfoWorld readers. InfoWorld doesn’t settle for advertising and marketing collateral for publication and reserves the fitting to edit all contributed content material. Ship all inquiries to

Copyright © 2022 IDG Communications, Inc.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments