12 ms·

SQL as API

64 points by valenterry 3y ago

Nevin1901 3y ago
I implemented something like this for a log platform I was working on. The user would write queries in Apache lucene, and they would be converted into json, and then a specific sql query. It worked nice, but apart from querying logs, I don’t see a use case where one would do this rather than just creating api endpoints.
a-saleh 3y ago
I half expected this to promote Postgrest or one of it's alternatives and was surprised it didn't do that by the end of the post. I used it for this exact usecase, we had some data in postgress and front-end had limited api, and readonly postgrest was simpler than adding the api I needed to the frontend.
p4bl0 3y ago
In the end, this is really just about using a well-known syntax to express queries: since you end up parsing it, validating it, and building the actual query anyway, the fact that the query is initially written in SQL or anything else doesn't really matter, except that developers know SQL already (which is a good point).
- valenterry 3y ago
  Yes, but it's a little bit more: SQL is a fully fledged language that covers an enormous range of potential tasks and the syntax works mostly seemlessly to enable that. This means, extending the API by allowing more and more parts of SQL is easy and still backwards compatible. It's much harder to come up with a custom language / structure from the get-go that doesn't cause backwards-compatibility issues down the road. I guess I should have emphasized this point in the article a bit more, thank you for the comment.
  - randomdata 3y ago
    The trouble with SQL is that it is designed around the idea of being able to execute many queries without any overhead. No problem when you are within the confines of local memory. Manageable when you are in a well connected datacenter. But you will quickly find yourself in a world of hurt when you have clients on flaky, high latency mobile connections. Okay, so now you find some way to bundle many queries, with allowances for connecting the result of one query to another, into a single request with all the results in a single response. Congratulations, you just recreated GraphQL.
    - valenterry 3y ago
      I can't follow. I'm very familiar with both SQL and GraphQL. But SQL is strictly more powerful. If you want, you can extend the API to let the caller use with-statements, which allows to aggregate all necessary data within the same SQL query and being returned as a single response. No disadvantage to GraphQL here. I'm not saying that I suggest to do that, but for the specific point that you mention, I don't see any theoretical advantage of GraphQL in terms of performance.
      - deleted 3y ago
        [deleted]
      - randomdata 3y ago
        > But SQL is strictly more powerful. I'm not sure I understand. GraphQL doesn't need to be, nor is it meant to be, powerful. Its only purpose in life is to roll up multiple actions into a single response made from a single request. It was originally designed for those actions to be REST/RPC endpoints, but if you have an SQL API endpoint the same applies. SQL as an API isn't some novel thing. Your DBMS already does exactly that! We've been using SQL as an API for 50-some-odd years and will probably still be using it for APIs in 50 more. But, there is good reason why we have added layers on top in the datacenter. Don't expect SQL to be a suitable API for the entire world to use. > which allows to aggregate all necessary data within the same SQL query and being returned as a single response. If you push SQL to really pained lengths it can be done, but with a whole lot of added overhead elsewhere. There is no free lunch. Best to leave SQL to the job it was designed for; the job it is good at. It's okay to let it have help.
- JUNGLEISMASSIVE 3y ago
  [dead]
mg 3y ago
But faster than you can imagine, the next requirement comes in: OR filters! bicycles that are (made of steel AND weigh from 10 to 20kg) OR (made of carbon AND weigh from 5 to 10kg) Hmm.. I don't know if this is a good example. I have been in the product search space for almost 10 years now and that feature request never came up. And I cataloged thousands of feature requests users had about product search interfaces. In the rare case users do something like this, they usually use two tabs. One, where they compare steel bikes and one where they compare carbon bikes. It makes sense, when you think about the frontend implications. Users are very used to checkboxes and range sliders. The user would check "steel" and set the range slider to "10-20". Now how do they expect to duplicate the sliders and the checkboxes to construct the alternative configuration? They open the page in a new tab and use the alternative settings in that tab.
- valenterry 3y ago
  Yeah, maybe the example isn't that great, I admit so much. :-) However, it's not only about the users as in the visitors of the shop. It can also be a feature such as "show similar bicycles" that will require more complicated queries. And while it's of course possible to maybe run multiple queries and combine the results by code, it can be nice to just do it in one query that is easy to understand and debug/optimize because it's pretty much just SQL.
- sausagefeet 3y ago
  Maybe it's how you approach products or the space you've been in, but the ability to do "OR" has come up quite frequently for me, and on the opposite side of things, I often am very frustrated when products with search do not let me do OR.
  - mg 3y ago
    Can you link to a product search where OR is supported?
    - sausagefeet 3y ago
      Pretty much any log search interface, I use Papertrail. Google search. For the feature I am currently working on, it's an audit trail for the operations we run and OR is required. I wish the various searches on GitHub supported "or". Additionally, tons of products do support "OR". Take the Amazon search. It has sections that are "OR" inside of them and "AND" across the sections.
xd 3y ago
I've a similar way of interacting with the API of my product but instead keep everything prefix notation (lispy) so for example; "and": [ { "property": "material", "operator": "equals", "value": "carbon" }, Would be; ["and", ["=", "material", "carbon"], ...]
- turboponyy 3y ago
  If you're reinventing a language, it's probably not a bad idea to reinvent the simplest one!
- cyptus 3y ago
  Is there any standard for describing filters in json like this, or any well known library that is also able to parse this back to expressions?
  - egeozcan 3y ago
    In my humble opinion, it would benefit all better if we standardized something like jql (jira query language, so the user layer) first. From experience, many who find sql too complex have no problem with writing complicated jql queries. it's both easy to use and a great first step to writing actual SQL. I wish it was a standard like json.
  - gregsadetsky 3y ago
    The first thing that came to my mind is the mapbox espression language/spec, which uses a very similar format: https://docs.mapbox.com/style-spec/reference/expressions/ https://docs.mapbox.com/style-spec/reference/expressions/
  - xd 3y ago
    The way the JSON filters work would be directly coupled to the JSON fix notation and SQL infix notation so it's directly coupled to SQL and doesn't really require a standard, imho.
  - pgwhalen 3y ago
    Check out https://substrait.io/ https://substrait.io/
