Postgresql full text search with TypeOrm

Question

There is some way to handle full text search with Postgres and TypeOrm. I've seen some examples but they only work with Mysql. How can I get the equivalent of this but with Postgresql?

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: string;

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

And use query builder:

const searchTerm = "John";

const result = await connection.manager.getRepository(User)
            .createQueryBuilder()
            .select()
            .where(`MATCH(name) AGAINST ('${searchTerm}' IN BOOLEAN MODE)`)
            .getMany();

Answer 1

Here's an alternative using tsvector, where query is the input string.

      const songs = await getManager()
        .createQueryBuilder()
        .select('song')
        .from(Models.Song, 'song')
        .where(
          'to_tsvector(song.title) @@ to_tsquery(:query)',
          { query }
        )
        .getMany();

If you wish to include stop words (You, A, etc), and want partial matching.

      const songs = await getManager()
        .createQueryBuilder()
        .select('song')
        .from(Models.Song, 'song')
        .where(
          `to_tsvector('simple',song.title) @@ to_tsquery('simple', :query)`,
          { query: `${query}:*` }
        )
        .getMany();

If you also want to allow for multi word strings (with spaces).

      const formattedQuery = query.trim().replace(/ /g, ' & ');
      const songs = await getManager()
        .createQueryBuilder()
        .select('song')
        .from(Models.Song, 'song')
        .where(
          `to_tsvector('simple',song.title) @@ to_tsquery('simple', :query)`,
          { query: `${formattedQuery}:*` }
        )
        .getMany();

Answer 2

For Full-Text Search (FTS), I would recommend you to use PostgreSQL specific functions on the WHERE clause. The LIKE & ILIKE operators are too simple and too slow for a Full-Text Search. I suggest you also watch this video. It really explains clearly and easily on how to implement such a feature with PostgreSQL and at the last minutes, it shows how it implements it with Node.js.

I hope that helps! Please let me know if there is anything else I can help you with.