Skip to content
FraiseQL v2.0.0-alpha

Threaded Comments

This guide picks up where Your First API left off. The blog API has a flat tb_comment table where every comment belongs directly to a post. This guide adds threading: comments can reply to other comments, producing a tree. A single recursive CTE view exposes the full tree to FraiseQL, which reconstructs the nesting from the parent_id relationship.

What We’re Building

Section titled “What We’re Building”
  • A parent_id self-reference on tb_comment
  • A recursive CTE view that exposes each comment’s depth and ancestors
  • A self-referential Comment type with a children field
  • A createComment mutation that accepts an optional parent_id

Step 1: Update the Comments Table

Section titled “Step 1: Update the Comments Table”

Add a nullable parent_id column that references tb_comment itself. Top-level comments leave it NULL; replies point to their parent.

db/schema/01_write/tb_comment_thread.sql
ALTER TABLE tb_comment
    ADD COLUMN fk_parent_comment BIGINT
        REFERENCES tb_comment(pk_comment)
        ON DELETE CASCADE,
    ADD COLUMN parent_id UUID
        REFERENCES tb_comment(id)
        ON DELETE CASCADE;


/*index so "fetch children of comment X" is a seek, not a scan*/
CREATE INDEX ix_tb_comment_fk_parent ON tb_comment(fk_parent_comment)
    WHERE fk_parent_comment IS NOT NULL;

Step 2: Replace the Flat View with a Recursive CTE View

Section titled “Step 2: Replace the Flat View with a Recursive CTE View”

The original v_comment was a simple join. Replace it with a view that uses a recursive CTE to walk the tree. Each row exposes depth (0 = top-level) and ancestors (an ordered array of ancestor IDs for breadcrumb display).

FraiseQL reads this view as a flat result set. It reconstructs the tree by following the parent_id relationship declared in the schema — the view’s job is only to expose the right columns cleanly.

db/schema/02_read/v_comment.sql
CREATE OR REPLACE VIEW v_comment AS
WITH RECURSIVE comment_tree AS (
    /*base case: top-level comments*/
    SELECT
        c.pk_comment,
        c.id,
        c.fk_post,
        c.fk_parent_comment,
        c.parent_id,
        c.content,
        c.fk_user,
        c.created_at,
        0                           AS depth,
        ARRAY[]::uuid[]             AS ancestors
    FROM tb_comment c
    WHERE c.fk_parent_comment IS NULL


    UNION ALL


    /*recursive case: replies*/
    SELECT
        child.pk_comment,
        child.id,
        child.fk_post,
        child.fk_parent_comment,
        child.parent_id,
        child.content,
        child.fk_user,
        child.created_at,
        parent.depth + 1            AS depth,
        parent.ancestors || parent.id AS ancestors
    FROM tb_comment child
    JOIN comment_tree parent
        ON child.fk_parent_comment = parent.pk_comment
    WHERE parent.depth < 10         /*guard against runaway recursion*/
)
SELECT
    ct.id,
    ct.parent_id,
    ct.fk_post,
    jsonb_build_object(
        'id',         ct.id::text,
        'parent_id',  ct.parent_id::text,
        'content',    ct.content,
        'depth',      ct.depth,
        'ancestors',  ct.ancestors,
        'created_at', ct.created_at,
        'author',     vu.data
    ) AS data
FROM comment_tree ct
JOIN tb_user u  ON u.pk_user = ct.fk_user
JOIN v_user vu  ON vu.id = u.id;

The depth < 10 guard in each CTE prevents runaway recursion on malformed or adversarial data. Adjust the limit for your domain — most blog comment trees stay under five levels in practice.

Step 3: Update the Comment Type

Section titled “Step 3: Update the Comment Type”

Add a children field to Comment. This is a self-referential list: FraiseQL resolves it by querying v_comment filtered to rows where parent_id matches the current comment’s id.

schema.py
from __future__ import annotations
import fraiseql
from fraiseql.scalars import ID, DateTime


@fraiseql.type
class Comment:
    """A comment on a post, optionally nested under another comment."""
    id: ID
    parent_id: ID | None
    content: str
    depth: int
    ancestors: list[ID]
    created_at: DateTime
    author: 'User'
    children: list['Comment']

FraiseQL resolves children by selecting from v_comment where parent_id = <this comment's id>. No custom resolver needed.

Step 4: Add the createComment Mutation

Section titled “Step 4: Add the createComment Mutation”

The new mutation accepts an optional parent_id. The database function validates that, when provided, the parent comment belongs to the same post — preventing cross-post threading.

Database Function

Section titled “Database Function”
db/schema/03_mutations/fn_create_comment.sql
CREATE OR REPLACE FUNCTION fn_create_comment(
    p_content   TEXT,
    p_post_id   UUID,
    p_author_id UUID,
    p_parent_id UUID DEFAULT NULL
) RETURNS UUID LANGUAGE plpgsql AS $$
DECLARE
    v_fk_user    BIGINT;
    v_fk_post    BIGINT;
    v_fk_parent  BIGINT;
    v_id         UUID;
