18 ms·

SQL as an API language

201 points by craigkerstiens 8y ago

truth_seeker 8y ago
I am all for it. Also, Postgres FDW for network(TCP/HTTP/UDP) sockets would be great to further enhance or push the capabilities of PG
skrebbel 8y ago
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.
- 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.
zrail 8y ago
I've been thinking about this in terms of GDPR. Most companies will just give a CSV dump to the user, but what if we exported it as a SQLite database instead (or in addition to)?
- tlrobinson 8y ago
  That would be nice, but companies don't really have any incentive to make it easy for you to use your data elsewhere.
- Radim 8y ago
  Ha! We do offer powerful "personal information analytics" in terms of GDPR, https://pii-tools.com https://pii-tools.com :) It's more for auditors and DPOs though. The challenge with GDPR is more simplicity, taming the mess. Rather than adding more options. What sort of use-case scenarios do you have in mind? What need would SQLite exports solve?
egeozcan 8y ago
I really want to use SQL everywhere. I'm sad that they deprecated the WebSQL, and looking forward to the WebAssembly implementations (IIRC there already was a project doing that).
- deleted 8y ago
  [deleted]
- mamcx 8y ago
  I step up the challenge: To use the relational model everywhere. I'm working on it: http://tablam.org http://tablam.org Eventually, if this project move forward, I pretend to provide a nicer version of sql on top of it, so get possible to do: from customers where id=1 instead of select * from customers where id=1
bpchaps 8y ago
A really neat project (written in haskell) for turning a postgres database into a restful API: https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest
- durbatuluk 8y ago
  This is a amazing project. It has many of features I always lost my time writing API.
- Noumenon72 8y ago
  I wish it gave a little "hello world" example of what it lets you do.
  - bpchaps 8y ago
    From that repo: http://postgrest.org/en/stable/api.html http://postgrest.org/en/stable/api.html
    - Noumenon72 8y ago
      That's just what I wanted. What kind of search skills did you use to find it? Is there a convention?
- KayEss 8y ago
  The thing I disliked about it is that the APIs were very ugly. We ended up building our own tool that allows for much nicer APIs to be built: <https://github.com/KayEss/fostgres> https://github.com/KayEss/fostgres>
dustingetz 8y ago
Don't use JSON, XML has been able to do that since 1996! I actually agree with the premise – my startup is http://www.hyperfiddle.net/ http://www.hyperfiddle.net/ – but SQL is too complicated and abstraction-resistant in a dozen different ways to be a suitable foundation.
mindcrime 8y ago
I'd rather see "SPARQL[1][2] as an API language" myself. SPARQL is criminally under-appreciated. BTW, if anybody wants to play around with SPARQL and see some of what it can do, the Wikidata public SPARQL endpoint[3] is very nice and comes preloaded with some great examples[4]. [1]: https://en.wikipedia.org/wiki/SPARQL https://en.wikipedia.org/wiki/SPARQL [2]: https://www.w3.org/TR/sparql11-overview/ https://www.w3.org/TR/sparql11-overview/ [3]: https://query.wikidata.org/ https://query.wikidata.org/ [4]: https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/queries/examples https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/...
- owyn 8y ago
  This may just be down to wikidata's implementation, but the reliance on hardcoded unique id's everywhere is... bleh The following query uses these: Items: hospital (Q16917) Properties: instance of (P31) coordinate location (P625) 2 #defaultView:Map 3 SELECT * WHERE { 4 ?item wdt:P31*/wdt:P279* wd:Q16917; 5 wdt:P625 ?geo . 6 }
  - mindcrime 8y ago
    You always need unique identifiers for things, that's pretty much core to the whole Semantic Web idea. But what makes the Wikidata approach frustrating to me is that they use these identifiers that are totally opaque. I can sort of understand why they did it that way, but it does make for ugly queries.
