Building a GraphQL Video Discovery API with Strawberry and FastAPI

When DailyWatch crossed a few hundred thousand video records, the bottleneck stopped being the database and became the shape of our API. A single discovery screen needed five separate REST round trips: trending videos, the channel behind each video, related tags, aggregate view counts, and a "because you watched" rail. Mobile clients on weak connections were firing 30+ requests to paint one page, and every new front-end feature meant either another bespoke endpoint or another ?expand=channel,tags,stats query-param that nobody wanted to own.

GraphQL fixes exactly that: the client asks for the fields it needs, nested how it needs them, in one request. This post walks through how I built a production video-discovery GraphQL API with Strawberry — a Python library that builds schemas straight from type hints — and FastAPI, sitting in front of the same SQLite FTS5 search index that powers our PHP 8.4 site behind LiteSpeed and Cloudflare. I'll cover schema design, full-text search ranking, the N+1 problem that everyone hits, and how to keep an inherently POST-based protocol cacheable at the edge.

Why Strawberry over the alternatives

I evaluated Graphene, Ariadne, and Strawberry before committing. Strawberry won for a few concrete reasons:

• Type hints are the schema. You write normal Python dataclasses with annotations and Strawberry derives the GraphQL types. There is no duplicated SDL file to drift out of sync with the code.
• Async-native. Resolvers are async def, which matters when every field can hit SQLite — or, later, a network service — without blocking the event loop.
• First-class FastAPI integration. GraphQLRouter drops straight into an existing FastAPI app, so the GraphQL endpoint lives next to my REST health checks and webhooks under one ASGI server.
• Built-in DataLoader. The batching primitive that kills N+1 ships in the box, so I didn't have to bolt on a third-party loader.

FastAPI handles the HTTP layer, OpenAPI docs for the non-GraphQL routes, and dependency injection for cross-cutting concerns like auth and rate limiting.

The data model

Our discovery data is deliberately boring: a videos table, a channels table, and an FTS5 virtual table mirroring the searchable text columns. The site itself is PHP, and the schema migrations live there, so the canonical FTS5 setup is owned by the PHP side:

<?php
$db = new PDO('sqlite:/var/www/dailywatch/data/videos.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec(<<<SQL
CREATE TABLE IF NOT EXISTS videos (
    id           INTEGER PRIMARY KEY,
    title        TEXT NOT NULL,
    description  TEXT,
    channel_id   INTEGER NOT NULL,
    views        INTEGER NOT NULL DEFAULT 0,
    published_at TEXT NOT NULL
);

-- external-content FTS5 table: stores only the index, not a copy of the text
CREATE VIRTUAL TABLE IF NOT EXISTS videos_fts USING fts5(
    title, description,
    content='videos',
    content_rowid='id',
    tokenize='porter unicode61'
);
SQL);

// keep the FTS index in lockstep with writes
$db->exec(<<<SQL
CREATE TRIGGER IF NOT EXISTS videos_ai AFTER INSERT ON videos BEGIN
    INSERT INTO videos_fts(rowid, title, description)
    VALUES (new.id, new.title, new.description);
END;
SQL);

The content='videos' option makes videos_fts an external content table — it stores only the index, not a second copy of the text, which keeps the file lean. porter stemming means a search for "running" also matches "run", and unicode61 handles the diacritics in our non-English titles. The Python service connects to this exact same database file read-only. There is no second source of truth; GraphQL is purely a read API over the data the PHP cron jobs maintain.

Defining the schema in Strawberry

Here is the core schema. Note how Video.channel is a resolver, not a stored field — the client only pays for the channel join if it asks for it.

import strawberry
from typing import Optional


@strawberry.type
class Channel:
    id: strawberry.ID
    name: str
    subscriber_count: int


@strawberry.type
class Video:
    id: strawberry.ID
    title: str
    description: Optional[str]
    views: int
    published_at: str
    # Private keeps the FK out of the public schema but available to resolvers
    channel_id: strawberry.Private[int]

    @strawberry.field
    async def channel(self, info: strawberry.Info) -> Channel:
        loader = info.context["channel_loader"]
        return await loader.load(self.channel_id)


@strawberry.type
class Query:
    @strawberry.field
    async def search(
        self, info: strawberry.Info, query: str, limit: int = 20, offset: int = 0
    ) -> list[Video]:
        return await search_videos(info.context["db"], query, min(limit, 100), offset)

    @strawberry.field
    async def trending(self, info: strawberry.Info, limit: int = 20) -> list[Video]:
        return await trending_videos(info.context["db"], min(limit, 100))


