All that said...SQL is pretty darn effective. As a language, it's the true backbone of the internet today. It's readable, explicit, fairly concise and naturally translates to how data should be broken down for efficient storage...or make some trade-offs to allow for more efficient retrieval.

There are differences with different vendor implementations...but that's what different vendors are for - to find things the other guys are doing wrong and improving on them to build a better product.

I wish the folks luck in their work to improve things, but the language I've been able to rely on consistently over the last 17 years or so has been SQL...and I've worked with a lot of languages. SQL is the one that gets the most done reliably and lets me down the least often.

As a standardized language, it doesn’t really exist; everyone implements numerous extensions, and almost no one is fully ansi compliant

Almost all formatters attempt to be generic (believing standardization exists), and fail to support the full grammar for any dialect.

Across the board, all parsers have pathetic error message support (error on line 3, which is actually just the start of the statement).

The schema offers type constraints, but querying/ide’s extract no value from that (that is, types are statically specified/constrained, but query editors all pretend its fully dynamic)

Theres a lot of awkward nonsense, like where clauses are parsed before the select in most parsing engines, causing alias usage to fail without wrapping in a subselect/with clause

The grammars themselves are an inconsistent, ad-hoc mess

The grammar is also unnecessarily context-dependent (eg from must follow select, and where after that), making programmatic composition unnecessarily difficult

I don’t know how much of the tooling issue is a result of SQL as a language versus the history itself, but I can at least confirm that trying to parse multiple dialects is absolute hell, which would at least explain the sorry state of affairs for eg formatters.

But the majority of its expressive power derives from the relational algebra, and has nothing to do with the SQL grammar, and thats the majority of its value. It seems obvious to me that at the very least the compositional issue of SQL, and its self-inconsistent grammar, should be vulnerable to near-lossless improvement without too much struggle, though I can’t say what the alternative would actually look like.

But it seems like its riddled with a lot of unnecessary flaws

I was experimenting around with creating an Entity Framework equivalent in Typescript and really wanted to create a SQL AST for use under the hood(optimizing queries, SQL push down, etc). Ended up using the PostgreSQL types and a Ruby plugin that binds some PostgreSQL libs to work on my POC. Crazy town.

As I learn more about SQL and pull off more complex queries, my respect for it deepens. To have such power and support so many use cases with so few constructs is really an engineering feat. It’s timeless for a reason.

Some of my relatively common access patterns are awkward to express, but they can still be expressed in a few lines + a CTE or two, which is really impressive for a language so small.

This is not to say we can’t do better. But SQL has achieved a deep resonance with its problem space that most tools don’t even come close to. The brightest minds and most effective tooling shops in our field would be lucky merely to do as well.

I'll give you just one : in SQL, you can write "WHERE 4 > (SELECT COUNT() FROM ... WHERE ... )" but you cannot write "WHERE (SELECT COUNT() FROM ... WHERE ... ) < 4" [Darwen, "The askew Wall"].

Or iow, for certain specific kinds of 'a' and 'b', you can write "a < b" but not "b > a". Do you actually know ANY language that exposes that kind of thing ??? REALLY ???

Given the vast usage of SQL and its overwhelming popularity, if there were real problems with the language then there would be a lot more noise about them. But most people seem to be happy with it.

If it's not broken, let's not fix it, eh?

>Given the vast usage of SQL and its overwhelming popularity, if there were real problems with the language then there would be a lot more noise about them. But most people seem to be happy with it.

In what universe does popularity imply quality? Certainly not the one I’m in — Avengers endgame is apparently #2 gross worldwide ever :-)

Which finally implies that usage popularity is not adequately explained by user preference, because you wouldn’t expect user language preference to have significant impact on the decision making process.

But regardless, are you also going to claim that C, Oracle, IBM, Microsoft, etc were optimal in their respective fields, during their respective eras, because they held total market domination? Incumbency is one hell if a drug..

Bandwaggoning and deffering to the status quo is not argument for quality; there are too many factors involved beyond quality itself. Fixable flaws in the language have been pointed out and not been addressed in this conversation chain. Alternatives languages exist, both academically and practically; they don’t come alone. Its not a competition in a void, the ecosystem has to move together. This occurs in every aspect of tech (did you ever wonder if there could be an OS other than unix and window styles?).

Asking why SQL was never beaten out in popularity is a hugely different question from whether there can be a better language than SQL, with largely unrelated amswers(Oracle/IBM were hardly fair players in anything they did)

The lack of popularity is also a reflection of how the string of next best things have failed to actually deliver on their promise, accompanied by the lack of a rational argument to adopt them instead of using a time-tested technology.

The SQL language is just an API to the total engine; the majority of its value derives from the relational model; the value of the relational algebra is not being questioned. Only the particular interface to describe the relational algebra.

The power of the JVM is not the power of the Java language. You can have other languages make use of the same power Java has access to by targetting the JVM, and as a programming community we accept that just fine (and we also accept that despite Java having many known flaws as a language, its status as a first-party Oracle interface to the JVM, and its position within the status quo, makes it extremely difficult to upend; but that hardly implies Java is some perfect language, as most HN users will trivially acknowledge).

In the same fashion, the SQL language is (ideally) decoupled from the RDBMS engine; it can be replaced. But in practice, they're not so decoupled, and SQL has been consistently the only first-class interface to the engine, so like Java, it (can) enjoys far greater ubiquity and stability from the quality of the underlying tech, than the language itself may deserve.

So once again; popularity/stability of the SQL language is not (necessarily) exemplary of the quality of the SQL language. It's much more likely that it represents the value of the RDBMS, and the relational model (which I don't think anyone is arguing against), and the SQL language enjoys a free ride by being the only interface thats even offered.

