Postgres tsvector with TypeORM

PostgreSQL offers two specific data types that make full-text search easier.
Full-text search is basically the process of scanning through natural-language documents to find the ones that align most closely with a given query.

The tsvector type is used to represent a document in a way that’s fine-tuned for this kind of search.
The tsquery type is used to structure the text query itself.

Using TypeORM in Node.js application gives you nice support of Typescript for ORM models.
Lets see how can we setup PostgreSQL and tsvector with TypeORM

Background

Lets assume we have product and categories tables and corresponding models. We want to search products by product name, ean and category name.

Product model:

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

  @Column({
    type: 'varchar',
  })
  name: string;

  @Column({
    type: 'varchar',
  })
  ean: string;

  @Column({
    type: 'int',
  })
  categoryId: number;
}

Category model:

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

  @Column({
    type: 'varchar',
  })
  name: string;
}

Setup tsvector

We need to create a column in the table where we want to store the search vectors and retrieve search results from.

Add full text search column to Product model

  @Column({
    type: 'tsvector',
    nullable: true,
  })
  fullTextSearch: string;

Create migration

import { MigrationInterface, QueryRunner } from 'typeorm'

export class SearchVector1724418715424 implements MigrationInterface {
  name = 'SearchVector1724418715424'

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "product" ADD "fullTextSearch" tsvector`)

    await queryRunner.query(`
        CREATE INDEX "IDX_fullTextSearch" 
        ON "product" 
        USING GIN ("fullTextSearch");
    `)

    // Populate the fullTextSearch column for existing records
    await queryRunner.query(`
        UPDATE "product" p
        SET "fullTextSearch" = 
            setweight(to_tsvector(coalesce(p."ean", '')), 'A') || 
            setweight(to_tsvector(coalesce(p."name", '')), 'B') ||
            setweight(to_tsvector(coalesce(c."name", '')), 'C')
        FROM "category" c
        WHERE p."categoryId" = c."id";
    `)
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
            DROP INDEX "IDX_fullTextSearch";
        `)
    await queryRunner.query(`ALTER TABLE "product" DROP COLUMN "fullTextSearch"`)
  }
}

Updating fullTextSearch column with vectors

This can be done 2 ways - database trigger and using TypeORM subscribers.

Database trigger - can be added within migration

await queryRunner.query(`
    CREATE OR REPLACE FUNCTION update_fullTextSearch_trigger()
    RETURNS TRIGGER AS $$
    BEGIN
        NEW."fullTextSearch" :=
            setweight(to_tsvector(coalesce(NEW."gtin", '')), 'A') ||
            setweight(to_tsvector(coalesce(NEW."name", '')), 'B') ||
            setweight(to_tsvector(coalesce((SELECT "name" FROM "category" WHERE "id" = NEW."categoryId"), '')), 'C');
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;

    CREATE TRIGGER "update_fullTextSearch"
    BEFORE INSERT OR UPDATE ON "product"
    FOR EACH ROW EXECUTE FUNCTION update_fullTextSearch_trigger();
`);

TypeORM subscribers

Using TypeORM subscribers offers a more controlled and maintainable way to update the search vector. This approach integrates directly with your application code, making it easier to manage, test, and version-control the logic.

Create subscriber class

@EventSubscriber()
export class ProductSubscriber implements EntitySubscriberInterface<Product> {
  listenTo() {
    return Product
  }

  async afterInsert(event: InsertEvent<Product>) {
    await this.updateSearchVector(event)
  }

  async afterUpdate(event: UpdateEvent<Product>) {
    await this.updateSearchVector(event)
  }

  private async updateSearchVector(event: UpdateEvent<Product> | InsertEvent<Product>) {
    if (!event.entity) return
    await event.manager.query(
      `
        UPDATE product
        SET "fullTextSearch" = 
            setweight(to_tsvector(coalesce(product.gtin, '')), 'A') || 
            setweight(to_tsvector(coalesce(product.name, '')), 'B') || 
            setweight(to_tsvector(coalesce(category.name, '')), 'C')
        FROM category
        WHERE product."categoryId" = category.id
        AND product.id = $1
        `,
      [event.entity.id],
    )
  }
}

Add subscriber to TypeORM DataSource

{
    type: 'postgres',
    host: process.env.DB_HOST,
    port: process.env.DB_PORT as number | undefined,
    username: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    entities: [
      Category,
      Product,
    ],
    subscribers: [ProductSubscriber],
  }

Apply migration to your database

Now we all set to use tsvector for full text search!

Usage

The usage is very straightforward with tsquery:

const queryBuilder = this.createQueryBuilder('product')

queryBuilder.where('product.fullTextSearch @@ plainto_tsquery(:value)', { value })


const result = await queryBuilder.getManyAndCount()

Summary

In this article, we’ve demonstrated how to integrate PostgreSQL’s full-text search capabilities using the tsvector and tsquery data types within a Node.js application using TypeORM. We walked through setting up the tsvector column in the Product model, creating a migration to populate and index this column, and explored methods to keep the search vector updated—whether through a database trigger or a TypeORM subscriber. By the end, you can now efficiently search across multiple fields like product name, EAN, and category name using full-text search, making it a powerful tool for optimizing search functionality in your applications.