cryptonector 8y ago
Use PostgREST (written in Haskell) and PostgreSQL. No SQL injection, but you get a lot of the power of SQL anyways.
qwerty456127 8y ago
> On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold. Can't it consider the complexity of the query and the actual database (indices, tables size etc) to guess how heavy the request is goign to be in advance?
- arez 8y ago
  sure, but aren't there some things that can't calculated in advance like i/o, locked tables or priority queries just to name a few
  - qwerty456127 8y ago
    > sure, but aren't there some things that can't calculated in advance I don't mean precise calculation but a reasonable guess > like i/o It can be benchmarked and estimated. > locked tables Why would tables be locked if we work in read-only mode?
    - jondumbau 8y ago
      How did the data get there.
      - qwerty456127 8y ago
        The page by the link says it's about the immutable mode and INSERT and UPDATE errors raise errors. As about me it's a very usual use case: I pre-populate a database with a huge data set (some GiBs) in a single batch and work with the data (data science stuff) in read-only mode then, some time after, add another batch of data (usually much smaller than the initial one) to it but database writing and reading never happens simultaneously in this scenario. In fact Datasette seems a thing I've always wanted as it is probably going to let me access my SQLite databases over a network this way.
steve-chavez 8y ago
You could get a more complete solution with PostgreSQL + PostgREST. Regarding the concerns: > Security: we don’t want people messing up our data Row level security + roles/grants should be enough to cover this one. > On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold. With a timeout, what you'll need is to request the expensive queries in parallel to ddos the service. Would be better to filter by the query plan, in PostgreSQL there's an extension for this https://github.com/pgexperts/pg_plan_filter https://github.com/pgexperts/pg_plan_filter. Though pg also has a `statement_timeout`. > Hiding implementation details You can expose a dedicated schema for you API that doesn't contain a single table but only views and stored functions. Migrating this schema should be easy thanks to transactional DDL. That being said, I agree with the sentiment about GraphQL, besides the extra query language, there's an additional impedance mismatch since now you have to map your DB schema to the GraphQL schema.
- copperx 8y ago
  I'm always surprised that PostgREST isn't the industry standard for creating an API. It looks like it would save so much work and the corner cases can be handled without writing code in a different language. What am I missing?
  - tempotemporary 8y ago
    PostgraphQL
  - mmt 8y ago
    If I had to guess, this is due to a combination of only being a few years old and marketing (initially, the major version number was 0).
  - ufmace 8y ago
    I tried it, and still have it running on a little toy service I built. It's cool, but IMO, it doesn't really seem production-ready for a large-scale service. Last time I updated it, I had to spend a full day sorting out the changes they made to how the server config and user authentication works. Last I checked, they don't even have a suggested script to run it as a service. Going by the contributor graph, it's still essentially a 1-person project.
forkandwait 8y ago
Slightly on topic: when giving unsolicited tech career advice, my favorite trope is "learn sql". Don't have any computer background? Start with SQL. Already write compilers for living but don't know SQL? Of course you should learn sql. A www dev who is tied to ORMs? Learn SQL. A manager who wants to understand the IT that runs your department? Yep, SQL. Etc, etc. SQL is probably the most ubiquitous language under hood today.
- WrtCdEvrydy 8y ago
  It powers the best tested technology on the planets. We've been writing relational databases for 45 years, and it has worked.
  - weberc2 8y ago
    Well, sort of. Every decade it bolts on support for the new technology craze, and usually that proves insufficient so vendors add their own features, and even that tends to be pretty painful. Definitely a useful skill set but it’s still pretty painful.
    - tomnipotent 8y ago
      > Every decade it bolts on support for the new technology craze You mean they got better? I've been using Postgres, MySQL, and SQL Server professionally for over 20 years (Postgres for only 15), but they've only become better & faster over time. I don't see this "technology craze" you mention, but I have seen things like parallel processing, bitmap indexes, selective indexes, query plans that use multiple indexes, online index creation (no locking), support for complex data types like JSON or XML, partitioning, vastly improved resource usage (CPU+memory+disk) just to name a few. This doesn't even include some of the cool vendor-specific features like foreign data wrappers (FDW) in Postgres, embedded language runtime support (SQL Server can do .NET, Python, R) and so on. > so vendors add their own features Of course they do, RDBMS' are products.
      - weberc2 8y ago
