Skip to content

Blog API Tutorial

Complete blog application demonstrating FraiseQL's CQRS architecture, N+1 prevention, and production patterns.

Overview

Build a blog API with:

  • Users, posts, and threaded comments
  • JSONB composition (single-query nested data)
  • Mutation functions with explicit side effects
  • Production-ready patterns

Time: 30-45 minutes Prerequisites: Completed quickstart, basic PostgreSQL knowledge

Database Schema

Tables (Write Side)

-- Users
CREATE TABLE tb_user (
    pk_user INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- Internal fast joins
    id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),         -- Public API
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Posts
CREATE TABLE tb_post (
    pk_post INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- Internal fast joins
    id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),         -- Public API
    fk_author INT NOT NULL REFERENCES tb_user(pk_user),        -- Fast FK to pk_user
    title VARCHAR(500) NOT NULL,
    slug VARCHAR(500) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    tags TEXT[] DEFAULT '{}',
    is_published BOOLEAN DEFAULT false,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Comments (with threading)
CREATE TABLE tb_comment (
    pk_comment INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- Internal fast joins
    id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),            -- Public API
    fk_post INT NOT NULL REFERENCES tb_post(pk_post) ON DELETE CASCADE,     -- Fast FK to pk_post
    fk_author INT REFERENCES tb_user(pk_user),                    -- Fast FK to pk_user
    fk_parent INT REFERENCES tb_comment(pk_comment),              -- Fast FK to pk_comment
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_post_author ON tb_post(fk_author);
CREATE INDEX idx_post_published ON tb_post(is_published, published_at DESC);
CREATE INDEX idx_comment_post ON tb_comment(fk_post, created_at);
CREATE INDEX idx_comment_parent ON tb_comment(fk_parent);

Views (Read Side)

N+1 Prevention Pattern: Compose nested data in views.

-- Basic user view
CREATE VIEW v_user AS
SELECT
    id,
    jsonb_build_object(
        '__typename', 'User',
        'id', id,
        'email', email,
        'name', name,
        'bio', bio,
        'avatar_url', avatar_url,
        'created_at', created_at
    ) AS data
FROM tb_user;

-- Post with embedded author
CREATE VIEW v_post AS
SELECT
    p.id,
    u.id as author_id,
    p.is_published,
    p.created_at,
    jsonb_build_object(
        '__typename', 'Post',
        'id', p.id,
        'title', p.title,
        'slug', p.slug,
        'content', p.content,
        'excerpt', p.excerpt,
        'tags', p.tags,
        'is_published', p.is_published,
        'published_at', p.published_at,
        'created_at', p.created_at,
        'author', (SELECT data FROM v_user WHERE id = u.id)
    ) AS data
FROM tb_post p
JOIN tb_user u ON u.pk_user = p.fk_author;

-- Comment with author, post, and replies (prevents N+1!)
CREATE VIEW v_comment AS
SELECT
    c.id,
    c.fk_post,
    c.fk_parent,
    c.created_at,
    jsonb_build_object(
        '__typename', 'Comment',
        'id', c.id,
        'content', c.content,
        'created_at', c.created_at,
        'author', (SELECT v.data FROM v_user v JOIN tb_user u ON u.id = v.id
                   WHERE u.pk_user = c.fk_author),
        'post', (
            SELECT jsonb_build_object(
                '__typename', 'PostSummary',
                'id', p.id,
                'title', p.title
            )
            FROM tb_post p WHERE p.pk_post = c.fk_post
        ),
        'replies', COALESCE(
            (SELECT jsonb_agg(
                jsonb_build_object(
                    '__typename', 'Comment',
                    'id', r.id,
                    'content', r.content,
                    'created_at', r.created_at,
                    'author', (SELECT v.data FROM v_user v JOIN tb_user u ON u.id = v.id
                               WHERE u.pk_user = r.fk_author)
                ) ORDER BY r.created_at
            )
            FROM tb_comment r
            WHERE r.fk_parent = c.pk_comment),
            '[]'::jsonb
        )
    ) AS data
