Hasura for Hasura

Posted on Dec 18, 2019 • Originally published at blog.hasura.io on Nov 26, 2019

Full Text Search with Hasura GraphQL API and Postgres

#postgres #search #graphql

In this post, you will look at how to implement full text search with Hasura GraphQL API, leveraging some of the Postgres features and it's support for search.

There are different ways to approach full text search with Postgres. The requirements for search could range from searching a single column, multiple columns or across tables, which can potentially be achieved using views.

I have taken the chinook database schema with sample data to demonstrate the examples. You can apply the above sql to your Postgres instance connected to Hasura.

LIKE and ILIKE operator

The first easy approach is to use the LIKE or ILIKE operator in a where clause. Both these operators are used for pattern matching in Postgres. The difference is that ILIKE does case insensitive pattern matching.

There are a couple of important selectors associated with this. The % selector and _ selector.

% sign is used to pattern match any sequence of zero or more characters.
_ sign is used to match any single character.

Now, let's look at the use cases one by one.

Simple search on one column

Consider the following query:

query {
  tracks(where: {name: {_ilike: "%wall%"}}) {
    id
    name
    album {
      id
      title
    }
  }
}

The above query returns the list of tracks which contains the substring wall in the column name. The response looks like the following:

id	name
2	Balls to the Wall
147	Blood In The Wall
151	Behind The Wall Of Sleep
1780	It's A Bitter Pill To Swallow
2538	Wall Of Denial
3373	Four Walled World

Remember this uses the where clause with ilike usage as %wall%. Let's look at the different usage of the ilike operator.

%wall% - Find all occurrences of track name containing the substring wall
wall% - Find all occurrences of track name starting with wall
%wall - Find all occurrences of track name ending with wall
_wall_ - Find all occurrences of track name with a 6 character word having wall

You can also combine both these selectors to apply a match.

Searching on multiple columns

Searching multiple columns can be done via simple boolean expressions in the where clause. We can specify that the term wall should be either matched with field name or the album title using the _or operator.

{
  tracks(where: {_or: [{name: {_ilike: "%wall%"}}, {album: {title: {_ilike: "%wall%"}}}]}) {
    id
    name
  }
}

Note that we are making a pattern match across a relationship called album. We can apply the same for multiple columns too.

Built-in Full Text Search

Now consider the following SQL with tsvector and tsquery

select id, name from tracks where to_tsvector(name) @@ to_tsquery('wall')

This will return a response like the one below:

id	name
2	Balls to the Wall
147	Blood In The Wall
151	Behind The Wall Of Sleep
2538	Wall Of Denial
3373	Four Walled World

This is built-in Postgres full text search that returns documents matching a search query of stemmed words. But this doesn't account for mis-spelling.

Fuzzy Search

In the above examples, notice that the results do not have any order with respect to matching the name. There is no ranking for this search to give more relevant results. This query also scans through all rows in the table to find the results, making it extremely slow.

Let's say you want to fuzzy match the search terms to column data to accommodate typos or similar results.

We will make use of the pg_trgm extension in Postgres to implement this. This module provides functions and operators to determine similarity of alphanumeric text.

Let's get started by creating the extension:

CREATE EXTENSION pg_trgm;

We need to also setup index

CREATE INDEX tracks_name_gin_idx ON tracks
USING GIN ((name) gin_trgm_ops);

Let's try executing the following SQL query to see what results we get. Note that the search term we use here is wals, a typo of wall.

SELECT id, name 
FROM tracks 
WHERE 'wals' <% ( name ) 
ORDER BY Similarity('wals', ( name )) DESC 
LIMIT 5;

The result will look like:

id	name
601	Walkin'
2941	Walk On
2868	Walkabout
2	Balls to the Wall
10	Evil Walks

Search using Postgres Functions

The above SQL cannot be directly queried using GraphQL. Hence, we create a Postgres function to implement fuzzy search.

CREATE FUNCTION search_tracks(search text) 
returns setof tracks AS $ 
SELECT * 
FROM tracks 
WHERE search <% ( NAME ) 
ORDER BY similarity(search, ( NAME )) DESC limit 5; 

$ language sql stable;

Once this function is created and tracked, you can make the following GraphQL query:

query {
  search_tracks(args: {search: "wall"}) {
    id
    name
  }
}

This above input argument args -> search can even have typo terms which would give back similar results.

Access Control for Search

There might be a use case to allow this search functionality to only logged in users of the app. This restriction can be applied using Hasura's role based permission system.

Table permissions

For example, under tracks table, we can apply the following select permission for role user.

Function permissions

Once the permission for the table is defined, it will be applied to the function which uses it underneath for querying.

Read more on different example on our Docs on Fuzzy Match Search Functions.

This is a series of posts demonstrating the possibilities of Searching with Hasura GraphQL. In the next post, we will take a look at how to integrate ZomboDB extension to perform search with Elasticsearch for more advanced use cases.