Isn't that what llvm does? Or GCC? Or a JIT?

> Possibly it would be better to send small programs written in a "better" language (something like Lua) to the server?

I'd certainly like something better than SQL, but going for an imperative, Turing complete language seems like the opposite direction of where things should be going in this scenario.

My theory is people would understand indexes and the like way more if they were writing up the lookups

Eeehhh, kinda. Sure, a compiler will let you just write some assembly by hand, but it won't let you write a optimization step, or even directly configure it. You have to tweak your code or the compiler flag to give the right hints so the compiler generates the binary code you want. Doesn't look much different than a query planner.

To make a loose metaphor, if SQL is like high level opengl, then I want the low level Vulkan and shader APIs for hand optimization.

I would like this especially for Apache Spark sql, since a bad plan that isn't aware of the data shape can blow gigabytes over the network unnecessarily.

However, using a type-unsafe (read weak typing), turing-complete language introduces the usual problems we know and love, such as infinite loops, runtime type errors, exceptions, and the like.

Personally, I'm looking forward to a WASM runtime for databases -- so we can run webassembly on the database. This COULD be carefully designed to be statically checked and, possibly, make it really hard to write runaway loops.

Weak typing: what about TypeScript?

Slow loops: yes, this is a problem. However, SQL (and even more so, GraphQL) also has a problem of large results / operations spanning too many entries. During development, the number of entries is fine, but not in production. Specially if indexes are missing, this is also a problem in SQL. (Endless loops are actually less of a problem than slow loops: it's easier to detect endless loops during development).

To process large results in a good way, often, pagination is needed; best would be keyset pagination. What if a statement returns a "continuation" statement in addition to the result set? If the client wants to get more results (or process more entries), then he would need to run the "continuation" statement.

Say a database doesn't provide SQL, but instead a set of low-level APIs (plus a ton of tools). Developers would then (be forced to) write properly modularized, versioned code on top of those APIs.

Writing SQL is like uploading a program; just like a shader to a GPU. And if the network stopped being the bottleneck, the DB server's load would become the problem. Just like with a GPU, you want to upload your programs and data as infrequently as possible. But unlike a GPU, you have full flexibility to permanently store your logic right there in your SQL DB. If it's well-documented, it's much more streamlined to write views and views-on-views, than to keep all that DB logic in your code. I don't find that it becomes a mess at all, because properly crafted views and procedures strongly encourage forced indexing and checking execution plans when you refactor them; as opposed to in-code, ad-hoc queries where it's hard to know if someone ever ran tests to optimize them.

Some statements do need to be very large, for efficiency. Others are better being broken up into sub-procedures, temp tables or even roundtrips. A great query is a work of art where each part of it is optimized not just for execution plan but also for read/write, network state and expected load. What I like about SQL is the flexibility to decide which way to go, and prioritize based on how often something will be used and what kind of strain it'll place on each of the layers in the stack. As with anything powerful, there are a million ways it can be abused. But what I've found in the ORM space and also with noSQL solutions is that simplicity of use comes at the price of flexibility and control.

[edit] >> null handling is complicated

This would probably also be my biggest complaint about the language itself; the existence of IS NULL / IS NOT NULL and both null-safe and -unsafe comparisons gets confusing (and especially painful if you're trying to write a parser). I think it's one of those things like `undefined` vs `null` vs `false` in Javascript where, on second thought, it's just too late to fix it without breaking half a billion websites.

I have to provide caution here, when you create views and stored procedures, I've always found it a mess to maintain:

1) VCS for stored procedures and views is usually non existent. Good luck understanding how these change over time and who changed them.

2) Deploying application logic alongside these systems is very painful, because unless you've versioned your views/stored procedures, when you make a breaking change you need to stop the entire application, make the change in the DB, and restart the entire application again on the new version. Zero-downtime deploys would require versioned views/stored procedures.

