Back to Scaling Node.js Applications guides

Full‑Text Search in Postgres with TypeScript

Bun PostgreSQL Drizzle
Jakub Arbet
Updated on June 2, 2025

Introduction

Full-text search powers everything from e-commerce product discovery to documentation sites. While many developers reach for complete solutions like Algolia or Elasticsearch, PostgreSQL's built-in full-text search delivers millisecond response times on millions of documents with zero additional infrastructure. We'll build a complete search solution using Bun, Drizzle ORM, and PostgreSQL's tsvector to show you how simple yet powerful database-native search can be.

Prerequisites

Before we dive in, ensure you have the following set up:

  • Bun runtime installed (Bun) - see our Introduction to Bun
  • PostgreSQL database running and accessible. You'll need at least version 12 to support all features used in this guide.
  • Basic knowledge of PostgreSQL and JavaScript/TypeScript

Project setup

Let's start by initializing our project and setting up the basic structure.

Initialize the project with Bun:

 
mkdir article-search && cd article-search
 
bun init -y

Feel free to delete the default index.ts file - we won't use it:

 
rm index.ts

Now install dependencies:

 
bun add drizzle-orm
 
bun add -d @types/bun drizzle-kit pg

Create a .env file with your PostgreSQL connection string. Note that Bun automatically loads .env files, so we don't need the dotenv package.

.env
DATABASE_URL=postgres://username:password@localhost:5432/article_search

If you don't have a PostgreSQL database up and running, you can use the following command to start a new PostgreSQL database using Docker:

Optional - if you don't already have PostgreSQL up & running
docker run -d --name postgres-article-search -e POSTGRES_USER=username -e POSTGRES_PASSWORD=password -e POSTGRES_DB=article_search -p 5432:5432 postgres:latest

Now let's set up our database connection with Drizzle:

src/db/index.ts
import { drizzle } from 'drizzle-orm/bun-sql';

export const db = drizzle(process.env.DATABASE_URL!);

Creating the Basic Schema

We'll start with a simple articles table that we can use to demonstrate search capabilities:

src/db/schema.ts
import { pgTable, serial, timestamp, text } from 'drizzle-orm/pg-core';