Kwpolska 3y ago
This is asking users to generate SQL code client-side, parses the SQL server-side to make sure it's safe, and then generates new SQL for some reason. This seems needlessly complex, requiring more logic on the client side, and putting a lot of trust in the server-side validator. The examples also use non-parametrised SQL, which means the client needs to know how to correctly escape values, and the database might fail to optimise queries. I see no benefits and many disadvantages compared to a simple JSON structure, even if it's custom: it's trivial to generate and parse, it can be used with an ORM, and the server will probably use parametrised SQL, because that's easier than the alternative.
- sausagefeet 3y ago
  I don't think JSON solves the problem, especially if you want users to be able to input complex queries, JSON will be a far worse experience. In his example, he does say he is processing the input language and generating SQL via a library. I think that calling the input language SQL, in this case, is probably a red herring. It's a very simple language with a strict set of binary operators that happens to look like SQL. Like I said in another post, I do a similar thing for the product we make, but our input language is not SQL, but equally as expressive as the subset of SQL this blog post allows. I don't see an issue in failing to optimize queries. The database does query planning on the fly. You do have to be a bit careful that you don't generate queries that can DoS your DB, but all solvable and not drastically different than a developer adding a new query to the product. What we do does create a prepare statement on the fly. We process the input query and generate SQL and collect each value the user as input into arrays for the types the correspond to and then index those arrays. So, for example, the query "pr:123 and (user:foo or user:bar)" would turn to SQL like: "pull_request = ($1)[1] and (user = ($2)[1] or user = ($2)[2])" (this allows us to statically type our queries). In short, if your requirements that users should be able to generate complex queries, I think this is significantly better than JSON. And you can process an input language into JSON and the problem is effectively the same from that point.
  - Kwpolska 3y ago
    For many applications, such as the bicycle shop in the examples, having users type in SQL or custom-language queries would be too difficult, considering most of the audience is not tech-savvy. Your user-friendly frontend UI would be happier to generate JSON instead of the custom textual format. Manually typing queries works for GitHub, but not much beyond it. Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively.
samwillis 3y ago
With SQLite in the browser via WASM, and the sync technology that's available for that (ElectricSQL (I work on this), CR-SQLite, Sqlsync), webapps are increasingly using SQL in the UI layer to a local DB. It's only one step further to then have a multi-modal app that uses SQL to query the server database for non-synced data. I'm expecting raw SQL to replace the API for a whole class of apps. Throw in an event sourcing architecture for server side processing and you have a single SQL based API for all operations.
dventimi 3y ago
> using SQL has been despised by people for the longest time, probably for good reasons at the time. Don't be too quick to assign rationality where its absence will do. If SQL has been despised it's quite possible the reasons have never been any good.
dventimi 3y ago
> We surely don't want to have the webshop-frontend send database queries to the backend to get the results. We don't? Why not?
- onion2k 3y ago
  You could get queries that don't use indexes and have to perform table scans. That's fine when your data is small, but it's a performance bottleneck waiting to happen.
  - dventimi 3y ago
    The statement timeout will put an end to that.
- contrast 3y ago
  Some reasons are given in the very first paragraph.
  - dventimi 3y ago
    You mean the very first paragraph in which the author invites us to reevaluate those reasons? You mean those reasons which include the following? - Exposing an API that accepts SQL is crazy. - It's a terrible idea. - Especially if the API is exposed on the internet. That isn't reasoning. We don't encounter actual reasoning until: - Doing that is insecure - itwill lead to SQL injection attacks - it is a nightmare to maintain - it will lock the backend implementation into a specific technology Even these are highly debatable, so I ask again, why not just expose a SQL API, even over the Internet?
coolgoose 3y ago
Or use graphql? :)
- JUNGLEISMASSIVE 3y ago
  [dead]
- mirekrusin 3y ago
  GraphQL is terribe on query part ie. it doesn't support unions or recursion (needed for OR/AND ops etc). It's kind of weird designers of GraphQL spent so much effort on cherry picking results but left query part static. It should be called PickResultLang (PickRL) instead.
  - JUNGLEISMASSIVE 3y ago
    [dead]
reliablereason 3y ago
Sanitize... Just create a sql user with the correct access rights. Don't reinvent the wheel.
- bvrmn 3y ago
  Access rights don't help with SELECT resource abuse. It's the reason 2-tier architecture is long dead.
  - dventimi 3y ago
    But row access policies do
- sausagefeet 3y ago
  Do you really think this is a good option? Even if you do properly enforce row-level security (Postgresql supports this), you're still exposing a lot of power to the user. They could write some pretty rough queries that could make guaranteeing QoS difficult or impossible. Row-level security is not easy to get right, as well. Maybe there is a library that can help you set this up.
  - reliablereason 3y ago
    If you need row level security for some tables you can also solve that with views that are made with the appropriate rows. QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. For small applications with a hundred to a few hundred users using the databases own user management is quite handy. You can be very sure that you don't leek information and you can be quite certain that potential bugs in your application code won't lead to many of the common security vulnerability's that exist.
    - sausagefeet 3y ago
      > QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. It seems to me that there is a large gap between "inject SQL from the user directly into the database" and "provide a limited query language that can be translated to SQL". There are plenty of situations where the latter is totally fine. Is that reinventing the wheel? I don't think so.