FROM tb_comment c;

-- Full post view with comments
CREATE VIEW v_post_full AS
SELECT
    p.id,
    p.is_published,
    p.created_at,
    jsonb_build_object(
        '__typename', 'Post',
        'id', p.id,
        'title', p.title,
        'slug', p.slug,
        'content', p.content,
        'excerpt', p.excerpt,
        'tags', p.tags,
        'is_published', p.is_published,
        'published_at', p.published_at,
        'created_at', p.created_at,
        'author', (SELECT v.data FROM v_user v JOIN tb_user u ON u.id = v.id
                   WHERE u.pk_user = p.fk_author),
        'comments', COALESCE(
            (SELECT jsonb_agg(data ORDER BY created_at)
             FROM v_comment
             WHERE fk_post = p.pk_post AND fk_parent IS NULL),
            '[]'::jsonb
        )
    ) AS data
FROM tb_post p;

Performance: Fetching post + author + comments + replies = 1 query (not N+1).

JSONB keys are snake_case, like the Python field names (created_at). FraiseQL's where and order_by read these keys (data -> 'created_at'), and the response turns them into the GraphQL names (createdAt).

GraphQL Types

from datetime import datetime
from fraiseql.types import ID
import fraiseql

@fraiseql.type(sql_source="v_user")
class User:
    id: ID
    email: str
    name: str
    bio: str | None
    avatar_url: str | None
    created_at: datetime

# The post a comment belongs to, as v_comment embeds it (id and title).
# Comment can't name Post here: Post isn't defined yet.
@fraiseql.type
class PostSummary:
    id: ID
    title: str

@fraiseql.type(sql_source="v_comment")
class Comment:
    id: ID
    content: str
    created_at: datetime
    author: User
    post: PostSummary
    replies: list["Comment"]

@fraiseql.type(sql_source="v_post")
class Post:
    id: ID
    title: str
    slug: str
    content: str
    excerpt: str | None
    tags: list[str]
    is_published: bool
    published_at: datetime | None
    created_at: datetime
    author: User
    comments: list[Comment]

# get_post reads Post rows from v_post_full too
from fraiseql.db import register_type_for_view
register_type_for_view("v_post_full", Post, has_jsonb_data=True, jsonb_column="data")

Queries

A query is an async resolver whose first parameter is info. It reads a view through the repository in info.context["db"]: find_one for one row, find for a list. mandatory_filters compares view columns (id, is_published) for equality. A list query also gets where and orderBy arguments; leave order_by unannotated and pass it on, or the client's orderBy is ignored.

from graphql import GraphQLResolveInfo
from fraiseql.types import ID
import fraiseql

@fraiseql.query
async def get_post(info: GraphQLResolveInfo, id: ID) -> Post | None:
    """Get single post with all nested data."""
    db = info.context["db"]
    # v_post_full embeds the comments and their replies
    return await db.find_one("v_post_full", info=info, mandatory_filters={"id": id})

@fraiseql.query
async def get_posts(
    info: GraphQLResolveInfo,
    is_published: bool | None = None,
    order_by=None,
    limit: int = 20,
    offset: int = 0
) -> list[Post]:
    """List posts with filtering and pagination, newest first by default."""
    db = info.context["db"]
    filters = {} if is_published is None else {"is_published": is_published}
    return await db.find(
        "v_post",
        info=info,
        mandatory_filters=filters,
        order_by=order_by or {"created_at": "DESC"},
        limit=limit,
        offset=offset,
    )

Mutations

Pattern: PostgreSQL functions handle business logic. A class mutation calls the function named after it (CreatePost calls create_post) with its context params first and the input as one jsonb, and turns the mutation_response it returns into the success or error type. mutation_response and the mutation_* helpers come from migrations/trinity/005_add_mutation_response.sql.

