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

211–220 of 354 posts

Re: Against SQL

#211

Post by christophilus »

thinkr42 wrote: post #200

Though verbose and somewhat strange at times, one thing I love about SQL is that the query statements read like a set definition from set theory. That declarative nature is pretty powerful IMO, sure there are hiccups but it is a different way of thinking.

I agree. I also agree with the post. I’m a big fan of sql and write a lot of it by hand. TFA makes a lot of excellent points to which I could add quite a few more.

If we ever do get a replacement, I hope it retains the declarative set theory approach of SQL while addressing the warts.

Re: Against SQL

#212

Post by ethbr0 »

tremon wrote: post #199
spicybright wrote: post #182

Earlier quoted context omitted.

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

.Net's LINQ comes close, at least for querying. Update statements using LINQ usually translate to a select query followed by an in-language loop, but that's probably more an Entity Framework limitation than of LINQ itself. The problem with most abstractions on top of SQL is that the first thing they abstract away is the relational model; you end up with an opaque result set, a mapped object, or an untyped collection.…

SQL abstractions are stuck with a market problem, for reasons in this thread. Everyone who wants better-than-SQL is generally already adept at SQL relational gymnastics. And everyone who isn't adept at SQL wants to be able to ignore relational thinking.

End result? Abstractions target the easier of the two markets: people who don't want to learn relational modeling.

Re: Against SQL

#213

Post by laszlokorte »

laszlokorte wrote: post #174
goto11 wrote: post #102

Earlier quoted context omitted.

You can already do that by having circle and rectangle relations. Union types dos not give you any additional power compared to relations. Your proposal might be more convenient though than creating multiple relations, so we should look into making it just as convenient to create the necessary relations to express this. So lets say your syntax proposal creates multiple relations under the hood - then I'm all aboard!…

But if I have a circle relation and a triangle relation, how do I create a foreign key for the favourite_shape column in my user relation?

Thinking a bit more about it I think this would be a very elegant solution for Tagged-Unions in SQL:

- simply allow tagged unions for foreign key constraints: CONSTRAINT my_fk FOREIGN KEY (own_column) REFERENCES EITHER table_a (a_id) OR table_b (b_id) OR table_c (c_id)

- add join syntax to join via defined foreign key: FROM own_table JOIN VIA my_fk

The union FK would store an additional flag determining the target table and the JOIN VIA would switch on that flag and it would be allowed to use any columns from all target tables in the query but only the matching ones would be not null.

Re: Against SQL

#214

Post by le-mark »

xarope wrote: post #198
spicybright wrote: post #182

Earlier quoted context omitted.

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

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

Re: Against SQL

#215

Post by smitty1e »

masklinn wrote: post #172
smitty1e wrote: post #157

> First, while SQL allows user-defined types, it doesn't have any concept of a union type. Isn't a union type essentially a de-normalized field? This seems like attacking arithmetic operators for their lousy character string support. Weren't XML databases (briefly) a (marketing) thing some decades back? One idea might be to have everyone integrate jq[1] into their database engines. My understanding is that one can ma…

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

Re: Against SQL

#217

Post by LAC-Tech »

fauigerzigerk wrote: post #191
zozbot234 wrote: post #144

Earlier quoted context omitted.

The author seems to think that putting structured data in columns is a good idea. That is pretty clearly contrary to the basics of the relational model itself, never mind SQL. In fact it's quite close to how document databases work, so a very NoSQLish proposal overall.

>That is pretty clearly contrary to the basics of the relational model itself The consensus among relational theorists appears to be that data types can be arbitrarily complex. For instance, C.J Date and Hugh Darwen write the following ([1] page 56): Third, we remind you that types are not limited to simple things like integers. Indeed, we saw in Chapter 1 that values and variables can be arbitrarily complex—and that…

Alright, JSON in RDBMS is now canon!

Re: Against SQL

#218

Post by nojvek »

I agree with the Author. SQL is not a great query language. Almost every decently sized app I have written I have needed some sort of a query compiler so I don’t have to deal with nuances.

Also agree that GraphQL is a pretty fantastic language for working with graphs. And that relational databases are essentially graphs. Hasura is neat.

Re: Against SQL

#220

Post by jiggawatts »

spicybright wrote: post #182
barrkel wrote: post #121

Earlier quoted context omitted.

I think I'm experienced enough to understand the article, and I agree. I've written multiple optimizing SQL generators (altering generated SQL to access better plans), and rewritten hundreds of queries for better performance, which involves trying many semantically identical rewrites of the same query. I agree with Jamie. I think SQL is irritatingly non-composable, many operations require gymnastics to express, and I…

What is your opinion on abstractions on top of SQL queries? On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

> On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.

That's precisely because of some the flaws of SQL outlined in the article.

Generating SQL is complex. Generating portable SQL is impossible.

Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene how complex the queries can get!

I've tried to write some SQL generators before, of various types. I always got bogged down in the ludicrous complexity.

The author of the article makes so many good points that it's easy to gloss over entire categories of mistakes in the SQL spec. For example, he briefly mentions that getting a column schema back in query is weird and non-standard. The real problem here is that SQL is not homoiconic: tables, columns, and their types could have been represented as tables, including when defining them. E.g.: instead of "CREATE TABLE" the syntax should have been more like "INSERT INTO sys.tables ... ". (Similarly for columns, constraints, foreign keys, etc...)

Instead, we have a language designed for data that until recently had implementation-defined read-only system views for the schema data! Writing into that data uses an entirely different set of key words and syntax, which is "not really SQL" in the sense that it isn't relational and cannot use tables as input unless coded in an external language like Java, or built up using string manipulation an called via some sort of non-standard "exec string". Of course, then you have to worry about things like not having a built-in standard escape/unescape function! (QUOTENAME is the MS SQL version of this, surely non-standard).

Post reply on HN