Intentionally or not, many enterprises have found themselves moving data away from traditional databases and into SaaS applications. While this almost always opens up new possibilities, the novel (and sometimes inconsistent) nature of web APIs used to integrate with that data can prevent us from getting the most out of existing systems, which are built for good ol’ databases.

In this post, we’ll discuss how SQL-friendly web APIs might be an appealing middle ground, enabling enterprises to connect systems new and old. We’ll look at some examples of how SQL access has been implemented in modern-day APIs, what benefits that provides, and some of the challenges, solutions, and tips involved in its implementation — with plenty of insights from Jerod Johnson’s presentation on this subject.

SQL in the World of APIs

SQL, or Structured Query Language, is the de facto standard for those looking to interact with a database programmatically. It enables developers to find, filter, sort, modify, and delete data with simple, readable queries.

A lot of the time, applications use APIs to retrieve or edit a platform’s internal data — which begs the question, wouldn’t it be great if we could use SQL to do so? As it turns out, some APIs already offer a dedicated query endpoint, which can read SQL or SQL-like queries such as the following:

SELECT *
FROM speakers
WHERE name = "Jerod Johnson"

Examples

In his talk, Jerod shares three examples of popular API products which support SQL or SQL-like queries:

• Google BigQuery allows developers to interact with their BigQuery data using standard SQL queries (based on the 2011 standard).
• Couchbase supports queries in two SQL-like languages: SQL++ and their proprietary N1QL (pronounced “nickel”). While the basic format of queries is the same as regular SQL, the response is always a JSON document.
• Salesforce also has two SQL-like languages: Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL).

There are plenty more APIs that support SQL access — Amazon S3, Elastic, MongoDB, SAP Business One, Snowflake, Veeva Vault, and others.

Benefits of SQL-Friendly APIs

Why is it that tech giants like Google are choosing to implement SQL in their APIs? There are numerous benefits of doing so, including ease of integration, simplified integration with tooling, and server-side processing.

Ease of Integration

SQL access makes it much easier for developers to use a new API. If they know how to write SQL queries, they can find out what objects exist, what fields exist within those objects, what relationships exist between objects, and much more, without having to familiarize themselves with the endpoints of that specific API. As the number of APIs and API integrations continues to grow, the ability to interact with APIs consistently and predictably is only going to become more valuable.

Simplified Integration with Tooling

A considerable number of business intelligence tools and platforms are built with native support for SQL. By enabling SQL access in your API, developers can pass queries from these sources directly to the API, quickly creating new data flows. Jerod provides the example of Tableau, a data visualization tool that uses SQL queries to populate visualizations. If an API includes support for these queries, all developers have to do is pass them over from Tableau; otherwise, they need to build customer connectors.

Server-Side Processing

SQL access also enables developers to leverage server-side processing. By passing a query with WHERE and/or ORDER clauses, developers ask the API to filter and/or sort data before returning it. This reduces the amount of data processing necessary on the client-side (once again enabling easier integration) and reduces unnecessary bandwidth usage.

Use Cases for SQL Access

The benefits discussed above are pretty universal. But there are some specific situations where SQL access can shine. Jerod shares these use cases as ideal candidates for SQL:

• Retrieving data from multiple related objects
• Retrieving parts of fields, like extracting the year from a DateTime
• Counting the number of records that match specific criteria
• Sorting results, where not possible inherently

Challenges of Consuming SQL APIs

For developers, SQL access isn’t all sunshine and rainbows. Unfortunately, there are a couple of challenges associated with consuming SQL-friendly APIs that might be holding back from their adoption.

First, an API providing SQL access doesn’t always mean it will return an SQL response; as an example, consider the Couchbase API, which returns JSON documents. For developers and systems used to working with traditional databases, this means additional effort will be needed to consume the response. As a result, there may be situations in which it is just as easy to interact with the API’s resources directly.

Second, SQL access is not consistent across APIs. Some use standard SQL, but many others use SQL-like, sometimes proprietary query languages. As a result, developers may still experience a learning curve, and not all systems and APIs will be compatible from the get-go.

There is, of course, a solution to these challenges.

Standards-Based Drivers for SQL Access

APIs are usually consumed in one of three ways. With direct integration, developers make calls to the API directly. With software development kits or SDKs, developers leverage language-specific libraries to simplify interactions with the API. With middleware, developers make calls to the API indirectly, instead passing them through a central service.

The thing is, none of these approaches work particularly well for interacting with SQL. So, to facilitate SQL-driven API interactions, Jerod offers a fourth solution: standards-based drivers. These drivers, created by the API provider, enable developers to interact with the API through SQL as if it were a database. They achieve this by adhering to popular database standards like JDBC, ODBC, and ADO.NET, which also makes for seamless integration with database-ready enterprise systems.

Modeling APIs as Databases

It might come as a surprise that modeling data-oriented APIs — especially REST APIs — as databases is relatively straightforward. Resource collections, like orders, accounts, pages, or users, end up corresponding to tables and views. Then, individual objects within those collections correspond to rows in the table. Sub-collection relationships, like the order line items within an order, are maintained.

As for operations, traditional CRUD operations can be translated (roughly) into SQL statements with relative ease, as follows:

Operations and functions with no clear SQL counterparts, like uploading a document, generating a report, or sending an email, are implemented with stored procedures.

Conclusion

Providing SQL access is an interesting way for SaaS platforms to accelerate enterprise adoption of their web APIs. One reason for this is that enterprise developers and systems are often accustomed to working with traditional databases. Equally important, as the number of APIs grows, enterprise developers are on the lookout for standardized ways to interact with them.

While there are challenges associated with consuming SQL-friendly APIs, they can often be solved with standards-based drivers. Best of all, modeling resource-oriented APIs as databases isn’t all too difficult, which makes implementing SQL-access relatively straightforward.