I don't need your query language | Hacker News Reader

I don't need your query language

antonz.org

330 pointspolyrand294 comments

SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows.

And there are often problems when using multiple SELECT queries in a batched statement: you can't re-use existing CTE queries. Not all client libraries support multiple result-sets. It's essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn't even support named result sets...), which means you can't opportunistically skip or omit a SELECT query in a batch because your client reader won't know how to parse/interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you'll run into concurrency issues if data changes between SELECT queries in the same batch ()

[1] https://learn.microsoft.com/en-us/ef/core/performance/effici... and https://learn.microsoft.com/en-us/ef/core/querying/single-sp...

Isn't your comment judging a fish by its ability to climb a tree?

If the task is to climb a tree, it's reasonable to not hire a fish.

https://www.google.com/search?q=can+fish+climb+tree

Thank you eimrine, very cool!

You're asking those whose object problem model is a FOR loop not finding set based operations intuitive, efficient or ever necessary?

This is no longer true in database that have JSON support (which is most of them these days). You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

That further increase the complexity of the queries, and then you start hitting weird corner-cases like postgres's difficulty (inability?) to convert a JSON array of JSON text elements to an array of text.

I donno about other databases but it doesn’t add complexity to PostgreSQL. And you can convert json array to text array.

> further increase the complexity of the queries

Small price to pay for improving RDBMS throughput and eking out more from limited hardware. There is no shortage of use cases where this just makes sense. Doing all of that work in a single SQL query also makes sure there's less buffer cache thrashing.

I think you're wrong, happy to be corrected though. As far as I can tell, if you change a subselect column into JSON, it's much more expensive in CPU and marginally more expensive in network bandwidth.

1. The data has to be serialised into JSON on the DB server, which costs CPU.

2. It then has to be deserialised on the application server (unless your backend is written in javascript and then your throughput problem is that you're using javascript instead of a better, compiled language)

3. The network bandwidth is actually larger as you've got all those extra {} in your result set, compared to the raw data in column format

It might "look" bigger to a human as there's more columns, but the data is exactly the same. So by definition, youre doing extra CPU work of serialising/deserliazing JSON and adding all the object markers of extra characters like {} and "" and : means the payload is bigger too.

> which costs CPU

Which costs very little CPU in 2023.

> deserialised on the application server

This is true regardless. The low-level libraries are still parsing the stream into meaningful in-memory structures. With JSON, the low-level library only has to parse a variable length string, then JSON decode. I'm unfamiliar with any language in 2023 that doesn't have incredibly fast and efficient JSON parsers.

> bandwidth is actually larger as you've got all those extra

This is true, but generally negligible. I run into very few scenarios where network saturation is more of a problem than CPU or memory issues. If you have network-constrained problems, obviously optimize accordingly.

No, you can't use 'negigibly' worse as a defence.

It's either better, or not. Your comment does not make it better, it's still worse. So it won't improve performance, but degrade it, even if it's negilible.

So there's no reason to do it.

Plus you've made a crazy SQL select instead of a normal one, which is harder to maintain.

So it's worse performance and worse maintenance.

i.e. don't do this, it's dumb, especially for the reasons claimed which are factually incorrect as it will not improve throughout but make it worse

> you can't use 'negigibly' worse as a defence

Absolutely you can when the increase in something (bandwidth) in a system with surplus supply with the trade-off of optimizing a more constrained supply (CPU or memory).

> made a crazy SQL select instead of a normal one, which is harder to maintain.

Purely subjective. Myself nor the people I've hired would have a problem maintaining a more complex SQL query using CTE's and JSON serialization than not.

> it's worse performance

I cannot imagine that's the case in the context we've been discussing. An RDBMS duplicating JSON output of tuples multiple times in a single transaction is not particularly expensive compared to the alternative.

The funnest part of outputting JSON from the query is that your API now basically becomes a RPC, as you don't have to do any marshalling/deserialization before returning the response with Content-Type application/json

Response times are really fast like that, as you probably already know. Always fun to see <10ms round trips in the browser network tab on some requests

Also no worry about n+1 queries, as they're fundamentally impossible to do like that

I've long wondered if there would be any performance advantage for an API server to zero-copy the DB response to the browser, deserializing from the DB wire protocol on the front end.

Or perhaps sending DB query results directly from DB server to browser, with the API server just initializing and securing.

Thanks for pointing out how JSON from the DB is another option for moving a bit of processing elsewhere in the stack.

You've basically described Firebase and its archetype of database.

TLS everywhere makes the whole point of zero copying obsolete. Encryption eats so much CPU that copying data around does not change anything.

It does add a few ms, but you're overstating it. For LAN traffic, it's usually 5-8ms last time I checked on my servers.

That not what we're talking about though, we're talking about aggregating the the result of a subselect to reduce the throughput of a SQL query.

I'm just pointing out it doesn't achieve that.

It's not subjective, it's.objective.

This solution for tuples is objectively worse in every way, throughout, network bandwidth, code complexity, maintainability, error likeliness.

You're just clearly someone who can't admit when they're wrong.

> it's.objective.

Prove it.

> You're just clearly someone who can't admit when they're wrong

You don't have a good technical argument so you jump to ad-hominem's?

They've presented plenty of drawbacks, which you've dismissed as "it's not that bad", while not presenting any benefit.

I've presented the bandwidth vs. memory and CPU trade-off, was that not obvious? Not to mention reduced network round trips, and less overhead on transaction management.

If the biggest issue in my product is JSON deser, I'd be a happy camper.

Bandwidth and memory are probably both worse, JSON adds overhead. Round trip latency only happens if you wait for the results of one query to send the other.

>You're just clearly someone who can't admit when they're wrong.

I suggest avoiding statements like this. Yes they help vent your frustrations but they destroy the otherwise constructive and interesting debate the two of you were having.

> No, you can't use 'negigibly' worse as a defence

Absolutely you can when I can absolutely guarantee you that you’ve written worse performing queries in the past that caused more cpu issues than the overhead of working with JSONB.

> Which costs very little CPU in 2023.

That's a very bad argument for an RDBMS, since you want to make it scale vertically as much as you can (unlike your application server, horizontally scaling your database is a completely different matter, and isn't straightforward at all).

The added cost of JSON serialization is easily offset by reducing the total number of overall queries and implicit (or explicit) transactions. The additional parallelization the RDBMS can achieve is generally greater than the added JSON serialization and extra network bandwidth.

Maybe, but this isn't the same as saying CPU cost doesn't matter because it's cheap.

> same as saying CPU cost doesn't matter because it's cheap

A straw man argument you've pulled out of thin air.

It’s still a big saving overall if the alternative is a JOIN causing a cartesian explosion in the number of rows

1. Read the source. It's very efficient: https://github.com/postgres/postgres/blob/a14e75eb0b6a73821e...

2. You don't have to do deserialization in the application layer. If all you're using JSON for is to convert to OOP objects, just deserialize in the db -- which again, trivial.

3a. This is wrong on many counts. If you want efficient passing of JSON, use JSONB which is the binary encode of the JSON, as a tree structure. It will not include the structural characters.

3b. Bandwidth is also cheap.

4. Don't optimize without profiling. A few extra CPU cycles is not going to make-or-break your scaling journey, you'll most likely run into larger problems before that happens.

5. You can get "non-uniform" tuples by using UNIONs and a smart flagging system that points to tuple schemas -- rather than using JSON; the difference is entirely ergonomic.

6. If you're in a low-latency environment and the CPU cycles are absolutely critical, write your own extensions to handle what you're trying to do, instead of twisting Postgres into doing your bidding.

    select array_agg(e)  from jsonb_array_elements_text('["a","b"]'::jsonb) e;

?

Absolutely. JSON serialization is so well optimized in most RDBMS vendors now it puts very little additional CPU work on the database, and duplicating data in the output for large graph-based results is a small price to pay to breaking out of set-based constraints.

> This is no longer true in database that have JSON support

Doing that means losing foreign-key referential integrity...

He specifically addressed that in the comment. In the sentence right after the bit you quoted.

The comment alludes to this, but to clarify:

You can have your data stored in tables with all the constraints you might want, but then use json in queries to return the results in whatever form you want.

My mistake - to be fair, it is 5am here

Generated columns (i.e. some_json->>'id') can have constraints just fine, maintaining whatever integrity you want

The crap mini-language of Postgres functions to manipulate JSON makes otherwise reasonable queries unreadable, though.

That “crap mini-language” is now in ISO SQL. Now we’re stuck with it forever. Even in Oracle :o

I think they are adding many readable function versions lately

JSON has pretty limited choice of data types though. Want better precision for numbers, use a string. Want dates? Use a string. Etc.

> You can aggregate the result of a subselect into a single column (the underling data doesn’t have to be stored as JSON, you can convert as part of the query)

For anyone (like me) who is not quite able to visualize this, here is an example:

    SELECT json_agg(trips)
    FROM (
        SELECT 
            json_agg(
                json_build_object(
                    'recorded_at', created_at, 
                    'latitude', latitude, 
                    'longitude', longitude
                )
            ) as trips
        FROM data_tracks
        GROUP by trip_log_id
    )s

