19 ms·

I don't need your query language

330 points by polyrand 3y ago

alecco 3y ago
Datomic's Datalog is much better but it's not easy to unlearn SQL.
ayhanfuat 3y ago
In case anyone is wondering he is talking about EdgeDB (https://www.edgedb.com/ https://www.edgedb.com/)
- nalgeon 3y ago
  No. I'm talking about "SQL shaming" and about my preference for SQL over yet-another-query-language. I have absolutely nothing against EdgeDB or its creators. As far as I can tell, it's a great product.
  - ayhanfuat 3y ago
    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.
    - dewey 3y ago
      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.
      - ayhanfuat 3y ago
        > 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.
  - smitty1e 3y ago
    The joy of SQL is that it's so high-level. Other responses note some (to me) esoteric enterprise use-cases for which SQL may not sufficiently describe exotic data vistas. Sure. But most of the "shaming" one encounters seems to be about advertising some sort of magic wand product more than pointing out a substantial woe in a system that has been prominent for a half century.
    - 8note 3y ago
      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
- bokwoon 3y ago
  https://twitter.com/edgedatabase/status/1620582614703964160 https://twitter.com/edgedatabase/status/1620582614703964160 EdgeQL: select Child {name} filter .<child[is Parent].name = 'Uma Thurman'; SQL: select child.name from child join parent_child_rel using (child_id) join parent using (parent_id) where parent.name = 'Uma Thurman'; IMO EdgeQL is going too far with the sigils
egeozcan 3y ago
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.
- ellisv 3y ago
  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.
LunicLynx 3y ago
The main issue with sql is, that it is the wrong way around, which eliminates all tooling support. You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that. So switching this, moving from and joins in front of select, might be everything needed to fix sql.
- XorNot 3y ago
  FROM users SELECT name, id, location WHERE name LIKE 'a%'; You know I think you're right.
  - frogulis 3y ago
    Go further: `FROM users WHERE name LIKE 'a%' SELECT name, id, location` - after all, you can WHERE on things that aren't projected by SELECT
    - deleted 3y ago
      [deleted]
    - 6510 3y ago
      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.
  - user3939382 3y ago
    There was an article on HN about this maybe 6 months ago. I’m in agreement.
  - ellisv 3y ago
    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.
bazoom42 3y ago
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.
- Olreich 3y ago
  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.
  - bazoom42 3y ago
    First normal form disallows nested tables and SQL does not support querying nested tables.
jrumbut 3y ago
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).
- nickpeterson 3y ago
  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.
  - sp33der89 3y ago
    > that JavaScript framework that takes a year to understand will no longer be used in 7 years. Surely there is a lot more nuance for having a nicer database query language than comparing it to JS frontend practices. SQL is something that'll stick for a long time, but I support attempts from people that don't want to let SQL be the endgame. Of course don't go around deploying highly experimental shiny things on production! :P
    - forgetfreeman 3y ago
      Nuance? Not really. Bullshit flash in the pan tech stacks are what they are regardless of how much marketing drapery one adorns them with.
  - cratermoon 3y ago
DaiPlusPlus 3y ago
SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows. And there are often problems when using multiple SELECT queries in a batched statement: you can't re-use existing CTE queries. Not all client libraries support multiple result-sets. It's essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn't even support named result sets...), which means you can't opportunistically skip or omit a SELECT query in a batch because your client reader won't know how to parse/interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you'll run into concurrency issues if data changes between SELECT queries in the same batch () [1] https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#avoid-cartesian-explosion-when-loading-related-entities https://learn.microsoft.com/en-us/ef/core/performance/effici... and https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries https://learn.microsoft.com/en-us/ef/core/querying/single-sp...
- gozzoo 3y ago
  Isn't your comment judging a fish by its ability to climb a tree?
  - post-it 3y ago
    If the task is to climb a tree, it's reasonable to not hire a fish.
    - eimrine 3y ago
      https://www.google.com/search?q=can+fish+climb+tree https://www.google.com/search?q=can+fish+climb+tree
