Against SQL • Hacker News Board

Re: Against SQL

#281

Post by karmakaze » Sat, Jul 10, 2021, 4:35 PM UTC

twodave wrote: post #266

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, str…

> complex queries are usually an indication of a poor design.

Can you give an illustrative example of one. I suspect that framing it this way biases designs away from 'poor ones that use complex queries' into one that foregoes other good aspects such as normalization. Sometimes the best design uses a complex query for something other than a report.

Design is not something that should be done by application of dogma and avoiding smells.

Re: Against SQL

#282

Post by samatman » Sat, Jul 10, 2021, 4:38 PM UTC

bob1029 wrote: post #270

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

The categories were made for man, not man for the categories.

A not-shitty schema is always to be preferred over a shitty one, to be sure. But for any data schema, there will be queries which cut against unexpected joints (joins?).

And SQL is bad for this. The entire Fine Article is a detailed exploration of how it's bad for this. A decent query language would keep the easy things easy (SQL is ok at this) and wouldn't make the hard things pointlessly difficult or impossible-except-at-the-application-layer.

Sometimes it's just a weird or one-off query, and sometimes there are so many of them that migrating to a better schema is indicated. Since it's difficult to do the dozen or so things the article sketches out, it's also difficult to iterate your way to a better schema arrangement.

The sort of waterfall database design you advocate (which, yes, think long and hard about your data!) always breaks down, because building business logic is a process of discovery. There are always missing pieces.

Re: Against SQL

#283

Post by smitty1e » Sat, Jul 10, 2021, 4:44 PM UTC

masklinn wrote: post #223

smitty1e wrote: post #215

Earlier quoted context omitted.

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…

I would contend that there is a minimum set of tools across which an system will span.

However, attempts to crunch the system into One True Tool will hit the diminishing returns curve early and hard.

Quite to the contrary, many users tend toward Object-Relational Mappers, just to ease the transition between data and logic portions of the application.

Re: Against SQL

#284

Post by jandrewrogers » Sat, Jul 10, 2021, 4:57 PM UTC

Another subtle issue with SQL is that it tacitly assumes a great deal about the internal architecture of the database engine implementing it. SQL is designed to be easy to implement for the way SQL databases worked in the 1990s. Unfortunately, modern high-end databases today have radically different internal architectures, are capable of much greater internal expressivity as a minimum, and are designed to support data models as first-class citizens that weren't even on the radar in the 1990s. Patching the first-class capabilities of modern database kernels into the SQL language, such as generalized recursion, can often be awkward or require non-standard syntax or behaviors that defeat easy optimization. The DDL has similar issues, particularly around its concept of what an "index" can look like under the hood or the myriad ways in which data can be organized.

I've used and even written SQL databases for much of my career. SQL is pretty satisfactory for what it was designed to do. I view SQL like classic inheritance-based OOP; it works well for the problem domains for which it was originally designed, but is poor for efficiently expressing problem domains that are better expressed in a composition-based or functional way. Yet it worked so well in its original domain that we try to apply it everywhere. The diversity of data models and the kinds of operations we want to do with them today is far greater than was considered when SQL crystallized into its current form.

The limitation of most nominal SQL replacements I've seen is that they commit the same sin of SQL originally: overfitting for a problem domain that the designer was most interested in. There is an appetite for a really good SQL replacement if done well, and in principle anything SQL can do could be directly translated into a new language for compatibility.

Re: Against SQL

#285

Post by wvenable » Sat, Jul 10, 2021, 4:57 PM UTC

Izkata wrote: post #108

The GROUP BY section is odd: > You can use as to name scalar values anywhere they appear. Except in a group by. -- can't name this value > select x2 from foo group by x+1 as x2; ERROR: syntax error at or near "as" LINE 1: select x2 from foo group by x+1 as x2; -- sprinkle some more select on it > select x2 from (select x+1 as x2 from foo) group by x2; ?column? ---------- (0 rows) Looking at that first one I'm just ki…

Sadly using the renamed column name in a group by or order by doesn't work on all database engines.

Re: Against SQL

#286

Post by vaughan » Sat, Jul 10, 2021, 5:03 PM UTC

rawoke083600 wrote: post #40

I've been thinking about this problem a lot, CRUDs, GraphQL,ORMs, Models etc. Mostly in the "CRUD-Like" environment. I have been thinking about a "client side SQL impl". In most CRUD's we currently have on the backend layers and layers of software with ORMS, frameworks etc, and it all boils down to "Writing/Generating the correct(good-enough) SQL" We now have added stuff like GraphQL, which if you squint hard enough…

