Skip to content

v0.26.0 Bidirectional relationships

Choose a tag to compare

@marcgrue marcgrue released this 29 Sep 22:17
· 36 commits to main since this release

Molecule now supports bidirectional relationships. Query any relationship in both directions.

All relationship handling has been re-written from scratch to support bidirectional relationships and to be more consistent with SQL. Legacy thinking from Datomic has been removed and SQL being fully embraced.

Read all about relationship definitions and queries on the Scala Molecule website or read on here:

Relationships

As in an SQL schema definition we define a relationship with a foreign key attribute that points to the related entity.

For instance we define InvoiceLines to have a foreign key attribute invoice that points to the Invoice they belong to:

trait InvoiceLine:
  val invoice = manyToOne[Invoice] // foreign key to one Invoice

As with SQL, in Molecule, we always define a relationship on the entity where the foreign key is, the "many" side. To be explicit about this, we use the manyToOne method to define the relationship. Many invoice lines are related to one invoice.

Foreign key constraint

For each foreign key defined, Molecule adds a foreign key constraint in the generated SQL schema definition:

-- Foreign key constraints
ALTER TABLE InvoiceLine 
  ADD CONSTRAINT `_invoice` 
  FOREIGN KEY (invoice) 
  REFERENCES Invoice (id);

Foreign key index

Molecule also generates an index for the foreign key attribute in the generated SQL schema definition:

-- Foreign key indexes
CREATE INDEX IF NOT EXISTS _InvoiceLine_invoice ON InvoiceLine (invoice);

Foreign key attribute

The invoice foreign key attribute is accessible as any other attribute:

val invoiceId: Long = InvoiceLine.invoice.query.get.head

Many-to-one

Molecule uses the capitalized name of the foreign key attribute as a relationship accessor name to go from the many-side to the one-side.

In our invoice example we can therefore query the original many-to-one relationship from InvoiceLine to Invoice by using the capitalized Invoice many-to-one relationship accessor:

InvoiceLine.product.Invoice.no.query.get ==> List(
  ("Chocolate", 2),
  ("Coffee", 1),
  ("Milk", 1),
  ("Tea", 1),
  ("Tea", 2),
)

One-to-many

Molecule also generates a relationship accessor in the reverse direction from the one-side to the many-side by using the pluralized name of the entity where the relationship is defined. In our example, "InvoiceLine" where the relationship is defined is pluralized to "InvoiceLines".

So now we can query the one-to-many relationship from Invoice to InvoiceLine by using the InvoiceLines one-to-many relationship accessor:

Invoice.no.InvoiceLines.product.query.get ==> List(
  (1, "Coffee"),
  (1, "Milk"),
  (1, "Tea"),
  (2, "Chocolate"),
  (2, "Tea"),
)

Note that relationships - independent of bidirectional queries - are always defined from the "many" side to the "one" side.

Custom one-to-many name

We can also define the one-to-many relationship name explicitly by adding oneToMany("MyName") after the relationship definition:

trait InvoiceLine:
  val invoice = manyToOne[Invoice].oneToMany("Lines")

Then Molecule will call the reverse relationship accessor Lines instead. The SQL query will be the same as above since Lines still points to the same table InvoiceLine:

Invoice.no.Lines.product.query.get ==> List(
  (1, "Coffee"),
  (1, "Milk"),
  (1, "Tea"),
  (2, "Chocolate"),
  (2, "Tea"),
)

Cascade delete

Add the owner option to a relationship definition to delete all related entities when the "owner" entity is deleted.

In our invoice example, we can define the invoice relationship with owner to let the invoice own its invoice lines:

val invoice = manyToOne[Invoice].owner // Invoice owns its invoice lines

// or with custom reverse name
val invoice = manyToOne[Invoice].oneToMany("Lines").owner

Adding the owner option makes Molecule add "ON DELETE CASCADE" to the foreign key constraint in the generated SQL schema definition:

-- Foreign key constraints
ALTER TABLE InvoiceLine 
  ADD CONSTRAINT `_invoice` 
  FOREIGN KEY (invoice) 
  REFERENCES Invoice (id) 
  ON DELETE CASCADE;

Beware! Owned relationships delete all related entities when the "owner" entity is deleted. So in the example above, if an invoice is deleted, all its invoice lines will be deleted!

Nested accessor

One-to-many relationships can even be queried as nested data, now using the Lines.* accessor:

Invoice.no.Lines.*(InvoiceLine.product).query.i.get ==> List(
  (1, List("Coffee", "Milk", "Tea")),
  (2, List("Chocolate", "Tea")),
)

The only difference between the "flat" and "nested" SQL queries is that Molecule for the nested query also returns the id of the parent entity (Invoice.id) in order to nest the data correctly.

Many-to-many

As in SQL, a many-to-many relationship is defined in Molecule with a join table/entity that defines two or more many-to-one relationships to the entities that need to be joined.

To be treated as a many-to-many relationship by Molecule, the joining entity (Assignment) must extend the Join trait as shown below:

object Company extends DomainStructure {
  trait Project {
    val name   = oneString
    val budget = oneInt
  }
  trait Employee {
    val name = oneString
  }
  trait Assignment extends Join { // extend Join to treat as many-to-many
    val project  = manyToOne[Project]
    val employee = manyToOne[Employee]
    val role     = oneString
  }
}

We can still query with a normal one-to-many nested relationship from Project to Assignment and then a flat many-to-one relationship from Assignment to Employee:

Project.name.Assignments.*(Assignment.Employee.name).query.get ==> List(
  ("BigProject", List("Alice", "Bob", "Carol", "Diana")),
  ("MediumProject", List("Alice", "Bob", "Eve", "Frank")),
  ("SmallProject", List("Grace")),
)

Bridged accessor

But by extending the Join trait, we can also query the many-to-many relationship directly from Project to Employee by using the bridging Employees.** many-to-many relationship accessor:

Project.name.Employees.**(Employee.name).query.get ==> List(
  ("BigProject", List("Alice", "Bob", "Carol", "Diana")),
  ("MediumProject", List("Alice", "Bob", "Eve", "Frank")),
  ("SmallProject", List("Grace")),
)

The Employees.** accessor is simply convenience syntax sugar for the previous two-step relationship build-up. So the generated SQL is exactly the same in both queries.

And vice-versa we can make a bridged query in the opposite direction too:

Employee.name.Projects.**(Project.name).query.get ==> List(
  ("Alice", List("BigProject", "MediumProject")),
  ("Bob", List("BigProject", "MediumProject")),
  ("Carol", List("BigProject")),
  ("Diana", List("BigProject")),
  ("Eve", List("MediumProject")),
  ("Frank", List("MediumProject")),
  ("Grace", List("SmallProject")),
)