From StackOverflow user S-man https://stackoverflow.com/a/53087015

As someone who would like to use SQL to return a tree-like structure, how well does this scale (in terms of readability, performance, etc.) when the query is extremely large and nested? For example, if we are attempting to replace GraphQL with some sort of SQL.

I'm super unfamiliar with this space, and would love to know whether it is a feasible/worthy goal to replace GraphQL queries with generated SQL.

Very well, I run huge nested CTEs with json aggregation in SQLite for findsight.ai and there’s really not any noticeable performance overhead.

A good editor (DataGrip) helps.

You might be interested in PostGraphile. [1]

[1] https://www.graphile.org/postgraphile/

Like with most performance questions, it depends.

I see someone else said that it scaled well for sqlite. For postgres, it very much depends. Building a json object is much, much slower than selecting a row. Updating JSON objects involves building a new one, since you need to replace the entire object, so avoid building your schema in a way that requires updating JSON objects.

(Experience is with postgres 14. To be fair, performance was generally fine up until the 100s of millions of rows)

JSON query performance in postgres though is generally quite good. If you have static data, throwing it into a JSONB column with a jsonb_path_ops GIN index (or the default if you need the extra query flexibility) scales well up to billions of rows.

building a json object is slow compared to returning rows. but its far faster than multiple trips to the database to build a json object in the application layer.

But now you have to work in...shudder...JSON.

It's nice being able to use datetimes, 64 bit ints, binary, etc

Yup, this right here. This aspect of SQL is overwhelmingly why I insist on ORMs, too. Any efficiency gains you get by having a senior dev write raw SQL for a complex query are immediately negated by a junior turning what an ORM would write as a single query into three DB calls. All because SQL insists on a flat result set you have to turn into a nested collection yourself, without an ORM doing it for you with eager loading.

From my experience, ORMs in hands of junior.developers who happen to not yet know SQL are a disaster. However hard the ORMs may try, the code ends up making a ton of small queries instead of one efficient query, and fetching a ton of unused columns. The developers then end up doing joins manually in application code, some distance further from the place of the original queries.

ORMs also tend to sneak "live" objects into unexpected places, triggering surprise DB accesses.

Not that ORMs are completely useless. We just need to stop pretending that there can be a smooth and performant automatic mapping between relational tables living in a DBMS and Business Objects living in some idealized world without storage limitations, where any connection between them is like following a pointer in RAM.

Most ORMs provide tools to write composable, reusable queries and parts thereof. These are the best parts.

Every time I have used an ORM I end up supplementing or replacing it with a "query DSL" like jOOQ, Linq, Arel, Ecto, diesel, etc. I seem to have the opposite problem of OP: I don't often find myself wanting to hydrate some complex object graph, what I really want is some small fraction of what constitutes "an object": "get me the distinct values of this column, sorted by another column", or "get me a list of user IDs and e-mails that are subscribed to this topic", etc. Trivial to do in SQL, and much faster to do that sort of thing _in the database_, where the data is already _memory/cache resident._

ORMs, by design, bring unnecessary data over the wire for the sake of inflating parts of an object graph you don't care about 90% of the time. Most of that data will either be unused, or you are going to transform and then discard anyways. If you go out of your way to actually optimize out unused fields: now you're passing around objects with nulled-out references around your application, which is just a disaster waiting to happen.

Having a query DSL that actually maps result sets to your language's type system is the only way I've found to actually write robust, performant, maintainable code. My result sets being "too big cartesian disasters" is just not a problem I have, because I don't think in objects. I ask the database for what I want to get the job done.

Often we just want "totally adhoc result set, but constrained using some common where or join." We keep on with the orm, but it's basically a slow and complicated form of a view at this point

These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications. What was once a single API call for the application, backed by a SQL query joining over tables has been replaced by multiple micro service calls. It’s now the application’s responsibility to hold these MS call results in memory and then relate them once all the data has been fetched. This works well because the failure rate of http requests is so much lower than a DB query. And after all, the further away the code from the data the better equipped it is to reason about the data.

> These problems have been “solved” by designing micro services that don’t perform any joins but shift that responsibility to the applications

This is utter nonsense. There is absolutely nothing about microservices architecture that relates to performing JOINs in SQL.

thatsthejoke.jpg

I’ve had a first-of-class linuxian excellent developer but junior, tell me that we need Kafka because our SQL requests took 3 seconds.

It should be a single INSERT, but through an ORM that multiplies it. The only upside of Kafka is not having the ORM…

If you need pre-fetching and 2-phase-commits, a database designed for queues can easily work 3x better than a genetic SQL database.

That's being said, Kafka is not one of them.

In this case, you are the senior that needs to bypass the ORM and just use whatever raw parameterized query support exists in your ORM of choice.

> whatever raw parameterized query support exists in your ORM of choice

That doesn't help with conditional-predicates - that's another major shortcoming in SQL.

"raw sql" is table stakes, core competency for every software engineer

ORMs 100% always do it worse

I keep staring at the output of sqlalchemy's "select in" eager loader trying to figure out if has managed to pipline the follow up queries into a single DB round trip for the problem of tree shaped data. Still don't know, maybe someone does.

https://docs.sqlalchemy.org/en/14/orm/loading_relationships....

Those are problems in MS Sql, certainly not in object relational DBs such as Postgres or Oracle. And its rather sad that instead of embracing that we ended up with Json as poor man replacement for such advanced usages. I guess non portability across DBs certainly doesnt help. I tried showing that 10 years ago: https://github.com/ngs-doo/revenj/ but it just resulted in confusion.

You don't really need multiple result sets, you can do lateral joins (cross apply in microsoftish) to bring in any unrelated data and distinct the result. And fancyql doesn't provide any alternatives, so it makes no sense to even bring this up.

I think of SQL as one of the few good things we have in software development, so like the author I consider it best to try to do as much in SQL as possible.

It's not too uncommon I run into code in other languages where I just don't understand what it does, or to write code myself that behaves in ways that surprise me. That almost never happens in SQL.

Even a big hairball of a query just takes time to figure out (unless a database-specific function with odd behavior is used).

I tell new developers that SQL is one of those few things in our field you get to keep forever.

That JavaScript framework that takes a year to understand will no longer be used in 7 years. SQL is going to be here forever and learning it is useful your whole career.

Other common entries on this list of forever tools: regular expressions, emacs, bash/shell scripting, excel, probably more I’m forgetting.

Devs always push back on the suggestion to learn something like excel (actually learning it, not just clicking around), but when they first start they really underestimate how often it’s the tool the business speaks and feels comfortable giving feedback on technical questions in.

> that JavaScript framework that takes a year to understand will no longer be used in 7 years.

Surely there is a lot more nuance for having a nicer database query language than comparing it to JS frontend practices.

SQL is something that'll stick for a long time, but I support attempts from people that don't want to let SQL be the endgame.

Of course don't go around deploying highly experimental shiny things on production! :P

Nuance? Not really. Bullshit flash in the pan tech stacks are what they are regardless of how much marketing drapery one adorns them with.

> how often it’s the tool the business speaks and feels comfortable giving feedback on technical questions in.

Yes, but as often as not business people end up using Excel as a glorified text editor with built-in tabular formatting. Some of the spreadsheets I've been handed by project stakeholders would make 90s-era pre-CSS HTML using tables for formatting look clean and simple by comparison.

After writing applications in "4GL" sql tools early in my career, I've spent the entirety of the rest of my career trying to avoid writing SQL or having anything to do with the inner guts of those gigantic global variables known as relational databases.

The problem is that a lot of developers consider things forever tools when they are not forever tools and not even close. SQL is one of the forever tools, but even in your short list I would absolutely strike down two of them as "forever tools". They're more like "my favorites that I've invested decades into".

Which 2 would you strike down out of interest?

I wholeheartedly agree that engineers massively underestimate the power of Excel.

Forever is a stretch, maybe “until the end of my career” tools. That list of tools could eventually be replaced, but I bet whatever comes after them will be forced to adopt a lot of their metaphors to gain acceptance. For instance, maybe excel eventually gets replaced, but the thing that kills it will feel a lot like it. The experience won’t be wasted, useful things you learned to do in it will continue to be useful in the new tool.

> That JavaScript framework that takes a year to understand will no longer be used in 7 years. SQL is going to be here forever and learning it is useful your whole career.

Nothing seems more ephemeral than JavaScript framework du jour.

>> Other common entries on this list of forever tools: regular expressions, emacs, bash/shell scripting, excel, probably more I’m forgetting.

Oh yes, many more: Java, JCL & COBOL, Windows Forms, PhP, Wordpress, Joomla, Drupal, Perl, Visual Basic, IIS, SSIS, Ruby on Rails, and so on and so forth.

Maintaining enterprise software is a special circle of hell reserved for developers. Just sayin'.

I tell new developers that SQL is one of those few things in our field you get to keep forever. --- I'm not so sure anymore. Look what is happening with css... every simple thing will be killed and replaced with something complex, so you could achieve less with much more effort.

The main issue with sql is, that it is the wrong way around, which eliminates all tooling support.

You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that.

So switching this, moving from and joins in front of select, might be everything needed to fix sql.