I mean hell, just read the article. It has trivially observable flaws in its design and semantics (like stuffing a 3VL logic into a 2VL language). EdgeQL may or may not be the optimal solution, and I'm not arguing (or interested in arguing) one way or the other on that, but I don't see how anyone can reasonably argue that SQL's ubiquity shows its perfection, when it's so clearly imperfect (because those flaws are being very directly pointed out).

Why is SQL the exception to the rule? You talk about popularity, but its really a question about stability. Remember, SQL is not that popular, as the NOSQL movement and comments like yours prove.

I don't think you've picked a very good example here of a field where the lingua franca has progressed over time. C still dominates nearly all systems programming, against all odds. It dominates operating system development, driver development, embedded systems development, pretty much anything that demands an extremely high degree of efficiency.

I agree with your overall point regarding SQL and its superior stability, but C is an example of where popularity won out against many better options over time. It's not like Rust was the first mainstream attempt to create safer languages for systems development either. There's been many over the years designed to fill the same niche, with more modern features, tools and goals. For better or worse, C still dominates the landscape for reasons entirely independent of whether or not it was truly the best tool for the every task.

Also, I'd contend that your claim regarding the popularity of NoSQL is incorrect. If you only talk to web developers writing Javascript, you'd get the impression that NoSQL is taking over the world. But the reality is that amongst pretty much every other demographic, SQL is still highly regarded as the ideal technology.

Well yes, because I argued that it was irrelevant to the original question: can SQL be improved?

Additionally I actually did address the question of SQL’s stability, at least partially: it’s not SQL thats so stable, but the relational model. SQL just happened to be IBM’s, and IBM was highly successful pushing its DB around, and Oracle (kind-of) cloned it to push their DB around with less contention, and so it went on. But its the RDBMS engine that primarily pushes a DB’s value; The SQL language is a ride-along.

And once more to be clear: its longevity is not a result of SQL’s quality, but the quality of the relational model. Thus its popularity, and stability (its also not that stable, in that its heavily extended by everyone in arbitrary fashion), is irrelevant to the original question.

>SQL is not that popular, as the NOSQL movement

If people are trying to make use of NoSQL because they want to avoid the SQL language (not the relational model), they’ve made a grave mistake in understanding their technologies; I don’t think such a naive opinion should be considered relevant to the equation.

If they’ve chosen NoSQL to avoid the relational model, then their choice says nothing about the SQL language.

Yes, they are trying to avoid the relational model by avoiding SQL because SQL has basically been the face of the relational model for 40 years. if there was something better, they'd use that. Nothing better has come up, and i don't know why either.

If you want to believe that popularity is a measure or reliable indicator of quality, I can't be bothered with you.

PS I have a worked-out example on my site of what it takes to enforce a business rule "no one has a salary higher than his manager's" in SQL (> 100 LOC) and in SIRA_PRISE (one single relatively simple formula of the RA to declare). You decide which is "better".

However, there is a more insidious problem with SQL: it's all too easy to write SQL statements that have O(N^2) complexity. A simple JOIN can easily result in O(N^2) complexity, yet there aren't easy tools to identify these performance issues. As a result, as a database grows, things that once were executed quickly take forever.

I'd like to see the end of joins, replaced with something that is more explicit about what is happening under the hood.

However. You are wrong tying the problem to joins. I remember an analyst who launched a SELECT COUNT because he was just curious about the number of rows in the table. No joins involved but users did suffer. Elsewhere in this thread I've seen the problem be tied to table scans, and that's also wrong. A table scan isn't a problem if it's a 5-page table. As Darwen often argued : why are people always only lamenting about those couple of tables with millions of rows ? Why should we deprive users of the power of relational algebra if their database simply aren't that big ?

It's a matter of determining the cost of the data access strategy (regardless of JOIN/EXCEPT/what_have_you) and (implementing a protocol for) capping it at runtime (or earlier if possible). No need for language changes here.

I was using joins as the common example. Sure, there are many other ways of using SQL to shoot yourself in the foot, but most of the issues I've run into seem to come from joins.

I don’t see the relationship between those two things; or rather, what the latter even means. Can you expand?

Also, apparently the “sets” described in the article are actually multisets aka bags [0] — so the same semantics as any other RDBMS/SQL. No idea why they’d confound the two, especially when set vs bag semantics is a well-discussed topic in the literature..

[0] https://edgedb.com/docs/edgeql/overview/#everything-is-a-set

SELECT StudentID, Name, (SELECT COUNT(x) FROM StudentExam WHERE StudentExam.StudentID = Student.StudentID) / (SELECT COUNT(x) FROM StudentEnrollmetnPeriods WHERE StudentEnrollmetnPeriods.StudentID = Student.StudentID) AS ExamsTakenPerYear FROM Student ORDER BY ExamsTaken DESC;

works because the result from aggregate are scalar and thus math operations always meaningful, albeit this in particular might not be the most brilliant example, there's definitely a case for running such operations on the server, because you might filter on the results, say, like

"select all student with less than 2 exam per year average"

at which point either you have the distinction between scalar and set in the language itself and you can filter invalid queries at the parsing level or you have to do the checks at runtime when two set are in an operation with mixed cardinality, halt the query and throw an error.

edit: count(x) because I don't know how to escape asteriks

it's not coincidence that the article complains about the same thing but in reverse:

> This is legal, but only if the subquery returns not more than one row. Otherwise, an error would be raised at run time.

except the 'fix' reintroduce it in a way that's subtler and way hard to detect because now everything is a set even when the intent is to have a scalar.

A lot of HiveQL's power comes from extending the language, not from the use of a small set of timeless features.

I mean, if I gave you protons, electrons and neutrons, you could build the universe out of them!

Expressiveness isn't inversely correlated to number of constructs.

In reality, it's not just about the small number of constructs, but also about how fundamental they are. In one sense, atoms are less fundamental than protons/neutrons/electrons, and therefore reduce the number of possible creations.