domoritz 8y ago
I actually implemented something like this a few years ago on top of Postgres for the CKAN Data Portal Software. Check out the docs at https://docs.ckan.org/en/2.8/maintaining/datastore.html#ckanext.datastore.logic.action.datastore_search_sql https://docs.ckan.org/en/2.8/maintaining/datastore.html#ckan.... CKAN is used by data.gov, data.gov.uk and many other governments to publish open data.
batbomb 8y ago
We do this heavily in astronomy: http://ivoa.net/documents/TAP http://ivoa.net/documents/TAP Although it's a bit crusty, it is heavily used. We store additional metadata about columns and we annotate the results we return. You can build something easily like this in Java with the Presto parser, including adding authorization to systems that can't/don't support it. If Go is more of your thing, there's a parser in Vitess: https://github.com/vitessio/vitess/blob/master/go/vt/sqlparser/sql.y https://github.com/vitessio/vitess/blob/master/go/vt/sqlpars... I had done some work on porting the presto parser to python (github.com/slaclab/lacquer) to also build an API, but I had some problems in my parser grammar I never figured out. I was going to revisit this again now that Antlr4 can deal with Python properly and I can reuse the presto grammar, but I haven't had the chance. You can couple all of this with csvw as well, and get some additional nice features out + JSON-LD.
ricardobeat 8y ago
The author is missing a few important notions: - graphQL server is storage-agnostic. It allows you to mix databases, APIs, anything, into one cohesive queryable model. It acts as a hub for several data sources. Consumers don’t need to care - queries are easily composable, reusable, and can be aggregated for batching, deduplication and optimisation (can be done with a query planner, but is way more complex) - it’s safe enough for queries to be sent directly from the client on user devices
wenc 8y ago
This sounds unintuitive, but the most lightweight API for analytics work I've come across is a SQL view. It provides one level of indirection from the underlying data structures (if the data changes, you can just rewrite the view), and most db's provide very good access control. The view's schema is your interface contract, so users can rely on its output. I was all about building REST interfaces to our backend databases until I realized that SQL views were a simpler, more efficient abstraction for analytics/machine learning work. You may be like, "SQL views aren't REST APIs! It doesn't seem kosher!" But if you think about it, REST APIs were designed for retrieving small amounts of data quickly using simple parameters, which is useful for low-latency web applications. For analytics/machine learning work, we generally don't want that. We usually want to retrieve large chunks of pre-computed tabular data. And there's already a language for that... SQL. And SQL databases are naturally fast for large chunks of data (with optimizations like indexing, compression, etc. your results can get even faster... plus no JSON serialization cost). If you build a REST API to SQL datasets, you have to first (inefficiently) parse the returned JSON, and reconstruct the dataframe for downstream analytics/machine learning work. Why do that, when you can use SQL (with all of its attendant data aggregation/manipulation keywords) to efficiently retrieve data via a binary wire protocol (native drivers or ODBC/JDBC) into a dataframe-like object?
- Serow225 8y ago
  Can you please explain what the interface looks like a little more concretely?
  - wenc 8y ago
    It's exactly as it sounds: a SQL view on a database server. It manifests as a SQL table backed by a query. It's mind-bendingly simple. You can connect to it via a native db connector or ODBC. It's also accessible to a wide range of tools including Excel, Tableau, or any number of programming languages. In Python you can pull data from it into a Pandas dataframe via TurbODBC (which recently added Apache Arrow in-memory support). In R, you can connect to it via dplyr. Apache Spark can get data from it via JDBC. You can experiment with it via Jupyter Notebooks. SQL support is ubiquitous.
