We Can Do Better Than SQL

I don't disagree that SQL can be improved. It's one of the biggest reasons I use Postgres in the first place because there are so many improvements available on top of SQL.

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.

I’ll never understand why obvious marketing makes it to the front page, only to get shat upon by 95% of the comments.

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

Yeah I'm not sold. One example from this post that struck me was the author wanted to embed a select in a table expression. I'm not a fan of this at all. I don't want it not to be clear if a given expression list will explode in values or not.

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

Common alternative:

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

whereas I'd prefer:

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

If the language is the problem, why write a new server?

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.

I have no background in databases so this may be naive or wrong, but the single biggest pain point in SQL that comes to my mind is that it can be difficult to tell what a query is doing without also knowing the constraints on the tables involved. Here's a real-life (ish) example from work:

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.

I don't disagree with the obviously true statement, but this code comment comes to mind:

    // 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

I think "ease of use" over SQL is not the hill I would die over if I were trying to displace SQL.

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.

Hi - a lot of commenters have valid concerns and critiques, but I joined HN after lurking for years to say that I really like the direction you’ve taken! I’m particularly happy about the convenient syntax for joined inserts and sum types.

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.

The article criticized SQL because the following expressions are incompatible

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

Yes.

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) 

and many many other things like that

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!

It's like SQL, but different. About as hard to learn, not fully developed and incompatible with years of tooling in the ecosystem.

SQL is an incredible language. I don't think we can do better, only different. It's not that common to mix two programming languages like you mix SQL and application code. For example having your app code in JavaScript then write SQL queries to access the data feels unfashionable. Polling also seem like something from the 80's. RethinkDb was a step in the right direction, but I think the mistake was to invent another query language, instead of making the database querying seamlessly integrated to programming languages. It should be possible to write observables without having to context switch back and forth between data querying and app logic.

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.

This is the wrong direction.

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/

I always wished SQL had a better handle on sum types. If I have a user whose favorite story is an instance of Either[Movie, Book], then it's already a pain to deal with in a nice simple way. And that's as simple as sum types get.

It is interesting to see new approaches. But SQL is so entrenched, so available and so good enough.. it will be a very uphill journey to compete

Many comments (probably correctly) point out that SQL can't be displaced because it is ubiquitous and "good enough".

I wonder if there is room for a "TypeScript" of SQL that would allow developers to opt-in to whatever new language features or paradigms we feel SQL is missing.

It would then transpile down to regular SQL to be executed.

The demise of SQL has been greatly exaggerated. If somebody wants to actually propose something better, the threshold is high. They should publish:

- A tool which transpiles their new language into (multiple versions of) SQLite, MySQL, PostgreSQL - This tool should always produce a correct result (Modernizr) or a specific error (e.g. upgrade MySQL to XX+ to use `NEW_LANGUAGE_CODE_SNIPPET` JSON functions) - A separate tool that up-piles SQLite, MySQL, PostgreSQL to the new language, possibly exploiting new streamlined expressions - An editor with IntelliSense for the new language

Even then you can probably achieve a lot value just by inputting table joins before the developer starts editing code.

---

If you're not doing that then you are just keyword spamming by mentioning SQL.

>The questions we often hear are “Why create a new query language?” and “What’s wrong with SQL?”. This post contains answers to both.

Ah, I can already tell I'm not going to like this article.

>lack of proper orthogonality — SQL is hard to compose;

Their reasoning: "The difference in structure is large enough to make any sort of source-level query reuse impractical."

This is blatantly false. The second example in the article using the join covers both the single-row and multi-row result cases. Additionally, SQL can be made very reusable and modular using tools like DBT, or within a DB using views or other constructs.

>lack of compactness — SQL is a large language;

It's certainly smaller in the keyword set than almost any other computer language I have used. This is like saying "It's a very cold day in the Sahara desert" when the temperature is 40C/105F.

>lack of consistency — SQL is inconsistent in syntax and semantics;

Is it? Or do people take liberties with their implementations? There is an SQL Standard and Postgres is compliant to it. Other databases not being fully ANSI SQL compliant is the fault of the DB creators, not the fault of SQL.

