Standard SQL/JSON – The Sobering Parts

It's been almost 1 year now since jOOQ 3.14 was released in October 19, 2020 with SQL/JSON (and SQL/XML) support. Half a year later, we've released jOOQ 3.15 with MULTISET support, which builds on top of these features to offer type-safe nested collections, the way every ORDBMS should implement them. Building (dogfooding) on top of … Continue reading Standard SQL/JSON – The Sobering Parts

jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL

This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn't adopt it. And those who did, didn't agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL

Nesting Collections With jOOQ 3.14’s SQL/XML or SQL/JSON support

One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set. The following has always been possible in jOOQ, assuming PostgreSQL's INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module): class … Continue reading Nesting Collections With jOOQ 3.14’s SQL/XML or SQL/JSON support

Using SQL Server FOR XML and FOR JSON Syntax on Other RDBMS With jOOQ

SQL Server supports transforming flat tabular SQL result sets into hierarchical structures by convention using the convenient FOR XML or FOR JSON syntaxes. This is really convenient and less verbose than the standard SQL/XML or SQL/JSON APIs - although the standard ones are more powerful. In this blog post, I'd like to show a few … Continue reading Using SQL Server FOR XML and FOR JSON Syntax on Other RDBMS With jOOQ

Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead

It's been a while since I've ranted on this blog, but I was recently challenged by a reddit thread to write about this topic, so here goes... So, you're writing a service that produces some JSON from your database model. What do you need? Let's see: Read a book on DDD Read another book on … Continue reading Stop Mapping Stuff in Your Middleware. Use SQL’s XML or JSON Operators Instead

How to Aggregate an Archive Log’s Deltas into a Snapshot with SQL

A customer of my popular SQL training (which you should book!) has recently challenged me to optimise a hierarchical query that merges an archive log's deltas in order to obtain a snapshot of some record at a given point in time. In this article, I will reproduce their problem statement in a simplified version and … Continue reading How to Aggregate an Archive Log’s Deltas into a Snapshot with SQL

jOOQ Tuesdays: Gerald Sangudi and Keshav Murthy Reveal the Secrets of N1QL (SQL on JSON)

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. I'm very excited to … Continue reading jOOQ Tuesdays: Gerald Sangudi and Keshav Murthy Reveal the Secrets of N1QL (SQL on JSON)

When to Choose SQL and When to Choose NoSQL

Some people make architecture decisions purely based on the loudest consultant: https://twitter.com/gareth/status/778575385600688129 For most others, however, decisions are not as simple as this. For instance: When should we start evaluating NoSQL storage systems as an alternative to RDBMS? RDBMS as the hammer for all nails This question obviously assumes that you're starting out with an … Continue reading When to Choose SQL and When to Choose NoSQL