Comparing Different "SQL over HTTP" APIs

Several database products and database-as-a-service (DBaaS) providers support some sort of "SQL over HTTP" API, also sometimes referred to as a "Data API". These have a couple of different use cases:

• Supporting environments where open connections cannot be cached/reused (so, essentially, client-side pooling doesn't make sense).
  • The main example here are serverless functions, which are stateless and asynchronous.
• Building applications that leverage a direct connection from the client/frontend to the database.
  • This is a bit more niche, but a certainly valid use case, as is evidenced by popular Postgres extensions such as PostGraphile and PostgREST.
  • A lot of DBaaS providers use these APIs in order to implement their own management dashboards, which often include a SQL Editor feature.

(The MongoDB Docs have some good content regarding when to use these "Data" APIs.)

What are some examples of these APIs?

• Snowflake SQL REST API
• SingleStoreDB Data API
• MongoDB Data API (only for Atlas)
• SQL over HTTP for Xata Postgres
• Neon Postgres HTTP and WebSockets
• PostgREST for Postgres (which powers Supabase)
  • Not really "SQL over HTTP", it's SQL turned into a RESTful API.
• PostGraphile for Postgres
  • Not really "SQL over HTTP", it's SQL turned into a GraphQL API.
• Elastic SQL REST API
• PlanetScale Serverless Driver (HTTP)

This is not a comprehensive list, there's definitely more examples! However, this list is good enough for us to dive into some interesting design and implementation details below.

Let's look at how these APIs tackle different issues.

How do these various APIs differ?

There's a few different aspects for us to compare across these different APIs:

• Authentication
• Result pagination
• Overall API design
  • SQL over HTTP vs. REST/GraphQL
  • Syncs vs Async
  • Column-by-column results vs. row-by-row results
  • Query parametrization
  • Support for multiple query statements per requests
  • Data type conversion
• Transaction support
• HTTP vs. WebSockets

Let's start by looking at the biggest discrepancy between these APIs: SQL over HTTP vs. REST/GraphQL. This is such a fundamental difference that it warrants its own section. Most of the "SQL over HTTP" APIs mentioned above accept raw SQL and return JSON responses. However, I wouldn't consider certain tools such as PostgREST in the same category. This is because they don't allow for "raw SQL" to be passed in (not easily anyway). As such, they belong more in the realm of "database to API" tools which includes other services like Hasura and Grafbase.

As for the rest of the categories, I've put together a large multi-dimensional table with some comments on each API:

This table was quite a lot of work to put together, but even still it should probably not be used as a "reference" because all of these products have been evolving over time, and so it's quite likely that it'll be out of date soon.

With that in mind, I'd like to touch on a few important aspects of each API's design that I find particularly relevant.

Overall Documentation

I found the documentation for most of the APIs to be very good. Unfortunately, both PlanetScale and Neon seem to only talk about their HTTP APIs in the context of their JavaScript drivers — and they don't have docs for their raw HTTP APIs (which their JS SDKs leverage underneath).

Authentication

Most of these APIs support JWTs and some of them even support third-party signed JWTs which can be verified with a custom JWKS. This is awesome! It's a very flexible and very powerful authentication mechanism. As expected, all of these APIs receive these tokens via the Authorization HTTP Header (typically with a Bearer-style token).

Pagination

Only a couple of these APIs support pagination, which is understandable. It requires the API to be stateful which makes the API service much harder to build. So, the vast majority of the "SQL over HTTP" APIs leave pagination at the responsibility of the client.

Sync vs. Async

Only the data warehouses (Snowflake, Elastic) support async query execution. This makes perfect sense — again, it introduces a lot of complexity to the implementation of the API which is only worthwhile to do for systems that expect their queries to take too long for regular HTTP timeouts (which can be the case for analytical queries).

The Snowflake SQL API even supports canceling an ongoing query.

Data Type Conversion

When it comes to mapping from native database types to JSON APIs, some of the APIs offer a good amount of customization. I especially liked SingleStore's docs on how they map database data types into JSON types.

Transactions

Transactions over HTTP are complicated. The PlanetScale Serverless Driver (which is HTTP powered) supports transactions, and so does the Neon Serverless Driver, but via WebSockets (and the isolation mode can even be specified). However, if you're depending on transactions over HTTP I would highly recommend that you study how they're implemented by the driver you're using. With some of them, it may not be possible to use intermediate query return values in later queries inside the same transactions. And certain "SQL over HTTP" APIs may have other limitations around transactions. Be careful!

HTTP vs. WebSockets

It seems that Neon is the only provider that officially supports WebSockets. (I know at least one other provider has a WebSockets API but has simply not documented it.)

Wrapping Up

I mainly wrote this blog post as a way to "force" myself to go deep on the API designs for all of these competing products and services. Having said that, I hope it's useful to others who are looking to understand the problem space.

I'm sure there's a couple of mistakes and other things I could improve on this blog post. If you have any feedback, feel free to reach out on Twitter!