Database first is at the core of jOOQ's design. jOOQ has been made primarily for classic systems the database is always there and always has been and will never leave. This is because we think "data have mass" https://twitter.com/ChrisRSaxon/status/1093122970254536704 This not only translates to moving logic closer to the data (see our previous posts about … Continue reading Using Testcontainers to Generate jOOQ Code
Tag: PostgreSQL
Vendor Agnostic, Dynamic Procedural Logic with jOOQ
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic, Dynamic Procedural Logic with jOOQ
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
Simulating Latency with SQL / JDBC
I've run across a fun little trick to simulate latency in your development environments when testing some SQL queries. Possible use-cases including to validate that backend latency won't bring down your frontend, or that your UX is still bearable, etc. https://twitter.com/gunnarmorling/status/1361061234569805832 The solution is PostgreSQL and Hibernate specific, though to doesn't have to be. Besides, … Continue reading Simulating Latency with SQL / JDBC
Implementing a generic REDUCE aggregate function with SQL
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: Stream.of(2, 4, 3, 1, 6, 5) .reduce((i, j) -> i * j) .ifPresent(System.out::println); // Prints 720 SQL doesn't have this, yet it would be very useful to be … Continue reading Implementing a generic REDUCE aggregate function with SQL
Having “constant” columns in foreign keys
I was asked a very interesting question on Twitter just now: https://twitter.com/connolly_s/status/1303957373107818497 Can we have "constant" foreign key columns in (PostgreSQL) tables? Luckily, yes, we can. Using a nice standard feature that is "computed columns" or "generated columns" Sometimes, you cannot completely normalise your schema for whatever reason. There may be a case where you … Continue reading Having “constant” columns in foreign keys
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
What’s Faster? COUNT(*) or COUNT(1)?
One of the biggest and undead myths in SQL is that COUNT(*) is faster than COUNT(1). Or was it that COUNT(1) is faster than COUNT(*)? Impossible to remember, because there's really no reason at all why one should be faster than the other. But is the myth justified? Let's measure! How does COUNT(...) work? But … Continue reading What’s Faster? COUNT(*) or COUNT(1)?
Using DISTINCT ON in Non-PostgreSQL Databases
A nice little gem in PostgreSQL's SQL syntax is the DISTINCT ON clause, which is as powerful as it is esoteric. In a previous post, we've blogged about some caveats to think of when DISTINCT and ORDER BY are used together. The bigger picture can be seen in our article about the logical order of … Continue reading Using DISTINCT ON in Non-PostgreSQL Databases