quickthrower2 3y ago
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.
frognumber 3y ago
I'm working on a query language right now! Why not SQL? Lack of tooling for SQL. Yes, SQL lacks tooling. There's a ton of stuff to build a SQL client, obviously. However, on the other side: - I have no sane way to parse SQL - I have no sane way to comprehend SQL Writing a SQL query system would be many months of work. Tossing together a good-enough query language with standards like JSON or YAML means I can json.loads(query) in Python and JSON.parse in JavaScript. SQL would be an ideal fit if there was good tooling, and it fits in more places than most people realize. Web API query a whole bunch of stuff stored in all sorts of complex ways. SQL is better on paper than RESTful / AJAXy / GraphQL / etc. APIs. It's not better if it means that the query language takes more time to build out than the entire rest of the system. TL;DR: If you want to build a high-visibility open-source project and guarantee employment for the rest of your life, an elegant SQL parser, especially for building web APIs, would be a great thing to do.
- mcdonje 3y ago
  I'm generally on the side of OP, but this is reasonable.
- t8sr 3y ago
  This comment is full of hyperbole. Writing a precedence-climbing SQL parser should take a few days. I know because I've done it. I don't know where you're getting "months of work" - mine ended up being like 600 lines of Python. And I don't know what you mean by "no sane way to comprehend SQL" - I guess the millions of data people in the industry are just insane? Cobbling it together with YAML or JSON is a reasonable trade off if you're in a hurry, but I don't understand how we got to the point where we're throwing out estimates like "writing a parser is months of work" and "it's a PhD project to render some glyphs [1]" 1: https://news.ycombinator.com/item?id=28743687 https://news.ycombinator.com/item?id=28743687
  - 8note 3y ago
    As far as "comprehend" goes, id think about getting typed responses to sql where I put in an arbitrary query and get a typed output. Generally, I've only seen ORMs create the query from the object, rather than the object from the query
0x69420 3y ago
people who intentionally choose sql databases often make every effort to avoid writing any of the stuff by hand, which should say something about how much of the value proposition lies in the language itself > SQL has a solid standards committee that maintains and improves it. so does c++. so does javascript. so does cobol
RyanHamilton 3y ago
Show me an elegant SQL version for the queries in this article: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ 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.
- iLoveOncall 3y ago
  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.
  - dan-robertson 3y ago
    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.
  - rak1507 3y ago
    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.
interleave 3y ago
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?
n_e 3y ago
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.
move-on-by 3y ago
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.
dan-robertson 3y ago
I don’t really find the examples convincing. Like, I get that sql could maybe be written in a slightly less horrid way but I would prefer something a lot less horrid. I think I’m much more motivated by analytics queries than the kinds of thing in this example though. I find sql is poorly suited in this case because it is verbose and written backwards, and often requires many layers of subqueries. That said, one can usually still express queries in SQL that other systems do not allow. For these kinds of queries I think there are just better ways to express them. Another issue with sql is that has some quite strange semantics.[1] An example query I wrote yesterday is: select group, min, max, (max-min)/1e9 range from (select group, min(size) min, max(size) max from (select time, instance, sum(size) size, regexp_replace(name,…) group from X group by regexp_replace(name,…), time, instance) group by group) order by range desc limit 10 Which is neither pleasant to write nor iterate on interactively. With something like dplyr instead: X %>% mutate(group=regexp_replace(name,…)) %>% group_by(group,time,instance) %>% summarize(size=sum(size)) %>% group_by(group) %>% summarize(min=min(size),max=max(size),range=(min-max)/1e9) %>% arrange(-range) %>% head(n=10) And that can be built up interactively pretty easily by adding onto the end of the pipeline. I would also note that, due to sql being painful, the query is not exactly the one I wanted and instead I would have wanted something better capturing the change over time, but the thought of doing that in SQL seemed too unpleasant. An example of an actual query language that tries to be better for analytics: https://prql-lang.org/ 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 https://www.scattered-thoughts.net/writing/against-sql and just on semantics: https://www.scattered-thoughts.net/writing/select-wat-from-sql/ https://www.scattered-thoughts.net/writing/select-wat-from-s...
- nalgeon 3y ago
  I think the problem here is with the query, not the language. You can immediately improve its maintainability and readability by using CTEs. https://antonz.org/cte/ https://antonz.org/cte/
  - rjbwork 3y ago
    This exactly the comment I was going to leave. If I get past one sub query, I will refactor to CTE.
mritchie712 3y ago
I felt the same way when Malloy[0] launched. It has some interesting features, but I couldn't see myself ever using it. Nothing makes a big enough difference to spend the time to learn it. Would love to hear from anybody that's using it regularly 0 - https://www.malloydata.dev/ https://www.malloydata.dev/
civilized 3y ago
It's funny how some devs are negative towards SQL but fiercely protective of other much more obscure tools from the 1970s, like unix utilities.
- vlovich123 3y ago
  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?