Are we just assuming that protons and neutrons automatically pull in the quarks, neutrinos, other leptons and force carriers as well? Does the electron have a photon dependency? What about dark matter?

If not, then the our three particles are internally inconsistent and woefully incapable of building a universe. Otherwise, our set of dependencies is essentially the whole darn universe anyway.

Same goes for atoms. Give me enough and I can build a star or particle accelerator to create whatever elements or fundamental particles I want.

Also, the GPs suggestion of building an accelerator is as silly as someone writing an assembly compiler via a mush of SQL. It probably can be done, but it's not in the spirit of the topic!

Anyway, I feel like your original comment and my reply are kind of doing the same thing at the meta-conversation level. We're stretching a metaphor in order to make some counterpoint argument. My intent with the excessive pedantry was to highlight the farce of conflating rough metaphor with substantive insight.

Anyway, internet conversations are hard. Thanks for engaging!

> In reality, it's not just about the small number of constructs, but also about how fundamental they are. In one sense, atoms are less fundamental than protons/neutrons/electrons, and therefore reduce the number of possible creations.

I think what OJFord is getting at is, what if you had more subatomic particles to build with?

Thus, whatever the complete set is for subatomic particles, it will most certainly have lesser members than for the complete set of atoms.

Getting back to my original point, SQL operates at a fundamental enough level that you do not need a great number of "constructs" to create a query that gives you whatever it is that you need.

I'm not saying it's perfect, but was just supplementing the OPs observation that SQL has surprisingly few "constructs".

Radiation makes the universe a lot more interesting.

All that said I think it was originally structured to partially be a human readable language and it fails pretty hard at that - that's a facet I'm sure smart people could revise to make more natural.

The problem isn't SQL. It's that Splunk's query engine is tied up internally with a "grammar" that is a direct port of a shell pipeline into C++ with no intermediate representation or anything a compiler guy would recognize as a grammar. There was no design, no mathematical underpinning to it.

Splunk's unstructured log capabilities are really domain knowledge about making them semistructured as fast as possible: token indexing, a lot of effort on recognizing character encodings and timestamps intelligently, looking for key=value pairs, and letting people write regexes to extract fields themselves. The query language isn't somehow designed for a different data model.

In EWD1123, Dijkstra showed that the relational calculus and the regularity calculus (which governs regexes) are basically the same thing. My takeaway from that is that the relational model can be reinterpreted as a model over anything you want to match and manipulate with regexes by just changing the field selectors.

More legitimately splunk may simply be unable to deliver performant data expression if a user is typing a query complex enough to justify SQL.

FWIW postgres used to have its own query language derived from QUEL[0] rather than SQL.

And findings that SQL is kinda shit are not exactly recent, e.g. C.J. Date's "A Critique of the SQL Language" (1983) lists the following sections

* lack of orthogonality: expressions

* lack of orthogonality: builtin functions

* lack of orthogonality: miscellaneous items formal definition

* mismatch with host languages

* missing function

* mistakes

* aspects of the relational model not supported

The conclusion was, obviously, prescient:

> if SQL is adopted on a wide scale in its present fortm~ then we will to some degree have missed the relational boat~ or at least failed to capitalize to the fullest possible extent on the potential of the relational model. That would be a pity, because we had an opportunity to do it right, and with a little effort we could have done so. The question is whether it is now too late. I sincerely hope not.

SQL succeeded not because it's "pretty darn effective" but because IBM decided on it (at a time where it drove technology) and Oracle are great at sales and marketing (whereas Ingres definitely wasn't).

[0] https://en.wikipedia.org/wiki/QUEL_query_languages

Most of Postgres is standard SQL. It's just that most non-Postgres databases do not implement standard SQL very well.

It's got a lot of stuff going on in there.

This sounds amazing. Does anyone have a link to some docs for this?

That's correct, and that's why PostgreSQL has ADD CONSTRAINT.

The constraints describe the actual schema of the data; INDEX is an DBMS-specific implementation* for improving performance (including index types, etc.).

* Those since the standard does not yet cover some thing partial unique constraints, these have to be done as INDEX in PostgreSQL.

That's not a bug, that's a feature. Deriving from the deliberate intent to make physical independence (which was simply nonexistent at the time the model was conceived) a reality.

Call it a "hint", if you will, the meaning of which is implementation defined. But standardize the syntax for pete's sake.

And "hints" in the language are not exactly going to improve "interoperability" if their meaning is still allowed to be implementation-defined, are they ?

Sure, but the non-standard enhancements like JSON support are part of what sets Postgres apart from the competition IMO.

The release is planned for roughly next September/October.

[0] https://modern-sql.com/

[1] https://modern-sql.com/slides/ModernSQL-2019-05-08.pdf, for example

(Edited for formatting.)

In PG, a JSON column is so well integrated that you can do all sorts of crazy stuff (indices over JSON queries is my favorite). You could build an entire RDBMS on top of PG's JSON column.

Postgrest works very well as this shim layer, though I've moved on to writing sql directly in the client and communicating through a web socket shim. In terms of reducing code complexity and improving performance this is absolutely unbeatable, you just need to parse incoming sql to sanitize it and make sure there is no role escalation. Because of postgres's foreign data wrappers this method can provide a consistent surface for basically all your enterprise data. The only gotcha with FDWs is that some of them don't "push down" many query clauses, so you end up doing much slower queries on the remote system and filtering locally, which is terrible for obvious reasons. That being said, the FDWs are pretty much all open source, so you can just implement push down support for those missing clauses yourself.

"Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql".

Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.

A good tool for this purpose is https://github.com/JavaScriptor/js-sql-parser as it will fail to parse complex statements that are likely to include an attack vector.

In terms of authorization, you can either create per user connection pools if using web sockets and log the user in directly that way (which makes things easy) or if you must use rest, use a single connection pool with a master user then use some form of token to tell the shim who to set role to before executing the query.

