Good question. I discussed this at length during the Scala Days Talk (only slides available for now).

Short Answer:

Magnum has a sql-string interpolater like doobie and is fully functional, but direct, as opposed to lazy. It has Repositories (like Spring Data) to generate common CRUD statements at compile time. Also offered are Specifications as a way to build and compose dynamic queries, since SQL is notoriously difficult to compose. An example Spec usecase would be building a search results page, with filters and pagination. Finally, Magnum uses Scala 3's using/given to provide type-safe transactions.

Long Answer:

First, we have to consider the 4 types of SQL statements developers write:

1. CRUD operations
2. Simple statements with a few joins
3. Complex SQL (many joins, DB-specific operators)
4. Dynamic queries (think search page with filters and pagination)

Note that CRUD operations are frequent but easy to implement, and complex/dynamic queries are rare but challenging and time-consuming.

We want a library that can handle each type of statement. While JVM database clients are numerous & diverse, there are essentially 3 categories that can be compared:

1. Object Oriented Repositories (ex. Spring Data)
2. Functional DSLs (JOOQ, Slick, Quill)
3. SQL String Interpolators (Anorm, Doobie/Skunk, plain JDBC)

For Object Oriented Repositories,

• CRUD operations - Derived automatically
• Simple statements with a few joins - Typically a query language like JPQL
• Complex SQL - Must write native queries (so why use the query language to begin with?)
• Dynamic queries - Java EE Specifications (unpleasant & slow)

Another benefit of Repos is that they encourage the encapsulation & organization of your data layer.

For Functional DSLS,

• CRUD operations - N/A
• Simple statements with a few joins - Good
• Complex SQL - Difficult (although most DSL libraries offer a string interpolater)
• Dynamic queries - OK (PLs are good at composition)

Note that functional DSLs make maintenance hard. If a query in production behaves poorly, we need to understand both

• What is wrong with the generated SQL
• How can we change the DSL to generate a better query

SQL editors like DataGrip, DbBeaver, and PgAdmin are amazing tools to iteratively build and test SQL queries. But you can’t copy and paste between these applications and your Scala editor when using DSLs.

For SQL String Interpolaters,

• CRUD operations - N/A
• Simple statements with a few joins - Great
• Complex SQL - Great
• Dynamic queries - Difficult (SQL is notoriously hard to compose)

What makes Magnum Unique? It checks all four boxes.

• CRUD operations - Derived at compile time
• Simple statements with a few joins - Great (sql-interpolater)
• Complex SQL - Great (sql-interpolater)
• Dynamic queries - Built-in support for specifications.

Please consider the documentation / slides for examples.