nextaccountic 3y ago
Doing this correctly basically reinvents half of Postgrest. So just use postgrest.
- sausagefeet 3y ago
  If this reinvents half of postgrest then postgrest is a very small piece of software (I don't think it is). I think the author has restricted themselves to such a small portion of SQL, it's probably not really worth comparing it to that, it's really just a small bespoke language transformer to SQL, and this is not so hard to implement. But, even if they did reinvent half of postgrest, there are plenty of reasons this might be the right choice given more contextual information, tech decisions aren't made in a vacuum, it just might not be possible to use postgrest.
JUNGLEISMASSIVE 3y ago
[dead]
sausagefeet 3y ago
You can do what the author is suggesting without the "SQL" part. In reality, they aren't exposing SQL but, at best, a subset, and most likely really a "language that looks roughly like SQL". This article is timely as I am implementing a similar thing for our product. I already knew I didn't want to go into it doing the original solution the author presents. Our product already has a little query language called tag queries[0], so I already have a parser for it. Now I have two consumers of the AST: one that does what our docs says, but now we use it for various APIs that need query ability. It's pretty simple to write a transformer from the bespoke language to SQL. In the normal usage we have special tag prefixes with meaning, and now each query entry point has the same query language and you just need to know what the tag prefixes mean for that context. I haven't done it yet, but next step is to add a complexity limit, that way people just can't send us queries that will DoS the database. [0] https://terrateam.io/docs/features/tags/#tag-query https://terrateam.io/docs/features/tags/#tag-query
vira28 3y ago
Interesting approach. We have been seeing this trend (or pendulum swing) of pushing SQL and simplicity. I am not saying this solution is simple (will leave that for discussion). Tangential, if this kinda stuff is interesting, folks might be interested in what Omnigres (disclaimer: my employer) is doing with the python integration in Postgres https://docs.omnigres.org/omni_python/intro/ https://docs.omnigres.org/omni_python/intro/
Ericson2314 3y ago
The fundamental problem with SQL is ambient authority - it's insecure (update anything, anywhere! just need to get by check constraints) - it's slow (table scans, when I meant to use index) - it's bad for horizontal scaling (things like sharding fundamentally relies on shrinking the domain of access patterns). Solve that by introducing some sort of capability system into the query language (think foreign keys, forward or backward), and we can make major strides on all fronts.
- PartiallyTyped 3y ago
  > - it's insecure (update anything, anywhere! just need to get by check constraints) It's entirely possible to add permissions and access control on any modern dbms. > - it's slow (table scans, when I meant to use index) Maybe you are using an OLTP when you meant to use an OLAP dbms? > - it's bad for horizontal scaling (things like sharding fundamentally relies on shrinking the domain of access patterns). See above. SQL is more adequate as a "language" or "standard" because that's what it is. What matters is that the underlying dbms that offers this functionality is appropriate.
  - PartiallyTyped 3y ago
    Sorry, grammar correction, meant to say: SQL is more than adequate as a "language" or a "standard".
  - Ericson2314 3y ago
    > It's entirely possible to add permissions and access control on any modern dbms. My understanding is this is all mandatory access control which is cumbersome as usual. > Maybe you are using an OLTP when you meant to use an OLAP dbms? "It's slow" was too pithy. When it's fast it great, but it's too easy to write things that are slow my mistake. I want things to be rejected if it they don't use an index in many cases, not fall back on a slow table scan. The most key part is that I posit that the "when are table scans allowed?" policy is very similar to the security policy.
    - dventimi 3y ago
9dev 3y ago
I once asked the same question on Security Stack Exchange. It has some interesting responses, if you’re curious what security people say. Edit: totally forgot to add the actual link. Here goes: https://security.stackexchange.com/questions/229954/why-cant-i-just-let-customers-connect-directly-to-my-database https://security.stackexchange.com/questions/229954/why-cant...
- valenterry 3y ago
  Can you find the link for me?
  - dventimi 3y ago
    Unsurprisingly, the answers to that security question including the accepted answer, are full of mistakes. This is such a sad state of affairs.
    - 9dev 3y ago
      I accepted that particular answer because it was balanced and comprehensive; I still think there’s a way to do this right that provides lots of benefits, namely something similar to what TFA is describing, that is: a wrapper layer that speaks SQL and applies ACLs and other limiters before passing the query to the database, potentially even translating between a virtual, exposed schema, and the actual database. The key advantage here being the ability to package this into a library, instead of everyone and their mother rolling their own.
      - dventimi 3y ago
        I think that's funny because personally I DIDN'T find that answer to be comprehensive or balanced. By my lights, it seems to go out of its way to ignore the affordances modern databases offer to protect against abuse. I found the following paragraph from the answer to be especially immeasured. "One of the most common vulnerabilities on web applications is SQL Injection, and you are giving a SQL console to your users. You are giving them a wide variety of guns, lots of bullets, and your foot, your hand, your head... And some of them don't like you." No, you are not giving your users a wide variety guns and bullets and hands and heads. You DO have some measure of control in the amount of power available to users. I don't consider it comprehensive or balanced not to address that fact in the answer.
bvrmn 3y ago
So instead of using direct AST (JSON) author proposes to make half baked SQL parser which is way harder to extend.
CSMastermind 3y ago
Search technologies seem to solve the problem the author is talking about out of the box?
nickray 3y ago
Google's proposal for filtering: https://google.aip.dev/160 https://google.aip.dev/160 Specifically, https://google.aip.dev/assets/misc/ebnf-filtering.txt https://google.aip.dev/assets/misc/ebnf-filtering.txt which is a modification of their common expression language https://github.com/google/cel-spec https://github.com/google/cel-spec
vbernat 3y ago
I am using a similar approach to filter network flows, but the language is just SQL-like (notably because users may not be familiar with all the quirks of SQL) and gets translated to ClickHouse SQL. The language is exposed to the end user, so there is completion and syntax highlighting. Since the result is open source, you get a complete example (notably to evaluate how complex this is). See https://vincent.bernat.ch/en/blog/2023-sql-like-language-filter https://vincent.bernat.ch/en/blog/2023-sql-like-language-fil....
nbittich 3y ago
this is why we go full circle all the time. 1) devs expose plain sql query to the users 2) devs realize it's a bad idea 3) 10 years later, a dev thinks maybe it's not that bad 4) full circle --- 1) devs use the server to generate html response to the users 2) devs think maybe it's better to do everything on the frontend side instead 3) 10 years and 250k frameworks later, devs think maybe it should be done on the server side instead
- dventimi 3y ago
  I'm not persuaded any of this is true.
techn00 3y ago
You're basically creating an expression language: https://github.com/expr-lang/expr https://github.com/expr-lang/expr
sophiabits 3y ago
Instead of inventing your own DSL for filtering, there are also some well-specified standards. ODATA [1] is probably the main one, and it’s adopted by e.g. Microsoft’s Azure APIs. JSON:API [2] is another, although I’ve never seen it used anywhere personally. They’re both pretty heavy specifications so I’ve never bothered implementing anything that 100% follows them. But I have picked bits and pieces from time to time—in particular ODATA’s idea for filtering is nice and comprehensive imo, and there are a number of libraries in various languages which handle parsing its filter strings into structured data. [1] https://www.odata.org/ https://www.odata.org/ [2] https://jsonapi.org/ https://jsonapi.org/
- Exoristos 3y ago
  I use JSON:API, a little simplified. It works great.
  - JUNGLEISMASSIVE 3y ago
    [dead]
oefrha 3y ago
I hate these sort-of-well-known-language-but-not-really DSLs. Since they’re sort of the well known language, documentation is usually sparse, you never know how to deal with edge cases without trial and error. For instance, in the given example, how do you escape SQL keywords? How do you escape quotes in string literals? Even SQL dialects don’t necessarily agree on what’s allowed, never mind this DSL with a half-assed parser. Not to mention this practically invites dumb string interpolation in SQL preparation, undoing decades of work to rid the world of this terrible habit. (Edit: This problem is solvable.) Don’t try to be clever, use a structured format (JSON, protobuf, etc.) with a well documented schema. Note that I’m only talking about APIs. It’s an entirely different matter if your product allows users to type in and send complex queries interactively.
- MissingNo_ 3y ago
  Aye, and if you need a query language inside your API it's probably best to use one of the existing languages specifically made for this use case, like GraphQL
  - sausagefeet 3y ago
    GraphQL does not solve the problem if the query language is user-facing. Additionally, GraphQL a pretty huge tech stack injection. Something like this is much simpler. So, it depends on the context of what you're making and why.
- ahoka 3y ago
  SQL is structured with a well documented schema.
  - sausagefeet 3y ago
    I believe they mean that the language described in here is not really SQL. It looks like SQL. But the question is: what are the differences? Is it really like SQL?
  - oefrha 3y ago
