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

Against SQL

scattered-thoughts.net

Post reply on HN
Print view View original

81–90 of 354 posts

Re: Against SQL

#81

Post by thom »

faho wrote: post #51
benjiweber wrote: post #34

> By far the most common case for joins is following foreign keys. SQL has no special syntax for this You can use NATURAL JOIN select * from foo natural join bar Works as long as the keys are named the same. However, a lot of people have a habit of naming keys differently in the two tables.

And it also breaks if two non-key columns are named the same. This makes naming key columns differently a defence technique, so you stop people from using natural joins.

I generally use a prefix for columns based on the relation name, but preserving the name of keys (so a foreign key to user_id is user_id, not order_user_id etc). You obviously can't use natural joins if you end up with _multiple_ foreign keys with different roles, but generally I find this a better way to live all round. Never having to rename five 'name' columns in some output to make it clear which is which etc.

Re: Against SQL

#82

Post by progre »

kinjba11 wrote: post #46
progre wrote: post #22

Earlier quoted context omitted.

While some of their complaints are legit I think most of the SQL in this article are straw men. Most of it can be made more readable and more performant.

Care to back up those claim with evidence? The SQL queries are toy examples. Experienced SQL users don't write SQL like in the article because they know SQL's pitfalls and avoid them. It doesn't mean SQL isn't full of pitfalls and bad decisions (like many very old programming languages still in use).

Evidence? This is just, like, my opinion, man. But nested subqueries and old style joins... ripe for rewrite if I had say.

Re: Against SQL

#83

Post by exmadscientist »

_the_inflator wrote: post #54

I feel the pain. As someone who only uses SQL a couple of times a year, I feel that SQL shares the same fate as everything in IT: invented almost 50 years ago, not with today's world in mind, it has been blown up somewhat. Reminds me a bit of JavaScript: everything that can be done in JavaScript, will be done in JavaScript. Like after C followed C++ and here Java and others there will be new DSL and techniques on top…

I think the biggest difference between JavaScript and SQL is that there are things that SQL is actually extremely good at. For certain tasks, it really is the best language available, and not in the "least bad" sense but the "why would anyone even try to do this any other way?" sense.

To get that level of applicability, of course, you have to make your problem match the form SQL needs. For applications on its home turf, for example a simple inventory system, this can be both easy to do and beneficial (since if you're on SQL's home turf and you want to do something you can't do, there's probably a Very Good Reason). Unfortunately, this is not always easy to do, or even possible at all, and even when it is possible you usually need to know some basic relational algebra. (Tangentially, I am convinced that many of SQL's critics would be quieter if they knew a bit of relational algebra themselves, though I don't think that applies to this article.)

As you say, though, trying to make a tool do something it just shouldn't is the road to madness. The article's discussion of JSON in SQL is a pretty decent indicator of how that goes wrong even when it goes right. For further snapshots of the road to madness, the interested reader might examine C++, JavaScript, and a competent psychiatrist. Sometimes it really is time to move on, or at least add on.

Re: Against SQL

#84

Post by croes »

cm2187 wrote: post #23
saurik wrote: post #16

Earlier quoted context omitted.

You don't have to do all of those things though? Just create table as select (or sometimes select into).

Thanks. I didn't know this syntax. You still have to drop it in the end but somehow I never ran into this syntax.

You don't have to drop temp tables unless you want to use a second select into in the same session. Otherwise temp tables are automatically dropped if the session ends.

Re: Against SQL

#85

Post by tome »

The only way out that I can see is to design embedded domain specific languages (EDSLs) that inherit the expressiveness, composability and type safety from the host language. That's what Opaleye and Rel8 (Postgres EDSLs for Haskell do. Haskell is particularly good for this. The query language can be just a monad and therefore users can carry all of their knowledge of monadic programming to writing database queries.

This approach doesn't resolve all of the author's complaints but it does solve many.

