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

101–110 of 184 posts

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

#101

Post by edgyquant »

commandlinefan wrote: post #25

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…

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.

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

#102

Post by cogman10 »

lostcolony wrote: post #69
cogman10 wrote: post #29

Earlier quoted context omitted.

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

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. This is just a bad tradeoff.

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

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

#104

Post by tmnstr85 »

This reminds me of a reddit comment from /r/programmer humor - someone was correlating music styles to programming languages. They decided MSSQL was the equivalent of a 6th grade recorder recital. More power to them I say!

I've been on the data side for a long time and have always wanted to explore front end but the API mandate sure did make that learning curve difficult, but for good reason.

It would be ideal if someone could iterate the existing frameworks to step something like GraphQL to a full blown front to back SQL tool.

Anyone who says SQL perms are crap doesn't know what they're doing with perms. I agree something is going to have to eloquently add controls between front and back.

Very thoughtful piece.

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

#105

Post by dragonwriter »

falcolas wrote: post #31

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…

> For one, permissions around SQL are already crap

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.

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

#106

Post by ufmace »

holtalanm wrote: post #48

There is PostgREST, which is just a thin wrapper REST api around a Postgres database: https://postgrest.org/en/stable/

I thought of this when I first read the article title, and I think it's a decent compromise that mostly addresses the concerns in the other comments. Supporting truly arbitrary SQL seems too high-risk to allow from the front-end. PostgREST supports a simple subset in a HTML-like language that's fairly unlikely to have any unpredictable/negative consequences. I think it has limits for query time and result size built-in already too. You do have to get used to setting up the correct security constraints in Postgres natively though.

Strangely enough, I hardly ever see anyone really use the Postgres security checks. Postgrest is pretty much the only use case I've ever heard of.

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

#107

Post by porsager »

smuemd wrote: post #103

With HashQL there actually was an experimental implementation of this idea https://github.com/porsager/HashQL-todos-sample/blob/master/...

Not only experimental. I've been running HashQL in production for over a year now. I really wish I had time to write about it. I'm going to present it at https://speakeasyjs.com/ on the 7th of May, so I'll have to get something ready until then at least.

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

#108

Post by dragonwriter »

BiteCode_dev wrote: post #96
falcolas wrote: post #31

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…

Not to mention: - 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…

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

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

#109

Post by droopyEyelids »

ipnon wrote: post #87
falcolas wrote: post #31

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…

It seems that the typical software engineer doesn't give security a single thought.

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'

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

#110

Post by zozbot234 »

falcolas wrote: post #31

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…

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).
Post reply on HN