Using PostgreSQL Full-Text Search for Powerful Querying in Node.js Apps

PostgreSQL offers powerful built-in full-text search capabilities that let you build sophisticated search experiences without relying on external engines like Elasticsearch. In this article, we’ll integrate full-text search into a Node.js app using a PostgreSQL database.

Step 1: Create a Table With Searchable Text

We’ll begin by creating a simple articles table with a column for the title and body text.

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  tsv tsvector
);

We’ll also create a trigger to auto-update the tsv column:

CREATE FUNCTION update_tsv() RETURNS trigger AS $$
BEGIN
  NEW.tsv :=
    setweight(to_tsvector('english', NEW.title), 'A') ||
    setweight(to_tsvector('english', NEW.body), 'B');
  RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER tsvupdate BEFORE INSERT OR UPDATE
  ON articles FOR EACH ROW EXECUTE FUNCTION update_tsv();

Step 2: Set Up the Node.js App

mkdir pg-fulltext-search
cd pg-fulltext-search
npm init -y
npm install pg express

Step 3: Add a Search Route

// index.js
const express = require("express");
const { Pool } = require("pg");

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const app = express();
const PORT = 3000;

app.get("/search", async (req, res) => {
  const { q } = req.query;
  if (!q) return res.status(400).send("Query required");

  try {
    const result = await pool.query(
      `SELECT id, title, ts_rank(tsv, query) AS rank
       FROM articles, to_tsquery($1) query
       WHERE tsv @@ query
       ORDER BY rank DESC
       LIMIT 10`,
      [q.replace(/\s+/g, " & ")]
    );
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send("Error searching articles");
  }
});

app.listen(PORT, () => console.log(`Server running on http://localhost:${PORT}`));

Step 4: Add Sample Data and Test

Insert some articles and test your search endpoint by navigating to /search?q=your+query.

INSERT INTO articles (title, body) VALUES
('Introduction to Node.js', 'Learn the basics of Node.js'),
('Advanced PostgreSQL Search', 'Implement full-text search with ranking');

Conclusion

With just a few SQL functions and a bit of setup, PostgreSQL's full-text search lets you build powerful search functionality directly into your Node.js apps — no external service required.

If this post helped you, consider supporting me: buymeacoffee.com/hexshift