zX41ZdbW 3y ago
You can allow users to write direct SQL queries or use SQL as API if the database server does all of the following: 1. Access control. Table and column-level access control. Row-level restrictions by expressions. Out-of-band table filters that the user cannot override in the query. 2. Query complexity restrictions. Allow queries only using the index. Restrict the maximum number of records to scan. Limit the max query runtime or maximum memory consumption. Out-of-band limiting on the result size. 3. Rate limiting. Limit the number of rows or bytes scanned over a period of time for a user, for an IP address or IP subnet. 4. Interfaces and formats. CORS headers. HTTP compression. TLS. Proxy protocols. JSON with metadata, Protobuf, etc. TLDR. It works with ClickHouse; it is doubtful with other DBMS.
- valenterry 3y ago
  I'm afraid this might not be sufficient. For example, you might have a table "users" but while "admins" can see all users, "moderators" might only be allowed to see the users of their moderated areas. As soon as this kind of flexibility is needed, solutions like Postgrest stop working - they are just not made for that level of customization.
  - ruslan_talpa 3y ago
    You are wrong on both statements :) It does not stop working and this “customization” is easy.
stana 3y ago
That Scala SQL syntax parsing/validation is neat. Anything equivalent in Python?
- valenterry 3y ago
  Author here, unfortunately not (to my knowledge). I've used a lot of Python and Typescript and have never found a solution that made me happy when it comes to pattern matching / destructuring. Sorry :'(
DrDroop 3y ago
I think that the url search parameter convention as is used by PostgREST is a better alternative then using SQL for this. It is more ergonomic, easier to parse and allows for some additional functionality you would usually need GraphQL for. It has some smaller downsides but those are in part due to the translation mismatch between URLs and SQL. Remember that the SQL spec runs in the thousands of pages and that the only reliable way to parse Postgres's version of SQL is by linking to actual Postgres implementation. I've been playing around with this idea of using this language as a colloquial standard for writing things like a DB poxy/cache or a "standard" interface for something like a ECS based OLAP-esque on columnar stores. Nothing to report at the time but I love to hear from people who have worked on a similar thing.
speedgoose 3y ago
I’m currently playing with PostgreSQL, foreign data wrappers, and pgrx rust extensions. My development experience has been surprisingly smooth and enjoyable. My main issue is that joins will be processed locally, so all the foreign data will be fetched before the join happens. But otherwise basic CRUD is easy. https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://github.com/pgcentralfoundation/pgrx https://github.com/pgcentralfoundation/pgrx https://github.com/supabase/wrappers https://github.com/supabase/wrappers
pdepoulpiquet 3y ago
Real life implementations: https://developers.google.com/google-ads/api/docs/query/overview https://developers.google.com/google-ads/api/docs/query/over... https://developer.intuit.com/app/developer/qbo/docs/learn/explore-the-quickbooks-online-api/data-queries https://developer.intuit.com/app/developer/qbo/docs/learn/ex... https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.... https://carto.com/developers/sql-api/ https://carto.com/developers/sql-api/ From an end user standpoint, I preferred working with such API than with the ones creating nested json filters structures to model the filtering logic. It also provides a convenient way to select fields in the response, better in my opinion than "field mask" alternative that you can find on other APIs.
- valenterry 3y ago
  Thanks! I might steal this list if I extend the article later. ;-)
aragonite 3y ago
This is a bit off-topic, but I wonder if anyone knows of a programming language/DSL that supports 'subsentential' logical operators. I was just thinking the other day how convenient it'd be to be able to write SQL queries like this: ... WHERE (either A or B or C) (either beginswith OR endswith) (either X or Y or Z) i.e. expressing the condition in a way closely mirroring the English syntax, instead of having to fully write out all 18 (=3x2x3) clauses. (Natural rules for short-circuiting would apply, so if 'A beginswith X' evaluates to true, no further conditions need be check) This isn't just limited to query languages. In many programming languages, it strikes me as odd that I can't write something like `if A is (either X or Z)`, which mirrors how I would naturally express the condition in English, but need to reformulate it as `if A is X or A is Z`, or (worse IMO from the readability standpoint) something like `if [X,Z].includes(A)`.
- dubbel 3y ago
  I don't know any language that offers this, I just wanted to say that I have also thought about this feature at times - but when reading your pseudocode I didn't initially get it, because of the OR, which already has its meaning - instead one could use "either", like "either(a, b, c).either(beginswith, endswith)...".
  - aragonite 3y ago
    Thanks! I just fixed the pseudocode. Indeed `either ... or` seems almost obligatory for clarity purposes in this type of context.
avinassh 3y ago
This post reminded me of Fly's shipping the DB idea [0]. It sounds crazy, but works beautifully for use cases. Instead of data, the caller gets the replicated SQLite itself and may do whatever it wishes to do. [0] Skip the API, Ship Your Database - https://fly.io/blog/skip-the-api/ https://fly.io/blog/skip-the-api/
- paulryanrogers 3y ago
  And clients will just have to deal with schema changes, or never make backward incompatible changes
  - dventimi 3y ago
    Not if the tables in the schema that's changing are hidden behind an abstraction layer of views and procedures, they won't.
thiht 3y ago
> Because we are essentially inventing our own query language here. And inventing a custom language should be pretty much the last resort, I hope we agree on that. No. I don’t agree at all. Creating a custom query language based on json or lisp or whatever is easy enough, flexible enough, prone to evolution, and helps building an abstraction between the front and the back. With this in mind, the day you decide to query an ElasticSearch instead of the RDBMS, the frontal API doesn’t need to change, there’s juste to update the translation layer to generate an ES query instead of SQL. Exposing SQL to the API is an awful idea.
sdsd 3y ago
When I was reading Beautiful Racket [0] I was still working as a data engineer, writing complex SQL queries all day. I actually sort of fell in love with SQL, and decided to write a web development framework in SQL. INSERT requests would create forms, SELECT would create a table. The idea was that you could create something simple like an BBS with only a few lines of code: CREATE TABLE posts (title TEXT, content TEXT) INSERT INTO posts (title, content) VALUES (request.title, request.content) SELECT title, content FROM posts It actually works! Here's the code on GH: https://github.com/seisvelas/Sea-Quill https://github.com/seisvelas/Sea-Quill Sadly, I stopped working on it after only 1 or 2 weekends because I switched professions to cybersecurity and had too much to learn - every weekend thereafter was CTFs or bug bounties. Oh well! Someday I'd like to write a more intuitive, "conventional" style language for Urbit, maybe called HoonScript or something. So my affection for Racket's language oriented programming will (maybe, one day) not be in vain!
- sdsd 3y ago
  [0] - https://beautifulracket.com/ https://beautifulracket.com/
beaugunderson 3y ago
In this vein I was surprised to learn that crt.sh (certificate transparency log) lets anyone connect to their postgres and write their own queries against it: psql --host=crt.sh --port=5432 --username=guest certwatch
- dventimi 3y ago
  So what? That role has read-only access probably to all the relations, and it has a statement_timeout of '2 min'. Evidently, that's acceptable to its maintainers and we're in no position to say that they're wrong.
  - beaugunderson 3y ago
    It is an example of using an actual SQL database as a public API that works very well (and which is quite rare!) so I felt it was worth sharing.
    - dventimi 3y ago
      Fair enough! Thank you for sharing!