- 8y ago
tejasmanohar 8y ago
Salesforce actually exposes a SQL variant in their bulk export API called SOQL, https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm https://developer.salesforce.com/docs/atlas.en-us.soql_sosl..... It made it surprisingly easy for me to do some SF ETL tool a few years ago.
voltagex_ 8y ago
https://www.geopackage.org/ https://www.geopackage.org/ is worth a look too - although it's more "SQLite as file format" than SQLite as API.
commandlinefan 8y ago
In my experience, REST APIs devolve into thin wrappers over SQL statements anyway. Might as well embrace it.
halfway 8y ago
The best "API language" is the one constructed to work with the app's data model. SQL constructs are relational - fine for rails, django, or salesforce apps which map screen to table (or table join). Wouldn't work for facebook, google knowledge graph, or the larger enterprise app vendors today. Theirs is typed graph.
Someone 8y ago
Reading the title, I expected something like update screen set red=3, green=200, blue=125 where (row-100)*(row-100) + (col-200)+(col-200) < 12345 That could be a wonderful hack, certainly if combined with triggers that generate or even react to screen updates. Carefully done joins could easily blit in character bitmaps and icons. If you have time at hand, feel free to implement this idea by adding a storage engine that stores on screen to your favorite sql database. Bonus points if you manage to run many of the updates on the GPU.
tango12 8y ago
SQL could actually be an API language without needing to be wrapped in JSON/GraphQL. Building a GraphQL backend makes you deal with a GraphQL AST, parse it, process it and so on. GraphQL backends are hard to build because naive implementations can't "optimise" the AST and resolve to making individual data calls for each node in the AST. But considering the tooling around GraphQL on the server and client side, it would have been amazing to have that API tooling around SQL! Assuming that writing an SQL parser, hooking into the AST to run the business logic or to add auth rules, and then use great client side libraries like jooq [1], massivejs [2] to be able to construct SQL queries programmatically, SQL as an API could have been a very feasible and neat idea! The similarities between GraphQL and SQL are many on the surface. Another nice parallel that the blogpost doesn't mention is the idea of query variables in GraphQL which is reminiscent of prepared statements + variables in Postgres. We believe in a similar thesis at Hasura[3], where the idea is to give app developers GraphQL on SQL (Postgres) with minimal abstraction. However, we've had to do a lot of work to support "application level" authorization so that read/write permissions are controlled through the applications auth system and make it a more complete solution. In fact, our initial version was basically a JSON DSL very similar to datasette, and we switched to GraphQL primarily because the client-side tooling is quite amazing. [1] https://www.jooq.org/ https://www.jooq.org/ [2] https://github.com/dmfay/massive-js https://github.com/dmfay/massive-js [3] https://github.com/hasura/graphql-engine https://github.com/hasura/graphql-engine
emmelaich 8y ago
See also Programming in the URL string https://apenwarr.ca/log/20121218 https://apenwarr.ca/log/20121218 Previously (not :-) discussed much at https://news.ycombinator.com/item?id=4939674 https://news.ycombinator.com/item?id=4939674
jillesvangurp 8y ago
The problem with SQL is that most products that implement it have subtle (and not so subtle) differences with custom functions, types, syntax for all sorts of things, etc. Sqlite is actually rather limited and lacks a lot of stuff you'd find in e.g. postgres or mysql, both of which tend to implement the same things very differently. That's of course aside from things like joins, sub selects, etc. ANSI SQL only gets you so far. I'd argue decoupling how you distribute data from how you store data, and how you are going to query it is probably a good thing. CSV is indeed not a great format because it lacks structure and types (everything is a string). Flattening complex data in csv format can get ugly quickly. I recently had to deal with some proprietary object database dumped out as csv. I had some great fun trying to reverse engineer their schema from the raw data. XML has the same problem unless you dream up some schema that explicitly states the type. Json has some basic types for strings, booleans, and numbers. This makes it a bit more useful than XML out of the box. Both xml xml and json objects represent trees of information and SQL databases represent tables of rows. Of course some databases can store json (or xml even) and allow you to do some queries over that. E.g. Elasticsearch does a decent job of guessing schemas for json documents. It will figure out things like dates, geojson, numbers, strings, etc. So, distributing data as sqlite files is not necessarily a horrible idea but it doesn't really solve the problem of how to move data between different data tools. What's lacking is a standard way to to distribute strongly typed tabular data. Most databases can import/export csv and native inserts for their flavor of sql but not much else. Most (open) data get shipped using some custom schema on top of xml, csv, json or whatever else seemed fashionable at the time.
z3t4 8y ago
Is there any better alternatives to SQL for requesting data !?
lukaseder 8y ago
> On security: the data is read-only, using SQLite’s immutable mode. You can’t damage it with a query—INSERT and UPDATEs will simply throw harmless errors. In times of data breaches, the naivety of this statement is alarming. It is not too difficult to extract (all) data from an API that allows for passing arbitrary SQL statements. Be careful here!
- lukaseder 8y ago
  I mean this: https://fivethirtyeight.datasettes.com/fivethirtyeight-c9e67c4?sql=select+*+from+sqlite_master https://fivethirtyeight.datasettes.com/fivethirtyeight-c9e67...

