Correct way to use a Postgrex extension types with latest Ecto/Postgrex/Phoenix 1.7 in 2024

[sskamal]

Greetings!
Elixir/Phoenix noob here, so it is possible this question may have been answered before, apologies

I am trying to piece together the steps involved in using the Postgrex ltree extension in my Phoenix 1.7 app.

I have looked around and the examples I found were for a package or a custom extension. Github also does not have many examples on how to set this up in a Phoenix app.

Basically, if someone can please spell out the steps involved in Ecto + Postgrex extensions integration for the latest versions of Ecto, Postgrex + Phoenix in 2024, that would be hugely helpful.

I have gathered that there are a few things that need to line up correctly.

So it would be very helpful to understand

1. what the migration will look like
2. what the schema will look like and
3. any other steps involved like Postgrex.types.define

in loading and using the ltree extension or other Postgrex extensions in a Phoenix

Thanks in advance!

[ibarch]

You can start as simple as the following.
Create a migration file:

defmodule MyApp.Repo.Migrations.CreateProducts do
  use Ecto.Migration
  @tbl "products"

  def change do
    execute(
      "CREATE EXTENSION ltree",
      "DROP EXTENSION ltree"
    )

    create table(@tbl) do
      add :category, :ltree, null: false
    end

    create index(@tbl, :category, using: "GIST")
  end
end

Create a schema module:

defmodule MyApp.Product do
  use Ecto.Schema

  schema "products" do
    field :category, :string
  end
end

Start using it in your Phoenix context:

defmodule MyApp.Products do
  def list_products(category_ltree) do
    from(p in MyApp.Product,
      where: fragment("? <@ ?", p.category, ^category_ltree)
    )
    |> Repo.all()
  end
end

[sskamal]

Thank you so much @ibarch, got it working with your help. Appreciate it.

I also found a blog post that helped me clarify how Postgrex extensions, Ecto types, migrations and schemas fit together.