Polymorphic associations with many to many

Sooner or later one record needs to belong to records of more than one kind. The classic example is a comment-like child — call it a todo item — that can appear under either a todo list or a todo card. In Elixir’s Ecto guide this is the textbook case for polymorphism, and the textbook trap. This guide ports that guide to lode: it shows the design Ecto warns against, the design Ecto recommends, and why lode’s explicit association values make the recommended design especially legible.

The design to avoid

The tempting move is one table for the children and one join table that points at “whatever the parent happens to be”:

todo_items
  id, text

todo_items_assoc            -- the polymorphic join table to avoid
  todo_item_id
  assoc_id                  -- the parent's id
  assoc_type                -- "TodoList" | "TodoCard" (a string discriminator)

assoc_id cannot be a foreign key. A foreign key references exactly one table, but assoc_id points sometimes at todo_lists and sometimes at todo_cards, chosen at runtime by the assoc_type string. So the database can no longer guarantee that a parent exists: delete a todo list and its items become silent orphans, their assoc_id dangling. You have traded referential integrity for the convenience of a single column, and every query now has to remember to filter on assoc_type by hand.

In Ecto this anti-pattern is usually reached through a deceptively pleasant Ecto.Schema macro DSL with an assoc_type discriminator. lode has no such DSL — associations are ordinary values, and there is no built-in “polymorphic” association variant at all (see DESIGN.md in the repository for the no-macros, no-reflection stance). That absence is a feature here: the language never tempts you toward the broken design in the first place.

The design to prefer: one child table, one join table per parent

Ecto’s recommendation is to keep the shared child table but give each parent type its own join table, each with a real foreign key to the shared child and a real foreign key to that specific parent:

todo_items            -- the shared child table
  id, text

todo_list_items       -- join table: todo_lists  <-> todo_items
  todo_list_id  -> todo_lists.id
  todo_item_id  -> todo_items.id

todo_card_items       -- join table: todo_cards  <-> todo_items
  todo_card_id  -> todo_cards.id
  todo_item_id  -> todo_items.id

Now every column is a genuine foreign key, integrity is the database’s job again, and a todo item can still be reached from either kind of parent. The only cost is one extra table per parent type — cheap, and tables are not a scarce resource.

The migration

The shared child table, the two parent tables, and the two join tables, with a ddl.references foreign key on each join column:

import lode/migration
import lode/migration/ddl

