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