64 points by valenterry 3y ago

Nevin1901 3y ago

I implemented something like this for a log platform I was working on. The user would write queries in Apache lucene, and they would be converted into json, and then a specific sql query. It worked nice, but apart from querying logs, I don’t see a use case where one would do this rather than just creating api endpoints.

a-saleh 3y ago

I half expected this to promote Postgrest or one of it's alternatives and was surprised it didn't do that by the end of the post. I used it for this exact usecase, we had some data in postgress and front-end had limited api, and readonly postgrest was simpler than adding the api I needed to the frontend.

p4bl0 3y ago

In the end, this is really just about using a well-known syntax to express queries: since you end up parsing it, validating it, and building the actual query anyway, the fact that the query is initially written in SQL or anything else doesn't really matter, except that developers know SQL already (which is a good point).

valenterry 3y ago
Yes, but it's a little bit more: SQL is a fully fledged language that covers an enormous range of potential tasks and the syntax works mostly seemlessly to enable that. This means, extending the API by allowing more and more parts of SQL is easy and still backwards compatible. It's much harder to come up with a custom language / structure from the get-go that doesn't cause backwards-compatibility issues down the road. I guess I should have emphasized this point in the article a bit more, thank you for the comment.
- randomdata 3y ago
  The trouble with SQL is that it is designed around the idea of being able to execute many queries without any overhead. No problem when you are within the confines of local memory. Manageable when you are in a well connected datacenter. But you will quickly find yourself in a world of hurt when you have clients on flaky, high latency mobile connections. Okay, so now you find some way to bundle many queries, with allowances for connecting the result of one query to another, into a single request with all the results in a single response. Congratulations, you just recreated GraphQL.
  - valenterry 3y ago
    I can't follow. I'm very familiar with both SQL and GraphQL. But SQL is strictly more powerful. If you want, you can extend the API to let the caller use with-statements, which allows to aggregate all necessary data within the same SQL query and being returned as a single response. No disadvantage to GraphQL here. I'm not saying that I suggest to do that, but for the specific point that you mention, I don't see any theoretical advantage of GraphQL in terms of performance.
    - deleted 3y ago
      [deleted]
    - randomdata 3y ago
      > But SQL is strictly more powerful. I'm not sure I understand. GraphQL doesn't need to be, nor is it meant to be, powerful. Its only purpose in life is to roll up multiple actions into a single response made from a single request. It was originally designed for those actions to be REST/RPC endpoints, but if you have an SQL API endpoint the same applies. SQL as an API isn't some novel thing. Your DBMS already does exactly that! We've been using SQL as an API for 50-some-odd years and will probably still be using it for APIs in 50 more. But, there is good reason why we have added layers on top in the datacenter. Don't expect SQL to be a suitable API for the entire world to use. > which allows to aggregate all necessary data within the same SQL query and being returned as a single response. If you push SQL to really pained lengths it can be done, but with a whole lot of added overhead elsewhere. There is no free lunch. Best to leave SQL to the job it was designed for; the job it is good at. It's okay to let it have help.
JUNGLEISMASSIVE 3y ago
[dead]

mg 3y ago

But faster than you can imagine, the next requirement comes in: OR filters! bicycles that are (made of steel AND weigh from 10 to 20kg) OR (made of carbon AND weigh from 5 to 10kg) Hmm.. I don't know if this is a good example. I have been in the product search space for almost 10 years now and that feature request never came up. And I cataloged thousands of feature requests users had about product search interfaces. In the rare case users do something like this, they usually use two tabs. One, where they compare steel bikes and one where they compare carbon bikes. It makes sense, when you think about the frontend implications. Users are very used to checkboxes and range sliders. The user would check "steel" and set the range slider to "10-20". Now how do they expect to duplicate the sliders and the checkboxes to construct the alternative configuration? They open the page in a new tab and use the alternative settings in that tab.

valenterry 3y ago
Yeah, maybe the example isn't that great, I admit so much. :-) However, it's not only about the users as in the visitors of the shop. It can also be a feature such as "show similar bicycles" that will require more complicated queries. And while it's of course possible to maybe run multiple queries and combine the results by code, it can be nice to just do it in one query that is easy to understand and debug/optimize because it's pretty much just SQL.
sausagefeet 3y ago
Maybe it's how you approach products or the space you've been in, but the ability to do "OR" has come up quite frequently for me, and on the opposite side of things, I often am very frustrated when products with search do not let me do OR.
- mg 3y ago
  Can you link to a product search where OR is supported?
  - sausagefeet 3y ago
    Pretty much any log search interface, I use Papertrail. Google search. For the feature I am currently working on, it's an audit trail for the operations we run and OR is required. I wish the various searches on GitHub supported "or". Additionally, tons of products do support "OR". Take the Amazon search. It has sections that are "OR" inside of them and "AND" across the sections.

xd 3y ago

I've a similar way of interacting with the API of my product but instead keep everything prefix notation (lispy) so for example; "and": [ { "property": "material", "operator": "equals", "value": "carbon" }, Would be; ["and", ["=", "material", "carbon"], ...]

turboponyy 3y ago
If you're reinventing a language, it's probably not a bad idea to reinvent the simplest one!
cyptus 3y ago
Is there any standard for describing filters in json like this, or any well known library that is also able to parse this back to expressions?
- egeozcan 3y ago
  In my humble opinion, it would benefit all better if we standardized something like jql (jira query language, so the user layer) first. From experience, many who find sql too complex have no problem with writing complicated jql queries. it's both easy to use and a great first step to writing actual SQL. I wish it was a standard like json.
- gregsadetsky 3y ago
  The first thing that came to my mind is the mapbox espression language/spec, which uses a very similar format: https://docs.mapbox.com/style-spec/reference/expressions/ https://docs.mapbox.com/style-spec/reference/expressions/
- xd 3y ago
  The way the JSON filters work would be directly coupled to the JSON fix notation and SQL infix notation so it's directly coupled to SQL and doesn't really require a standard, imho.
- pgwhalen 3y ago
  Check out https://substrait.io/ https://substrait.io/

Kwpolska 3y ago

This is asking users to generate SQL code client-side, parses the SQL server-side to make sure it's safe, and then generates new SQL for some reason. This seems needlessly complex, requiring more logic on the client side, and putting a lot of trust in the server-side validator. The examples also use non-parametrised SQL, which means the client needs to know how to correctly escape values, and the database might fail to optimise queries. I see no benefits and many disadvantages compared to a simple JSON structure, even if it's custom: it's trivial to generate and parse, it can be used with an ORM, and the server will probably use parametrised SQL, because that's easier than the alternative.

