I'm forgetting the name of it but there is a spec, I think in wicg, for low-level/byte-level file access. This should make using emscripten to compile things like sqlite far more straightforward, to build whatever you want.
I like the shout out to streaming changes out of the database, which the author mentions in terms of redux & time travel abilities from having a wal. Server side systems like Debezium for doing this have been gamechanging. At the moment, the high-level file api has no support for watching for changes (https://github.com/WICG/file-system-access/issues/72). Maybe post 1.0 we might see progress. I don't believe the low-level api has anything for watching for changes.
From a more meta-assesment level of this article: I do hope we can stick with HTTP centric entities, personally. GraphQL with it's generic endpoint that all operations get sent to is, in my view, quite a bad development. But not irreconcileably so: bridges could be built. I'm definitely in favor of experimentation, trying things out. But I also think there's good reasons to keep entities and their urls around, to not abandon that. GraphQL right now doesn't seem to think about that or care about that, but I also think it could be reformed.
> From article: we think developer aesthetics are an important consideration...we were resolved that using strings representing SQL commands lacked the elegance of a “web native” JavaScript API, and started looking at alternatives.
Seems a bit short-sighted considering SQL is super popular today on the backend.
There's a massive difference between exposing SQL as your API and exposing the database over a proxy. Your exposed SQL may have a separate model, or a more limited model, than what's actually in your database.
I think, basically, yes. What GraphQL really solves is letting the frontend compose complex, yet specific, queries. SQL lets you do the same thing. But REST APIs and gRPC-based APIs generally do not.
I guess what I would argue against in this post is that SQL is the right language for this. The post starts off with the assertion that we're just using SQL on the backend anyways - not so, in fact my company doesn't have a single SQL database.
But I think the overall point remains - if you want powerful clients you can't beat a query language.
Abstraction exists for a reason. It comes with trade-offs. Often they are worth it. To me, abstracting the DB layer seems like a no-brainer so often but I’ll admit that may be a bias due to getting minor heartburn when dealing with the DB layer in general...
Yes, a client side DSL for querying data is superior to REST.
But the data model of a complex app is usually more than its sql database. So maybe something closer to gql with code gen for your database is more appropriate?
a thing a I tried, and ran out of steam with, was writing SQL queries in my client side code but statically extracting them with babel, so you have some code like this:
const users = await sql.query`select * from users`;
console.log(users);
and the query itself stays on the server so you don't have to deal with the problem of unbounded complexity / DoS.
This is the same approach Facebook uses for GraphQL. The only reason I gave up with this idea is that getting the developer experience right is hard work and it does introduce some very tight coupling!
Legacy codebases are hard to remove because it's difficult but once you turn every SQL call into an HTTP call... the remaining part is the logic which can be rewritten into something more modern.
What's important is that those two EXTREMELY important paradigms of data organization basically don't support each other without kludges galore.
Hierarchical vs relational
But, fundamentally I agree with the poster, what we want are powerful proven data models and powerful proven access languages (SQL for relational, for hierarchical, I'll just throw out XPath which is basically the ONLY good thing from the era of XML that I liked).
Graph could potentially be considered, but IMO it has never proven itself in the practical marketplace. In particular it has no proven and stabilized query language in wide use by people that aren't domain experts, unlike users of the filesystem (basically everyone with a computer) and (less universally) users of databases.
No. I want and need to do manky shit on the backend that I desperately need to hide from users or the front end. If I can't hide that then basically all my users get to see my underwear and that's no good for many reasons (e.g. exploits, abusing the api, etc).
and all the security can of worms. Seriously, if you don't respect the benefit of the text layer in JSON then you need to go have a look at why .NET Remoting failed. You want loose coupling here.
I've spent the last six months working with a codebase that does exactly this. Aside from the obvious problems with exposing your schema to potential attackers, opening up potential DOS vectors, and training front-end engineers on yet another technology, you end up with some code that is very, very difficult to test. If you're using it in your hobby project and you're aware of the pitfalls - fine, go ahead and do whatever you like. But if you're expecting to incorporate this as part of an engineering organization: Save everyone the hassle now and don't.
These are the types APIs that I CONSTANTLY have to tell our internal guys "Hey, don't do that!"
I get WHY you'd want to be able to throw in any random filter/sort to get the exact datapoints you are after, but that's both really hard to scale and often ends up being highly coupled to the underlying datastore.
I get it, requirements gathering and making a clean API is hard and time consuming. What's harder is not doing that and needing to eventually figure out "How on earth can I get constant response times or stop someone from sending an app crashing query all while supporting current usages".
Yeah; reading this the only 'gain' I saw was "we don't have to define an API". But instead you get to try and ensure that raw SQL from the client is always safe.
The former isn't THAT hard to do. The latter is extraordinarily hard.
Get the former wrong and you fix it before it ever goes live. Get the latter wrong and you fix it only after you're massively pwned.
Agreed. The issue is it's "easy" to do if you have a massive data model and can feel pretty fun making a query-able API. What's more fun? Making your own DSL or writing a billion "/foo/{id}" endpoints. Usually the DSL is a lot more fun and rewarding, however, "/foo/{id}" is a FAR more stable and easy to maintain API.
That's where I've often run into the problem. When someone wants to make a monolith into a more microservice thing the hard part of carving out monolith usages takes meetings with consumers. Devs don't want a bunch of meetings and requirements gathering and a lot (at least in my org... :( ) would rather just write something cool and throw their hands up and say "We don't know how you'd use this, so we made you the boss".
A lot of internal enterprise tools explicitly have "filter everything" as a feature, so in those cases you might as well use a super-generic API, since you'll need that flexibility anyway.
Side-effects as well. You'll still need an API in order to handle things like sending transactional emails, kicking off batch jobs, or producing other side effects. It doesn't really spare you from developing an API for any non-trivial application.
Absolutely. Not to mention, it feels like a bleeding of concerns to me. The simplest answer to me is "I don't _want_ my front end concerned with SQL queries"
Well, back in the "olden days" in the late 90's, this was actually pretty common: ColdFusion allowed SQL statements to be embedded in the templates, and JSP had sql tags. IIRC, most people moved away from that model primarily for flexibility and maintainability: having SQL statements mixed in with the presentation led to a lot of code duplication and made putting a different front-end (say, a third party API) nearly impossible.
I use to build sites in PHP where the front and backend were basically the same thing, with only the database existing outside the single codebase. It was definitely common for there to be sql injection vulnerabilities and years later i shuttered at the thought of some of those companies still writing code I wrote when I was young and knew almost nothing I’d security. Luckily now days most of them have switched to new code bases (or went out of business) but this wasn’t the case even 2-3 years ago.
I wondered about that, too, but I'd be hard-pressed to say whether SQL injection vulnerabilities are more or less common if you embed SQL in the front-end vs. in a back-end service. I supposed they would be slightly easier to identify and exploit in a more modern front-end like React because the source code is actually viewable to users.
Because it exposes a lot of inner details and potential security/privacy risks if clients are able to change parameters.
But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.:
Signed blob:
{
sql: "SELECT name, email, ... FROM users WHERE users.id = %(userid)s"
allowedParameters: []
}
Such that the client is allowed to send this query, but they're not allowed to provide any parameters, the backend has to provide the userid. That seems workable to me.
Likewise:
{
sql: "SELECT order.price, order.... FROM orders WHERE orders.user_id = %(userid)s AND orders.id = %(orderid)s"
allowedParameters: ["orderid"]
}
This would allow the frontend to provide an order ID, but not the user ID - so the user still can't look at other people's orders and if not logged in, it's an invalid query without a userid. And the client isn't allowed to change the "sql" or parameters sent or the signature is invalid.
Edit: Someone else replied that if you have sufficiently advanced build tooling, you can statically extract the SQL/GraphQL from the client side code and replace them with IDs, and then move the SQL to the backend. That's very cool, but the average startup isn't using bazel/buck/etc. Those tools aren't yet small app friendly enough (which is unfortunate, IMO.)
Or you could could use Row Level Security, and/or stored functions/procedures for access controls. You can put these constraints in the DB, and just give each of your users a DB role.
Another issue is part of what makes a company valuable is their biz logic. Moving all of that to the frontend means you can no longer guard it. Even if you can extract it to the backend, how many REST/GQL endpoints use a single query? And where do the parameters get supplied if not from a route specific handler?
This is a really interesting interface design, but seems like for any non-trivial database it would be too easy to make mistakes and give the client too much access. Also seems like it's be really easy to end up with performance issues. Also, there's basically zero information hiding. The database is your interface. Changing the schema in any way whatsoever is a breaking change to the API! You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API. You've just turned your database schema into the API :'(
But I haven't actually seen a codebase that does this, so maybe I'm wrong. Have you used this approach in a project with more than a few developers and a large database? How did it go?
> You haven't really saved yourself from the hassle of designing and maintaining backward compatibility for the API.
Another way to look at it is that changing the database schema is already a problem for the API which has to talk to it, so you're not really saving yourself any work by putting an extra layer between the frontend and the database.
In fact, if anything, you're doubling the amount of work, because there are now two interfaces (DB to API, and API to frontend) whose backwards compatibility you have to worry about.
Yes. The decision calculus here really boils down to how many clients the API has and how much you care about those clients.
If you have 100 other teams each with 5+ developers consuming your API -- or especially if your API is a product -- then keeping the API to frontend interface relatively stable with rare breaking changes is much more valuable than saving yourself even a major time commitment on DB to API work.
On the other hand, if you have relatively few clients -- or if the clients are "lower status"/"cheaper" teams -- saving time on the DB to API work a the expense of client teams might make sense.
> You've just turned your database schema into the API
It might actually be quite easy to version an SQL api, because you have the DDL migrations files and could actually make things backwards compatible to some degree. You would have a translation layer to modify the queries. Of course, your migrations would need to be more detailed tracking how things map to each other. But probably it would get too complex in the end. But not that much different to changes to a REST api really.
This sounds like a good way to spend 2 years happy and then in the 3rd year spend a monumental amount of money hiring a world-class compilers/databases expert to 10x the perf of DB migrations :)
Not quite sure I get what's signing the request. Presumably if it's the client, it can be tampered with, so we're guessing that there's still an API layer signing things?
That said, I guess you could just add row / column ACLs to the database user, and then map the database user to the app user.
> Not quite sure I get what's signing the request.
Server sends the query so server can send the signature hash with it, so the server signs the query with the server's key and sends both query and signature.
If the server ever receives a query without a signature or with a signature that is incorrect it can ignore or generate an error.
Yeh, that's the best idea. A babel plugin could do it easily. I already have have watcher script calling `graphql-codegen` to add types to my graphql queries, which works fine in the workflow.
> But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters
Or...you could just expose a schema with views and sprocs where their definitions use only the SQL you are comfortable with against the base tables, which live off in their own schema isolated from external users. Same effect, but doesn’t require a whole new custom layer on top to reimplement what has been a basic feature of RDBMS engines for decades.
> Someone else replied that if you have sufficiently advanced build tooling, you can statically extract the SQL/GraphQL from the client side code and replace them with IDs, and then move the SQL to the backend.
We’ve taken this approach for implementing database queries in Lowdefy [0] (SQL support will be live in next version, implementation using knex [1]. Also a “shared state” between backend and frontend makes parsing paramaters to queries on the backend really seamless and in return a great dev experience. Also allows to to only parse some parameters like secrets only on the backend.
What we are experiencing with Lowdefy apps is that bringing the data model closer to the UI makes coding and UI maintainability of simple projects very easy. This approach works exceptional for us in the BI reporting space where you primarily aggregate data.
However as soon as you move out of the CRUD space to more complex backend logic, extracting the logic to a single interface again simplified the implementation a lot, in fact apps very quickly becomes hard to maintain in my experience.
This does not mean that there is no place for easy CRUD dev tools like OP, but I do believe that an API like solution is required when life gets more complex or even just transactional. But there is also more creative ways to solve this problem.
Don’t know about web support these days but a lot of mobile and desktop apps use a pattern of having a local SQLite db which is “synced” to a remote database using a sync protocol.
That sync protocol could be anything (rest, rpc, graphql, or even xml changesets in zip files on webDAV - I’m looking at you OmniFocus)
This setup has the benefits of being able to work offline (just sync later when network is back) and the ability to perform local SQL queries to populate UI views. But comes with all the extra complexity related to synchronising local and remote database. Stuff like handling conflicts.
How many people are actually using raw SQL on the backend? I thought people got tired of maintaining strings of raw SQL and migrated to query builders or ORMs.
> I usually end up with a bunch of Lodash (groupBy, filter, map, reduce) to shape the data I get from the server.
I mean, we are also doing the same thing with ORMs on the backend.
It's pretty rare for an ORM to cover all possible scenarios. I just introduced another raw query to our database because our ORM isn't able to generate `insert into ... select ... where not exists (select ...)` which is crucial for one critical bit that needs to handle idempotent writes.
This is true, and there is definitely a place for raw sql, but in my experience ORMs cover most cases and I only have to write SQL queries for special cases.
If you don't fully understand what happens under the hood with those ORMs and limit yourself to its, well limitations, then somebody is going to have a bad time. And in my experience too, with almost with all (read all that I tried) of them, some queries end up to be way easier to write yourself, and most frequent this is performance/resource issue.
for my latest contracted small web app project, I didn't see the need to get an ORM set up—I opted instead to just go for plain SQL statements in a single PHP file, even going so far as to use `?> ... <?` for "templating" HTML blocks, and a fat query string switch for the different subpages/GET/POST actions on the site. I'm sure almost everyone here, especially web developers, would absolutely cringe at what I've done, but for the scope of this small project it works extremely well. after my last job at a remote django/react shop, it was a breath of fresh air to build something "from scratch" without setting up and configuring large amounts of boilerplate, class structures, etc.—instead, the website is quite procedural, just like I would've written when I was teaching myself web development in high school in the mid 00s (except, without the gaping SQL injection holes). designing the web app this way let me build it quickly and rapidly iterate on features as the client requested, and he ended up extremely happy with the results.
> How many people are actually using raw SQL on the backend?
I sure do. Nothing beats being able to just copy/paste a SQL query from a code base to your client to see what's going on. Then tweak that said query until it works as expected. When I do backend development involving the database a lot (and I do use a lot of views, functions and triggers), I spend more time in my database client than in my IDE.
I think ActiveRecord beats this, since the code ends up more readable, but you can still run it in the rails repl, and the generated SQL syntax is printed in both the webserver logs and the repl output.
> ActiveRecord beats this, since the code ends up more readable
It's subjective and I tend to disagree. Especially for very simple and very complex queries.
Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM, you have to redefine your tables, columns and relationships a second time and keep them up-to-date with every change, which is a huge hassle.
Obviously if your app is a simple CRUD app, might be simpler to just use Rails/Django/Symfony with an ORM and embrace the code-first approach.
Rails has very robust and well documented generators if you find it's a lot of work to create the migration file and the model file when you're changing the schema (I usually just create them manually though). It's a pretty standard workflow for rails developers, and given that rails is used by a lot of companies that are not "simple CRUD apps" (like AirBnB, Stripe, etc.) I'd say it's definitely very useful in many scenarios.
In fact, I'd argue that the "code-first" approach as you call it is actually more useful, because rails gives you bindings for before/after commit hooks, validators that aren't supported by SQL, etc.
> Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM
I've literally never heard of a rails team migrating their DBs manually. Everyone uses ActiveRecord because it's a joy to use and very well supported and documented.
As many SQL Injection issues (and various other injection forms, including Javascript) as we have with backend code, fuck no we don't want SQL being issued from the frontend.
For one, permissions around SQL are already crap. It takes the smallest screwup to expose data in co-mingled databases. No need to make it even worse.
For two, at least if the SQL is on the backend, a fix for an exponential query DDOSing your DB is fairly quick; you don't have to worry about some client keeping a cached copy of the frontend around for months at a time.
Finally, if you let the frontend send SQL, you have lost any and all ability to do a static audit against the queries that will be run against your database - because you have no control over what the client does.
It feels like you and the parent commenters are framing this as a choice between allowing public access to existing databases, or continuing to build new tools for accessing the data.
The way I understood the article, the ideas is 'can we expose a safe subset of SQL or fix the security issues instead of continuing to build entirely new systems'
Here's the root of my concern - safely and securely emitting SQL from an untrusted client is exponentially harder than running canned SQL queries from a trusted client.
People forget (or don't care) about security or safety when it lets them move faster.
"Move fast and break things," is a terrible security model.
I think you would be surprised how quickly and easily an attacker can automatically export vulnerabilities in web applications with programs such as Ffuf and SQLmap.
Not disagreeing. I'm just saying people on the front end never actually deal with security, hence this is the mentality that is the instigator of the suggestion of using SQL on the front end.
- super hard to cache if any client can basically generate an infinite number of different queries.
- which dialect of SQL ? If you change DB or upgrade version, do your ask all clients to change as well ?
- how do you do query that involves several data sources ? Some response data come from redis + elastic search + postgres. That's why FB created graphql. That's also why it's so limited in scope, so that complexity is manageable.
- do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?
> do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?
No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.
Direct SQL from the client definitely isn't for everyone, but these problems aren't insurmountable.
Caching: This is true of any API allowing, for example, advanced search. If you can get by without it, great, but it's often a requirement.
Dialect: I don't think it's a terrible idea to bet on one (ideally stable and FLOSS) database, it's just a risk to be managed, and I think it can provide benefits in some cases. Or you could be fancy and use jOOQ's SQL dialect translator (this could also be used to create a "custom dialect" without some features).
Multiple data sources: You could use PostgreSQL's Foreign Data Wrappers. An unorthodox solution, but it allows seamless access with fast joins.
Complex SQL features: I don't think it's really a problem, in complex analytics code I think some users would actually appreciate having all these features. It can be a code quality challenge, but I think that's better solved on a personal level.
This approach does have real problems, like it being hard to prevent DoS attacks, but I don't think there are as many problems as it seems on first glance.
Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user. Something I (and most InfoSec professionals) wouldn't be keen on automating or managing, for fear of getting it wrong.
[EDIT] The following was removed from the parent post. It was in response to "you can't audit queries".
> No, you don’t
Citation needed. Yelling "No, you don't" without anything else is fscking useless in moving a conversation forward.
If the query is coming from the frontend, it's coming from a client that is outside of your control. I.E. you don't know what queries will come from the frontend, because the user can execute an arbitrary query.
If you take an effort to extract and bake those queries into a backend (perhaps calling it a proxy), then you're not actually emitting SQL from the front end, you're relying on a backend to emit the actual SQL used after performing a number of security checks. AKA, the status quo.
> It also requires you to have one database user per external user.
So?
You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other.
You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine.
I’m not sure why so many developers believe reinventing the wheel on database security is more effective than understanding their tools.
How many millions, or billions, of database users are you comfortable managing?
0 millions for me.
I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership.
The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain an InfoSec professional would have kittens if they saw that attempted in a production environment.
> The idea of using one DB user per end user is, at best, novel and untested
Its a technique older in continuous use with RDBMSs and more battle-tested than, say, the web itself, and plenty of enterprises (even the kind that have apps that not only don’t provided direct DB access to their frontend, but don’t even provide their backend access to tables or views but mediate all external access to the DB through sprocs) have it as a security norm and (correctly) view apps that manage end-user access outside of the database as taking a relatively novel, untested, and risky approach.
Looks like you have some knowledge hole. Let’s start with MSSQL you can use AD groups and those are the one and only thing that database setups are concerned with. On the AD side users get added/removed all the time. I have personally managed this kind of system with nearly “0” overhead on the DB side. Group assignment is the maintained by team managers decentralizing the whole thing.
For non Kerberos Linux systems using LDAP to synchronize group membership is the only extra automation needed.
It is possible and fairly easy to manage a very large number of accounts IF done properly.
In practice, you would use something like SPARQL (which is already a W3C standard) as opposed to literally feeding raw SQL to the backend database. And SPARQL is designed for reasonable complexity, while still being more flexible than some purely ad-hoc thing like GraphQL (which only really deals well with tree-like hierarchies of data, not anything more general).
Whether you're making a REST API, a graphql endpoint, or a SPARQL (or even plain old SQL), you're still going to end up creating a backend to parse, transform, and apply security policies to the incoming queries so they can then be run against the backend.
The complexity and security costs just keep going up the more logic you shove into an untrusted client.
In my practical experience having worked with SPARQL and GraphQL, I would very much prefer GraphQL.
SPARQL is really nice for querying graph data, but I'd argue that for most applications tree-like hierarchies are good enough. Also GraphQL and associated frontend libraries are written to be consumed by browsers and results are much easier to handle than results from a SPARQL query, JSON+LD is not the easiest format to handle. The tooling around SPARQL is not great compared to GraphQL either.
Furthermore I'd argue that SPARQL queries are really hard to statically analyze / optimize on the Backend. Different SPARQL engines behave differently and whether you run your query against e.g. Stardog or Virtuoso can have vast performance differences. SPARQL being hard to analyze statically actually becomes apparent once you start thinking about authorization of resources, it is not a trivial problem to know to which resources a client should have access to or not.
And while it is true that you can model provenance, authorization and all kind of other niceties in SPARQL as well, you will likely be on your own building all those tools yourself.
I believe SPARQL is a great query language for public / open data, e.g. Wiki Data, Government data, etc, but in a business context I'd rather choose REST or GraphQL over it.
I did write a go application that gives full querying capability to the clients for their own Sqlite3 instances, maybe I should open source it (Just wanted to see if there's any interest).
We are currently settling for something in between: EQL (using Pathom resolvers)
This gives front-end devs the flexibility of GraphQL, composability for free (EQL's DSL is just a data-structure you can manipulate easilty) and is database agnostic.
In fact, Pathom makes it easy to link different datasources and make them available in a uniform way to the front-end devs.
All the while, resolvers (running on the backend) keep control and prevent malicious clients from doing harm.
Same issues you would have with GraphQL though: N+1 queries need special consideration when writing resolvers.
183 comments
[ 3.6 ms ] story [ 272 ms ] threadhttps://caniuse.com/sql-storage
Supported in Chrome 4, circa January 2010.
I'm forgetting the name of it but there is a spec, I think in wicg, for low-level/byte-level file access. This should make using emscripten to compile things like sqlite far more straightforward, to build whatever you want.
I like the shout out to streaming changes out of the database, which the author mentions in terms of redux & time travel abilities from having a wal. Server side systems like Debezium for doing this have been gamechanging. At the moment, the high-level file api has no support for watching for changes (https://github.com/WICG/file-system-access/issues/72). Maybe post 1.0 we might see progress. I don't believe the low-level api has anything for watching for changes.
From a more meta-assesment level of this article: I do hope we can stick with HTTP centric entities, personally. GraphQL with it's generic endpoint that all operations get sent to is, in my view, quite a bad development. But not irreconcileably so: bridges could be built. I'm definitely in favor of experimentation, trying things out. But I also think there's good reasons to keep entities and their urls around, to not abandon that. GraphQL right now doesn't seem to think about that or care about that, but I also think it could be reformed.
https://softwareengineering.stackexchange.com/questions/2202...
>Byte-level storage
Would be cool.
---
https://hacks.mozilla.org/2010/06/beyond-html5-database-apis...
> From article: we think developer aesthetics are an important consideration...we were resolved that using strings representing SQL commands lacked the elegance of a “web native” JavaScript API, and started looking at alternatives.
Seems a bit short-sighted considering SQL is super popular today on the backend.
https://github.com/WICG/storage-foundation-api-explainer
And it’s true this works. If you only have to manage a non-shared todo list.
tl;dr: nope.
I guess what I would argue against in this post is that SQL is the right language for this. The post starts off with the assertion that we're just using SQL on the backend anyways - not so, in fact my company doesn't have a single SQL database.
But I think the overall point remains - if you want powerful clients you can't beat a query language.
Apache Calcite comes to mind: https://calcite.apache.org/
[1]: https://postgrest.org/en/stable/schema_structure.html#schema...
But the data model of a complex app is usually more than its sql database. So maybe something closer to gql with code gen for your database is more appropriate?
[0] - https://github.com/alexanderguy/pgress
This is the same approach Facebook uses for GraphQL. The only reason I gave up with this idea is that getting the developer experience right is hard work and it does introduce some very tight coupling!
The article is less about remotely executing SQL, but more about having a relational data model as a cache in the frontend.
However, when sending the query through to the backend on a cache miss, would be useful to only allow queries that have been extracted like so.
Its similar to how Apollo does query [caching][1].
I really like it!
[1]: https://www.apollographql.com/docs/apollo-server/performance...
Legacy codebases are hard to remove because it's difficult but once you turn every SQL call into an HTTP call... the remaining part is the logic which can be rewritten into something more modern.
http://alasql.org/
I think the read/write-through cache to a backend database is the missing piece...at least as far as I can see.
* Front end - Show the user what she/he wants in an intuitive manner.
* Back end - Handle business layer which can be scaled horizontally. Have databases that can be scaled.
Mixing it all up results in one huge mess where you cant extend code or change functionalities well.
This post: treat everything like a SQL database
What's important is that those two EXTREMELY important paradigms of data organization basically don't support each other without kludges galore.
Hierarchical vs relational
But, fundamentally I agree with the poster, what we want are powerful proven data models and powerful proven access languages (SQL for relational, for hierarchical, I'll just throw out XPath which is basically the ONLY good thing from the era of XML that I liked).
Graph could potentially be considered, but IMO it has never proven itself in the practical marketplace. In particular it has no proven and stabilized query language in wide use by people that aren't domain experts, unlike users of the filesystem (basically everyone with a computer) and (less universally) users of databases.
and all the security can of worms. Seriously, if you don't respect the benefit of the text layer in JSON then you need to go have a look at why .NET Remoting failed. You want loose coupling here.
Perhaps I'm missing the point of the article?
I get WHY you'd want to be able to throw in any random filter/sort to get the exact datapoints you are after, but that's both really hard to scale and often ends up being highly coupled to the underlying datastore.
I get it, requirements gathering and making a clean API is hard and time consuming. What's harder is not doing that and needing to eventually figure out "How on earth can I get constant response times or stop someone from sending an app crashing query all while supporting current usages".
The former isn't THAT hard to do. The latter is extraordinarily hard.
Get the former wrong and you fix it before it ever goes live. Get the latter wrong and you fix it only after you're massively pwned.
This is just a bad tradeoff.
That's where I've often run into the problem. When someone wants to make a monolith into a more microservice thing the hard part of carving out monolith usages takes meetings with consumers. Devs don't want a bunch of meetings and requirements gathering and a lot (at least in my org... :( ) would rather just write something cool and throw their hands up and say "We don't know how you'd use this, so we made you the boss".
But... it seems like if you're willing to expose your data model to the client, then it seems like some combination of code signing the SQL (with parameters left empty) along with the acceptable list of named parameters, e.g.:
Signed blob:
Such that the client is allowed to send this query, but they're not allowed to provide any parameters, the backend has to provide the userid. That seems workable to me.Likewise:
This would allow the frontend to provide an order ID, but not the user ID - so the user still can't look at other people's orders and if not logged in, it's an invalid query without a userid. And the client isn't allowed to change the "sql" or parameters sent or the signature is invalid.Edit: Someone else replied that if you have sufficiently advanced build tooling, you can statically extract the SQL/GraphQL from the client side code and replace them with IDs, and then move the SQL to the backend. That's very cool, but the average startup isn't using bazel/buck/etc. Those tools aren't yet small app friendly enough (which is unfortunate, IMO.)
The middleware doesn’t do anything except populate query parameters and do a bit of sanity checking.
But I haven't actually seen a codebase that does this, so maybe I'm wrong. Have you used this approach in a project with more than a few developers and a large database? How did it go?
Another way to look at it is that changing the database schema is already a problem for the API which has to talk to it, so you're not really saving yourself any work by putting an extra layer between the frontend and the database.
In fact, if anything, you're doubling the amount of work, because there are now two interfaces (DB to API, and API to frontend) whose backwards compatibility you have to worry about.
If you have 100 other teams each with 5+ developers consuming your API -- or especially if your API is a product -- then keeping the API to frontend interface relatively stable with rare breaking changes is much more valuable than saving yourself even a major time commitment on DB to API work.
On the other hand, if you have relatively few clients -- or if the clients are "lower status"/"cheaper" teams -- saving time on the DB to API work a the expense of client teams might make sense.
It might actually be quite easy to version an SQL api, because you have the DDL migrations files and could actually make things backwards compatible to some degree. You would have a translation layer to modify the queries. Of course, your migrations would need to be more detailed tracking how things map to each other. But probably it would get too complex in the end. But not that much different to changes to a REST api really.
But, yeah, this happens with REST APIs too.
That said, I guess you could just add row / column ACLs to the database user, and then map the database user to the app user.
Server sends the query so server can send the signature hash with it, so the server signs the query with the server's key and sends both query and signature.
If the server ever receives a query without a signature or with a signature that is incorrect it can ignore or generate an error.
Yeh, that's the best idea. A babel plugin could do it easily. I already have have watcher script calling `graphql-codegen` to add types to my graphql queries, which works fine in the workflow.
Or...you could just expose a schema with views and sprocs where their definitions use only the SQL you are comfortable with against the base tables, which live off in their own schema isolated from external users. Same effect, but doesn’t require a whole new custom layer on top to reimplement what has been a basic feature of RDBMS engines for decades.
We’ve taken this approach for implementing database queries in Lowdefy [0] (SQL support will be live in next version, implementation using knex [1]. Also a “shared state” between backend and frontend makes parsing paramaters to queries on the backend really seamless and in return a great dev experience. Also allows to to only parse some parameters like secrets only on the backend.
What we are experiencing with Lowdefy apps is that bringing the data model closer to the UI makes coding and UI maintainability of simple projects very easy. This approach works exceptional for us in the BI reporting space where you primarily aggregate data.
However as soon as you move out of the CRUD space to more complex backend logic, extracting the logic to a single interface again simplified the implementation a lot, in fact apps very quickly becomes hard to maintain in my experience.
This does not mean that there is no place for easy CRUD dev tools like OP, but I do believe that an API like solution is required when life gets more complex or even just transactional. But there is also more creative ways to solve this problem.
[0] - https://github.com/lowdefy/lowdefy [1] - https://github.com/knex/knex
That sync protocol could be anything (rest, rpc, graphql, or even xml changesets in zip files on webDAV - I’m looking at you OmniFocus)
This setup has the benefits of being able to work offline (just sync later when network is back) and the ability to perform local SQL queries to populate UI views. But comes with all the extra complexity related to synchronising local and remote database. Stuff like handling conflicts.
> I usually end up with a bunch of Lodash (groupBy, filter, map, reduce) to shape the data I get from the server.
I mean, we are also doing the same thing with ORMs on the backend.
I sure do. Nothing beats being able to just copy/paste a SQL query from a code base to your client to see what's going on. Then tweak that said query until it works as expected. When I do backend development involving the database a lot (and I do use a lot of views, functions and triggers), I spend more time in my database client than in my IDE.
It's subjective and I tend to disagree. Especially for very simple and very complex queries.
Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM, you have to redefine your tables, columns and relationships a second time and keep them up-to-date with every change, which is a huge hassle.
Obviously if your app is a simple CRUD app, might be simpler to just use Rails/Django/Symfony with an ORM and embrace the code-first approach.
In fact, I'd argue that the "code-first" approach as you call it is actually more useful, because rails gives you bindings for before/after commit hooks, validators that aren't supported by SQL, etc.
> Also, unless you are a following a "code-first" approach and doing all your schema migrations through the ORM
I've literally never heard of a rails team migrating their DBs manually. Everyone uses ActiveRecord because it's a joy to use and very well supported and documented.
For one, permissions around SQL are already crap. It takes the smallest screwup to expose data in co-mingled databases. No need to make it even worse.
For two, at least if the SQL is on the backend, a fix for an exponential query DDOSing your DB is fairly quick; you don't have to worry about some client keeping a cached copy of the frontend around for months at a time.
Finally, if you let the frontend send SQL, you have lost any and all ability to do a static audit against the queries that will be run against your database - because you have no control over what the client does.
The way I understood the article, the ideas is 'can we expose a safe subset of SQL or fix the security issues instead of continuing to build entirely new systems'
People forget (or don't care) about security or safety when it lets them move faster.
"Move fast and break things," is a terrible security model.
- super hard to cache if any client can basically generate an infinite number of different queries.
- which dialect of SQL ? If you change DB or upgrade version, do your ask all clients to change as well ?
- how do you do query that involves several data sources ? Some response data come from redis + elastic search + postgres. That's why FB created graphql. That's also why it's so limited in scope, so that complexity is manageable.
- do you want to expose clients to implementation details such as OUTER JOIN vs INNER JOIN vs an array ? Or let them figure out tricky queries involving HAVING, DISTINCT, COUNT or subqueries ?
No, you implement an exposed schema for each app (or possibly more granular) that consists of objects (mostly views, possibly sprocs) optimized for the planned access pattern of the app (or whatever client/component thr schema is for), including convenience views abstracting those things away to the extent appropriate for the use case. That’s been a widely known RDBMS best practice for decades.
Caching: This is true of any API allowing, for example, advanced search. If you can get by without it, great, but it's often a requirement.
Dialect: I don't think it's a terrible idea to bet on one (ideally stable and FLOSS) database, it's just a risk to be managed, and I think it can provide benefits in some cases. Or you could be fancy and use jOOQ's SQL dialect translator (this could also be used to create a "custom dialect" without some features).
Multiple data sources: You could use PostgreSQL's Foreign Data Wrappers. An unorthodox solution, but it allows seamless access with fast joins.
Complex SQL features: I don't think it's really a problem, in complex analytics code I think some users would actually appreciate having all these features. It can be a code quality challenge, but I think that's better solved on a personal level.
This approach does have real problems, like it being hard to prevent DoS attacks, but I don't think there are as many problems as it seems on first glance.
No, they aren’t. At least not Postgres, and AFAIK its true of every other major RDBMS, too.
Non-DB-specialists typical level of knowledge of DB permissions may be, but that’s a whole different problem.
Yes, Row Based Security exists. But not by default. It also requires you to have one database user per external user. Something I (and most InfoSec professionals) wouldn't be keen on automating or managing, for fear of getting it wrong.
[EDIT] The following was removed from the parent post. It was in response to "you can't audit queries".
> No, you don’t
Citation needed. Yelling "No, you don't" without anything else is fscking useless in moving a conversation forward.
If the query is coming from the frontend, it's coming from a client that is outside of your control. I.E. you don't know what queries will come from the frontend, because the user can execute an arbitrary query.
If you take an effort to extract and bake those queries into a backend (perhaps calling it a proxy), then you're not actually emitting SQL from the front end, you're relying on a backend to emit the actual SQL used after performing a number of security checks. AKA, the status quo.
So?
You are adding a database entry per external user, along with data identifying their roles/permissions in the app, one way or the other.
You can either use custom code you’ve built on top of the DB engine to apply this, or you can use the far more battle-tested code in the database engine.
I’m not sure why so many developers believe reinventing the wheel on database security is more effective than understanding their tools.
0 millions for me.
I'll stick with the well tested and explored method of having a users table with foreign keys on the ID to other tables to identify data ownership.
The idea of using one DB user per end user is, at best, novel and untested. We don't know where or how it will fail. Scaling would be a real bear too. And I'm fairly certain an InfoSec professional would have kittens if they saw that attempted in a production environment.
Its a technique older in continuous use with RDBMSs and more battle-tested than, say, the web itself, and plenty of enterprises (even the kind that have apps that not only don’t provided direct DB access to their frontend, but don’t even provide their backend access to tables or views but mediate all external access to the DB through sprocs) have it as a security norm and (correctly) view apps that manage end-user access outside of the database as taking a relatively novel, untested, and risky approach.
For non Kerberos Linux systems using LDAP to synchronize group membership is the only extra automation needed.
It is possible and fairly easy to manage a very large number of accounts IF done properly.
The complexity and security costs just keep going up the more logic you shove into an untrusted client.
SPARQL is really nice for querying graph data, but I'd argue that for most applications tree-like hierarchies are good enough. Also GraphQL and associated frontend libraries are written to be consumed by browsers and results are much easier to handle than results from a SPARQL query, JSON+LD is not the easiest format to handle. The tooling around SPARQL is not great compared to GraphQL either.
Furthermore I'd argue that SPARQL queries are really hard to statically analyze / optimize on the Backend. Different SPARQL engines behave differently and whether you run your query against e.g. Stardog or Virtuoso can have vast performance differences. SPARQL being hard to analyze statically actually becomes apparent once you start thinking about authorization of resources, it is not a trivial problem to know to which resources a client should have access to or not.
And while it is true that you can model provenance, authorization and all kind of other niceties in SPARQL as well, you will likely be on your own building all those tools yourself.
I believe SPARQL is a great query language for public / open data, e.g. Wiki Data, Government data, etc, but in a business context I'd rather choose REST or GraphQL over it.
This gives front-end devs the flexibility of GraphQL, composability for free (EQL's DSL is just a data-structure you can manipulate easilty) and is database agnostic.
In fact, Pathom makes it easy to link different datasources and make them available in a uniform way to the front-end devs. All the while, resolvers (running on the backend) keep control and prevent malicious clients from doing harm.
Same issues you would have with GraphQL though: N+1 queries need special consideration when writing resolvers.