>poor system cohesion — SQL does not integrate well enough with application languages and protocols.

It is a relational language designed to be used within a relational database. Do the authors also complain that their screwdrivers are very inefficient at cutting meat?

They're onto something with the trickiness of NULL, but there have already been tons and tons of discussions on Hacker News - anyone with database experience, either analytical or transaction, will tell you that imagining a NULL-free world actually creates more problems then it solves.

I found this to be a highly misinformed article, personally; really it's a marketing ploy for their proprietary language. Just like most other "we have our own query language better than SQL" product, it probably isn't.

This article misses the single biggest failing of SQL, in my mind: that it's not a language. It's a family of incompatible languages, with similar syntax -- like "Lisp".

I can't write a program that works against 'any database', because switching databases means a lot of extra custom work. That means databases can't easily compete against each other. (Oracle's license terms sure don't help.) That's terrible. It's worse than x86/PPC/ARM -- at least with CPUs, a compiler can generate machine code for any of them, coming from the same source code.

I like a SQL a lot, but I wouldn't mind a nicer interface to a relational database. TutorialD seems more elegant and expressive than EdgeQL though. If I were designing a new query language, that's where I would look for inspiration. Anyway I'm glad to see people experimenting!

I am waiting for someone to write a paper titled "SQL NULL is (nearly) a Monad." It is "contagious" just like mapping over None. Sometimes I think it'd be cool to add Maybe<T> columns to Postgres, where operators would do the right thing for Some(x) vs None, but then I think, "Wait, that's how it already works!" At least it is very close. But one way or another I would love to see a re-assessment of SQL NULL from the perspective of category theory. If we could redo NULLs as Maybe types, what would that improve? Incidentally, Leonid Lybkin published a really cool paper in 2014 about handling NULLs with category-theory ideas, but it is more about drawing inferences from incomplete premises ("Incomplete data: what went wrong and how to fix it"). Maybe he'll write something about Maybe column types too. :-)

Also if you are interested in improving relational databases, Codd's 1979 paper "Extending the Database Relational Model to Capture More Meaning" is super interesting. The first part is where he adds NULLs (in order to add OUTER JOINs basically), but the second part is almost never mentioned and gives a way to query the schema itself as part of a database query. It looks a lot like doing graph database queries from your relational db. Also a better kind of EAV pattern. Also more support for OO-style inheritance. Somehow 40 years later it is still way more advanced than any existing RDBMS. It would totally break the parse-plan-execute pipeline of today's systems, but it is fascinating to think about. I wish more people would read this paper before trying to go "beyond relational"!

Not sure if there is something wrong in my brain but I have not been able to understand the hate against NULL. To me the three value logic feels very natural and just clicks. Lately I have been dipping my toes into Alteryx, which treats empty values in a way that definitely does not correspond to 'normal' NULL logic in databases and it feels very much like a constraint on building my workflows.

The one small thing I've often wished for is to place the from clause before the select clause. Having to type out the columns before the table means auto completion never works well.

I said this before and I will say it again: SQL is for data processing what IP is for networks: it's the neck of the hourglass.

There are many things happening below SQL layer (storage engines, implementations, hardware etc) as well as above it (applications, reporting engines, various services and whatnot). There are many things happening below IP layer (hardware, protocols) as well as above it (TCP, applications).

Even though there are better ways to build a neck of an hourglass, my bet is that (1) they are not much better and (2) it would be extremely difficult to get them replaced in all the existing hourglasses without breaking things terribly.

Conclusion: SQL is going to stay forever, like it or not.

Open source (Apache 2.0), and implemented on top of Postgres. This is quite interesting. I love SQL, but there's definitely room for improvement.

The critiques of SQL arent' wrong. I have written 4 parsers and can tell you the lack of orthogonality is a pain. Luckily we have the NIST compatibility guidelines to keep us safe. A replacement of SQL which contains the same compatibility tests would sell me on a new language, but it's really hard

We can do better. I use sql a lot though I’m not great at it. This seems much better.

The problem is getting a replacement on enough databases (storage engines?) so it’s universal. And performant. I’m not sure what’s involved with that but since there are a lot of open source dbs it seems possible.