https://github.com/PostgREST/postgrest

https://wiki.postgresql.org/wiki/Python

https://github.com/plv8/plv8

https://www.graphile.org/

But, the common workhorse tool that has stayed strong through all these has been the common SQL. Elegant, simple, powerful and thoroughly reliable, it is my primary go-to tool. In an otherwise changing ecosystem, its simplicity and reliability is a boon. Yes, it is primarily because of the nostalgic familiarity but I also believe it continues to be extremely powerful, one that will serve you very well.

Of course we can do better than SQL. We could obviously do better than Javascript. It’s globally understood that we could all do better than English.

We sometimes struggle to express ourselves with language. We can blame the language, try to fix it, or invent a new language and try to get people to speak it.

I would rather spend my time refining my elocution than learn a new language. That said, there are words and phrases that simply work better in other languages. I don’t know a single-word corollary to ‘Simpatico’ in English.

I wish the best to those who would make programming more expressive, and the worst to those who would try to streamline away subtlety.

Performance is quite loosely linked to language, given sufficient abstractions and optimizations. I submit NumPy as an example. We humans have lots of ways to say what we want, and we want lots of different things in myriad ways. As an analyst I often wish for a richer language, some way I could transcend tabular data thinking to find and make associations around real-world state. I’m certainly self-satisfied when my nested subqueries return what I’m expecting, but I may be able to add more value if I had a better way of expressing my questions.

If we can do better than SQL, it ought to bring more people closer to the reality that lies behind the data, and further from the methods used to obtain it. Maybe I don’t have the words for what I’m looking for yet...

Posts have an upvote button, but no downvote button, so dissenters can only use comments to express disagreement.

"amiable"? "congenial"? "affable"?

Upvote manipulation maybe? Then once it is on the front-page it gets lots of exposure. So the probability to get upvoted becomes higher.

Perhaps YCombinator omitted the downvote button because it can be abused. For example, if there is a story that is bad news for a certain company, that company could organize a downvoting campaign to hide it. But the upvote button can also be manipulated, as you say.

However, which is worse? (A) To write a silly story and have it rightfully downvoted into obscurity, or (B) To write a silly story, have it upvoted into the limelight, but be littered with comments that expose its flaws.

I think that as long as I remember that just because a story is on the front page doesn't mean it's right, then Hacker News has a nicely curated set of moderation rules.

That's how story flagging currently works though, and is significantly more effective

I like the fact that SQL has a solid foundation in relational algebra. I see no such foundation for the alternative.

I do like what LINQ did here (being SQLish), which was to put the FROM clauses first. Some SQL variants have WITH clauses that are quite convenient but you end up with:

    WITH (...) AS a,
    (...) AS b
    SELECT
      a.a1,
      b.b1
    FROM a
    JOIN b
    ON a.a = b.b

   SELECT
     a.a1,
     b.b1
   FROM (...) a
   JOIN (...) b
   ON a.a = b.b

   FROM (...) a
   JOIN (...) b
   ON a.a = b.b
   SELECT
     a.a1,
     b.b1

“Some variants”, including standard SQL since SQL:1999.

https://modern-sql.com/feature/with#compatibility

Postgres is changing in the next release, BTW: https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

Transpile your language to the equivalent SQL, and rely on decades of research and real world experience in things like replication, optimization, locking strategies, high availability, security etc. the things unrelated to the language current SQL databases are really good at.

Enterprises need enterprisey features.

Even if I was convinced EQL was the future I wouldn’t throw out the old stuff. If the old stuff continues working but there is a smooth migration path, I would probably give it a try.

Think of TypeScript vs. JavaScript.

No, but we have a few ideas on how to connect existing databases to EdgeDB.

> Even if I was convinced EQL was the future I wouldn’t throw out the old stuff. If the old stuff continues working but there is a smooth migration path, I would probably give it a try.

Yes, we'll be working on that.