schema = strawberry.Schema(query=Query)

A few things worth calling out:

• strawberry.Private[int] keeps channel_id out of the public schema while still letting the resolver use it. Clients see channel, never the raw foreign key.
• The channel resolver pulls a loader off the request context instead of querying directly — that is the seam where batching happens.
• search and trending are the only two entry points. Everything else hangs off the Video type.

Schema design choices that aged well

A GraphQL schema is a contract you live with for years, so a few early decisions paid off:

• IDs are opaque strings. Even though our primary keys are integers, every id is a GraphQL ID serialized as a string. That left room to switch to a global relay-style ID later without breaking clients.
• Nullability is intentional. title is non-null because a video without a title is a bug; description is nullable because plenty of imported videos genuinely have none. Encoding that in the type means clients handle the empty case at compile time, not at 2 a.m.
• No mutations on the public schema. This endpoint is read-only by design. Writes happen through the PHP cron pipeline, which removed an entire category of authorization headaches from the Python service.
• Resolvers stay thin. Each resolver does one query or one loader call. Logic that would tempt me to fan out lives in plain service functions I can unit-test without spinning up the GraphQL layer.

Wiring it into FastAPI

The context getter runs once per request. It is where I open the database connection and build the per-request loaders — loaders must never be shared across requests, or one user's cache leaks into another's.

import aiosqlite
from fastapi import FastAPI
from strawberry.fastapi import GraphQLRouter
from dataloaders import make_channel_loader
from schema import schema

DB_PATH = "/var/www/dailywatch/data/videos.db"


async def get_context():
    db = await aiosqlite.connect(DB_PATH)
    db.row_factory = aiosqlite.Row
    return {
        "db": db,
        "channel_loader": make_channel_loader(db),  # fresh loader per request
    }


graphql_app = GraphQLRouter(schema, context_getter=get_context)

app = FastAPI()
app.include_router(graphql_app, prefix="/graphql")

In production I front this with a small connection pool rather than opening aiosqlite per request, and I register a shutdown hook to close connections. But per-request connections are fine to start with: SQLite reads are cheap, and LiteSpeed and Cloudflare absorb most of the traffic before it ever reaches Python.

Full-text search with FTS5 and bm25 ranking

This is the part that actually makes discovery feel good. FTS5 ships with bm25(), a relevance scoring function, and I blend it with a light popularity signal so a strong textual match on an obscure clip doesn't always beat a slightly weaker match on something with a million views.

async def search_videos(db, query: str, limit: int, offset: int) -> list:
    sql = """
        SELECT v.id, v.title, v.description, v.views,
               v.published_at, v.channel_id
        FROM videos_fts f
        JOIN videos v ON v.id = f.rowid
        WHERE videos_fts MATCH ?
        ORDER BY bm25(videos_fts)            -- lower score = better match
                 - (v.views / 1000000.0)     -- light popularity boost
        LIMIT ? OFFSET ?
    """
    # sanitize the user term into FTS5 prefix syntax; never interpolate raw input
    term = " ".join(f'"{w}"*' for w in query.split() if w)
    rows = await db.execute_fetchall(sql, (term, limit, offset))
    from schema import Video
    return [Video(**dict(row)) for row in rows]

Two details that bite people:

• Never interpolate the user's raw string into a MATCH clause. FTS5 has its own query grammar (AND, OR, NEAR, column filters). I split the input and wrap each word as a quoted prefix term ("word"*), which gives type-ahead behavior and neutralizes injection into the FTS syntax.
• bm25() is lower-is-better, so the popularity term is subtracted. Tune the divisor to your view-count distribution; ours is power-law, so in production I actually wrap it in log() to stop a handful of viral videos from dominating every result set.

The N+1 problem and DataLoader

Here is the trap. A query like { trending(limit: 20) { title channel { name } } } calls the channel resolver twenty times, and a naive resolver fires twenty separate SELECT ... WHERE id = ? statements. That is the N+1 problem, and it is worse in GraphQL than in REST because the client controls the fan-out.

DataLoader solves it by batching: every .load(id) call within a single event-loop tick is collected, and your batch function runs one query for all of them.

from strawberry.dataloader import DataLoader
from schema import Channel