3) It quickly becomes a challenge to answer the question "what happens when I do X?" reliably, where X is insert, delete, or even select a row. Once you have complex enough stored procedures, modifying the data changes things in unexpected or unintended ways. A dev unaware of how the system works might assume that inserting a row simply inserts a row, but instead due to stored procedures hidden from application logic, it might cascade into inserting rows elsewhere, deleting rows, or modifying other rows in other tables. Discovering these issues without knowing that they could exist is often done around midnight during a production outage, because a new feature was released that did something in the DB that was presumed safe, but wasn't. If the code for the business logic were in one place, the application, it would been much easier to see what the change would actually do.

I understand entirely that performance gains from good DB use are astronomical, but in my humble experience, I've found them to be more trouble in the long run than they are worth.

e: spelling

This is a mindset problem, not a technology problem. Treat your stored procedures/functions/views like code, not like data. Keep a separate "code" schema, put your procedures/functions/views definitions in files, and store your files in Git, just like Java or Ruby code. Deployment then becomes an automated drop and recreate of the code schema in a single transaction with zero downtime.

> Deploying application logic alongside these systems is very painful

This is not my experience at all. The stored procedures form an API. If changing the API breaks your application, you are doing something fundamentally wrong. API versioning should only be necessary if third parties depend on your API, but I wouldn't recommend giving third parties direct access to the database anyway.

> Once you have complex enough stored procedures, modifying the data changes things in unexpected or unintended ways

I assume you mean triggers because stored procedures don't happen as side effects, you have to call them explicitly. Regarding triggers, I agree with everything you say.

[0] https://www.liquibase.org/

After some web searching, I came across a project[1] that's trying to do what I describe but it appears to be dead. I'm surprised that migration tools (that I looked through) don't already support such a basic approach that I suspect I'm missing something obvious. Some fundamental block in how SQL works or maybe it doesn't work that well in practice for, in concept, it sounds easy to script using what they call a shadow db and `pg_diff`.

[0]: https://docs.liquibase.com/concepts/bestpractices.html

[1]: https://github.com/cbowdon/ddl-diff

As for 3, I would prefer to "go to" procedure rather than "come from".

Oh, ORMs are not the solution. I agree, ORMs often bring new issues (like, loading too many entries in memory), and don't solve many problems (encapsulation).

> I don't ever want to have a lot of SQL logic in my code.

That works if you are a small team. With big teams / multiple teams, the small problems become a huge problem: someone / some team will write huge statements, will use weird syntax, and so on. (C++ has similar problems btw) Specially the lack of proper encapsulation can become a huge problem.

> Some statements do need to be very large, for efficiency.

Well, then the problem is that SQL doesn't give you a good way to avoid large statements.

> there are a million ways it can be abused

Exactly. Other languages (like Java) make it harder to abuse the language. If you really want, you still can; but it's easier to refactor and maintain.

I agree - this is a really interesting way to put this.

1. Support trailing commas.

2. Add operator `is` which works like `==` but for nulls it returns `true`. Could also remove automatic type casting, I'd be happy about that, never liked it.

3. Support trailing `or`-s and `and`-s.

So query could look like

    select a, b,
    from t
    where (a.x is :x and a.y is :y and) or
          (b.x is :x and) or

    select
      a,
      b,
    from t
    where any(
      all(a.x is :x, a.y is :y,),
      b.x is :x,
    )

It's not always possible to avoid, and it makes perfect sense sometimes, but it'd be better if folks didn't lean into OR so much.

How many of those are "innovative"? How many of those have an ecosystem around them that can be taken seriously?

There's maybe one innovative language per decade. Minor improvements to an existing language (with a load of unnecessary syntax changes) don't make up for programming in a barren ecosystem. And a bunch of wrappers around C libraries don't make an ecosystem, they don't allow the language to materialize any actual new paradigms and just result in a C dialect.

