Introduction

Molecule is a Scala 3 library for querying and mutating SQL databases using a type-safe DSL — not one for SQL, but one generated from your own domain structure.

Most libraries offer a DSL to express their concepts — like SQL or a SQL-DSL. Molecule inverts that: it builds a DSL directly from your domain structure, letting you model and query data in your own domain terms.

After defining your domain structure, Molecule generates boilerplate code for your custom DSL. You can then declare what data you want using this DSL, and Molecule handles how to retrieve or modify it.

Compose your Data

Compose a "molecule" data model with the words of your domain in plain Scala instead of writing brittle query text strings or navigating intermediary DSLs:

Molecule

Person.name.age.Address.street

SQL

SELECT
  Person.name,
  Person.age,
  Address.street
FROM Person
  INNER JOIN Address
    ON Person.address = Address.id
WHERE
  Person.name IS NOT NULL AND
  Person.age  IS NOT NULL;

ScalaSql

Person.select.join(Address)(_.id === _.personId)
  .map { case (p, a) => (p.name, p.age, a.street) }

Slick

(people join addresses on (_.id === _.addressId))
  .map { case (p, a) => (p.name, p.age, a.street) }

Molecule translates your molecules to queries for the chosen database and you get back matching typed data with one of four APIs:

Sync

import molecule.db.postgres.sync.*
       
val persons: List[(String, Int, String)] =
  Person.name.age.Address.street.query.get

Async

import molecule.db.postgres.async.*

val persons: Future[List[(String, Int, String)]] =
  Person.name.age.Address.street.query.get

ZIO

import molecule.db.postgres.zio.*

val persons: ZIO[Conn, MoleculeError, List[(String, Int, String)]] =
  Person.name.age.Address.street.query.get

IO

import molecule.db.postgres.io.*

val persons: cats.effect.IO[List[(String, Int, String)]] =
  Person.name.age.Address.street.query.get

Likewise, Data can be transacted with molecules:

Save

Person.name("Ben").age(22).Address.street("Main st.").save.transact

Insert

Person.name.age.Address.street.insert(List(
  ("Lisa", 20, "Broadway"),
  ("John", 24, "5th Avenue")
)).transact

Update

Person(lisaId).age(21).update.transact

Delete

Person(benId).delete.transact

Domain Structure

The Domain Structure for the example above is written in vanilla Scala:

import molecule.DomainStructure

object MyDomainStructure extends DomainStructure {
  trait Person {
    val name    = oneString
    val age     = oneInt
    val address = manyToOne[Address]
  }
  trait Address {
    val street = oneString
  }
}

Running sbt moleculeGen then generates the necessary DSL code to let you write molecules.

Limitation

Molecule is not a facade to database APIs or JDBC.

Molecule only focuses on generating queries and transactions from "molecule" data models in your code and execute them against a database, as easily and intuitively as possible.

Other more administrative operations on databases like creating indexes, manual rollbacks of transactions etc. are outside the scope of Molecule. It might be helpful to use other SQL library APIs to handle those operations if not more low-level abstractions like JDBC.