oliver-kriska/claude-elixir-phoenix / targets/codex/skills/ecto-patterns
ecto-patterns
VerifiedUse when a task touches Ecto, even a how-to question: schemas, changesets, validations, queries, preloads, Multi…
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 3.3 KB |
| references/changesets.md | markdown | 4.2 KB |
| references/fulltext-search.md | markdown | 5.6 KB |
| references/migrations.md | markdown | 4.5 KB |
| references/queries.md | markdown | 4.8 KB |
| references/transactions.md | markdown | 4.4 KB |
references/migrations.md
View on GitHub# Migrations Reference
## Basic Migration
```elixir
defmodule MyApp.Repo.Migrations.CreateUsers do
use Ecto.Migration
def change do
create table(:users, primary_key: false) do
add :id, :binary_id, primary_key: true
add :email, :string, null: false
add :name, :string
add :role, :string, default: "user"
timestamps(type: :utc_datetime_usec)
end
create unique_index(:users, [:email])
end
end
```
## Add Foreign Key (Safe - 2 steps)
```elixir
# Step 1: Add without validation (fast, no table lock)
def change do
alter table(:posts) do
add :user_id, references(:users, type: :binary_id, validate: false), null: false
end
create index(:posts, [:user_id])
end
# Step 2: Validate in separate migration (separate deploy)
def change do
execute "ALTER TABLE posts VALIDATE CONSTRAINT posts_user_id_fkey", ""
end
```
## Add NOT NULL (Safe - 3 steps)
```elixir
# Step 1: Add check constraint without validation
def change do
create constraint("products", :active_not_null,
check: "active IS NOT NULL",
validate: false
)
end
# Step 2: Backfill data (separate deploy)
def change do
execute "UPDATE products SET active = false WHERE active IS NULL", ""
end
# Step 3: Validate constraint, add NOT NULL, drop constraint
def change do
execute "ALTER TABLE products VALIDATE CONSTRAINT active_not_null", ""
execute "ALTER TABLE products ALTER COLUMN active SET NOT NULL", ""
drop constraint("products", :active_not_null)
end
```
## Pre-Migration Safety: Check Data BEFORE Unique Index
A unique index on a table with existing duplicates fails at deploy time —
in the worst case mid-release. Check FIRST, and include soft-deleted rows:
they're invisible in the app but still block the index.
```sql
-- Find duplicates the index would reject (run before writing the migration)
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;
-- Soft-deleted rows count too — check whether they collide
SELECT email, COUNT(*) FILTER (WHERE deleted_at IS NOT NULL) AS deleted,
COUNT(*) FILTER (WHERE deleted_at IS NULL) AS live
FROM users GROUP BY email HAVING COUNT(*) > 1;
```
Resolutions, in order of preference:
1. **Partial index** when soft-deleted rows may legitimately collide:
`create unique_index(:users, [:email], where: "deleted_at IS NULL")`
2. **Data-fix migration first** (separate deploy), then the index
3. **Composite key** if the duplicates are actually valid scoping
(e.g., unique per tenant: `unique_index(:users, [:org_id, :email])`)
Same discipline applies before adding NOT NULL (check for NULLs) and
before foreign keys (check for orphaned rows).
## Concurrent Index (Large Tables)
```elixir
@disable_ddl_transaction true
@disable_migration_lock true
def change do
create index(:posts, [:slug], concurrently: true)
end
```
## Batched Data Migration
```elixir
def change do
# For large tables, process in batches
execute &migrate_data/0, &rollback_data/0
end
defp migrate_data do
repo().transaction(fn ->
from(u in "users", where: is_nil(u.status), select: u.id, limit: 1000)
|> repo().all()
|> Enum.each(&update_user_status/1)
end)
end
```
## Mixed Primary Key Types (bigint + binary_id)
When integrating libraries that use UUID PKs (Sagents, Oban, etc.)
while your project uses bigint:
```elixir
# WRONG: Global @foreign_key_type affects ALL associations
@foreign_key_type :binary_id
# RIGHT: Explicit type ONLY on specific associations
schema "interviews" do
belongs_to :user, MyApp.Accounts.User # bigint (default)
belongs_to :conversation, Agents.Conversation, type: :binary_id
end
```
In migrations, match the referenced table's PK type:
```elixir
alter table(:interviews) do
add :user_id, references(:users, type: :bigint), null: false
add :conversation_id, references(:conversations, type: :binary_id)
end
```
## Associations
```elixir
# One-to-many (always specify on_delete!)
has_many :posts, Post, on_delete: :delete_all
belongs_to :user, User
# Many-to-many
many_to_many :tags, Tag, join_through: "post_tags", on_replace: :delete
# Has one through
has_one :organization, through: [:user, :organization]
# Self-referential
belongs_to :parent, __MODULE__
has_many :children, __MODULE__, foreign_key: :parent_id
```
## Optimistic Locking
```elixir
schema "products" do
field :name, :string
field :lock_version, :integer, default: 1
end
def changeset(product, attrs) do
product
|> cast(attrs, [:name])
|> optimistic_lock(:lock_version)
end
# Usage - raises Ecto.StaleEntryError if version changed
Repo.update!(changeset)
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| slack-gif-creatoranthropics/skillsKnowledge and utilities for creating animated GIFs optimized for Slack. Provides constraints, validation tools, and animation concepts. Use when users request animated GIFs for Slack like "make me a GIF of X doing Y for Slack." | Stars: 198k | Updated | Claude CodeCodex | Verified | Install |
| ponytailDietrichGebert/ponytailLazy senior dev mode for any coding task: the smallest change that fully solves it, and a reply a busy human understands in one read. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailHarvest every shortcut: comment into one debt ledger, so deferrals get tracked instead of forgotten. One-shot report. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-debtDietrichGebert/ponytailList every `shortcut:` comment (and older `ponytail:` ones) as a debt ledger. One-shot report, changes nothing. Use for "ponytail debt", "what did ponytail defer", "list the shortcuts", /ponytail-debt. | Stars: 194k | Updated | Claude CodeCodex | Verified | Install |