How to do full-text search in Supabase?

 

Step 1: Set Up Supabase Project

 

1. Create a Supabase account or log in: Visit the Supabase website and sign up or log in to your existing account.

2. Create a new project: After logging in, create a new project by clicking the "New Project" button in the dashboard. Provide the necessary details, such as project name, database password, and region.

 

Step 2: Set Up Your Database

 

1. Navigate to the SQL Editor: In the Supabase dashboard, click on the "SQL Editor" tab to access the SQL console.

2. Set up a new table: Create a table where you plan to store the data you want to perform a full-text search on. Use the SQL editor to execute the following SQL command to create a sample table:

   
   CREATE TABLE articles (
     id SERIAL PRIMARY KEY,
     title TEXT,
     content TEXT
   );
   

3. Add data to your table: Insert some sample data into your new table. Here's an example SQL insert statement:

   
   INSERT INTO articles (title, content) 
   VALUES
   ('First Article', 'This is the content of the first article.'),
   ('Second Article', 'Content for the second article goes here.'),
   ('Third Article', 'And this is the third article content.');
   

 

Step 3: Enable Full-Text Search

 

1. Create a tsvector column: In order to take advantage of full-text search in PostgreSQL, you'll need to create a column of type tsvector. Add the following column to your table:

   
   ALTER TABLE articles
   ADD COLUMN tsv tsvector;
   

2. Update the tsvector column: Populate the tsv column with data from your text columns using the to_tsvector function:

   
   UPDATE articles
   SET tsv = to\_tsvector('english', title || ' ' || content);
   

3. Create a trigger to automatically update the tsvector column: Enable automatic updates of the tsv column whenever the data changes:

   
   CREATE OR REPLACE FUNCTION articles_tsvector_update() RETURNS trigger AS $$
   BEGIN
     NEW.tsv :=
       to\_tsvector('english', NEW.title || ' ' || NEW.content);
     RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
   ON articles FOR EACH ROW EXECUTE PROCEDURE articles_tsvector_update();
   

 

Step 4: Perform a Full-Text Search Query

 

1. Use the to_tsquery function: Use the to_tsquery function to perform full-text searches on your data. Here's an example query:

   
   SELECT \* FROM articles
   WHERE tsv @@ to\_tsquery('english', 'content');
   

2. Use the plainto_tsquery function for plain text search: For a more straightforward text search without operators, use plainto_tsquery:

   
   SELECT \* FROM articles
   WHERE tsv @@ plainto\_tsquery('english', 'first article');
   

 

Step 5: Integrate Full-Text Search with Supabase API

 

1. Get your API URL and anon key: In the Supabase dashboard, navigate to the "Settings" section and locate the API details, including the URL and anon key.

2. Set up your client: Integrate the Supabase client into your application using the API URL and anon key. Install the Supabase client library if you haven't already:

   
   npm install @supabase/supabase-js
   

3. Initialize the Supabase client: Initialize the client in your JavaScript application:

   
   import { createClient } from '@supabase/supabase-js';
   
   const supabaseUrl = 'https://your-project.supabase.co';
   const supabaseAnonKey = 'your-anon-key';
   
   const supabase = createClient(supabaseUrl, supabaseAnonKey);
   

4. Query data using full-text search: Use the Supabase client to perform full-text search queries:

   
   const { data, error } = await supabase
     .from('articles')
     .select('\*')
     .filter('tsv', 'fts(to\_tsquery)', 'first & article');
   
   if (error) console.log('Error:', error);
   else console.log('Data:', data);
   

 

Conclusion

 

Congratulations! You have successfully set up and implemented full-text search in Supabase. By following the steps outlined above, you can efficiently search through large datasets using powerful PostgreSQL full-text search capabilities within your Supabase projects. Make sure to explore further customization options, such as modifying search configurations, to better suit your application's needs.