pub fn add_todo_items() -> migration.Migration {
  migration.new(20_260_613_120_000)
  |> migration.create_table("todo_lists", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("title", ddl.Text) |> ddl.not_null,
  ])
  |> migration.create_table("todo_cards", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("title", ddl.Text) |> ddl.not_null,
  ])
  |> migration.create_table("todo_items", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("text", ddl.Text) |> ddl.not_null,
  ])
  |> migration.create_table("todo_list_items", [
    ddl.column("todo_list_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("todo_lists")),
    ddl.column("todo_item_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("todo_items")),
  ])
  |> migration.create_index(
    "todo_list_items",
    ["todo_list_id", "todo_item_id"],
    unique: True,
  )
  |> migration.create_table("todo_card_items", [
    ddl.column("todo_card_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("todo_cards")),
    ddl.column("todo_item_id", ddl.Integer)
      |> ddl.not_null
      |> ddl.references(ddl.reference("todo_items")),
  ])
  |> migration.create_index(
    "todo_card_items",
    ["todo_card_id", "todo_item_id"],
    unique: True,
  )
}

Note — what makes this design work. The ddl.references foreign keys on each join column are what make this design worth choosing over the polymorphic anti-pattern, and the unique index on each join table’s column pair stops a parent from linking the same item twice.

The schemas

The shared child is an ordinary schema with no association back to its parents — a todo item does not care which kind of parent linked it, and not pointing back keeps the child genuinely shareable:

import lode/association
import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/result

pub type TodoItem {
  TodoItem(id: Int, text: String)
}

pub fn todo_item_schema() -> Schema(TodoItem) {
  schema.new(
    source: "todo_items",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "text", of: primitive.string()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use text <- result.try(schema.require_string(row, "text"))
      Ok(TodoItem(id:, text:))
    },
    dump: fn(t: TodoItem) {
      dict.from_list([#("id", VInt(t.id)), #("text", VString(t.text))])
    },
  )
}

Each parent registers its own association.many_to_many to the shared child, and the only thing that differs between them is the join_through table and the join_owner_key. This is the crux of the guide: in Ecto the join table hides behind many_to_many :todo_items, ... join_through: "todo_list_items", easy to skim past; in lode the join table is a plain string argument you must name on every association, so “two distinct associations sharing one child table, through two distinct join tables” is written out in full, no macro magic concealing it (the no-macros re-expression — see DESIGN.md in the repository):

pub type TodoList {
  TodoList(id: Int, title: String, todo_items: List(TodoItem))
}

pub fn todo_list_schema() -> Schema(TodoList) {
  schema.new(
    source: "todo_lists",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "title", of: primitive.string()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use title <- result.try(schema.require_string(row, "title"))
      Ok(TodoList(id:, title:, todo_items: []))
    },
    dump: fn(l: TodoList) {
      dict.from_list([#("id", VInt(l.id)), #("title", VString(l.title))])
    },
  )
  |> association.many_to_many(
    name: "todo_items",
    related: todo_item_schema,
    join_through: "todo_list_items",
    join_owner_key: "todo_list_id",
    join_related_key: "todo_item_id",
    owner_key: fn(l: TodoList) { VInt(l.id) },
    child_key: fn(t: TodoItem) { VInt(t.id) },
    put: fn(l, items) { TodoList(..l, todo_items: items) },
    opts: association.options()
      |> association.on_replace(association.ReplaceDelete),
  )
}

The second parent is the same shape, differing only where it must — its source, its own join table, and its own owner key:

pub type TodoCard {
  TodoCard(id: Int, title: String, todo_items: List(TodoItem))
}

pub fn todo_card_schema() -> Schema(TodoCard) {
  schema.new(
    source: "todo_cards",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "title", of: primitive.string()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use title <- result.try(schema.require_string(row, "title"))
      Ok(TodoCard(id:, title:, todo_items: []))
    },
    dump: fn(c: TodoCard) {
      dict.from_list([#("id", VInt(c.id)), #("title", VString(c.title))])
    },
  )
  |> association.many_to_many(
    name: "todo_items",
    related: todo_item_schema,
    join_through: "todo_card_items",
    join_owner_key: "todo_card_id",
    join_related_key: "todo_item_id",
    owner_key: fn(c: TodoCard) { VInt(c.id) },
    child_key: fn(t: TodoItem) { VInt(t.id) },
    put: fn(c, items) { TodoCard(..c, todo_items: items) },
    opts: association.options()
      |> association.on_replace(association.ReplaceDelete),
  )
}

Two associations, named identically (todo_items) on two different parents, each carrying its own join_through. There is no abstraction shared between them, and that is deliberate: the only thing they have in common is the child table, and the child schema already expresses that. If you find yourself wishing for a single helper that declares “this parent links to todo items” once and reuses it across both parents, you can of course write one in Gleam — a function returning a partially-applied many_to_many — but the library does not (and should not) ship a built-in “polymorphic association” to paper over the two join tables. The whole point of the recommended design is that the two links are genuinely separate.

Writing through put_assoc

Linking todo items to a parent is an ordinary many-to-many write. Because the items already exist (and carry their primary keys), we hand put_assoc changesets built from the loaded rows; the association write reconciles only the join-table rows, honoring on_replace(ReplaceDelete), and never tries to re-insert the shared items:

import lode/changeset
import lode/error.{type LodeError}
import lode/repo.{type Repo}
import gleam/list

pub fn set_list_items(
  r: Repo,
  list_id list_id: Int,
  items items: List(TodoItem),
) -> Result(TodoList, LodeError) {
  let parent = TodoList(id: list_id, title: "", todo_items: [])
  let cs =
    changeset.change(parent, todo_list_schema())
    |> changeset.put_assoc(
      "todo_items",
      list.map(items, fn(item) { changeset.change(item, todo_item_schema()) }),
    )
  repo.update(r, todo_list_schema(), cs)
}

The same item can be linked to a card by going through that parent’s own association — note this is a different join table, written through a different schema, even though the function body looks identical:

pub fn set_card_items(
  r: Repo,
  card_id card_id: Int,
  items items: List(TodoItem),
) -> Result(TodoCard, LodeError) {
  let parent = TodoCard(id: card_id, title: "", todo_items: [])
  let cs =
    changeset.change(parent, todo_card_schema())
    |> changeset.put_assoc(
      "todo_items",
      list.map(items, fn(item) { changeset.change(item, todo_item_schema()) }),
    )
  repo.update(r, todo_card_schema(), cs)
}

The two writes land in todo_list_items and todo_card_items respectively. A todo item linked to both a list and a card has one row in each join table and a single row in todo_items — exactly the sharing the design set out to allow. (For the get-or-insert dance when the children are new rather than already persisted, see the tag example in Constraints and Upserts; the polymorphic angle adds nothing to it.)

Reading through preload

Each parent loads its items by the name it registered. The preload follows that parent’s own join table, so a list sees only its todo_list_items links and a card sees only its todo_card_items links — there is no assoc_type to filter on, because the join tables are already separate:

import lode/preload
import lode/query

pub fn list_with_items(
  r: Repo,
  list_id list_id: Int,
) -> Result(List(TodoList), LodeError) {
  let q =
    query.from(source: "todo_lists", alias: "l")
    |> query.preload([preload.one("todo_items")])
  repo.all(r, todo_list_schema(), q)
}

Or, when the parents are already in hand, with repo.preload:

let assert Ok(cards) =
  repo.all(r, todo_card_schema(), query.from("todo_cards", "c"))
let assert Ok(cards) =
  repo.preload(
    repo: r,
    schema: todo_card_schema(),
    parents: cards,
    preloads: [preload.one("todo_items")],
  )

Why this reads well in lode

In Ecto the recommended design and the anti-pattern look almost the same in the schema file — both are a many_to_many (or a has_many ... through) macro, and the difference lives in a join_through: option and the migration. The reader has to know to look. In lode the join table is never optional and never inferred: join_through: "todo_list_items" and join_through: "todo_card_items" sit right there in the two declarations, so the “separate join table per parent” structure is the literal text of the program. The library gives you exactly one tool, association.many_to_many, and the good design falls out of using it twice.

For the full many-to-many reference (join schemas, ordering, on_delete), see Associations; for the get-or-insert and constraint patterns the writing side leans on, see Constraints and Upserts; and for the complete list of differences from Elixir’s Ecto, see Divergences from Ecto.