Choreo ERD: Comprehensive Walkthrough

# For Hex publication/readers:
Mix.install([
  {:choreo, "~> 0.14.1"},
  # {:choreo, path: Path.expand("../..", __DIR__), force: true},
  {:kino_vizjs, "~> 0.9.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.

Choreo provides two complementary syntax styles:

1. Programmatic Pipe API (Choreo.ERD) — A stable, pipe-first interface ideal for dynamic builders and integrations (e.g., extracting schemas from Ecto).
2. Lab DSL (Choreo.Lab.DSL.ERD) — A concise, Livebook-friendly syntax for sketching schemas and exploring data models.

The introductory example below uses the explicit pipe-first syntax. All subsequent examples throughout this guide demonstrate the Lab DSL.

alias Choreo.ERD
alias Choreo.ERD.Analysis
import Choreo.Lab.DSL.ERD

# Initialize a standard e-commerce database schema using pipe syntax
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")

Kino.Layout.tabs(
  Siren: Choreo.Lab.Siren.new(ERD.to_mermaid(schema)),
  Graphviz: Kino.VizJS.render(ERD.to_dot(schema)),
  Sketch: Choreo.Lab.Sketch.new(ERD.to_mermaid(schema))
)

Schema Construction & Strict Validation

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

Column Validation in the DSL

In the DSL, tables are declared with table("name") do ... end (or entity), and columns are declared with dedicated helper verbs:

• pk :name, :type / primary_key :name, :type — Primary Key.
• fk :name, :type / foreign_key :name, :type — Foreign Key.
• field :name, :type / column :name, :type — Regular column, accepting optional :key and :comment.

Each column is validated against a schema:

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

# This will fail at runtime because the column key option is invalid
try do
  erd do
    table("broken_table") do
      field :id, :integer, key: :invalid_key
    end
  end
rescue
  e -> e
end

Relationship Multiplicities

Relationships support standard crow's foot multiplicities via typed DSL constructors or the :cardinality option:

# Example of a one-to-one profile link using the DSL
profile_schema =
  erd do
    users =
      table("users") do
        pk :id, :integer
      end

    profiles =
      table("profiles") do
        pk :id, :integer
        fk :user_id, :integer
      end

    has_one users ~> profiles, "has_one"
  end

Kino.Layout.tabs(
  Siren: Choreo.Lab.Siren.new(ERD.to_mermaid(profile_schema)),
  Graphviz: Kino.VizJS.render(ERD.to_dot(profile_schema)),
  Sketch: Choreo.Lab.Sketch.new(ERD.to_mermaid(profile_schema))
)

Strict Column & Datatype Verification

To ensure that your relationships point to valid columns and do not introduce mismatching datatypes, Choreo.ERD supports column-level mapping checks via :from_column (or from:) and :to_column (or to:).

Column matching is opt-in: passing only :from_column or only :to_column skips the check entirely. Both must be provided to trigger validation.

You can also toggle the global strict_column_matching: true option in the DSL:

# This will fail because strict_column_matching is active but we did not provide column mappings
try do
  erd strict_column_matching: true do
    users =
      table("users") do
        pk :id, :integer
      end

    posts =
      table("posts") do
        pk :id, :integer
        fk :user_id, :integer
      end

    one_to_many users ~> posts
  end
rescue
  e -> e
end

# This will fail because the datatypes of the joined columns mismatch (integer vs varchar)
try do
  erd do
    users =
      table("users") do
        pk :id, :integer
      end

    posts =
      table("posts") do
        pk :id, :integer
        fk :user_uuid, :varchar
      end

    one_to_many users ~> posts, from: :id, to: :user_uuid
  end
rescue
  e -> e
end

# This succeeds perfectly because columns exist and datatypes match
strict_schema =
  erd strict_column_matching: true do
    users =
      table("users") do
        pk :id, :integer
      end

    posts =
      table("posts") do
        pk :id, :integer
        fk :user_id, :integer
      end

    one_to_many users ~> posts, from: :id, to: :user_id
  end

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:

tabs = [
  {"Default", Kino.VizJS.render(ERD.to_dot(schema))},
  {"Ocean", Kino.VizJS.render(ERD.to_dot(schema, theme: :ocean))},
  {"Forest", Kino.VizJS.render(ERD.to_dot(schema, theme: :forest))},
  {"Dark", Kino.VizJS.render(ERD.to_dot(schema, theme: :dark))}
]

Kino.Layout.tabs(tabs)

Note on Mermaid theming: The native Mermaid erDiagram syntax has limited styling support. ERD.to_mermaid/2 accepts a :theme option for API consistency but currently ignores it.

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 using the DSL: User -> Order -> Item -> User
circular_schema =
  erd do
    users =
      table("users") do
        pk :id, :integer
      end

    orders =
      table("orders") do
        pk :id, :integer
        fk :user_id, :integer
      end

    items =
      table("order_items") do
        pk :id, :integer
        fk :order_id, :integer
      end

    one_to_many users ~> orders, "places"
    one_to_many orders ~> items, "contains"
    one_to_many items ~> users, "assigned_to"
  end

# 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:

# An ERD with an isolated logging audit table defined using the DSL
isolated_schema =
  erd do
    users =
      table("users") do
        pk :id, :integer
      end

    orders =
      table("orders") do
        pk :id, :integer
        fk :user_id, :integer
      end

    table("audit_logs") do
      pk :id, :integer
      field :event, :varchar
    end

    one_to_many users ~> orders, "places"
  end

# 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)

5. Normalization Score

Analysis.normalization_score(schema)

The default :one_to_one penalty is 0 because legitimate 1-1 splits (e.g., PII isolation) are common. Pass weights: [one_to_one: 5] to opt-in to penalizing them.

Cheat Sheet

Lab DSL Syntax

Programmatic Pipe API & Analysis