Molecule vs Slick

Using examples from Coming from SQL to Slick:

Select all table values

// Slick

In molecule we would declare each attribute we are interested in also to infer the exact return type

// Molecule

Select certain columns => (p.age, ++ " (" ++[String] ++ ")")).result

With Molecule we would concatenate name and id with the returned result set: map { case (age, name, id) => (age, s"$name ($id)" }

filter / WHERE

people.filter(p => p.age >= 18 && === "C. Vogt").result

Molecule filter values by applying a required value to an attribute or supply a value to compare against (>=(18)):

Person.age.>=(18).name("C. Vogt").get

(Again we would define which attribute values we want to return)

sortBy / ORDER BY

people.sortBy(p => (p.age.asc,

Ordering is applied on the result set in the application code:


Aggregate functions like max are all applied as a keyword value to an attribute.


// or get a range of top values

We can aggregate values also with the counterpart min or get a random value with rand. Or perform aggregate calculations with count, countDistinct, sum, avg, median, variance and stddev which are all built functions in Datomic.

groupBy / GROUP BY

people.groupBy(p => p.addressId)
       .map{ case (addressId, group) => (addressId, }

Molecule automatically group by attributes not having an aggregate expression. In this case the query will group by address and calculate the average age for persons living there.


groupBy+filter / HAVING

people.groupBy(p => p.addressId)
       .map{ case (addressId, group) => (addressId, }
       .filter{ case (addressId, avgAge) => avgAge > 50 }
Person.address.age(avg).get.filter(_._2 > 50)

Implicit join

people.flatMap(p =>
  addresses.filter(a => p.addressId ===
           .map(a => (,

// or equivalent for-expression:
(for(p <- people;
     a <- addresses if p.addressId ===
 ) yield (,

Explicit join

(people join addresses on (_.addressId ===
  .map{ case (p, a) => (, }.result

left/right/outer join

(addresses joinLeft people on ( === _.addressId))
  .map{ case (a, p) => (, }.result
// Add `$` to attribute name to get optional values$


val address_ids = addresses.filter( === "New York City").map(
people.filter( in address_ids).result // <- run as one query"New York City").get

insert => (, p.age, p.addressId))
       .insert(("M Odersky",12345,1))"M Odersky").age(12345).address(1).save


people.filter( === "M Odersky")
       .map(p => (,p.age))
       .update(("M. Odersky",54321))
// Find entity id with meta Molecule attribute `e`
val oderskyId = Person.e.name_("M Odersky").get.head
Person(oderskyId).name("M. Odersky").age(54321).update


people.filter(p => === "M. Odersky")
Person.e.name_("M. Odersky").get.head.retract

case =>
    If(p.addressId === 1) Then "A"
    If(p.addressId === 2) Then "B"
Person.address(1 or 2).get map {
  case 1 => "A"
  case 2 => "B"


Compare Gremlin…