Choreo ERD: Comprehensive Walkthrough

Mix.install([
  {:choreo, "~> 0.8"},
  {:kino_vizjs, "~> 0.8.0"}
])

Introduction

Choreo.ERD is a database schema modeler that supports Entity-Relationship Diagrams (ERDs). It allows database engineers and system architects to dynamically build out database tables, model exact relationships (with standard cardinalities), automatically validate constraints, and run advanced topological analysis on schemas.

Everything renders flawlessly to DOT (Graphviz) for styled HTML record tables or Mermaid.js native erDiagram representation.

alias Choreo.ERD
alias Choreo.ERD.Analysis

# Initialize a standard e-commerce database schema
schema =
  ERD.new()
  |> ERD.add_table(:users, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :email, type: :varchar, comment: "unique index"}
  ])
  |> ERD.add_table(:orders, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :user_id, type: :integer, key: :fk},
    %{name: :total_amount, type: :numeric}
  ])
  |> ERD.add_table(:order_items, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :order_id, type: :integer, key: :fk},
    %{name: :product_id, type: :integer, key: :fk},
    %{name: :quantity, type: :integer}
  ])
  |> ERD.add_table(:products, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :sku, type: :varchar, comment: "stock keeping unit"},
    %{name: :price, type: :numeric}
  ])
  # Draw foreign key relationships with multiplicities
  |> ERD.add_relationship(:users, :orders, cardinality: :one_to_many, label: "places")
  |> ERD.add_relationship(:orders, :order_items, cardinality: :exactly_one_to_many, label: "contains")
  |> ERD.add_relationship(:products, :order_items, cardinality: :zero_or_one_to_many, label: "ordered_in")

Styled Graphviz Rendering

ERD.to_dot(schema)
|> Kino.VizJS.render()

Native Mermaid.js erDiagram Rendering

ERD.to_mermaid(schema)
|> Kino.Mermaid.new()

Schema Construction & Strict Validation

Choreo.ERD enforces strict compile-time/runtime validation using NimbleOptions to guarantee the model’s structural integrity.

Column Validation

Every column in add_table/3 is validated against a rigorous schema, supporting parameters:

• :name — (Required) Atom or String column name.
• :type — (Required) Data type representation (e.g. :integer, :varchar, :numeric).
• :key — (Optional) Key constraint, either :pk (Primary Key), :fk (Foreign Key), or nil.
• :comment — (Optional) Explanatory comment displayed in tooltips/captions.

# This will fail because column fields are missing/invalid
try do
  ERD.new()
  |> ERD.add_table(:broken_table, columns: [
    %{name: :id} # Missing :type!
  ])
rescue
  e -> e
end

Relationship Multiplicities

Relationships support standard crow’s foot multiplicities via the :cardinality option:

| :one_to_one | teetee / teetee | ||--|| | | :one_to_many | teetee / crowodot | ||--o{ | | :zero_or_one_to_many | odot / crowodot | |o--o{ | | :exactly_one_to_many | teetee / crowtee | ||--|{ | | :many_to_many | crowodot / crowodot | }|--|{ |

# Example of a one-to-one profile link
profile_schema =
  ERD.new()
  |> ERD.add_table(:users, columns: [%{name: :id, type: :integer, key: :pk}])
  |> ERD.add_table(:profiles, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :user_id, type: :integer, key: :fk}
  ])
  |> ERD.add_relationship(:users, :profiles, cardinality: :one_to_one, label: "has_one")

ERD.to_mermaid(profile_schema)
|> Kino.Mermaid.new()

Strict Column & Datatype Verification

To ensure that your relationships point to valid columns and don’t introduce mismatching datatypes, Choreo.ERD supports column-level mapping checks via :from_column and :to_column options.

Additionally, you can toggle a global :strict_column_matching flag when initializing your diagram:

# This will fail because strict_column_matching is active but we did not provide column mappings
try do
  ERD.new(strict_column_matching: true)
  |> ERD.add_table(:users, columns: [%{name: :id, type: :integer, key: :pk}])
  |> ERD.add_table(:posts, columns: [%{name: :user_id, type: :integer, key: :fk}])
  |> ERD.add_relationship(:users, :posts, cardinality: :one_to_many)
rescue
  e -> e
end

# This will fail because the datatypes of the joined columns mismatch (integer vs varchar)
try do
  ERD.new()
  |> ERD.add_table(:users, columns: [%{name: :id, type: :integer, key: :pk}])
  |> ERD.add_table(:posts, columns: [%{name: :user_uuid, type: :varchar, key: :fk}])
  |> ERD.add_relationship(:users, :posts,
    cardinality: :one_to_many,
    from_column: :id,
    to_column: :user_uuid
  )
rescue
  e -> e
end

# This succeeds perfectly because columns exist and datatypes match
strict_schema =
  ERD.new(strict_column_matching: true)
  |> ERD.add_table(:users, columns: [%{name: :id, type: :integer, key: :pk}])
  |> ERD.add_table(:posts, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :user_id, type: :integer, key: :fk}
  ])
  |> ERD.add_relationship(:users, :posts,
    cardinality: :one_to_many,
    from_column: :id,
    to_column: :user_id
  )

Themed Visualizations

All built-in themes are optimized specifically for HTML-like database record labels, adjusting header fills, fonts, card borders, and relationship paths dynamically:

# Let's inspect the Ocean theme on our standard e-commerce schema
ERD.to_dot(schema, theme: :ocean)
|> Kino.VizJS.render()

# Let's inspect the Forest theme
ERD.to_dot(schema, theme: :forest)
|> Kino.VizJS.render()

# Let's inspect the Dark presentation theme
ERD.to_dot(schema, theme: :dark)
|> Kino.VizJS.render()

Topological Analysis Suite

The Choreo.ERD.Analysis suite runs graph-theoretic queries across the database schema, identifying layout hotspots, joining issues, and structural coupling metrics.

1. Optimal Join Paths

To join two distant tables, the shortest_join_path/3 BFS treats the database relationships as an undirected graph, computing the exact sequence of intermediate joins:

# Find the exact join sequence between users and products
Analysis.shortest_join_path(schema, :users, :products)

2. Circular Foreign Key Cycles

Circular relationships complicate database migrations, row insertions, and cascade teardowns. Analysis.cycles/1 detects all circular foreign key references:

# Introducing a circular relation loop: User -> Order -> Item -> User
circular_schema =
  schema
  |> ERD.add_relationship(:order_items, :users, cardinality: :one_to_many, label: "assigned_to")

# Detect the cycle loop
Analysis.cycles(circular_schema)

3. Orphan Tables

Orphan tables are structurally isolated from the database relationships, highlighting neglected assets or incomplete models:

# Add an isolated logging audit table
isolated_schema =
  schema
  |> ERD.add_table(:audit_logs, columns: [
    %{name: :id, type: :integer, key: :pk},
    %{name: :event, type: :varchar}
  ])

# Identify orphans
Analysis.orphans(isolated_schema)

4. Structural Table Degrees (Coupling Metrics)

Determines table centrality. Tables with high outgoing/incoming degrees represent vital database hubs, whereas low-degree tables are leaf attributes:

# Calculate degrees for each table
Analysis.table_degrees(schema)

Cheat Sheet Cheat Sheet