export const articlesTable = pgTable('articles', {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    body: text('body'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
    // We'll add our search-optimized column here soon!
});

Create the Drizzle configuration file:

drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Now generate the migration:

 
bun drizzle-kit generate

And run the migration:

 
bun drizzle-kit migrate

If you would like to learn more about Drizzle, better understand the schema and configuration we just used, and the migrations, see our Getting started with Drizzle ORM guide.

Seeding Sample Data

Let's create some sample articles to work with, feel free to add your own examples:

src/db/seed.ts
import { db } from './index';
import { articlesTable } from './schema';

const sampleArticles = [
  {
    title: "Better Stack: Complete Observability for Modern Applications",
    body: "Better Stack provides comprehensive logging, uptime monitoring, and incident management. With Better Stack's advanced log management, you can centralize logs from all your applications, set up intelligent alerts, and debug issues faster. The platform excels at log aggregation, structured logging, and real-time log analysis, making it essential for maintaining application reliability and performance monitoring."
  },
  {
    title: "Getting Started with PostgreSQL Full-Text Search",
    body: "PostgreSQL offers powerful full-text search capabilities built right into the database. Learn how to use tsvector, tsquery, and GIN indexes to implement fast and accurate search functionality without external dependencies."
  },
  {
    title: "TypeScript Best Practices for 2024",
    body: "Modern TypeScript development requires understanding of advanced types, proper configuration, and effective tooling. This comprehensive guide covers everything from basic setup to complex type manipulations and performance optimization."
  },
  {
    title: "Building Scalable APIs with Node.js",
    body: "API design and implementation strategies for high-performance Node.js applications. Topics include authentication, rate limiting, caching strategies, and database optimization techniques for modern web services."
  },
  {
    title: "Database Performance Optimization Techniques",
    body: "Comprehensive guide to optimizing database performance including indexing strategies, query optimization, connection pooling, and monitoring. Learn how to identify bottlenecks and improve application response times."
  },
  {
    title: "Modern JavaScript Framework Comparison",
    body: "Detailed analysis of React, Vue, Angular, and Svelte. Compare performance characteristics, developer experience, ecosystem maturity, and use case recommendations for each framework in 2024."
  }
];

async function seed() {
  console.log('🌱 Seeding database...');

  try {
    await db.insert(articlesTable).values(sampleArticles);
    console.log('✅ Database seeded successfully!');
  } catch (error) {
    console.error('❌ Error seeding database:', error);
    process.exit(1);
  }
}

seed();

Run the seed script:

 
bun run src/db/seed.ts

And you should see output like this:

Running the seed script

Building a Simple CLI Search App

Let's create a basic CLI application to demonstrate our search functionality. We'll start with a simple prompt-based interface:

src/index.ts
import { db } from './db/index';
import { articlesTable } from './db/schema';
import { ilike, or } from 'drizzle-orm';

async function searchArticlesLike(query: string) {
  const searchTerm = `%${query}%`;

  const startTime = performance.now();

  const results = await db
    .select()
    .from(articlesTable)
    .where(
      or(
        ilike(articlesTable.title, searchTerm),
        ilike(articlesTable.body, searchTerm)
      )
    )
    .limit(10);

  const endTime = performance.now();
  const searchTime = (endTime - startTime).toFixed(2);

  return { results, searchTime };
}

function displayLikeResults(results: any[], searchTime: string, query: string) {
  console.log(`\n🔍 ILIKE search results for "${query}" (${searchTime}ms):`);
  console.log('─'.repeat(60));

  if (results.length === 0) {
    console.log('No articles found.');
    return;
  }

  results.forEach((article, index) => {
    console.log(`\n${index + 1}. ${article.title}`);
    console.log(`   ${article.body.substring(0, 120)}...`);
    console.log(`   ID: ${article.id} | Created: ${article.createdAt.toDateString()}`);
  });
}

async function main() {
  console.log('🚀 Article Search CLI');
  console.log('Compare ILIKE vs Full-Text Search performance');

  while (true) {
    const query = prompt('\n📝 Enter search query (or "exit" to quit): ');

    if (!query || query.toLowerCase() === 'exit') {
      console.log('👋 Goodbye!');
      break;
    }

    try {
      const likeResults = await searchArticlesLike(query);
      displayLikeResults(likeResults.results, likeResults.searchTime, query);
    } catch (error) {
      console.error('❌ Search error:', error);
    }
  }

  process.exit(0);
}

main();

There's nothing complex going on, just simple search using the built-in ILIKE wildcard matching, and a CLI around it. We can test it out by running:

 
bun run src/index.ts

Try searching for terms like "logging", "Better Stack", "PostgreSQL", or "TypeScript" to see the ILIKE-based search in action.

Testing out simple ILIKE search

Understanding ILIKE Query Limitations

While our basic search works, ILIKE queries have significant limitations:

Performance Issues: ILIKE queries with wildcards can't use standard indexes effectively, leading to full table scans on larger datasets.

Limited Matching: ILIKE requires exact substring matches and doesn't understand word variations (e.g., "optimize" won't match "optimization" from our database articles).

Searching for "optimize" results in no articles

No Relevance Ranking: Results appear in database order rather than relevance to the search query.

Introducing PostgreSQL tsvector

PostgreSQL's tsvector (text search vector) solves these limitations by preprocessing text into a normalized, searchable format. It handles:

  • Stemming: Reduces words to their base forms ("logging" → "log")
  • Stop word removal: Eliminates common words like "the", "and", "is"
  • Language awareness: Understands linguistic rules for better matching
  • Ranking: Provides relevance scores for search results

Let's enhance our schema to include full-text search capabilities using PostgreSQL's generated columns:

src/db/schema.ts
import { pgTable, serial, timestamp, text, index, customType } from 'drizzle-orm/pg-core';


import { SQL, sql } from 'drizzle-orm';




// Custom tsvector type for Drizzle


export const tsvector = customType<{


  data: string;


}>({


  dataType() {


    return 'tsvector';


  },


});


export const articlesTable = pgTable('articles', {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    body: text('body'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),

    // Search vector combining title and body with different weights


    searchVector: tsvector('search_vector')


      .notNull()


      .generatedAlwaysAs((): SQL => 


        sql`setweight(to_tsvector('english', ${articlesTable.title}), 'A') || 


            setweight(to_tsvector('english', coalesce(${articlesTable.body}, '')), 'B')`


      ),


}, (table) => [


  // GIN index for fast full-text search


  index('articles_search_idx').using('gin', table.searchVector),


]);