sausagefeet 3y ago
I don't think JSON solves the problem, especially if you want users to be able to input complex queries, JSON will be a far worse experience. In his example, he does say he is processing the input language and generating SQL via a library. I think that calling the input language SQL, in this case, is probably a red herring. It's a very simple language with a strict set of binary operators that happens to look like SQL. Like I said in another post, I do a similar thing for the product we make, but our input language is not SQL, but equally as expressive as the subset of SQL this blog post allows. I don't see an issue in failing to optimize queries. The database does query planning on the fly. You do have to be a bit careful that you don't generate queries that can DoS your DB, but all solvable and not drastically different than a developer adding a new query to the product. What we do does create a prepare statement on the fly. We process the input query and generate SQL and collect each value the user as input into arrays for the types the correspond to and then index those arrays. So, for example, the query "pr:123 and (user:foo or user:bar)" would turn to SQL like: "pull_request = ($1)[1] and (user = ($2)[1] or user = ($2)[2])" (this allows us to statically type our queries). In short, if your requirements that users should be able to generate complex queries, I think this is significantly better than JSON. And you can process an input language into JSON and the problem is effectively the same from that point.
- Kwpolska 3y ago
  For many applications, such as the bicycle shop in the examples, having users type in SQL or custom-language queries would be too difficult, considering most of the audience is not tech-savvy. Your user-friendly frontend UI would be happier to generate JSON instead of the custom textual format. Manually typing queries works for GitHub, but not much beyond it. Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively.

samwillis 3y ago

With SQLite in the browser via WASM, and the sync technology that's available for that (ElectricSQL (I work on this), CR-SQLite, Sqlsync), webapps are increasingly using SQL in the UI layer to a local DB. It's only one step further to then have a multi-modal app that uses SQL to query the server database for non-synced data. I'm expecting raw SQL to replace the API for a whole class of apps. Throw in an event sourcing architecture for server side processing and you have a single SQL based API for all operations.

dventimi 3y ago

> using SQL has been despised by people for the longest time, probably for good reasons at the time. Don't be too quick to assign rationality where its absence will do. If SQL has been despised it's quite possible the reasons have never been any good.

dventimi 3y ago

> We surely don't want to have the webshop-frontend send database queries to the backend to get the results. We don't? Why not?

onion2k 3y ago
You could get queries that don't use indexes and have to perform table scans. That's fine when your data is small, but it's a performance bottleneck waiting to happen.
- dventimi 3y ago
  The statement timeout will put an end to that.
contrast 3y ago
Some reasons are given in the very first paragraph.
- dventimi 3y ago
  You mean the very first paragraph in which the author invites us to reevaluate those reasons? You mean those reasons which include the following? - Exposing an API that accepts SQL is crazy. - It's a terrible idea. - Especially if the API is exposed on the internet. That isn't reasoning. We don't encounter actual reasoning until: - Doing that is insecure - itwill lead to SQL injection attacks - it is a nightmare to maintain - it will lock the backend implementation into a specific technology Even these are highly debatable, so I ask again, why not just expose a SQL API, even over the Internet?

coolgoose 3y ago

Or use graphql? :)

JUNGLEISMASSIVE 3y ago
[dead]
mirekrusin 3y ago
GraphQL is terribe on query part ie. it doesn't support unions or recursion (needed for OR/AND ops etc). It's kind of weird designers of GraphQL spent so much effort on cherry picking results but left query part static. It should be called PickResultLang (PickRL) instead.
- JUNGLEISMASSIVE 3y ago
  [dead]

reliablereason 3y ago

Sanitize... Just create a sql user with the correct access rights. Don't reinvent the wheel.

bvrmn 3y ago
Access rights don't help with SELECT resource abuse. It's the reason 2-tier architecture is long dead.
- dventimi 3y ago
  But row access policies do
sausagefeet 3y ago
Do you really think this is a good option? Even if you do properly enforce row-level security (Postgresql supports this), you're still exposing a lot of power to the user. They could write some pretty rough queries that could make guaranteeing QoS difficult or impossible. Row-level security is not easy to get right, as well. Maybe there is a library that can help you set this up.
- reliablereason 3y ago
  If you need row level security for some tables you can also solve that with views that are made with the appropriate rows. QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. For small applications with a hundred to a few hundred users using the databases own user management is quite handy. You can be very sure that you don't leek information and you can be quite certain that potential bugs in your application code won't lead to many of the common security vulnerability's that exist.
  - sausagefeet 3y ago
    > QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. It seems to me that there is a large gap between "inject SQL from the user directly into the database" and "provide a limited query language that can be translated to SQL". There are plenty of situations where the latter is totally fine. Is that reinventing the wheel? I don't think so.

nextaccountic 3y ago

Doing this correctly basically reinvents half of Postgrest. So just use postgrest.