def make_channel_loader(db) -> DataLoader:
    async def load_channels(keys: list) -> list:
        placeholders = ",".join("?" for _ in keys)
        sql = f"""
            SELECT id, name, subscriber_count
            FROM channels
            WHERE id IN ({placeholders})
        """
        rows = await db.execute_fetchall(sql, keys)
        by_id = {
            r["id"]: Channel(
                id=str(r["id"]), name=r["name"],
                subscriber_count=r["subscriber_count"],
            )
            for r in rows
        }
        # DataLoader maps results to keys positionally: same order, pad with None
        return [by_id.get(k) for k in keys]

    return DataLoader(load_fn=load_channels)

Two non-negotiable rules for the batch function:

• It must return results in exactly the same order as the input keys, padding missing rows with None. DataLoader maps keys to results positionally, not by id.
• It must be built per request, inside the context getter, so its cache lifetime matches the request. A loader cached at module scope will serve stale channels forever.

With the loader in place, that twenty-video query issues two SQL statements total: one for the videos, one for all twenty channels. On our dataset that took a discovery query from roughly 40ms down to under 5ms.

Errors, nullability, and partial responses

One thing REST developers underestimate: GraphQL can return partial data. If the channel resolver throws for one video in a list of twenty, GraphQL returns the nineteen good results plus an errors array describing the single failure — the whole response is not a 500. That is powerful, but it needs discipline:

• I let expected gaps — a deleted channel, for instance — resolve to null on a nullable field, not raise an exception.
• I reserve raised exceptions for unexpected failures like the database being down, and I map them to a stable code in the extensions block so clients branch on a code rather than parsing an English message.
• I never leak SQL or stack traces into error messages in production. Strawberry lets you install a custom error formatter that masks anything that isn't an allow-listed, client-safe error.

Keeping GraphQL cacheable behind Cloudflare

GraphQL defaults to POST, and POST is uncacheable at the edge — a problem when Cloudflare is the thing keeping your origin alive. Three changes make it work:

• Automatic Persisted Queries (APQ). The client sends a SHA-256 hash of the query instead of the full text. Strawberry supports APQ, and because the request is now small and deterministic, you can serve it over GET with the hash and variables in the query string.
• GET for read-only operations. Once queries go over GET, Cloudflare's cache rules apply normally. I set a short Cache-Control: public, max-age=60 on trending and longer TTLs on stable lookups.
• Cache key on hash plus variables. A Cloudflare cache rule keyed on the persisted-query hash and the variables turns repeated trending calls into edge hits that never touch FastAPI.

This mirrors the layered caching we already run on the PHP side — LiteSpeed page cache in front of the app, Cloudflare in front of that. The GraphQL service slots into the same mental model: treat reads as cacheable artifacts, not opaque POSTs.

Pagination, limits, and protecting the origin

A public GraphQL endpoint is a footgun if you don't bound it. What I enforce:

• Hard limit caps. The resolvers clamp to a maximum of 100 regardless of what the client requests.
• Query depth limiting. Strawberry supports validation rules; I cap depth so nobody requests channel.videos.channel.videos... into oblivion.
• Keyset pagination for deep lists. Offset pagination is fine for the first few pages but degrades on large offsets in SQLite, so infinite-scroll rails paginate on (published_at, id) cursors instead.
• Rate limiting in FastAPI middleware, keyed on the real client IP from Cloudflare's forwarding header.

Observability

Because one HTTP request now triggers a tree of resolvers, "the endpoint is slow" stops being a useful statement. I instrument at the resolver level: a Strawberry extension records timing per field, so I can see that channel is fine but search is the hot path. The DataLoader logs its batch sizes — a batch of size one repeated many times means a loader isn't being reused and is a bug. And each request carries the Cloudflare ray ID, so a slow GraphQL request can be traced back through the edge to the exact origin call.

What I'd do differently

If I were starting over, I would introduce DataLoader from day one rather than retrofitting it after the first slow query — the naive resolver pattern is easy to write and painful to walk back. I would also generate the persisted-query manifest at build time so the edge cache rules are deterministic, instead of relying purely on runtime APQ registration.

Conclusion

Strawberry plus FastAPI gave us a typed, async, single-request discovery API on top of infrastructure we already trusted: SQLite FTS5 for search, with LiteSpeed and Cloudflare doing the heavy lifting on caching. The wins that mattered were concrete — one request instead of five, bm25() relevance blended with popularity, and DataLoader collapsing N+1 fan-outs into single batched queries. None of it required a new database or a rewrite of the PHP site; the GraphQL layer is just a sharp read API over data that was already there. If you're drowning in ?expand= parameters and one-off endpoints, that's the signal it's time to try this stack.