Against SQL • Hacker News Board

Re: Against SQL

#221

Post by Seb-C » Sat, Jul 10, 2021, 1:02 PM UTC

JoelJacobson wrote: post #210

I suggest using the fact foreign keys are constraints with unique names, and using these names to explicitly specify what column(s) to join between the two foreign key tables. In PostgreSQL [2], foreign key contraint names only need to be unique per table, which allows using the foreign table "as is" as the constraint name, which allows for nice short names. In other databases, the names will just need to be a little…

This is interesting, I did not know this syntax.

Alternatively there are still the NATURAL JOIN and USING syntaxes that have been standard like forever.

Re: Against SQL

#222

Post by JoelJacobson » Sat, Jul 10, 2021, 1:07 PM UTC

Seb-C wrote: post #221

JoelJacobson wrote: post #210

I suggest using the fact foreign keys are constraints with unique names, and using these names to explicitly specify what column(s) to join between the two foreign key tables. In PostgreSQL [2], foreign key contraint names only need to be unique per table, which allows using the foreign table "as is" as the constraint name, which allows for nice short names. In other databases, the names will just need to be a little…

This is interesting, I did not know this syntax. Alternatively there are still the NATURAL JOIN and USING syntaxes that have been standard like forever.

I should clarify this syntax is only an idea, it's not implemented yet in any vendor nor part of the SQL standard, yet.

I think it would be a nice feature to add to the SQL standard.

Re: Against SQL

#223

Post by masklinn » Sat, Jul 10, 2021, 1:11 PM UTC

smitty1e wrote: post #215

masklinn wrote: post #172

Earlier quoted context omitted.

> Isn't a union type essentially a de-normalized field? No? You have to denormalize to emulate unions when they're missing. Sum types are a fundamental category of types, that SQL only supports product types is a problem you have to work around.

See mannykannot's reply. The argument for union types seems to get weak when one asks: how do we index their components? Because there seems little middle ground between needing discrete fields and safely just parking the data as a memo field and deferring the management to the application. Unix win by letting the system utilities specialize. SQL need not be "one language to rule them all".

> See mannykannot's reply.

Their answer clearly, explicitly, assumes a C-style `union`. Despite the essay literally using a proper sum type as example.

> The argument for union types seems to get weak when one asks: how do we index their components?

With the system creating partial indices under the cover? I fail to see what's complicated about it.

> SQL need not be "one language to rule them all".

SQL has "won" the relational battle and is literally the only way to query relational databases. Any time SQL is unable to do the job and you have to move that job to application code, you're making the schema less reliable and less of a source of truth, because parts of the schema's information have to be embedded in each application instead.

That doesn't seem desirable to me, unless you assert SQL should just be a trivial data storage and retrieval interface, which it has not been… possibly ever, but at the very least since the introduction of window functions.

Re: Against SQL

#224

Post by dorgo » Sat, Jul 10, 2021, 1:16 PM UTC

le-mark wrote: post #214

xarope wrote: post #198

Earlier quoted context omitted.

I'd agree with this. Despite hating databases whilst studying for my undergrad, I've come to realise after almost 30 years in the industry that expertise in SQL and database optimizations are key, and all the various frameworks I've tried to use to "hide" the complexity of SQL for my team, end up causing more headaches. e.g. when I use sqlalchemy, I end up writing queries using core than ORM, because it becomes simpl…

> versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do. ORM generally aren’t meant to totally replace sql, it’s an anti pattern. There was a quote by Gavin king explicitly calling this out; ORM for crud operations on object hierarchies, sql for everything else (reporting, adhoc queries etc). I try to educate this whenever I can but so many teams…

ORMs assume that they are the only access point to the database and cache result sets based on that. Disable the cache or keep in mind that bugs can happen due to that behaviour.

Re: Against SQL

#225

Post by glogla » Sat, Jul 10, 2021, 1:20 PM UTC

slx26 wrote: post #67

I think the problem of this essay is that it's overly technical: only those versed well enough in SQL will really care to read the whole thing, and if they are already at that level, either they accepted that "SQL will get the job done in the end", or they learned to live along it and now even kinda embrace it, and are happy to write about how the examples are very poor and dismiss the critique based on that, when th…

I think most of the criticism is about SQL not being the best programming language - a language in which pros build application, especially web applications. And SQL is truly not best at that. It is not the best API.

But in my world, SQL is much more of a Human Data Interface than Application Programming Interface. SQL is used for both ad-hoc and automated data querying and transformation. It is something manufacturing intelligence and supply chain experts and researchers and others learn to empower themselves to get to the data - and yes, you won't see any CEO running reports in SQL themselves, but it is not for programmers only.

Those people would not benefit from syntax with more brackets instead of words, easier way to define and call functions, or the ability to install libraries - in fact I think it would make it harder and less accessible for them.

The OP is right that for machine-to-machine interface, all the syntax baroqueness is not worth it. And of course, having more portability would be great.

But while machine-to-machine interfaces written by skilled developers who knows exactly what are they doing might actually be the most common use of SQL (since half the internet runs on Wordpress and MySQL and most smartphone apps might have SQLite in them), it is not where the majority of complexity or time is spent - that one is with the people working with data.

