Building a Blog API with FraiseQL: Schema Authoring in Python¶
Duration: ~30 minutes Outcome: A working GraphQL Blog API (authors, posts, comments) served over FastAPI Prerequisites: Python 3.13+, PostgreSQL 13+ Focus: Authoring the schema in Python and running it against PostgreSQL
Overview¶
FraiseQL is a Python runtime GraphQL framework for PostgreSQL. You describe
your API with Python decorators; at app startup FraiseQL builds the GraphQL
schema in memory and serves it over FastAPI. There is no compile step,
no generated artifact files, and no separate server binary — you just run a
FastAPI app with uvicorn.
In this tutorial you'll build a Blog API by:
- Designing the PostgreSQL schema — write tables, read views, and write functions
- Defining GraphQL types with
@fraiseql.type - Writing query resolvers with
@fraiseql.query - Writing mutation resolvers with
@fraiseql.mutation - Assembling and running the app with
create_fraiseql_app+uvicorn - Testing in the GraphQL playground
What You'll Build¶
A Blog API supporting:
- Authors — people who write posts and comments
- Posts — blog posts with an embedded author
- Comments — comments on posts, each with an embedded author
Key Concepts You'll Learn¶
- FraiseQL's decorators:
@fraiseql.type,@fraiseql.query,@fraiseql.mutation,@fraiseql.input,@fraiseql.success,@fraiseql.error - The CQRS split: read from
v_*views, write through PostgreSQL functions - The Trinity identifier pattern:
pk_*(internalBIGINT),id(publicUUID),identifier(optional human-readable slug) - JSONB composition in views to fetch nested data in a single query
- Modern Python type hints (
X | None,list[X]) and theIDscalar
Part 1: The PostgreSQL Schema¶
FraiseQL follows CQRS (Command Query Responsibility Segregation):
- Reads come from
v_*views, each of which exposes anidcolumn plus adataJSONB column built withjsonb_build_object(...). - Writes go through PostgreSQL functions, one per mutation, which hold the validation and write logic and return a JSONB result.
This keeps your write tables normalized while letting reads return ready-to-serve nested JSON.
Step 1.1: Create the Database¶
createdb blog
Step 1.2: Write Tables (the source of truth)¶
Each table uses the Trinity pattern:
pk_*— internalBIGINTprimary key for fast joins (never exposed)id— publicUUID(the GraphQLid)identifier— optional human-readableTEXTslug
-- Authors
CREATE TABLE tb_author (
pk_author BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- internal, fast joins
id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(), -- public GraphQL id
identifier TEXT UNIQUE, -- optional handle/slug
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
bio TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Posts
CREATE TABLE tb_post (
pk_post BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
identifier TEXT UNIQUE, -- slug
fk_author BIGINT NOT NULL REFERENCES tb_author(pk_author), -- fast INT FK
title VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
published BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Comments
CREATE TABLE tb_comment (
pk_comment BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
id UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
fk_post BIGINT NOT NULL REFERENCES tb_post(pk_post) ON DELETE CASCADE,
fk_author BIGINT NOT NULL REFERENCES tb_author(pk_author),
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Indexes on the internal INT keys (fast joins) and public UUIDs (lookups)
CREATE INDEX idx_post_author ON tb_post(fk_author);
CREATE INDEX idx_post_published ON tb_post(published);
CREATE INDEX idx_comment_post ON tb_comment(fk_post);
CREATE INDEX idx_comment_author ON tb_comment(fk_author);
Why
pk_andfk_? InternalBIGINTkeys make joins fast and keep the publicUUIDstable and non-enumerable. Thepk_*/fk_*columns are never exposed in GraphQL — onlyid(and optionallyidentifier) are.
Step 1.3: Read Views (the query side)¶
A read view always exposes an id column plus a data JSONB column built with
jsonb_build_object(...). Add a plain column for anything a query filters on
with mandatory_filters (here published and post_id). Compose related data
inside the view so a single query returns the full nested shape — this is
how FraiseQL avoids N+1 queries. Never put pk_* inside data.
-- Author view
CREATE VIEW v_author AS
SELECT
a.id,
jsonb_build_object(
'id', a.id,
'identifier', a.identifier,
'name', a.name,
'email', a.email,
'bio', a.bio,
'created_at', a.created_at
) AS data
FROM tb_author a;
-- Post view with the author embedded
CREATE VIEW v_post AS
SELECT
p.id,
p.published,
jsonb_build_object(
'id', p.id,
'identifier', p.identifier,
'title', p.title,
'content', p.content,
'published', p.published,
'created_at', p.created_at,
'author', jsonb_build_object(
'id', a.id,
'name', a.name,
'email', a.email
)
) AS data
FROM tb_post p
JOIN tb_author a ON a.pk_author = p.fk_author; -- fast INT join
-- Comment view with author and post embedded
CREATE VIEW v_comment AS
SELECT
c.id,
p.id AS post_id,
jsonb_build_object(
'id', c.id,
'content', c.content,
'created_at', c.created_at,
'author', jsonb_build_object(
'id', a.id,
'name', a.name
),
'post', jsonb_build_object(
'id', p.id,
'title', p.title
)
) AS data
FROM tb_comment c
JOIN tb_author a ON a.pk_author = c.fk_author
JOIN tb_post p ON p.pk_post = c.fk_post;
JSON keys are snake_case, like the Python field names (
created_at). FraiseQL'swhereandorder_byread these keys (data -> 'created_at'), and the response turns them into the GraphQL names (createdAt).
Step 1.4: Write Functions (the command side)¶
Each mutation calls the PostgreSQL function named after it (CreatePost calls
create_post). Each takes the GraphQL input as one
jsonb (with snake_case keys: input->>'author_id'), validates it, writes, and
returns a mutation_response row:
status:'created'selects the mutation's success type; a prefix such as'validation:'or'not_found:'selects its error type.message: a human-readable message.entity: the object the success type's field returns. Read it back from the view, so it has the same shape as a query result.
All write business logic lives in PostgreSQL.
-- The row every mutation function returns
CREATE TYPE mutation_response AS (
status text,
message text,
entity_id text,
entity_type text,
entity jsonb,
updated_fields text[],
cascade jsonb,
metadata jsonb
);
-- Create an author
CREATE OR REPLACE FUNCTION create_author(input jsonb)
RETURNS mutation_response AS $$
DECLARE
new_id uuid;
BEGIN
IF coalesce(input->>'email', '') = '' THEN
RETURN ROW('validation:email', 'email is required',
NULL, NULL, NULL, NULL, NULL, NULL)::mutation_response;
END IF;
IF EXISTS (SELECT 1 FROM tb_author WHERE email = input->>'email') THEN
RETURN ROW('conflict:email', 'email is already registered',
NULL, NULL, NULL, NULL, NULL, NULL)::mutation_response;
END IF;
INSERT INTO tb_author (name, email, bio)
VALUES (input->>'name', input->>'email', input->>'bio')
RETURNING id INTO new_id;
RETURN ROW('created', 'Author created', new_id::text, 'Author',
(SELECT data FROM v_author WHERE id = new_id),
NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;
-- Create a post (looks up the author by public UUID)
CREATE OR REPLACE FUNCTION create_post(input jsonb)
RETURNS mutation_response AS $$
DECLARE
v_fk_author bigint;
new_id uuid;
BEGIN
SELECT pk_author INTO v_fk_author
FROM tb_author
WHERE id = (input->>'author_id')::uuid;
IF v_fk_author IS NULL THEN
RETURN ROW('not_found:author', 'author not found',
NULL, NULL, NULL, NULL, NULL, NULL)::mutation_response;
END IF;
INSERT INTO tb_post (fk_author, title, content, published)
VALUES (
v_fk_author,
input->>'title',
input->>'content',
COALESCE((input->>'published')::boolean, false)
)
RETURNING id INTO new_id;
RETURN ROW('created', 'Post created', new_id::text, 'Post',
(SELECT data FROM v_post WHERE id = new_id),
NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;
-- Add a comment to a post
CREATE OR REPLACE FUNCTION add_comment(input jsonb)
RETURNS mutation_response AS $$
DECLARE
v_fk_post bigint;
v_fk_author bigint;
new_id uuid;
BEGIN
SELECT pk_post INTO v_fk_post FROM tb_post WHERE id = (input->>'post_id')::uuid;
SELECT pk_author INTO v_fk_author FROM tb_author WHERE id = (input->>'author_id')::uuid;
IF v_fk_post IS NULL THEN
RETURN ROW('not_found:post', 'post not found',
NULL, NULL, NULL, NULL, NULL, NULL)::mutation_response;
END IF;
IF v_fk_author IS NULL THEN
RETURN ROW('not_found:author', 'author not found',
NULL, NULL, NULL, NULL, NULL, NULL)::mutation_response;
END IF;
INSERT INTO tb_comment (fk_post, fk_author, content)
VALUES (v_fk_post, v_fk_author, input->>'content')
RETURNING id INTO new_id;
RETURN ROW('created', 'Comment added', new_id::text, 'Comment',
(SELECT data FROM v_comment WHERE id = new_id),
NULL, NULL, NULL)::mutation_response;
END;
$$ LANGUAGE plpgsql;
Step 1.5: Apply the Schema¶
Save the SQL from Steps 1.2–1.4 into a single file schema.sql and run:
psql blog < schema.sql
Part 2: The Python Schema¶
Now translate the database schema into FraiseQL decorators. Create app.py.
Import style. Use the namespaced form (
import fraiseql, then@fraiseql.type). The direct namestypeandinputwould shadow Python builtins, so the namespaced form is recommended.
Step 2.1: Types¶
Each @fraiseql.type maps to a read view via sql_source, and its fields are
read from the view's data JSONB column (the default jsonb_column is "data").
Use ID for entity identifiers and modern union syntax (X | None) for nullable
fields.
from datetime import datetime
import fraiseql
from fraiseql.types import ID
@fraiseql.type(sql_source="v_author", jsonb_column="data")
class Author:
"""A blog author."""
id: ID
identifier: str | None
name: str
email: str
bio: str | None
created_at: datetime
@fraiseql.type(sql_source="v_post", jsonb_column="data")
class Post:
"""A blog post with its author embedded."""
id: ID
identifier: str | None
title: str
content: str
published: bool
created_at: datetime
author: Author
@fraiseql.type(sql_source="v_comment", jsonb_column="data")
class Comment:
"""A comment on a post."""
id: ID
content: str
created_at: datetime
author: Author
post: Post
The nested author and post fields are populated directly from the JSONB the
views already compose — no extra resolvers or N+1 queries.
Step 2.2: Queries¶
A @fraiseql.query resolver is an async function whose first parameter is
info. Get the repository from info.context["db"] and call db.find(...) for
lists or db.find_one(...) for a single record, passing info=info: FraiseQL
reads the requested fields and the field name from it. The GraphQL field name
is inferred from the function name.
from graphql import GraphQLResolveInfo
@fraiseql.query
async def authors(
info: GraphQLResolveInfo,
where=None,
order_by=None,
limit: int | None = 20,
offset: int | None = None,
) -> list[Author]:
"""List authors, newest first unless orderBy says otherwise."""
db = info.context["db"]
return await db.find(
"v_author",
info=info,
where=where,
order_by=order_by or {"created_at": "DESC"},
limit=limit,
offset=offset,
)
@fraiseql.query
async def author(info: GraphQLResolveInfo, id: ID) -> Author | None:
"""Get a single author by id."""
db = info.context["db"]
return await db.find_one("v_author", info=info, mandatory_filters={"id": id})
@fraiseql.query
async def posts(
info: GraphQLResolveInfo,
published: bool | None = None,
where=None,
order_by=None,
limit: int | None = 20,
offset: int | None = None,
) -> list[Post]:
"""List posts, optionally only the published (or unpublished) ones."""
db = info.context["db"]
filters = {} if published is None else {"published": published}
return await db.find(
"v_post",
info=info,
mandatory_filters=filters,
where=where,
order_by=order_by or {"created_at": "DESC"},
limit=limit,
offset=offset,
)
@fraiseql.query
async def post(info: GraphQLResolveInfo, id: ID) -> Post | None:
"""Get a single post by id."""
db = info.context["db"]
return await db.find_one("v_post", info=info, mandatory_filters={"id": id})
@fraiseql.query
async def post_comments(info: GraphQLResolveInfo, post_id: ID) -> list[Comment]:
"""List a post's comments, oldest first."""
db = info.context["db"]
return await db.find(
"v_comment",
info=info,
mandatory_filters={"post_id": post_id},
order_by={"created_at": "ASC"},
)
The repository (db) is FraiseQL's CQRS repository. Its main query methods are:
await db.find("v_x", info=info, where=..., order_by=..., limit=..., offset=...)— list rows from a viewawait db.find_one("v_x", info=info, mandatory_filters={"id": id})— a single row (orNone)
mandatory_filters compares view columns for equality (id, published,
post_id above), and the client can't remove it. where and order_by address
keys of the data JSONB in snake_case: order_by={"created_at": "DESC"}
sorts on data -> 'created_at'. A query returning a list gets where,
orderBy, limit and offset GraphQL arguments; declaring them, as authors
and posts do, lets you set defaults and pass them on. Leave where and
order_by unannotated: FraiseQL gives them the type's generated input types
(AuthorWhereInput, AuthorOrderByInput).
Step 2.3: Inputs and Result Types¶
A mutation takes an @fraiseql.input, and returns its @fraiseql.success type
or its @fraiseql.error type, chosen by the function's status.
@fraiseql.success adds status, message, updated_fields and id to the
class; its own field (author below) receives the function's entity.
@fraiseql.error adds status, message, code (the HTTP-style code of the
status prefix: 422 for validation:, 404 for not_found:, 409 for
conflict:) and errors.
@fraiseql.input
class CreateAuthorInput:
name: str
email: str
bio: str | None = None
@fraiseql.input
class CreatePostInput:
title: str
content: str
author_id: ID
published: bool = False
@fraiseql.input
class AddCommentInput:
post_id: ID
author_id: ID
content: str
@fraiseql.success
class CreateAuthorSuccess:
author: Author
@fraiseql.success
class CreatePostSuccess:
post: Post
@fraiseql.success
class AddCommentSuccess:
comment: Comment
@fraiseql.error
class MutationError:
"""status, message and code come from the function's mutation_response."""
Step 2.4: Mutations¶
A mutation is a class naming its input, success and error types. FraiseQL calls
the PostgreSQL function named after the class in snake_case (CreateAuthor calls
create_author) with the input as one jsonb, and turns the mutation_response
it returns into the success or error type. To call a function with another name,
pass function="..." together with name="createAuthor": without name=, the
GraphQL field is named after the function. The
class body holds no code: the write logic is in the function.
@fraiseql.mutation
class CreateAuthor:
"""Create a new author."""
input: CreateAuthorInput
success: CreateAuthorSuccess
error: MutationError
@fraiseql.mutation
class CreatePost:
"""Create a new post."""
input: CreatePostInput
success: CreatePostSuccess
error: MutationError
@fraiseql.mutation
class AddComment:
"""Add a comment to a post."""
input: AddCommentInput
success: AddCommentSuccess
error: MutationError
Part 3: Assemble and Run the App¶
create_fraiseql_app builds the GraphQL schema in memory from your types,
queries, and mutations, and returns a ready-to-serve FastAPI app. There is no
export, no compile, and no schema file — the schema is constructed at startup.
Add this to the bottom of app.py:
import os
import uvicorn
from fraiseql.fastapi import create_fraiseql_app
app = create_fraiseql_app(
database_url=os.getenv("DATABASE_URL", "postgresql://localhost/blog"),
types=[Author, Post, Comment],
queries=[authors, author, posts, post, post_comments],
mutations=[CreateAuthor, CreatePost, AddComment],
title="Blog API",
description="A simple blog API built with FraiseQL",
production=False, # False enables the GraphQL playground
)
if __name__ == "__main__":
uvicorn.run(app, host="0.0.0.0", port=8000)
Run it:
pip install "fraiseql[all]<2"
export DATABASE_URL=postgresql://localhost/blog
uvicorn app:app --reload
Open the GraphQL playground at http://localhost:8000/graphql.
production=Falseenables the interactive playground and introspection, which is what you want while developing. Set it toTruefor production.
Part 4: Test in the Playground¶
Step 4.1: Seed an Author and a Post¶
Run these mutations in the playground. Mutations return a union, so select the
fields with inline fragments (... on TypeName).
mutation {
createAuthor(input: { name: "Alice Smith", email: "alice@example.com", bio: "Full-stack developer" }) {
... on CreateAuthorSuccess {
author { id name email }
message
}
... on MutationError {
message
code
}
}
}
Copy the returned author id, then create a post:
mutation {
createPost(input: {
title: "Getting Started with GraphQL"
content: "GraphQL is a query language for your API..."
authorId: "PASTE-AUTHOR-ID-HERE"
published: true
}) {
... on CreatePostSuccess {
post {
id
title
published
author { name }
}
}
... on MutationError {
message
}
}
}
Step 4.2: Read Data Back¶
List published posts with their authors embedded — all in a single SQL query
thanks to the JSONB composition in v_post:
query {
posts(published: true) {
id
title
content
createdAt
author {
name
email
}
}
}
Fetch a single post by id:
query {
post(id: "PASTE-POST-ID-HERE") {
id
title
published
author { name }
}
}
Step 4.3: Add and List Comments¶
mutation {
addComment(input: {
postId: "PASTE-POST-ID-HERE"
authorId: "PASTE-AUTHOR-ID-HERE"
content: "Great article!"
}) {
... on AddCommentSuccess {
comment {
id
content
author { name }
}
}
... on MutationError {
message
}
}
}
query {
postComments(postId: "PASTE-POST-ID-HERE") {
id
content
createdAt
author { name }
}
}
Part 5: How It Fits Together¶
GraphQL request FraiseQL (Python + FastAPI) PostgreSQL
─────────────────────────────────────────────────────────────────────────────
query { posts { ... } } → @fraiseql.query resolver → SELECT data FROM v_post
db.find("v_post") (JSONB, author embedded)
mutation { createPost } → @fraiseql.mutation class CreatePost → SELECT * FROM create_post(jsonb)
(validate + INSERT,
returns mutation_response)
- Reads flow through
@fraiseql.query→db.find/db.find_one→ av_*view'sdataJSONB. FraiseQL trims the JSONB to exactly the fields the client requested. - Writes flow through a
@fraiseql.mutationclass → its function, which validates, writes and returns amutation_responsewhoseentityit read back through a view. Itsstatuspicks the success or the error type. - The schema is built in memory at startup from your decorated Python
objects. Restart the app to pick up Python changes; run new SQL with
psqlto pick up schema changes.
Part 6: Common Pitfalls¶
Use modern type hints¶
# Correct: modern union syntax
def author(info, id: ID) -> Author | None: ...
Avoid the old typing.Optional / typing.List forms — use X | None and
list[X] instead.
Always set sql_source on a type¶
@fraiseql.type(sql_source="v_author", jsonb_column="data")
class Author: ...
Without sql_source, FraiseQL can't map the type to a read view.
JSONB keys are snake_case¶
Build the view's JSONB with the Python field name ('created_at', a.created_at),
not the GraphQL one. where and order_by look up data -> 'created_at'; with a
camelCase key, a filter on that field matches nothing and a sort on it does
nothing. FraiseQL camelCases the keys in the response (createdAt).
order_by takes a dict¶
order_by={"created_at": "DESC"}, or a list of such dicts for several keys. A
string such as "-created_at" is not a sort specification.
Never expose pk_*¶
Keep pk_*/fk_* out of the data JSONB. Expose only id (a UUID) and,
optionally, identifier.
Cast UUID inputs back inside the function¶
The input reaches the function as JSONB, where an ID is a string. Cast it
back with (input->>'author_id')::uuid. Input keys are snake_case, like the
Python field names.
Next Steps¶
You've built a complete, runtime-served Blog API: PostgreSQL write tables, JSONB read views, write functions, and Python decorators tying them to GraphQL.
- Blog API Tutorial — a deeper blog example with threaded comments and production patterns
- Quick Start — the 5-minute version
- First Hour Guide — a progressive, hands-on walkthrough
- Production Deployment — ship it to production
Summary¶
You've learned how to:
- Design a CQRS PostgreSQL schema:
tb_*write tables (Trinity pattern),v_*read views building adataJSONB, and write functions - Define GraphQL types with
@fraiseql.type(sql_source=..., jsonb_column="data") - Write query resolvers with
@fraiseql.queryusingdb.find/db.find_one - Write mutations as
@fraiseql.mutationclasses backed by functions that return amutation_response, answered with a@fraiseql.successor@fraiseql.errortype - Assemble and serve the API with
create_fraiseql_appanduvicorn— schema built in memory at startup, no compile step