sausagefeet 3y ago
If this reinvents half of postgrest then postgrest is a very small piece of software (I don't think it is). I think the author has restricted themselves to such a small portion of SQL, it's probably not really worth comparing it to that, it's really just a small bespoke language transformer to SQL, and this is not so hard to implement. But, even if they did reinvent half of postgrest, there are plenty of reasons this might be the right choice given more contextual information, tech decisions aren't made in a vacuum, it just might not be possible to use postgrest.

JUNGLEISMASSIVE 3y ago

[dead]

sausagefeet 3y ago

You can do what the author is suggesting without the "SQL" part. In reality, they aren't exposing SQL but, at best, a subset, and most likely really a "language that looks roughly like SQL". This article is timely as I am implementing a similar thing for our product. I already knew I didn't want to go into it doing the original solution the author presents. Our product already has a little query language called tag queries[0], so I already have a parser for it. Now I have two consumers of the AST: one that does what our docs says, but now we use it for various APIs that need query ability. It's pretty simple to write a transformer from the bespoke language to SQL. In the normal usage we have special tag prefixes with meaning, and now each query entry point has the same query language and you just need to know what the tag prefixes mean for that context. I haven't done it yet, but next step is to add a complexity limit, that way people just can't send us queries that will DoS the database. [0] https://terrateam.io/docs/features/tags/#tag-query https://terrateam.io/docs/features/tags/#tag-query

vira28 3y ago

Interesting approach. We have been seeing this trend (or pendulum swing) of pushing SQL and simplicity. I am not saying this solution is simple (will leave that for discussion). Tangential, if this kinda stuff is interesting, folks might be interested in what Omnigres (disclaimer: my employer) is doing with the python integration in Postgres https://docs.omnigres.org/omni_python/intro/ https://docs.omnigres.org/omni_python/intro/

Ericson2314 3y ago

The fundamental problem with SQL is ambient authority - it's insecure (update anything, anywhere! just need to get by check constraints) - it's slow (table scans, when I meant to use index) - it's bad for horizontal scaling (things like sharding fundamentally relies on shrinking the domain of access patterns). Solve that by introducing some sort of capability system into the query language (think foreign keys, forward or backward), and we can make major strides on all fronts.

PartiallyTyped 3y ago
> - it's insecure (update anything, anywhere! just need to get by check constraints) It's entirely possible to add permissions and access control on any modern dbms. > - it's slow (table scans, when I meant to use index) Maybe you are using an OLTP when you meant to use an OLAP dbms? > - it's bad for horizontal scaling (things like sharding fundamentally relies on shrinking the domain of access patterns). See above. SQL is more adequate as a "language" or "standard" because that's what it is. What matters is that the underlying dbms that offers this functionality is appropriate.
- PartiallyTyped 3y ago
  Sorry, grammar correction, meant to say: SQL is more than adequate as a "language" or a "standard".
- Ericson2314 3y ago
  > It's entirely possible to add permissions and access control on any modern dbms. My understanding is this is all mandatory access control which is cumbersome as usual. > Maybe you are using an OLTP when you meant to use an OLAP dbms? "It's slow" was too pithy. When it's fast it great, but it's too easy to write things that are slow my mistake. I want things to be rejected if it they don't use an index in many cases, not fall back on a slow table scan. The most key part is that I posit that the "when are table scans allowed?" policy is very similar to the security policy.
  - dventimi 3y ago

9dev 3y ago

I once asked the same question on Security Stack Exchange. It has some interesting responses, if you’re curious what security people say. Edit: totally forgot to add the actual link. Here goes: https://security.stackexchange.com/questions/229954/why-cant-i-just-let-customers-connect-directly-to-my-database https://security.stackexchange.com/questions/229954/why-cant...

valenterry 3y ago
Can you find the link for me?
- dventimi 3y ago
  Unsurprisingly, the answers to that security question including the accepted answer, are full of mistakes. This is such a sad state of affairs.
  - 9dev 3y ago
    I accepted that particular answer because it was balanced and comprehensive; I still think there’s a way to do this right that provides lots of benefits, namely something similar to what TFA is describing, that is: a wrapper layer that speaks SQL and applies ACLs and other limiters before passing the query to the database, potentially even translating between a virtual, exposed schema, and the actual database. The key advantage here being the ability to package this into a library, instead of everyone and their mother rolling their own.
    - dventimi 3y ago
      I think that's funny because personally I DIDN'T find that answer to be comprehensive or balanced. By my lights, it seems to go out of its way to ignore the affordances modern databases offer to protect against abuse. I found the following paragraph from the answer to be especially immeasured. "One of the most common vulnerabilities on web applications is SQL Injection, and you are giving a SQL console to your users. You are giving them a wide variety of guns, lots of bullets, and your foot, your hand, your head... And some of them don't like you." No, you are not giving your users a wide variety guns and bullets and hands and heads. You DO have some measure of control in the amount of power available to users. I don't consider it comprehensive or balanced not to address that fact in the answer.

bvrmn 3y ago

So instead of using direct AST (JSON) author proposes to make half baked SQL parser which is way harder to extend.

CSMastermind 3y ago

Search technologies seem to solve the problem the author is talking about out of the box?

nickray 3y ago

Google's proposal for filtering: https://google.aip.dev/160 https://google.aip.dev/160 Specifically, https://google.aip.dev/assets/misc/ebnf-filtering.txt https://google.aip.dev/assets/misc/ebnf-filtering.txt which is a modification of their common expression language https://github.com/google/cel-spec https://github.com/google/cel-spec

vbernat 3y ago

I am using a similar approach to filter network flows, but the language is just SQL-like (notably because users may not be familiar with all the quirks of SQL) and gets translated to ClickHouse SQL. The language is exposed to the end user, so there is completion and syntax highlighting. Since the result is open source, you get a complete example (notably to evaluate how complex this is). See https://vincent.bernat.ch/en/blog/2023-sql-like-language-filter https://vincent.bernat.ch/en/blog/2023-sql-like-language-fil....

nbittich 3y ago

this is why we go full circle all the time. 1) devs expose plain sql query to the users 2) devs realize it's a bad idea 3) 10 years later, a dev thinks maybe it's not that bad 4) full circle --- 1) devs use the server to generate html response to the users 2) devs think maybe it's better to do everything on the frontend side instead 3) 10 years and 250k frameworks later, devs think maybe it should be done on the server side instead

dventimi 3y ago
I'm not persuaded any of this is true.

techn00 3y ago

You're basically creating an expression language: https://github.com/expr-lang/expr https://github.com/expr-lang/expr

sophiabits 3y ago

Instead of inventing your own DSL for filtering, there are also some well-specified standards. ODATA [1] is probably the main one, and it’s adopted by e.g. Microsoft’s Azure APIs. JSON:API [2] is another, although I’ve never seen it used anywhere personally. They’re both pretty heavy specifications so I’ve never bothered implementing anything that 100% follows them. But I have picked bits and pieces from time to time—in particular ODATA’s idea for filtering is nice and comprehensive imo, and there are a number of libraries in various languages which handle parsing its filter strings into structured data. [1] https://www.odata.org/ https://www.odata.org/ [2] https://jsonapi.org/ https://jsonapi.org/

Exoristos 3y ago
I use JSON:API, a little simplified. It works great.
- JUNGLEISMASSIVE 3y ago
  [dead]

oefrha 3y ago

I hate these sort-of-well-known-language-but-not-really DSLs. Since they’re sort of the well known language, documentation is usually sparse, you never know how to deal with edge cases without trial and error. For instance, in the given example, how do you escape SQL keywords? How do you escape quotes in string literals? Even SQL dialects don’t necessarily agree on what’s allowed, never mind this DSL with a half-assed parser. Not to mention this practically invites dumb string interpolation in SQL preparation, undoing decades of work to rid the world of this terrible habit. (Edit: This problem is solvable.) Don’t try to be clever, use a structured format (JSON, protobuf, etc.) with a well documented schema. Note that I’m only talking about APIs. It’s an entirely different matter if your product allows users to type in and send complex queries interactively.

MissingNo_ 3y ago
Aye, and if you need a query language inside your API it's probably best to use one of the existing languages specifically made for this use case, like GraphQL
- sausagefeet 3y ago
  GraphQL does not solve the problem if the query language is user-facing. Additionally, GraphQL a pretty huge tech stack injection. Something like this is much simpler. So, it depends on the context of what you're making and why.
ahoka 3y ago
SQL is structured with a well documented schema.
- sausagefeet 3y ago
  I believe they mean that the language described in here is not really SQL. It looks like SQL. But the question is: what are the differences? Is it really like SQL?
- oefrha 3y ago

zX41ZdbW 3y ago

You can allow users to write direct SQL queries or use SQL as API if the database server does all of the following: 1. Access control. Table and column-level access control. Row-level restrictions by expressions. Out-of-band table filters that the user cannot override in the query. 2. Query complexity restrictions. Allow queries only using the index. Restrict the maximum number of records to scan. Limit the max query runtime or maximum memory consumption. Out-of-band limiting on the result size. 3. Rate limiting. Limit the number of rows or bytes scanned over a period of time for a user, for an IP address or IP subnet. 4. Interfaces and formats. CORS headers. HTTP compression. TLS. Proxy protocols. JSON with metadata, Protobuf, etc. TLDR. It works with ClickHouse; it is doubtful with other DBMS.

