Against SQL • Hacker News Board

Re: Against SQL

#261

Post by kthejoker2 » Sat, Jul 10, 2021, 3:08 PM UTC

mickael-kerjean wrote: post #165

pjmlp wrote: post #29

> Why did SQL have to add it to the language spec? Most likely because there isn't cargo for SQL, everyone has to make do with a default install offers, and most big boys databases offer FFI to Java, .NET and C. > This works for data modelling (although it's still clunky because you must try joins against each of the tables at every use site rather than just ask the value which table it refers to) Only if one never l…

After years of doing that same technique, in my new job, people would write: SELECT foo.id, quux.value FROM foo, quux, bar WHERE foo.bar_id = bar.id AND bar.quux_id = quux.id I couldn't find anyone telling me the difference between those 2 ways to write a query, do someone know more about this?

There's no performance difference in the engine.

The way you have written it (ANSI-89)used to be the only way joins could be written.

The second one (ANSI-92) was introduced to allow for composability since the entities being joined and the join condition are next to each other in the code and multiple joins can be generated one after the other.

IMO it also enhances developmemt quality of life since you can understand a new-to-you query faster (especially complex ones), you can just comment out a join in one line when testing replacement, cut and paste between queries easier, etc.

An SO question on the topic

https://stackoverflow.com/questions/334201/why-isnt-sql-ansi...

Re: Against SQL

#262

Post by blacktriangle » Sat, Jul 10, 2021, 3:15 PM UTC

neuronic wrote: post #231

blacktriangle wrote: post #227

Earlier quoted context omitted.

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…

Can you? For every article hating SQL there is one for hating NoSQL: http://www.sarahmei.com/blog/2013/11/11/why-you-should-never...

NoSQL is much bigger than Mongo, which deservers books written on hating it. The relational algebra is great, but its also overkill for things like event streams, message brokers, caches, does hit scalability issues for certain legitimately large workloads, and in comparison to Mongo and other document-based databases, fails when your data simply is not relational.

Then there's the whole object/relational impedance missmatch, which if you insist on using OOP, there's a variety of object databases that make a small persistance layer much easier.

Re: Against SQL

#263

Post by deergomoo » Sat, Jul 10, 2021, 3:17 PM UTC

goto11 wrote: post #111

kinjba11 wrote: post #28

Earlier quoted context omitted.

> maybe they are asking a bit much from SQL But this article is thought provoking to say the least. It follows the courtroom logic of holding the defendant SQL on trial for as much as possible. And SQL is guilty of a lot of crimes. I do hope GraphQL and similar query languages become more prevalent and standardized, as it seems SQL could really use some stiffer competition.

SQL is often conflated with the relational model (hence the term NoSQL for non-relational databases), but the article is careful to explain that the relational model is great, but SQL is a clunky syntax/standard. Going to graph databases is certainly throwing the baby out with the bathwater. The relational model was invented to address shortcomings in the hierarchical and graph database models.

Aren’t you just doing the same thing in reverse here? You can use GraphQL with a relational database.

Re: Against SQL

#264

Post by latte » Sat, Jul 10, 2021, 3:19 PM UTC

laurent123456 wrote: post #195

latte wrote: post #187

SQL and the relational model mostly works well and it's probably not practical to redo the enormous amount of work that was invested in SQL and its implementations and extensions. As someone who frequently used SQL for analytics and less frequently for app development, I would gladly use a language that would transparently translate to SQL while adding some syntactic niceties, like Coffeescript did to JS: - Join / su…

Isn't it what query builders, such as Knex.js, are for?

Basically it would be ideal to have a query builder that is easily integrated with shells and notebooks (so that it can be used outside the context of writing programs in a specific languages) and that is accepted across the community.

Re: Against SQL

#265

Post by kthejoker2 » Sat, Jul 10, 2021, 3:19 PM UTC