bad_alloc 3y ago
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.
mcs_ 3y ago
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.
hcks 3y ago
"You can write SQL queries in lower case"
- SushiHippie 3y ago
  I know this, but I can't. Muscle Memory, even though I don't use SQL that much.
- euroderf 3y ago
  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.
hosteur 3y ago
I would love if sql would support a slight syntax change of accepting From table select col; as an optional alternative to select col from table; This would allow autocompleting col names in editors. Other than that I quite like sql being the standard db query language.
- masklinn 3y ago
  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.
  - hosteur 3y ago
    > That only fixes trivial selects Absolutely true. But a huge amount of queries are in fact trivial selects. And this change alone would make autocompleting them easy for various editors/IDEs. Perfect is the enemy of the good, etc.
  - marcosdumay 3y ago
    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.
    - masklinn 3y ago
      > You can easily change it to make `group by` behave much better Change what? Make "group by" behave better how? > `having` redundant with `where` They filter different things, how do you make `where` perform both jobs? > like it should always have been if you just evaluate from start to end, without any hidden reordering. The only "hidden reordering" is an optimisation.
vunoo 3y ago
I wonder if the author has tried CodeQL? One of the best query languages I've ever used, even if it is very domain-specific.
pjmlp 3y ago
Lovely, right on the subject of all those wannabe replacements.
tudorg 3y ago
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.
- manojlds 3y ago
  Probably end up with English powered by LLMs
wwilim 3y ago
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
OliverJones 3y ago
As an experienced (===old) developer, I have learned that data long outlasts the programs that access it. The lifetime of data is measured in decades, but programs last for years. Most SQL-based RDBMS teams have figured out workable version migration paths allowing old data to run on newer servers. Because this kind of migration is a very common and economically valuable operation, the vendors make sure it works correctly. Sometimes a project, especially a greenfield project, looks like it will benefit from more recently invented data storage and query tech than your grandmother's SQL. That's always possible. And as developers we hope for, and work for, continued progress. But consider what may happen when the project succeeds. If you're still on the project, you'll wake up one day and realize your oldest data is 20 years old. What happens if your storage and query engines are also 20 years old, because they didn't succeed to the extent needed to pay for maintenance and upgrades? You'll be in the software equivalent of the century-old subway system where you have to make all your replacement parts yourself, or get gouged by vendors that can't spread their costs among many customers. Build for the ages, not for the moment!
rubyfan 3y ago
I used to bash SQL. Then I learned how to use it.
bshacklett 3y ago
This honestly feels like a great advertisement for fancy-ql. Writing queries that take the form of the dataset you want back is awesome.
dools 3y ago
I feel exactly the same way about ActiveRecord ORMs. That’s why I created PluSQL: https://github.com/iaindooley/PluSQL https://github.com/iaindooley/PluSQL
pier25 3y ago
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.
- bdcravens 3y ago
  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.
SeanLuke 3y ago
What happened to Datalog?
- grose 3y ago
  I was hoping for a Datalog shoutout. I like SQL, but it has a few disadvantages compared to Datalog. - In Datalog, queries and the data itself are homoiconic; the structure for both is exactly the same. Uses first-class variables to represent unknowns. - Easy to compose queries (just tack on another predicate) Of course it has the disadvantages that it's not widely supported (outside of Datomic/Clojure/Prolog ecosystem?), and not as popular, and maybe even more difficult to optimize [citation needed]. I would love to see a new Datalog-based DB. SQL-but-slightly-different-syntax is "lipstick on a pig" as they say, not very compelling IMO (and not really novel either, you can see it reinvented in every ORM).
Mizoguchi 3y ago
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.
rubyn00bie 3y ago
sickos.jpg YES! I can't count the number of services or things I've had to use which invent their own query language instead of using SQL. In every case the language is empirically worse than SQL, especially when it's some mangled hybrid to make things "easier" coughNRQLcough. The silliest part is, if we all just fucking used SQL the tooling and integration would be much fucking easier and/or free. Not to mention, in almost no organization will you have the time or resources to not make something that's a half-implemented, poorly spec'd, rubbish version of SQL. I think the biggest problem with SQL is that folks feel like they don't need to know it or that it's not useful. Oh dear beebs, it's fucking useful. Next time I personally need to build a rich query interface, I'm just using row level security and opening up Postgres.
galaxyLogic 3y ago
The problem with SQL is that it is not a (very) composable language. The documentation for EdgeDb goes into some detail about that and shows an alternative better language for data-queries. https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql To understand why SQL is bad, you must first be shown something better, and EdgeDb seems to be such better more composable language.
- IanCal 3y ago
  Malloy is another thing to check out in this space https://www.malloydata.dev/ https://www.malloydata.dev/
