Skip to main content

Plain SQL

Plain SQL

Plain SQL libraries allow you to write SQL directly, typically through string interpolators or lightweight wrappers, with minimal abstraction.

  • Doobie - Functional JDBC wrapper with typeclasses and F[_] support
  • Magnum - Minimalist raw-SQL library with repository helpers
  • Skunk - Pure functional Postgres library using cats-effect
  • Anorm - Lightweight SQL wrapper integrated with Play Framework

Comparison

We'll compare Molecule with the plain-SQL libraries in the following areas:

  1. Environment - Scala versions, JS/Native, APIs, runtime/compiletime etc
  2. Supported databases - tested/optional/not supported databases
  3. Data types - attribute types and defaults
  4. Setup - schema, model mappings
  5. Query - syntax, features, result types
  6. Transaction - mutation syntax and tx handling
  7. Integration with other libraries

1. Environment

MoleculeDoobieMagnumSkunkAnorm
Scala 3✅✅✅✅✅
Scala 2.13-✅-✅✅
Scala 2.12-✅-✅✅
Scala JS✅----
Scala Native-----
Modelruntimeruntimeruntimeruntimecompile
APISync
Async
ZIO
IO		F[_]SyncF[_]Sync
Code generation✅---✅
(macro)
Repository pattern support-✅--
Db compliance SPI✅ 1)----
  1. Molecule guarantees that all supported databases behave identically for the end-user. Each supported database pass the same comprehensive compliance SPI test suite of ~2000 tests.

2. Supported databases

MoleculeDoobieMagnumSkunkAnorm
Postgres✅✅✅✅✅
MySQL✅✅✅❌✅
H2✅✅✅❌✅
SQLite✅✅✔❌✅
MariaDB✅✔✔❌✔
Oracle-✅✔❌✅
SQL Server-✅✔❌✅
DB2-✅✔❌✔
HSQLDB-✅✅❌✔
Derby-✅✔❌✔
OrientDB-✔✔❌✅
Cassandra-✔✔❌✅
Spark-✔✔❌✅
Clickhouse-✔✅❌✔

✅ = Fully supported and tested
✔ = JDBC-compatible; should work
- = Can be implemented
❌ = Not supported

3. Data types

MoleculeDoobieMagnumSkunkAnorm
Scalars
String✅
Int✅
Long✅
Float✅
Double✅
Boolean✅
BigInt✅
BigDecimal✅
Byte✅
Short✅
Char✅
Enum✅
Java types
java.util.Date✅
java.util.UUID✅
java.net.URI✅
java.time.Duration✅
java.time.Instant✅
java.time.LocalDate✅
java.time.LocalTime✅
java.time.LocalDateTime✅
java.time.OffsetTime✅
java.time.OffsetDateTime✅
java.time.ZonedDateTime✅
Collections
Set✅
Seq✅
Map✅
Collections
Geo-spatial?
Composite?
More?

4. Setup

  • "What is needed to set up to start using each library?"
  • Setups for all libs should have the same tables/attributes so that we can compare
Doobie
// Doobie code..

More comparisons...?

MoleculeDoobieMagnumSkunkAnorm
SQL schema generated✅
Custom attribute types
Views
Migration handling (a la Flyway)

5. Query

MoleculeDoobieMagnumSkunkAnorm
Type-safe query✅
Type-safe filtering✅
Type-safe result✅
Nested result✅
Sorting✅
Offset pagination✅✅✅✅✅
Cursor pagination✅
Attribute filter✅
Attribute calculations
Having✅
union, unionAll
except, intersect
window functions
window functions
Tuples returned✅
Mapping to case class✅
Variable binding✅
Subscription✅
StreamingZStream
fs2		fs2
Subqueries✅

Example query

"Departments with more than 2 employees assigned to projects with a budget exceeding 1M"

Molecule:

Department.name.Employees.id_(countDistinct).>(2).d1.Projects.budget_.>(1000000)

SQL:

SELECT
  Department.name,
  COUNT(DISTINCT Employee.id) Employee_id_count
FROM Department
  INNER JOIN Employee   ON Department.id = Employee.department
  INNER JOIN Assignment ON Employee.id = Assignment.employee
  INNER JOIN Project    ON Assignment.project = Project.id
WHERE
  Department.name IS NOT NULL AND
  Project.budget  > 1000000
GROUP BY Department.name
HAVING COUNT(DISTINCT Employee.id) > 2
ORDER BY Employee_id_count DESC;

Of course, Table prefixes could often be omitted but for more complex queries they will often be needed anyway to distinguish fields.

6. Transaction

MoleculeDoobieMagnumSkunkAnorm
Type-safe transaction✅
Save points✅
Unit of work✅
Rollback✅
Join mutations✅
Nested mutations✅

Comparing Molecule save, update and delete with mutations in plain SQL libraries:

(same idea as for query...)

Save
// Molecule
Person.name("Ben").age(22).Address.street("Main st.").save

// SQL - 2 operations
INSERT INTO Address(
  street
) VALUES ("Main st.")

INSERT INTO Person(
  name,
  age,
  home
) VALUES("Ben", 22, [addressId])

7. Integration

bla bla

MoleculeDoobieMagnumSkunkAnorm
ZIO✅✅✅
Cats✅✅
Play✅
Akka
Pekko

Okay, let me be a bit more specific. I want to examine how to name many-to-many join tables that have no additional attributes. I'm considering to define such a domain structure like I do already in Molecule. We'll examine the more complex case of many-to-many relations with additional attributes later. For now, let's focus on the simple case and how to

Okay, let me be a bit more specific. Say that I define two many-to-many relationships:

object WebCompany extends DomainStructure {

  trait Employee {
    val name      = oneString
    val frontends = manyToOne[Project].designers
    val backends  = manyToOne[Project]
  }

  trait Project {
    val name      = oneString
    val budget    = oneInt
    val designers = manyToOne[Employee]
    val engineers = manyToOne[Employee].backends
  }


  trait Person {
    val friends = manyToOne[Person]
  }
}

Notice how I tie the Employee.fontends ref to the reverse designers ref in the related Project trait to denote that these should share the same join table.
And likewise for the Project.engineers ref to Employee.backends ref - now just making the link from the Project side.

What would be meaningfull ways to distinctively name the two join tables?

As I do it now, I name them like this:

Employee_frontends_Project // using ref attribute name "frontends" from Employee trait Project_engineers_Employee // using ref attribute name "engineers" from Project trait

This takes the distinguishing ref attrubute name from the "defining" side of the relationship.

Last Updated