Schema guide

BoxTurtle.Schema gives every Ecto schema the same small data-access API. Callers use it instead of building queries and calling the Repo themselves.

This guide builds a small blog step by step. Every snippet is complete, and later snippets show each module in full as it grows. Run them in order in iex -S mix and you'll get the results shown. The guide assumes an application :my_app with an Ecto repo MyApp.Repo.

1. Schemas and migration

BoxTurtle works with plain Ecto schemas. The only requirement is a changeset function for writes (changeset/2 by default).

defmodule MyApp.Blog.Author do
  use Ecto.Schema
  import Ecto.Changeset

  schema "authors" do
    field :name, :string
    has_many :posts, MyApp.Blog.Post
    timestamps()
  end

  def changeset(author, attrs) do
    author |> cast(attrs, [:name]) |> validate_required([:name])
  end
end

defmodule MyApp.Blog.Post do
  use Ecto.Schema
  import Ecto.Changeset

  schema "posts" do
    field :title, :string
    field :status, :string, default: "draft"
    field :likes, :integer, default: 0
    belongs_to :author, MyApp.Blog.Author
    timestamps()
  end

  # Used for inserts.
  def changeset(post, attrs) do
    post
    |> cast(attrs, [:title, :status, :likes, :author_id])
    |> validate_required([:title])
  end

  # Used for updates: only the status may change after creation.
  def update_changeset(post, attrs) do
    post
    |> cast(attrs, [:status])
    |> validate_inclusion(:status, ~w(draft published archived))
  end
end

# priv/repo/migrations/20260101000000_create_blog.exs
defmodule MyApp.Repo.Migrations.CreateBlog do
  use Ecto.Migration

  def change do
    create table(:authors) do
      add :name, :string, null: false
      timestamps()
    end

    create table(:posts) do
      add :title, :string, null: false
      add :status, :string, null: false, default: "draft"
      add :likes, :integer, null: false, default: 0
      add :author_id, references(:authors, on_delete: :delete_all)
      timestamps()
    end

    create index(:posts, [:author_id])
  end
end

2. Query and Change modules

Put the modules inside the schema's namespace. The schema is then inferred: MyApp.Blog.Post.Query serves MyApp.Blog.Post. If yours live elsewhere, pass schema: explicitly.

defmodule MyApp.Blog.Author.Query do
  use BoxTurtle.Schema.Query, repo: MyApp.Repo, default_sort: [:name, :id]
end

defmodule MyApp.Blog.Author.Change do
  use BoxTurtle.Schema.Change, repo: MyApp.Repo
end

defmodule MyApp.Blog.Post.Query do
  # Newest first; the id breaks ties between rows inserted in the same second.
  use BoxTurtle.Schema.Query, repo: MyApp.Repo, default_sort: [desc: :inserted_at, desc: :id]
end

defmodule MyApp.Blog.Post.Change do
  use BoxTurtle.Schema.Change, repo: MyApp.Repo, update_changeset: :update_changeset
end

All use options are documented in BoxTurtle.Schema.Query and BoxTurtle.Schema.Change.

3. Writing

alias MyApp.Blog.{Author, Post}

{:ok, ann} = Author.Change.create(%{name: "Ann"})
{:ok, bob} = Author.Change.create(%{name: "Bob"})

{:ok, hello} = Post.Change.create(%{title: "Hello", author_id: ann.id})
{:ok, _second} = Post.Change.create(%{title: "Second post", likes: 12, author_id: ann.id})
{:ok, _news} = Post.Change.create(%{title: "Bob's news", status: "published", likes: 5, author_id: bob.id})
{:ok, scratch} = Post.Change.create(%{title: "Scratch"})

# changeset/2 requires a title
{:error, %Ecto.Changeset{}} = Post.Change.create(%{})

# Updates go through update_changeset/2
{:ok, hello} = Post.Change.update(hello, %{status: "published"})
{:error, %Ecto.Changeset{}} = Post.Change.update(hello, %{status: "deleted"})

# Delete a struct, a changeset or a primary key
{:ok, _} = Post.Change.delete(scratch)
{:error, :not_found} = Post.Change.delete(scratch.id)

create/1 and update/1 also accept a ready-made Ecto.Changeset. create!/1, update!/2 and delete!/1 return the struct or raise; delete!/1 raises Ecto.NoResultsError for a missing key.

The blog now has three posts:

4. Reading

Every function has a tuple form and a ! form:

{:ok, %Post{title: "Hello"}} = Post.Query.get(hello.id)
{:error, :not_found} = Post.Query.get(-1)
nil = Post.Query.get!(-1)
%Post{likes: 12} = Post.Query.get!(%{title: "Second post"})   # look up by other columns

Post.Query.list!() |> Enum.map(& &1.title)
#=> ["Bob's news", "Second post", "Hello"]   (newest first)

Post.Query.list!(filters: [status: "published"], sort: [:title]) |> Enum.map(& &1.title)
#=> ["Bob's news", "Hello"]

