Full-Text Search: A Practical Guide for Node.js, TypeORM & PostgreSQL

Full-text search is a technique used to analyze strings, documents, or entire database columns to deliver flexible and intelligent search results. Instead of relying on string matching, full-text search looks at context, synonyms, and word variations.

So while a regular SQL query might match only "run", a full-text search can also match "running", "ran", or "runner" depending on the configuration.

Full-text search is supported by many databases including PostgreSQL, MySQL, MongoDB, and specialized engines like Elastic-search and Meilisearch.

In this article, we focus on PostgreSQL full-text search using TypeORM + Node.js.

Benefits of full-text search

1. Search Large Bodies of text quickly: If you have large text fields, like blog posts, recipes, product descriptions, or comments, full-text search lets you efficiently check which documents contain certain keywords.

   Example: Search for "chicken" → return all recipes that mention chicken anywhere in the text.

2. Better and more flexible search results: Full-text search handles imperfections in queries:

   • Typing "run shoes" can still match "running shoes"
   • Searching "comput" can match "computer" and "computing"

   This dramatically improves user experience on e-commerce, blogs, dashboards, and enterprise search tools.

When to use full-text search?

1. You want flexibility, not exact matches; Searching products, users, notes, events, or blog posts where partial matches are okay.
2. When you want to index large bodies of text and return results based on if a certain word or keyword is contained within that body of text ( returning a list of recipes that contain chicken).

Setting up full-text search in typeorm + postgres + nodejs

Below is an example using a simple Recipe entity that supports searching on both the name and description fields.

1. Create the Recipe Entity

@Entity()
export class Recipe {
    @PrimaryGeneratedColumn()
    id: number;

    @Index({fulltext: true})
    @Column("varchar")
    name: string;

    @Index({fulltext: true})
    @Column("varchar")
    description: string;
}

1. Query the database using a the typeorm query-builder to return data based on the provided search query

async function searchRecipes(searchQuery: string) {
  return dataSource
    .getRepository(Recipe)
    .createQueryBuilder("recipe")
    .where(`
      to_tsvector('english', recipe.name || ' ' || recipe.description)
      @@ plainto_tsquery('english', :query)
    `)
    .setParameter("query", searchQuery)
    .getMany();
}

In raw SQL this would look something like this

SELECT *
FROM recipe 
WHERE to_tsvector(name || " " || description) @@ to_tsquery("Chicken");

This will then return all the recipes with chicken in it’s description or name

Comments

[Prudent Bird]

very detailed!

[Oluwatobi O]

Nice read

[OritseWeyinmi Samuel Tuoyo]

Nice read