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:
To make sure the search remains fast as the table grows, we’re going to index the field using Postgres’ pg_trgm:
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:
This query will compare the search term, and order by similarity. Let’s see it in action:
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: