fulltext_search_strawberry_django.md

Full Text Search in Strawberry-Django with PostgreSQL

The Scenario

Suppose you have an Article model that belongs to a Category, and you want to search articles by text content from both the article's own fields and its related category name.

# models.py
from django.db import models
from django.contrib.postgres.indexes import GinIndex
from django.contrib.postgres.search import SearchVectorField


class Category(models.Model):
    name = models.CharField(max_length=200)

    class Meta:
        db_table = "myapp_category"


class Article(models.Model):
    title = models.CharField(max_length=200)
    body = models.TextField()
    category = models.ForeignKey(
        Category, on_delete=models.CASCADE, related_name="articles"
    )

    # Pre-computed search vector — avoids recomputing to_tsvector on every query
    search_vector = SearchVectorField(null=True)

    class Meta:
        db_table = "myapp_article"
        indexes = [
            GinIndex(fields=["search_vector"]),
        ]

The SearchVectorField stores a pre-computed tsvector column. Combined with a GIN index, this lets Postgres do full text matching against the stored vector directly, rather than computing to_tsvector() on every row at query time.

---

The Strawberry-Django Filter

Strawberry-django's @strawberry_django.filter_field decorator lets you write custom filter methods that receive the queryset and return a (queryset, Q) tuple. This is where you wire up SearchQuery against your search_vector column.

# types.py
import strawberry
import strawberry_django
from strawberry_django import auto
from django.contrib.postgres.search import SearchQuery, SearchRank
from django.db.models import F, Q, QuerySet
from strawberry.types import Info

from . import models


@strawberry_django.filter_type(models.Article)
class ArticleFilter:
    category_id: auto | None = strawberry.UNSET

    @strawberry_django.filter_field
    def search(
        self,
        queryset: QuerySet,
        value: str,
        prefix: str,
    ) -> tuple[QuerySet, Q]:
        query = SearchQuery(value, search_type="websearch")
        queryset = queryset.annotate(
            search_rank=SearchRank(F(f"{prefix}search_vector"), query)
        )
        return queryset, Q(**{f"{prefix}search_vector": query})


@strawberry_django.type(models.Article, filters=ArticleFilter)
class ArticleType:
    id: auto
    title: auto
    body: auto

A few things to note:

• search_type="websearch" lets users type queries like django -celery or "full text" with natural boolean/phrase syntax, which Postgres parses into a proper tsquery.
• prefix is critical for correctness. Strawberry-django passes a prefix string (e.g. "" at the root, or "article__" when filtering through a relation) so that your field names resolve against the right table. Always interpolate it into your field references.
• SearchRank is annotated onto the queryset so you can order by relevance downstream. The filter itself returns a Q object that does the actual @@ match against the stored vector.
• The method returns tuple[QuerySet, Q] rather than calling queryset.filter() directly. This is important — strawberry-django combines the Q objects from all active filters using AND/OR/NOT logic, and calling .filter() inside a custom method would bypass that composition.

Wiring It Into the Schema

# schema.py
import strawberry
import strawberry_django


@strawberry.type
class Query:
    articles: list[ArticleType] = strawberry_django.field()


schema = strawberry.Schema(query=Query)

Because ArticleType declares filters=ArticleFilter, the articles field automatically exposes the filter input in the schema. A query looks like:

{
  articles(filters: { search: "postgres full text" }) {
    id
    title
  }
}

Ordering by Rank

If you want results ordered by relevance, you can add a custom ordering type or use the annotated search_rank field. One approach:

@strawberry_django.order_type(models.Article)
class ArticleOrder:
    @strawberry_django.order_field
    def search_rank(self, queryset: QuerySet, value: strawberry_django.Ordering):
        # Only meaningful when a search filter has been applied
        # (which annotates search_rank onto the queryset)
        return queryset.order_by(
            F("search_rank").desc(nulls_last=True)
            if value == strawberry_django.Ordering.DESC
            else F("search_rank").asc(nulls_last=True)
        ), []

---

Keeping the SearchVectorField Updated

A SearchVectorField is only useful if it stays in sync with the data it indexes. There are three common strategies; we'll focus on database triggers since they're the most robust.

Strategy 1: Simple Trigger for the Article's Own Fields

The simplest case updates search_vector whenever title or body changes on the article itself. Postgres ships with a built-in tsvector_update_trigger() function for exactly this. Deploy it via a Django migration:

# migrations/0002_article_search_trigger.py
from django.contrib.postgres.search import SearchVector
from django.db import migrations


def compute_search_vector(apps, schema_editor):
    """Backfill existing rows."""
    Article = apps.get_model("myapp", "Article")
    Article.objects.update(
        search_vector=SearchVector("title", weight="A")
        + SearchVector("body", weight="B")
    )


class Migration(migrations.Migration):
    dependencies = [
        ("myapp", "0001_initial"),
    ]

    operations = [
        migrations.RunSQL(
            sql="""
                CREATE TRIGGER article_search_vector_trigger
                BEFORE INSERT OR UPDATE OF title, body, search_vector
                ON myapp_article
                FOR EACH ROW EXECUTE PROCEDURE
                    tsvector_update_trigger(
                        search_vector, 'pg_catalog.english', title, body
                    );

                -- Force the trigger to fire for all existing rows
                UPDATE myapp_article SET search_vector = NULL;
            """,
            reverse_sql="""
                DROP TRIGGER IF EXISTS article_search_vector_trigger
                ON myapp_article;
            """,
        ),
        migrations.RunPython(
            compute_search_vector,
            reverse_code=migrations.RunPython.noop,
        ),
    ]

How this works:

• The BEFORE INSERT OR UPDATE OF title, body, search_vector clause means the trigger fires only when those specific columns change (or on insert).
• tsvector_update_trigger is a Postgres built-in that sets the named column (search_vector) to the tsvector computed from the listed source columns, using the given text search configuration (pg_catalog.english).
• The UPDATE ... SET search_vector = NULL trick forces the trigger to fire for every existing row, backfilling the vector. The RunPython step does the same thing at the Django ORM level as a safety net.

This is fast and reliable for columns that live on the same table. But tsvector_update_trigger can only reference columns on its own row — it can't reach into a related table.

Strategy 2: Cross-Table Trigger for Related Model Changes

When you want the article's search_vector to include the category name (so that searching "tutorials" finds articles in a category called "Tutorials"), you need a custom trigger function. There are actually two triggers needed:

1. On the article table — recomputes the vector on insert/update, pulling in the category name via a subquery.
2. On the category table — when a category's name changes, updates the search vector on all related articles.

# migrations/0003_article_search_trigger_with_category.py
from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [
        ("myapp", "0002_article_search_trigger"),
    ]

    operations = [
        # First, drop the simple trigger from the previous migration
        migrations.RunSQL(
            sql="DROP TRIGGER IF EXISTS article_search_vector_trigger ON myapp_article;",
            reverse_sql="",  # handled by reverse of the next operation
        ),
        migrations.RunSQL(
            sql="""
                -- Function that computes the article search vector,
                -- including the related category name.
                CREATE OR REPLACE FUNCTION article_search_vector_update() RETURNS trigger AS $$
                DECLARE
                    category_name TEXT;
                BEGIN
                    -- Look up the category name for this article
                    SELECT name INTO category_name
                    FROM myapp_category
                    WHERE id = NEW.category_id;

                    NEW.search_vector :=
                        setweight(to_tsvector('pg_catalog.english', coalesce(NEW.title, '')), 'A') ||
                        setweight(to_tsvector('pg_catalog.english', coalesce(NEW.body, '')), 'B') ||
                        setweight(to_tsvector('pg_catalog.english', coalesce(category_name, '')), 'C');

                    RETURN NEW;
                END;
                $$ LANGUAGE plpgsql;

                -- Trigger on the article table itself
                CREATE TRIGGER article_search_vector_trigger
                BEFORE INSERT OR UPDATE OF title, body, category_id, search_vector
                ON myapp_article
                FOR EACH ROW EXECUTE FUNCTION article_search_vector_update();

                -- Backfill
                UPDATE myapp_article SET search_vector = NULL;
            """,
            reverse_sql="""
                DROP TRIGGER IF EXISTS article_search_vector_trigger ON myapp_article;
                DROP FUNCTION IF EXISTS article_search_vector_update();
            """,
        ),
        migrations.RunSQL(
            sql="""
                -- Function that propagates category name changes to articles.
                CREATE OR REPLACE FUNCTION category_name_change_update_articles() RETURNS trigger AS $$
                BEGIN
                    -- Only act if the name actually changed
                    IF NEW.name IS DISTINCT FROM OLD.name THEN
                        -- Setting search_vector to NULL will cause the
                        -- article-level trigger to recompute it.
                        UPDATE myapp_article
                        SET search_vector = NULL
                        WHERE category_id = NEW.id;
                    END IF;

                    RETURN NEW;
                END;
                $$ LANGUAGE plpgsql;

                CREATE TRIGGER category_name_update_articles_trigger
                AFTER UPDATE OF name
                ON myapp_category
                FOR EACH ROW EXECUTE FUNCTION category_name_change_update_articles();
            """,
            reverse_sql="""
                DROP TRIGGER IF EXISTS category_name_update_articles_trigger ON myapp_category;
                DROP FUNCTION IF EXISTS category_name_change_update_articles();
            """,
        ),
    ]

There are a few design decisions here worth calling out:

The article trigger uses BEFORE INSERT OR UPDATE and modifies NEW directly. This is the standard pattern for computed columns — the trigger sets the value on the row before it's written to disk, so there's no extra UPDATE statement.

The category trigger uses AFTER UPDATE and sets search_vector = NULL. This is a deliberate cascade pattern: rather than duplicating the vector computation logic in the category trigger, it nulls out the column, which in turn fires the article-level trigger to recompute correctly. This keeps the vector logic in one place. The tradeoff is that it generates one UPDATE per affected article, which is fine for most workloads but could be a concern if you have millions of articles per category. In that case you could inline the computation directly in the category trigger, or batch the updates.

IS DISTINCT FROM rather than != handles the case where name is NULL correctly (since NULL != NULL is NULL in SQL, not TRUE).

Weights (A, B, C) let you rank title matches higher than body matches, and body higher than category. Postgres uses these weights in ts_rank calculations. The default weight values are A=1.0, B=0.4, C=0.2, D=0.1.

A Note on tsvector_update_trigger vs Custom Functions

The built-in tsvector_update_trigger() is convenient but limited — it can only reference columns on the same row and doesn't support weights or cross-table joins. For anything beyond the simplest case, you'll want a custom PL/pgSQL function as shown above. The custom function approach also gives you the ability to use coalesce for NULL safety and setweight for relevance tuning.

---

Putting It All Together

With the model, the GIN index, the triggers, and the strawberry-django filter in place, the full flow is:

1. An article is created or updated → the BEFORE trigger computes the tsvector from title + body + category name and stores it in search_vector.
2. A category name changes → the AFTER trigger nulls the search_vector on all related articles → the article-level trigger fires and recomputes.
3. A GraphQL query with filters: { search: "..." } hits the strawberry-django filter → SearchQuery is matched against the pre-computed search_vector using the GIN index → fast, ranked results.

The search vector is always up to date, the GIN index keeps queries fast, and the strawberry-django filter layer is a thin bridge between GraphQL and Django's ORM.