Let's break down what's happening in our enhanced schema:

to_tsvector('english', text) converts raw text into a tsvector by applying language-specific rules for stemming, stop word removal, and normalization. The 'english' configuration handles common English language processing. Learn more about text search configurations.

setweight(tsvector, weight) assigns importance levels to different parts of your content. Weights range from 'A' (highest) to 'D' (lowest), allowing you to prioritize title matches over body content in search rankings. Read about weight assignment.

Generated columns automatically maintain the search vector whenever the source columns change, eliminating synchronization issues between your content and search index. This PostgreSQL 12+ feature ensures your search data stays current without manual triggers.

GIN indexes (Generalized Inverted Index) are specifically designed for full-text search, providing fast lookups by creating an inverted index of all lexemes. They're typically 3x faster than GiST indexes for search operations but require more storage space. Understanding GIN indexes.

Now that we understand what our new schema does, let's generate and run the migration to update your database:

 
bun drizzle-kit generate && bun drizzle-kit migrate

And then let's update our CLI to use both search methods at the same time and compare the results:

src/index.ts
import { db } from './db/index';
import { articlesTable } from './db/schema';

import { ilike, or, sql } from 'drizzle-orm';


async function searchArticlesLike(query: string) {
  // Remains the same
}


async function searchArticlesFTS(query: string) {


  const startTime = performance.now();




  const processedQuery = sql`to_tsquery('english', ${query.replace(/\s+/g, ' & ')})`;




  const results = await db


    .select({


      id: articlesTable.id,


      title: articlesTable.title,


      body: articlesTable.body,


      createdAt: articlesTable.createdAt,


      // Calculate relevance rank


      rank: sql<number>`ts_rank(${articlesTable.searchVector}, ${processedQuery}) as rank`,


      // Generate highlighted snippet


      snippet: sql<string>`ts_headline('english', ${articlesTable.body}, ${processedQuery}, 'StartSel=\x1b[1m, StopSel=\x1b[0m, MaxWords=30, MinWords=10')`,


    })


    .from(articlesTable)


    .where(sql`${articlesTable.searchVector} @@ ${processedQuery}`)


    .orderBy(sql`rank DESC`)


    .limit(10);




  const endTime = performance.now();


  const searchTime = (endTime - startTime).toFixed(2);




  return { results, searchTime };


}


function displayLikeResults(results: any[], searchTime: string, query: string) {
  // Remains the same
}


function displayFTSResults(results: any[], searchTime: string, query: string) {


  console.log(`\n🚀 Full-text search results for "${query}" (${searchTime}ms):`);


  console.log('─'.repeat(60));




  if (results.length === 0) {


    console.log('No articles found.');


    return;


  }




  results.forEach((article, index) => {


    console.log(`\n${index + 1}. ${article.title} (Rank: ${article.rank.toFixed(3)})`);


    console.log(`   ${article.snippet}`);


    console.log(`   ID: ${article.id} | Created: ${article.createdAt.toDateString()}`);


  });


}


async function main() {
  console.log('🚀 Article Search CLI - Compare LIKE vs Full-Text Search');

  while (true) {
    const query = prompt('\n📝 Enter search query (or "exit" to quit): ');

    if (!query || query.toLowerCase() === 'exit') {
      console.log('👋 Goodbye!');
      break;
    }

    try {

      // Execute both searches


      const likeResults = await searchArticlesLike(query);


      const ftsResults = await searchArticlesFTS(query);




      // Display results from both methods


      displayLikeResults(likeResults.results, likeResults.searchTime, query);


      displayFTSResults(ftsResults.results, ftsResults.searchTime, query);

    } catch (error) {
      console.error('❌ Search error:', error);
    }
  }

  process.exit(0);
}

main();

In searchArticlesFTS we are using some new concepts around FTS in PostgreSQL, let's break those down:

Query Processing: The query.replace(/\s+/g, ' & ') transforms user input into PostgreSQL's tsquery format, converting spaces to & operators for AND searches. You can learn more at Parsing queries

 
const processedQuery = sql`to_tsquery('english', ${query.replace(/\s+/g, ' & ')})`;

