PostgreSQL Full-Text Search with Node.js and Sequelize

PostgreSQL is a robust and versatile relational database system. One powerful feature it provides is full-text search, which allows you to search for words and phrases in your text. When combined with Node.js and the ORM Sequelize, you can develop applications that leverage these capabilities efficiently.

In this tutorial, we will walk through setting up a Node.js application using Sequelize that interacts with a PostgreSQL database to perform full-text searches. We will use the example of a blog application.

Setting Up Your Environment

First, you need a PostgreSQL database and a Node.js environment. Ensure PostgreSQL is installed and running on your machine. Then, create a database for our application. For this example, blogdb will be the name of our database.

CREATE DATABASE blogdb;

Next, ensure Node.js is installed on your machine and set up a new Node.js project with Sequelize.

mkdir blog-search-app
cd blog-search-app
npm init -y
npm install sequelize pg pg-hstore

Configuring Sequelize

After installing the necessary packages, configure Sequelize to connect to your PostgreSQL database. Create a configuration file named config.js in your root directory:

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('blogdb', 'yourusername', 'yourpassword', {
  host: 'localhost',
  dialect: 'postgres'
});

module.exports = { sequelize };

Don't forget to replace yourusername and yourpassword with your actual PostgreSQL credentials.

Defining the Blog Model

We need a model to represent the blog posts. Create a new file named models.js:

const { Sequelize, DataTypes } = require('sequelize');
const { sequelize } = require('./config');

const BlogPost = sequelize.define('BlogPost', {
  title: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false,
  }
});

module.exports = { BlogPost };

Adding Full-Text Search Capabilities

PostgreSQL uses text search vectors for full-text search operations. You can create such a vector in a new column and update it every time a record is modified. We'll create two methods, one to build the vector and another for searching.

ALTER TABLE "BlogPosts" ADD COLUMN "searchVector" tsvector;
CREATE INDEX "blog_search_idx" ON "BlogPosts" USING GIN("searchVector");

In your application, add hooks to execute SQL to update the search vector whenever a blog post changes:

BlogPost.addHook('beforeSave', async (post, options) => {
  const searchText = `${post.title} ${post.content}`;
  await sequelize.query(
    'UPDATE "BlogPosts" SET "searchVector" = to_tsvector(?) WHERE "id" = ?;',
    { replacements: [searchText, post.id] }
  );
});

Implementing Search with Sequelize

Once you have the search vectors set up, you can perform search queries using Sequelize:

async function searchBlogPosts(query) {
  const searchQuery = await sequelize.query(
    'SELECT * FROM "BlogPosts" WHERE "searchVector" @@ to_tsquery(?)',
    { replacements: [query], type: sequelize.QueryTypes.SELECT }
  );
  return searchQuery;
}

You can now call searchBlogPosts with terms you need to find in your blog content.

Testing the Full-Text Search

To finalize our setup, kick off the app by inserting some sample data and querying it:

async function initialize() {
  // Synchronize models
  await sequelize.sync({ force: true });

  // Create sample posts
  await BlogPost.bulkCreate([
    { title: "Introducing Full-Text Search", content: "This is a tutorial..." },
    { title: "Using Full-Text Search in Node", content: "Search efficiently in PostgreSQL..." }
  ]);

  // Perform a search
  const results = await searchBlogPosts('Search & Node');
  console.log(results);
}

initialize();

Run your application with node models.js and inspect the output — the results should contain the blog posts you entered if they match the search query. By following these steps, you will successfully integrate full-text search functionality into your Node.js application using PostgreSQL and Sequelize.