--- name: ecto-patterns-82291296478f description: Ecto patterns for Phoenix/Elixir apps. Covers schemas, changesets, migrations, queries, Ecto.Multi, transactions, constraints, associations, pagination, tenant partitioning, performance, and testing. version: 1.0.0 category: toolchain author: Claude MPM Team license: MIT progressive_disclosure: entry_point: summary: "Ecto schemas/changesets, migrations, queries, transactions, constraints, pagination, tenancy, and performance for Phoenix apps." when_to_use: - "Designing schemas and changesets with validation/constraints" - "Running migrations safely (DDL ordering, locks) and managing data migrations" - "Executing transactional flows with Ecto.Multi" - "Implementing pagination, preloading, and multi-tenant partitioning" quick_start: - "mix ecto.create && mix ecto.migrate" - "Define schemas + changesets; enforce DB constraints (unique/foreign keys)" - "Use Repo.transaction / Ecto.Multi for multi-step writes" - "Preload associations; paginate with limit/offset or cursor libs (Scrivener/Flop)" token_estimate: entry: 170 full: 5400 --- # Ecto Patterns for Phoenix/Elixir Ecto is the data layer for Phoenix applications: schemas, changesets, queries, migrations, and transactions. Good Ecto practice keeps domain logic in contexts, enforces constraints in the database, and uses transactions for multi-step workflows. ## Schemas and Changesets ```elixir defmodule MyApp.Accounts.User do use Ecto.Schema import Ecto.Changeset schema "users" do field :email, :string field :hashed_password, :string field :confirmed_at, :naive_datetime has_many :memberships, MyApp.Orgs.Membership timestamps() end def registration_changeset(user, attrs) do user |> cast(attrs, [:email, :password]) |> validate_required([:email, :password]) |> validate_format(:email, ~r/@/) |> validate_length(:password, min: 12) |> unique_constraint(:email) |> hash_password() end defp hash_password(%{valid?: true} = cs), do: put_change(cs, :hashed_password, Argon2.hash_pwd_salt(get_change(cs, :password))) defp hash_password(cs), do: cs end ``` **Guidelines** - Keep casting/validation in changesets; keep business logic in contexts. - Always pair validation with DB constraints (`unique_constraint`, `foreign_key_constraint`). - Use `changeset/2` for updates; avoid mass assigning without casting. ## Migrations ```elixir def change do create table(:users) do add :email, :citext, null: false add :hashed_password, :string, null: false add :confirmed_at, :naive_datetime timestamps() end create unique_index(:users, [:email]) end ``` **Safe migration tips** - Prefer additive changes: add columns nullable, backfill, then enforce null: false. - For large tables: use `concurrently: true` for indexes; disable in `change` and wrap in `up/down` for Postgres. - Data migrations belong in separate modules called from `mix ecto.migrate` via `execute/1` or in distinct scripts; ensure idempotence. - Coordinate locks: avoid long transactions; break migrations into small steps. ## Queries and Preloads ```elixir import Ecto.Query def list_users(opts \\ %{}) do base = from u in MyApp.Accounts.User, preload: [:memberships], order_by: [desc: u.inserted_at] Repo.all(apply_pagination(base, opts)) end defp apply_pagination(query, %{limit: limit, offset: offset}), do: query |> limit(^limit) |> offset(^offset) defp apply_pagination(query, _), do: query ``` **Patterns** - Use `preload` rather than calling Repo in loops; prefer `Repo.preload/2` after fetching. - Use `select` to avoid loading large blobs. - For concurrency, use `Repo.transaction` with `lock: "FOR UPDATE"` in queries that need row-level locks. ## Transactions and Ecto.Multi ```elixir alias Ecto.Multi def onboard_user(attrs) do Multi.new() |> Multi.insert(:user, User.registration_changeset(%User{}, attrs)) |> Multi.insert(:org, fn %{user: user} -> Org.changeset(%Org{}, %{owner_id: user.id, name: attrs["org_name"]}) end) |> Multi.run(:welcome, fn _repo, %{user: user} -> MyApp.Mailer.deliver_welcome(user) {:ok, :sent} end) |> Repo.transaction() end ``` **Guidelines** - Prefer `Multi.run/3` for side effects that can fail; return `{:ok, value}` or `{:error, reason}`. - Use `Multi.update_all` for batch updates; include `where` guards to prevent unbounded writes. - Propagate errors upward; translate them in controllers/LiveViews. ## Associations and Constraints - Use `on_replace: :delete`/`:nilify` to control nested changes. - Define `foreign_key_constraint/3` and `unique_constraint/3` in changesets to surface DB errors cleanly. - For many-to-many, prefer join schema (`has_many :memberships`) instead of automatic `many_to_many` when you need metadata. ## Pagination and Filtering - Offset/limit for small datasets; cursor-based for large lists (`Scrivener`, `Flop`, `Paginator`). - Normalize filters in contexts; avoid letting controllers build queries directly. - Add composite indexes to match filter columns; verify with `EXPLAIN ANALYZE`. ## Multi-Tenancy Patterns - **Prefix-based**: Postgres schemas per tenant (`put_source/2` with `prefix:`) — good isolation, needs per-tenant migrations. - **Row-based**: `tenant_id` column + row filters — simpler migrations; add partial indexes per tenant when large. - Always scope queries by tenant in contexts; consider using policies/guards to enforce. ## Performance and Ops - Use `Repo.stream` for large exports; wrap in `Repo.transaction`. - Cache hot reads with ETS/Cachex; invalidate on writes. - Watch query counts in LiveView/Channels; preload before rendering to avoid N+1. - Telemetry: `OpentelemetryEcto` exports query timings; add DB connection pool metrics. ## Testing ```elixir use MyApp.DataCase, async: true test "registration changeset validates email" do changeset = User.registration_changeset(%User{}, %{email: "bad", password: "secretsecret"}) refute changeset.valid? assert %{email: ["has invalid format"]} = errors_on(changeset) end ``` - `DataCase` sets up sandboxed DB; keep tests async unless transactions conflict. - Use factories/fixtures in `test/support` to build valid structs quickly. - For migrations, add regression tests for constraints (unique/index-backed constraints). ## Common Pitfalls - Running risky DDL in a single migration step (avoid locks; break apart). - Skipping DB constraints and relying only on changesets. - Querying associations in loops instead of preloading. - Missing transactions for multi-step writes (partial state on failure). - Forgetting tenant scoping on read/write in multi-tenant setups. --- Source attribution: julio211916 Original project: https://github.com/julio211916/TlantiCAD-Studio-V0.1-alpha/blob/590aa4fe05d1ec71a02ee9cc76c9a20508b5bcea/skills/ju/skills/ecto-patterns/SKILL.md License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice MIT License Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Mega AI Hub resource metadata Creator / publisher: julio211916 Original project: https://github.com/julio211916/TlantiCAD-Studio-V0.1-alpha/blob/590aa4fe05d1ec71a02ee9cc76c9a20508b5bcea/skills/ju/skills/ecto-patterns/SKILL.md Attribution basis: Source repository record Fields of work: Development & Engineering, Product Management Roles: Backend Developer, Full-Stack Developer, Product Manager Specialties: Backend Tools: Ecto Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 245ee2627ba3b0ba6ab2f0de8d86047efdd9ec57262e7414b5adbd8afe00be99