ecto-schema-patterns

name	ecto-schema-patterns
user-invocable	false
description	Use when defining data structures using Ecto schemas including fields, associations, embedded schemas, and schema metadata. Use for modeling domain data in Elixir applications.
allowed-tools	["Bash","Read"]

Ecto Schema Patterns

Master Ecto schemas to define robust data structures for your Elixir applications. This skill covers schema definitions, field types, associations, embedded schemas, and advanced patterns for modeling complex domain data.

Basic Schema Definition

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string
    field :age, :integer
    field :confirmed_at, :naive_datetime

    timestamps()
  end
end

Schemas map to database tables and define the structure of your data. Each schema has a source name (table name) and a list of fields with their types. The timestamps() macro automatically adds inserted_at and updated_at fields.

Field Types and Options

defmodule MyApp.Product do
  use Ecto.Schema

  schema "products" do
    # Standard field types
    field :title, :string
    field :description, :string
    field :price, :decimal
    field :quantity, :integer
    field :is_active, :boolean, default: true
    field :published_at, :utc_datetime

    # Enum type
    field :status, Ecto.Enum, values: [:draft, :published, :archived]

    # Map type for unstructured data
    field :metadata, :map

    # Array type
    field :tags, {:array, :string}

    # Binary type for binary data
    field :image_data, :binary

    # Virtual field (not persisted to database)
    field :display_price, :string, virtual: true

    timestamps()
  end
end

Ecto supports a wide range of field types including strings, integers, decimals, booleans, datetime types, enums, maps, arrays, and binary data. Virtual fields exist only in memory and are useful for computed values.

Using Map Type for Flexible Data

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string
    field :data, :map

    timestamps()
  end
end

# Usage
user = %MyApp.User{
  name: "John Doe",
  email: "[e-mail]",
  data: %{
    preferences: %{
      theme: "dark",
      notifications: true
    },
    settings: %{
      language: "en"
    }
  }
}

The :map type allows storing arbitrary Elixir maps in the database, providing flexibility for unstructured or semi-structured data without requiring schema changes.

Embedded Schemas

defmodule MyApp.Address do
  use Ecto.Schema

  embedded_schema do
    field :street, :string
    field :city, :string
    field :state, :string
    field :zip_code, :string
    field :country, :string, default: "US"
  end
end

Embedded schemas define data structures that are not tied to a database table. They can be embedded within other schemas or used independently in memory for data validation and casting.

Embedding One Association

defmodule MyApp.Order do
  use Ecto.Schema

  schema "orders" do
    field :total, :decimal
    field :status, :string

    embeds_one :item, Item

    timestamps()
  end
end

defmodule MyApp.Item do
  use Ecto.Schema

  embedded_schema do
    field :title, :string
    field :price, :decimal
    field :quantity, :integer
  end
end

The embeds_one macro defines a one-to-one relationship with an embedded schema. The embedded data is stored as a JSON or map column in the parent table, not in a separate table.

Inline Embedded Schema Definition

defmodule MyApp.Parent do
  use Ecto.Schema

  schema "parents" do
    field :name, :string

    embeds_one :child, Child do
      field :name, :string
      field :age, :integer
    end

    timestamps()
  end
end

Schemas can be embedded inline using a do block, which creates a nested module (e.g., MyApp.Parent.Child). This is useful for simpler embedded structures that don't need to be defined separately.

Embedding Many Association

defmodule MyApp.Order do
  use Ecto.Schema

  schema "orders" do
    field :customer_name, :string
    field :total, :decimal

    embeds_many :items, OrderItem do
      field :product_name, :string
      field :quantity, :integer
      field :price, :decimal
    end

    timestamps()
  end
end

The embeds_many macro defines a one-to-many relationship with embedded schemas. Multiple embedded records are stored as a JSON array in the parent table.