- nalgeon 3y ago
  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).
  - default-kramer 3y ago
    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.
    - camgunz 3y ago
      The answers are stored procedures and templates. People argue against them, but they're basically the data versions of software affordances we already have.
- fbdab103 3y ago
benrutter 3y ago
Maybe I didn't grok this article but all the examples made me think "fancyql" (as it calls it) looks a lot better than SQL. If I could compile back and forth between SQL and "fancyql" then using fancyql feels like an absolute no brainer to me? I'm a lot less sympathetic to the "everyone already knows it" argument after dealing with SQL queries that are many hundred lines long.
raybpurchase 3y ago
I'd also stick to SQL until my boss says we'll be using something else because is cheaper.
Hendrikto 3y ago
Funny how differently things can be framed. You can either call SQL proven and battle-tested, or crusty and outdated, depending on your agenda. Same for the fancy new alternative: It is either fresh and innovative, freed from the shackles of legacy and standard-compliance, or reinventing the wheel in a non-standardized manner.
- croes 3y ago
  For data in databases I prefer crusty and outdated
rebataur 3y ago
Actually SQL is pretty cool and by using concatenative concepts, you could do some pretty cool stuff. We built a datascience tool to quickly build data apps which can be extended from the frontend, including data wrangling and datascience functions. Most exciting part was using the PostgreSQL and SQL to process, clean and enhance data and write extensions and bring it all together. We open sourced alpha version yesterday, more documentation to come. https://github.com/rebataur/rapidiam https://github.com/rebataur/rapidiam
divan 3y ago
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".
- divan 3y ago
  Shameless plug – I wrote a post with my experiences with EdgeDB last year. It's a bit outdated already, EdgeDB 3.0 launch is happening next week, but I can only add good things to the post so far. My experience with EdgeDB (Jul 26, 2022) https://divan.dev/posts/edgedb/ https://divan.dev/posts/edgedb/
  - llimllib 3y ago
    > 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
bob1029 3y ago
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.
- Jenk 3y ago
  The "domain" in the DSL of SQL is "relational data" not "your business domain"
  - bob1029 3y ago
    What does this "relational data" (hopefully) represent?
    - preseinger 3y ago
      it doesn't matter what the data represents, what matters is the structure of that data, and specifically (for SQL) that it is relational, rather than key-value or document-oriented or whatever many business domains are well-modeled by relational data some are not
jmartrican 3y ago
In the lists of most popular languages, SQL always to seem up at the top.
thomasmg 3y ago
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.)
- ako 3y ago
  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.
karmakaze 3y ago
> Here is another common argument: SQL was designed with 1970s businessmen in mind, and it shows. That is a funny way of looking at it. I see that SQL is based on the work of a computer scientist vs DSLs being made by hobbyists, and it shows.
- deleted 3y ago
  [deleted]
ur-whale 3y ago
> I don't need your query language With LLM's, no one is ever going to need any query language starting effing now. And good riddance to all of them too, I've yet to see one that made any kind of sense from the ease of use perspective.
amai 3y ago
SQL got the order of key words wrong. Instead of „Select … from …“ it would be much better to write „From … select …“. PRQL got that right: https://prql-lang.org/ https://prql-lang.org/
tikhonj 3y ago
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?
- 9dev 3y ago
  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?
  - tikhonj 3y ago
    It's evidence that SQL isn't entirely unusable—but I've worked with too much popular technology to believe it means anything more than that.
- alexchamberlain 3y ago
  Aren't Views SQL's answer to an abstraction?
- jeltz 3y ago
  Because the alternatives are worse. It is that simple.
- 1st1 3y ago
  Check out EdgeDB, you'll like it.
revskill 3y ago
How about nested selection ? SQL is dump because it doesn't allow nested selection by default.
kagevf 3y ago
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!
rwiggins 3y ago
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.
jrm4 3y ago
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."
- zzzeek 3y ago
  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.
  - jrm4 3y ago
    Oh, I agree, I meant to add something like "after the fact, a human needs to inspect." But it still feels like this would speed up time dramatically.
