My biggest issue with sql has and always will be the lack of definitions.

There's no way to express data structure knowledge in sql, only relations and keys. Discoverability can be quite lacking. And every time I want to join table A to B I have to re-define everything, because SQL doesn't store that.

ORMs help. They help because they encode relationships in meaningful ways. A has many Bs, so A.B works, and I don't need to repeat this join logic every damn time. But ORMs have down sides too. Sometimes they generate queries that are really sub-optimal.

I think my favorite ORM usage was with Hibernate. I KNOW!!! THAT DEVIL! But honestly we wrote SQL in hibernate, and then invoked it to populate our data. Yes it was a bit more work than RoR's "order.items.where("price > 40")" but when complex things happened, it was always easier.

I've always looked at nosql as "absolutely, sounds great, how do you represent relationships?"

Hi, just curious - could you or someone else be more specific about the way in which an ORM encodes relationships that SQL doesn't?

An ORM allows you to encode a relationship once then use it for many different queries, I think that's the idea.

But a view also does that? Like, if you want to assemble information about a user from several different tables, you can have a view that does the join for you.

Sure, but people never use those. Also it gets back to the discoverability issue. The ORM documents relationships with the rest of the model code, if you have a poorly named view in a large, complex schema it may be hard to find. You could reinvent the wheel easily.

Like everything with SQL, you can solve the problem but sometimes the solution isn't elegant. People want elegance.

This is an accurate description of how I think of this problem. And it would be tough to describe all this:

- table a joins b yay a view

- table b joins c yay a view

- table c join d yay a view

- table a joins to d. well technically it can, but are you really gonna write all the permutations of views for every possible join?

ORMs encode that nicely so I can easily walk the relationships and get to the query I need.

Essentially all of the actual information is encoded in the foreign key constraints (in combination with uniqueness constraints.) What ORMs provide that SQL doesn’t isn’t much encoding of information (they usually have facilities to distinguish data tables from pure join tables, and to distinguish 1:1 and 1:M, (M>0) relations from 1:(0-1) and 1:M, (M>=0) ones, so they do encode some additional information), but provide convenient syntactic shorthands for the client to use the encoded relationships.

This would be easy to add to SQL, as syntax sugar, https://news.ycombinator.com/item?id=34587412

To be completely honest, I forgot who was it, but about a year or 2 ago someone posted about a sql alternative language. The goal was to be adaptable to be used in all sql application, but a better designed language so that things like a calculation in a select can be reused in another select column, and better structure to make things easily reusable or composable, etc.

Just a new language to replace sql, that is still sql but just better designed with the benefit of 30+ years of language design improvements.

OQL: https://en.wikipedia.org/wiki/Object_Query_Language UnQL: https://www.dataversity.net/unql-a-standardized-query-langua...

More modern:

PRQL: https://prql-lang.org/ Malloy: https://news.ycombinator.com/item?id=30053860 (it is so obscure that Google replaces it to "Malay language")

Another example: ClickHouse supports standard SQL with many features such as window functions, SQL/JSON, and extends it to make it convenient for data analysts by adding: - higher-order functions; nested data structures, arrays, tuples, and maps; aggregate function states as first-class citizens, unrestricted usage of aliases in any place of expression, etc.

https://github.com/ClickHouse/ClickHouse/

I'm an everyday user of ClickHouse, and I'm finding its SQL implementation the most pleasant to use! Although it's unsurprising, because I'm also one of its authors... I'm also welcoming innovation and improvement of SQL without the introduction of a completely different language.