valenterry 3y ago
I'm afraid this might not be sufficient. For example, you might have a table "users" but while "admins" can see all users, "moderators" might only be allowed to see the users of their moderated areas. As soon as this kind of flexibility is needed, solutions like Postgrest stop working - they are just not made for that level of customization.
- ruslan_talpa 3y ago
  You are wrong on both statements :) It does not stop working and this “customization” is easy.

stana 3y ago

That Scala SQL syntax parsing/validation is neat. Anything equivalent in Python?

valenterry 3y ago
Author here, unfortunately not (to my knowledge). I've used a lot of Python and Typescript and have never found a solution that made me happy when it comes to pattern matching / destructuring. Sorry :'(

DrDroop 3y ago

I think that the url search parameter convention as is used by PostgREST is a better alternative then using SQL for this. It is more ergonomic, easier to parse and allows for some additional functionality you would usually need GraphQL for. It has some smaller downsides but those are in part due to the translation mismatch between URLs and SQL. Remember that the SQL spec runs in the thousands of pages and that the only reliable way to parse Postgres's version of SQL is by linking to actual Postgres implementation. I've been playing around with this idea of using this language as a colloquial standard for writing things like a DB poxy/cache or a "standard" interface for something like a ECS based OLAP-esque on columnar stores. Nothing to report at the time but I love to hear from people who have worked on a similar thing.

speedgoose 3y ago

I’m currently playing with PostgreSQL, foreign data wrappers, and pgrx rust extensions. My development experience has been surprisingly smooth and enjoyable. My main issue is that joins will be processed locally, so all the foreign data will be fetched before the join happens. But otherwise basic CRUD is easy. https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://github.com/pgcentralfoundation/pgrx https://github.com/pgcentralfoundation/pgrx https://github.com/supabase/wrappers https://github.com/supabase/wrappers

pdepoulpiquet 3y ago

Real life implementations: https://developers.google.com/google-ads/api/docs/query/overview https://developers.google.com/google-ads/api/docs/query/over... https://developer.intuit.com/app/developer/qbo/docs/learn/explore-the-quickbooks-online-api/data-queries https://developer.intuit.com/app/developer/qbo/docs/learn/ex... https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.... https://carto.com/developers/sql-api/ https://carto.com/developers/sql-api/ From an end user standpoint, I preferred working with such API than with the ones creating nested json filters structures to model the filtering logic. It also provides a convenient way to select fields in the response, better in my opinion than "field mask" alternative that you can find on other APIs.

valenterry 3y ago
Thanks! I might steal this list if I extend the article later. ;-)

aragonite 3y ago

This is a bit off-topic, but I wonder if anyone knows of a programming language/DSL that supports 'subsentential' logical operators. I was just thinking the other day how convenient it'd be to be able to write SQL queries like this: ... WHERE (either A or B or C) (either beginswith OR endswith) (either X or Y or Z) i.e. expressing the condition in a way closely mirroring the English syntax, instead of having to fully write out all 18 (=3x2x3) clauses. (Natural rules for short-circuiting would apply, so if 'A beginswith X' evaluates to true, no further conditions need be check) This isn't just limited to query languages. In many programming languages, it strikes me as odd that I can't write something like `if A is (either X or Z)`, which mirrors how I would naturally express the condition in English, but need to reformulate it as `if A is X or A is Z`, or (worse IMO from the readability standpoint) something like `if [X,Z].includes(A)`.

dubbel 3y ago
I don't know any language that offers this, I just wanted to say that I have also thought about this feature at times - but when reading your pseudocode I didn't initially get it, because of the OR, which already has its meaning - instead one could use "either", like "either(a, b, c).either(beginswith, endswith)...".
- aragonite 3y ago
  Thanks! I just fixed the pseudocode. Indeed `either ... or` seems almost obligatory for clarity purposes in this type of context.

avinassh 3y ago

This post reminded me of Fly's shipping the DB idea [0]. It sounds crazy, but works beautifully for use cases. Instead of data, the caller gets the replicated SQLite itself and may do whatever it wishes to do. [0] Skip the API, Ship Your Database - https://fly.io/blog/skip-the-api/ https://fly.io/blog/skip-the-api/

paulryanrogers 3y ago
And clients will just have to deal with schema changes, or never make backward incompatible changes
- dventimi 3y ago
  Not if the tables in the schema that's changing are hidden behind an abstraction layer of views and procedures, they won't.

thiht 3y ago

> Because we are essentially inventing our own query language here. And inventing a custom language should be pretty much the last resort, I hope we agree on that. No. I don’t agree at all. Creating a custom query language based on json or lisp or whatever is easy enough, flexible enough, prone to evolution, and helps building an abstraction between the front and the back. With this in mind, the day you decide to query an ElasticSearch instead of the RDBMS, the frontal API doesn’t need to change, there’s juste to update the translation layer to generate an ES query instead of SQL. Exposing SQL to the API is an awful idea.

sdsd 3y ago

When I was reading Beautiful Racket [0] I was still working as a data engineer, writing complex SQL queries all day. I actually sort of fell in love with SQL, and decided to write a web development framework in SQL. INSERT requests would create forms, SELECT would create a table. The idea was that you could create something simple like an BBS with only a few lines of code: CREATE TABLE posts (title TEXT, content TEXT) INSERT INTO posts (title, content) VALUES (request.title, request.content) SELECT title, content FROM posts It actually works! Here's the code on GH: https://github.com/seisvelas/Sea-Quill https://github.com/seisvelas/Sea-Quill Sadly, I stopped working on it after only 1 or 2 weekends because I switched professions to cybersecurity and had too much to learn - every weekend thereafter was CTFs or bug bounties. Oh well! Someday I'd like to write a more intuitive, "conventional" style language for Urbit, maybe called HoonScript or something. So my affection for Racket's language oriented programming will (maybe, one day) not be in vain!

sdsd 3y ago
[0] - https://beautifulracket.com/ https://beautifulracket.com/

beaugunderson 3y ago

In this vein I was surprised to learn that crt.sh (certificate transparency log) lets anyone connect to their postgres and write their own queries against it: psql --host=crt.sh --port=5432 --username=guest certwatch

dventimi 3y ago
So what? That role has read-only access probably to all the relations, and it has a statement_timeout of '2 min'. Evidently, that's acceptable to its maintainers and we're in no position to say that they're wrong.
- beaugunderson 3y ago
  It is an example of using an actual SQL database as a public API that works very well (and which is quite rare!) so I felt it was worth sharing.
  - dventimi 3y ago
    Fair enough! Thank you for sharing!