In programming languages we have imperative and functional languages, type-safe / untyped, memory managed / unmanaged, low-level vs high level, interpreted / JIT / ahead-of-time. So much innovation! And SQL is almost 50 years old, and basically still the same (plus adding features). And now maybe GraphQL. Very little innovation.

I may be in a minority, but I like the projection list being first in simple queries. To help with auto-complete just type the other parts first then fill in the select list after? This falls apart with CTEs though as they have to come first syntactically, so now they exist maybe at the end is the best place for the output list so it is easy to find in a complex statement.

> Many developers end up writing huge SQL statements

Compossibility can definitely be a significant problem with SQL. There are structures that can help significantly, views, CTEs, etc, but they all have either potential maintainability issues or potential performance issues.

Partly, from a performance PoV, this is an issue with the query optimisers. On the maintainability matter I think the syntax for CTEs and sub-queries generally could have been much better thought out – I've seen a few attempts to beautify (or replace) SQL that essentially amount to moving parts around so you can make CTEs & sub-queries look more like functions/procedures in imperative languages (transpiling the result back to SQL for submission to the database after).

> Regular programming languages don't require "query optimizers".

Because regular programming languages are “lower level” in that regard and require you to do that optimisation yourself. The point of a language like SQL is that you describe the shape and content of what you want and don't have to worry about the most efficient way to retrieve that information.

And come to think of it, many regular languages do have optimisers. Think of all the work a compiler is doing or the JIT compiler in JS engines and their ilk – they don't just blindly interpret each line of C/other into a distinct selection of CPU instructions. Consider declarative frameworks for those languages too, like many used for SPAs which deal with DOM manipulation optimisation for you, and the many attempts at no-code or low-code development solutions which seek to abstract such concerns away from the solving of problems that require programming (by doing what SQL attempts: trying to translate what you want, stated as simply as possible, into what is needed to do that efficiently).

Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information? In a regular programming language, typically people don't complain too much about this "missing feature" of a query optimizer. It's about modularization / abstraction. In SQL, the data model is king; in a regular programming language, the API is king. Yes, compilers have optimizers, but they optimize the machine instructions, not (so much) the algorithms that are used (scan vs using an index / using which index).

Maybe I just have seen too many cases where developers didn't create the necessary indexes, or databases did't use the (obviously) best indexes, or developers writing 10'000 lines SQL statements (that I then re-wrote in Visual Basic, and the resulting program was much faster). Or 1000 line GraphQL query that returns 50 MB of JSON. Yes, I have also seen Java developers writing exponential algorithms or worse, and running into memory issues - but I personally found it easier to analyze and optimize then SQL. Maybe I have some bias thought.

On the other hand, as you noted, SQL is forever while programs and programming languages come and go. Maybe having a declarative data mindset is appropriate since data is more valuable and durable than code.

> Yes! But I wonder: wouldn't it be better if the developer has to specify how he wants to retrieve the information?

I'd be against making SQL lower level in that sense – part of its purpose is to be back-end agnostic and once you start letting the dev get too details you are locking code to one implementation. Of course this isn't how things generally work anyway beyond the basics, with engine specific hints and such, but let us not intentionally make that more of an issue!

Though if we do go deliberately back-end specific, perhaps a way of describing a query plan more directly and saying “use this” is what you are wanting. Something like plan forcing in SQL Server's query store, but with you actually dictating the plan not picking one the engine has created from SQL. That way SQL stays purer but you have the option of something more direct, like mixing bits of (obviously platform specific) assembler into your C or other higher-level code. There will still be terrible code created that way though – possibly much worse.

Low-level languages can be backend agnostic. For example C or Rust allows to write low-level code that is back-end agnostic, and doesn't cause cause vendor or technology lock-in.