FROM users SELECT name, id, location WHERE name LIKE 'a%';

You know I think you're right.

Go further: `FROM users WHERE name LIKE 'a%' SELECT name, id, location` - after all, you can WHERE on things that aren't projected by SELECT

A human would grab the paper with the right table on it then search for the row he wants with his finger and finally gets the phone number from the row.

There was an article on HN about this maybe 6 months ago. I’m in agreement.

Eh. I’m not convinced by this. I may know what I want to query as often or more than knowing where it comes from.

I suppose it could be nice if the user could specify clauses in an arbitrary order but it’d certainly add complexity.

I don’t find it difficult to jump around a bit from clause to clause while writing a query. In fact, it’s incredibly rare to write a query straight through and have it do what you want it to do.

O well, if we are going to be like that?:

FROM users WHERE name LIKE 'a%' SELECT name, id, location

(I should refresh the page before posting)

I might be wrong, but there’s nothing to stop a front end UI from accepting this syntax and then writing out canonical SQL to do the actual query, is there?

Exactly, it puzzles me that tooling doesn't do that...

Ain't broke.

GP clearly shows why it is. I think SQL is great but their point is spot on.

The wrong order in the statements is a minor inconvenience but the widespread use and having an industry standard is 100x as valuable.

Agreed. The last thing I need is more flexibility in how people write queries. It would be shortly followed by numerous aggressive query formatting tools. Things would get messy fast.

That's just his opinion. SQL does it's job extremely well, is relatively easy to parse for its inherent complexity. That somebody would like FROM before SELECT or WHERE after GROUP BY is of no consequence.

C#'s LINQ (query syntax, not methods) got it right

var result = from s in stringList where s.Contains("Tutorials") select s;

or just `var result = stringList.Where( s => s.Contains("Tutorials") )`

I can't stand the non-extension-method Linq syntax: the _only_ place where it offers a readability improvement over ext-methods is using `join` - but I hardly ever do that in Linq anyway.

Also, in both my code and yours, `result` will be a lazy-evaluated `IEnumerable<String>` which may be undesirable - which means it's probably a good idea to use `.ToList()` to materialize it - which means having to use ext-methods anyway - and mixing both syntaxes in the same expression is aesthetically atrocious.

>I can't stand the non-extension-method Linq syntax: the _only_ place where it offers a readability improvement over ext-methods is using `join`

Hah! I tell the juniors this all the time :D. This seems to be basically the consensus among the C# community these days as far as I can tell as well.

I guess I'm not in the C# community. `let` is pretty annoying to reproduce with extension methods. Also, when you use multiple `from` clauses, you get access to all scopes, whereas with `SelectMany` you only get the parameters of your current lambda.

> you get access to all scopes

Read: "you create new heap-allocated closures which wreck your Linq expression's runtime performance"

Or:

"you create Linq queries that cannot be translated into SQL"

It was all in memory. To the extent that it's a performance tradeoff, it might be one that's worth making depending on the use.

Aye - but the annoying thing is that it was never necessary: with a few subtle changes to Linq it’s possible to have allocation-free closures by passing state via hidden parameters on the stack - but just like every language out there we’re now hobbled by decisions made 15 years in the past.

——

On a related note, it’s interesting just how unpopular so many new C# language features are (just by looking at the numbers of Thumbs-down reactions on the GitHub Issues/PRs - stuff like top-level Main. It feels like C#’s LDT wants to be like Swift, but without Swift’s willingness to ditch ill-conceived features after a few years… but I think C# would be well-served by taking an axe to some language-features by now - like keyword-Linq and CLS-compliance (honestly, do any ISAs today still lack hardware support for unsigned ints?)

Interesting.

I think there are some otherwise-seldom-used linq overloads for doing just that. Maybe on Join() or GroupJoin() or something like that. Query expressions use them for compilation to keep closure use down, but I don't know too much about it.

Anyway, if top-level statements are wrong, I don't ever want to be right. I had no idea there was any controversy on that. It's trivial to add the boiler-plate back in if you want it.

The main thing that bugs me is that Expression<> is stuck with language features that existed in C#4, even when there are trivial lowerings. e.g. `is not null` could be `!= null`. (This example ignores operator implementations, but most IQueryables ignore more than that already) Even better, add AST node types for the new language features.

I'm not sure the Github issues are the best reflection of the popularity of those features.

Top level statements and the new HostBuilders are fantastic, they're much cleaner and easier to follow than the previous mess. Add Minimal APIs to the mix and C# is finally a viable choice for spinning up something quickly.

I completely agree with you, in the context of C# code. But also take into account not everybody in HN is a C# developer, and OP syntax works better as an example of his point. All non C# devs will understand his "sql like" example easier.

> the _only_ place where it offers a readability improvement over ext-methods is using `join`

No love for let?

    from item in items 
    let frob = Expenseive(item.P1) 
    where frob > 3 
    selec new { frob, item }

That can be done with ValueTuples instead of Anonymous Types: `items.Select( i => ( i, frob: Expensive( i.P1 ) ) ).Where( t => t.frob > 3 );`

...and ValueTuples are superior to Anonymous Types because you can actually return them from a function or use them as parameters - whereas Anonymous Types cannot cross method-call boundaries (excepting using generics for pass-through).

Anonymous Types in C# were a massive mistake. They should be [Obsolete]'d, IMO.

The tuple might be gone by the time the scope ends. I'm aware it's possible to reproduce the behavior without let. The code just looks worse.

chill,

I've been just showing that *approach* of starting SQL query from "from" part is viable, because that's how Microsoft timplemented in one of two LINQ "API"s"

I'm not trying to convince anyone to use LINQ's Query Syntax.

I wonder if that could be addressed at the spec level allowing reverse order of these keywords. It doesn't seem complex on the surface and the the engines could slowly add support for it.

LINQ and proper reflection are what I miss most from C#. (I’m all Typescript at the moment.)

Gods, I tried to do some reflection stuff in a Node TS project I got put on about 18-24 months ago, and it was a total shit show. It was really disappointing to see how lacking the actual runtime capabilities of TS are.

There are no TS runtime capabilities. This isn't a thing.

There are some libraries that allow you to do some stuff. You can attach metadata to some objects about types in some contexts. But obviously it's bolted on because under the hood it's all Javascript.

What runtime capabilities? There shouldn’t be any?

What does "proper reflection" mean to you?

The work was already done for them, as LINQ is just do-notation from Haskell.

No, Linq is *not* in any way like Haskell's `do`.

Linq is a (reasonably) compromised, non-referentially-transparent, implementation of a restricted form of relational-algebra with side-effects permitted, so I'm not comfortable describing Linq as "monadic".

Whereas Haskell's `do` is strictly monadic.

I recall Anders saying on some podcast they did that so they could provide auto-complete. Funny thing is that I used it for a couple years, but almost never use the linq syntax any more, and not sure why.

Probably because if you wanted to write something like SQL, you would write it in SQL, and if you want to write C#, you write C#

Linq query syntax is just dumb

I'm so jealous that Java does not have its own LINQ.

Tcl dicts do something very similar, and Tcl is very well integrated with SQLite.

While I agree with your point, this isn't really as big of a stopper for tooling as you make it seem. Many programming languages use something akin to import foo from bar and their tooling is just fine.

Always start with the end in mind, first what your goal is then how to achieve it.

Also, i don't actually see the problem because you never write a query in a lineair way. Usually start with "select * from table limit 10", look at the columns and data available, and then start refining. By now, code completion works as the table is known. Wouldn't help much to write it table first.

Exactly, you could actually drop the `select` in this case and just say `from table limit 10` don't state what you don't need.

What you describe is learned behavior to get along with a design flaw. SQL won't change, so no reason to worry.

My point is: People keep creating new versions of it, because it is not as `easy` to work with as it could be.

That's valid DuckDB syntax

DuckDB has some really nice syntax updates that I hope get added to the ISO spec, like GROUP BY ALL and GROUP BY aliases.

I like their approach of adding thoughtful quality of life improvements instead of coming up with a new language.

https://duckdb.org/2022/05/04/friendlier-sql.html

Most of the improvements in the article, including unrestricted aliases, were added in ClickHouse and subsequently influenced DuckDB. They still have to implement many usability and language improvements from ClickHouse.

Didn't know that, thanks.

> GROUP BY aliases

We're migrating from Sybase SQLAnywhere to MSSQL, and not being able to use aliases in WHERE, GROUP BY and ORDER BY is such a pain in the behind.

Almost all our non-trivial queries have to be nested multiple levels due to this, which doesn't exactly help readability.

Ugh, I'll be having to do this same migration soon for one of my clients. Not looking forward to it. Good to know!

I would love if base SQL were improved. On the other hand, we are just slapping lipstick on a pig. It is already a gargantuan mess which needs a path to replacement.

Then again, Oracle just this year added support for booleans, so asking the incumbents to switch to a new query language seems an impossible ask.

Agreed... Their SQL is awesome and seemingly only getting better.

My favorite is window aliases (I'm sure it's found in other SQL engines too). It's not only cleaner and less likely to accidentally introduce a discrepancy, but apparently also results in better performance.