Nice work

There have been tens of attempts over the years - a Dutch one called Xplain was quite good IIRC. But they all suffer from delivering too little extra (if fully adopted) against the incumbent.

Unfortunately, SQL is good enough for the majority of users and uses.

Saying you can do better than SQL is an extraordinarily bold claim, considering SQL is one of the most successful programming languages in human history and certainly the most successful fourth-generation programming language. Wide swaths of people you might ordinarily think of as non-programmers know how to use SQL in meaningful ways.

Doing better is certainly possible, but SQL is much better than a lot of people give it credit for.

In my opinion the example of the first chapter ("Lack of Orthogonality") is wrong. The subquery (is it called "inline subquery?)...

> SELECT name FROM emp WHERE role = 'dept head' AND deptno = dept.no

...should in my opinion definitely return only 1 row for each department - from a logical point of view returning multiple rows would mean that the data is corrupt upstream or that the organization itself is corrupt or that there is a lack of attributes in the DB (if no additional selection criteria like for example "management" or "operations" or "deputee" etc... can be added to the query - meaning that no sub/organization can have more than 1 person responsible for the exact same thing).

I admit that this is a very focused critic and that I'm very happy with the current behaviour of the generic SQL language and its special cases which are linked to the DB being used (using currently Oracle, MariaDB, Clickhouse - used DB2, Kudu through Cloudera stack, PostgreSQL, maybe something else) and how it stores/processes the data etc... .

> In situations where EdgeDB cannot infer the type of an empty set [2]

Why not bottom, or are containers not covariant? I'm worried that we're not clear about the difference between variables and values.

> Strictly speaking, EdgeQL sets are multisets [1]

Like nulls, this is another broken aspects of SQL.

Maybe you can address these concerns, but I'm skeptical that it's relational:

* It conflates types with relation variables

* It's using links rather than foreign key constraints

* It doesn't seem like the user can even specify a candidate key

* Which is why the "id" field is magical and special

* Constraints only seem to apply to scalars

The reason SQL has been successful is that tables are dead simple to understand, and you're throwing that out. This feels more like a graph database.

[1]: https://edgedb.com/docs/edgeql/overview#type-system [2]: https://edgedb.com/docs/edgeql/expressions/overview#set-cons...

>> We Can Do Better Than SQL (edgedb.com)

No you can't. Many companies have tried, including very large and successful companies and have failed miserably. Just try out the JSON query language used by MongoDB, including their aggregation pipelines, and you will absolutely love SQL.

"We Can Do Better Than SQL", sure, but how much better?

Fundamentally, SQL is a declarative language for databases, that makes the easy stuff easy, and the hard stuff doable.

If people want to build new front ends for SQL databases, to make it easier to build apps, I can buy that.

But trying to replace SQL as the standard declarative language for data is a fool's errand. You'd have better luck getting developers to switch away from git. People don't use SQL databases because they're fun for developers to hack on, they use them because they solve a business need.

If you want to innovate in the database space, I think you need to provide something that benefits end users (global availability, auto scaling, speed, client syncing ala Firebase, ...).

Amazing how our history of programming has so many things putting layers over SQL. I think mainly because the bridge between SQL and General Programming is often not too pretty ( string based queries with no validation ). But in my experience, it's often better to just embrace SQL. I won't argue the syntax is nice, but it is conceptually nice. Usually the further away you get from queries on relational databases, the more often you do things very inefficiently from a database point of view, to the point of being orders of magnitude worse.

The {} construct seems awfully close to NULL. Not sure I understand the fundamental difference -- having a third value in boolean contexts still introduces 3VL.

Why not just use an Option/Maybe type?

I acknowledge the problems with SQL that the author stated, but I still prefer SQL over the alternative presented.

What would be other alternatives to a declarative language like SQL? Prolog?

Why not Datalog?

It would seem to meet all your constraints, and is well-studied.

Category theory can do better than relational database theory: the open-source categorical query language CQL extends SQL with generalizations of abstractions such as schema mappings and queries while supporting automated theorem proving and fixing foundational semantic issues such as null handling.

http://categoricaldata.net

I'm fine with SQL, but i would also like there to be a more explicit language that requires you to invoke the indexes when querying explicitly (instead of implicitly like SQL). If you attempted to use an index that didn't exist the query would simply fail (instead of just run slowly).

Before diving into ways that SQL could improve I'd like to give some thanks to a real workhorse that has proved useful over decades, which is an incredibly long time in tech.

Could it be better? Sure, but author's proposal doesn't solve where I usually have problems. What are my pain points and what would I like to see instead?

1) One giant statement. Personally I really like Hadley Wickham's dplyr [1,2] (think "data pliers") which has a SQL like notion of joining different tables and selecting values but separates the filter, mutate and summarise verbs as separate steps in a pipeline rather than one huge statement. For transactions dplyr would have to add an update verb as well.

