# Ecto: Schemas, Changesets and Queries — Elixir & Phoenix

Source: https://www.geekswithgeeks.com/en/elixir-phoenix/p-ecto

> Persist data with Ecto schemas, validate with changesets and query safely.

## Data access, explicitly

**Ecto** is Elixir's database toolkit, not a traditional ORM. Its parts: the **Repo** (`Shop.Repo`) performs all database operations (`Repo.insert`, `Repo.get`, `Repo.all`, `Repo.transaction`); **schemas** map database tables to structs (`schema "products" do field :name, :string ... end`) with associations such as `belongs_to` and `has_many`; **changesets** filter, cast and **validate** external data before it touches the database (`cast/3`, `validate_required`, `validate_number`, `unique_constraint`), collecting errors to show to users; **migrations** (`mix ecto.gen.migration add_products`) evolve the schema with `create table` and indexes; and the **query DSL** builds composable, parameterised SQL (`from p in Product, where: p.price < ^max`), safe from SQL injection because values are pinned as parameters. Associations are **never loaded implicitly**: use `Repo.preload` or `preload:` in queries, which avoids hidden N+1 queries. **`Ecto.Multi`** groups several operations into a single transaction. PostgreSQL is the most common database, with adapters for MySQL, SQLite and others.

## Schema, changeset, migration and queries

Validation happens in the changeset; queries pin values as parameters.

```elixir
# priv/repo/migrations/20261003120000_create_products.exs
defmodule Shop.Repo.Migrations.CreateProducts do
  use Ecto.Migration

  def change do
    create table(:products) do
      add :sku, :string, null: false
      add :name, :string, null: false
      add :price_paise, :integer, null: false
      timestamps()
    end

    create unique_index(:products, [:sku])
  end
end

# lib/shop/catalog/product.ex
defmodule Shop.Catalog.Product do
  use Ecto.Schema
  import Ecto.Changeset

  schema "products" do
    field :sku, :string
    field :name, :string
    field :price_paise, :integer
    timestamps()
  end

  def changeset(product, attrs) do
    product
    |> cast(attrs, [:sku, :name, :price_paise])
    |> validate_required([:sku, :name, :price_paise])
    |> validate_number(:price_paise, greater_than: 0)
    |> unique_constraint(:sku)
  end
end

# lib/shop/catalog.ex (context)
defmodule Shop.Catalog do
  import Ecto.Query
  alias Shop.Repo
  alias Shop.Catalog.Product

  def create_product(attrs), do: %Product{} |> Product.changeset(attrs) |> Repo.insert()

  def cheaper_than(max_paise) do
    from(p in Product, where: p.price_paise < ^max_paise, order_by: [asc: p.price_paise])
    |> Repo.all()
  end
end

# Shop.Catalog.create_product(%{"sku" => "pen", "name" => "Pen", "price_paise" => 0})
# => {:error, %Ecto.Changeset{errors: [price_paise: {"must be greater than %{number}", ...}]}}
```

## A security check at the door

A changeset is the security check at the entrance: it inspects every bag (field), lets through only what is allowed, and writes down everything wrong, so problems are reported politely instead of causing chaos inside the database.

**Quiz:** What is the main job of an Ecto changeset?

- [ ] To open database connections
- [x] To cast and validate external data and track changes and errors before persisting
- [ ] To render HTML
- [ ] To start processes

*Answer:* To cast and validate external data and track changes and errors before persisting. Changesets filter, cast and validate data, collecting errors.