Adding Trigram Searching to a Clojure Webapp with YeSQL, Migratus, and Postgres
Posted on October 29, 2017 (Last modified on October 23, 2022 )
3 minutes • 524 words
Recently on Porios, I added the ability to do fuzzy text searches with Trigrams. Porios is powered by a Clojure API (a dated Luminus template, actually) which uses YeSQL, Postgres, Compojure and several other libraries. Let’s talk about implementing a trigram search for this application components.
What is a Trigram?
A Trigram is a three character subsection of a string of text. This allows you to match text if you’re close to the actual text you’re looking for. For example, the string foobar can be represented as the following trigrams: foo, oob, oba, bar. With trigram searching, “Grammy” could match one of the popular podcasts we have on the site called “Grammar Girl.” This will help users find content when they don’t exactly know what they’re looking for.
Adding the pg_trgm extension to Postgres
The pg_trgm (postgres_trigram) extension is shipped with most modern versions of postgres so all we need to do is enable it. Since we’re using Migratus, we simply can create a migration to create the extension.
lein migratus create add_pg_trgm_ext
This will create a new up and down migration for us. For our up, add the following text:
CREATE EXTENSION pg_trgm;
In the down migration, you can add:
DROP EXTENSION pg_trgm;
Adding Indexes to existing tables
Now we’ll need to create some indexes to use trigrams. Let’s assume we have a users table with the columns first_name and last_name. You can add some indexes for these columns with another new migration.
lein migratus create add_users_first_name_and_last_name_indexes
In the newly generated .up file, you’ll want to add the following:
CREATE INDEX users_on_last_name_indx ON users USING GIN(last_name gin_trgm_ops);
CREATE INDEX users_on_first_name_indx ON users USING GIN(first_name gin_trgm_ops);
This creates an index using the trigram extension that we just added. There are four different types of index types using the trigram extension, but for our example let’s just use GIN. You can read more about the index types in the documentation for the pg_trgm extension.
In the respective .down file, you’ll want to add the following to remove the indexes.
DROP INDEX users_on_last_name_indx;
DROP INDEX users_on_first_name_indx;
Now we can move up to setting up queries!
YeSQL Queries
Every project is different, but I have a file for queries relative to the data structures that we’re producing. For example: users.queries.sql is a sql file containing queries to the Users table and generating a Users data-map.
We’ll want to add our search functionality to users.queries.sql. Following the specific format for YeSQL, you’ll want a comment giving a binding-function name before the SQL. You can also add a doc-comment.
-- name: search-users-by-name
-- uses pg_trgm to search users by first or last name
SELECT * FROM users
WHERE
lower(first_name) % lower(:name)
or lower(last_name) % lower(:name)
Keep in mind YeSQL uses the keyword syntax for placeholders that will be passed in on function call.
Using the generated function
In our code, we can import the namespace generated by YeSQL and use our new function.
(defqueries "resources/queries/users.queries.sql"
{:connection db-spec})
(search-users-by-name {:name "Grammy"})
And that should work! We’ll be able to see our “Grammar Girl” podcast like we’d expect. Are you using YeSQL or Migratus in your app? Let me know below!