Complex Embedded Schema with Custom Changeset

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :full_name, :string
    field :email, :string
    field :avatar_url, :string
    field :confirmed_at, :naive_datetime

    embeds_one :profile, Profile do
      field :online, :boolean
      field :dark_mode, :boolean
      field :visibility, Ecto.Enum, values: [:public, :private, :friends_only]
    end

    timestamps()
  end

  def changeset(%__MODULE__{} = user, attrs \\ %{}) do
    user
    |> Ecto.Changeset.cast(attrs, [:full_name, :email])
    |> Ecto.Changeset.cast_embed(:profile, required: true, with: &profile_changeset/2)
  end

  def profile_changeset(profile, attrs \\ %{}) do
    profile
    |> Ecto.Changeset.cast(attrs, [:online, :dark_mode, :visibility])
    |> Ecto.Changeset.validate_required([:online, :visibility])
  end
end

Custom changeset functions can be defined for embedded schemas using the :with option in cast_embed/3. This allows for specific validation logic on nested data.

Extracted Embedded Schema Module

# user/user.ex
defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :full_name, :string
    field :email, :string
    field :avatar_url, :string
    field :confirmed_at, :naive_datetime

    embeds_one :profile, MyApp.UserProfile

    timestamps()
  end
end

# user/user_profile.ex
defmodule MyApp.UserProfile do
  use Ecto.Schema

  embedded_schema do
    field :online, :boolean
    field :dark_mode, :boolean
    field :visibility, Ecto.Enum, values: [:public, :private, :friends_only]
  end

  def changeset(%__MODULE__{} = profile, attrs \\ %{}) do
    profile
    |> Ecto.Changeset.cast(attrs, [:online, :dark_mode, :visibility])
    |> Ecto.Changeset.validate_required([:online, :visibility])
  end
end

Extracting embedded schemas into dedicated modules improves organization and allows the embedded schema to have its own changeset functions, validations, and behavior.

Belongs To Association

defmodule MyApp.Comment do
  use Ecto.Schema

  schema "comments" do
    field :body, :string
    field :author, :string

    belongs_to :post, MyApp.Post

    timestamps()
  end
end

defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    field :body, :string

    has_many :comments, MyApp.Comment

    timestamps()
  end
end

The belongs_to macro defines a foreign key relationship. By default, it creates a post_id field in the comments table. The parent schema typically defines the inverse relationship with has_many.

Custom Belongs To Field

defmodule MyApp.Comment do
  use Ecto.Schema

  schema "comments" do
    field :post_id, :integer
    belongs_to :post, MyApp.Post, define_field: false
  end
end

You can customize the foreign key field definition by setting define_field: false and manually defining the field. This is useful when you need special options on the foreign key field.

Has One Association

defmodule MyApp.Account do
  use Ecto.Schema

  schema "accounts" do
    field :email, :string

    has_one :profile, MyApp.Profile

    timestamps()
  end
end

defmodule MyApp.Profile do
  use Ecto.Schema

  schema "profiles" do
    field :name, :string
    field :age, :integer

    belongs_to :account, MyApp.Account

    timestamps()
  end
end

The has_one macro defines a one-to-one relationship where the foreign key is stored in the associated schema. The associated schema must have a corresponding belongs_to relationship.

Has Many Association

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string

    has_many :posts, MyApp.Post, foreign_key: :author_id

    timestamps()
  end
end

The has_many macro defines a one-to-many relationship. You can customize the foreign key name using the foreign_key option if it differs from the default convention.

Many to Many Association with Join Table

defmodule MyApp.Post do
  use Ecto.Schema

  schema "posts" do
    field :title, :string
    field :body, :string

    many_to_many :tags, MyApp.Tag,
      join_through: "posts_tags",
      on_replace: :delete

    timestamps()
  end
end

defmodule MyApp.Tag do
  use Ecto.Schema

  schema "tags" do
    field :name, :string

    many_to_many :posts, MyApp.Post,
      join_through: "posts_tags"

    timestamps()
  end
end

The many_to_many macro defines a many-to-many relationship through a join table. The join_through option specifies the table name, and on_replace: :delete controls how the association is updated.

Many to Many with Join Schema

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string

    many_to_many :organizations, MyApp.Organization,
      join_through: MyApp.UserOrganization

    timestamps()
  end
end

defmodule MyApp.Organization do
  use Ecto.Schema

  schema "organizations" do
    field :name, :string

    many_to_many :users, MyApp.User,
      join_through: MyApp.UserOrganization

    timestamps()
  end
end

defmodule MyApp.UserOrganization do
  use Ecto.Schema

  @primary_key false
  schema "users_organizations" do
    belongs_to :user, MyApp.User
    belongs_to :organization, MyApp.Organization
    field :role, :string
    field :joined_at, :utc_datetime

    timestamps()
  end
end

Using a dedicated join schema (instead of just a table name) allows you to add additional fields to the join table, such as role or timestamp information.

Schema with Composite Primary Key

defmodule MyApp.UserOrganization do
  use Ecto.Schema

  @primary_key false
  schema "users_organizations" do
    belongs_to :user, MyApp.User, primary_key: true
    belongs_to :organization, MyApp.Organization, primary_key: true
    field :role, :string

    timestamps()
  end
end

Composite primary keys can be created by setting @primary_key false and marking the relevant belongs_to associations with primary_key: true.

Custom Primary Key

defmodule MyApp.Product do
  use Ecto.Schema

  @primary_key {:sku, :string, autogenerate: false}
  schema "products" do
    field :name, :string
    field :price, :decimal

    timestamps()
  end
end

You can customize the primary key field name, type, and autogeneration behavior using the @primary_key module attribute.

Schema Metadata Access

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string

    has_many :posts, MyApp.Post
    belongs_to :organization, MyApp.Organization

    timestamps()
  end
end

# Access schema metadata
MyApp.User.__schema__(:source)        # "users"
MyApp.User.__schema__(:fields)        # [:id, :name, :email, :organization_id, :inserted_at, :updated_at]
MyApp.User.__schema__(:primary_key)   # [:id]
MyApp.User.__schema__(:associations)  # [:posts, :organization]
MyApp.User.__schema__(:type, :name)   # :string

The __schema__/1 and __schema__/2 functions provide access to schema metadata at runtime, useful for metaprogramming and dynamic query building.

Self-Referencing Association

defmodule MyApp.Person do
  use Ecto.Schema

  schema "people" do
    field :name, :string

    many_to_many :relations, MyApp.Person,
      join_through: MyApp.Relationship,
      join_keys: [person_id: :id, relation_id: :id]

    timestamps()
  end
end

defmodule MyApp.Relationship do
  use Ecto.Schema

  @primary_key false
  schema "relationships" do
    belongs_to :person, MyApp.Person
    belongs_to :relation, MyApp.Person
    field :relationship_type, :string

    timestamps()
  end
end

Self-referencing associations allow a schema to reference itself, useful for hierarchical data or relationships between entities of the same type.

Polymorphic Association Pattern

defmodule MyApp.TodoItem do
  use Ecto.Schema

  schema "todo_items" do
    field :description, :string

    timestamps()
  end
end

defmodule MyApp.Comment do
  use Ecto.Schema

  schema "comments" do
    field :body, :string
    field :commentable_id, :integer
    field :commentable_type, :string

    timestamps()
  end
end

# Create a comment for a TodoItem
comment = %MyApp.Comment{
  body: "This needs to be done ASAP",
  commentable_id: todo_item.id,
  commentable_type: "todo_item"
}

Polymorphic associations can be implemented using a combination of ID and type fields. While Ecto doesn't have built-in polymorphic associations like some ORMs, this pattern provides similar functionality.

Schema with Virtual Fields

defmodule MyApp.User do
  use Ecto.Schema

  schema "users" do
    field :first_name, :string
    field :last_name, :string
    field :email, :string

    # Virtual fields
    field :full_name, :string, virtual: true
    field :password, :string, virtual: true
    field :password_hash, :string

    timestamps()
  end

  def build_full_name(%__MODULE__{} = user) do
    %{user | full_name: "#{user.first_name} #{user.last_name}"}
  end
end

Virtual fields are not persisted to the database but can be useful for temporary data like unhashed passwords or computed values like full names.

Schema with Custom Source

defmodule MyApp.LegacyUser do
  use Ecto.Schema

  @schema_prefix "legacy"
  schema "tbl_users" do
    field :user_name, :string, source: :username
    field :user_email, :string, source: :email
    field :create_date, :utc_datetime, source: :created_at

    timestamps()
  end
end

You can map schema fields to different column names using the :source option, useful when working with legacy databases or following different naming conventions.

Schema Prefix for Multi-Tenant Applications

defmodule MyApp.Organization do
  use Ecto.Schema

  @schema_prefix "public"
  schema "organizations" do
    field :name, :string
    field :slug, :string

    timestamps()
  end
end

defmodule MyApp.Tenant.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    field :email, :string

    timestamps()
  end
end

# Query with dynamic prefix
MyApp.Repo.all(MyApp.Tenant.User, prefix: "tenant_#{tenant_id}")

The @schema_prefix attribute and the prefix option enable multi-tenant applications where each tenant has its own database schema or prefix.

When to Use This Skill

Use ecto-schema-patterns when you need to:

• Define database-backed schemas for your application's domain models
• Create embedded schemas for nested or complex data structures
• Set up associations between different schemas (belongs_to, has_many, many_to_many)
• Model hierarchical or self-referential data
• Work with legacy databases using custom field mappings
• Implement multi-tenant applications with schema prefixes
• Define virtual fields for computed or temporary data
• Use custom primary keys or composite keys
• Store unstructured data using map or array fields
• Create polymorphic association patterns

Best Practices

• Use descriptive schema and field names that reflect your domain
• Always define timestamps() for audit trails unless there's a specific reason not to
• Prefer embedded schemas over JSON columns when the structure is known
• Use Ecto.Enum for fields with a fixed set of values
• Keep schemas focused on data structure, put business logic elsewhere
• Use virtual fields for data that shouldn't be persisted
• Define proper associations to leverage Ecto's preloading capabilities
• Use on_replace: :delete for many_to_many associations that should cascade
• Extract complex embedded schemas into separate modules for reusability
• Use belongs_to with define_field: false when you need custom foreign key options
• Leverage schema metadata functions for metaprogramming sparingly
• Use :source option to bridge schema and database naming differences
• Set appropriate @primary_key for non-standard primary keys
• Use join schemas instead of join tables when you need extra attributes
• Document schema relationships and constraints in module documentation

Common Pitfalls

• Forgetting to add timestamps() and losing audit information
• Not setting on_replace option for associations, causing unexpected behavior
• Confusing embeds_one/embeds_many with has_one/has_many
• Using :map type when an embedded schema would provide better structure
• Defining associations without corresponding database constraints
• Not marking virtual fields with virtual: true, causing database errors
• Mixing business logic with schema definitions
• Creating circular dependencies between schema modules
• Forgetting to set @primary_key false for join schemas
• Not using proper foreign key options in belongs_to
• Accessing associations without preloading them first
• Over-nesting embedded schemas, making them hard to maintain
• Not considering the difference between has_one and belongs_to
• Using polymorphic patterns without proper indexing
• Forgetting that embedded data is stored as JSON/map in a single column
• Not handling nil values properly in custom source mappings
• Creating too many associations, leading to N+1 query problems
• Not using join schemas when additional join table attributes are needed
• Hardcoding schema prefixes instead of making them configurable
• Not validating embedded schemas separately from parent schemas

Resources

Official Ecto Documentation

• Ecto.Schema Module
• Schema Definition
• Field Types
• Associations
• Embedded Schemas
• Polymorphic Associations
• Self-Referencing Many to Many

Guides and Patterns

• Getting Started with Ecto
• Schema Metadata
• Multi-Tenancy Guide
• Ecto Best Practices

Community Resources

• Elixir School - Ecto
• Elixir Forum - Ecto Category
• Programming Ecto Book