I am all for it. Also, Postgres FDW for network(TCP/HTTP/UDP) sockets would be great to further enhance or push the capabilities of PG

skrebbel 8y ago

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.
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.

zrail 8y ago

I've been thinking about this in terms of GDPR. Most companies will just give a CSV dump to the user, but what if we exported it as a SQLite database instead (or in addition to)?

tlrobinson 8y ago
That would be nice, but companies don't really have any incentive to make it easy for you to use your data elsewhere.
Radim 8y ago
Ha! We do offer powerful "personal information analytics" in terms of GDPR, https://pii-tools.com https://pii-tools.com :) It's more for auditors and DPOs though. The challenge with GDPR is more simplicity, taming the mess. Rather than adding more options. What sort of use-case scenarios do you have in mind? What need would SQLite exports solve?

egeozcan 8y ago

I really want to use SQL everywhere. I'm sad that they deprecated the WebSQL, and looking forward to the WebAssembly implementations (IIRC there already was a project doing that).

deleted 8y ago
[deleted]
mamcx 8y ago
I step up the challenge: To use the relational model everywhere. I'm working on it: http://tablam.org http://tablam.org Eventually, if this project move forward, I pretend to provide a nicer version of sql on top of it, so get possible to do: from customers where id=1 instead of select * from customers where id=1

bpchaps 8y ago

A really neat project (written in haskell) for turning a postgres database into a restful API: https://github.com/PostgREST/postgrest https://github.com/PostgREST/postgrest

durbatuluk 8y ago
This is a amazing project. It has many of features I always lost my time writing API.
Noumenon72 8y ago
I wish it gave a little "hello world" example of what it lets you do.
- bpchaps 8y ago
  From that repo: http://postgrest.org/en/stable/api.html http://postgrest.org/en/stable/api.html
  - Noumenon72 8y ago
    That's just what I wanted. What kind of search skills did you use to find it? Is there a convention?
KayEss 8y ago
The thing I disliked about it is that the APIs were very ugly. We ended up building our own tool that allows for much nicer APIs to be built: <https://github.com/KayEss/fostgres> https://github.com/KayEss/fostgres>

dustingetz 8y ago

Don't use JSON, XML has been able to do that since 1996! I actually agree with the premise – my startup is http://www.hyperfiddle.net/ http://www.hyperfiddle.net/ – but SQL is too complicated and abstraction-resistant in a dozen different ways to be a suitable foundation.

mindcrime 8y ago

I'd rather see "SPARQL[1][2] as an API language" myself. SPARQL is criminally under-appreciated. BTW, if anybody wants to play around with SPARQL and see some of what it can do, the Wikidata public SPARQL endpoint[3] is very nice and comes preloaded with some great examples[4]. [1]: https://en.wikipedia.org/wiki/SPARQL https://en.wikipedia.org/wiki/SPARQL [2]: https://www.w3.org/TR/sparql11-overview/ https://www.w3.org/TR/sparql11-overview/ [3]: https://query.wikidata.org/ https://query.wikidata.org/ [4]: https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/queries/examples https://www.wikidata.org/wiki/Wikidata:SPARQL_query_service/...

owyn 8y ago
This may just be down to wikidata's implementation, but the reliance on hardcoded unique id's everywhere is... bleh The following query uses these: Items: hospital (Q16917) Properties: instance of (P31) coordinate location (P625) 2 #defaultView:Map 3 SELECT * WHERE { 4 ?item wdt:P31*/wdt:P279* wd:Q16917; 5 wdt:P625 ?geo . 6 }
- mindcrime 8y ago
  You always need unique identifiers for things, that's pretty much core to the whole Semantic Web idea. But what makes the Wikidata approach frustrating to me is that they use these identifiers that are totally opaque. I can sort of understand why they did it that way, but it does make for ugly queries.

cryptonector 8y ago

Use PostgREST (written in Haskell) and PostgreSQL. No SQL injection, but you get a lot of the power of SQL anyways.

qwerty456127 8y ago

> On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold. Can't it consider the complexity of the query and the actual database (indices, tables size etc) to guess how heavy the request is goign to be in advance?