Yeh, this is lacking right now. GQL makes joins much easier to write than SQL, which is what you want to be using in your components. But GQL is not great for offline-support and caching. You want your frontend to know about how your data relates to each other. Your frontend GQL should query a local SQL db.

I'm not sure you need a full SQL implementation on the frontend though, as the data is not going to get all that large to need the optimizations it affords, but it would be nice to be able to use the same queries on your browser DB as your backend DB.

Re: Against SQL

#287

Post by goto11 » Sat, Jul 10, 2021, 5:17 PM UTC

mcv wrote: post #277

goto11 wrote: post #111

Earlier quoted context omitted.

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.

Do graph databases predate relational databases? Do you have a source for that? I thought graph databases were a fairly new thing. I only recently started working with a graph database (a bit over two years now), and it struck me just how terrible relational databases are at relationships, compared to graph databases. I know nothing about the history of databases, but my impression is that relational DBs are basicall…

Sure, in the 1970 paper "A Relational Model of Data for Large Shared Data Banks" where E.F.Codd introduced the relational model, he specifically mention how it is superior to hierarchical and graph databases, which were the dominant models at the time.

The hierarchical database evolved from the flat file database by allowing nested records (i.e a record could have sets of child records, arbitrary deep). The graph model further added support for navigation links or pointers between records across the structure, hence supporting a graph model (also commonly called network model at the time).

Re: Against SQL

#288

Post by mamcx » Sat, Jul 10, 2021, 5:23 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…

> We do deserve better.

Totally! SQL is like shell, c and js that somehow generate this "never ever try to improve them for real, lets stay suffering for all eternity!"

And the AMOUNT of energy in mask them (transpilers, linters, "good practiques", tricks, etc) is a monumental waste of effort that cost so much than actually go and fix the core issues.

And in this case, SQL is the most trivial to fix. It could get simplified and streamlined far easier that is to replace C, for example.

And a lot of interactions to the DBs are using bridges already. Create a "good sql" is similar to create WASM and then for legacy reasons support in EOL fashion all the old sql and all the new go against the new way.

But this mean at least 2 or 3 major database vendors go for it (I dream: sqlite & postgresql).

P.D: I'm exploring building a relational language, so already have a simplified dialect for it: https://tablam.org/tutorial

Re: Against SQL

#289

Post by [deleted] » Sat, Jul 10, 2021, 5:31 PM UTC

wvenable wrote: post #285

Izkata wrote: post #108

The GROUP BY section is odd: > You can use as to name scalar values anywhere they appear. Except in a group by. -- can't name this value > select x2 from foo group by x+1 as x2; ERROR: syntax error at or near "as" LINE 1: select x2 from foo group by x+1 as x2; -- sprinkle some more select on it > select x2 from (select x+1 as x2 from foo) group by x2; ?column? ---------- (0 rows) Looking at that first one I'm just ki…

Sadly using the renamed column name in a group by or order by doesn't work on all database engines.

[deleted]

Re: Against SQL

#290

Post by culebron21 » Sat, Jul 10, 2021, 5:34 PM UTC

erezsh wrote: post #36

I share the author's point of view, which led me to start a new relational programming language that compiles to SQL. It's a way to build on existing databases, like postgres or mysql, with all of their advantages, but improve on many of SQL's limitations. If that sounds interesting, you can find it here: https://github.com/erezsh/Preql

I was sketching a syntax for python to work with pandas, and avoid extra annoying code, and surprisingly some of the elements and approaches I see in your code are similar. My syntax for subsetting was

   df{\*, -column1}
   df{column1: new_name1, column2, column3: new_name3}

I noticed in working with pandas you often need to do lookups into other dataframes. It's partially solved by assignment operator if left field equals right index, or .map method, the same way.

But often you need a lookup with merging by an arbitrary column, then grouping and aggregation by the left table items. This is partially doable without special functions. But this can be a killer feature if one makes this for spatial joins.

Very often you need to do the following:

    gdf1 = geodataframe of points
    gdf2 = geodataframe of points

need to make gdf1.geometry.buffer(500 m) and sjoin it with gdf2.geometry, then lookup gdf2 fields and bring them to gdf1, and keep original gdf1.geometry (points). This operation takes a dozen of lines and leaves lots of variable garbage if not put into a separate function.

But IMO it could be condensed to something more natively supported.