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.
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
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.
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.
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.
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?
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.
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 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.
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?)
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.
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.
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.
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 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.
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".
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.
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.
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.
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.
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.
> 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.
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.)
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.
`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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
> 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".
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.
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 ()
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
> 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
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.
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.
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.
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.
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.
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.
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]"
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.
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")
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.
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 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?
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.
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.
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
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.
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.
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.
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/
Maintainability is not relevant to ad-hoc analytics queries. That may be dealt with once the correct query has been determined from sufficient iteration.
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?
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?
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.
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.
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.
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.
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.
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.
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.
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.
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
303 comments
[ 3.6 ms ] story [ 289 ms ] threadI have absolutely nothing against EdgeDB or its creators. As far as I can tell, it's a great product.
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.
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.
EdgeQL:
SQL: IMO EdgeQL is going too far with the sigilsRecently I was burned by a bad query in Google Log Explorer. There was no feedback my query was wrong, just no data.
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.
You know I think you're right.
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.
FROM users WHERE name LIKE 'a%' SELECT name, id, location
(I should refresh the page before posting)
var result = from s in stringList where s.Contains("Tutorials") select s;
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.
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.
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"
——
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?)
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.
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.
No love for let?
...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.
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.
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.
Linq query syntax is just dumb
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.
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.
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
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.
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.
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.
It's been an alternative in Postgres for decades.
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.
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.)
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 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.
You have a library of useful joins that you've checked for correctness. Saving the library as reusable code would be even more useful.
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.
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).
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.
[0] https://kakoune.org
I mean, if you hit v first, that's exactly what it does.
Or is this just reversing the order so it's {motion}{verb} instead of {verb}{motion}?
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.)
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/
The situation could certainly be better, but at least this works today.
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.
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.
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.
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.
I just added it as a separate point, to say "you can have autocomplete no matter the order in which you write your query"...
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.
Is it ideal? No. But it's muscle memory now, so...
¯\_(ツ)_/¯
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.
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.
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.
It makes more sense to have the select last.
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.
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).
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.
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
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.
I wholeheartedly agree that engineers massively underestimate the power of Excel.
Nothing seems more ephemeral than JavaScript framework du jour.
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'.
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...
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.
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 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.
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
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.
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
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.
I'm just pointing out it doesn't achieve that.
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.
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?
If the biggest issue in my product is JSON deser, I'd be a happy camper.
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.
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.
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).
A straw man argument you've pulled out of thin air.
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.
Doing that means losing foreign-key referential integrity...
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.
For anyone (like me) who is not quite able to visualize this, here is an example:
From StackOverflow user S-man https://stackoverflow.com/a/53087015I'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.
A good editor (DataGrip) helps.
[1] https://www.graphile.org/postgraphile/
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.
It's nice being able to use datetimes, 64 bit ints, binary, etc
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.
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.
This is utter nonsense. There is absolutely nothing about microservices architecture that relates to performing JOINs in SQL.
It should be a single INSERT, but through an ORM that multiplies it. The only upside of Kafka is not having the ORM…
That's being said, Kafka is not one of them.
That doesn't help with conditional-predicates - that's another major shortcoming in SQL.
ORMs 100% always do it worse
https://docs.sqlalchemy.org/en/14/orm/loading_relationships....
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.
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
Generally, I've only seen ORMs create the query from the object, rather than the object from the query
Or are you talking about something different?
- 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 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.
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.
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.
People pay a lot of money for kdb so clearly they see some value in it despite the lack of sql.
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?
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.
> SQL has a solid standards committee that maintains and improves it.
so does c++. so does javascript. so does cobol
Would love to hear from anybody that's using it regularly
0 - https://www.malloydata.dev/
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.
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:
Which is neither pleasant to write nor iterate on interactively.With something like dplyr instead:
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...
https://antonz.org/cte/
Ex:
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?
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.
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.
- 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.
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.
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.
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.
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.