asylteltine 3y ago
[dead]
lee101 3y ago
[dead]
amai 3y ago
But see https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/

330 points by polyrand 3y ago

alecco 3y ago

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

ayhanfuat 3y ago

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

nalgeon 3y ago
No. I'm talking about "SQL shaming" and about my preference for SQL over yet-another-query-language. I have absolutely nothing against EdgeDB or its creators. As far as I can tell, it's a great product.
- ayhanfuat 3y ago
  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.
  - dewey 3y ago
    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.
    - ayhanfuat 3y ago
      > 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.
- smitty1e 3y ago
  The joy of SQL is that it's so high-level. Other responses note some (to me) esoteric enterprise use-cases for which SQL may not sufficiently describe exotic data vistas. Sure. But most of the "shaming" one encounters seems to be about advertising some sort of magic wand product more than pointing out a substantial woe in a system that has been prominent for a half century.
  - 8note 3y ago
    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
bokwoon 3y ago
https://twitter.com/edgedatabase/status/1620582614703964160 https://twitter.com/edgedatabase/status/1620582614703964160 EdgeQL: select Child {name} filter .<child[is Parent].name = 'Uma Thurman'; SQL: select child.name from child join parent_child_rel using (child_id) join parent using (parent_id) where parent.name = 'Uma Thurman'; IMO EdgeQL is going too far with the sigils

egeozcan 3y ago

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.

ellisv 3y ago
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.

LunicLynx 3y ago

The main issue with sql is, that it is the wrong way around, which eliminates all tooling support. You need to state what you want (select a, b, c) before you tell it from where to get it (from). And no tooling can predict that. So switching this, moving from and joins in front of select, might be everything needed to fix sql.

XorNot 3y ago
FROM users SELECT name, id, location WHERE name LIKE 'a%'; You know I think you're right.
- frogulis 3y ago
  Go further: `FROM users WHERE name LIKE 'a%' SELECT name, id, location` - after all, you can WHERE on things that aren't projected by SELECT
  - deleted 3y ago
    [deleted]
  - 6510 3y ago
    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.
- user3939382 3y ago
  There was an article on HN about this maybe 6 months ago. I’m in agreement.
- ellisv 3y ago
  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.

bazoom42 3y ago

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.

Olreich 3y ago
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.
- bazoom42 3y ago
  First normal form disallows nested tables and SQL does not support querying nested tables.

jrumbut 3y ago

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).

nickpeterson 3y ago
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.
- sp33der89 3y ago
  > that JavaScript framework that takes a year to understand will no longer be used in 7 years. Surely there is a lot more nuance for having a nicer database query language than comparing it to JS frontend practices. SQL is something that'll stick for a long time, but I support attempts from people that don't want to let SQL be the endgame. Of course don't go around deploying highly experimental shiny things on production! :P
  - forgetfreeman 3y ago
    Nuance? Not really. Bullshit flash in the pan tech stacks are what they are regardless of how much marketing drapery one adorns them with.
- cratermoon 3y ago

DaiPlusPlus 3y ago