Disclaimer: I'm the author of Opaleye. Rel8 is built on Opaleye. Other relational query EDSLs are available.

[1] https://github.com/tomjaguarpaw/haskell-opaleye/ [2] https://github.com/circuithub/rel8/

Re: Against SQL

#86

Post by wruza »

kinjba11 wrote: post #39
ltbarcly3 wrote: post #7

Lots of the examples here are yhe author writing very poor, non idiomatic SQL and then criticizing it. I could write a point by point rebuttal but I'll just pick one point, compressibility: VIEWs.

I've been around quite a bit of SQL, and views are great. But they're not exactly as easy as assigning to a variable. If I followed your logic per compressibility I'd have to be creating views - permanent global objects - for almost every query I write. Do you create views every time you need to write some ad-hoc SQL?

And much worse problem is naming them correctly. And maintaining in such global-only space.

But just give up, you hit the wall. Many people tried for decades to make this argument, but every time it was raised you’d see this thread full of people who see no problems. There is a lot of powerful, unrepeatable-in-your-lifetime software behind stupidest frontends that you can’t bypass, because most people write only straightforward code with no need for any abstractions beyond what was given to them.

Re: Against SQL

#87

Post by bccdee »

thom wrote: post #78
valenterry wrote: post #61

Earlier quoted context omitted.

But the world just works like that. There are unions everywhere. No matter how it's implemented under the hood, it should really be a first class concept in any language, including SQL.

In most languages where unions are a first class concept, are they not generally warned against?

Not at all. Untagged unions, sure, but no decent modern language has first-class support for untagged unions. First-class tagged unions are a real treat to use.

Re: Against SQL

#88

Post by zbuf »

cm2187 wrote: post #23
saurik wrote: post #16

Earlier quoted context omitted.

You don't have to do all of those things though? Just create table as select (or sometimes select into).

Thanks. I didn't know this syntax. You still have to drop it in the end but somehow I never ran into this syntax.

Even better, look at WITH, or CREATE TEMPORARY VIEW AS. Then the planner can make optimisations to the overall query. That is assuming you use your "temporary" only once.

Re: Against SQL

#89

Post by thom »

hugodrax157 wrote: post #63
bvrmn wrote: post #13

> fk_join(foo, 'bar_id', bar, 'quux_id', quux) This example has same amount of semantic entities as in SQL. Also there is USING. Also why author needs a strict modeling over json when one can model in native types? It's a very strange article.

It's also not obvious to me what has been gained from introducing a function which takes 5 unnamed arguments. Without looking at some separate (and therefore possibly wrong) documentation there's no way to guess what it's doing. Writing the SQL may take a bit longer but reading and understanding it is far easier.

What other approaches to making SQL composable can we imagine? With functions it seems very simple to extract a WHERE clause into a predicate of some sort. I'm sure even if this exact syntax isn't preferable, being able to reuse join logic would be great.

That said, in the case of wanting to abstract out or reuse joins, just write a view, I guess. And I get a lot of mileage in Postgres from just writing functions to abstract out predicates, because it allows you to write things like `select * from order where order.is_complete` instead of `where is_complete(order)`

Re: Against SQL

#90

Post by croes »

roenxi wrote: post #20

One of the elephants in the room with SQL is that it is one of a small number of popular languages that doesn't use function(arg, arg, arg) It is strange that "SELECT a, b, c FROM schema.table" keeps any aura of respectability. That is legitimately outdated syntax, people don't write languages that way any more. It was a 70s era experiment and what was learned from that experiment is that the style has no upside and…

>When Ray and I were designing Sequel in 1974, we thought that the predominant use of the language would be for ad-hoc queries by planners and other professionals whose domain of expertise was not primarily data- base management. We wanted the language to be simple enough that ordinary people could ‘‘walk up and use it’’ with a minimum of training. https://ieeexplore.ieee.org/document/6359709

So they wanted it to be easy for non programmers, more natural language like, so functions and brackets are quite the opposite.

Post reply on HN