And really, projects like this are fighting the general ignorance the industry as a whole has about what a relational database actually is. It's better since the NoSQL wave crested and we stopped hearing stupid shit like "my data isn't tabular and doesn't fit in a schema", but there's still a preponderance of people who haven't stopped to learn the lessons that Date and Codd tried to teach decades ago, and constantly reach for graph/network/hierarchical databases without understanding why we originally tried to move beyond those back in the 70s.
Unfortunately because of this most attempts to do "better than SQL" end up not working from first principles and look like a dogs breakfast of ill-formed "that'd be neat" ideas.
And, yeah, as I said the "pure relational" projects that are out there are usually either academic projects or hobby. (Yes I've had a few of my own)
I think there might be hope in the Datalog-type systems that have emerged recently.
And those that are built on "first principles" end up looking like a dog's breakfast just as well because people expect to not have to learn a novel language.
People don't want to be told to forget everything they thought they knew. People don"t want to be told they've been wrong for 40 yrs. Even if you add that it's not entirely their own fault because they've been consistently misled by an entire industry.
You can crack the problem of supporting CREATE ASSERTION, they'll still dismiss it (handwave handwave) believing you're just bluffing.
And I mostly disagree with the optimism built on Datalog.
The other alternative might be to implement one's new thing as a patch to alter the frontend of Postgres. I looked at this many years ago and the engineering effort was immense. But it might be easier now.
1) SQL's type model has no concept of nested relations or rich datatypes; it's limited to basic scalar types <tho there are vendor additions to this>, so implementing e.g. Date's concept of "relation valued attributes" <essentially tables-in-tables> for example would be impractical
2) SQL's storage model doesn't map 1:1 with e.g. a relational model because SQL tables are bags instead of sets (duplicate rows are possible). This has a couple downsides. a) whatever more expressive and relational algebraic language you built would have to inherit that model and and have some way of dealing with bags instead of proper relational ranges <which are sets> and b) the presence of the possibility of dupes actually makes some query planner optimizations impossible to perform. <CJ Date goes into more depth explaining this, I don't have room for it here>
3) Likewise similar problems with SQL NULLs. NULLs are not in the "pure" relational model, so your language can't be pure. NULLs mess with the type system, and you end up inheriting a bunch of the weird casting/identity/comparison issues that come with NULLS. And NULLs, again mess with the query planner.
Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement.
(1) is a function of what SQL you might transpile to. Any SQL w/ rich types (like PG) or w/ JSON support (most others) will be able to meet (1) with varying levels of hackiness.
(2) even if some SQL RDBMS allows you to have tables w/o primary keys, the thing transpiling to SQL wouldn't be obligated to have tables w/o primary keys. Some SQL RDBMSes do not allow tables to lack PKs (e.g., SQLite3 doesn't), but even this wouldn't be a problem -if one wanted tables-with-dups- because one could use hidden row IDs to make up for that.
(3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL.
(4) is not a semantics issue, and anyways, some SQL RDBMSes let you implement tables yourself, and for the others there's CTEs and TEMP tables.
Of these only (1) is a problem if you want to target pure standard SQL, though it depends on which SQL standard you target.
We're definitely not talking about transpiling to 1980s SQL!
So, while there might some semantic impedance mismatches for a transpiling approach, and the choice of RDBMS to transpile to matters, on the whole I'm unconvinced that there are such problems that are very serious. My own imagination is failing me in my attempts to come with such semantic impedance mismatches.
> (3) just as with (2), the new thing transpiling to SQL wouldn't have to allow NULLs, and could generate SQL that uses `IS [NOT] NULL` / `IS [NOT] DISTINCT FROM NULL` to obtain the desired semantics if the new thing kept any notion of NULL.
You may disallow nulls stored, but anything like a non-inner join will produce nulls and then you're almost back to where you started.
> (4) is not a semantics issue, and anyways, [where 4 is "Finally, the execution model of SQL is always against a DBMS table or view. So in-memory relations, etc. look difficult to implement"].
Spot on and great catch. I missed that.
> I'm unconvinced that there are such problems that are very serious
Let me help you - simple sqlNG commands could transpile into more much complex normal sql target statements, breaking the optimiser. That's a very real risk.
Good post though
Breaking the optimizer is a big deal, yes. I have this fantasy that we could have an out-of-band hinting system for SQL, like "start the query plan with table source <alias>", or "use index <index-name> for table source <alias>", or "materialize table source <alias> and create these indices for it", or "index the CTEs by..." (the last one should really be automatically done by the optimizer, but when you can save it thinking time, that can be a good thing). In-band hinting has generally been problematic. Out-of-band hinting could either follow the query itself, or be provided in a SQL exec API as a separate argument, or as explicit method calls in query setup in LINKQ-like systems.
> You may disallow nulls stored, but anything like a non-inner join will produce nulls and then you're almost back to where you started.
Now that just points out that the problem arises naturally and isn't entirely SQL's fault. Null values are as much a problem for SQL as for C and many other languages.
The way modern programming languages deal with nulls is by insisting on full pattern matching on algebraic data types. So it follows that one might want to do the same in a new query language, and/or possibly as an extension to SQL. So that this would be OK because at the top-level, but not as a subquery:
SELECT a.name, b.thing
FROM a LEFT JOIN b USING (id)
> Good post though
Ditto.
> I have this fantasy that we could have an out-of-band hinting system for SQL,
I think not breaking the optimiser, and out of band hinting appear somewhat orthogonal but I'd be very glad to be wrong. I'm looking at OOB hinting for a different reason, but what you're thinking is very close to mine, and I'm struck by the fact you see hinting the same way I do (actual, and extensive, logical vs physical separation, inc. indexes as hints).
> and one would be forced to check for [nulls]
Even more interesting. I will shortly have something of relevance. Do you have a contact e-mail?
If you're writing a transpiler, you might as well make it an optimizer and use a solid hinting system to guide the target RDBMS' optimizer so as not to be at its mercy. If you were implementing from scratch rather than as a transpiler, you'd have to write an optimizer anyways.
Also, the reason optimizers can suck is precisely that there can be many ways to plan a query.
E.g., if you have 10 table sources INNER JOINed in sequence, you could start the query plan with any one of them, but not every one of those 10 tables will yield a good query plan when used as the starting table! You want to start such a query plan with the table that has a PK or index key that is most fully determined by the WHERE/ON constraints, by which I mean: the table source that will have the smallest cardinality given the constraints imposed on it by the query.
Sometimes the query that one has to optimize is a sub-query deeply buried in another, possibly in a VIEW, and the hinting needed may be specific to the context outside the view. Now inline hinting leads to having to inline sub-queries (repetition).
The many table sources INNER JOINed case is one I've actually run into.
Part of the problem seems to be that pushing constraints is a fairly advanced sort of query algebra: you need a good AST in order to be able to do it, and even then, it's not all you have to do to build a decent optimizer. If you look at the SQLite3 optimizer, it's gotten much better over the years at pushing constraints.
With every table source named, you can address hints about them, and you can do it out of band.
That or pick a target whose optimizer you can either work on or already is good enough for the transpiler's purposes. However, keeping things general is a good idea, especially when it comes to SQL (there's so much incompatible variation between SQLs!), so as to keep the transpiler generic and portable. The lack of a standard and solid hinting system is a real problem. Also, sometimes optimizers get worse.
> Do you have a contact e-mail?
${HN_username} @ gmail2) Supose you're writing sqlng, compiling it to sql. sqlng simply mandates a primary key on everything. Not a problem.
3) nulls can be a horror but the alternatives aren't pretty. Removing nulls means tables have to be broken up, see https://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without.... I believe there are better ways to handle this issue.
(final line) Makes no sense, a tab;e doesn't have to be disk-resident. Once read, with sufficient memory, the file sits in ram cache.
I think it also depends on how the 'model objects' are implemented. Is it Active Record or Domain Model? I think most of the complaints about ORM are actually complaints about Active Record / DAO, or just a crappy implementation. Also nothing wrong with using SQL in an otherwise ORMed application. Just harder to unit test, refactor etc.
My application code just calls stored procedures. It's unaware of the tables and underlying data model.
1) SPs usually mix persistence concerns with business logic. Making it harder to understand business intent. I find objects much more expressive than raw data. Sometimes you want to concentrate on the plain logic, without worrying about how something gets saved. Also you will not have to rewrite everything if you ever want to change how something is stored. Sometimes people switch from mysql to pg, or even doc or kv database. Keeping business logic separate enables this. And good ORM enables this separation.
2) Refactoring tooling and unit tests. SPs are lacking here significantly compared to general purpose languages.
3) Business logic outside of DB allows easier horizontal scaling.
PHP, ASP, ASP.NET, Angular, React, Django, whatever. Pick a year, pick a framework. The database stays steady.
I wrote about it at https://sive.rs/pg
and posted my SQL shopping cart at https://github.com/sivers/store
Please contact me if you'd like to share tips: https://sive.rs/contact
I read your blog post and I agree 100%
The database is the easiest place to code business logic that pertains to the data. Write it once, it's available for all client applications.
I think the staying power of SQL comes from its broader audience - programmers, analysts, and executives all use it. It's much easier to motivate programmers to learn a new, superior language than it is to motivate executives to learn a new technology that only gives ergonomic improvements.