What I like to have is guarantees similar to what a RTOS (real-time operating system) has. Maybe using hints, or something like that. This could be standardized. Languages like C or Rust have such guarantees naturally: statements have some kind of guaranteed time constraints (that's why RTOSes can be mostly written in C). Sure, loops can be endless, but loops, e.g. in Rust, can't become 1 million times slower suddenly because the backend changed.

Why not use a key–value store or just a file system, if you want to write everything yourself?

I want a good abstraction (API) that allows to write programs that access and manipulate data in an efficient and simple way. But I argue it shouldn't be a declarative set manipulation language like SQL, but a language that is more low-level and makes it hard to (by mistake) retrieve or update millions of rows, or makes it hard to write statements that are very inefficient, and makes it hard to have systems that lack encapsulation, so you end up having hundreds of tables that are entangled in a big mess.

I do like the fact that network roundtrips can be reduced: you can send SQL (or GraphQL) statements from the client to the server, where the statements are processed and the result is returned. But it's too easy for the result to become large, or statements too slow to be processed. With a key-value store (like Redis) you can't do that. Or can you send a list of commands to Redis (or some other key-value store) that are executed in a safe and efficient way (with guaranteed runtime) and the result is returned? That would be what I look for.

This is the limiting factor there. The best plan is going to vary depending on data patterns and (if variables are involved) input parameters. You would have to spend a potentially infinite amount of time testing for different patterns and store which plan is best for each, or do the analysis for every possible parameter each time the referenced data changes.

> plans have a tiny budget devoted to their optimisation

The reason planners get minimal time for planning is that in the general you quickly hit the point of diminishing returns. Their job is to find something good enough and find it quickly. They are helped by pre-computed hints like index stats, so you could perhaps spend more time building other helpful pre-computed data but you would need to reassess this data regularly (as index stats are reconsidered under certain conditions) and again you hit a point of diminishing returns in terms of the amount of time you spend on this as data it modified compared to the potential benefits from better plans.

> For something like a view

Databases do not create query plans for views when the view is created. They create query plans for overall statements that use the views when those statements are executed (or chose a cached one if the statement is sufficiently similar to a previous one for which there is a cached plan).

The underlying implementation will always affect top level access performance. "Best" will always be implementation dependent.

That said, the DBs I use most (Postgres, MySQL, SQLite, and their derivatives) have pretty consistently improved performance from version to version with surprisingly few regressions.

Also there have been more "knobs and dials" added to get what you want.

  WITH foo MATERIALIZED AS …

Agreed

> null handling is complicated;

Mostly the issue is that null in dbs has different semantics than all programming languages. I wish programming languages used the db style of handling nulls. In dbs, null means that the value is unknown and can't be reasoned about. DB uses nulls in how people reason arguments: "Joe's age is unknown, Jane is 25 years old. Are they of same age? We can't say if their age are equal, or unequal, because we don't know."

> the syntax in general is weird.

I find syntax to be like any time switching a language. Python is weird to C++. Atleast with SQL, I find usage docs more understandable than many programming languages.

> Compatibility is a problem

This sounds like a complaint that two different programming languages are not providing same features. Or two operating systems have different UI toolkits (and different menu conventions, like where should the 'preferences' be under).

I wouldn't expect sqlite to be Oracle / MSSQL / PGSQL compatible.

Except it can also mean it's `null` in that the field is optional and the record doesn't have it.

A table ("relation") describes one entity. The attributes of the entity are reflected in the table column set, and each table row represents a different instance of said entity. Marking a column as nullable means that not knowing that attribute is a recoverable situation (for example, because it is only stored for informational purposes and not for processing).

If you're using the same table to store multiple entities, using nullable columns to indicate if a row represents entity A or entity B, you're doing it wrong.

For example, it is optional to enter the colour of the car I own. So my car colour is null. The database can't confirm if my car is Red or isn't Red, because it is an unknown value.

That's one way to look at it, but another way is to consider that, as a sort of assembly, SQL is as good a language to express relational algebra as any, given that many devs don't usually write a lot of raw SQL themselves.

When you look at the ORM and query builder space, there are a lot of different solutions - you don't have to like them (for example, I don't generally like ORMs), but they definitely exist, and they have all sorts of capabilities for type-safety, composability, and so on.

