Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Don’t we all just want to use SQL on the front end?

vjpr.medium.com

Post reply on HN
Print view View original

51–60 of 184 posts

Re: Don’t we all just want to use SQL on the front end?

#51

Post by vaughan »

AaronFriel wrote: post #27

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 =…

> you can statically extract the SQL/GraphQL

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.

Re: Don’t we all just want to use SQL on the front end?

#52

Post by holtalanm »

swlkr wrote: post #47

If you have a brand new SaaS app where there is very little data (kilobytes to megabytes) and each user/team is separated from the others, you could have: - a sqlite database for each user on the backend - then on page load, have them download their whole sqlite db on the frontend - sync the two with something like litestream.io compiled for webassembly

i think the use-cases for that are pretty few and far between, at least at the 'team' level. If you have a team, you need some way to manage members, which means adding/removing members. with this model, you effectively would not be able to remove members because you wouldnt know which members had downloaded an offline copy of the team's database.

for individual users, this might have a lot more potential.

Re: Don’t we all just want to use SQL on the front end?

#53

Post by dane-pgp »

throwawaygh wrote: post #39
AaronFriel wrote: post #27

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 =…

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 yo…

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

Re: Don’t we all just want to use SQL on the front end?

#54

Post by holtalanm »

jayd16 wrote: post #49
tgv wrote: post #42

I'm in the "no" camp. First, I don't want to couple my data model to the front-end. Second, the front end applications I worked on, have a relatively small contact surface with the database. Mostly, they retrieve a bunch of records with a common property. There's no need to expose the full power of a query language. Third, the thought of uncontrolled, expensive queries should make every sysadmin break out in sweat. I…

No one said it had to be coupled. You could, in theory, expose your API as a view of some kind.

isnt that pretty much.....GraphQL?

Re: Don’t we all just want to use SQL on the front end?

#57

Post by vaughan »

throwawaygh wrote: post #39
AaronFriel wrote: post #27

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 =…

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 yo…

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

Re: Don’t we all just want to use SQL on the front end?

#58

Post by dvdkon »

I'm currently working on a project to fully expose SQL to clients in some reasonable fashion. It's not going to be a great fit for every application, obviously, but just allowing users to filter any data any way they want by default is nice. For example, in many apps reports have to be baked into the API and/or running reports basically gives people admin-level database access, with SQL access they can just be HTML/JS scripts.

Combined with row-level permissions (which my library abstracts), I think it's a very powerful and usable approach to apps where storing structured data is the main goal.

Article for an older iteration: https://dvdkon.gitlab.io/mocasys-dascore/

Re: Don’t we all just want to use SQL on the front end?

#59

Post by jayd16 »

AaronFriel wrote: post #27

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 =…

I don't think you would use SQL directly. You do want something like GraphQL or FirebaseDB where the language is designed around untrusted users.
Post reply on HN