Your 'transpile' handwaving won't fly. You are blindly presuming that the 'SQL equivalent' (a) always exists (it doesn't) and (b) can always be generated by an automated transpiler (it can't).

We have some_table which we want to join to other_table, but we need to map an identifier through mapping_table in order to do it. So we end up with a query like:

SELECT (...) FROM some_table INNER JOIN mapping_table ON (...) INNER JOIN other_table ON (...) ...;

I know for sure when writing this query that the middle join to mapping_table will map every some_table row to exactly one row (no more, no fewer) in mapping_table. The problem is that the query doesn't capture this. The mapping table isn't really named something as obvious as "mapping_table" so someone reading the query has a hard time inferring what the intent was. It totally changes how you mentally parse and think about the query if the result set can be accumulating multiple matching rows from the join, or maybe even losing rows if there are no matches. You have to go bring up your database schema to figure this out.

And, as a fan of static typing, I can't help but cringe at the possibility of someone changing the constraints on the table without realizing that there are queries which implicitly depend on the old ones. SQL offers no resilience to this and will happily change the meaning of your query without a peep of complaint if you drop that constraint from mapping_table.

If there's a fancy way to capture this "mapping" relationship in standard SQL that doesn't just use a dumb inner join, I'd love to know about it. If not, I'd love a query language that supports some annotations that help reading and are either stripped out before sending to the database engine, or are actually checked at runtime.

Your example query joins one some_table row with 1-n rows on mapping table, and another 1-n rows from whatever else is in there to that. If you're expecting a single row in the resulting set per some_table row, it means that you're filtering very hard (which is fine) or that you've a schema problem (which is the actual problem).

Folks who know more SQL than me: Is there a good way to say "I would rather this query fail than try to scan a table?"

They can't, if you do perf testing as part of your pre-release testing, which you should do.

If you aren't doing perf testing, you are saying perf isn't an acceptance criteria, so why are you upset that breaking perf doesn't break tests?

(I understand full well that that is a problem if the company has kicked out the DBA role and handed it over to the individual programmers, but perhaps that is precisely the problem.)

The DBA you were mentioning here, e.g., could first do a query in the EXPLAIN results, and he'll have his "company-wide queries audit" in a matter of seconds.

The SQL scripting function is a tool run from a desktop, all emulation and such is JAVA based, with the feature to ask the system what the query is doing. The feature called Visual Explain will explode the query into a graphic representation of how the system optimized it to run. It will recommend indexes as needed. This is very good for understanding when table scans are forced, how files actually joined up, and more.

When creating views and sprocs, SQL Server lets you mark them as 'schema bound', creating dependencies on the schema objects it uses.

Not sure if something like this exists in Postgres?

    // Dear maintainer:
    // 
    // Once you are done trying to 'optimize' this routine,
    // and have realized what a terrible mistake that was,
    // please increment the following counter as a warning
    // to the next guy:
    // 
    // total_hours_wasted_here = 42

And as for "waste" : if while learning a better language, they are in addition also learning the relational model, and to think relationally, (their knowledge of both of which will be VERY poor if all they've ever seen is SQL) then there can't have been much time "wasted", can it ?

It's far too embedded throughout the entire industry and as a data analyst, learning EdgeQL vs SQL and then being locked into a new startup database that could disappear in a year doesn't seem like a high probability strategy.

I wish the people all the luck but unfortunately SQL is "good enough", pretty standardized (I can use just about any relational database and get useful data by knowing the basics). The inconsistencies may be mathematically "ugly" but it's not hard to wrap your head around and overcome.

That is, a reliable tool that translates 99% of normal SQL into readable Edge SQL, and vice versa, would help adoption a lot.

Remember how new JavaScript features became mainstream though transpiling, long before native implementations.

There are just so many free things you get with SQL and established RDBMS that deeply impact application features, quality, stability, operations, and much, much, more. I've had to write a custom mongo-db like interface for querying, as well as a fair number of hacky bits to effectively cover the surface area of SQL in an inferior way.

I've learned tremendously, but I just wish people don't follow in my exact footsteps because that's probably wasted time.

Why is it so hard to imagine something displacing SQL? A simpler, more predictable syntax seems perfectly plausible--it could ship alongside SQL. Do we have StockHolm Syndrome?

The negativity is surprising and at the same time predictable.

Simpler languages have been shipped dozens if not hundreds of times, and they generally tend towards expressing the things they missed or not giving enough functionality for the things they missed.

I am not saying its impossible, but you're going to have to do a lot more than hand waving to justify the reverse position.

Interesting for you to say that. The last time I went looking for mouse traps, I found that every place I went had a reinvented type, and traditional style were extremely hard to find. Yet, it is true that the traditional style is simple and effective. I've learned from experience that anything else is likely to be useless.

Yes you can make some lovecraftian horrors if you really want to, but SQL is one of those things where just a little bit of knowledge goes a long way. If you can understand the basics you can get a lot of work done.

It's a lot like Excel. You can do some really complex confusing stuff in Excel. But you can also teach the basics to non-programmers quite easily, and command of the basic skills will be very empowering. Basic knowledge of Excel, like SQL, gives the user new ways to leverage computers when creating their own solutions to their own problems.

The problem is not reading SQL, but writing it.

It's a lot like Excel. You can do some really complex confusing stuff in Excel […] Basic knowledge of Excel, like SQL, gives the user new ways to leverage computers when creating their own solutions to their own problems.

I can’t speak to your experiences, but I’ve never in my life encountered someone who was not a professional programmer ever even contemplating using SQL for anything let alone creating their own solutions to their own problems. I think it’s safe to assume that the overwhelming majority of people who use SQL, are programmers who most certainly cannot get by with just basic knowledge.

>I think it’s safe to assume that the overwhelming majority of people who use SQL, are programmers who most certainly cannot get by with just basic knowledge.

Most demonstrably do though, so there's that. If you cast a wide net when polling programmers, I think you'd find that mode level of knowledge was relatively low. You don't need to be a SQL rockstar ninja dude to do what most professional programmers are doing with SQL most of the time. Obviously advanced knowledge is good for any professional programmer to have, but the fact is there are a TON of people out there who only know the basics, and that works for them.

I have. That's only an anecdotal observation, but it seems to me that the existence of Visual Basic and the popularity over the years, combined with the utter disdain for it by "real programmers" is evidence that, more generally, there are a huge number of kinda, sorta, programmers who are outside the IT culture.

After some time, most can handle enough SQL to help customers with basic issues that cannot be handled in the application. Some have become quite good at it, and can do quite non-trivial stuff. None of these folks write any code beyond SQL.

I agree. At the same time a little bit of knowledge is incredibly dangerous.

That query, which worked so brilliantly on the test system during devlopment, suddenly grinds all of production to a halt.

The basic problem is that indexing and other physical performance boosters were not really rerquired when those queries were tested with 3'000 customers.

Being set-based that's quite different when you suddenly deal with 30'000'000 customers and a number of joins, which may not be supported by indexes, since that was never obvious in development.

That said: I'm not arguing against SQL. It's a great language for its purpose. What I do argue for is to have an SQL domain expert and an expert on how it phyisically maps to the underlying database engine for more complex projects.

Such a resource can be immensly valuable in assisting application developers to avoid major mistakes when they deal with the underlying database.

Edit: A couple of issues, which actually negated my argument upon reread

Yes, it was that, but, still, there's a big and growing hurdle here that many similar efforts, with similar objective merits compared to contemporary SQL implementations, have failed to overcome, and not a lot of reason provided to think EdgeDB is better positioned.

> Why is it so hard to imagine something displacing SQL?

Because systems providing just as good solutions to largely the same set of SQL deficiencies have been produced and failed to displace SQL for a couple of decades.

The problem isn't doing better than SQL. It's doing enough better than SQL to overcome the depth of knowledge, experience, support, tooling maturity, and comfort people have with SQL. And that most gets deeper over time, on top of SQL getting internal mitigations, if not actual solutions, to some of the problems over time.

That said—as I’ve done with several before—I’ll probably download EdgeDB and try to do some stuff with it.

There's no reason the industry can't move to new technology for new projects. No one is going to rewrite legacy applications in the new language, at least not until the transition is so far along that SQL specialists are aged out and costly. But for new things? It's totally acceptable to pick new languages.

And btw, this has already happened in some areas. Most developers I know don't code in SQL: they use an ORM provided by their language runtime or a support library. That's essentially the same thing.

This works well enough until your ORM shits the bed and you're forced to figure out why your SQL database is "slow".

SQL isn't slow, ORMs are just terrible when you hit an edge-case or when pretend there isn't a relational model behind your opaque materialised objects.

I used to be one of those developers you mentioned, but I'm not anymore after years of debugging "SQL performance issues" (hint: it was the ORM), and actually taking the time to learn the language and take advantage of specific RDBMS features. My preference has shifted to just use a lightweight library to materialise objects (like Dapper), and write the queries myself.

I know I'm staying with the nulls. I wish the best luck for them, and if it survives to maturity, I'll haply go get the ~1% (probably less) more productivity they offer. But right now I'm not moving. It's sad, really, but things are stacked against them. Change is costly, so we lose all the small changes that could compose into something huge.

The crux is : it takes much much more to achieve that than what the average C# or java coder yup has to offer. So the average C# or java coder yup will dismiss those things too.

The JS framework churn makes me super appreciative of the stability that SQL brings.

Even if it's still true, "churn" translates to progress. It would be hard to argue complex web application UI development isn't better off now than it was 10 years ago.

I don't have a citation, but a colleague of mine who's working on front-end projects repeatedly complains that today's JS stacks require half a dozen base JS packages, which in turn download dozens if not hundreds of ancillary packages, not to mention requiring a couple of transpilers and a bunch of tooling.

And for what? Well, just to be able to render some text and a couple of buttons.

Nowadays we have whole server projects that take less than 50MB of source code and dependencies to build, while a miserable SPA with a login screen and a couple of menus and buttons requires nearly 400MB of JS.

That's pretty bleak.

There are projects like create-react-app and Parcel which offer very reasonable zero-configuration toolchains.

If you care a lot about runtime dependency weight, there are lightweight libraries like Preact (3kB).

Perhaps SQL is fine if it's your interface for accessing data on a one-off basis, but if you're trying to build a complex tool on top of it (say, an analysis tool for arbitrary data), the inconsistencies and performance concerns mount. People often end up inventing their own proprietary databases to do these analyses (e.g., virtually any business intelligence tool) assuming they can afford to do so. Perhaps EdgeQL isn't the ideal alternative, but as it is SQL is not good enough for many use cases.

I'd recommend reading the PGSQL manual, they go very in depth about many of the supported features and how they are implement and can be used.

That's kind of orthogonal to what I think is the issue being expressed here.

SQL can do many things; the problems tend to be when a query doesn't perform consistently and predictably. There's always a balance to be struck between communicating what is to be done, and how it is to be done, and SQL leaves so much of the "how" out that the query interpreter/optimizer is incredibly sophisticated and does a fantastic amount of work and yet frequently gets things spectacularly wrong, maybe due to misconfiguration and maybe due to fundamental limitations.

Obviously more information on how to do something is not always better; otherwise we'd be using assembler. But there is a balance.

Experts tend to say "write everything in one query, and if it doesn't work, fix the configuration of your database" which is not helpful given the division of responsibilities in any company. But they will say that because they are devoted to the idea that all that expressiveness is good for something.

If that were true, people would build RDBMS’s in SQL.

Since the addition of SQL/PSM (1996 IIRC) it has become a computationally complete language (procedural like many of the others) with variables and loops and what have you.

Sounds like you're looking for a magic silver bullet - there's no free lunch in our field though.

Lastly, the comment I was replying to can be paraphrased as "well is SQL is so great why aren't RDBMS' built using SQL, huh?". Which is a ridiculous question since SQL isn't the right tool for that job - its very name tells you that.

If you want to continue arguing against strawmen do it with someone else.

It'll take several unicorns and Fortune 100s hiring thousands of engineers to code in an SQL-alternative to create an ecosystem large enough to eventually overtake SQL.

I see that you’ve built this as a patched version of Postgres, but I’m curious how much of this syntax you could implement as a client library and shell that would run against an existing Postgres instance.

Right away, you would get adoption from people who have an existing Postgres, or who want to take advantage of SaaS offerings like AWS Aurora.

Longer term, I could imagine the client/shell being extended to support multiple backend DB dialects, even things like Spark or Redshift which you’d have a hard time modifying intrusively.

It could also be cool to explore interoperation with existing schemas written in plain SQL, so people could adopt it incrementally that way.

> It could also be cool to explore interoperation with existing schemas written in plain SQL, so people could adopt it incrementally that way.

Yes, this will happen too.

> SELECT * FROM table

and

> SELECT count(*) FROM table

This is actually not true, as both return table values. It then says that, in EdgeQL, every expression results in a 'set'. This is a distinction without a difference.

I don't disagree we can do better, but this is the same.

The difference in EdgeDB isn't really everything returns a set, but seems instead to be that everything consumes sets and not just scalars.

This is a valid expression:

  SELECT (SELECT count(*) FROM table) + 1

  SELECT (SELECT * FROM table), 1

  SELECT (count(SomeType) + 1)

  SELECT (SomeType, 1)

    SELECT count(*) + 1 FROM table

    SELECT *, 1 FROM table

Indeed, a more relevant second counter example would be:

    SELECT (SELECT * FROM table) + 1

SELECT Table1, 1

returns each row of Table1 along with 1 and that 1 returns the set {1} means that you have arbitrarily assigned each row of Table1 a row from the set {1}. This gives what you want in the case of a scalar, but what about a non-scalar.

For example, suppose Table1 contains (John, Smith), (Alice, Perkins), (Bob, Best). Then, what should

SELECT Table1, {1,2}

return? If you do not say cross product, you have arbitrarily assigned rows to one another, resulting in meaningless data. If you say cross product, then you have rewritten the sql expression

SELECT * FROM Table1, (VALUES (1), (2))

I don't disagree the SQL syntax is longer and has some unnecessary keywords, but unlike EdgeQL, the query means something particular.

    edgedb> SELECT {'First', 'Second'} ++ '!';
    {'First!', 'Second!'}

SELECT 1

and SELECT * FROM TABLE1 and SELECT 1 compose to

> SELECT * FROM TABLE1, (SELECT 1)

Aside from the different syntax, it is the same kind of composition. Not sure what EdgeQL has gained here, then.

Some people use a thing x

A subset of those people use a subset of x called y

This subset of people have built a tool to make y easier to express

Thus x is invalid

I think that's a really bad argument, if everyone was using ORMs then we'd all merrily forget about SQL and just use ORMs which would become the new "language for structuring queries", but we don't all, and EdgeDB in particular thinks that ORMs provide a much degraded expression set from SQL and thus want to tweak X so that it's as expressible as the tool to express Y but without making it just about Y... I think your goal boils down to shifting the trade-off point between X and Y - the extreme of which is writing a very expressive Y that also supports X but in a non-expressive manner.

This cannot be the case if you call each thing a 'set'. Unless `(a, b)` calculates the cross product, there is no meaningful non-arbitrary way to assign each element of a to an element of b. That would depend on an ordering, which makes it not a set to everyone but a marketing dept somewhere.

SELECT count(star) FROM table

and

SELECT star FROM table

Are both valid, not equivalent.

Furthermore, listing any kind of set depends on some ordering, be it random.

What would you expect to returned for by this query?

I think many here miss totally the point. Is the same mindset that lead to believe C, bash or unix are good(?) then why bother?

I'm very lucky that I start with FoxPro, so I know what is to live with a MUCH better thing than SQL.

To the point:

- "SQL is standard, everywhere... why try to change or do something else?"

I hope none here work in new software products.

And also, try to replace ALL the OLD with the NEW is no the point, is to try to DO BETTER THAN THE OLD.

I suspect most of use are in that business... right?

- "SQL is a good language!"

Superficially, yes.. as good is javascript or C. More exactly to be the "default" option we are VERY lucky SQL is not alike C or JS. But "good" is not. Is adecuate.

IF you DON'T KNOW WHY then learn the why first. For us that LIVE in the RDBMS world is clear as water than SQL is not the ideal, is what we have.

----

Is important to understand that "SQL", Rdbms and the relational model are badly misunderstood (just look how the NoSql movement market itself!)

- SQL IS NOT THE RELATIONAL MODEL, is BASED on it.

The relational model is MORE expressive, powerful, flexible and EASIER than SQL.

- SQL IS NOT RDBMS

A much better APIs can be build on top a RDBMS but SQL is a poor language for that. This is part of why exist so many extensions.

Think, for example, why you can't do

    SELECT key FROM BTreeInde_CustomerCode
    GROUP BY (whatever -- And I mean GROUP, not SUMMARIZE!)
    let city = SELECT name
    let filter = WHERE id = @id
    let query = city + filter(id=1) 

Many things that get mixed with the "SQL" language are part of the implementation or capabilities of the RDBMS.

- However, SQL IS A FINE EXAMPLE IN HOW DO DATA

And in fact, is not that hard to imagine a much better version, SQL is not that far from a better option!

I advocate: Building a relational data model on top of hash-map to achieve a combination of NoSQL and RMDB advantages. This is actually a reverse implementation of posgtresql.

[Clojure is a functional programming language based on relational database theory](https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

[Everything is RMDB](https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

[Implement relational data model and programming based on hash-map (NoSQL)](https://github.com/linpengcheng/PurefunctionPipelineDataflow...)

I’m not quite understanding the benefit of your example. I don’t personally want to write that style of syntax.

SQL wasn’t designed nor ever meant to be able to write a full app. You’re expecting it to do something it wasn’t designed to do. Because it is a programming language doesn’t necessarily mean it ought to be able to solve any kind of problem.

I personally don’t think SQL is all that great, for various reasons (lack of standardization with various dialects and their own gaps or abilities).

Optimally, you'll want to build your full app in a single language, frontend, data queries, everything. That eliminates every problematic interface where you have to re-encode values, verify types and other invariants, connect different runtimes, abstract foreign behavior, etc. (Somehow the JS developers talking about isoiconicity only cite code reuse... And the Lisp ones focus on metaprograming.)

We have a variety of good application languages, so it's a safe bet that one can create a not horrible one; we also have a few standards for IPC and some very safe literature on how to integrate them with a language. Foreign interfaces are always iffy, but it's also settle how to create a not horrible one. The most risky part is data querying, so it makes sense to start with a proven implementation here.

"We Can Do Better Than SQL"

And the fact that some can't imagine to build a full app in SQL (not as is TODAY but as could BE) is sad.

I live in that world before. With the base/foxpro familia's you can totally build a full app with a database language, in fact, easier than with python.

---

The point here is that obviously, SQL is not mean for programming, but do one-off queries here and there. But it not exist a reason to aim higher.

BTW, part of the laments of the creators of the relational model is that SQL have ruined, like COBOL, the mind of millions of developers.

You are outlining some general purpose programming language that allows querying, information retrieval, maybe declaratively or functionally or using objects? I don’t know - you provide no other details.

How expressive would this language be? What abstractions would it provide out of the box? How expressive is it? Your examples allow some code reuse via naming/aliasing specific clauses... is that it?

Specifically what apps would it allow someone to build? And what trade offs result from those decisions? Is it better suited for mathematical operations or data science vs building general enterprise applications? There’s a difference. Is it interpreted like SQL? Would it need to be compiled?

You’re doing a lot of handwaving, and when pressed for details, you respond with a thinly veiled insult - the problem is with the reader or me specifically for not being able to read you mind. We should take you at face value without question.

Good luck with that attitude in your career.

It may help to expand on your proposal. For example, what do you mean with group by, but not summarize? The group by clause says nothing about how to combine data not in the group. That has to be on the select. And I have yet to find a solution that beats ROLLUP for giving a great overview of data.

About the only thing that sucks on the current syntax is that it is not possible to get auto complete in the select, without having some from.

ROLLUP is not the same than groups. With

    [1:a 1:b 2:a]

    [1:[a,b] 2:[a]]

    [1:2 2:1] //assuming count

    [1:[a,b] Count:2;  2:[a] Count:1]

----

Part of the deceptive power of SQL is that is very adequate and for "just query" of data is ok for most cases.

But as "programming language" it lacks some extra power and versatility.

SQL has nested relations since SQL:2003[1] (called multisets). They are not widley supported and frankly speaking I don't like them.

JSON was finally added with SQL:2016[2].

[1] https://sigmodrecord.org/publications/sigmodRecord/0403/E.Ji...

[2] https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#js...

And group by does work the same as other languages. However, you can't talk of the group by without the select. So, if you use count, or sum, or max, or ..., Yes it will be a summary. You can use string_agg or friends if you want all values. But... Probably safer to get each as a row.

SQL is a programming language. Is just too limited and that is why many can't imagine to use it for a full project.

But that limitations have no sense. The amount of code, time and efficiency that bring is huge (I know, I code in that kind of lang before) and is discussed in:

http://blogs.tedneward.com/post/the-vietnam-of-computer-scie...

In special look a point 5/6 of Summary. The irony is that we already have a "solution"(sql) to the whole problem, but is too limited and awkward to be used in practique.

What gets me, is even in languages where I'm not working with a relational database, the API for selecting data almost always drifts to something akin to SQL. To the point that I cringe if someone tries to force a new API.

For us who didn't use FoxPro, why was it MUCH better than SQL?

    let keys = SELECT key FROM BTreeIndex_CustomerCode;

    let city = SELECT name;

    let groups = GROUP BY (whatever -- And I mean GROUP, not SUMMARIZE!);

    let filter = WHERE id = @id;
    let query = city + filter(id=1);

    for city in query GROUP BY keys
         ....

I have the same concerns about the proposed language. Yes it's better, but is that enough? What's going to drive adoption?

I'm a "full stack" developer/speaker, and for a long time I useed English on the front-end, German on the back-end, and Spanish for talking to the databaste. When I switched to English on the back-end I saw an productivity boost and I got better at and started to like English more. But I still talk Spanish with the database. I could probably be more efficient using English to talk to everything. I would probably still use French for design though.

Let's get the history in place first. The relational calculus set up a set of operators. That's what Codd worked with. It was combined with a natural language project at IBM which gave us the vaguely English like syntax of SQL.

> In EdgeDB, the data schema is formulated in a way that is much closer to the contemporary application data model. ...Unlike SQL, EdgeQL can easily extract arbitrary data trees.

This is a reinvention of the the hierarchical database, one of the architectures that was abandoned when the relational database came along. This was all fought out on a large scale in the 1980's.

The fundamental idea that you have a highly structured data tree is what the relational model threw away. "Data Oriented Design"[^1] does a pretty good job of explaining why, but roughly: we're accustomed to programming where we have an object/entity/value that represents a conceptual "thing" in our discourse. We compose things into other things, subtype things, and otherwise deal in things. And this turns out to be a disaster when lots of people need to extend and reuse hunks of the same database. If you give up on things as first class parts of your data model, and only talk about relations among attributes, a lot of those problems go away.

If you want to improve SQL, look at going back to the relational calculus without the natural language veneer. There have been a number of systems that tried that, though none have gotten real world traction. I'm also blanking on their names at the moment, so if someone who's memory is working better can help me, I would appreciate it.

[^1]: http://www.dataorienteddesign.com/dodmain/

It's not. Just because data can be fetched as a set of "tree-like" things does not make the model hierarchical in any way. But it makes lots of practical use cases easier.

> The fundamental idea that you have a highly structured data tree is what the relational model threw away.

And then ORMs came and reinstated the status quo. Truth is, many applications actually benefit from a highly structured data graph. That said, you don't have to use the "highly structured" bits of EdgeDB, it's perfectly fine if all your types are simple tuples.

> We compose things into other things, subtype things, and otherwise deal in things. And this turns out to be a disaster when lots of people need to extend and reuse hunks of the same database.

I would be very interested in reading about this. Do you have any references? Most cases of "disaster" in my experience had to do with extremely poorly defined and documented schemas that had barely any relation to the actual business model.

  type Movie { 
    property director -> str
  };
  type Book {
    property author -> str
  };

  type User {
      multi link favorites -> Movie | Book
  };

  SELECT User {
    favorites: {
        [IS Movie].director,
        [IS Book].author,
    }
  };

Would this query result in, e.g. [(director: Null, author: J.K Rowling), (director: Spielberg, Null), ...] or would it be [author: J.K. Rowling, director: Spielberg, ...] or just plain strings: [J.K. Rowling, Spielberg, ...]? I still don't totally get the model here.

You can also query the actual object type by referring to the __type__ link:

  SELECT User {
    favorites: {
        __type__,
        [IS Movie].director,
        [IS Book].author,
    }
  };