SQL does have a significant drawback w.r.t. how databases are used today (imo): a SELECT query can only return a single resultset of uniform tuples: if you want to query a database for hetereogenous types with differing multiplicity (i.e. an object-graph) then you either have to use multiple SELECT queries for each object-class - or use JOINs which will result in the Cartesian Explosion problem[1] which also results in redundant output data due to the multiplicity mismatch - SQL JOINs also lack the ability to error-out early if the JOIN matches an unexpected number of rows. And there are often problems when using multiple SELECT queries in a batched statement: you can't re-use existing CTE queries. Not all client libraries support multiple result-sets. It's essentially impossible to return metadata associated with a resultset (T-SQL and TDS doesn't even support named result sets...), which means you can't opportunistically skip or omit a SELECT query in a batch because your client reader won't know how to parse/interpret an out-of-order resultset, and most importantly: you need to be careful w.r.t. transactions otherwise you'll run into concurrency issues if data changes between SELECT queries in the same batch () [1] https://learn.microsoft.com/en-us/ef/core/performance/efficient-querying#avoid-cartesian-explosion-when-loading-related-entities https://learn.microsoft.com/en-us/ef/core/performance/effici... and https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries https://learn.microsoft.com/en-us/ef/core/querying/single-sp...

gozzoo 3y ago
Isn't your comment judging a fish by its ability to climb a tree?
- post-it 3y ago
  If the task is to climb a tree, it's reasonable to not hire a fish.
  - eimrine 3y ago
    https://www.google.com/search?q=can+fish+climb+tree https://www.google.com/search?q=can+fish+climb+tree

quickthrower2 3y ago

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.

frognumber 3y ago

I'm working on a query language right now! Why not SQL? Lack of tooling for SQL. Yes, SQL lacks tooling. There's a ton of stuff to build a SQL client, obviously. However, on the other side: - I have no sane way to parse SQL - I have no sane way to comprehend SQL Writing a SQL query system would be many months of work. Tossing together a good-enough query language with standards like JSON or YAML means I can json.loads(query) in Python and JSON.parse in JavaScript. SQL would be an ideal fit if there was good tooling, and it fits in more places than most people realize. Web API query a whole bunch of stuff stored in all sorts of complex ways. SQL is better on paper than RESTful / AJAXy / GraphQL / etc. APIs. It's not better if it means that the query language takes more time to build out than the entire rest of the system. TL;DR: If you want to build a high-visibility open-source project and guarantee employment for the rest of your life, an elegant SQL parser, especially for building web APIs, would be a great thing to do.

mcdonje 3y ago
I'm generally on the side of OP, but this is reasonable.
t8sr 3y ago
This comment is full of hyperbole. Writing a precedence-climbing SQL parser should take a few days. I know because I've done it. I don't know where you're getting "months of work" - mine ended up being like 600 lines of Python. And I don't know what you mean by "no sane way to comprehend SQL" - I guess the millions of data people in the industry are just insane? Cobbling it together with YAML or JSON is a reasonable trade off if you're in a hurry, but I don't understand how we got to the point where we're throwing out estimates like "writing a parser is months of work" and "it's a PhD project to render some glyphs [1]" 1: https://news.ycombinator.com/item?id=28743687 https://news.ycombinator.com/item?id=28743687
- 8note 3y ago
  As far as "comprehend" goes, id think about getting typed responses to sql where I put in an arbitrary query and get a typed output. Generally, I've only seen ORMs create the query from the object, rather than the object from the query

0x69420 3y ago

people who intentionally choose sql databases often make every effort to avoid writing any of the stuff by hand, which should say something about how much of the value proposition lies in the language itself > SQL has a solid standards committee that maintains and improves it. so does c++. so does javascript. so does cobol

RyanHamilton 3y ago

Show me an elegant SQL version for the queries in this article: https://www.timestored.com/b/kdb-qsql-query-vs-sql/ 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.

iLoveOncall 3y ago
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.
- dan-robertson 3y ago
  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.
- rak1507 3y ago
  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.

interleave 3y ago

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?

n_e 3y ago

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.

move-on-by 3y ago

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.

dan-robertson 3y ago

I don’t really find the examples convincing. Like, I get that sql could maybe be written in a slightly less horrid way but I would prefer something a lot less horrid. I think I’m much more motivated by analytics queries than the kinds of thing in this example though. I find sql is poorly suited in this case because it is verbose and written backwards, and often requires many layers of subqueries. That said, one can usually still express queries in SQL that other systems do not allow. For these kinds of queries I think there are just better ways to express them. Another issue with sql is that has some quite strange semantics.[1] An example query I wrote yesterday is: select group, min, max, (max-min)/1e9 range from (select group, min(size) min, max(size) max from (select time, instance, sum(size) size, regexp_replace(name,…) group from X group by regexp_replace(name,…), time, instance) group by group) order by range desc limit 10 Which is neither pleasant to write nor iterate on interactively. With something like dplyr instead: X %>% mutate(group=regexp_replace(name,…)) %>% group_by(group,time,instance) %>% summarize(size=sum(size)) %>% group_by(group) %>% summarize(min=min(size),max=max(size),range=(min-max)/1e9) %>% arrange(-range) %>% head(n=10) And that can be built up interactively pretty easily by adding onto the end of the pipeline. I would also note that, due to sql being painful, the query is not exactly the one I wanted and instead I would have wanted something better capturing the change over time, but the thought of doing that in SQL seemed too unpleasant. An example of an actual query language that tries to be better for analytics: https://prql-lang.org/ 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 https://www.scattered-thoughts.net/writing/against-sql and just on semantics: https://www.scattered-thoughts.net/writing/select-wat-from-sql/ https://www.scattered-thoughts.net/writing/select-wat-from-s...

nalgeon 3y ago
I think the problem here is with the query, not the language. You can immediately improve its maintainability and readability by using CTEs. https://antonz.org/cte/ https://antonz.org/cte/
- rjbwork 3y ago
  This exactly the comment I was going to leave. If I get past one sub query, I will refactor to CTE.

mritchie712 3y ago

I felt the same way when Malloy[0] launched. It has some interesting features, but I couldn't see myself ever using it. Nothing makes a big enough difference to spend the time to learn it. Would love to hear from anybody that's using it regularly 0 - https://www.malloydata.dev/ https://www.malloydata.dev/

civilized 3y ago

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

vlovich123 3y ago
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?

bad_alloc 3y ago

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.

mcs_ 3y ago

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.

hcks 3y ago

"You can write SQL queries in lower case"

SushiHippie 3y ago
I know this, but I can't. Muscle Memory, even though I don't use SQL that much.
euroderf 3y ago
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.

hosteur 3y ago

I would love if sql would support a slight syntax change of accepting From table select col; as an optional alternative to select col from table; This would allow autocompleting col names in editors. Other than that I quite like sql being the standard db query language.

masklinn 3y ago
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.
- hosteur 3y ago
  > That only fixes trivial selects Absolutely true. But a huge amount of queries are in fact trivial selects. And this change alone would make autocompleting them easy for various editors/IDEs. Perfect is the enemy of the good, etc.
- marcosdumay 3y ago
  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.
  - masklinn 3y ago
    > You can easily change it to make `group by` behave much better Change what? Make "group by" behave better how? > `having` redundant with `where` They filter different things, how do you make `where` perform both jobs? > like it should always have been if you just evaluate from start to end, without any hidden reordering. The only "hidden reordering" is an optimisation.

vunoo 3y ago

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

pjmlp 3y ago

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

tudorg 3y ago

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.

manojlds 3y ago
Probably end up with English powered by LLMs

wwilim 3y ago

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

OliverJones 3y ago

As an experienced (===old) developer, I have learned that data long outlasts the programs that access it. The lifetime of data is measured in decades, but programs last for years. Most SQL-based RDBMS teams have figured out workable version migration paths allowing old data to run on newer servers. Because this kind of migration is a very common and economically valuable operation, the vendors make sure it works correctly. Sometimes a project, especially a greenfield project, looks like it will benefit from more recently invented data storage and query tech than your grandmother's SQL. That's always possible. And as developers we hope for, and work for, continued progress. But consider what may happen when the project succeeds. If you're still on the project, you'll wake up one day and realize your oldest data is 20 years old. What happens if your storage and query engines are also 20 years old, because they didn't succeed to the extent needed to pay for maintenance and upgrades? You'll be in the software equivalent of the century-old subway system where you have to make all your replacement parts yourself, or get gouged by vendors that can't spread their costs among many customers. Build for the ages, not for the moment!

rubyfan 3y ago

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

bshacklett 3y ago

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

dools 3y ago

I feel exactly the same way about ActiveRecord ORMs. That’s why I created PluSQL: https://github.com/iaindooley/PluSQL https://github.com/iaindooley/PluSQL

pier25 3y ago

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.

bdcravens 3y ago
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.

SeanLuke 3y ago

What happened to Datalog?

grose 3y ago
I was hoping for a Datalog shoutout. I like SQL, but it has a few disadvantages compared to Datalog. - In Datalog, queries and the data itself are homoiconic; the structure for both is exactly the same. Uses first-class variables to represent unknowns. - Easy to compose queries (just tack on another predicate) Of course it has the disadvantages that it's not widely supported (outside of Datomic/Clojure/Prolog ecosystem?), and not as popular, and maybe even more difficult to optimize [citation needed]. I would love to see a new Datalog-based DB. SQL-but-slightly-different-syntax is "lipstick on a pig" as they say, not very compelling IMO (and not really novel either, you can see it reinvented in every ORM).

Mizoguchi 3y ago

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.

rubyn00bie 3y ago

sickos.jpg YES! I can't count the number of services or things I've had to use which invent their own query language instead of using SQL. In every case the language is empirically worse than SQL, especially when it's some mangled hybrid to make things "easier" coughNRQLcough. The silliest part is, if we all just fucking used SQL the tooling and integration would be much fucking easier and/or free. Not to mention, in almost no organization will you have the time or resources to not make something that's a half-implemented, poorly spec'd, rubbish version of SQL. I think the biggest problem with SQL is that folks feel like they don't need to know it or that it's not useful. Oh dear beebs, it's fucking useful. Next time I personally need to build a rich query interface, I'm just using row level security and opening up Postgres.

galaxyLogic 3y ago

The problem with SQL is that it is not a (very) composable language. The documentation for EdgeDb goes into some detail about that and shows an alternative better language for data-queries. https://www.edgedb.com/showcase/edgeql https://www.edgedb.com/showcase/edgeql To understand why SQL is bad, you must first be shown something better, and EdgeDb seems to be such better more composable language.

IanCal 3y ago
Malloy is another thing to check out in this space https://www.malloydata.dev/ https://www.malloydata.dev/
nalgeon 3y ago
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).
- default-kramer 3y ago
  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.
  - camgunz 3y ago
    The answers are stored procedures and templates. People argue against them, but they're basically the data versions of software affordances we already have.