2) Hard to test, especially for more complex ETL. dplyr approach highlights that a lot of SQL these days is being used in ETL applications in addition to the usual retrieval, transactions and reporting. Being able to express as a pipeline of operations is easier for me to understand as execution is conceptually consecutive and I can unit test individual parts as part of a normal programming language environment.

3) My data isn't all tabular. Better support and semantics for non-scalar entries where value is a record itself like in json, BigQuery, Hive, Presto, etc.

4) Not that extendible. Better support for user defined operations (UDFs). More and more frequently I want to apply some non-trivial operation to data, e.g. run a machine learning model and it makes sense to do that as close as possible to the data usually. It is possible to do a fair bit in SQL itself with window functions but it is generally painful. You can point Hive at your jar and run a UDF but it is also painful to integrate and debug in my experience.

[1] https://cran.r-project.org/web/packages/dplyr/vignettes/dply... [2] https://datacarpentry.org/R-genomics/04-dplyr.html

We can do so much better! The problem really is, even if you have a nicer more expressive language that cross-compiles to SQL, it's really easy to end up with things that are impossible write in some databases. Even though SQL is based on Math, so it should "just work", the syntax is badly designed, and it is tied up with database consistency models, so it is easy to write something that should be possible but isn't. We can probably make much better compromises in a different language, but it is unclear how we could efficiently do backwards compatibility given all the existing databases and database clients.

From all the languages I learned since high school, SQL is the one that is pretty much still the same. Agreed, it's not perfect but I think it's one of the most stable things there are. New features get added, which creates inconsistencies between dialects. But if you stick to basics, it's all still very much the same. In 2000s XML was added, then later JSON and there will be future support for other formats also to integrate new data types.

The end of that headline is "but it's not really worth the effort and will likely not succeed".

Sure, SQL is not the most elegant language. But it works. It does what we need it to. Millions of developers knows it, countless tools speak it.

So I expect EdgeQL to go over like a lead balloon. Unless you can convince one of the big existing SQL servers to adopt it, almost no one will ever get the chance to use it, and it'll be a niche language for a niche database.

As someone with experience in using PostgreSQL, but who found that it would fall short for a current project due to among other things the problems you refer to as “poor system cohesion”, I am intrigued by EdgeQL.

Something I wonder though; can materialized views be created with EdgeQL? And if so, does updating materialized views still require fully rebuilding them, as PostgreSQL does, or can they be updated incrementally?

I fundamentally disagree with this article. SQL is arguably the most important language in a developers toolbox. The attempts to isolate and replace it's complexity through ORMs and nosql implementations has never resulted in a viable replacement.

Like mathematical syntax fits mathematical logic. So SQL matches data retrieval. Let's stop trying to replace it just because it's old. SQL is amazing.

EdgeQL looks really interesting - I'm glad I read through the article.

I think the issue - for me - with articles like this is the tendency to take the stance of "${EXISTING_TECH} sucks, let's drop it for this new thing"

I would have found this way more compelling if this line was the very first in the article:

> The relational model is still the most generally applicable and effective method of representing data.

I always have negative opinion on SQL but I can bear with it by just using a subset. Most of complex stuff are handled in application rather than SQL. Just use it as a simple data store.

I think Datalog is a better alternative. However, for SQL or DBMS is a field with more powerful lock-in than other fields. Or Oracle wouldn't live so long so well without significant improvement over these years.

