Prerequisites

This article assumes you are familiar with Python. It also assumes that you have read the Supabase documentation and are familiar the with concept of Full Text Search.

We will use the following tools

  • Poetry - we used version 1.8.2 for this article.
  • Supabase CLI - we used version 1.161.0 for this article installed via npx. Alternatively you can use the Supabase Dashboard if you don’t wish to setup the CLI and docker in order to use it.

Setting up project

We will first create out project directory and install all the dependencies with Poetry.

poetry new supabase_full_text_search

Let’s change directory to the one poetry created for us:

cd supabase_full_text_search

Now we can install the python dependencies we will need to create this project:

poetry add supabase python-dotenv

Create a file inside of the supabase_full_text_search directory next to the __init__.py file called main.py.

The project directory setup is now completed from the Python side of things.

Setup Supabase CLI (optional)

We won’t cover installing the CLI here as Supabase has done a good job at this.

Lets initiate the project with the Supabase CLI:

npx supabase init

Creating our table and data set

Below is the SQL to setup a movies table:

create table movies (
  id uuid primary key default gen_random_uuid(),
  title text,
  rating text,
  description text
);

Along with the SQL to insert some data into the movies table:

insert into movies
  (title, rating, description)
values
  (
    'A Rainy Day in New York',
    'PG-13',
    'A young couple arrives in New York for a weekend where they are met with bad weather and a series of adventures.'
  ),
  ('Murder Manual', '18', 'Creepy, terrifying chapters from our book of horror include a little girl''s journey from a world of nightmares into the nightmare of reality, a gay couple''s romantic getaway in Palm Springs...'),
  ('Mean Girls', 'PG-13', 'Cady Heron is a hit with The Plastics, the A-list girl clique at her new school, until she makes the mistake of falling for Aaron Samuels, the ex-boyfriend of alpha Plastic Regina George.'),
  (
    'Jurassic World: Fallen Kingdom',
    'PG-13',
    'When the island''s dormant volcano begins roaring to life, Owen and Claire mount a campaign to rescue the remaining dinosaurs from this extinction-level event.'
  ),
  (
    'The Avengers',
    'PG-13',
    'Earth''s mightiest heroes must come together and learn to fight as a team if they are going to stop the mischievous Loki and his alien army from enslaving humanity.'
  );

We can either copy and paste these into the SQL editor inside of the Supabase Dashboard or we can use the CLI to handle migrations and seeding the database locally (optional).

Handle migrations and seeding the database locally (optional)

Inside of our terminal lets run the following command to create a migration file:

npx supabase migration new create_movies_table

We can now open this create_movies_table.sql file and paste the content from the create table movies snippet above.

Open the seed.sql file and paste the content from the insert into movies snippet above.

Now we can start up the local instance of Supabase and have the migration and seed files executed by the CLI:

npx supabase start

Setup the Supabase Python client

Lets open our main.py and create our supabase client:

import os
from dotenv import load_dotenv
from supabase import create_client

load_dotenv()

url = os.environ.get("SUPABASE_URL", "")
key = os.environ.get("SUPABASE_ANON_KEY", "")

client = create_client(url, key)

You will notice we are using environment variables here, so we will need to setup our respective .env file with the values from our Supabase instance. In the example below I will add the values for the local instance which can be seen after npx supabase start has completed or by running npx supabase status in the Terminal.

SUPABASE_URL="http://127.0.0.1:54321"
SUPABASE_ANON_KEY="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZS1kZW1vIiwicm9sZSI6ImFub24iLCJleHAiOjE5ODM4MTI5OTZ9.CRXP1A7WOeoJeXxjNni43kdQwgnWNReilDMblYTn_I0"

Search can be performed using the .text_search method of the Supabase python library. Open the main.py file and add the following code below:

...
def main():
    res = (
        client.table("movies")
        .select("*")
        .text_search("title", "'The Avenger'|'Mean'")
        .execute()
    )
    print(res.data)


if __name__ == "__main__":
    main()

The code above is making use of the client we created in the setup the Supabase Python client section above. We then select the table movies using the table method and in this movies table we want all columns to be returned so we use .select('*'). Next we call the .text_search method with the column (title) we would like to search on followed by the keywords (The Avenger and Mean) we are searching for.

You will also notice that the search keywords have a single quote around them and are separated by the | pipe symbol. This is necessary for the full text search to work correctly.

Full code snippet

Your full code snippet should now look like:

import os
from dotenv import load_dotenv
from supabase import create_client

load_dotenv()

url = os.environ.get("SUPABASE_URL", "")
key = os.environ.get("SUPABASE_ANON_KEY", "")

client = create_client(url, key)


def main():
    res = (
        client.table("movies")
        .select("*")
        .text_search("title", "'The Avenger'|'Mean'")
        .execute()
    )
    print(res.data)


if __name__ == "__main__":
    main()

You can now run this code from inside of your terminal by first switching to the virtual environment that poetry created:

poetry shell

And then calling our main.py using:

python supabase_full_text_search/main.py

You should now get two results from our database printed in your terminal.

Conclusion

In this post we explained how to setup a Python project to do full text search with the Supabase python library. You can learn more about the Python library by reading the reference docson the Supabase website.

The completed project can be found in this GitHub repository.