> The three window functions will also share the data layout, which will improve performance.

https://duckdb.org/docs/sql/window_functions#window-clauses

I'm surprised some of the other big-name OLAPs don't provide this. *cough* Snowflake *cough*. Snowflake's query optimizer doesn't even seem to recognize common window definitions across multiple window functions.

Because the DuckDB folks used the Postgres query parser.

It's been an alternative in Postgres for decades.

In Postgres, it's

   TABLE foo
   LIMIT 100;

No SELECT with columns and no FROM keyword.

> Usually start with "select * from table limit 10", look at the columns and data available, and then start refining.

An experienced person won't do that. For any moderately complex SQL query, before writing it I already have in mind the several jointures I'll need, since I usually know the tables and FK I'm working with. It's like following the edges of a graph, all in my head. But I don't know all the fields of these tables, so I rarely write their names from memory. So I have to write "SELECT 1 FROM …" and then go back to that "1" once my FROM is complete. That's not the end of the world, but it does smell.

> An experienced person won't do that.

I’m very experienced and do that kind of thing all the time.

Also, your “SELECT 1” technique seems like practically the same thing, except I guess you discover column names from autocomplete tooling rather than query output. (To my mind the difference is inconsequential.)

Me too and I don't even bother with LIMIT 10. However, the "proper" experienced way would be DESCRIBE I guess.

If you mean a developer with deep and recent experience on the exact tables your query will use, then yeah, this one won't do that.

But if you mean any other kind of experience or expertise, you are wrong. Those do not correlate with how a person assembles a query.

I consider myself experienced, started professionally with sql in 1994 with oracle 6, and it really depends on how familiar you are with the database. I often work with databases I’m not familiar with, so exploring the structure by looking at examples of the data is where I start.

> have in mind the several jointures

I typically keep all JOINs I've ever used on that schema in a single file, one per line.

Before writing a new query I can just copy paste some JOINs, simply skimming through table names like lego bricks.

That way it's surprisingly easy to beam from domain problem to a new query that uses 10 or 20 tables.

I've just realized that it might be all archaic now, in LLM era.

This is a great idea in practice, but it really points to how sql could improve.

You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.

This is also my primary issue with vi.

d3w (`d`elete `3 w`ords) cannot be highlighted / indicated in any way ahead of time. If the motion specifier came first, it could be.

v3wd?

`3w` is a command all its own. There’s an implicit move command baked in. Moving gets pretty clunky if you don’t have first order movement (adding a specifier or something to clear selection). You can mimic this by mapping a single button to <C-v> and disabling all movement commands in normal mode. It’s rough, but may be learnable.

I think something that might work better is adding a “commit” signal to operations. So you type `d3w` and the editor highlights the next three words with a strike through or red or whatnot. Then you can hit enter to commit the delete or escape to cancel it (cursor resets to start position, highlight goes away).

If you use visual mode, you can do `v3w` to highlight three words, and then you can manipulate that selection before you do something with it, like `d` for delete.

You can also use that with ex commands, like if you do `vap:s/foo/bar/g` you will replace `foo` with `bar` only within the block of code you visually highlighted with `ap` (which I remember as “a paragraph).

So if you want “delete three words”, do `d3w`. If you want “highlight three words” do `v3w` and then issue another command like `d` to do something with what you highlighted.

I think these two concepts could be reconciled by having selection be combined with moving. So 3w could select three words but also move the cursor to the end of selection, ready to process another normal mode command. The highlighting would need to be scaled back a little (perhaps a dark gray background), as you don't want your entire screen to light up when moving but I think it's doable.

That's exactly the model used by the Kakoune editor[0]. It definitely feels more intuitive to me, but I personally didn't stick with it due to vim's ubiquity.

[0] https://kakoune.org

> So 3w could select three words but also move the cursor to the end of selection

I mean, if you hit v first, that's exactly what it does.

Yes, but in terms of editing speed and comfort, one keystroke is the difference between life and death. As long as the motion can be described in a single command, visual mode is overkill.

Wouldn't what you're proposing complicate the rest of normal mode though?

Or is this just reversing the order so it's {motion}{verb} instead of {verb}{motion}?

If you want to highlight something, use `v` for "visual" mode

