Full Text Search with Hasura GraphQL API and Postgres
Tags: postgres, search, graphql
Published: 2019-12-18 12:37:21

---
title: Full Text Search with Hasura GraphQL API and Postgres
published: true
date: 2019-11-26 06:25:00 UTC
tags: postgres, search, graphql
canonical_url: https://blog.hasura.io/full-text-search-with-hasura-graphql-api-postgres/
---

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](https://raw.githubusercontent.com/xivSolutions/ChinookDb_Pg_Modified/pg_names/chinook_pg_serial_pk_proper_naming.sql) 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](https://www.postgresql.org/docs/current/pgtrgm.html) extension in Postgres to implement this. This module provides [functions](https://www.postgresql.org/docs/current/pgtrgm.html#id-1.11.7.40.5) 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`.

![](https://blog.hasura.io/content/images/2019/11/tracks-user-permission.png)

### **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](https://docs.hasura.io/1.0/graphql/manual/queries/custom-functions.html#example-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.