6 ms·

Note that this is solely in the context of a static data set. That makes this a relatively limited idea, since most datasets are either dynamic or small enough

Note that this is solely in the context of a static data set. That makes this a relatively limited idea, since most datasets are either dynamic or small enough to just copy over. I write this not to dismiss Datasette, which looks like an excellently designed tool for the problem it's meant to tackle, but mostly as a response to the title of this particular HN submission, "SQL as an API language" which suggests maybe more than "just" querying static data. Thing is, I really want to use/expose SQL as an API language for dynamic data. E.g. the author writes: > On hidden implementation details: since we are publishing static data rather than maintaining an evolving API, we can mostly ignore this issue. If you are really worried about it you can take advantage of canned queries and SQL view definitions to expose a carefully selected forward-compatible view into your data. At my company (https://talkjs.com https://talkjs.com) we explored making our public API SQL-based for the chat & message data that our customers host with us. It would solve lots of customer requests in one go, much more so than adding a new REST resource for every kind of question a customer might want to ask our API. What's been holding us back is exactly this: Even if we'd carefully design a set of database views to guarantee backward compatibility, doing so will severely limit the kinds of database refactorings we'd be able to do. Especially when it comes to adding redundancy, denormalizing stuff, etc. If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem?

scrollaway 8y ago
What you're describing is exactly what GraphQL sets out to solve. Most of the flexibility and declarativeness of SQL, but still sitting a layer above the raw data itself.
- forkandwait 8y ago
  So why not pure SQL, which everyone knows already?
  - scrollaway 8y ago
    GraphQL has a lot of advantages over SQL, API-wise; namely it's a lot more natural to describe what you want to get back. But without getting into that, the point is that you usually don't want to give direct data access, with all that entails. You still want to be declaring the fields and properties of the data that users will be querying, and that usually sits a layer above your raw data. You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way. _______ Edit: Clarifying my stance on SQL views. I'm not dismissing it as a "bad tool", but there's good reasons it's not a popular one today. Writing SQL is not easy for everyone, and it's especially not easy for potential API consumers. The best APIs are those you grok. If you grok SQL, congratulations, but most devs don't, and APIs are usually designed for most people. Obviously, if SQL is the right tool for the job, use SQL. But don't start doing stuff like this for ideological reasons.
    - tathougies 8y ago
      I mean SQL views have been a thing for literally decades longer than GraphQL. If any technology should be accused of causing a 'lot of hassle to solve a solved problem', it's graphql, not SQL.
      - atombender 8y ago
        If your original data is flat and your app can consume it flat, no worries. But SQL is a worse fit for structured data, so you end up with an ORM on top, which is what GraphQL sort of is. Typically web apps are written with web technologies that work with structured objects, and you want something like {title: "Stapler", price: 1230, category: {id: "123", name: "Office supplies"}, mainPhoto: {url: "https://..." https://...", width: 600, height: 300}} back to feed directly in the UI. Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal.
        just_myles 8y ago
- aikah 8y ago
  > Most of the flexibility and declarativeness of SQL, does graphQL support aggregate functions like count,sum,average and co?
  - scrollaway 8y ago
    No, that's why I said most; and that's also what makes it more appropriate for APIs -- you don't want uncontrolled performance hitting you like this; if you wish to allow for aggregates, you expose them as properties.
    - derefr 8y ago
      > you don't want uncontrolled performance hitting you like this On RDBMSes with multitenancy support, you can simply apply resource constraints and quotas (in CPU time, CPU percentage, wall-clock time, memory, disk usage, etc.) to each user and to each connection. (You just have to ensure that each separate customer ends up hitting the DBMS as a separate DB user.) The entire point of the OLAP model is to allow clients to ask their own questions. Some of those questions may be too expensive to answer—modern DBMSes are smart enough to realize which queries those are, and allow the sysadmin to restrict users from doing them. But that restriction should always be a matter of scale, not of kind. Users should be able to do a very complex query over a small data-set, or a very simple query over a huge data-set, or a combination, as long as they aren't actually impacting the performance of the cluster for other users.
      - scrollaway 8y ago
        Sure. And if that's a model that is appropriate for your API users, go for it. But usually, it won't be appropriate. And by usually, I mean it will almost never be appropriate to adopt a model like this for your users. And I say this having been in the very situation where it was appropriate and we did expose it.
        derefr 8y ago
        Alternately, what's wrong with just tracking the expense of the user's query and then passing the costs of it back to them? Like most "scale-free" DBaaSes (Dynamo, BigQuery, etc.) do. This is assuming your users are paying for your API, mind you. I have no idea why you'd want to provide an online relational data-access service for free. (The "free" option for OLAP is, and has always been, "we give you a .zip of CSV files, and you ETL them into your own DB to look at them." That's how things like government GIS data or Amazon's and Google's hosted "public datasets" work—you pay for online access, or you get free offline snapshot dumps.)
- skrebbel 8y ago
  GraphQL is just a query language. It has exactly the same problems in this respect as SQL - once a certain query from a customer that you never imagined they'd write works, it has to keep working, even if your underlying data model totally changes.
  - scrollaway 8y ago
    This has a greatly reduced effect. What you have to keep updated is the accessible properties. As I said, it's similar to writing a SQL view and giving access to that. Difference being, GraphQL is a lot more appropriate especially for web REST endpoints
BerislavLopac 8y ago
> Note that this is solely in the context of a static data set. That makes this a relatively limited idea Not if you use the event sourcing approach.
o1lab 8y ago
If anyone is looking for apis for mysql : xmysql will be really helpful. https://github.com/o1lab/xmysql/ https://github.com/o1lab/xmysql/ Xmysql is just one command to generate REST APIs for any mysql database.
siscia 8y ago
Maybe I am a little biased, but once you define what SQL interface you want to provide to your users (so the schema of the tables) you have basically decoupled your internal data structure from the view of the client. At this point the only thing you need to do is to update the user facing database whenever necessary and keep it consistent with your view of the world. Yes, you have a new problem since you had to maintain the costumer view consistent with your view, but it may be a price worth paying. The way I would test this implementation is with RediSQL (https://RediSQL.com https://RediSQL.com) that I wrote (so I am definitely biased) but it may be worthed to test it.
pjungwir 8y ago
I've done it before. Some day I'll blog about it, but in the meantime here are a few places I've brought it up in HN comments: https://news.ycombinator.com/item?id=14464929 https://news.ycombinator.com/item?id=14464929 https://news.ycombinator.com/item?id=15335915 https://news.ycombinator.com/item?id=15335915 https://news.ycombinator.com/item?id=17820410 https://news.ycombinator.com/item?id=17820410 I agree with the concern about schema flexibility, although since you'd be exposing only a derived, flattened, OLAP-oriented database, you can still change your OLTP schema without breaking compatibility. You just have to maintain the mapping from OLTP->OLAP. You could even version the OLAP schemas so that you have a controlled way of getting customers onto newer structures. If I were taking that approach I'd consider what Stripe has said about how they build "mappings" from one API version to another. My email is in my profile if you'd like to talk more about this! SQL-as-an-API is a big interest of mine.
- btown 8y ago
  The Stripe article in question, for others' reference: https://stripe.com/blog/api-versioning https://stripe.com/blog/api-versioning . Your comments strike very close to home! In one project, to support Tableau analysis for business analytics, I did the opposite of "wrapping a JSON API around a relational database" - I used https://www.torodb.com/ https://www.torodb.com/ to wrap a derived Postgres database around our Mongo source of truth! (Laugh if you want - it got us to launch!) GraphQL, for all its power, can't support arbitrary aggregates the way SQL can. And when working with web-scale data, you want a server to be doing those calculations, not the client. I continue to work on systems with constantly-reprocessing data pipelines feeding the primary records that users interact with; per the above, they can feed derived databases that support SQL queries as well, whether or not we give that full flexibility to the API user. What I should write a blog post about is how we think of optimistic updates; just like in a rich client-side app where you want to optimistically update client-side data with how you might expect it to appear after receiving confirmation of your transaction, you also want to optimistically update derived-database data with how you might expect it to appear after it flows fully through an asynchronous data pipeline that overwrites that derived database. It's optimistic-updating all the way down, with the deepest layer (a more-or-less immutable event stream, incorporating things pulled in from the outside world) operating as the source of truth. The trick with a system like this is to build the right abstractions (and they're very domain-specific) to make it a joy to program in - more Django than PySpark - but it's definitely possible.
ako 8y ago
Never have query APIs on your operational databases. Either use a read-only slave, or another database filled through an ETL process. This allows you to refactor your main database, without impacting your API. Also you avoid the performance impact of the APIs on your main database. As an alternative for SQL you can consider oData, it's standardized REST compatible generic query language, similar to graphql, but supported in BI tooling like PowerBI, tableau and excel.
btown 8y ago
What about a stable materialized view (either materialized by SQL that is kept up to date with your source-of-truth tables as they're migrated... or, if necessary, by a custom real-time data pipeline to feed it) to service reads? And an event-based, insert-only schema to service writes? I'm always inspired by Martin Kleppmann's talk about the architecture his team built at LinkedIn (and subsequently spun off as a separate business): https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou... . An event-sourced system like that, one that can denormalize and transform data in any way that's needed for security and performance, could theoretically support a SQL-like API. It could even support exposing SQL-like triggers for real-time updates to untrusted clients (or, in LinkedIn's case, practically limitless internal teams). With chat being your domain, it's possible that event sourcing into a SQL database would be a great paradigm match for you. I've seen it work wonders.
z3t4 8y ago
You could create the views, but add a deprication period, log queries and automatically remind users querying the-to-be-deprecated views. A customer that runs SQL queries directly will probably tolerate having to rewrite their SQL queries from time to time.
barrkel 8y ago
We give people a series of dropdowns on columns on tabular data, and they can do things like IN and BETWEEN queries for date and numeric data, as well as literal equality for string data. The catch is that they can do this filtering on columns that are, behind the scenes, retrieved via joins. And the data set being searched may be many millions of rows. And it needs to come back interactively, i.e. no more than a second or two. And the schema is customer-defined. I implemented a syntax tree that is composed from the various columnar dropdown filters and get passed as a big AND to the API. The API essentially recieves a full syntax tree for a predicate, but each field referenced in the predicate might be only available through a series of joins, or via the main table - it requires some analysis. The database backing this is MySQL. MySQL has a fairly simple query optimizer, and it is reliably poor at being given complex predicates over queries with lots of joins. So, we split up the query: we try and tear off ANDed predicate parts that relate to the core table, push them into a derived table (i.e. nested query) along with the sorting predicate, if possible. We then join in just the tables required for evaluating the rest of the predicate, in the next level out. And finally, in the outermost query, we do all the joins required for the final page of results, joins that are necessary to retrieve all the columns that haven't been used by filters or sorts. With this level of analysis of the predicate being executed, we could do things like reject overly complex queries. As it is, our underlying data model is limited somewhat in the expected volume on a time period basis, and we have some defaults over the maximum time window that can be searched, so it's mostly under control. Occasionally new devs add in extra complex joins with derived tables that are a little bit too complex for interarctive querying, and staying on top of that is a challenge. It's needing a bunch more performance testing before releases, whereas we've been mostly able to get by with retrospective performance monitoring until now. But the team is bigger, not every query modification gets sufficiently expert eyes, and so we need to beef up our processes.
GorgeRonde 8y ago
> If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem? This is something I've been experimenting with lately. The problem with APIs is that we conceive them as opaque abstractions. This is reassuring when you need to tweak the background and avoid breaking the informal contract a public API has with its clients. But this also mean you need to start over if you want to implement joins or filters. This is both what REST and GraphQL does over SQL which already has this. I'd also argue that SQL is a more specified and more broadly accepted standard than REST or GraphQL. So here is how it goes: 1. First, I use a SQL DSL that is written in a data-based language rather than being string based as SQL is by default. Since I write in Clojure, Clojure being a Lisp, Code being Data, this DSL is just EDN, a clone of JSON. 2. I add an abstraction layer on top of this DSL to make SQL more malleable and more lispy with respect to certain things (in particular you can't nest arbitrary SQL like you can with Lisp code). 3. I add another abstraction layer on top of the INSERT/CREATE/UPDATE/DELETE verbs (or actually the DSL's equivalents) so that any record inside any table can have its own set of custom, optional attributes/columns and can be queried, filtered and joined like any other field. Any SQL type is supported and EDN/JSON data is supported as well. We're done with the DB, now let's turn to the API. 4. I add new verbs implemented by complex queries (point 2) that decompose into the enhanced INSERT/CREATE/UPDATE/DELETE verbs (point 3). Since the Clojure SQL DSL I'm extending supports churning of ingoing and outgoing DB data I also use that i.e. not everything is run in the database, and there can be multiple travels between the server and the db for the same verb -> This is how I blend database "code" (queries) with the service's business code. These verbs are used to make the API deterministic (it's a critical service, you don't want the same stuff happening twice by mistake). Example: CONJURE my_record BY {linked_client_id: 123} VALUES {field: "abc"}. Like ActiveRecord's find_or_create_by. This verb is used as both a replacement for CREATE and SELECT (but receives/returns only one record). This implies two things: - The client doesn't have to store any data from my service anymore, not even ids. He just needs to send to the service data that allows it to uniquely identify the operation in question (like one of its id). - Forcing the client into this deterministic mindset allows it to think less about the state of my service which is particularly useful against timeouts (when you're left not knowing if the operation has been carried out by the service) and simplifies the tying of the client's business logic with that of the service. This is also why we need to be able to accept arbitrary data from the client in a flexible way (point 3). 5. I just stupidly expose the DSL (remember it's JSON-like by essence since it's Clojure code): I read payloads sent by the client, make sure the code is restricted to the DSL (is not actual evil Clojure code), then eval it. 6. As for the client, I just find the best SQL ORM in the target language and implement an adapter for my service's exposed SQL. At this point I basically just have to convert the ORM's AST into the Clojure-like form required by the service. Querying the service just feels like querying a local database. 7. The service also needs to handle basic retries against timeouts. As for "pagination", I use PostgreSQL streaming abilities in synchrony with that of HTML + lazy sequences on the client side. This also allows very large datasets to be processed. As for joins between the client's db and my service, this can be done using a WHERE ID IN (...) clause, which might be supported by the ORM natively. This might also be the occasion to implement some caching to alleviate the network load. This is what I tried, and having achieved partially what's sketched on this roadmap, I think the overall approach I have here with respect to the concept of abstraction is to use it as a transparent structure throughout the implied levels (db -> server business logic -> API -> client -> client) in a bet whose goal is not to loose the expression power of the underlying RELATIONAL model since that's what is already used in the DB, it's how the API is designed and most likely it's what the client is using to model its own business. I also like how the concept of API just melts away, hidden behind the client's ORM.

scrollaway 8y ago

What you're describing is exactly what GraphQL sets out to solve. Most of the flexibility and declarativeness of SQL, but still sitting a layer above the raw data itself.

forkandwait 8y ago
So why not pure SQL, which everyone knows already?
- scrollaway 8y ago
  GraphQL has a lot of advantages over SQL, API-wise; namely it's a lot more natural to describe what you want to get back. But without getting into that, the point is that you usually don't want to give direct data access, with all that entails. You still want to be declaring the fields and properties of the data that users will be querying, and that usually sits a layer above your raw data. You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way. _______ Edit: Clarifying my stance on SQL views. I'm not dismissing it as a "bad tool", but there's good reasons it's not a popular one today. Writing SQL is not easy for everyone, and it's especially not easy for potential API consumers. The best APIs are those you grok. If you grok SQL, congratulations, but most devs don't, and APIs are usually designed for most people. Obviously, if SQL is the right tool for the job, use SQL. But don't start doing stuff like this for ideological reasons.
  - tathougies 8y ago
    I mean SQL views have been a thing for literally decades longer than GraphQL. If any technology should be accused of causing a 'lot of hassle to solve a solved problem', it's graphql, not SQL.
    - atombender 8y ago
      If your original data is flat and your app can consume it flat, no worries. But SQL is a worse fit for structured data, so you end up with an ORM on top, which is what GraphQL sort of is. Typically web apps are written with web technologies that work with structured objects, and you want something like {title: "Stapler", price: 1230, category: {id: "123", name: "Office supplies"}, mainPhoto: {url: "https://..." https://...", width: 600, height: 300}} back to feed directly in the UI. Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal.
      - just_myles 8y ago
aikah 8y ago
> Most of the flexibility and declarativeness of SQL, does graphQL support aggregate functions like count,sum,average and co?
- scrollaway 8y ago
  No, that's why I said most; and that's also what makes it more appropriate for APIs -- you don't want uncontrolled performance hitting you like this; if you wish to allow for aggregates, you expose them as properties.
  - derefr 8y ago
    > you don't want uncontrolled performance hitting you like this On RDBMSes with multitenancy support, you can simply apply resource constraints and quotas (in CPU time, CPU percentage, wall-clock time, memory, disk usage, etc.) to each user and to each connection. (You just have to ensure that each separate customer ends up hitting the DBMS as a separate DB user.) The entire point of the OLAP model is to allow clients to ask their own questions. Some of those questions may be too expensive to answer—modern DBMSes are smart enough to realize which queries those are, and allow the sysadmin to restrict users from doing them. But that restriction should always be a matter of scale, not of kind. Users should be able to do a very complex query over a small data-set, or a very simple query over a huge data-set, or a combination, as long as they aren't actually impacting the performance of the cluster for other users.
    - scrollaway 8y ago
      Sure. And if that's a model that is appropriate for your API users, go for it. But usually, it won't be appropriate. And by usually, I mean it will almost never be appropriate to adopt a model like this for your users. And I say this having been in the very situation where it was appropriate and we did expose it.
      - derefr 8y ago
        Alternately, what's wrong with just tracking the expense of the user's query and then passing the costs of it back to them? Like most "scale-free" DBaaSes (Dynamo, BigQuery, etc.) do. This is assuming your users are paying for your API, mind you. I have no idea why you'd want to provide an online relational data-access service for free. (The "free" option for OLAP is, and has always been, "we give you a .zip of CSV files, and you ETL them into your own DB to look at them." That's how things like government GIS data or Amazon's and Google's hosted "public datasets" work—you pay for online access, or you get free offline snapshot dumps.)
skrebbel 8y ago
GraphQL is just a query language. It has exactly the same problems in this respect as SQL - once a certain query from a customer that you never imagined they'd write works, it has to keep working, even if your underlying data model totally changes.
- scrollaway 8y ago
  This has a greatly reduced effect. What you have to keep updated is the accessible properties. As I said, it's similar to writing a SQL view and giving access to that. Difference being, GraphQL is a lot more appropriate especially for web REST endpoints

BerislavLopac 8y ago

> Note that this is solely in the context of a static data set. That makes this a relatively limited idea Not if you use the event sourcing approach.

o1lab 8y ago

If anyone is looking for apis for mysql : xmysql will be really helpful. https://github.com/o1lab/xmysql/ https://github.com/o1lab/xmysql/ Xmysql is just one command to generate REST APIs for any mysql database.

siscia 8y ago

Maybe I am a little biased, but once you define what SQL interface you want to provide to your users (so the schema of the tables) you have basically decoupled your internal data structure from the view of the client. At this point the only thing you need to do is to update the user facing database whenever necessary and keep it consistent with your view of the world. Yes, you have a new problem since you had to maintain the costumer view consistent with your view, but it may be a price worth paying. The way I would test this implementation is with RediSQL (https://RediSQL.com https://RediSQL.com) that I wrote (so I am definitely biased) but it may be worthed to test it.

pjungwir 8y ago

I've done it before. Some day I'll blog about it, but in the meantime here are a few places I've brought it up in HN comments: https://news.ycombinator.com/item?id=14464929 https://news.ycombinator.com/item?id=14464929 https://news.ycombinator.com/item?id=15335915 https://news.ycombinator.com/item?id=15335915 https://news.ycombinator.com/item?id=17820410 https://news.ycombinator.com/item?id=17820410 I agree with the concern about schema flexibility, although since you'd be exposing only a derived, flattened, OLAP-oriented database, you can still change your OLTP schema without breaking compatibility. You just have to maintain the mapping from OLTP->OLAP. You could even version the OLAP schemas so that you have a controlled way of getting customers onto newer structures. If I were taking that approach I'd consider what Stripe has said about how they build "mappings" from one API version to another. My email is in my profile if you'd like to talk more about this! SQL-as-an-API is a big interest of mine.

btown 8y ago
The Stripe article in question, for others' reference: https://stripe.com/blog/api-versioning https://stripe.com/blog/api-versioning . Your comments strike very close to home! In one project, to support Tableau analysis for business analytics, I did the opposite of "wrapping a JSON API around a relational database" - I used https://www.torodb.com/ https://www.torodb.com/ to wrap a derived Postgres database around our Mongo source of truth! (Laugh if you want - it got us to launch!) GraphQL, for all its power, can't support arbitrary aggregates the way SQL can. And when working with web-scale data, you want a server to be doing those calculations, not the client. I continue to work on systems with constantly-reprocessing data pipelines feeding the primary records that users interact with; per the above, they can feed derived databases that support SQL queries as well, whether or not we give that full flexibility to the API user. What I should write a blog post about is how we think of optimistic updates; just like in a rich client-side app where you want to optimistically update client-side data with how you might expect it to appear after receiving confirmation of your transaction, you also want to optimistically update derived-database data with how you might expect it to appear after it flows fully through an asynchronous data pipeline that overwrites that derived database. It's optimistic-updating all the way down, with the deepest layer (a more-or-less immutable event stream, incorporating things pulled in from the outside world) operating as the source of truth. The trick with a system like this is to build the right abstractions (and they're very domain-specific) to make it a joy to program in - more Django than PySpark - but it's definitely possible.

ako 8y ago

Never have query APIs on your operational databases. Either use a read-only slave, or another database filled through an ETL process. This allows you to refactor your main database, without impacting your API. Also you avoid the performance impact of the APIs on your main database. As an alternative for SQL you can consider oData, it's standardized REST compatible generic query language, similar to graphql, but supported in BI tooling like PowerBI, tableau and excel.

btown 8y ago

What about a stable materialized view (either materialized by SQL that is kept up to date with your source-of-truth tables as they're migrated... or, if necessary, by a custom real-time data pipeline to feed it) to service reads? And an event-based, insert-only schema to service writes? I'm always inspired by Martin Kleppmann's talk about the architecture his team built at LinkedIn (and subsequently spun off as a separate business): https://www.confluent.io/blog/turning-the-database-inside-out-with-apache-samza/ https://www.confluent.io/blog/turning-the-database-inside-ou... . An event-sourced system like that, one that can denormalize and transform data in any way that's needed for security and performance, could theoretically support a SQL-like API. It could even support exposing SQL-like triggers for real-time updates to untrusted clients (or, in LinkedIn's case, practically limitless internal teams). With chat being your domain, it's possible that event sourcing into a SQL database would be a great paradigm match for you. I've seen it work wonders.

z3t4 8y ago

You could create the views, but add a deprication period, log queries and automatically remind users querying the-to-be-deprecated views. A customer that runs SQL queries directly will probably tolerate having to rewrite their SQL queries from time to time.

barrkel 8y ago

We give people a series of dropdowns on columns on tabular data, and they can do things like IN and BETWEEN queries for date and numeric data, as well as literal equality for string data. The catch is that they can do this filtering on columns that are, behind the scenes, retrieved via joins. And the data set being searched may be many millions of rows. And it needs to come back interactively, i.e. no more than a second or two. And the schema is customer-defined. I implemented a syntax tree that is composed from the various columnar dropdown filters and get passed as a big AND to the API. The API essentially recieves a full syntax tree for a predicate, but each field referenced in the predicate might be only available through a series of joins, or via the main table - it requires some analysis. The database backing this is MySQL. MySQL has a fairly simple query optimizer, and it is reliably poor at being given complex predicates over queries with lots of joins. So, we split up the query: we try and tear off ANDed predicate parts that relate to the core table, push them into a derived table (i.e. nested query) along with the sorting predicate, if possible. We then join in just the tables required for evaluating the rest of the predicate, in the next level out. And finally, in the outermost query, we do all the joins required for the final page of results, joins that are necessary to retrieve all the columns that haven't been used by filters or sorts. With this level of analysis of the predicate being executed, we could do things like reject overly complex queries. As it is, our underlying data model is limited somewhat in the expected volume on a time period basis, and we have some defaults over the maximum time window that can be searched, so it's mostly under control. Occasionally new devs add in extra complex joins with derived tables that are a little bit too complex for interarctive querying, and staying on top of that is a challenge. It's needing a bunch more performance testing before releases, whereas we've been mostly able to get by with retrospective performance monitoring until now. But the team is bigger, not every query modification gets sufficiently expert eyes, and so we need to beef up our processes.

GorgeRonde 8y ago

> If anyone else has experience with exposing queryable (SQL or otherwise) dynamic APIs directly to customers, I'd love to hear your insights. How did you tackle this problem? This is something I've been experimenting with lately. The problem with APIs is that we conceive them as opaque abstractions. This is reassuring when you need to tweak the background and avoid breaking the informal contract a public API has with its clients. But this also mean you need to start over if you want to implement joins or filters. This is both what REST and GraphQL does over SQL which already has this. I'd also argue that SQL is a more specified and more broadly accepted standard than REST or GraphQL. So here is how it goes: 1. First, I use a SQL DSL that is written in a data-based language rather than being string based as SQL is by default. Since I write in Clojure, Clojure being a Lisp, Code being Data, this DSL is just EDN, a clone of JSON. 2. I add an abstraction layer on top of this DSL to make SQL more malleable and more lispy with respect to certain things (in particular you can't nest arbitrary SQL like you can with Lisp code). 3. I add another abstraction layer on top of the INSERT/CREATE/UPDATE/DELETE verbs (or actually the DSL's equivalents) so that any record inside any table can have its own set of custom, optional attributes/columns and can be queried, filtered and joined like any other field. Any SQL type is supported and EDN/JSON data is supported as well. We're done with the DB, now let's turn to the API. 4. I add new verbs implemented by complex queries (point 2) that decompose into the enhanced INSERT/CREATE/UPDATE/DELETE verbs (point 3). Since the Clojure SQL DSL I'm extending supports churning of ingoing and outgoing DB data I also use that i.e. not everything is run in the database, and there can be multiple travels between the server and the db for the same verb -> This is how I blend database "code" (queries) with the service's business code. These verbs are used to make the API deterministic (it's a critical service, you don't want the same stuff happening twice by mistake). Example: CONJURE my_record BY {linked_client_id: 123} VALUES {field: "abc"}. Like ActiveRecord's find_or_create_by. This verb is used as both a replacement for CREATE and SELECT (but receives/returns only one record). This implies two things: - The client doesn't have to store any data from my service anymore, not even ids. He just needs to send to the service data that allows it to uniquely identify the operation in question (like one of its id). - Forcing the client into this deterministic mindset allows it to think less about the state of my service which is particularly useful against timeouts (when you're left not knowing if the operation has been carried out by the service) and simplifies the tying of the client's business logic with that of the service. This is also why we need to be able to accept arbitrary data from the client in a flexible way (point 3). 5. I just stupidly expose the DSL (remember it's JSON-like by essence since it's Clojure code): I read payloads sent by the client, make sure the code is restricted to the DSL (is not actual evil Clojure code), then eval it. 6. As for the client, I just find the best SQL ORM in the target language and implement an adapter for my service's exposed SQL. At this point I basically just have to convert the ORM's AST into the Clojure-like form required by the service. Querying the service just feels like querying a local database. 7. The service also needs to handle basic retries against timeouts. As for "pagination", I use PostgreSQL streaming abilities in synchrony with that of HTML + lazy sequences on the client side. This also allows very large datasets to be processed. As for joins between the client's db and my service, this can be done using a WHERE ID IN (...) clause, which might be supported by the ORM natively. This might also be the occasion to implement some caching to alleviate the network load. This is what I tried, and having achieved partially what's sketched on this roadmap, I think the overall approach I have here with respect to the concept of abstraction is to use it as a transparent structure throughout the implied levels (db -> server business logic -> API -> client -> client) in a bet whose goal is not to loose the expression power of the underlying RELATIONAL model since that's what is already used in the DB, it's how the API is designed and most likely it's what the client is using to model its own business. I also like how the concept of API just melts away, hidden behind the client's ORM.