fbdab103 3y ago

benrutter 3y ago

Maybe I didn't grok this article but all the examples made me think "fancyql" (as it calls it) looks a lot better than SQL. If I could compile back and forth between SQL and "fancyql" then using fancyql feels like an absolute no brainer to me? I'm a lot less sympathetic to the "everyone already knows it" argument after dealing with SQL queries that are many hundred lines long.

raybpurchase 3y ago

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

Hendrikto 3y ago

Funny how differently things can be framed. You can either call SQL proven and battle-tested, or crusty and outdated, depending on your agenda. Same for the fancy new alternative: It is either fresh and innovative, freed from the shackles of legacy and standard-compliance, or reinventing the wheel in a non-standardized manner.

croes 3y ago
For data in databases I prefer crusty and outdated

rebataur 3y ago

Actually SQL is pretty cool and by using concatenative concepts, you could do some pretty cool stuff. We built a datascience tool to quickly build data apps which can be extended from the frontend, including data wrangling and datascience functions. Most exciting part was using the PostgreSQL and SQL to process, clean and enhance data and write extensions and bring it all together. We open sourced alpha version yesterday, more documentation to come. https://github.com/rebataur/rapidiam https://github.com/rebataur/rapidiam

divan 3y ago

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".

divan 3y ago
Shameless plug – I wrote a post with my experiences with EdgeDB last year. It's a bit outdated already, EdgeDB 3.0 launch is happening next week, but I can only add good things to the post so far. My experience with EdgeDB (Jul 26, 2022) https://divan.dev/posts/edgedb/ https://divan.dev/posts/edgedb/
- llimllib 3y ago
  > 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