You might like kakoune (https://github.com/mawww/kakoune), which does exactly that: first you select the range (which can even be disjoint, e.g. all words matching a regex), then you operate on it. By default, the selected range is the character under cursor, and multiple cursors work out of the box.

It's also generally lean and follows the Unix philosophy, e.g. by using shell script, pipes, and built-in Unix utilities to do complex operations, rather than inventing a new language (vimscript) for it.

(Not affiliated with the creator, but kakoune has been my daily driver for years now.)

Helix[1] is another editor which heavily borrows from kakoune’s “selection then action” paradigm. The editor is very good, but still in heavy development, so it lacks plugins and has the occasional rough edge. [1] https://helix-editor.com

I know you specifically mentioned 'vi', but I'm going to blindly assume you meant 'vim'. In which case, as others already pointed out, you can use visual model to get exactly that behavior.

A good mindset to have in regards to Vim is, "Vim can do anything, even make you coffee". The trick with Vim is actually figuring out /how/ to do it.

I highly recommend you start with the VimCasts[1] video series. They're short, 5 minute, videos. With each covering a specific functionality of Vim. They straight to the point, and the author provides samples code for all videos.

[1] http://vimcasts.org/episodes/page/8/

In most tooling, you can write SELECT FROM table, then go back to the select list and have autocomplete work.

The situation could certainly be better, but at least this works today.

The query is just a requirements statement.

After parsing and analysis steps, the system is going to do what it does with the statement, no?

The syntax is for the user, not the system.

Sure, but the parent is speaking about how it is difficult for tooling to present with options when it doesn't know which table to select from.

Yeap, simply supporting FROM … SELECT … WHERE … would make sql much more autocompleteable?

This is a strength, not a weakness. It forces you to only select what you actually need, and state where it comes from afterwards, to only limit your query to exactly what you need.

This is paramount for performance.

If autocompletion is your issue, just get a better client, it's perfectly possible to autocomplete field names even before specifying database or table name.

I think you're missing what they're trying to say. You can still select what you actually need in a different order, but changing the order gives more immediate feedback from autocomplete. The order has pretty much zero impact on the performance of a query. A parser would still have to read out the whole query, and it's not expensive to unravel into a more efficient implementation if it would really help.

The problem is that SQL forces you to think about what to select before you even say where you're selecting from. There's nothing a client can do to recommend columns if it doesn't know where you're selecting from. It's pretty cumbersome to have to SELECT * FROM x and then go back and erase the * to actually get auto-completions.

It basically forces you to tell it what you want before you even know what the options are.

You are missing what I said.

I know that from the interpreter's standpoint, it doesn't matter which one is written first.

What I meant is that as a human, if you have to think first of the columns you want to bring in, it will guide you towards the joins that you need and only those, rather than thinking "let me join all those tables because I need _some_ data from the entities inside".

My point about "autocompletion is still position" was not connected to the first part of my comment.

Even if I know the exact query I want to write, your suggestion does nothing to improve autocomplete for typing it in.

Yes because that's not the main point of my comment. Disregard completely the part about autocomplete if you want.

I just added it as a separate point, to say "you can have autocomplete no matter the order in which you write your query"...

Almost exactly what I was going to say, then saw your comments. To me that the biggest problem. What I have been doing to workaround this is to just write select *, then finish the from join etc, go back and fix the result columns.

I get around this when hand coding by doing a quick 'SELECT * FROM', adding my joins, then going back and filling in the fields using intellisense. Intellisense doesn't know what columns are available until the FROM clause is handled.

To me, this is a slight annoyance with an easy workaround. If the SQL spec gets updated to allow switching the clauses around, I'll be pleased. But I'm not about to change languages over it.

That's exactly what I do too. 'SELECT * FROM', then write the rest of the query, then double back and replace the * at the end.

Is it ideal? No. But it's muscle memory now, so...

¯\_(ツ)_/¯

Oh, that's a great idea. What editor/ui do you use? Since I've got divorced from Jetbrains I miss Datagrip ...

Why the divorce? DataGrip has been the best database-gui that I've ever used.

I had the all-tools subscription for years (working mainly on Spring Boot and Angular projects) and IntelliJ and Webstorm were fantastic. Then I started Vue projects and later on Svelte and the Webstorm support for both (and Tailwind btw.) has been a disaster. Esp. how they handled the countless issues created by me and many others. Many other small annoyances like no support for international keyboards with dead keys on Linux and esp. how support handled it.

So, I had to move to VS Code for Vue and Svelte and as I didn't want to use 2 different IDEs I canceled my JB subscription. So far VS Code is pretty good for Goland and Rust as well, but as you said, I struggle to find something as good as DataGrip for SQL. Maybe I should just get a DataGrip license (single tool).

Edit: I forgot another issue ... DataGrip didn't support the latest SQLite version for a very long time, as the maintainer for the open source Java client wasn't working on it. When I dared to ask the JB support a second time for the status after a while, I got a stroppy reply from the responsible Jetbrains engineer, as how I dare asking twice and there's nothing Jebrains could do when the single person maintainer of an open source lib is doing other stuff with his life. Crazy.

vscode. mssql plugin because that's what I'm dealing with.

I agree with you and think this backwards model leads to developers having a poor mental model of what they are doing.

Step 1: build the dataset you want (FROM and JOIN) with all columns.

Step 2: filter out the rows you don't want (WHERE).

Step 3: choose which columns/values you want (SELECT).

Maybe it's just me but this model makes so much more sense to me! I'm sure not every developer has the same way of thinking, but it sure does seem more logical to me.

This is definitely something that makes tooling harder. But I think it is because tooling is though up wrongly.

Consider `SELECT 0 AS some_num`. This does not have a FROM clause.

While this example seems contrived, there are several examples of queries where the FROM clause is not just a listing of tables. Especially when moving into larger projects in SQL.

If I could change one thing with sql, this would be it.

It makes more sense to have the select last.

Just start with `SELECT *` and sculpt w/ autocomplete afterwards as you like.

Since you express that opinion, I trust you're already aware of this, but just in case: https://prql-lang.org/

>eliminates all tooling support.

That's an exaggeration. Usually I just start with Select * and build all the necessary joins. For that the tooling support works without problems and when I select the columns in the end it works too.

The problem with SQL is that it is not a (very) composable language.

The documentation for EdgeDb goes into some detail about that and shows an alternative better language for data-queries.

https://www.edgedb.com/showcase/edgeql

To understand why SQL is bad, you must first be shown something better, and EdgeDb seems to be such better more composable language.

Malloy is another thing to check out in this space

https://www.malloydata.dev/

SQL is infinitely composable. Each SELECT returns a relation that another SELECT can query (or combine with another relation using set operators like UNION etc).

I suppose it's infinitely composable in that very limited dimension, but SQL's critics are asking for composability in other dimensions. For example, say I have a pretty long query of some Order table. Now I want the exact same query, but it should start from the OrderArchive table instead. How do you do this? Dynamic SQL? The world has lambasted Javascript for much less, but somehow the fact that so many tasks require dynamic SQL (or copy-paste) is considered acceptable.

For the record, I make heavy use of SQL because relational databases are awesome and SQL is the least bad option I've found so far. But the many shortcomings of SQL still annoy me.

The answers are stored procedures and templates. People argue against them, but they're basically the data versions of software affordances we already have.

I like PRQL [0]. It fixes a lot of the SQL warts and compiles down to regular SQL (think Typescript to JS).

The syntax example on the PRQL homepage

  from invoices
  filter invoice_date >= @1970-01-16
  derive [
    transaction_fees = 0.8,
    income = total - transaction_fees
  ]
  filter income > 1
  group customer_id (
    aggregate [
      average total,
      sum_income = sum income,
      ct = count,
    ]
  )
  sort [-sum_income]
  take 10
  join c=customers [==customer_id]
  derive name = f"{c.last_name}, {c.first_name}"
  select [
    c.customer_id, name, sum_income
  ]

Trailing commas, select at the bottom, filter is both a WHERE and HAVING replacement, easy filtering of created columns, etc

[0] https://prql-lang.org/

I'd much rather debug an SQL query with CTEs, which is what that is approximating.

The notion that SQL is not composable is either a lie or simply repeated by folks with only a cursory knowledge of SQL from 20 years ago.

Views, set-returning functions, CTEs, and more: all examples of composability in SQL.

Then of course there's the issue of security where folks tend to put all of their access constraints into their middleware AFTER the data has already been returned over the wire in bulk. Take a moment to consider role-based access control and row-level security policies. Instead of trying to track down every possible spot where a JOIN could have crept in past the code reviews (you code review your DDL and DML, right?), you set your GRANTs, REVOKEs, and POLICYs at the points in your data model that need them; restricted data never makes it into intermediate result sets let alone the final result set and the wire.

Don't misunderstand me. GRANT, REVOKE, and POLICY can be a real PITA, but that's because *security* is a PITA, not the SQL syntax to enforce it. Anyone who tells you their app solves your data security problems in the app tier with a point and click is a lying salesman.

> The problem with SQL is that it is not a (very) composable language.

I thought the same until a few weeks ago. Then we used the WITH operator for pre-processing and giving things human-readable names.

That helped us manage complexity. The final SELECT statement was very easy to reason about.

Not sure if this is a best (or worst) practice but it helped us ship it.

Interestingly, EdgeDB is exactly the unnamed tool that the article criticizes.

SQL in the context of a single query struggles with a composable features. But looking slightly outside the scope of queries with assignment statements and view/materialization dags then you start getting some of that composition back. I think SQRL is an good example of SQL with a bunch of composable feature bolt-ons.

https://www.datasqrl.com/blog/sqrl-high-level-data-language-...

As an experienced (===old) developer, I have learned that data long outlasts the programs that access it. The lifetime of data is measured in decades, but programs last for years. Most SQL-based RDBMS teams have figured out workable version migration paths allowing old data to run on newer servers. Because this kind of migration is a very common and economically valuable operation, the vendors make sure it works correctly.

Sometimes a project, especially a greenfield project, looks like it will benefit from more recently invented data storage and query tech than your grandmother's SQL. That's always possible. And as developers we hope for, and work for, continued progress. But consider what may happen when the project succeeds.

If you're still on the project, you'll wake up one day and realize your oldest data is 20 years old. What happens if your storage and query engines are also 20 years old, because they didn't succeed to the extent needed to pay for maintenance and upgrades? You'll be in the software equivalent of the century-old subway system where you have to make all your replacement parts yourself, or get gouged by vendors that can't spread their costs among many customers.

Build for the ages, not for the moment!

Show me an elegant SQL version for the queries in this article: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ Particularly when you are trying to run queries where order matters, e.g. top 3 posters by topic on HN. You will find it much more annoying. Fundamentally SQL is based on the concept of tuples/sets which have no order so there's no way to avoid it being messy. What you want is a database based on the concept of an ordered list, suddenly what is complex in standard set SQL becomes easy in almost any other language. My second big complaint would be that SQL isn't really a programming language. Parts have been bolted on by various vendors or they now let you run python/java on the SQL server but considering how heavy SQL already is, having a full blown language may actually be less cognitive load than learning all the sub variations of language implementations.

It's easy to cherry pick. I guarantee you there are a lot more queries that are easier to write in SQL than in your favorite FancySQL (or even worse, NoSQL) variation.

But one doesn’t write arbitrary queries. It is very easy to end up frequently wanting to write the kinds of analytic queries described in the GP rather than the kinds of things which SQL expressed better (which you fail to describe).

People pay a lot of money for kdb so clearly they see some value in it despite the lack of sql.

There aren't. Feel free to try to come up with something. 'SQL' is pretty feature light (without specific extensions). Qsql is really great, it's a shame it's locked behind a proprietary language.

I don’t really find the examples convincing. Like, I get that sql could maybe be written in a slightly less horrid way but I would prefer something a lot less horrid.

I think I’m much more motivated by analytics queries than the kinds of thing in this example though. I find sql is poorly suited in this case because it is verbose and written backwards, and often requires many layers of subqueries. That said, one can usually still express queries in SQL that other systems do not allow.

For these kinds of queries I think there are just better ways to express them. Another issue with sql is that has some quite strange semantics.[1]

An example query I wrote yesterday is:

  select group, min, max, (max-min)/1e9 range
  from
    (select group, min(size) min, max(size) max
     from
       (select time, instance, sum(size) size, regexp_replace(name,…) group
        from X
        group by regexp_replace(name,…), time, instance)
     group by group)
  order by range desc 
  limit 10

Which is neither pleasant to write nor iterate on interactively.

With something like dplyr instead:

  X %>% mutate(group=regexp_replace(name,…))
    %>% group_by(group,time,instance)
    %>% summarize(size=sum(size))
    %>% group_by(group)
    %>% summarize(min=min(size),max=max(size),range=(min-max)/1e9)
    %>% arrange(-range)
    %>% head(n=10)

And that can be built up interactively pretty easily by adding onto the end of the pipeline.

I would also note that, due to sql being painful, the query is not exactly the one I wanted and instead I would have wanted something better capturing the change over time, but the thought of doing that in SQL seemed too unpleasant.

An example of an actual query language that tries to be better for analytics: https://prql-lang.org/

[1] from someone who spent a lot of time working on databases and sql: https://www.scattered-thoughts.net/writing/against-sql and just on semantics: https://www.scattered-thoughts.net/writing/select-wat-from-s...

I think the problem here is with the query, not the language. You can immediately improve its maintainability and readability by using CTEs.

https://antonz.org/cte/

This exactly the comment I was going to leave. If I get past one sub query, I will refactor to CTE.

Maintainability is not relevant to ad-hoc analytics queries. That may be dealt with once the correct query has been determined from sufficient iteration.

You can’t mix/max a summed group? The sum would be in the same group level as min max…

I corrected my example (the inner group was meant to be by more columns)

You could probably use window functions

Ex:

  min(sum(size)) over(partition by group) min,
  max(sum(size)) over(partition by group) max

Why do you need the outermost query?

I think I had originally written something like select min(size) min, max(size) max, max-min range, but that didn’t work as the newly introduced names ‘weren’t in scope’ and I didn’t want to type those aggregations out again. You’re right that it could have been avoided.

The author’s second example is extremely unconvincing for me. Why would I want to be forced to use a SELECT expression to calculate a mean? Relational algebra is a great abstraction. SQL, however, seems to be poorly thought out and ad hoc. It’s just the first implementation of a relational algebra language that worked. But why should we be stuck with it forever?

SQL is only ever as good as the schema relative to the business or problem domain. The focus on the syntax of the language was always a mystery to me. It's a domain-specific language. It's up to you to make it not suck.

If you are forced to work with a schema that is poorly-aligned with the logical reality it intends to represent, you would definitely walk away with a bad taste in your mouth. Hacking around bad normalization is 99% of what makes SQL suck for me.

If you ever get a chance to design the whole thing yourself from zero, you should almost always insist on one big database/schema and routinely review the table structure with the business owners before you actually go to prod.

The moment you start doing things like putting data for service A into database A and service B into database B, you lose a lot of power. Sometimes this is required, but most of the time it's an org-chart alignment meme. There are ways to join these separate databases, but it starts to fall down pretty quickly. The true magic of SQL is having all of those dimensions in one place at one moment in time so you can put a pin in anything without complex distributed transactions.

The "domain" in the DSL of SQL is "relational data" not "your business domain"

What does this "relational data" (hopefully) represent?

it doesn't matter what the data represents, what matters is the structure of that data, and specifically (for SQL) that it is relational, rather than key-value or document-oriented or whatever

many business domains are well-modeled by relational data

some are not

Also important: The behaviour of SQL is well understood, a new query language always introduces the risk of defects in the query language itself or developers making mistakes in an unfamiliar language.

> Here is another common argument: SQL was designed with 1970s businessmen in mind, and it shows.

That is a funny way of looking at it. I see that SQL is based on the work of a computer scientist vs DSLs being made by hobbyists, and it shows.

I had a similar experience this month. We've been pair-programming using Dbt to write "long-form" SQL to bubble up a report to our business users.

After an initial "Uh-oh, I haven't manually written complex SQL in a while..." it all came back fast enough (Thanks, first-semester relational algebra!). Turns out, sql is well-suited for business "in-queries"!

The things that made us scratch our heads came from how the schema had evolved over time. We now have those hairballs at least 'contained' and visible. And it's all pretty readable imho.

I guess my initial unease came from using ORMs for CRUD persistence and very rare exploration. And holy moly, I'm grateful for ORMs. I wouldn't want to manually write those inserts and updates.

So, I guess it depends on what you want to accomplish with your database.

Btw: A HUGE shout-out to Dbt and Dbt cloud for letting us treat sql as code. Didn't expect to love it that much. How was this not a thing earlier?

I'm working on a query language right now!

Why not SQL?

Lack of tooling for SQL.

Yes, SQL lacks tooling. There's a ton of stuff to build a SQL client, obviously. However, on the other side:

- I have no sane way to parse SQL

- I have no sane way to comprehend SQL

Writing a SQL query system would be many months of work. Tossing together a good-enough query language with standards like JSON or YAML means I can json.loads(query) in Python and JSON.parse in JavaScript.

SQL would be an ideal fit if there was good tooling, and it fits in more places than most people realize. Web API query a whole bunch of stuff stored in all sorts of complex ways. SQL is better on paper than RESTful / AJAXy / GraphQL / etc. APIs.

It's not better if it means that the query language takes more time to build out than the entire rest of the system.

TL;DR: If you want to build a high-visibility open-source project and guarantee employment for the rest of your life, an elegant SQL parser, especially for building web APIs, would be a great thing to do.

I'm generally on the side of OP, but this is reasonable.

This comment is full of hyperbole. Writing a precedence-climbing SQL parser should take a few days. I know because I've done it. I don't know where you're getting "months of work" - mine ended up being like 600 lines of Python.

And I don't know what you mean by "no sane way to comprehend SQL" - I guess the millions of data people in the industry are just insane?

Cobbling it together with YAML or JSON is a reasonable trade off if you're in a hurry, but I don't understand how we got to the point where we're throwing out estimates like "writing a parser is months of work" and "it's a PhD project to render some glyphs [1]"

1: https://news.ycombinator.com/item?id=28743687

As far as "comprehend" goes, id think about getting typed responses to sql where I put in an arbitrary query and get a typed output.

Generally, I've only seen ORMs create the query from the object, rather than the object from the query

I don’t understand the problem - is it that you don’t know the result type from just the query? (You need to consider the underlying schema)

Or are you talking about something different?

I'm getting months of work because I need a production-grade system:

- Reasonable performance

- Test infrastructure

- Pretty comprehensive SQL support. A lot of computation happens in the queries.

- Maintainable

- Documented

Most things can be hacked together quickly, but that's different from correct production-grade code.

I was kind of assuming you had those things for a YAML based frontend, and just wanted to implement SQL support.

I can see that if your YAML solution doesn’t have a way to express GROUP BY, so the backend doesn’t support it, then of course that’ll be extra work, but then that’s IMO a different feature.

SQL itself is a tiny language - a parser that transforms it into your YAML based AST really would be pretty small. Here’s the one I made many years ago: https://github.com/google/dotty/blob/master/efilter/parsers/...

It’s not the best quality code, and it doesn’t implement SQL92, but we did run it in production.

My current JSON-based system doesn't implement full SQL semantics. We definitely don't have (or actually need) GROUP BY. Doing full SQL would require ASTs, a query optimizer, and similar. Right now, it's SQL-like, but the implementation is really quite dumb.

What I actually want is the whole dotty / efilter system, only with things like documentation.

We really do care about performance, though. From your code, this would not work:

api.apply("SELECT name FROM users WHERE age > 10", vars={"users": ({"age": 10, "name": "Bob"}, {"age": 20, "name": "Alice"}, {"age": 30, "name": "Eve"}))

It would really need to be:

query = api.compile("SELECT name FROM users WHERE age > 10")

query(vars={"users": ({"age": 10, "name": "Bob"}, {"age": 20, "name": "Alice"}, {"age": 30, "name": "Eve"})))

Or more likely, vars would be a closure which would allow it to interact with the proper data stores.

As a footnote, that looks like a really nice project. I wish it were supported, maintained, and finished.

Could you tell me what you want to accomplish by reimplementing query system? I really curious, because generally query systems used to query data from DB..

I'm working on a platform where data changes in real-time, often every few milliseconds. It's not stored in a DB, and a lot of it is computed on-the-fly.

What I want to query is a perfect fit for SQL. However, it's very much not in a database.

I also don't need a subset of SQL. I would need things like stored procedures and ideally things like virtual tables. There's a lot of computation going on in the queries. The postgres can represent 100% of what I want, but it's a big complex syntax.

In an ideal case, I could pick up pieces and run with them. I think I could reuse parts of things like a query optimizer too.

We write programs with Python, Java, Rust, Javascript and so on. Yet we use a very different language, eg. SQL, to query and modify data. Why? Why don't we use eg. Python as well? SQL is different from other languages: it is declarative, meaning it doesn't dictate how to do it, but what the response should be. Maybe that's the reason? But if that's the case, why are declarative languages not more popular? SQL and other language are not compatible, and that's often a problem. You have this hard border (related to impedance mismatch).

SQL (or GraphQL) is often used as a remote API: The client sends a SQL statement, the server processes it and sends the response. SQL is very powerful, but also dangerous: the statement might be very expensive, for example because an index is missing. Sure, you can shoot yourself in the foot also with Python or Java, but I argue it's harder. SQL is one more technology in your stack, one more thing to learn. And actually, there are many many SQL dialects.

I wish databases have better, faster, and standardized support for a fast procedural language. So that clients can send programs, the server processes it and sends the response. A way to access tables and indexes like a hash table or ordered map. That way, there is no border. There is no slow query due to a missing index. You have to think about how data is access, which indexes are needed. But you have everything under your control. There is no risk of a missing index, or risk of the database not picking the index it should.

(I wrote 3 relational database engines and 4 SQL parsers: HypersonicSQL, H2 database, Apache Jackrabbit Oak, PointBase Micro. I also wrote a GraphQL parser and engine, and a Key-Value store. It's not that I hate SQL.)

The problem with procedural is that it doesn’t optimize very well. The fastest way to get your data for a large number of random queries often depends on the data size, the available indexes, but is also influenced by changes in data size, etc. What is fast for a small dataset might be slow for a larger dataset. The right algorithm also depends on your filters and caching.

It’s almost impossible to write procedural queries that always perform, that is a really hard task. That’s why we use a declarative query language, I tell the database what data I need, and the database optimizer will determine the best performing algorithm to fetch the data based on all dynamic statistics it has.

Don’t underestimate how much hard work the database optimizer takes care of, I’m glad I don’t have to program all of that myself.

Better get used to this way of working, it resembles pretty much how AI assists us.

I have written 4 query optimizers: HypersonicSQL, H2 database, PointBase Micro, and Apache Jackrabbit Oak. I know procedural language don't have such optimizers. But I argue that you don't always need them. I argue that the database engine query optimizer is often more a risk than a help. I have seen missing indexes far too often. SQL doesn't require that the programmer thinks how the data is accessed, so the result is that too many programmers don't think about it, and so don't add indexes, request too much data, and so on. The SQL statements work fine with small (development) data sets, so the same statements are used with production databases, and you run into problems too late.

I don't think it's hard to write procedural queries that are always fast. You write a loop or map/filter/collect method. You just explicitly need to mention which index to use, is all.

Agree on the missing indexes, was in a debugging session yesterday where missing indexes turned out to cause some issues.

But I have little faith that people who forget indexes are capable of writing procedural queries that are always fast. These less experienced devs are the ones that benefit most from the query optimizer doing the hard work. I think the real solution is for database to automatically create indexes where needed, as some database already do. And some better visual query editor like ultorg.

> But if that's the case, why are declarative languages not more popular?

Declarative programming is not very popular because it’s hard to debug. You can’t step through it and inspect intermediate representations. It presents a black box to the developer.

That said, they do seem to show up around certain very complex APIs sometimes.

Besides SQL, CSS is the obvious one.

And I think many parts of otherwise functional/imperative APIs are pseudo-declarative.

For example, React is famously functional in its current form, with render functions and hooks being functions… but then common hooks take a dependency array which is declarative. And at the boundary of these hooks the code is no longer procedural, it disappears into the framework.

> I wish databases have better, faster, and standardized support for a fast procedural language.

That’s a fascinating idea. It would take someone with deep knowledge of a query engine to encapsulate it with a procedural API instead of a declarative one.

That’s not me, but I would love to see it.

not defending sql really but sql is more stable and well defined than operational programming languages, it's somehow of an asset in times of feature / paradigm volatility to me

SQLite somewhat works this way - the frontend compiles the query into bytecode instructions, that the data layer executes procedurally. It would be possible to expose that and write the bytecode directly (but it would take more effort to make it ergonomic.)

I used to love writing SQL, but now I'm very much in the same camp as you. I don't ever want to write SQL, I want to write some straightforward code in my language of choice which allows me to interact with the underlying data structures of the DB; every lookup or aggregation I could do in SQL could definitely be written in a much more clear fashion if it was in Rust, JS, or Python.

I'm hoping that as WASM games momentum and WASI becomes an actual thing, some DB is going to pop up that does just that. I'd like to try myself one day.

querying relational data with set theory is pretty different to expressing imperative programs in a programming language

sql is not a programming language in the traditional sense, it's a mistake to try to think of it in those terms, or demand traditional programming language type stuff from it

Having used (a lot) of Hive SQL, I absolutely do need your query language. SQL is fundamentally incapable of expressing any sort of abstraction, so non-trivial queries quickly become completely incomprehensible, unmaintainable and bug-prone.

Learning a new language is a one-time, up-front cost. Dealing with an awkward, inexpressive query language that integrates poorly with my main language, my types or my interface description languages is an ongoing source of painful friction. Learning something new should not be nearly the barrier to adoption that it seems to be for most people!

SQL's shortcomings seem so clear and omnipresent that I legitimately do not understand why everybody seems so drawn to it. Are the usage patterns for code against a transactional database so different from the sort of Hive queries I've had to deal with for data engineering and machine learning? Is everybody happy with abstractions layered over SQL like ORMs?

Why can't we have, I don't know, some typed variant of Datalog or something instead?

Not that I fundamentally disagree with you, but SQL has been the dominant query language for more than half a century. This lends it some credence for being a quite passable solution, don’t you think?

It's evidence that SQL isn't entirely unusable—but I've worked with too much popular technology to believe it means anything more than that.

Aren't Views SQL's answer to an abstraction?

Because the alternatives are worse. It is that simple.

Check out EdgeDB, you'll like it.

I agree with the premise of the article, I think, but I find the "good SQL" versions... uncompelling.

(1) Switching `left join` to the default inner `join` changes query behavior. I'm guessing it's intentional on the author's part? But it feels like the wrong change to make when trying to compare syntax like-for-like.

(2) I am also in camp "SQL keywords really don't need to be uppercase", so keep fighting the good fight, brother. That said: it's an uphill battle and far from universal. Most SQL "formatters" I've used automatically uppercase everything.

(3) Dropping the alias in `Actors.name AS actor_name` is another case where you're not doing like-for-like. Just using `Actors.name` means, for example, the first example's output table will have two columns: title and name. I'd argue for most uses title and actor_name are better output column names.

Those points aside, the primary simplification seems to be switching `join ... on` to `join ... using`. Big +1 from me on that.

In case anyone is wondering he is talking about EdgeDB (https://www.edgedb.com/)

No. I'm talking about "SQL shaming" and about my preference for SQL over yet-another-query-language.

I have absolutely nothing against EdgeDB or its creators. As far as I can tell, it's a great product.

All your example queries and quotations are from the EdgeDB landing page. Even if you are talking about "SQL shaming" you are very specifically talking about EdgeDB's SQL shaming.

You are missing the point, there's a reason why they don't name the database or link to it. It's a general behaviour that comes up with many new data stores or tools where you can query data. You could replace the images and examples with a different database that does something similar and the point would still stand.

> there's a reason why they don't name the database or link to it

That's a very commonly known technique where you purposefully take only the overly simplified points that you want to counter so that's easy to build arguments or say things like "What can your language offer besides being created in the 2020s?". This is not to say the author or majority of the readers would find what EdgeQL offers, other than being created in the 2020s, valuable but at least you wouldn't be fighting a straw man.

The joy of SQL is that it's so high-level.

Other responses note some (to me) esoteric enterprise use-cases for which SQL may not sufficiently describe exotic data vistas. Sure.

But most of the "shaming" one encounters seems to be about advertising some sort of magic wand product more than pointing out a substantial woe in a system that has been prominent for a half century.

It is, and it isn't. Similar to prolog, you start high level, then have to bend over backwards making your high level description into an implementation aware low level description, by mashing the high level components together into the right spell to make the low level implementation go brr

https://twitter.com/edgedatabase/status/1620582614703964160

EdgeQL:

    select Child {name}
    filter .<child[is Parent].name = 'Uma Thurman';

SQL:

    select child.name
    from child
    join parent_child_rel using (child_id)
    join parent using (parent_id)
    where parent.name = 'Uma Thurman';

IMO EdgeQL is going too far with the sigils

Funny how differently things can be framed.

You can either call SQL proven and battle-tested, or crusty and outdated, depending on your agenda.

Same for the fancy new alternative: It is either fresh and innovative, freed from the shackles of legacy and standard-compliance, or reinventing the wheel in a non-standardized manner.

For data in databases I prefer crusty and outdated

FancyQL, which he mentions, is, of course, EdgeQL – an insanely good query language of EdgeDB.

The truth is EdgeQL is so good that you never want to go back to SQL ever after. It's even a bit depressing when you realize how much time has been spent crafting SQL queries and dancing around it. EdgeQL renders most of those struggles obsolete.

The author of this post has written a book about SQL Window Functions, and probably developed an attachment with his SQL expertise. He probably doesn't need another query language – nobody likes to return to the "beginner" level after their identity has been attached to the "expert" level.

But people who hadn't developed abusive relationships with SQL expertise, they absolutely need "your query language".

Shameless plug – I wrote a post with my experiences with EdgeDB last year. It's a bit outdated already, EdgeDB 3.0 launch is happening next week, but I can only add good things to the post so far.

My experience with EdgeDB (Jul 26, 2022)

https://divan.dev/posts/edgedb/

> So if you’re a hardcore SQL user proud of their 20+ years of SQL experience – don’t try EdgeQL. It’s always hard to downgrade your identity from “master in something overly-complicated” to “newbie in better-and-less-complicated”.

My eyes rolled so hard they actually flipped completely around

The biggest advantage of SQL is that it's so common that if you deal with data a lot you tend to know it well enough. Sure, there are small differences between databases but joins/grouping/window functions tend to work similarly enough.

On the other hand, when I have to do a somewhat complex query in Elasticsearch, or MongoDB, or gorm, or Django ORM, I have to check each time in the docs how it's done.

It's funny how some devs are negative towards SQL but fiercely protective of other much more obscure tools from the 1970s, like unix utilities.

It’s more likely devs are a heterogenous group and you are mixing opinions from unrelated people? Unix utilities from the 1970s are shit. Many of their descendants today aren’t that bad but have lots of problems (eg gawk and gnu sed can do magic in the hands of masters but getting that proficiency is probably not worth the effort).

SQL is a problem not because of the era in which it was developed, but because somehow we haven’t evolved any meaningful successors. We have a bunch of dominant programming languages and only 1 data mining language? What’s up with that? Why is there this pretense around only having one language? Multiple languages are healthy because ideas cross-pollinate. How long after MongoDB did it take database vendors/OSS projects to start adding JSON support to their SQL databases?

Nothing is perfect but given that SQL solves the problem, is ubiquitous, has tons of tooling and educational material, and is extremely mature... It's not going anywhere.

It's not a dinosaur, it's a shark.

Would I like to have something more streamlined and less clunky? Absolutely. But it's going to take a lot of effort for anything to become as ubiquitous as sql.

The irony is that most of those pushing back against SQL are using Javascript, another language that is less than perfect but is winning because of reach.

I felt the same way when Malloy[0] launched. It has some interesting features, but I couldn't see myself ever using it. Nothing makes a big enough difference to spend the time to learn it.

Would love to hear from anybody that's using it regularly

0 - https://www.malloydata.dev/

I would love if sql would support a slight syntax change of accepting

From table select col;

as an optional alternative to

select col from table;

This would allow autocompleting col names in editors.

Other than that I quite like sql being the standard db query language.

That only fixes trivial selects. But you still have issues with e.g. GROUP BY, especially since the dependency is circular:

- barring extensions you can only select grouping expressions or aggregates

- but instead of repeating grouping expressions you can refer to a select expression (by index, some databases also allow the alias)

The "spec" order of evaluation for queries is WITH, FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, merge, ORDER BY, LIMIT. Although databases might decide to move SELECT after ORDER BY and LIMIT if they can, in order to avoid unnecessary evaluations.

> That only fixes trivial selects

Absolutely true. But a huge amount of queries are in fact trivial selects. And this change alone would make autocompleting them easy for various editors/IDEs.

Perfect is the enemy of the good, etc.

You can easily change it to make `group by` behave much better, and `having` redundant with `where`, like it should always have been if you just evaluate from start to end, without any hidden reordering.

> You can easily change it to make `group by` behave much better

Change what? Make "group by" behave better how?

> `having` redundant with `where`

They filter different things, how do you make `where` perform both jobs?

> like it should always have been if you just evaluate from start to end, without any hidden reordering.

The only "hidden reordering" is an optimisation.

Have a look at https://prql-lang.org/. They got this right.

> Have a look at https://prql-lang.org/. They got this right.

Yes, they absolutely got this right. But I agree with the author of TFA in that I don't want another query language. I just want this specific change to SQL. Maybe others as well. But I do not want another query language. PRQL is yet another query language.

This honestly feels like a great advertisement for fancy-ql. Writing queries that take the form of the dataset you want back is awesome.

Query language for database queries! I thought the argument were going to be against application query languages, like the JQL for Jira and so on, which I actually like.

Ugh I hate those. I’ve been using Jira for nearly a decade and have written numerous filters for dashboards, etc and I always have to look up the bespoke nuances of JQL.

Recently I was burned by a bad query in Google Log Explorer. There was no feedback my query was wrong, just no data.

The ecosystem of tools and learning resources around SQL is so large that I think generally any FancyQL is a liability. It would need to bring a 10x improvement over SQL and that is hard to believe.

However, I’d have said the same about JS a few years ago, and now we have TypeScript. Perhaps a language that is a strict superset of SQL and that compiles to SQL might be something worth trying.

Probably end up with English powered by LLMs

"You can write SQL queries in lower case"

I know this, but I can't. Muscle Memory, even though I don't use SQL that much.

I got started in I.T. at the end of the 70s and all the caps in SQL definitely put me off because well you know it's ALL SHOUTING. Unix was calm: Unix was lower case. IBM JCL WAS SHOUTY TOO.

sickos.jpg YES!

I can't count the number of services or things I've had to use which invent their own query language instead of using SQL. In every case the language is empirically worse than SQL, especially when it's some mangled hybrid to make things "easier" coughNRQLcough. The silliest part is, if we all just fucking used SQL the tooling and integration would be much fucking easier and/or free. Not to mention, in almost no organization will you have the time or resources to not make something that's a half-implemented, poorly spec'd, rubbish version of SQL.

I think the biggest problem with SQL is that folks feel like they don't need to know it or that it's not useful. Oh dear beebs, it's fucking useful. Next time I personally need to build a rich query interface, I'm just using row level security and opening up Postgres.

Lovely, right on the subject of all those wannabe replacements.

people who intentionally choose sql databases often make every effort to avoid writing any of the stuff by hand, which should say something about how much of the value proposition lies in the language itself

> SQL has a solid standards committee that maintains and improves it.

so does c++. so does javascript. so does cobol

If this is about NoSQL databases, I dont think SQL is useful for databases which does not follow first normal form. But any alternative to SQL for relational databases will fight an uphill battle. While SQL is somewhat clunky, it is also deeply entrenched.

SQL selection works perfectly on tables in poor normal forms. If you have the columns you need to query pre-joined into the table you’re querying, you just skip the joins. Updates are what gets fun if you don’t have normal form.

First normal form disallows nested tables and SQL does not support querying nested tables.

Datomic's Datalog is much better but it's not easy to unlearn SQL.

Perhaps I’m lucky, but I’ve never experienced SQL shaming. What I have experienced is referencing shaming, where I’m allowed to write SQL, but all table references in that SQL need to come from the model instead of being hard-coded in the SQL. I suppose it’s nice to have all the join tables’ models being included in the file. It makes it easy for a search to find all the usages in case there is a big refactor. It also makes the SQL look a lot more complicated then it really is and a lot less clean then these examples- at least in the code.

Actually SQL is pretty cool and by using concatenative concepts, you could do some pretty cool stuff.

We built a datascience tool to quickly build data apps which can be extended from the frontend, including data wrangling and datascience functions. Most exciting part was using the PostgreSQL and SQL to process, clean and enhance data and write extensions and bring it all together.

We open sourced alpha version yesterday, more documentation to come.

https://github.com/rebataur/rapidiam

I was exposed to Kusto Query Language this week. I used it to query logs in Azure. At first, I thought "what? Another query language to learn? " But I find myself liking it. It reminds me of ML style piping expressions, and it's very explicitly lays out how each operator works on top of previous clauses, which makes it very clear how and when each clause works. I'm assuming / hoping the queries that are actually executed are deferred - I would think they would have to be!

I agree with many points, however, it depends on the abstraction that you need and the abstraction depends on the architecture you are adopting.

For example, if you are doing DDD and your repository implementation is about SQL, adding another layer of abstraction is not worth. But if your design is less sophisticated, or you are in an early stage of the project, you may find appealing to use that abstraction.

Maybe I didn't grok this article but all the examples made me think "fancyql" (as it calls it) looks a lot better than SQL.

If I could compile back and forth between SQL and "fancyql" then using fancyql feels like an absolute no brainer to me?

I'm a lot less sympathetic to the "everyone already knows it" argument after dealing with SQL queries that are many hundred lines long.

Why not both. If kibana supported sql that would be cool in addition to it’s 2 (or more) distinct syntaxes.

The issue is with non relational dbs like redshift that do support sql it is very easy to write an innocent query that takes hours to run if you don’t use specific keys in the query (ones used for sharding). But then some kind of warning or query plan indication would help there.

I feel like this entire debate is strongly influenced, if not soon made obsolete and pointless, by the presence of the so called AI tools.

Feels like there's a universe of difference between the experience of "carefully craft the query yourself" and "describe the query and let AI write the code for it."

oh let's just see that world where "AI" is "writing" the SQL queries and see how that goes.

LLMs work by hallucinating mashups of existing code examples stolen from webpages. Writing correct SQL for complex cases definitely needs real understanding, not just elaborate typeahead.

Oh, I agree, I meant to add something like "after the fact, a human needs to inspect." But it still feels like this would speed up time dramatically.

But see https://www.scattered-thoughts.net/writing/against-sql/

> I don't need your query language

With LLM's, no one is ever going to need any query language starting effing now.

And good riddance to all of them too, I've yet to see one that made any kind of sense from the ease of use perspective.

This is like an airline startup offering you to fly on their much better, in-house designed/built aircraft. I think it's cool but no thank you, maybe I'll check back in 10-20 years to see where they are.

Especially the last point makes me realize that many frustrations with SQL are transferred frustrations with the suits (from the 70s or not) that we're all working for, and the company culture they've created

I feel exactly the same way about ActiveRecord ORMs. That’s why I created PluSQL:

https://github.com/iaindooley/PluSQL

What happened to Datalog?

I was hoping for a Datalog shoutout.

I like SQL, but it has a few disadvantages compared to Datalog.

- In Datalog, queries and the data itself are homoiconic; the structure for both is exactly the same. Uses first-class variables to represent unknowns.

- Easy to compose queries (just tack on another predicate)

Of course it has the disadvantages that it's not widely supported (outside of Datomic/Clojure/Prolog ecosystem?), and not as popular, and maybe even more difficult to optimize [citation needed].

I would love to see a new Datalog-based DB. SQL-but-slightly-different-syntax is "lipstick on a pig" as they say, not very compelling IMO (and not really novel either, you can see it reinvented in every ORM).

I used to bash SQL. Then I learned how to use it.

I wonder if the author has tried CodeQL? One of the best query languages I've ever used, even if it is very domain-specific.

How about nested selection ? SQL is dump because it doesn't allow nested selection by default.

I'd also stick to SQL until my boss says we'll be using something else because is cheaper.

In the lists of most popular languages, SQL always to seem up at the top.

SQL got the order of key words wrong. Instead of

„Select … from …“

it would be much better to write

„From … select …“.

PRQL got that right: https://prql-lang.org/

Reply on news.ycombinator.com