arez 8y ago
sure, but aren't there some things that can't calculated in advance like i/o, locked tables or priority queries just to name a few
- qwerty456127 8y ago
  > sure, but aren't there some things that can't calculated in advance I don't mean precise calculation but a reasonable guess > like i/o It can be benchmarked and estimated. > locked tables Why would tables be locked if we work in read-only mode?
  - jondumbau 8y ago
    How did the data get there.
    - qwerty456127 8y ago
      The page by the link says it's about the immutable mode and INSERT and UPDATE errors raise errors. As about me it's a very usual use case: I pre-populate a database with a huge data set (some GiBs) in a single batch and work with the data (data science stuff) in read-only mode then, some time after, add another batch of data (usually much smaller than the initial one) to it but database writing and reading never happens simultaneously in this scenario. In fact Datasette seems a thing I've always wanted as it is probably going to let me access my SQLite databases over a network this way.

steve-chavez 8y ago

You could get a more complete solution with PostgreSQL + PostgREST. Regarding the concerns: > Security: we don’t want people messing up our data Row level security + roles/grants should be enough to cover this one. > On performance: SQLite has a mechanism for canceling queries that take longer than a certain threshold. With a timeout, what you'll need is to request the expensive queries in parallel to ddos the service. Would be better to filter by the query plan, in PostgreSQL there's an extension for this https://github.com/pgexperts/pg_plan_filter https://github.com/pgexperts/pg_plan_filter. Though pg also has a `statement_timeout`. > Hiding implementation details You can expose a dedicated schema for you API that doesn't contain a single table but only views and stored functions. Migrating this schema should be easy thanks to transactional DDL. That being said, I agree with the sentiment about GraphQL, besides the extra query language, there's an additional impedance mismatch since now you have to map your DB schema to the GraphQL schema.

copperx 8y ago
I'm always surprised that PostgREST isn't the industry standard for creating an API. It looks like it would save so much work and the corner cases can be handled without writing code in a different language. What am I missing?
- tempotemporary 8y ago
  PostgraphQL
- mmt 8y ago
  If I had to guess, this is due to a combination of only being a few years old and marketing (initially, the major version number was 0).
- ufmace 8y ago
  I tried it, and still have it running on a little toy service I built. It's cool, but IMO, it doesn't really seem production-ready for a large-scale service. Last time I updated it, I had to spend a full day sorting out the changes they made to how the server config and user authentication works. Last I checked, they don't even have a suggested script to run it as a service. Going by the contributor graph, it's still essentially a 1-person project.

forkandwait 8y ago

Slightly on topic: when giving unsolicited tech career advice, my favorite trope is "learn sql". Don't have any computer background? Start with SQL. Already write compilers for living but don't know SQL? Of course you should learn sql. A www dev who is tied to ORMs? Learn SQL. A manager who wants to understand the IT that runs your department? Yep, SQL. Etc, etc. SQL is probably the most ubiquitous language under hood today.

WrtCdEvrydy 8y ago
It powers the best tested technology on the planets. We've been writing relational databases for 45 years, and it has worked.
- weberc2 8y ago
  Well, sort of. Every decade it bolts on support for the new technology craze, and usually that proves insufficient so vendors add their own features, and even that tends to be pretty painful. Definitely a useful skill set but it’s still pretty painful.
  - tomnipotent 8y ago
    > Every decade it bolts on support for the new technology craze You mean they got better? I've been using Postgres, MySQL, and SQL Server professionally for over 20 years (Postgres for only 15), but they've only become better & faster over time. I don't see this "technology craze" you mention, but I have seen things like parallel processing, bitmap indexes, selective indexes, query plans that use multiple indexes, online index creation (no locking), support for complex data types like JSON or XML, partitioning, vastly improved resource usage (CPU+memory+disk) just to name a few. This doesn't even include some of the cool vendor-specific features like foreign data wrappers (FDW) in Postgres, embedded language runtime support (SQL Server can do .NET, Python, R) and so on. > so vendors add their own features Of course they do, RDBMS' are products.
    - weberc2 8y ago

domoritz 8y ago

I actually implemented something like this a few years ago on top of Postgres for the CKAN Data Portal Software. Check out the docs at https://docs.ckan.org/en/2.8/maintaining/datastore.html#ckanext.datastore.logic.action.datastore_search_sql https://docs.ckan.org/en/2.8/maintaining/datastore.html#ckan.... CKAN is used by data.gov, data.gov.uk and many other governments to publish open data.

