Skip to content

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 PostgreSQL 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 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_* — 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 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's where and order_by read 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 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, 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 view
  • await db.find_one("v_x", info=info, mandatory_filters={"id": id}) — a single row (or None)

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=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 class CreatePost →   SELECT * FROM create_post(jsonb)
                                                                       (validate + INSERT,
                                                                        returns mutation_response)
  • 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 a @fraiseql.mutation class → its function, which validates, writes and returns a mutation_response whose entity it read back through a view. Its status picks 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 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 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.


Summary

You've learned how to:

  • Design a CQRS PostgreSQL schema: tb_* write tables (Trinity pattern), v_* read views building a data JSONB, and 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 mutations as @fraiseql.mutation classes backed by functions that return a mutation_response, answered with a @fraiseql.success or @fraiseql.error type
  • Assemble and serve the API with create_fraiseql_app and uvicorn — schema built in memory at startup, no compile step