jOOQ Blog

Fantastic.

There's an earlier article from the same blog that addresses this at a deep level

https://blog.jooq.org/2014/01/02/why-your-data-will-outlast-your-sexy-new-technology/

A couple of fundamental principles here:
Systems tend to outlast their developers
Data tends to outlast the systems operating on it

This is why I'd almost always go with database first design.

As a general rule (and test), a well designed database is useful outside of the context of the application that accesses it for persistence.

As an example from the kind of apps I've worked on: suppose you are writing a system for tracking and managing inventory, which will be used through a web interface. If you have direct access to the database, can you get useful information about orders, lead times, existing inventory levels, and so forth? If not, you don't have a useful database, and you are at risk. Migrating to a new system, which is probably inevitable down the road, will be very difficult as you no longer have control over your data.

Now, apps are useful, and code is useful. I wouldn't want to try to code up various supply chain management algorithms in SQL directly. The estimated lead time can be quite a calculation, and yeah, you are probably going to want to write python or ruby or java code to do that. But in the ruby world (with rails in particular), I have noticed databases used almost as a kind of object serialization and persistence, where the information makes almost no sense and can't…

You're basically dumping down a database to the web browser, including all of the internal metadata that's likely irrelevant to rendering the HTML.

For example, user role memberships:

{
"id": "c80b68c5-09ae-4a50-a447-df7c5a4a6d01",
"type": "user",
"attributes": {
"username": "kinshiki",
"roles": [
"ROLE_MEMBER",
"ROLE_GROUP_MEMBER",
"ROLE_POWER_UPLOADER"
],
"version": 1
}
}

Also record timestamp dates like created/changed, along with contact details that may be revealing sensitive info:

"attributes": {
"name": "SENPAI TEAM",
"locked": true,
"website": "https:\/\/discord.gg\/84e3j9b",
"ircServer": null,
"ircChannel": null,
"discord": "84e3j9b",
"contactEmail": "senpai.info@gmail.com",
"description": null,
"official": false,
"verified": false,
"createdAt": "2021-04-19T21:45:59+00:00",
"updatedAt": "2021-04-19T21:45:59+00:00",
"version": 1
}

But let's just go back to your response:

> Most of it is page filenames which indeed could be made optional

Do that! If you strip them out, the 529 kB document shrinks to 280 kB, which hardly seems worth the hassle, but when gzipped, this is a miniscule 13 kB! This is because those strings are hashes, which significantly reduces their compressibility compared to general JSON, which usually…

Joins can certainly work in a data format like YAML. For an example, see Honey SQL from the Clojure community [0] (though without something to contrast strings like Clojure's keywords, you miss out on the automatic parameterization).

You mentioned moving JOINs around, so I'll mention that if represented as structured data, you can move any of the top level components around, so you could more closely follow the "true order of SQL" [1]. For example, I would love to be able to put FROM before SELECT in all or almost all cases. There's also being able to share and add to something like a complicated WHERE clause, where essentially all programming languages have built-in facilities for robustly manipulating ordered and associative data compared to string manipulation, which is not well-suited for the task.

Now don't get me wrong, I don't particularly care for YAML (though it doesn't bother me that much), but as someone who's done their fair share of programmatic SQL creation and manipulation in strings, not having a native way to represent SQL as data is a mistake in my opinion.

0: https://github.com/seancorfield/honeysql#big-complicated-example
1: https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-operations/

> But I honestly think I'm missing something.

Since you confirmed my presumption, I don't think you're missing anything. Put another way, I think you're mostly missing the perspective of the typical ORM proponent.

From that perspective, even if the datastore isn't necessarily dumb, as such, it's more like an afterthought, conceptually subservient/inferior to the application, perhaps even a necessary evil. The job of the ORM is to release the data from that terrible (relational) paradigm and bring it into enlightened (object) world of whatever language the application is written in. This is, of course, a bit of a caricature for effect.

There was a fairly extensive discussion of a database-first paradigm a few weeks ago here on HN:

https://news.ycombinator.com/item?id=17247088
https://blog.jooq.org/2018/06/06/truth-first-or-why-you-should-mostly-implement-database-first-designs/

It's worth noting that even that author didn't suggest entirely avoiding an ORM, but, rather, using code-generation (in one that offers it).

Mostly because of SQL. Among other problems, it’s not set-oriented: there are duplicates in SQL, but neither in relational calculus, nor in algebra. Column order has meaning in SQL but not in the relational model (another consequence of SQL not being based on sets).
Another deviation is that SQL does not support column-less tables. On a rich-enough database (e.g. PostgreSQL) you can implement them yourself[0], but then that’s yet another incompatible across databases (one may think support for the empty table, which can only be one, is only of theoretical interest, but considering the model is based on set theory, it’s like not having a standard representation for the empty set).

[0]: https://blog.jooq.org/creating-tables-dum-and-dee-in-postgresql/

_relational_ is the key word you're missing.

ORMs map _objects_ to _relations_ (i.e. tables).

"Unlike ORM frameworks, MyBatis does not map Java objects to database tables but Java methods to SQL statements." https://en.wikipedia.org/wiki/MyBatis

"Jdbi is not an ORM. It is a convenience library to make Java database operations simpler and more pleasant to program than raw JDBC." https://jdbi.org/

"While jOOQ is not a full fledged ORM (as in an object graph persistence framework), there is still some convenience available to avoid hand-writing boring SQL for every day CRUD. That's the UpdatableRecord API [which is only one part of it and you don't have to use it]" https://blog.jooq.org/how-to-use-jooqs-updatablerecord-for-crud-to-apply-a-delta/