Datalog appears to address a lot of these concerns

I’ve been arguing relational databases are an operational anti-pattern for a few years.

Your architecture shouldn’t start with an implementation choice. It should start with business modeling and drive down to tech choices, which may be one of many types of serialization/data storage technology.

Adopters of Domain-Driven Design have already moved past this argument.

I'm disappointed they left QBE (https://en.wikipedia.org/wiki/Query_by_Example) out of their history of SQL and relations. SQL vs QBE is one of the great examples of concise technology beating over-wrought UX.

Having worked with databases for a while, SQL seems to be useful because it forces you to think about how your data is structured. To me SQL is a thin wrapper around the relational algebra notation. The biggest problem I run into with SQL is that it is hard to tell how performant a complex query is before actually running it.

I'm confused by this:

> In EdgeQL, sets are flat, i.e. a set (including an empty one) cannot be an element of another set

It seems like in the last example, they're returning a set (of movies) that contains other sets (directors, cast, reviews). Am I misunderstanding? I guess it would be helpful to see what the output looks like for these examples.

You know what would be amazing to have for DB engines? Auto-optimization. For example, something that automatically determines which queries are slow and which indexes should be created to speed them up. Basically self-tuning. Not sure if this could be something for a machine learning model to do.

I like the groupwise maximum query...I always have to look that one up if I haven't done one in a while.

>> The NoSQL movement was born, in part, out of the frustration with the perceived stagnation and inadequacy of SQL databases.

Maybe. I would suggest tight budgets and lack of talented DBAs are the two primary reasons NoSQL became a thing. "We can make do without a SQL DBA."

No. You can't. IMNSHO

My real issue with SQL is how verbose it is. The amount of relative importance to the number of characters in SQL is often pretty poor (for example, when doing multi-table joins). Having to spend a few minutes deciphering each SQL query is a real pain

I like the fact that this is built on Postgres. Will be curious to give it a try on the side.

Since Date’s criticism of SQL is prominently referenced, the natural question is “Is EdgeDB a D, and, if not, why not?” (D referring to the class of relational systems for which Date and Darwen provided criteria.)

We can do better than saying everything always needs to be "better".

That's a pass for me for now. I think it's current state is fairly easy to pick up. Should this somehow be highly a adopted and truly show a benefit over the standard, I still might pass :)

I happen to like SQL and found it pretty easy to learn. Tons of resources out there. And now with Postgres ad MySQL adding JSON storage we're getting all the benefits of NoSQL in SQL.

Sidenote, has anyone seen how to actually talk to EdgeDB languages without a Client? As it seems currently only Python has a client.