BEGIN
    /*validate: author exists*/
    SELECT pk_user INTO v_fk_user FROM tb_user WHERE id = p_author_id;
    IF v_fk_user IS NULL THEN
        RAISE EXCEPTION 'author not found: %', p_author_id;
    END IF;


    /*validate: post exists*/
    SELECT pk_post INTO v_fk_post FROM tb_post WHERE id = p_post_id;
    IF v_fk_post IS NULL THEN
        RAISE EXCEPTION 'post not found: %', p_post_id;
    END IF;


    /*validate: parent belongs to the same post*/
    IF p_parent_id IS NOT NULL THEN
        SELECT pk_comment INTO v_fk_parent
        FROM tb_comment
        WHERE id = p_parent_id AND fk_post = v_fk_post;


        IF v_fk_parent IS NULL THEN
            RAISE EXCEPTION
                'parent comment % not found on post %', p_parent_id, p_post_id;
        END IF;
    END IF;


    INSERT INTO tb_comment (
        identifier, fk_user, fk_post, fk_parent_comment, parent_id, content
    )
    VALUES (
        'cmt_' || gen_random_uuid()::text,
        v_fk_user, v_fk_post, v_fk_parent, p_parent_id, p_content
    )
    RETURNING id INTO v_id;


    RETURN v_id;
END; $$;

Schema Declaration

Section titled “Schema Declaration”
schema.py
@fraiseql.input
class CreateCommentInput:
    content:   str
    post_id:   ID
    author_id: ID
    parent_id: ID | None = None


@fraiseql.mutation(sql_source="fn_create_comment", operation="custom")
def create_comment(input: CreateCommentInput) -> Comment:
    """Create a top-level comment or a reply to an existing comment."""
    pass

Rebuild and Test

Section titled “Rebuild and Test”

Apply the schema changes and restart:

Terminal window
confiture build --env local
fraiseql compile
fraiseql run

Create a Thread

Section titled “Create a Thread”
# Top-level comment
mutation {
  createComment(input: {
    content:  "Great post!"
    postId:   "550e8400-e29b-41d4-a716-446655440000"
    authorId: "6ba7b810-9dad-11d1-80b4-00c04fd430c8"
  }) {
    id
    content
    depth
  }
}
# Reply to the comment above
mutation {
  createComment(input: {
    content:  "Thanks, glad you liked it."
    postId:   "550e8400-e29b-41d4-a716-446655440000"
    authorId: "6ba7b811-9dad-11d1-80b4-00c04fd430c8"
    parentId: "a3f1c924-1234-5678-abcd-ef0123456789"
  }) {
    id
    content
    depth
    ancestors
  }
}

Query the Full Thread

Section titled “Query the Full Thread”

Fetch a post with its complete comment tree. Pass parentId: null to the comments field to get only top-level comments; each level’s children field recurses from there.

query {
  post(id: "550e8400-e29b-41d4-a716-446655440000") {
    title
    comments(parentId: null) {
      id
      content
      depth
      author { username }
      children {
        id
        content
        depth
        author { username }
        children {
          id
          content
        }
      }
    }
  }
}

Response shape:

{
  "data": {
    "post": {
      "title": "Hello World",
      "comments": [
        {
          "id": "a3f1c924-...",
          "content": "Great post!",
          "depth": 0,
          "author": { "username": "alice" },
          "children": [
            {
              "id": "b7e2d035-...",
              "content": "Thanks, glad you liked it.",
              "depth": 1,
              "author": { "username": "bob" },
              "children": []
            }
          ]
        }
      ]
    }
  }
}

FraiseQL issues one query against v_comment for the post’s comments, then resolves each children field from the same flat result set — no additional round trips per level.

Performance Considerations

Section titled “Performance Considerations”

Recursive CTEs scan the full subtree on every query. For most blog workloads this is fine, but there are two things worth doing from the start:

Index fk_parent_comment. The CTE join condition is child.fk_parent_comment = parent.pk_comment. Without an index on fk_parent_comment, this becomes a sequential scan of the whole table for every recursion level. The CREATE INDEX statements in Step 1 cover this.

Cap recursion depth in the CTE. The WHERE parent.depth < 10 guard prevents the CTE from walking arbitrarily deep trees. Choose a limit that matches your domain. A depth column on the table — maintained by the function — lets you enforce the limit at write time instead of at query time.

Avoid aggregating the full tree into the post view. The original v_post view aggregated all v_comment rows with jsonb_agg. With threading, this would recurse the entire tree every time a post is loaded, regardless of whether the client requested comments. Instead, keep comments as a separate field resolved on demand from v_comment — this is the default FraiseQL behavior when children is declared on the Comment type.

What’s Next

Section titled “What’s Next”
  • Projection Tables — Maintain a comment_count column on tb_post using CQRS read models, so post listings never aggregate comments
  • Observers — Notify a post author when a reply arrives, triggered from the database without polling
  • Custom Queries — Add pagination and depth-limited filters to comment queries