Post.Query.count!()                          #=> 3
Post.Query.count!(group_by: [:status])       #=> [%{status: "draft", count: 1}, %{status: "published", count: 2}]
Post.Query.count!(distinct: [:author_id])    #=> 2

# An Ecto.Query, not run yet, to compose further or run yourself
query = Post.Query.query!(filters: [author_id: ann.id])
2 = query |> MyApp.Repo.all() |> length()

Call-time options (all of them are listed in BoxTurtle.Schema.Query.API):

Filters

Default filters compare columns:

Post.Query.count!(filters: [status: "published"])                # status = 'published'       => 2
Post.Query.count!(filters: [status: ["draft", "published"]])     # status IN (...)            => 3
Post.Query.count!(filters: [author_id: nil])                     # author_id IS NULL          => 0
Post.Query.count!(filters: [{"status", "draft"}])                # string key, existing atom  => 1

String keys are converted with String.to_existing_atom/1, so filters built from request params can't create new atoms. Still, only pass column names you allow. Any column of the schema is filterable.

For anything other than equality, write a filter module. Each clause handles the filter at the head of the list and must pass the rest back to filter_with/2. Filters your clauses don't match fall through to the defaults:

defmodule MyApp.Blog.Post.Filters do
  use BoxTurtle.Schema.Filters
  import Ecto.Query

  def filter_with(query, [{:min_likes, likes} | rest]) do
    query
    |> where([p], p.likes >= ^likes)
    |> filter_with(rest)
  end
end

defmodule MyApp.Blog.Post.Query do
  use BoxTurtle.Schema.Query,
    repo: MyApp.Repo,
    default_sort: [desc: :inserted_at, desc: :id],
    filter_module: MyApp.Blog.Post.Filters
end

Post.Query.list!(filters: [status: "published", min_likes: 1]) |> Enum.map(& &1.title)
#=> ["Bob's news"]

Custom clauses see keys exactly as given, so match atoms and convert request params yourself.

Sorting

A sort spec is a list of columns, {direction, column} pairs, or {direction, [columns]}. Keyword lists work as-is: [desc: :inserted_at, asc: :id]. Named sorts work like filters:

defmodule MyApp.Blog.Post.Sort do
  use BoxTurtle.Schema.Sort
  import Ecto.Query

  # Most liked first; the id keeps the order stable for pagination.
  def sort_by(query, [:popularity | rest]) do
    query
    |> order_by([p], desc: p.likes, asc: p.id)
    |> sort_by(rest)
  end
end

defmodule MyApp.Blog.Post.Query do
  use BoxTurtle.Schema.Query,
    repo: MyApp.Repo,
    default_sort: [desc: :inserted_at, desc: :id],
    filter_module: MyApp.Blog.Post.Filters,
    sorting_module: MyApp.Blog.Post.Sort
end

Post.Query.list!(sort: [:popularity]) |> Enum.map(& &1.title)
#=> ["Second post", "Bob's news", "Hello"]

An unknown sort element raises ArgumentError.

Preloads

Preloads are fetched with LEFT JOINs, so a list or get, together with all its associations, is a single round trip to the database. Ecto's plain preload issues one extra query per association. Nesting works to any depth:

[%Post{author: %Author{name: "Bob"}} | _] = Post.Query.list!(preloads: [:author])

Author.Query.list!(preloads: [posts: :author])
|> Enum.map(&{&1.name, length(&1.posts)})
#=> [{"Ann", 2}, {"Bob", 1}]

Joining a has_many repeats each parent row once per child. To keep :page and :distinct counting parents, BoxTurtle applies them in a primary-key subquery when a to-many association is preloaded, still in the same statement. Preloads accept association names only, not preload queries or functions.

Pagination

page: n returns page n of :page_size rows (default 40; set page_size: in use BoxTurtle.Schema.Query to change it). page: [number: n, size: s] sets the size per call. Pages below 1 are treated as 1.

Post.Query.list!(sort: [:popularity], page: [number: 1, size: 2]) |> Enum.map(& &1.title)
#=> ["Second post", "Bob's news"]

Post.Query.list!(sort: [:popularity], page: [number: 2, size: 2]) |> Enum.map(& &1.title)
#=> ["Hello"]

Always page with a sort that ends in a unique column, so rows don't move between pages.

More filter and sort examples

The repository has reference filter and sort modules with tests that use them: test/support/examples/schema.ex, custom_filters_test.exs and custom_sort_test.exs. They cover:

• Open modules (use BoxTurtle.Schema.Filters / Sort):
  • comparisons, LIKE search with escaping, flags that expand to other filters;
  • overriding a real column for one value;
  • filters and sorts on a joined association, sharing one named join;
  • filtering parents by their children with a subquery.
• Closed modules that implement the behaviour directly. They accept only allow-listed, string-keyed request params, and everything else raises.

5. Scoped queries: extend and delegate

The optional *_by(pattern, opts \\ []) functions (get_by, list_by, count_by, query_by! and their ! variants) answer questions like "the posts of this author".

• extend/2 defines one of them with your own clauses. It generates the function head with the opts \\ [] default. For list_by, count_by and query_by! (and their variants), it also adds a clause that treats a nil pattern as "everything".
• delegate/1 defines them by forwarding to a shared delegate module. Use it when many schemas are scoped the same way (every row with an author_id, say).

Write the scope once, in a delegate module. It is an ordinary Query module whose clauses use extend/2:

defmodule MyApp.Blog.ByAuthor do
  use BoxTurtle.Schema.Query, repo: MyApp.Repo, schema: MyApp.Blog.Author

  extend :list_by do
    def list_by(%MyApp.Blog.Author{id: id}, opts), do: list(add_filters(opts, author_id: id))
  end

  extend :count_by do
    def count_by(%MyApp.Blog.Author{id: id}, opts), do: count(add_filters(opts, author_id: id))
  end
end

Then delegate to it from each schema's Query module, and add your own clauses with extend/2. This is the final Post.Query:

defmodule MyApp.Blog.Post.Query do
  use BoxTurtle.Schema.Query,
    repo: MyApp.Repo,
    default_sort: [desc: :inserted_at, desc: :id],
    filter_module: MyApp.Blog.Post.Filters,
    sorting_module: MyApp.Blog.Post.Sort,
    delegates: [{MyApp.Blog.ByAuthor, %MyApp.Blog.Author{}}]

  # list_by/2 and count_by/2 for %Author{} (and nil) patterns
  delegate [:list_by, :count_by]

  # get_by!/2 for a title
  extend :get_by! do
    def get_by!(title, opts) when is_binary(title), do: get!(%{title: title}, opts)
  end
end

{:ok, posts} = Post.Query.list_by(ann)
Enum.map(posts, & &1.title)   #=> ["Second post", "Hello"]

{:ok, 1} = Post.Query.count_by(ann, filters: [status: "published"])
{:ok, 2} = Post.Query.count_by(ann, filters: [min_likes: 0])   # custom filters work in delegates too
{:ok, all_posts} = Post.Query.list_by(nil)                     # nil means unscoped
3 = length(all_posts)

%Post{likes: 5} = Post.Query.get_by!("Bob's news")

Post.Query passes %Author{} arguments to ByAuthor with its own settings (repo, schema, default sort, filter module and so on) injected into the options. So ByAuthor.list/1 queries posts, not authors. A delegate clause is generated only if the delegate module defines that function. That check happens at compile time, so the delegate must compile first. Mix handles this when the delegate is in its own file; within a single file, define the delegate above the modules that use it.

6. KSUID primary keys

defmodule MyApp.Blog.Tag do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:id, BoxTurtle.Schema.Ecto.Ksuid, autogenerate: true}
  schema "tags" do
    field :name, :string
  end

  def changeset(tag, attrs), do: tag |> cast(attrs, [:name]) |> validate_required([:name])
end

defmodule MyApp.Blog.Tag.Query do
  use BoxTurtle.Schema.Query, repo: MyApp.Repo, default_sort: [:name]
end

defmodule MyApp.Blog.Tag.Change do
  use BoxTurtle.Schema.Change, repo: MyApp.Repo
end

# priv/repo/migrations/20260101000001_create_tags.exs
defmodule MyApp.Repo.Migrations.CreateTags do
  use Ecto.Migration

  def change do
    create table(:tags, primary_key: false) do
      add :id, :string, size: 27, primary_key: true
      add :name, :string, null: false
    end
  end
end

{:ok, tag} = MyApp.Blog.Tag.Change.create(%{name: "elixir"})
27 = String.length(tag.id)
{:ok, ^tag} = MyApp.Blog.Tag.Query.get(tag.id)

KSUIDs are 27-character, time-sortable strings.

7. Testing

The generated functions are ordinary module functions, so test them through your context with the Ecto SQL sandbox. With a Phoenix-generated MyApp.DataCase:

defmodule MyApp.BlogTest do
  use MyApp.DataCase, async: true

  alias MyApp.Blog.{Author, Post}

  test "list_by/2 returns only the author's published posts" do
    {:ok, ann} = Author.Change.create(%{name: "Ann"})
    {:ok, bob} = Author.Change.create(%{name: "Bob"})
    {:ok, _} = Post.Change.create(%{title: "Mine", status: "published", author_id: ann.id})
    {:ok, _} = Post.Change.create(%{title: "Draft", author_id: ann.id})
    {:ok, _} = Post.Change.create(%{title: "Theirs", status: "published", author_id: bob.id})

    assert {:ok, [%Post{title: "Mine"}]} = Post.Query.list_by(ann, filters: [status: "published"])
  end
end

BoxTurtle's own suite runs against SQLite (ecto_sqlite3). For a compact setup, see test/support/ in the repository.