-- Create post function
CREATE OR REPLACE FUNCTION create_post(p_author_id UUID, input JSONB)
RETURNS mutation_response AS $$
DECLARE
    v_post_id UUID;
    v_author_pk INT;
    v_slug TEXT;
    v_is_published BOOLEAN := coalesce((input->>'is_published')::boolean, false);
BEGIN
    -- Get author internal pk_user
    SELECT pk_user INTO v_author_pk
    FROM tb_user WHERE id = p_author_id;

    IF v_author_pk IS NULL THEN
        RETURN mutation_not_found('User', p_author_id::text);
    END IF;

    -- Generate slug
    v_slug := lower(regexp_replace(input->>'title', '[^a-zA-Z0-9]+', '-', 'g'));
    v_slug := trim(both '-' from v_slug);
    v_slug := v_slug || '-' || substr(md5(random()::text), 1, 8);

    -- Insert post
    INSERT INTO tb_post (
        fk_author, title, slug, content, excerpt, tags,
        is_published, published_at
    )
    VALUES (
        v_author_pk, input->>'title', v_slug, input->>'content', input->>'excerpt',
        -- an omitted tags field arrives as JSON null
        ARRAY(SELECT jsonb_array_elements_text(
            coalesce(jsonb_strip_nulls(input)->'tags', '[]'))),
        v_is_published,
        CASE WHEN v_is_published THEN NOW() ELSE NULL END
    )
    RETURNING id INTO v_post_id;

    RETURN mutation_created(
        'Post created', (SELECT data FROM v_post WHERE id = v_post_id), 'Post'
    );
END;
$$ LANGUAGE plpgsql;

-- Create comment function
CREATE OR REPLACE FUNCTION create_comment(p_author_id UUID, input JSONB)
RETURNS mutation_response AS $$
DECLARE
    v_comment_id UUID;
    v_author_pk INT;
    v_post_pk INT;
    v_parent_pk INT;
BEGIN
    -- Get internal primary keys
    SELECT pk_user INTO v_author_pk FROM tb_user WHERE id = p_author_id;
    SELECT pk_post INTO v_post_pk FROM tb_post WHERE id = (input->>'post_id')::uuid;
    IF input->>'parent_id' IS NOT NULL THEN
        SELECT pk_comment INTO v_parent_pk
        FROM tb_comment WHERE id = (input->>'parent_id')::uuid;
    END IF;

    IF v_author_pk IS NULL OR v_post_pk IS NULL THEN
        RETURN mutation_not_found('Author or post');
    END IF;

    -- Insert comment
    INSERT INTO tb_comment (fk_author, fk_post, fk_parent, content)
    VALUES (v_author_pk, v_post_pk, v_parent_pk, input->>'content')
    RETURNING id INTO v_comment_id;

    RETURN mutation_created(
        'Comment added', (SELECT data FROM v_comment WHERE id = v_comment_id), 'Comment'
    );
END;
$$ LANGUAGE plpgsql;

Python Mutation Handlers:

import fraiseql
from fraiseql.types import ID

@fraiseql.input
class CreatePostInput:
    title: str
    content: str
    excerpt: str | None = None
    tags: list[str] | None = None
    is_published: bool = False

@fraiseql.input
class CreateCommentInput:
    post_id: ID
    content: str
    parent_id: ID | None = None

@fraiseql.success
class CreatePostSuccess:
    post: Post

@fraiseql.success
class CreateCommentSuccess:
    comment: Comment

@fraiseql.error
class BlogError:
    """status, message and code come from the function's mutation_response."""

# The author is the signed-in user: info.context["user_id"] is passed first
@fraiseql.mutation(context_params={"user_id": "p_author_id"})
class CreatePost:
    """Create new blog post."""
    input: CreatePostInput
    success: CreatePostSuccess
    error: BlogError

@fraiseql.mutation(context_params={"user_id": "p_author_id"})
class CreateComment:
    """Add comment to post."""
    input: CreateCommentInput
    success: CreateCommentSuccess
    error: BlogError