(edit: I'm specifically wondering about Rust, if it matters)

Very exciting project. Not a very good name, as it conflates with the existing term, "Edge Computing", making one wonder if they're somehow related.

SQL has been great. Relational data is greater.

I absolutely hate working with graphQL, and document stores are a non-starter for projects with any decent amount of data.

Good luck with that.

How about APL? (0=2|x)/x←⍳20

Visit rosettacode.org and try looking up some solutions in SQL and then reference the APL variant. It's night and day.

How do I test for empty set? If I understand correctly then "SELECT * FROM Movies WHERE description = {};" will not work, right?

Seriously, were talking about a relational system where expressing a tree is super difficult. Why do we put up with this???

Academically, EdgeDB is very interesting, but practically, it seems to be solving a problem that does not exist. SQL is great for simple set operations and will lose its efficiency as you move towards more advanced features. The industry has accepted SQLs limitations and created new ways to interface with data, such as UDFs, pig, and pyspark. Those widely accepted alternatives can save you from doing something in SQL that you really shouldn't be doing in the first place.

> any element-wise operation on an empty set is, likewise, an empty set

Does this mean that "SELECT 1 + {}" gives "{}"?

if you can compare SQL every syntax to every usecase possible , until then we cant claim what we are doing is lot better.

More vendors just need to support ANSI SQL.

I would very interested in a language that transcribes to SQL the same way typescript transcribes to javascript.

"We can do better than SQL."

I heard this not too many years ago, and I am now very skeptical based upon those results.

Any performance metrics for edgedb ?

It's kind of funny to point out issues with SQL's composability, and in the same breath spec out a language in which "a set (including an empty one) cannot be an element of another set".

There are many real life problems with SQL but this doesn't feel like it's resolving any of them.

I wish it had slightly more details, like what does the last query on the page actually return?

Also I am not sure how it's better yet. SQL is typed and has a ton of historical overhead and used in many different database softwares.

This kind of just seems like a slightly different implementation of NoSQL syntax.

If there was a perfect, seamless pandas to SQL translator, that would be it.

criticism on SQL does not make it meaningful for a new database. I mean, an ORM with its own query language, a new SQL dialect, some language that compile to standard and optimized SQL are all good ideas.

I love he relational model, but any language where null = null does not return true is insane.

Yes, I understand the theoretical rationale behind three-value-logic. Over here where I'm getting actual work done, it's nothing but an obstacle.

Meh. It looks like just another graph database with a (possibly) better query language.

People of HN: if you really want to make money, make a open source (open core or whatever) horizontally scalable version of Versant OODBMS (Object-Oriented DBMS).

Object relational? No thanks. Just give me relational.

How many of the PostgreSQL features does edgedb support?

Just wanted to say I see a lot of value in wrangling with the problems inherent to SQL, so kudos to you.

Hopefully a transpiler API is in the works for all of those wonderful standards deviant implementations?

SQL reminds me of COBOL. Even replacing some of the UPPERCASE words in SQL with C-like symbols would be nice.

My two main gripes with SQL are:

- Lack of interoperability with other languages.

- General ugliness of server-side SQL (stored procedures and functions).

--------

The first one isn't really solved by ORM tools, AFAICT. You can't simply write the SQL query and transpile it into a nice, statically-typed method (with types derived from the actual database structure!) that you can call directly from your language.

For example, the database structure (I'm using T-SQL types here):

    CREATE TABLE T (
        A int PRIMARY KEY,
        B bigint NOT NULL
    );

And the query (T-SQL style parameter syntax):

    SELECT A, B FROM T WHERE A = @a;

Would produce the following method after transpilation (C#, hopefully self-explanatory):

    IEnumerable<(int A, long B)> Query(int a);

But when the database structure changes, that would automatically be reflected in the client language (after a build). For example, making B NULL-abe would produce:

    IEnumerable<(int A, long? B)> Query(int a);

--------

The second gripe is not very important if you use SQL just as a client-side query language. But making the database "defend" its data (in presence of complex business logic, or security requirements, not fully expressible through declarative constraints) is still best achieved by "funnelling" all clients through an API of stored procedures/functions/views, IMHO. As a bonus, this approach also tends to lower database round-trips.

There seems to be a general lack of composability/reusability:

- E.g. one stored procedure returning a set of rows cannot just "pipe" them into another procedure or query - it must first copy the rows into a (temporary) table.

- If your dialect allows you to declare a table variable, you cannot just assign it to another, you have to INSERT.

- You can reuse the same SQL fragment multiple times in the same query (through WITH), but not in different queries without encapsulating it in a function (and good luck with performance if your dialect doesn't inline function query plans or doesn't support functions at all).

- You cannot parametrize ORDER BY, GROUP BY, IN...

And myriad of other problems:

- The syntax is stuck in the '80ties, not well suited for auto-completion, no type inference.

- Lack of simple struct/tuple types in some dialects (may lead to huge parameter lists).

- Inconsistent exception / error handling behavior (sometimes the transaction is aborted, sometimes it isn't).

- Silent data truncation in some cases.

- NULL sometimes meaning "unknown" and sometimes "empty".

- No boolean expressions (e.g. you can't write A IS NULL = B IS NULL).

- And probably many more that are currently not at the top of my head... <RANT CLOSED>

It strikes me that we can do better on all these fronts (and more), without abandoning the "good" parts of relational databases.

I know, I've used AR.

to do better than sql we should do at least as good as sql

The correct title for this article is "SQL can do better"

I wholeheartedly agree with the "complaining" part of the post, but then here comes the "solution" and I'm not quite sold.

I mean, I don't necessarily claim that this is not a solution, it just isn't obvious to me at all. Maybe a more extensive explanation with better examples would make it all clear to me and I'd be super-hyped about it already, but right now I'm more like confused.

First off, it would be helpful show the table structure in the examples, and then compare EdgeQL query to the easiest solution in the SQL. After all, the readers supposedly use SQL almost daily for many years (I know I do), but don't know a thing about EdgeQL, so if it can do everything SQL can, but easier, such a comparison must make it pretty obvious.

TBH, my knowledge of the relational algebra is quite rusty by now, so maybe that's the problem, but as I remember, many queries we commonly use with the SQL are not really "relational" queries. Relational algebra deals with the sets of tuples, so things like count(*) or ORDER BY, or GROUP BY are not really a part of relational model, they just exist because they are super-helpful in what we usually are trying to achieve with SQL.

The problems with NULL are of a similar nature. I don't think we should pretend that NULL not being equal NULL is not useful (we don't expect "missing data" to be exactly the same value as another "missing data", do we?), and SELECT DISTINCT treating them as equals is not intuitive (for me it absolutely is: when I'm asking what values occur in a table, a missing entry is a missing entry to me, I don't want to see NULL 10000 times).

So, the introduction kind of made me to expect the solution to be more in compliance with relational concepts, but it doesn't seem to be, since all of the above are present in the EdgeQL in one form or another.

I'm not sure how {} is different from NULL in the EdgeQL, since {} seems to be kind of special thing here, the same as NULL is in the SQL. I mean, it doesn't behave like a true empty set at all! Non-empty set {value} OR {} = {value}, not {} (OR ≡ ∪). <bool>{} being {} instead of a true boolean value looks even more confusing to me than NULL OR (NOT NULL) = NULL. Same ternary algebra here.

Then, I don't really understand a concept of a flat set here. I do kind of understand what we are trying to achieve here: we want to solve the problem of SELECT x, (SELECT y) FROM z throwing an error in a runtime, if count(SELECT y) != 1. And it kind of would make sense in SQL, but it's explicitly advertised as a feature of EdgeQL that it can return trees (json-like structures), and here it doesn't seem to make sense that an output of a query (which is a "flat set", I guess?) cannot have another set as an element. Moreover, it obviusly can be ordered, which also isn't a property of how "set" is commonly defined in the set theory.

As a first impression, syntax and overall structure of the queries doesn't strike me as obvious as well. In fact, since

> SQL does not integrate well enough with application languages and protocols

I would ultimately hope for something that can be expressed as a number of function calls and commonly used data structures (a list, a dictionary/record, etc.) in most/any mainstream PLs, not a one more DSL as in "free form text". (Maybe with a more succinct DSL for the use in a console. Maybe.)

And my ultimate source of confusion. SQL is more or less the same thing even in these DBMS where it isn't exactly The SQL (like ClickHouse). And given I know the overall structure of the DBMS (like, is it, for instance, row-based or column-based?) I can make pretty good assumptions of performance of a given query, even though SQL is still declarative and I do not know what exactly the query-optimizer will do. Maybe it's just that I'm not used to it, but I don't have a feel about how performant would be the last EdgeQL example of the article, and if it would be better to separate it into several queries at some scale. In fact, I don't even understand if it's something that would be reasonable easy to implement in other major RDBMS', or is it ultimately EdgeDB-only feature? If so, it can be only as good as EdgeDB — and is it as good as PosgreSQL, or MariaDB, or sqlite? Unfortunately, in the real world I have to worry more about how performant and robust a thing is under load, than I can worry about programming convenience.

kdb is better than sql.

And thus a new SQL dialect was born.

Well then do it.

Let me guess: ya can't.

So if you can handle the "complexity" of SQL and don't use NULL, SQL is fine?

I use ORMs

What is EdgeDB? It is new to me.

So, to fix SQL, they're taking an SQL server and building a proprietary language on top of it.

We Can Do Better Than English. It is full of strange grammar and inconsistencies. The tooling for checking grammar throws false positives. In fact all it has going for it is that billions of people already speak it...well I suppose that is a pretty big advantage.