Text Search

Build fast full-text search functionality into your apps

Introduction

In this Guide you will learn how to make a full-text search for a product catalog inside an ecommerce app. After you've worked through this guide you should be able to do any kind of search in your Thin Backend based products.

Here's an example of a search built with this Guide:

Schema Setup

To implement our above product search, we first need to create a products table with the Schema Designer.

1. Open the Schema Designer
2. Create a new table products with the following columns:
   • name (text)
   • description (text)
   • sku (text)

   CREATE TABLE products (
       id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
       name TEXT NOT NULL,
       description TEXT NOT NULL,
       sku TEXT NOT NULL
   );
   

In the next steps we will add a search that allows us to find products where a keyword appears in either the name, description or sku column.

To be able to search over all these columns in an efficient way, we need to add a column that stores the preprocessed search document.

This new column will be called text_search. It will be of type tsvector, which is the postgres native type for storing text search documents. We want the column to automatically be updated when the name, description or sku of a product changes, so we make the column a generated column. A generated column is automatically updated by postgres, and we can never manually set it's values.

1. Open the Schema Designer
2. Switch to Code Editor

3. Scroll to the definition of CREATE TABLE products and add a column text_search like this:

   CREATE TABLE products (
       id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
       name TEXT NOT NULL,
       description TEXT NOT NULL,
       sku TEXT NOT NULL, -- <-- ADD COMMA AFTER THIS
   
       -- Add this column below:
       text_search TSVECTOR GENERATED ALWAYS AS 
           ( setweight(to_tsvector('english', sku), 'A') ||
             setweight(to_tsvector('english', name), 'B') ||
             setweight(to_tsvector('english', description), 'C')
           ) STORED
   );
   

4. We should also set up an index for the new text_search column, so that search is fast even with many records.

   Scroll to the end of the SQL code and add a new index statement:

   CREATE INDEX products_text_search_index ON products USING gin(text_search);
   

5. Save the new column by clicking the Save button
6. Click on Migrate DB and run a database migration, so that the new column is added to the database.

The text_search columns will now be automatically generated whenever we insert a new record into the products table. It's value is set to the result of this:

setweight(to_tsvector('english', sku), 'A') ||
  setweight(to_tsvector('english', name), 'B') ||
  setweight(to_tsvector('english', description), 'C')

The to_tsvector('english', someValue) turns the passed value into a search document. As we want the sku to rank higher than the name or the description, we use setweight to assign weights accordlingly. The letters 'A', 'B', 'C' or 'D' stand for the weight. 'A' is most weight, and 'D' is least weight.

The || in the expression is the string concat operator, so like "some string" + "some other string" in JavaScript.

Querying the Search

We can now query the text_search column from our frontend:

function ProductSearch() {
    const [searchQuery, setSearchQuery] = useState('');
    const onChange = useCallback(event => setSearchQuery(event.target.value), [ setSearchQuery ]);

    const products = useQuery(query('products').whereTextSearchStartsWith('textSearch', searchQuery));

    return <form>
        <input type="text" className="form-control" value={searchQuery} onChange={onChange}/>

        <div>
            Result: {products?.map(product => <div key={product.id}>{product.name}</div>)}
        </div>
    </form>
}

We use the whereTextSearchStartsWith() function on the query builder to only show products where either the sku, name or description matches our search query.

Whenever you now type something into the text field, it will show all products matching the search query, ranked by how good they match: