SQL is messy because describing the underlying data relationships are messy. The orthogonality example is a great illustration of this. What exactly should the result be if there are multiple dept heads? Should the result rows be duplicated? It's not clear how edgeQl would handle either (their edgQl orthogonality examples were constructed to only have result per sub query), but it seems like they would be kept together as sets. In that case, the result set is no longer a table, it's a dataframe, which is a useful data structure but is also not what relational databases do.

> SELECT extract(day from timestamp '2001-02-16 20:38:40');

SQL is messy because all the syntax was decided on before there was a community that really understood what good syntax is. The 'from' in that extract does nothing and I can't easily identify if extract is a function or some sort of crazy parsing construct - what are the arguments? Is "day from timestamp '2001-02-16 20:38:40'" the argument? Are the arguments "day", "timestamp" and "'2001-02-16 20:38:40'"?

Are these annoyances crippling? Yes. Yes they are crippling. It should be possible for an amateur to quickly write an SQL validator as a starting project; relational algebra is not complicated. Any fool can write a validator for lisp. Relational algebra isn't that much more complicated - we don't have loops or flow control to contend with here.

Tidyverse's dplyr [0] implements the relational model for real dirty data and, as might be expected for something implemented this century, does a much better job than SQL. Not because the operations are that much different (although gather() & spread() are welcome additions) but through ingenious innovations like, as mentioned, functions having arguments instead of I-don't-even-know-what-that-is.

And the pipe operator which is legitimately ingenious. Great operator for data.

[0] https://dplyr.tidyverse.org/reference/index.html

...why? SQL has been wildly paradigm-definingly useful for decades. It has driven hundreds of billions, perhaps trillions, of dollars of value. None of this hinges on the ability for an amateur to be able to write a validator for the language. It just seems like such a non-sequitur to me, such a strange thing to call out as a criticism.

SQL hasn't been wildly successful either because of or despite its syntax, it has been wildly successful because organizing data in the relational model and querying it declaratively is extremely powerful. The syntax is just not the interesting part. Any syntax that meets those criteria would do.

Also FWIW, I'm not sure that writing a sql validator _is_ all that hard. The grammars for any dialect are readily available and really not so hard to turn into an AST with any of your favorite tools (ANTLR, lex/yacc/bison, any of a million PEG parser generators). In many cases you don't even need to, we do static analysis of spark SQL using their own parser, for postgres can use the parser straight out of libpg.

I guess the OPs point is that if you can validate it easily, you can understand it. I'm certain I could write a lisp parser in 10 minutes, but I don't think that would mean I immediately understand all Clojure code (which is often pretty obtuse). You still need to learn all the underlying concepts and data structures.

Then often the storage and retrieval of data is an after thought during the development of an application. This is definitely true for CRUD apps. Then maybe the person used a framework to handle database interactions and now after a year or so, it’s not scaling well because the data access was not really given priority during development. So then the problem is the database engine or the syntax instead of proper design and planning.

That’s not to say there are no drawbacks to writing most dialects of SQL. I just believe most of the hate over SQL is coming from a procedural mindset to a relational one.

I think the point he is trying to make here is the same as the post was making concerning orthogonality. Having a smaller set of special syntax, and therefore an easier validator to write, means easier queries to write for the user. I don’t think he was implying that everyone who makes use of the language should know how to write a lexer for it.

> The syntax is just not the interesting part. Any syntax that meets those criteria would do.

I have to disagree with this. If this was the case we would still be programming everything in BASIC or C because they’re just another imperative programming language and it gets the job done. Having sugar syntax, a consistent language, etc.. all makes it easier for a programmer (or data analyst) to get the job he needs quicker (and therefore reduces cost), makes a program easier to maintain, and so on.

<snarky> Ability to manage persistency and correctness of SQL engines is very usefull while language interface not so much. </snarky>

The same can (almost) be said for Javascript. A language with a lot of foibles can still be successful if it is the only realistic mechanism to interact with the system.

> ...why?

I don't know about amateurs, but if the typical intended user of the language does not have a good grasp of what is and is not valid, they will often be reduced to trying one thing after another until they hit upon something that seems to work, with no real understanding of what it does. This is not how robust, correct software is written.

I do think this way of utilizing SQL is a problem, but I would lay the blame for it at the feet of a lamentably widespread anti-intellectualism in the field. It is true that the relational model is not trivial and must be learned before SQL makes much sense. But it is not too much to expect from someone who wants to be a practitioner in this field.

Here you got me thinking: do we even understand that now? There was quite a bit of contemplating about of semantics, data types and different kinds of abstractions in the programming languages for the last couple of generations already, but the general consensus about the syntax is that "nice syntax is nice, but it isn't really that important". Most modern languages loosely follow either something C-like or Algol-like, most people like what they are familiar with. There are sometimes claims that a good syntax should be successfully parsed by some relatively simple parser (which is totally not obvious, TBH, because while it is clear why C++ is a good counter-example, we don't really have that many good examples: classic LR-parsers or something like that are really not that powerful, and most real-world programming languages implement something very non-generic of their own). Some people claim that "good syntax is no syntax" (Lisp), and some say that Haskell has a good syntax (ikr). The bottom line being that this is just a matter of taste, unlike most of what we can say about types and data structures.

So, to summarize: I never actually heard a compelling general theory of good syntax.

This is completely offtopic, BTW, I agree that SQL is trash and it is even kinda funny that somebody tries to defend it like that, since for a long time it was sort of a textbook example of "why a language created with the idea to be used by non-technical people is a failure from the very beginning".

I started to think along these lines when I got serious about learning a foreign language. Humans appear to have some innate language ability that’s reflected, among other things, in commonalities between disparate languages. As far as I can tell, there’s been no serious effort to design a computer language to take advantage of this.

My pet theory about why the object.method(args) call syntax won is that it really is the most natural to read, as evidenced by the fact that most human languages follow subject-verb-object word order.

Most western languages maybe, but according to wikipedia[1] the most common is actually SOV rather than SVO.

[1] https://en.wikipedia.org/wiki/Word_order#Distribution_of_wor...

Nope. [1]

[1] https://wals.info/chapter/81 (Note that while this is the language count only, the same comment applies to the population using such word order as a native tongue: SOV and SVO are both common.)

https://docs.microsoft.com/en-us/dotnet/fsharp/language-refe...

Yes it works best when you have one argument to pass, as the first argument is passed implicitly.

https://hexdocs.pm/elixir/Kernel.html#%7C%3E/2

https://docs.julialang.org/en/v1/base/base/#Base.:|%3E

Now, yes, there are languages with multimethods, where the distinction is less obvious. For those that always dispatch on all arguments, a uniform syntax makes more sense. CLOS is a good example.

The object.method(args) "won" because it is a low barrier to entry from English to Programming indeed, but as you become more and more experienced and you shake off the imperative way of thinking in favour of declarative thinking functional languages becomes more expressive than imperative languages. They become better at managing complexity because they allow you to build higher and higher towers of abstraction.

And then homoiconicity [2] shines because of its 'universally grammatic' property.

1. https://en.wikipedia.org/wiki/Universal_grammar

2. https://en.wikipedia.org/wiki/Homoiconicity

I’m thinking about basic stuff like improper nouns, denoting a clause’s role by inflection or adposition instead of word order, and having a clearly defined spoken representation- none of this is specific to which abstract structures the language is describing.

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

BASIC and C didn't have a concept of objects* , which didn't seem to hinder adoption at all. object.method() became popular when OOP languages became popular, and that was because IDEs offered better autocompletion with that syntax.

* We could emulate objects with C in some gnarly ways. Almost no one did at the time, excluding C-with-classes/C++.

Also there's a non-language related advantage of dot notation. Type the name of the object, press dot and IDE will show you what things you can do with that object. You don't have that with other notations.

Which goes to your point-- form follows function, in syntax as with many other things.

It's not that they didn't understand it. There are languages predating SQL that have better syntax.

But SQL was one of the so-called "4th generation programming languages", that were supposed to be operating at a much higher level. And because of that, there was the notion that, if they also have a more "natural" syntax, it would allow people who are not programmers to use them effectively. Hence why SQL was originally SEQUEL - Structured English Query Language. It was meant to be written by domain experts, not database experts.

Of course, that failed (like countless other similar attempts since then), and so now we're stuck with this syntax for no good reason whatsoever.

No, the relational model is beautiful and consistent! SQL is messy because the syntax is not consistent and elegantly composable. It could have those properties and still present the same underlying data relationships.

See Linq in C# as an example for how a more composable query syntax can expose the same data model.

For example in Linq you can chain arbitrary many select/join/where/group by in arbitrary order. In SQL you need nested subqueries to achieve the same which is a much more convoluted syntax.

The only criticism I have of Linq is that it is a more general language targeting different back-ends including plain old objects. If Linq did not allow things that do not make sense in SQL this would be a little less jarring.

I also wish it supported full outer joins, unions, and window functions.

I was able to hire a four or five contractors who never used Mongo just by requiring Entity Framework/Linq experience and less than 4 hours of training.

WITH statements alleviate some of this issue by allowing you to write subqueries in any order.

[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...

Exceptions:

1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call.

2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized.

https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.

After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.

Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

What is beautiful and consistent is the relational algebra. The relation model relies on this formalism to model data by making some rather strong assumptions about how tuples and relations represent things and how relational operations are used to process them. And these assumptions are precisely what propagates to SQL and what some authors (see references in the article) consider controversial and messy. Then the question is whether and how these controversies can be fixed (or whether they are bugs or features).

A radically different approach to fix these controversies is to introduce a different formalism and different data model (as opposed to fixing only syntax) which is based on using functions. In other words, instead of using sets and set operations, we use in addition functions and function operations [1]. Here you can find an implementation of this approach:

https://github.com/prostodata/prosto Prosto is a data processing toolkit radically changing how data is processed by using both sets and functions and being a major alternative to map-reduce, join-groupby and other set-oriented approaches

[1] Concept-oriented model: Modeling and processing data using functions: https://www.researchgate.net/publication/337336089_Concept-o... -> Read introduction (two pages) for why having only sets is not enough and why functions are important

FWIW, I find the relational model to be much nicer than typical object models. Even in Java, which I write every day at work, I've found it to be much clearer to use immutable objects representing records. Not just for interacting with the database, but just for handling information in general.

Of course, it would be extremely painful to do without Lombok's @Value, @Builder, and @With annotations.

I mean, many RDBMS's have support for JSON and/or XML, so you can sort of get it.

However, I wonder if we'd be better of as an industry if we dealt with our data in code in a more relation-y way than an object-y way.

I do hate most ORMs. The only oneS I like are Linq based ORMs. The “ORM” is actually well integrated into the language.

You might as well use something like Dapper.

But yes agreed about ActiveRecord.

Already, we're introducing a weird inversion of syntax that, in my experience, trips up people learning it: data in SQL is stored as "rows" with "columns" inside "tables". More formally, we've got a hierarchical relationship where Tables > Rows > Columns, yet we write the query as Columns > Table > Rows.

There are far more consistent and beautiful querying languages than SQL: I would point to MongoDB's query language, which is less of a query language and more of a static javascript-interpretable library, but is still far easier to learn and more consistent than SQL. The same query in MongoDB: "db.users.find({ foo: "bar" });". How is this better? It embeds the operation in the statement ("find"); reading it hierarchically follows how the data is stored (Collection > Rows); the filtering operation is the same shape as the data being stored; and it naturally disallows most injection attacks.

I don't know MongoDB query language, but gah! that looks horrible. It uses three different syntaxes; dot notation, curlies/brackets and colon key value. Full of punctuation and doesn't read like english.

There is no distinction between noun "users" and verb "find". There's extraneous "db". does foo: "bar" mean equal or is it find() that determines the operator, maybe combo of both? how do I do other operations.

.Only if you are familiar with programing language that has that same syntax does any of it make sense. Otoh even educated non-programmers are gonna be able to read the SQL as SELECT "these things" FROM "this table" WHERE "these conditions are true".

I'd argue that most relational DB users are familiar with a programming language, and therefore most likely familiar with the C-style syntax. It's better to build on something that most of the potentials users are familiar with already.

> There is no distinction between noun "users" and verb "find".

There is no such distinction in natural language either (if you see words purely as sequences of characters), you have to know what is what and infer it from the context.

> even educated non-programmers are gonna be able to read the SQL as SELECT "these things" FROM "this table" WHERE "these conditions are true".

Yeah SQL looks a bit more like natural language at first glance, but that's about it. That familiarity is a false friend, it doesn't really help with the learning curve.

This kind of thinking reminds me of the ruby community trend a decade ago when DSLs were created to look beautiful and like written language. It's useless and confusing for long-term, practical purposes. Same with BDD style testing languages. The promise that non-technical people will feel right at home and can start contributing rarely lives up to reality.

Is it an issue? using symbols makes it easier to understand syntax.

> There is no distinction between noun "users" and verb "find".

Weird criticism from SQL. It can be distinguish by call with parenthesis.

IMO SQL keyword is harder to recognize.

MongoDB is absolutely still capable of being vulnerable to injection; its just harder, because it requires the client to provide an object which is parsed by your application with no data validation. In other words, SQL is vulnerable to injection by-default, because everything is a string, while you have to opt-in to being vulnerable with MongoDB, by writing your application to parse user input with no schema.

In reality, do applications do this? Hell yeah. Wire up a basic Express API, have it auto-parse any JSON its given, pass it straight to mongo, you'll be vulnerable. But, a backend which has any kind of type safety or API schema or GraphQL or something like that will be safer on mongodb than one with all that, on a SQL database with no ORM or parameterized queries or prepared statements.

https://hexdocs.pm/ecto/Ecto.Query.html#module-composition

seriuosly?

    db.orders.aggregate([
       {
          $lookup:
             {
               from: "warehouses",
               let: { order_item: "$item", order_qty: "$ordered" },
               pipeline: [
                  { $match:
                     { $expr:
                        { $and:
                           [
                             { $eq: [ "$stock_item",  "$$order_item" ] },
                             { $gte: [ "$instock", "$$order_qty" ] }
                           ]
                        }
                     }
                  },
                  { $project: { stock_item: 0, _id: 0 } }
               ],
               as: "stockdata"
             }
        }
    ])

    SELECT *, stockdata
    FROM orders
    WHERE stockdata IN (SELECT warehouse, instock
                        FROM warehouses
                        WHERE stock_item= orders.item
                        AND instock >= orders.ordered );

Doing the same in JavaScript is possible, but it's slow and cumbersome by comparison.

MongoDB queries, while being interpretable by javascript, aren't really javascript. You can't interact with the data using javascript (well, you can, using eval, but you shouldn't). You interact with the data via the query language, which is, again, expressed in JS, just like SQL is expressed in English.

It's more accurate to consider the Aggregation Pipeline as being the "composable" system to get at data in MongoDB. And its exceedingly composable; far more than SQL. It's literally a pipeline; a series of steps which fetch, mutate, filter, map, limit, calculate, correlate, relate, and otherwise interact with the data in a database. Each step operates on the output of the previous step, in series. You can programmatically swap steps in-and-out, in production, with no string manipulation or ORM, debug each step in series, remove steps, see the output, get performance characteristics on each step. There's no complex black-boxed query execution planner or compiler, because the query plan is the pipeline.

OR, another way to look at it, using MongoDB's ORM, which is quite bad if you ask me.

That query is not my invention, it comes directly from MongoDB documentation

If it looks bad, it means it is bad by design

https://docs.mongodb.com/manual/reference/operator/aggregati...

http://www.mongodb-is-web-scale.com/

An "idealized" NoSQL schema is far, far more complex than anything anyone used to SQL would arrive at ([1]), but most of that is because in a "pure" NoSQL/Document-oriented database, the query engine simply isn't that powerful (think Dynamo). MongoDB has an inordinately powerful array of tools to get at data in a performant way, and $lookup is available as one of those tools. Can it be misused? Yeah; just look at the parent comment to see clear misuse. But generally, it's very common to see.

Modern thinking around MongoDB schema design is closer to SQL than NoSQL/Dynamo. Arrays are bad, denormalization can be valuable but use sparingly, that kind of stuff.

[1] https://docs.aws.amazon.com/amazondynamodb/latest/developerg...

One example: We used mongodb to track the state of a general CSV import system. So, we'd have a document for each csv file a user imported, and on that document, we were storing errors which occurred during the import to later display to the user. Of course, in an array. Worked great for years, until one day, a user uploaded a very bad CSV, non-maliciously, with hundreds of thousands of lines, with dozens of errors on each line, generating an array millions of items large. The failure condition here was wild: the import just got slower, and slower, and slower, until eventually the (modestly provisioned) db cluster started failing. We immediately normalized that array into its own collection, re-ran the import, still generated millions of errors, but with no problem.

As always, a general rule doesn't apply in every situation, but in my experience, unless you have a really strong grasp on how a system's use will scale, years into the future, unbounded arrays are icky. Lookups across two collections are only a modest performance loss over a direct query, and if the arrays get up there in size, they can actually be faster.

In our case someone uploaded a _huge_ CSV with some misalignment in the columns so every row had an error.

The resulting the mongo document was larger the max document size (16MB?), so it couldn't even save to the database.

Mongo is painful to work with and I feel like I keep finding more reasons to hate it.

Not sure that is true. You can imagine having a smaller query language with clean semantics able to capture messy data relationships. Small functional expression languages come to mind. Where it lies on the spectrum from purely-table-form to can-hold-everything is a design choice.

Your query language doesn’t need to capture the underlying data model exactly. SQL is an example of this itself.

I’ve run into the orthogonality issue myself several times. You don’t need to drop SQL today, but for some data domains more expressive query models can work really well.

Are there really any SQL libraries in the traditional sense (i.e. reusable/composable SQL code with a well specified API)? SQL "libraries" typically focus on hiding the inconsistencies and the abhorrent syntax under the carpet.

And the user base is there, mostly because of the database engine properties and features. The query language itself is just a bad side-effect. And TFA does make a point that NoSQL abandoning the underlying RDBMS model is in fact a regression.

The article presents several examples where SQL's messiness cannot be plausibly attributed to underlying data relationship messiness.

Instead, perhaps a second format should be standardized, which is machine readable/writable, that more or less represents relational algebra + whatever extra features SQL supports. It can be clunky and verbose, as long as it's straight-forward and easily composable. It would effectively be like a compiler intermediate representation.

Once you have something like that, you can have as many front-ends as you want with whatever syntax you prefer.

Of course, getting all of the RDBMS vendors to agree to it is still a problem, and they'll probably all still include their own vendor-specific extensions and differences, because they want to lock you in to their system.

But at least maybe the open source ones could agree, which would still be quite beneficial.

Also that is only one of the things they are hoping to fix. It sounds like you're saying "We have 5 variants of SQL already so nobody is allowed to write any other database query languages ever. We must use SQL forever." which is stupid.

At least we don't have to change our whole data model or give up consistent data to try it!

You want flat result, aggregated result. Why does this have to be blamed on the data relationship and not SQL language itself?

For any graphical type of relationships, I find SQL utterly hard to express my queries properly. It feels like a assembly language at that point.

This is a common misunderstanding of what the relational in "relational database" means. It's not (primarily) about relationships, except insofar that relationships can be described using relations and queried using relational algebra. But the key concept is that of the mathematical relation, as per Wikipedia:

"In mathematics, an n-ary relation on n sets, is any subset of Cartesian product of the n sets (i.e., a collection of n-tuples)"

So the goal of relational db languages is to provide tools for querying these sets of sets. Joins, Unions, Selections (Restrictions), and Projections are some of the tools that are provided. SQL provides some of these, though often calls them confusing names.

For example, in SQL the "select" keyword begins the query statement, but selection proper (as per relational algebra, also called restriction) is actually what is expressed in the "where" clause. What comes after "select" in your query is technically "projection" (choosing which tuples/columns exist in the output). Many ORMs and similar tools get this messed up, because the authors of them know SQL, but not the relational algebra on which it is nominally based.

Aside from DDL (which I'm not sure how you would do without distinguishing between base relvars [tables], derived relvars [views], and other classes of relations), SQL doesn't particularly treat different kinds of relation differently beyond what is minimally necessary (you can't write, via insert or update, to a relation that isn't a relvar, for instance.)

Try Sparql.

At least the old ideas are resurging in the form of Neo4j (essentially a triplestore) and Cypher (essentially SPARQL).

Cypher - https://en.wikipedia.org/wiki/Cypher_(query_language)

What do you mean here? The difference between a table and a dataframe is that a dataframe is a construct held in memory, while a table is persisted storage written to a database.

Either way, queries need to be composed at runtime a lot of the time, so for many uses of SQL you can't just have the queries as blobs that you prepare ahead of time in some other tool - they must be objects you can work with programmatically.

Then you can use git and optionally build custom tools like a visual diff of diagram v1 vs v2.

The dev still needs to type the code first, which does not address any problems with SQL.

> Most devs use straight SQL as far as I know because once you get into more complex queries the graphical interface becomes a hassle.

Those interfaces may be limited, or the devs may not have enough experience writing "code" via a higher-level graphical interface. Going from writing SQL to creating dataflow diagrams may be a bit like switching from imperative to functional programming.

The "Selection" is the set of predicates that restrict the resulting relation. Projection is choosing which tuples ("columns") to use in it.

The relational algebra has no "tables", this is, again, a SQL thing. It has relations (sets of sets) and operations on them. In SQL "tables" are one kind of relation, and views are another.

https://xkcd.com/927/

EdgeQL looks... interesting, but I need to see more to decide for sure. Would be cool if they built out a translator, so you could pass in SQL and get back EdgeQL.

I don't think translating from SQL to take a look at the EdgeQL it produces makes much sense, since it'd result in very unidiomatic queries. SQL prefers joins and flat rows, while EdgeQL is based on following links and has great support for nested data output.

SQL is an incredibly expressive and flexible way to read, store, and update data. It's ubiquitous, so the SQL skills I learned six jobs and three industries ago are still relevant and useful to me today. Relational Databases and SQL are heavy lifters that I often relay upon to build projects and get things done.

SQL got a bad rap in many ways due to security issues, databases in general, and "web-scale".

SQL as a language within other languages is a nightmare from a security standpoint, and if language integrated query was more common across languages earlier on then this wouldn't have been an issue.

Databases generally depend on normalization, but normalization comes with interesting scaling problems and how do you replicate normalized schemas. Thus denormalization became a thing, and then the emergence of NoSQL and document stores started to infect everywhere. The JOIN was a killer too, and then the discipline required to do sharding made it annoying to manage, so easier to manage solutions became a thing.

I'm looking at databases in a different light these days with more appreciation, but now the hot new thing is GraphQL makes things... interesting. I don't view GraphQL as a server-side solution, but a client solution to overcome the limits of HTTP/1.1. However GraphQL clients are exceptionally complicated, and I'm not sure they are worth it. The only problem is that to overcome them requires engineers "to know how to do things", but that is a hostile stance. People want to go fast and make progress, and GraphQL enables that.

GraphQL is, in no way, faster than any REST alternative in terms of implementation speed. If anything, it is slower, as you need to be extremely methodical with your API changes, as (same with REST I suppose) deprecating fields / entities, for mobile clients specifically, is a PITA unless your clients have really nicely built out forced upgrades.

What GraphQL _does_ give you, is type safety and extreme client flexibility . It is a better solution than REST in almost every scenario, other than the initial learning curve, which takes a couple weeks and then you know it forever.

Would I recommend some startup write a GraphQL API for their MVP? No, just get something working. Are you at a more medium-sized company looking to build out your much more permanent API? Then yes, you should probably strongly consider GraphQL.

GraphQL is a hammer, and now every project is a nail.

Anecdotal, of course, but the first thing all FE developers I've ever worked with do when they start/join a project is add/suggest GraphQL/Apollo.

Nobody considers how awful things look on the back-end when you need to cache or make magic happen to avoid thousands of N+1 queries.

I believe unfortunately it has become the only way many front-end developers learn to interact with any back-end, and now everyone's forced to use it regardless of its drawbacks.

Same with React. Facebook managed to get free training for all of their future hires. I hate the company but that was a genius move.

I'm using similar security tricks to you: read-only queries with a time limit, against SQLite rather than PostgreSQL.

More here: https://simonwillison.net/2018/Oct/4/datasette-ideas/ and https://github.com/simonw/datasette

I see very little value in using GraphQL, when you can just write SQL on the client!

We desperately need frameworks to better facilitate this, like Hasura or Django -- DB migrations, permissions, authentication, real-time subscriptions, Admin UI.

My most wanted feature is SQL type providers, like Rezoom.SQL - https://github.com/rspeele/Rezoom.SQL

Redis is also easier.

The key is what are you designing against. If you design against a DB, then you may find that scaling beyond a single host with gotchas. But, if you have the discipline to keep everything within a document, then you can scale up easier as the relationships between documents is more relaxed.

However, cross document indexing and what-not creates more problems, and that in and of itself is an interesting challenge.

It's true that in the 2000-2010 period many SQL implementations struggled to scale with growth in websites (many other parts of the webstack did too).

The question is what is it going to cost in either licensing solutions or engineering effort.

Perhaps the best querying language I've ever used is Q-SQL, integrated into kdb+/q. Unlike SQL, it's actually part of the language (q/k) and, most importantly, it's modular and more expressive than SQL.

If you're interested in how we can do a lot better than sending strings to remote databases using an inexpressive and non-turing complete language, check it out: https://code.kx.com/q4m3/9_Queries_q-sql/

I love the idea of a functional algebra for querying a database, and while SQL is acceptable, it is far from great in my opinion.

https://suneido.com/info/suneidoc/Database/Queries/Syntax.ht...

Not only I prefer to work with SQL nowadays I also prefer SQL over any ORM in older codebases, ORMs are pretty useful for getting up to speed without caring about your persistence layer too much but after 17 years in this industry I've had my fair share of issues with ORMs to avoid them whenever I can.

Native SQL queries with placeholders for my parameters in their own files, loaded by my database driver to execute and return data is my go-to solution for data access, it's flexible, maintainable and readable if you treat SQL as your normal code (code reviews, quality standards, etc.).

Personally, I was lucky because I had great courses, even in highschool, regarding SQL including: How are rows working, what is normalization, how does it help with data and so on. So naturally I developed some feeling on how to handle database tables.

Linq in .net shows IMHO how queries can be expressed in a more consistent and composable syntax while still conforming to the relational model.

Lately I use nosql for very light data and otherwise our API outputs heavily cached and extremely simple data. Adding a database as a middleman wouldn’t make sense. Still fun, but I miss Postgres!

Me and my companion moved everything to stored procedures. Now every IT-department says that the product is VERY stable.

This piece reads like Joey being unable to open a carton of milk [1], "there's gotta be a better way!".

[1] https://www.youtube.com/watch?v=wwROPN3Fir8

As for SQL it has its warts, but im pragmatic when it comes to programming languages, like for example C, JavaScript, its also "ugly" but its often the best option anyway.

Trying to implement business rules about data relations outside of the DB is a nightmare.

[1] We use dynamic SQL within stored procedures for pivots.

Why would you do that?

Also there are tools out there that provide that functionality. Found with a few seconds of searching. https://host.apexsql.com/sql-tools-source-control.aspx

So, when we interact with a relational DB we need some kind of layer to map between the world of relations in the DB and the world of objects in our program.

It was mainly to save time in writing code to map columns to object properties really, the sql statements themselves were trivial even if you weren't lazy and just used select *.

Also, here's a now mainly historical pain most devs never encounter any more: Before ORMs and the various migrators, your object properties might not have the same name as your SQL columns. Yes, it was dumb when you did it, yes, it caused loads of bugs, yes, it actually happened quite a lot.

I am running a side project and yes, writing the native SQL statement to take your object and put it in the database is not a problem, put in the parameterised values and off you go.

But getting the data back from the database? Oh the horror. So much boilerplate in order to see if there are any records returned it all, if there are enough columns with the correct name for the kind of object you are making, if there is data or not in each column as appropriate for that specific column, if a given field can be coerced into being a string or an integer or a date or similar, then they're all marshaled into a dto object which is passed to the create new object validator. 800 lines of code later, and you may have an object back!

Dapper appears to be the sweet spot for me, I am still writing SQL queries and still designing the SQL tables myself, no orm magic here, but it handles the actual marshalling to and from an in-memory dto object versus data in the table for me, and that is very valuable time savings.

It's perfectly fine to just 'know' that the data is going to be an int and just do `var id = rs.getInt(id")`.

I'm on my phone so it's too fiddly to write my own brief example, but if you get rid of the silly comments in this you'll see you can do it all in a few lines, just very boilerplate lines:

https://thedeveloperblog.com/sqlconnection

I'd love to see a "SQL-like" language that compiles down to SQL itself, much like Babel or TypeScript in the JavaScript world. I think the tricky thing is that there is no single SQL target.

What about common table expressions? Or custom defined functions?

    with X as ( select... )

What's an example of what you're trying to do? I think views, user-defined functions, routines, and select aliases would cover all the bases...

So the jquery of SQL, then.

it's alright, it's consistent enough, it's good

I'm reading about datalog and prolog more and more but sql is ok

To me relational algebra is the beauty queen, and SQL is the "beauty mark" that prevents it from reaching perfection.

1. Anyone striving to build a better SQL should make a comprehensive list of common (but difficult!) database tasks for OLTP and OLAP workloads. This will expose the weakness of their language. SQL has had 50 years and myriads of improvements to cover all these common cases. This is not a fair fight, so come prepared.

2. It's not enough to be just "better than SQL" to replace it. SQL has such a huge momentum that a new language needs to be absolutely better _and_ it should have many features that SQL cannot possibly have. My nice-to-have list would contain predictable performance, lock ordering, ownership relations (for easy data cleanup), and a standard low level language which the query optimizer would output.

Predictable performance - this will always be not only implementation-dependent but data-dependent as well. In order to know whether a join will be efficient or not, you need to know things like relative sizes of the tables, which is not necessarily a language problem. I have worked with SQL implementations that had extensions to let the user annotate joins with relative sizes on each side, but I don't think that's quite what you mean.

Lock ordering - again, good databases should have defined semantics (Postgres, for instance, does take locks in order when using ORDER BY), but I'll grant that this one could be stronger. That said, I think this is pretty niche. How often are you doing large multi-row transactions where lock order is a serious problem? If I have enough volume that deadlock is likely, I probably have enough volume that I want to be breaking up the process into a sharded or two-phase commit anyway.

Ownership relations - I think this is a DDL problem rather than a SQL problem.

Low-level language - I don't think you'll get a portable low-level language here (at least not for any definition of "low level" that's much lower than the SQL AST) because, again, the basics are implementation-dependent. What kind of scan is the base atom of a query? Well, it depends - is your database distributed? sharded? row-store-based? column-store-based? I do wish more open source database drivers would let you play with the AST in memory (Postgres has ways to print it out, but I don't think there's a good API). That would tend to solve the most significant problem raised in the article (composability) - plugging together SQL clauses automatically is hard, but plugging together subtrees can be much easier.

This immediately jumped out at me from the parent comment. It would be entirely possible to implement a query language where you specify a plan for your query. But then you’d immediately lose the “better than SQL” competition, because your complexity and maintainability problems would skyrocket.

I’ve had to deal with this problem as an Oracle DBA, and it’s a complete nightmare. It starts with a statistics refresh ruining a couple of execution plans, so you start specifying them manually with the plan manager. Then it gets worse over time, because stats refreshes become a big risk and you don’t want to do them anymore. Eventually you get to the point where you pretty much only run verified plans. Then your verified plans slowly degrade overtime, because the underlying cardinality of every table is constantly changing. You’ve replaced the query optimiser with yourself, which is not only tedious work, but it’s simply not possible to do the job as well as any mainstream DB engine could.

There certainly are (rare) situations, where you need to provide hints in one form or another, but it's really a bloody nightmare to maintain and may completely bork, when you - say - upgrade to a new version of the database engine.

I work with relational databases since the early 90s and can give you a no-bullshit money back guarantee that you (not you personally, obviously) are not smarter than the optimizer.

Usually there are weird data patterns involved if you absolutely must provide hints. But basically:

Don't do it!

Edit: don't do it

This could come across as me saying “well it’s easy if you do it right”, but the thing is, normalizing a schema is incredibly simple. I would expect a relatively inexperienced software engineer to be able to pick it up literally just from reading the Wikipedia page. In my experience, the more common underlying problem is that inexperienced engineers (even if they’re only inexperienced in terms of SQL and RDBMS knowledge), don’t actually know what normal forms are, or why they’re useful.

Data structures and concurrency control is just fundamentally useful computer science, but for some reason it seems to be a topic a lot of people don’t pay enough attention too. Maybe it’s just my personal pet peeve, but I’ve seen too many projects start with “wow NoSQL is great”, and a few months later end up with giant nested loops in their lookups, and some poorly built custom implementation of MVCC in their business logic.

(NoSQL is great btw, just not for relational data)

This is the crucial insight that has made tons of money for me over the last 3 decades. I have all these trite HHOS jokes about it, like telling people denormalizing from a schema not in a normal form is actually the process of "abnormalization". And then there's generic EAVil, where there's nothing that can't be stored, not that nothing really ever means something, heheh.

For every well designed and useful schema I've seen, there were 999 awful ones. For example a physical data model where the query writer has to use string manipulation for joins is going to result in all kinds of suckage. The developer will conclude NoSql is a perfectly reasonable alternative. Even though a modern RDBMS provides all sorts of nifty features to identify and correct such issues ex-post-facto.

For a relational model to work well there must be an a priori data design performed with significant discipline. This seems like too much like Big Design Up Front for the average developer or technical manager to stomach these days. It is true that a well-designed data collection system will have a simpler data design more amenable to a distributed NoSQL system and will support emergent schema and relations which may be divined via machine learning. It will also make Big Ball Of Mud more convenient to implement, but that's a posteriori observation, heheh, like that damned halting problem...

Ran into one recently. Where a table was joined either to one or the other table, based on if a value was null in the first one.

This was fine, until we added a where clause to a, through multiple joins, base table for both options. This tanked the performance >1000x.[1] If we just returned the value it had basically no impact.

We tried solving it with using the result set as a base for a select where we did the filtering. This also resulted in the slow performance. In the end I solved it by wrapping the column in a function call, which solved it. And I still don't know why.

My guess is that somehow without the function call, it optimizes it into one query, which results in basically the original case, while a function forces the evaluation of the subquery first.

[1]Sub 1sec to over 15 minutes

left join sometable on sometable.someuid = isnull(someothertable.someuid, somethirdtable.someuid)

I guess that is such an uncommon case that it tripped up the optimizer completely.

Also: Thanks for writing "polymorphic associations". Not knowing that probably is why I struggled to find any info on it.

Edit: Both tables were actually the same one, just retrieved via different joins, so different data.[1]

[1]One was a company, the other was the company we need to send money to. This is for when deal with a daughter company but pay the parent company directly, for example.

We might have had a similar experience with this. The first time I stumbled across this problem though I was specifically trying to figure out “what is the relational way to implement polymorphism”, so I pretty much lucked into the a rather productive series of google searches.

[1]http://duhallowgreygeek.com/polymorphic-association-bad-sql-...

Because of the endless articles and comments saying basic computer science knowledge "isn't really needed" for the majority of programming jobs.

I don't want to give hints to the optimiser. I want to specify precisely how the data will be read and joined.

I appreciate this is this is the exception rather than the rule compared to how most developers would prefer to work. And this system is a bit unusual in that it's using a database for passing messages, which it isn't well suited for (especially not the way they're storing the messages.)

And before that there was ALPHA. From https://www.labouseur.com/courses/db/s2-Remembering-Codd-2.p... :

"Ted [Codd] also saw the potential of using predicate logic as a foundation for a database language. He discussed this possibility briefly in his 1969 and 1970 papers, and then, using the predicate logic idea as a basis, went on to describe in detail what was probably the very first relational language to be defined, Data Sublanguage ALPHA, in “A Data Base Sublanguage Founded on the Relational Calculus,” Proc. 1971 ACM SIGFIDET Workshop on Data Description, Access and Control, San Diego, Calif. (November 1971). ALPHA as such was never implemented, but it was extremely influential on certain other languages that were, including in particular the Ingres language QUEL and (to a lesser extent) SQL as well."

Here's an example from http://arwan.lecture.ub.ac.id/files/2013/10/4.-relationalcal... :

SQL:

  SELECT DISTINCT F.Name
  FROM FACULTY F
  WHERE NOT EXISTS
    (SELECT * FROM CLASS C
     WHERE F.Id=C.InstructorId AND C.Year=2002)

  {F.Name | FACULTY( F) AND NOT
    (∃C ∈ CLASS( F.Id=C.InstructorId AND C.Year=2002))}

[1]https://en.wikipedia.org/wiki/Relational_algebra

So, when you ask a database system to perform a query, you ask it in relational algebra terms because they’re easy to understand. It then transforms your query into a relational calculus-like form to shuffle things around, and then back to a different, but equivalent, algebraic form to actually execute.

Relational calculus is logical programming: you specify what the solution looks like and the query engine figures out the sequence of operations to find a solution.

EDIT: To explain, the relational algebra IS a map-reduce functional programming language. The relational calculus IS a logical programming language. They just use tuples and sets instead of the more familiar lists and strings of LISP, Forth, Prolog, etc.

And the sibling comment further up is right to point out that there are mechanical transformations to turn one into the other, just like there are automatic ways to compile Prolog into LISP (and vice-versa, though that is rarely done).

there exists, in, for all

And I feel like "is not ASCII" is pretty silly complaint in 2020 anyways. First of, if these characters would be widely used in 1980, they would be on every keyboard today, same as it was on keyboards for APL. Second, you don't really have them to be literally ∀ and ∃, it could have been &A and &E for example, and then every other IDE, including some fancy product of JetBrains, vim & emacs and most likely even your mysql-cli (at least some wrapper around it written in Python) would turn them into pretty ∀ and ∃ on your screen, same as some editors do for λ.

And third, which is a personal pet peeve of mine (so I probably should keep my mouth shut about that, but I cannot): I really, really wish we'd stop with all this ASCII bullshit already. There's nothing special about ASCII except it's 7-bit. And that's outdated.

It may come as a shock to your average American, but most of the texts in the world are not ASCII, just deal with it. If your product doesn't support that, it probably means it's broken, outdated and will eventually lose to a competition, so you don't do yourself a favour forgetting about that.

And with regards to input, there's nothing complicated about typing ∀ (or anything else like that) on an ordinary US-layout keyboard. For me ∀ is 3 very fluent keystrokes. I use XCompose, which is a blessing, but other operating systems have similar software with similar capabilities as well. I understand that this is not something most people are familiar with, but that's just bad and shouldn't be respected: using a mouse also wasn't familiar to every single PC-user out there. With demand comes the supply.

So I really dream about the day when typing nice, clear syntax like ∀ instead of awkward Perl-like character sequences (to read which you need mentally traumatizing professional deformation and/or an IDE post-processor) becomes the norm of programming.

Computer languages would be different if Ascii had provided code points for ∧ ∨ ∩ ∪ ≡ ↑ etc. It's too bad so much real estate is wasted in the control chars.

Original ASCII (1963) had ↑, but it was converted to ^ in 1967 so that it could double as a circumflex.

(Also ←, which was a good assignment operator; it's too bad _ didn't replace \ instead.)

Speaking of negativity ;)

The article does nicely illustrate many of the well-known shortcomings of SQL. Chris Date and Hugh Darwen unsuccessfully tried to fix SQL with Tutorial D. Never heard of it? Exactly.

I often joke that SQL is the COBOL of the 21st century. HHOS. There's worse things...e.g. COBOL.

Actually it's very difficult to deny the empirically discernible utility of relational databases and SQL. SQLite, for example.

I think the problem is that developers love to abstract everything. And with the database often becoming a god object it's often targeted for abstraction.

The problem is, querying data is a complex problem, complicated enough to require a query language.

So the developer goes off to abstract the database for their needs, a helper table object here, a helper row object there and eventually you start to see an Orm appear.

Developers need to be told to stop this behavior.

The abstraction is the thing you are representing in the database, not the database.

Your models should use plain SQL queries to execute behavior.

If you must, setup some helpers to do very basic crud operations, but as soon as you need to really alter a where or a select, drop to SQL, use the language as intended and watch your code suddenly become modular, readable and maintainable.

Yes, this means you will need to write SQL, yes this means you will write a lot of select and where statements, no this is not code duplication, stop freaking out about non problems.

I swear to god DRY is the source of and solution to all developer problems.

I agree that programmers too often seem to wave database design and integrity aside and create RDBMS/SQL worst-cases with ORMs and terrible queries, and then blame the tools.

Well, just the D class of languages; Tutorial D is (as the name suggests) a pedagogy-focusses implementation of the D requirements, the intent was that there would be one or more Industrial Ds.

(Dataphor is a D—the first implemented, IIRC—and is successful enough that it's a still-living commercial product.)

"Accrington Stanley"

https://www.youtube.com/watch?v=zPFrTBppRfw

This is similar to the browser (especially in the pre-WASM days). No matter what language you actually wrote your software in, eventually you were going to end up converting it to Javascript out of necessity. Would you still say that your program written in C, compiled with emscripten, was written in Javascript?