batbomb 8y ago

We do this heavily in astronomy: http://ivoa.net/documents/TAP http://ivoa.net/documents/TAP Although it's a bit crusty, it is heavily used. We store additional metadata about columns and we annotate the results we return. You can build something easily like this in Java with the Presto parser, including adding authorization to systems that can't/don't support it. If Go is more of your thing, there's a parser in Vitess: https://github.com/vitessio/vitess/blob/master/go/vt/sqlparser/sql.y https://github.com/vitessio/vitess/blob/master/go/vt/sqlpars... I had done some work on porting the presto parser to python (github.com/slaclab/lacquer) to also build an API, but I had some problems in my parser grammar I never figured out. I was going to revisit this again now that Antlr4 can deal with Python properly and I can reuse the presto grammar, but I haven't had the chance. You can couple all of this with csvw as well, and get some additional nice features out + JSON-LD.

ricardobeat 8y ago

The author is missing a few important notions: - graphQL server is storage-agnostic. It allows you to mix databases, APIs, anything, into one cohesive queryable model. It acts as a hub for several data sources. Consumers don’t need to care - queries are easily composable, reusable, and can be aggregated for batching, deduplication and optimisation (can be done with a query planner, but is way more complex) - it’s safe enough for queries to be sent directly from the client on user devices

wenc 8y ago

This sounds unintuitive, but the most lightweight API for analytics work I've come across is a SQL view. It provides one level of indirection from the underlying data structures (if the data changes, you can just rewrite the view), and most db's provide very good access control. The view's schema is your interface contract, so users can rely on its output. I was all about building REST interfaces to our backend databases until I realized that SQL views were a simpler, more efficient abstraction for analytics/machine learning work. You may be like, "SQL views aren't REST APIs! It doesn't seem kosher!" But if you think about it, REST APIs were designed for retrieving small amounts of data quickly using simple parameters, which is useful for low-latency web applications. For analytics/machine learning work, we generally don't want that. We usually want to retrieve large chunks of pre-computed tabular data. And there's already a language for that... SQL. And SQL databases are naturally fast for large chunks of data (with optimizations like indexing, compression, etc. your results can get even faster... plus no JSON serialization cost). If you build a REST API to SQL datasets, you have to first (inefficiently) parse the returned JSON, and reconstruct the dataframe for downstream analytics/machine learning work. Why do that, when you can use SQL (with all of its attendant data aggregation/manipulation keywords) to efficiently retrieve data via a binary wire protocol (native drivers or ODBC/JDBC) into a dataframe-like object?

Serow225 8y ago
Can you please explain what the interface looks like a little more concretely?
- wenc 8y ago
  It's exactly as it sounds: a SQL view on a database server. It manifests as a SQL table backed by a query. It's mind-bendingly simple. You can connect to it via a native db connector or ODBC. It's also accessible to a wide range of tools including Excel, Tableau, or any number of programming languages. In Python you can pull data from it into a Pandas dataframe via TurbODBC (which recently added Apache Arrow in-memory support). In R, you can connect to it via dplyr. Apache Spark can get data from it via JDBC. You can experiment with it via Jupyter Notebooks. SQL support is ubiquitous.
8y ago

tejasmanohar 8y ago

Salesforce actually exposes a SQL variant in their bulk export API called SOQL, https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm https://developer.salesforce.com/docs/atlas.en-us.soql_sosl..... It made it surprisingly easy for me to do some SF ETL tool a few years ago.

voltagex_ 8y ago

https://www.geopackage.org/ https://www.geopackage.org/ is worth a look too - although it's more "SQLite as file format" than SQLite as API.

commandlinefan 8y ago

In my experience, REST APIs devolve into thin wrappers over SQL statements anyway. Might as well embrace it.

halfway 8y ago

The best "API language" is the one constructed to work with the app's data model. SQL constructs are relational - fine for rails, django, or salesforce apps which map screen to table (or table join). Wouldn't work for facebook, google knowledge graph, or the larger enterprise app vendors today. Theirs is typed graph.

Someone 8y ago

