jOOQ Blog

The Jooq blog is pretty great as well. https://blog.jooq.org/

Lukas Eder has a fantastic blog about SQL, Java, FP, and his library jOOQ: https://blog.jooq.org/. One of the things I sometimes find tricky with SQL is pattern matching a problem to a solution. It can be tricky to describe what you want and sometimes direct human help can't be beat so I do recommend Stackoverflow (normally I have mixed feelings about SO). There are few power users on SO like Craig Ringer and the horsesomethingsomething (can't recall the actual handle) that are helpful and friendly.

Since this is a bout Java and SQL in particular, I can only recommend our jOOQ blog, as we're heavily blogging about the two topics: http://blog.jooq.org Other good resources can be found in this top 100 list of Java blogs (not really containing 100 items, though): http://www.programcreek.com/2012/11/top-100-java-developers-blogs/ Oracle's Java tutorials are pretty cool, too: http://docs.oracle.com/javase/tutorial/index.html And this excellent SQL blog: http://use-the-index-luke.com/

https://blog.jooq.org/ is excellent if you do any SQL

Great book! Also, go read jOOQs blog: https://blog.jooq.org/

No, it should come after WHERE, GROUP BY but before ORDER BY. From my favorite SQL tutorial[1]: The lexical ordering is: SELECT FROM WHERE GROUP BY HAVING UNION ORDER BY while the logical order is: FROM WHERE GROUP BY HAVING SELECT UNION ORDER BY [1] https://blog.jooq.org/10-easy-steps-to-a-complete-understanding-of-sql…

I love this. I actually think making a tiny redis is way more ambitious than a SQL database. It looks like you have a good start! Two articles that really opened my eyes are: A Beginner’s Guide to the True Order of SQL Operations: https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-opera… Set Theory: the Method To Database Madness: https://medium.com/basecs/set-theory-the-method-to-database-madness-5e…

E.g. any TOP N query, e.g. music charts, best players, customers providing most revenue. Commonly used in dashboards. That is, the only use is not pagination as you claim. Regarding pagination, see https://blog.jooq.org/2014/08/06/join-the-no-offset-movement/

I found the jooq blog (not affiliated) excellent for understanding the concepts of SQL properly. This article is a great starting point: https://blog.jooq.org/2016/03/17/10-easy-steps-to-a-complete-understan… there are many other articles worth reading on there

More depth and detail in this post a couple of year ago on Lukas Eder's excellent SQL blog https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-true-order-o…

To me locking means waiting which means greatly reduced performance. That is the whole reason why event-based systems like NGINX and node.js are so popular. Look at the first slide from this talk by Professor Michael Stonebraker to see why more locking is not such a good idea. http://blog.jooq.org/2013/08/24/mit-prof-michael-stonebraker-the-tradi… Just because we can do it doesn't mean that we should do it.

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-opera…

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": "[email protected]", "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…

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

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

_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-app…

> They just released a proposal that effectively side-steps the problem of checked-exceptions-in-lambdas. The main problem of checked exception in lambdas is that Java's type system does not allow expressing statements like "`forEach` throws whatever the lambda throws". Here are two links: http://james-iry.blogspot.com/2012/02/checked-exceptions-might-have-th… https://blog.jooq.org/javas-checked-exceptions-are-just-weird-union-ty… The proposed syntax puts the exceptions on equal footing with the happy path. Not a bad idea, but irrelevant to the main problem.

You don’t need graphql for that, modern sql databases that support json documents already support something like this. In Postgres, either return nested json structures, or nested arrays. For an example see: https://shusson.info/post/building-nested-json-objects-with-postgres Also supported by some ORMs like JOOQ: https://blog.jooq.org/nesting-collections-with-jooq-3-14s-sql-xml-or-s…

Because a venn diagram does not describe join mechanics well. A venn diagram does however describes the UNION, INTERSECT, EXCEPT part of sql. https://blog.jooq.org/say-no-to-venn-diagrams-when-explaining-joins/ And more meta, it is an innocent slightly incorrect statement, stuff like that should not be down voted, reply with a correction. Save down votes for outright malicious posts.

some more tips and tricks https://blog.jooq.org/sql/ https://winand.at/sql-slides-for-developers

For me the aha moment was to understand the logical order of SQL operations: https://blog.jooq.org/a-beginners-guide-to-the-true-order-of-sql-opera… Never had a problem since.

I'm less familiar with OCaml and don't know off the top of my head. Doing a quick search I was only able to find references to type declaration variance [0], though I learned that Java also supports use-site variance too [1] [0] https://blog.janestreet.com/a-and-a/ [1] https://blog.jooq.org/tag/use-site-variance/

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/

Venn diagrams accurately describe set operations but most joins in common use in SQL are filtered Cartesian products. https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaini…

Even Lukas Eder, the creator of JOOQ, suggests to use Hibernate for CRUD and JOOQ for querying: https://blog.jooq.org/2015/03/24/jooq-vs-hibernate-when-to-choose-whic…

Postgres has a clever trick [1] you can use, though I wouldn't say it's something I would ever use. [1] https://blog.jooq.org/2018/05/14/selecting-all-columns-except-one-in-p…

Yeah it's mentioned in this blog post https://blog.jooq.org/2014/01/06/the-sql-languages-most-missing-featur… that SQL is missing Common Column Expressions, and that using Common Table Expressions reuse columns is simply a band-aid fix over the lack of those. For example, your example would have been more naturally expressed as SELECT partcol1 FROM (tbl WITH partcol1 AS AVG(col1) OVER (PARTITION BY col2)) WHERE partcol1 > 10.0

SQL and functional programming constructs are actually quite similar: SELECT -> map FROM -> stream JOIN -> flatMap WHERE -> filter GROUP BY -> collect ORDER BY -> sorted https://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivale…