Postgres Full Text Search in Ecto

By Mitchell Henke on May 13th 2015

Text search is often a core feature of web APIs, and Postgres and Ecto can fill the need without having to add additional technology (such as ElasticSearch or Solr) to the stack. We can lean on Postgres' pg_trgm to provide "good enough" search in early stages of API development.

My example will be on a User model, where we will be searching by username, so we'll set up a simple Ecto Model first:

defmodule User do
  use Ecto.Model

  schema "users" do
    field :username, :string
  end
end

To make sure the search remains fast as the table grows, we're going to index the field using Postgres' pg_trgm:

defmodule Repo.Migrations.UserSearch do
  use Ecto.Migration

  def up do
    execute "CREATE extension if not exists pg_trgm;"
    execute "CREATE INDEX users_username_trgm_index ON users USING gin (username gin_trgm_ops);"
  end

  def down do
    execute "DROP INDEX users_username_trgm_index;"
  end
end

Inspired by Drew Olson's post, we'll give the Ecto Model a function so we can easily compose search queries. Ecto doesn't have any shorthand for a trigram query over a text column, but using Ecto fragments is straightforward enough:

defmodule User do
  use Ecto.Model

  schema "users" do
    field :username, :string
  end

  def search(query, search_term) do
    from(u in query,
    where: fragment("? % ?", u.username, ^search_term),
    order_by: fragment("similarity(?, ?) DESC", u.username, ^search_term))
  end
end

This query will compare the search term, and order by similarity. Let's see it in action:

User |> User.search("mitch") |> Repo.all
# [debug] SELECT u0."id", u0."username" FROM "users" AS u0 WHERE (u0."name" % $1) ORDER BY similarity(u0."name", $2) DESC ["mitch", "mitch"] (1.9ms)
[%User{__meta__: %Ecto.Schema.Metadata{source: "users", state: :loaded},
  username: "mitch"},
 %User{__meta__: %Ecto.Schema.Metadata{source: "users", state: :loaded},
username: "mitch3"}]

We've been building more APIs in Elixir and Phoenix, and there have been questions on the Ecto mailing list about doing full text search in Ecto, so I thought I'd write up how we've done it. Hopefully it was helpful :)

Feel free to contact me on Twitter at @mitchellhenke or IRC in # elixir-lang with the same name.

ADDENDUM:

Someone on Reddit asked how to change the limit of the % operator in Postgres, which I've usually done via the set_limit() function on each connection, since it resets to the default on new connections.

Ecto doesn't have an after connect hook (created an issue here), and the user needed to be able to set the limit per query. A solution to this is to use the similarity function for both the filter and the order:

defmodule User do
  use Ecto.Model

  schema "users" do
    field :username, :string
  end

  def search(query, search_term, limit = 0.3) do
    from(u in query,
    where: fragment("similarity(?, ?) > ?", u.username, ^search_term, ^limit),
    order_by: fragment("similarity(?, ?) DESC", u.username, ^search_term))
  end
end

RokkinCat

is a software engineering agency. We build applications and teach businesses how to use new technologies like machine learning and virtual reality. We write about software, business, and business software culture.