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)
> 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
>> 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'.
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.
> 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.
> 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.
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.
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.
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.
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.
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 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.
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 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.
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".
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.
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.
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.
> 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.
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.
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.
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.
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.
`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
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.
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.
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?
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
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.
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.
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
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 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?
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.
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.)
> 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.
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.
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.
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.)
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
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?
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.
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
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.
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.
> 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 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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 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).
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...