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¶
- N+1 Prevention: JSONB composition in views
- CQRS: Separate read views from write tables
- Type Safety: Full type checking end-to-end
- Performance: Single-query nested data fetching
- Business Logic: PostgreSQL functions for mutations
Next Steps¶
- Database Patterns - tv_ pattern and production patterns
- Performance - Rust transformation, APQ, TurboRouter
- Multi-Tenancy - Tenant isolation patterns
See Also¶
- Quickstart - 5-minute intro
- Database API - Repository methods
- Production Deployment - Deploy to production