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:

1. Designing the PostgreSQL schema — write tables, read views, and write functions
2. Defining GraphQL types with @fraiseql.type
3. Writing query resolvers with @fraiseql.query
4. Writing mutation resolvers with @fraiseql.mutation
5. Assembling and running the app with create_fraiseql_app + uvicorn
6. 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 fn_* functions
• The Trinity identifier pattern: pk_* (internal BIGINT), id (public UUID), 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 the ID scalar

---

Part 1: The PostgreSQL Schema

FraiseQL follows CQRS (Command Query Responsibility Segregation):

• Reads come from v_* views, each of which exposes an id column plus a data JSONB column built with jsonb_build_object(...).
• Writes go through fn_* PostgreSQL functions, 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_* — internal BIGINT primary key for fast joins (never exposed)
• id — public UUID (the GraphQL id)
• identifier — optional human-readable TEXT slug

-- 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_ and fk_? Internal BIGINT keys make joins fast and keep the public UUID stable and non-enumerable. The pk_*/fk_* columns are never exposed in GraphQL — only id (and optionally identifier) are.

Step 1.3: Read Views (the query side)

A read view always exposes an id column (so resolvers can do WHERE id = $1) plus a data JSONB column built with jsonb_build_object(...). 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,
        'createdAt',  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,
    p.created_at,
    jsonb_build_object(
        'id',        p.id,
        'identifier', p.identifier,
        'title',     p.title,
        'content',   p.content,
        'published', p.published,
        'createdAt', 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,
    c.created_at,
    jsonb_build_object(
        'id',        c.id,
        'content',   c.content,
        'createdAt', 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 camelCase. FraiseQL maps GraphQL's createdAt to your Python field created_at automatically, so build the JSONB with camelCase keys (createdAt, etc.) to match the GraphQL field names clients will request.

Step 1.4: Write Functions (the command side)

Mutations call fn_* PostgreSQL functions. Each function takes a single jsonb input, performs validation and the write, and returns a JSONB result indicating success (and typically the new row's id). All write business logic lives in PostgreSQL.

-- Create an author
CREATE OR REPLACE FUNCTION fn_create_author(input jsonb)
RETURNS jsonb AS $$
DECLARE
    new_id uuid;
BEGIN
    IF input->>'email' IS NULL OR input->>'email' = '' THEN
        RETURN jsonb_build_object('success', false, 'message', 'email is required');
    END IF;

    INSERT INTO tb_author (name, email, bio)
    VALUES (input->>'name', input->>'email', input->>'bio')
    RETURNING id INTO new_id;

    RETURN jsonb_build_object('success', true, 'id', new_id);
END;
$$ LANGUAGE plpgsql;

-- Create a post (looks up the author by public UUID)
CREATE OR REPLACE FUNCTION fn_create_post(input jsonb)
RETURNS jsonb AS $$
DECLARE
    v_fk_author bigint;
    new_id      uuid;
BEGIN
    SELECT pk_author INTO v_fk_author
    FROM tb_author
    WHERE id = (input->>'authorId')::uuid;

    IF v_fk_author IS NULL THEN
        RETURN jsonb_build_object('success', false, 'message', 'author not found');
    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 jsonb_build_object('success', true, 'id', new_id);
END;
$$ LANGUAGE plpgsql;

-- Add a comment to a post
CREATE OR REPLACE FUNCTION fn_add_comment(input jsonb)
RETURNS jsonb 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->>'postId')::uuid;
    SELECT pk_author INTO v_fk_author FROM tb_author WHERE id = (input->>'authorId')::uuid;

    IF v_fk_post IS NULL THEN
        RETURN jsonb_build_object('success', false, 'message', 'post not found');
    END IF;
    IF v_fk_author IS NULL THEN
        RETURN jsonb_build_object('success', false, 'message', 'author not found');
    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 jsonb_build_object('success', true, 'id', new_id);
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 names type and input would 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. The GraphQL field name is inferred from the function name.

@fraiseql.query
async def authors(info) -> list[Author]:
    """List all authors."""
    db = info.context["db"]
    return await db.find("v_author", order_by="-createdAt")


@fraiseql.query
async def author(info, id: ID) -> Author | None:
    """Get a single author by id."""
    db = info.context["db"]
    return await db.find_one("v_author", id=id)


@fraiseql.query
async def posts(info, published: bool | None = None) -> list[Post]:
    """List posts, optionally filtered by published status."""
    db = info.context["db"]
    filters: dict = {}
    if published is not None:
        filters["published"] = published
    return await db.find("v_post", order_by="-createdAt", **filters)


@fraiseql.query
async def post(info, id: ID) -> Post | None:
    """Get a single post by id."""
    db = info.context["db"]
    return await db.find_one("v_post", id=id)


@fraiseql.query
async def post_comments(info, post_id: ID) -> list[Comment]:
    """List comments for a post."""
    db = info.context["db"]
    return await db.find("v_comment", post_id=post_id, order_by="createdAt")

The repository (db) is FraiseQL's CQRS repository. Its main query methods are:

• await db.find("v_x", **filters) — list rows from a view
• await db.find_one("v_x", id=...) — a single row (or None)
• await db.count("v_x", **filters) — a count

Filters become WHERE conditions; order_by accepts a column name, with a - prefix for descending (e.g. "-createdAt").

Step 2.3: Inputs and Result Types

Mutations take an @fraiseql.input and return a union of an @fraiseql.success and an @fraiseql.error. The @fraiseql.success decorator auto-injects status, message, updated_fields, and id fields.

@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:
    message: str
    code: str = "VALIDATION_ERROR"

Step 2.4: Mutations

A @fraiseql.mutation resolver calls a fn_* function via db.execute_function(name, params). It receives the function's JSONB result as a dict; on success it reads the freshly written row back through a view and wraps it in the success type.

@fraiseql.mutation
async def create_author(
    info, input: CreateAuthorInput
) -> CreateAuthorSuccess | MutationError:
    """Create a new author."""
    db = info.context["db"]
    result = await db.execute_function(
        "fn_create_author",
        {"name": input.name, "email": input.email, "bio": input.bio},
    )
    if not result.get("success"):
        return MutationError(message=result.get("message", "Failed to create author"))

    author = await db.find_one("v_author", id=result["id"])
    return CreateAuthorSuccess(author=author)


@fraiseql.mutation
async def create_post(
    info, input: CreatePostInput
) -> CreatePostSuccess | MutationError:
    """Create a new post."""
    db = info.context["db"]
    result = await db.execute_function(
        "fn_create_post",
        {
            "title": input.title,
            "content": input.content,
            "authorId": str(input.author_id),
            "published": input.published,
        },
    )
    if not result.get("success"):
        return MutationError(message=result.get("message", "Failed to create post"))

    post = await db.find_one("v_post", id=result["id"])
    return CreatePostSuccess(post=post)


@fraiseql.mutation
async def add_comment(
    info, input: AddCommentInput
) -> AddCommentSuccess | MutationError:
    """Add a comment to a post."""
    db = info.context["db"]
    result = await db.execute_function(
        "fn_add_comment",
        {
            "postId": str(input.post_id),
            "authorId": str(input.author_id),
            "content": input.content,
        },
    )
    if not result.get("success"):
        return MutationError(message=result.get("message", "Failed to add comment"))

    comment = await db.find_one("v_comment", id=result["id"])
    return AddCommentSuccess(comment=comment)

---

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=[create_author, create_post, add_comment],
    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=False enables the interactive playground and introspection, which is what you want while developing. Set it to True for 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 resolver        →   SELECT fn_create_post(jsonb)
                               db.execute_function("fn_create_post")   (validate + INSERT)

• Reads flow through @fraiseql.query → db.find / db.find_one → a v_* view's data JSONB. FraiseQL trims the JSONB to exactly the fields the client requested.
• Writes flow through @fraiseql.mutation → db.execute_function → an fn_* function that validates and writes, then the resolver reads the result back through a view.
• 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 psql to 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 must match GraphQL field names

GraphQL field createdAt maps to your Python field created_at. Build the view's JSONB with the camelCase key ('createdAt', a.created_at) so the data lines up with what clients request.

Never expose pk_*

Keep pk_*/fk_* out of the data JSONB. Expose only id (a UUID) and, optionally, identifier.

Pass UUID arguments to functions as strings

db.execute_function serializes its params dict to JSONB. Cast ID/UUID arguments with str(...), and cast them back inside the function (e.g. (input->>'authorId')::uuid).

---

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 a data JSONB, and fn_* write functions
• Define GraphQL types with @fraiseql.type(sql_source=..., jsonb_column="data")
• Write query resolvers with @fraiseql.query using db.find / db.find_one
• Write mutation resolvers with @fraiseql.mutation using db.execute_function, returning @fraiseql.success | @fraiseql.error unions
• Assemble and serve the API with create_fraiseql_app and uvicorn — schema built in memory at startup, no compile step