Match Filtering: The @@ operator, we use in the where clause, is PostgreSQL's text search match operator. It returns true when the tsvector contains all the terms specified in the tsquery.

 
where(sql`${articlesTable.searchVector} @@ ${processedQuery}`)

Relevance Ranking: ts_rank() calculates how well each document matches the search query, returning a float value where higher numbers indicate better matches. The ranking considers term frequency, document length, and the weights we assigned during tsvector creation. You can learn more at Ranking Search Queries

 
sql<number>`ts_rank(${articlesTable.searchVector}, ${processedQuery}) as rank`

Snippet Generation: ts_headline() creates context-aware excerpts with highlighted search terms. This function finds the parts of the text most relevant to the search query and creates a snippet with highlighted matches using terminal bold codes. The MaxWords and MinWords parameters control snippet length. You can learn more at Highlighting Results

 
sql<string>`ts_headline('english', ${articlesTable.body}, ${processedQuery}, 'StartSel=\x1b[1m, StopSel=\x1b[0m, MaxWords=30, MinWords=10')`

Comparing Search Methods

With the updated CLI we can now compare both of the approaches at the same time. We can start our search CLI:

 
bun run src/index.ts

And then try the following comparisons:

  • Search for "optimize":
    • ILIKE search: no results (doesn't match "optimization" in our database article)
    • Full-text search: finds the "Database Performance Optimization Techniques" article (understands stemming)

Search for optimize

  • Search for "application monitor":
    • ILIKE search: requires the exact phrase to appear
    • Full-text search: finds relevant articles even if words are separated

Search for application monitoring

  • Search for "postgres":
    • ILIKE search: finds the result containing "PostgreSQL" in the title
    • Full-text search: no results, as the English dictionary doesn't recognize "postgres" as a stem of "PostgreSQL", treating them as completely different terms

carbon (13).png

The side-by-side comparison shows that while full-text search is generally superior, the English text search configuration has limitations with technical terms, brand names, and domain-specific vocabulary. When the dictionary doesn't recognize word relationships (like "postgres" and "PostgreSQL"), full-text search can miss relevant results that ILIKE would find. This highlights the importance of choosing the right search approach based on your content and user search patterns.

Advanced tsvector Features

PostgreSQL's full-text search offers sophisticated features beyond basic matching:

Phrase Searches: Use <-> operator for exact phrase matching:

 
-- Find "Better Stack" as an exact phrase
SELECT * FROM articles 
WHERE search_vector @@ phraseto_tsquery('english', 'Better Stack');

Proximity Searches: Find words within N positions of each other:

 
-- Find "application" within 3 words of "monitoring"
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('english', 'application <3> monitoring');

Boolean Operations: Complex search logic with AND, OR, NOT:

 
-- Find articles about logging but not performance
SELECT * FROM articles 
WHERE search_vector @@ to_tsquery('english', 'logging & !performance');

As an exercise you can extend the CLI to support these advanced features by modifying the query processing logic.

Performance Considerations

The performance difference between LIKE and tsvector becomes dramatic with larger datasets:

  • Small datasets (< 10k records): Both methods perform reasonably well
  • Medium datasets (10k-100k records): tsvector shows 5-10x improvement
  • Large datasets (> 100k records): tsvector can be 50x faster with proper indexing

The GIN index on tsvector enables these performance gains by creating an inverted index structure optimized for text search operations.

Final Thoughts

PostgreSQL's built-in full-text search using tsvector provides a powerful alternative to external search engines for many applications. The combination of linguistic intelligence, relevance ranking, and excellent performance makes it an attractive choice for implementing search functionality.

Key advantages include simplified architecture (no additional services), ACID compliance with your main database, and sophisticated search features that rival dedicated search engines. For applications that don't require features like faceted search or complex aggregations, PostgreSQL's full-text search often provides everything needed with significantly less operational complexity.

The code examples in this guide provide a solid foundation for implementing full-text search in your own applications. For more advanced features and configuration options, consult the PostgreSQL documentation on full-text search.

Thanks for reading, and happy searching!

Got an article suggestion? Let us know
Next article
Prevent Bad Commits with Husky and lint-staged
Learn how to set up Husky and lint-staged to automatically format code, catch errors, and enforce consistent commits in your Node.js projects.
→
Licensed under CC-BY-NC-SA

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.