Reading the title, I expected something like update screen set red=3, green=200, blue=125 where (row-100)*(row-100) + (col-200)+(col-200) < 12345 That could be a wonderful hack, certainly if combined with triggers that generate or even react to screen updates. Carefully done joins could easily blit in character bitmaps and icons. If you have time at hand, feel free to implement this idea by adding a storage engine that stores on screen to your favorite sql database. Bonus points if you manage to run many of the updates on the GPU.

tango12 8y ago

SQL could actually be an API language without needing to be wrapped in JSON/GraphQL. Building a GraphQL backend makes you deal with a GraphQL AST, parse it, process it and so on. GraphQL backends are hard to build because naive implementations can't "optimise" the AST and resolve to making individual data calls for each node in the AST. But considering the tooling around GraphQL on the server and client side, it would have been amazing to have that API tooling around SQL! Assuming that writing an SQL parser, hooking into the AST to run the business logic or to add auth rules, and then use great client side libraries like jooq [1], massivejs [2] to be able to construct SQL queries programmatically, SQL as an API could have been a very feasible and neat idea! The similarities between GraphQL and SQL are many on the surface. Another nice parallel that the blogpost doesn't mention is the idea of query variables in GraphQL which is reminiscent of prepared statements + variables in Postgres. We believe in a similar thesis at Hasura[3], where the idea is to give app developers GraphQL on SQL (Postgres) with minimal abstraction. However, we've had to do a lot of work to support "application level" authorization so that read/write permissions are controlled through the applications auth system and make it a more complete solution. In fact, our initial version was basically a JSON DSL very similar to datasette, and we switched to GraphQL primarily because the client-side tooling is quite amazing. [1] https://www.jooq.org/ https://www.jooq.org/ [2] https://github.com/dmfay/massive-js https://github.com/dmfay/massive-js [3] https://github.com/hasura/graphql-engine https://github.com/hasura/graphql-engine

emmelaich 8y ago

See also Programming in the URL string https://apenwarr.ca/log/20121218 https://apenwarr.ca/log/20121218 Previously (not :-) discussed much at https://news.ycombinator.com/item?id=4939674 https://news.ycombinator.com/item?id=4939674

jillesvangurp 8y ago

The problem with SQL is that most products that implement it have subtle (and not so subtle) differences with custom functions, types, syntax for all sorts of things, etc. Sqlite is actually rather limited and lacks a lot of stuff you'd find in e.g. postgres or mysql, both of which tend to implement the same things very differently. That's of course aside from things like joins, sub selects, etc. ANSI SQL only gets you so far. I'd argue decoupling how you distribute data from how you store data, and how you are going to query it is probably a good thing. CSV is indeed not a great format because it lacks structure and types (everything is a string). Flattening complex data in csv format can get ugly quickly. I recently had to deal with some proprietary object database dumped out as csv. I had some great fun trying to reverse engineer their schema from the raw data. XML has the same problem unless you dream up some schema that explicitly states the type. Json has some basic types for strings, booleans, and numbers. This makes it a bit more useful than XML out of the box. Both xml xml and json objects represent trees of information and SQL databases represent tables of rows. Of course some databases can store json (or xml even) and allow you to do some queries over that. E.g. Elasticsearch does a decent job of guessing schemas for json documents. It will figure out things like dates, geojson, numbers, strings, etc. So, distributing data as sqlite files is not necessarily a horrible idea but it doesn't really solve the problem of how to move data between different data tools. What's lacking is a standard way to to distribute strongly typed tabular data. Most databases can import/export csv and native inserts for their flavor of sql but not much else. Most (open) data get shipped using some custom schema on top of xml, csv, json or whatever else seemed fashionable at the time.

z3t4 8y ago

Is there any better alternatives to SQL for requesting data !?

lukaseder 8y ago

> On security: the data is read-only, using SQLite’s immutable mode. You can’t damage it with a query—INSERT and UPDATEs will simply throw harmless errors. In times of data breaches, the naivety of this statement is alarming. It is not too difficult to extract (all) data from an API that allows for passing arbitrary SQL statements. Be careful here!

lukaseder 8y ago
I mean this: https://fivethirtyeight.datasettes.com/fivethirtyeight-c9e67c4?sql=select+*+from+sqlite_master https://fivethirtyeight.datasettes.com/fivethirtyeight-c9e67...