Actually, SQL is used to hide DBMS implementation complexity and diversity. It was a great abstraction when RDBMS tech progressed rapidly. Now it has matured to stability, and it makes sense to try a better language.

> SQL is used to reduce network roundtrips.

What I mean is: the database has an API that allows the client to specify (almost) arbitrary complex queries and operations. Regular REST APIs don't support this. GraphQL also supports it. The client sending queries to the database is a great way to reduce network roundtrips. Key-value stores that only support get/put/remove/list require a lot more network roundtrips for slightly complicated operations.

> it makes sense to try a better language.

I argue that the language itself (the syntax) isn't the main problem: part of the problem might be that SQL (even with a better syntax) would _still_ have very similar problems: missing indexes / wrong query plans, missing encapsulation which results in a complex mess of entangled code, things that work fine with small data sets but are extremely slow with large data sets,...

RDBMS's have been in use for more than 50 years:

https://en.wikipedia.org/wiki/Relational_database

There are hundreds of implementations some of which are decades old:

https://en.wikipedia.org/wiki/List_of_relational_database_ma...

Oracle and several other companies' (some of which are now defunct or were acquired by other companies) primary products were RDBMS software.

New implementations do not indicate lack of maturity but perhaps the wide availability of tools and libraries to build RBDMS's and their utility.

Perhaps you are referring to database systems in general and not relational databases?

I think this can unlock a lot more experimentation when it comes to SQL-alternative "languages" or approaches where plain text languages are just skipped all-together and are instead replaced by good libraries that act directly on query plans.

[0]: https://substrait.io/

Have you even used Jetbrains' IDE's DataGrip?

What we have is generally good enough for the purposes of set manipulation. SQL is not a programming language, and should not be approached as a programming language. It's a way to express relational algebra.

This is similar to complaining about any other system of algebra having issues... and wanting to come up with a New Way. We don't really need a new way to describe linear algebra; what we have works. And it may seem unintelligible and a mess from the outside, but if you take the time to learn it, you'll understand why and how it's laid out the way it is, and learn to get over it.

The other point being, there are a set of operations you can do on sets. SQL is as low level as you're going to get in regards to that. Any new language would either: change the labeling of the underlying operations (e.g. changing joins as a label for Cartesian products to some other label), abstract away the low-level operations into something completely else (foolish, in my view), or tweak it in minor ways to suit the preferences of the individual (again, not worth the effort in my view, just learn the syntax). All of these are inefficient; and all endeavors to make it more efficient have failed (as far as I can tell) -- primarily because the people working on these projects (such as your self) do not have a sufficient maturity in this space to understand why things are done the way they are, i.e. "Chesterton's Fence."

> Many developers end up writing huge SQL statements (one statement that is hundreds of lines). You wouldn't do that in a "proper" programming language; but in languages like SQL (or maybe Excel) this will happen.

Programming language and set manipulation languages are incomparable. The first is a language for describing procedural instructions and the last is a way to describe set transformations. It's not wise to apply the same set of standards to both. It's like complaining a proof is tediously long, when the actual underlying operations are simple -- you're missing the point. SQL has to be long and detailed -- when you want to be sure the data you're receiving is exactly the way you specified it to be (i.e. no compiler shenanigans where it turns your rough intent into concrete instructions).

However, I will concede that I've seen some monstrous SQL in the wild... mostly written by people who don't really know SQL... and which could've been greatly shortened by knowing the little tricks that are database-specific (and similarly, I've seen enough people request a dataset to do processing on the server-side in herculean efforts, which could've been done faster and written quicker in the database).

> Another problem is proper encapsulation. Views can be used, but often developers have access to all tables, and if you have many components this becomes a mess. Regular programming languages have module system and good encapsulation.

I don't understand. Permissions can be tweaked however which way you want. Am I missing something?