Re: Against SQL

#226

Post by le-mark » Sat, Jul 10, 2021, 1:22 PM UTC

dorgo wrote: post #224

le-mark wrote: post #214

Earlier quoted context omitted.

> versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do. ORM generally aren’t meant to totally replace sql, it’s an anti pattern. There was a quote by Gavin king explicitly calling this out; ORM for crud operations on object hierarchies, sql for everything else (reporting, adhoc queries etc). I try to educate this whenever I can but so many teams…

ORMs assume that they are the only access point to the database and cache result sets based on that. Disable the cache or keep in mind that bugs can happen due to that behaviour.

Caching is orthogonal to ORMs;

https://old.reddit.com/r/programming/comments/2cnw8x/what_or...

Re: Against SQL

#227

Post by blacktriangle » Sat, Jul 10, 2021, 1:35 PM UTC

ako wrote: post #131

slx26 wrote: post #67

I think the problem of this essay is that it's overly technical: only those versed well enough in SQL will really care to read the whole thing, and if they are already at that level, either they accepted that "SQL will get the job done in the end", or they learned to live along it and now even kinda embrace it, and are happy to write about how the examples are very poor and dismiss the critique based on that, when th…

There's a lot of talk about what is wrong with SQL, but i haven't seen something yet that is actually better than SQL for most use cases. Stop fighting SQL so much, and just focus on bringing a better solution. If potential users see it has significant benefits they'll start using it.

In theory datalog is better than SQL. However the reality is that we have millions of programmer-hours invested in making SQL perform well where datalog has no where near this effort.

I think that's why NoSQL is where the gains are being made. You can't magically improve things with a query language without putting the effort into performance, but you can get huge gains by changing your assumptions about how data is structured.

Re: Against SQL

#228

Post by dvdkon » Sat, Jul 10, 2021, 1:44 PM UTC

erezsh wrote: post #203

dvdkon wrote: post #194

Earlier quoted context omitted.

Thanks for the reply. I'm happy that others are also tackling the problem of a better query language. My approach isn't actually a full-on Lisp with parentheses and all, rather it's based on a single compound data structure (like Lisp's cons cell, but more like Lua tables). I call it an "arg-tuple", and it's basically a function's arguments in Python, but as a data type (which allows nesting). Add in a simple functio…

Python has a data structure like this called NamedTuple. I also noticed it's popping up everywhere, in this case sql rows (kind of). Conceptually I like the idea of using it as the mechanism for function arguments, but I didn't want the syntax to be too confusing for beginners. Although yours seems a little bit different, because different fields can have the same name. Just for the heck of it, here's how your second…

It's not quite that arg-tuples can contain multiple values under the same name, but rather ":a 1, :a 2" is actually an arg-tuple containing two nested arg-tuples as unnamed values. I agree that basing all of syntax on a single element (especially one that's so "light" on syntax), it's hard making the resulting syntax intuitive. I think it's worth it, but I can see other approaches' benefits.

Re: Against SQL

#229

Post by munchbunny » Sat, Jul 10, 2021, 1:57 PM UTC

DeusExMachina wrote: post #104

endymi0n wrote: post #99

Earlier quoted context omitted.

SQL is exactly like the QWERTY layout: A first quickshot with little design thoughts and unfixable architectural issues that‘s so widespread that everyone is used to it by now. Trying to change to the Dvorak layout taught me a lot about enacting change on such a grand scale. After a lot of hassle switching machines and OSes, typing on other user‘s computers, them typing on mine and general headaches among internation…

While I agree with your point, I wonder why that does not seem to be the case with programming languages. For example, in iOS development (and, more in general, on Apple platforms), there has been a huge shift from Objective-C to Swift. The same arguments should apply there. Swift is much better, but Objective-C got the work done, and many codebases were written in it, especially at Apple. And yet, the whole communit…

I don’t know if that’s really true these days. Each SQL server has its own dialect with nuances you have to pick up sooner or later, and if you’re a backend engineer for more than a couple years you’ve likely dealt with multiple types of data stores.

Re: Against SQL

#230

Post by IggleSniggle » Sat, Jul 10, 2021, 2:03 PM UTC

wibblewobble123 wrote: post #197

goto11 wrote: post #97

Earlier quoted context omitted.

SQL already have first class support for union types in the form of relations. Adding union-type columns would actually be second class compare to this. You would have to add special-case operators and it wouldn't give you more power compared to just using relations. That said, I'm not averse to the idea if someone can provide a realistic use case. The JSON example in the article is misguided though - you should not…

Relations don’t provide sum types in a first-class way. If you want a list of employees each of which have different roles, and depending on the role you have different fields guaranteed to be available (not null), this is not possible to express directly in SQL. You can express this with sum types in ML, Haskell or Rust. In SQL you would have to split it out into separate relations, like: create table employee(id it…

Yes! This! Exactly! There’s so much that _can be knowable_ with SQL based on the constraints of the _query_ (not just the relational model) that it should be possible to know the characteristics of the data (and in some cases even the performance characteristics) just from type characteristics.