As someone from the analytics side who's been working with SQL for 30 years (First Choice, remember that?) (but who also wrote a fair share of ORM boilerplate), I find these debates fascinating .. but also kind of trivial, in the sense that SQL has a lot of other pros and cons that app devs rarely consider.

Truly it is blind men evaluating an elephant.

Given SQL's roots as a human-friendly declarative interface, the only thing I see completely replacing it in the near future is a Copilot-style neural implant where you just think of the results you want.

Re: Against SQL

#266

Post by twodave » Sat, Jul 10, 2021, 3:25 PM UTC

We have a general rule on our team that complex SQL is a code smell. In our project complex queries are usually an indication of a poor design.

Anything SQL that can be made simpler via dynamic generation (which is safe as long as you use proper parameters for user inputs) is favored over creating logical branches in queries. Anything that can be processed further quickly in memory in the app (mapping operations, string ops, ordering/filtering predictably small data sets, etc.) we tend to offload from SQL into something more suitable.

And we tend to solve a class of problem in our data layer and reuse those generalized patterns heavily. This makes our codebase predictable even when dealing with unfamiliar subject matter.

Of course there are always places where some complex query is necessary (especially when building reports), but if it’s status quo then you’re doing something wrong—-it’s only a matter of time until you end up with a performance nightmare on your hands.

Re: Against SQL

#267

Post by gumby » Sat, Jul 10, 2021, 3:26 PM UTC

SQL is a COBOL-era language — though there are 15 years between them, language theory was quite rudimentary at that time.

But it exists and is adequate. And, as Gabriel’s famous essay says, Worse is Better.

Re: Against SQL

#268

Post by dbsmith83 » Sat, Jul 10, 2021, 3:36 PM UTC

roenxi wrote: post #38

dbsmith83 wrote: post #26

Earlier quoted context omitted.

Comparing SQL to those other languages doesn't really make sense. Their purpose is different. For what SQL does, the syntax makes a lot of sense because it is a completely different paradigm. I think it's dismissive to refer to SQL as merely a 70s experiment. It is used so widely today still

What advantages do you think SELECT a, b, c FROM d has over even a trivial modernisation like, say, table(d) |> select(a, b, c) ?

readability, typeability

It makes formulating more complex queries easier when you can just say it out loud

Re: Against SQL

#269

Post by treis » Sat, Jul 10, 2021, 3:38 PM UTC

ethbr0 wrote: post #212

tremon wrote: post #199

Earlier quoted context omitted.

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

I think there's some low hanging fruit out there. Simple improvements to make queries easier to write and maintain.

As an example, ActiveRecord allows you to define scopes. If you have an account things like:

platinum: -> value >100000

churn_risk: -> churn>.95

You can then chain these together i.e Account.platinum.churn_risk to get all the platinum accounts at risk of churn.

Afaik there's nothing similar in SQL. If your definition of a platinum customer changes you have to change a bunch of different queries instead of one definition.

Re: Against SQL

#270

Post by bob1029 » Sat, Jul 10, 2021, 3:41 PM UTC

I feel like most frustrations with SQL boil down to fighting against a shitty schema.

When you are sitting in a properly normalized database, it is a lot easier to write joins and views such that you can compose higher order queries on top.

If you are doing any sort of self-joins or other recursive/case madness, the SQL itself is typically not the problem. Whoever sat down with the business experts on day 1 in that conference room probably got the relational model wrong and they are ultimately to blame for your suffering.

If you have an opportunity to start over on a schema, don't try to do it in the database the first few times. Build it in excel and kick it around with the stakeholders for a few weeks. Once 100% of the participants are comfortable and understand why things are structured (related) the way they are, you can then proceed with the prototype implementation.

Achieving 3NF or better is usually a fundamental requirement for ensuring any meaningfully-complex schema doesn't go off the rails over time.

Only after you get it correct (facts/types/relations) should you even think about what performance issues might arise from what you just modeled. Premature optimization is how you end up screwing yourself really badly 99% of the time. Model it correctly, then consider an optimization pass if performance cannot be reconciled with basic indexing or application-level batching/caching.