> SQL statements often don't use indexes, for one reason or the other. With regular programming languages, it is harder to end up in this position (as you have to define the API properly: encapsulation). Regular programming languages don't require "query optimizers".

This is a trivial problem; but if you don't know why it happens, I can understand being befuddled by it. Query optimizers are involved so your access to the underlying data -- and the manipulations on it -- are done in the least costly fashion (i.e. in a rough sense to reduce the algorithmic complexity of your SQL to its least possible complexity) in regards to the set of hardware your cluster is running on, and the various access statistics involved. Regular programming languages do not have "query optimizers" -- but various libraries do have their own optimizations to reach the same end (as do compilers... compilers are nothing but optimizations upon optimizations in the same vein).

> SQL is used to reduce network roundtrips. Clients send SQL (or GraphQL) statements to the server, which are actually small programs. Those programs are executed on the server, and the response is sent to the client. (Stored procedures and for GraphQL persisted queries can be used - but that's often happening afterwards.) Possibly it would be better to send small programs written in a "better" language (something like Lua) to the server?

It would be very inefficient for general purpose. For most cases, the query optimizer can take your SQL and bring back your result sets in the most efficient way possible (in relation to the SQL you've written).

If you want to do the underlying operations yourself, instead of relying on the query optimizer, then you can use a compiled language like C and write your own extensions -- though it would take much more effort to do it properly than simply learning how your database works, and how to work with the query optimizer (rather than against).

The error is confounding a fourth generation DSL for set theory with a third generation general purpose application programming language. There's a really good reason ORMs have their impedance mismatch with relational databases.

Aside from this minor nit, your comment is spot on!

Well, that can be said about any programming language (assembler, C,...) but yet there is innovation in this area still. And that's good. But in the database space, there is little innovation. And no, I don't think what we have (SQL, GraphQL) is good enough.

> SQL is not a programming language

Yes and no. SQL is an API that allows changing the state. But (same as programming languages such as C) it has advantages and disadvantages.

> if you take the time to learn it

Again, that can be said about assembler as well :-) Or Excel. You can do a lot in Excel.

> SQL is as low level as you're going to get in regards to that.

Absolutely no: SQL is a high-level language. And I argue you don't need a declarative set-manipulation language at all, to store and retrieve data. Computers are not set-manipulation machines: they are instruction-based machines. And the popular programming languages reflect that: the most popular languages (Python, C, C++, Java, C#, Visual Basic, Javascript) are _all_ imperative languages, which is close to how computers operate. And that's relatively easy for people (developers) to understand. Functional languages are not that popular. So, SQL is an exception here.

> SQL has to be long and detailed

I argue the reason is that SQL is not a very good abstraction, if you need extremely large statements that are hard to understand and almost impossible to maintain. Same for Excel.

> I will concede that I've seen some monstrous SQL in the wild... mostly written by people who don't really know SQL

Yes. I argue that SQL _favors_ (or at least allows for) writing complicated, messy code that lacks encapsulation. In a larger company, you will have some percentage of people that are not all that great, and you don't want to pick a language that will easily result in a complete mess. Languages such as Java favor modular, well encapsulated code: it is hard to make a complete mess in Java.

> Permissions can be tweaked

Permissions are not an encapsulation mechanism, in the way "private" fields in Java / C++ are protecting other programs for accessing internal.

>> SQL statements often don't use indexes > This is a trivial problem

Well, for the experts, yes. But not for the novice programmer. And you will have some percentage of novice programmers. In a larger shop, you typically (still) need a "database administrator" or a group of (experienced) people that ensure indexes are made correctly.

> various libraries do have their own optimizations

Libraries typically don't have optimizers. Compilers have, yes, but those are not picking indexes or such that can result in one million times slower or faster runtime. The compilers typically can speed up things by a factor of 10, at most (constant factor). Query optimizers are very different. (Again, I wrote 3 relational databases, and 4 query optimizers.)