bob1029 3y ago

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.

Jenk 3y ago
The "domain" in the DSL of SQL is "relational data" not "your business domain"
- bob1029 3y ago
  What does this "relational data" (hopefully) represent?
  - preseinger 3y ago
    it doesn't matter what the data represents, what matters is the structure of that data, and specifically (for SQL) that it is relational, rather than key-value or document-oriented or whatever many business domains are well-modeled by relational data some are not

jmartrican 3y ago

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

thomasmg 3y ago

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.)

ako 3y ago
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.

karmakaze 3y ago

> Here is another common argument: SQL was designed with 1970s businessmen in mind, and it shows. That is a funny way of looking at it. I see that SQL is based on the work of a computer scientist vs DSLs being made by hobbyists, and it shows.

deleted 3y ago
[deleted]

ur-whale 3y ago

> I don't need your query language With LLM's, no one is ever going to need any query language starting effing now. And good riddance to all of them too, I've yet to see one that made any kind of sense from the ease of use perspective.

amai 3y ago

SQL got the order of key words wrong. Instead of „Select … from …“ it would be much better to write „From … select …“. PRQL got that right: https://prql-lang.org/ https://prql-lang.org/

tikhonj 3y ago

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?

9dev 3y ago
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?
- tikhonj 3y ago
  It's evidence that SQL isn't entirely unusable—but I've worked with too much popular technology to believe it means anything more than that.
alexchamberlain 3y ago
Aren't Views SQL's answer to an abstraction?
jeltz 3y ago
Because the alternatives are worse. It is that simple.
1st1 3y ago
Check out EdgeDB, you'll like it.

revskill 3y ago

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

kagevf 3y ago

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!

rwiggins 3y ago

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.

jrm4 3y ago

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."

zzzeek 3y ago
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.
- jrm4 3y ago
  Oh, I agree, I meant to add something like "after the fact, a human needs to inspect." But it still feels like this would speed up time dramatically.

asylteltine 3y ago

[dead]

lee101 3y ago

[dead]

amai 3y ago

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