Application Setup

create_fraiseql_app builds the schema and the FastAPI app, and opens the connection pool. The repository reaches every resolver as info.context["db"]; context_getter adds the request's own values to that context, here the user_id the mutations pass as p_author_id.

import os
from fraiseql.fastapi import create_fraiseql_app

# Context setup: merged into the default context (which holds "db")
async def get_context(request):
    return {
        "user_id": request.headers.get("X-User-ID"),  # From auth middleware
    }

# Initialize app
app = create_fraiseql_app(
    database_url=os.getenv("DATABASE_URL", "postgresql://localhost/blog"),
    types=[User, Post, Comment],
    queries=[get_post, get_posts],
    mutations=[CreatePost, CreateComment],
    context_getter=get_context,
    production=False,  # False enables the GraphQL playground
)

if __name__ == "__main__":
    import uvicorn
    uvicorn.run(app, host="0.0.0.0", port=8000)

Testing

GraphQL Queries

# Get post with nested data (1 query!)
query GetPost($id: ID!) {
  getPost(id: $id) {
    id
    title
    content
    author {
      id
      name
      avatarUrl
    }
    comments {
      id
      content
      author {
        name
      }
      replies {
        id
        content
        author {
          name
        }
      }
    }
  }
}

# List published posts
query GetPosts {
  getPosts(isPublished: true, limit: 10) {
    id
    title
    excerpt
    publishedAt
    author {
      name
    }
  }
}

GraphQL Mutations

A mutation returns a union of its success and error types, so select the fields with inline fragments. The author comes from the X-User-ID header, through get_context.

mutation CreatePost($input: CreatePostInput!) {
  createPost(input: $input) {
    ... on CreatePostSuccess {
      post {
        id
        title
        slug
        author {
          name
        }
      }
    }
    ... on BlogError {
      message
      code
    }
  }
}

mutation AddComment($input: CreateCommentInput!) {
  createComment(input: $input) {
    ... on CreateCommentSuccess {
      comment {
        id
        content
        createdAt
        author {
          name
        }
      }
    }
    ... on BlogError {
      message
      code
    }
  }
}

Performance Patterns

1. Materialized Views for Analytics

CREATE MATERIALIZED VIEW mv_popular_posts AS
SELECT
    p.pk_post,
    p.title,
    COUNT(DISTINCT c.id) as comment_count,
    array_agg(DISTINCT u.name) as commenters
FROM tb_post p
LEFT JOIN tb_comment c ON c.fk_post = p.pk_post
LEFT JOIN tb_user u ON u.pk_user = c.fk_author
WHERE p.is_published = true
GROUP BY p.pk_post, p.title
HAVING COUNT(DISTINCT c.id) > 5;

-- CONCURRENTLY needs a unique index on the materialized view
CREATE UNIQUE INDEX ON mv_popular_posts (pk_post);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_popular_posts;

2. Partial Indexes for Common Queries

-- Index only published posts
CREATE INDEX idx_post_published_recent
ON tb_post (created_at DESC)
WHERE is_published = true;

-- Index only top-level comments
CREATE INDEX idx_comment_toplevel
ON tb_comment (fk_post, created_at)
WHERE fk_parent IS NULL;

Production Checklist

  • Add authentication middleware
  • Implement rate limiting
  • Set up query complexity limits
  • Enable APQ caching
  • Configure connection pooling
  • Add monitoring (Prometheus/Sentry)
  • Set up database backups
  • Create migration strategy
  • Write integration tests
  • Deploy with Docker

Key Patterns Demonstrated

  1. N+1 Prevention: JSONB composition in views
  2. CQRS: Separate read views from write tables
  3. Type Safety: Full type checking end-to-end
  4. Performance: Single-query nested data fetching
  5. Business Logic: PostgreSQL functions for mutations

Next Steps

See Also